how to execute sql() routine of JJUPIN to save data in database

[eric anier]

hi,

I have a procedure in oracle db which insert new data on a custom table and a commit at the end. Calling this procedure directly from my db tool(toad) it works fine.

But using JJUPIN sql() routine in a SIL script and calling it from SIL RUNNER Gadget, it doesnt work.

Is it possible to execute insert statement with sql() routine from Sil Runner Gadget?

sqlDo = "execute CUSTOM_PKG.INSERTDATA('test')";

sqlResult = sql("JiraDB", sqlDo);

I have been using sql() routine and Sil Runner Gadget successfully many times but just querying data from external table.

Regards,

[Błażej O_ · Accepted solution]

OK, how about trying such script?:

string param1 = "'test'";
string sqlDo = "begin CUSTOM_PKG.INSERTDATA("+param1+"); end;";
string[] sqlResult = sql("JiraDB", sqlDo);

[Silviu Burcea]

Hi Eric,

You have a procedure there. You have to use a different routine for that: https://confluence.kepler-rominfo.com/display/SIL30/sqlCallStoredProcedure. If you need out params, check the very similar routine designed for this task.

HTH,

Silviu

[eric anier]

Hi Silviu, how do i do this in JJUPIN 2.6? Regards,

[Alexandra Topoloaga]

Hi Eric, The routine is available since JJUPIN 3.0. I suggest you to update to JJUPIN 3.0 since, in addition to bugfixes, we also added lots of features, including structures and error handling in SIL language. Alexandra

[eric anier]

Hi Alexandra, We will upgrade but not yet this time, do we have any other way to execute a procedure from Sil Runner Gadget? a workaround perhaps? Regards,

[Silviu Burcea]

Short answer: no workaround Long answer: There is https://confluence.kepler-rominfo.com/display/SIL/system routine, if you can manage to invoke an sql command using it, you have the solution for your problem.

[eric anier]

Thanks I'll give it a try...

[Błażej O_]

What type of data are your parameters?

You can try this way:

string param1 = "'test'";
string sqlDo = "execute CUSTOM_PKG.INSERTDATA("+param1+")";
string[] sqlResult = sql("JiraDB", sqlDo);

[eric anier]

Hi Blazej, I even tried a procedure without parameter, just directly insert and commit, it just doesnt work. Probably need to upgrade for this routine sqlCallStoredProcedure. Regards,

[Błażej O_]

Damn, maybe that's because Oracle handles it differently. We use above approach a lot in MS SQL and it works OK. Are you absolutely sure, that db user used in the JNDI source have permission to execute procedures and/or write data?

[eric anier]

yup, user used on JNDI/DB Tool is the same, and it works when using DB Tool.

[eric anier]

Hi Blazej, Can you share how you define your JNDI source?

[Błażej O_]

Sure, however it's a congiuration to ms sql, not oracle: <Resource name="myJNDIname" auth="Container" type="javax.sql.DataSource" username="myDBuser" password="myDBpassword" driverClassName="net.sourceforge.jtds.jdbc.Driver" url="jdbc:jtds:sqlserver://mySERVERaddress" />

[eric anier]

its same with ours.. :(

[Błażej O_]

Ok, one more thought. Have you looked into the logs? What response do you get when you try to call the procedure?

[eric anier]

This is the error log, any idea? 2015-08-13 18:54:14,894 pool-15-thread-1 ERROR eric1000 582x2403x1 jxc0go 192.168.10.10 /rest/keplerrominfo/jjupin/latest/rungadget/run [commons.sil.routines.SQLRoutine] Could not execute SQL >>execute PKG.INSERTDATA('Test')<< on database JiraDB. java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)

[eric anier]

wow... it works.. thanks a lot

[Błażej O_]

Point for google :> I've never used oracle in my life, just googled it up :D I'm glad it works :) I'll convert the above comment to an answer, someone might use it in future :)

[Błażej O_]

P.S. If my answer was helpful and worked, please, accept it as an answer to your question :)

[eric anier]

sure, convert it to answer..