I have just read the following sentence in the odi jyhon reference:
"Oracle Data Integrator users may write procedures or knowledge modules using Jython, and may mix Jython code with SQL, PL/SQL, OS Calls, etc."
Does anyone of you know how jython can be mixed with SQL in a knowledge module? I just know how to embed java code with the <% %> tag. But how about jython (e.g. when used in a knowledge module task with technology = "Oracle"?
I appreciate your help.
A few lines below there is a note, which seems to be contradicting the sentence quoted above.
"Note: To use Jython code in KM procedure commands or procedures commands, you must systematically set the technology to Jython."
I guess I just interpret the phrase "mix sql and jython" differently.
You can mix jython and SQL in playing on the 2 windows "Command on Source" and "Command on Target" with the Jython on the first which call variable aliased.
And the SQL in the 2nd where you alias the result .
This sounds very interesting. I just could not figure out how to access the result of the jython "command on source". How can I access this alias from the SQL statement? Second, even if I set the technology to jython on the "command on source" I still get an error message "java.sql.SQLException: Statement does not generate a result set"
I am lost :-(
Please help. Thanks!
Message was edited by:
So Firstly I have never done this in KM but it works well in Procedure so I think it would work in a KM but I'm not sure.
So I will show you an exemple which will be easyer than lot of explanation.
This exemple is a step of a procedure which is supposed to send an email if there is an error in an execution.
This Step retrieve informations about the execution as the error message or the execution context...
In the Target Command :
I have :
EmailBody = EmailBody + r'''<TR><TD><CENTER>#NO</CENTER></TD><TD>#SESS_NAME</TD><TD>#STEP_NAME
Contexte = '#CONTEXT'.
--> the #variable are the informations that I will extract in the source.
In the Source Command :
Technology = Oracle .
SELECT L.SESS_NO || ' / ' || L.NNO "NO", STEP_NAME, SESS_NAME, SS.CONTEXT_CODE "CONTEXT", L.STEP_BEG "DEBUT", X.TXT "MSG", L.step_rc "RC"
FROM <%=snpRef.getObjectName("L", "SNP_STEP_LOG", "D")%> L,
<%=snpRef.getObjectName("L", "SNP_SESS_STEP", "D")%> SS,
<%=snpRef.getObjectName("L", "SNP_SESSION", "D")%> S,
<%=snpRef.getObjectName("L", "SNP_EXP_TXT", "D")%> X
WHERE L.SESS_NO = <%=snpRef.getSession("SESS_NO")%> AND
L.SESS_NO = SS.SESS_NO AND L.NNO = SS.NNO AND S.SESS_NO = L.SESS_NO AND
STEP_STATUS = 'E' and L.I_TXT_STEP_MESS=X.I_TXT and X.TXT_ORD=0
And with this the Jython keep the information of the Query...
So I would like to apologize cause in my last post I have inversed the Target and the Source... Sorry. Hope that your problem didn't come from here...
Evidently you will have SQL as Source and Jython as Target...
That makes things much clearer. Now I can understand why I got the SQL Exception.
Thank you for clarification. Though, it looks like I can not use this to solve my problem, because I actually wanted to do exactly the opposite (use jython variable in SQL statement).
Maybe I should think of another approach.
If you are interested, this is what I wanted to achieve:
I wanted to be able to access the odiRef.getAKColList() method in an IKM. Thats why I created a CKM which writes the result of this method into a jython variable. The IKM inlcudes the CKM and therefore has access to the variable. Then I got stuck with getting this info into an SQL statement.
Anyway, thank you very much for your help!
you can use use java classes in Jython like in following short example:
import java.sql as sql
import java.lang as lang
sql_text="bla-bla-bla" + var1
lang.Class.forName("<%=snpRef.getInfo( "DEST_JAVA_DRIVER" )%>")
myCon = sql.DriverManager.getConnection("<%=snpRef.getInfo( "DEST_JAVA_URL" )%>", "<%=snpRef.getInfo( "DEST_USER_NAME" )%>", "<%=snpRef.getInfo( "DEST_PASS" )%>")
prepStmt = myCon.prepareStatement(sql_text)
As a result your query will be executed against target database.