Few posts back i have shown ,how to display the value of the variable using Jython and today i am showing how to display  SQL  Query result  in the operator.

— Displays the Sql results of query into Operator –

import string
import java.sql as sql
import java.lang as lang
sourceConnection = odiRef.getJDBCConnection("SRC")
sqlstring = sourceConnection.createStatement()
result=sqlstring.executeQuery("select table_name,owner from all_tables where owner='ODI_TEMP'")
i=0
rs=[]
while (result.next()):
    rs.append(str(i)+'t'+str(result.getString("table_name"))+'tt'+str(result.getString("owner"))+' , ')
    i=i+1

res='n'.join(map(string.strip,rs))
raise res
sourceConnection.close()

In  sourceConnection = odiRef.getJDBCConnection(“SRC”), the connection details are retrieved from ODI , to do so in the ‘ command on  Source tab’  – Select the Technology and required Schema so that ODI can retrieve the connection details.

result=sqlstring.executeQuery(“select table_name,owner from all_tables where owner=’ODI_TEMP'”)

# Enter the query between the two double quotes or replace the above query .

rs.append(str(i)+’t’+str(result.getString(“table_name”))+’tt’+str(result.getString(“owner”))+’ , ‘)

# For every column you mention in the above query – get them under result.getString(“Column_Name”)

# as for my example i am having two column so iam calling getString twice , similarly do so for every column

# called in the Select statement

In the Target technology select  ‘  Jython ‘

Enable  the ‘ Ignore Errors’ option.

Sample Output

– Codes to display the Row count –

import string
import java.sql as sql
import java.lang as lang
sourceConnection = odiRef.getJDBCConnection("SRC")
sqlstring = sourceConnection.createStatement()
sqlstmt="select count(*) as rowcount from all_tables where owner='ODI_TEMP'"
result=sqlstring.executeQuery(sqlstmt)
while result.next():
    count=result.getInt("rowcount")

rs='n'+str(sqlstmt)+'nn'+str(count)
raise rs
sourceConnection.close()

Sample Output