Oninit cdi / Archive
Talk to an expert →

RE: JDBC - calling a function with a OUT parameter

Posted in 2000

Topics: Stored Procedures & SPL, Connectivity: ODBC / JDBC / .NET, Data Types & Schema Design, Java & JDBC Development

Csom Gyula — — source: Informix-list mailing list archive (1991-1998)

Hi, could you show the original query? Otherwise you should use this sortof syntax: SELECT * FROM mytable WHERE subscribe(mycol1, mycol2, effected_p # INT)>0 AND effected_p=100; The SLV syntax is: slv_name # data_type Note: the function call must exist int he WHERE clause if you use its OUT paramter. Hope it helps. Csomi -----Original Message----- From: ritayung@my-deja.com [mailto:ritayung@my-deja.com] Sent: Friday, December 15, 2000 11:52 PM To: informix-list@iiug.org Subject: JDBC - calling a function with a OUT parameter I created a function in the Informix Dynamic Server 9.21 server as: CREATE FUNCTION subscribe_email_address(email_address_p varchar(255), listname_p varchar(80), OUT effected_p int) RETURNING int.. .. END FUNCTION Executing the function using JDBC gives me the following error : java.sql.SQLException: Argument must be a Statement Local Variable for an OUT parameter. at java.lang.Throwable.fillInStackTrace(Native Method) at java.lang.Throwable.fillInStackTrace(Compiled Code) at java.lang.Throwable.<init>(Compiled Code) at java.lang.Exception.<init>(Compiled Code) at java.sql.SQLException.<init>(SQLException.java:43) at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java) at com.informix.jdbc.IfxSqli.errorDone(IfxSqli.java) at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java) at com.informix.jdbc.IfxSqli.receiveMessage(Compiled Code) at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java) at com.informix.jdbc.IfxResultSet.executePrepare (IfxResultSet.java) at com.informix.jdbc.IfxPreparedStatement.<init> (IfxPreparedStatement.java) at com.informix.jdbc.IfxCallableStatement.<init> (IfxCallableStatement.java) at com.informix.jdbc.IfxSqliConnect.prepareCall (IfxSqliConnect.java) at DbPing.main(DbPing.java:453) Can anyone shed some light on my problem? --------------Excerpt of the code ------------------------------- String command = "{ ? = call unsubscribe_email_address (? , ?, ?)} "; System.out.println("prepareCall...preparing"); CallableStatement cstmt_t = null; cstmt_t = tcon.prepareCall(command); System.out.println("prepareCall (" + command + ")...okay"); System.out.println(""); System.out.println(""); System.out.println("Warnings on CallableStatement object: "); System.out.println("setString1"); cstmt_t.setString(1, "emailAddress@yahoo.com"); System.out.println("setString2"); cstmt_t.setString(2, "listname"); System.out.println("setInt3"); cstmt_t.setInt(3, 1); cstmt_t.registerOutParameter(3, java.sql.Types.INTEGER); ResultSet rs = cstmt_t.executeQuery(); Sent via Deja.com http://www.deja.com/

↪ replying to Csom Gyula
ritayung@my-deja.com — — source: Informix-list mailing list archive (1991-1998)

I tried your suggested select statement but DBAccess returns 'Illegal SQL statement in SPL routine.' However, my problem is related to the prepareCall statement in Infromix JDBC. As soon as the program tries to execute the prepareCall statement it throws out an exception at runtime. --------------Excerpt of the code ------------------------------- > String command = "{ ? = call unsubscribe_email_address > (? , ?, ?)} "; > > System.out.println("prepareCall...preparing"); > CallableStatement cstmt_t = null; > cstmt_t = tcon.prepareCall(command); CREATE FUNCTION subscribe_email_address(email_address_p varchar(255),listname_p varchar(80), OUT effected_p i nt) RETURNING int DEFINE effected_p INT; INSERT INTO rita_test values (email_address_p, 'TEXT', 'Y'); LET effected_p = 1; RETURN 0; END FUNCTION ; In article <91l7ti$fvj$1@news.xmission.com>, Csom Gyula <Csom@interface.hu> wrote: > > Hi, > could you show the original query? Otherwise you should use this > sortof syntax: > > SELECT * FROM mytable > WHERE subscribe(mycol1, mycol2, effected_p # INT)>0 > AND effected_p=100;> > The SLV syntax is: slv_name # data_type > Note: the function call must exist int he WHERE clause if you use > its OUT paramter. > > Hope it helps. > > Csomi > > -----Original Message----- > From: ritayung@my-deja.com [mailto:ritayung@my-deja.com] > Sent: Friday, December 15, 2000 11:52 PM > To: informix-list@iiug.org > Subject: JDBC - calling a function with a OUT parameter > > I created a function in the Informix Dynamic Server 9.21 server as: > > CREATE FUNCTION subscribe_email_address(email_address_p varchar(255), > listname_p varchar(80), OUT effected_p int) > RETURNING int> .. > .. > END FUNCTION > > Executing the function using JDBC gives me the following error : > java.sql.SQLException: Argument must be a Statement Local Variable for > an OUT parameter. > at java.lang.Throwable.fillInStackTrace(Native Method) > at java.lang.Throwable.fillInStackTrace(Compiled Code) > at java.lang.Throwable.<init>(Compiled Code) > at java.lang.Exception.<init>(Compiled Code) > at java.sql.SQLException.<init>(SQLException.java:43) > at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java) > at com.informix.jdbc.IfxSqli.errorDone(IfxSqli.java) > at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java) > at com.informix.jdbc.IfxSqli.receiveMessage(Compiled Code) > at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java) > at com.informix.jdbc.IfxResultSet.executePrepare > (IfxResultSet.java) > at com.informix.jdbc.IfxPreparedStatement.<init> > (IfxPreparedStatement.java) > at com.informix.jdbc.IfxCallableStatement.<init> > (IfxCallableStatement.java) > at com.informix.jdbc.IfxSqliConnect.prepareCall > (IfxSqliConnect.java) > at DbPing.main(DbPing.java:453) > > Can anyone shed some light on my problem? > --------------Excerpt of the code ------------------------------- > String command = "{ ? = call unsubscribe_email_address > (? , ?, ?)} "; > > System.out.println("prepareCall...preparing"); > CallableStatement cstmt_t = null; > cstmt_t = tcon.prepareCall(command); > System.out.println("prepareCall (" + command + ")...okay"); > System.out.println(""); > System.out.println(""); > System.out.println("Warnings on CallableStatement > object: "); > > System.out.println("setString1"); > cstmt_t.setString(1, "emailAddress@yahoo.com"); > System.out.println("setString2"); > cstmt_t.setString(2, "listname"); > System.out.println("setInt3"); > cstmt_t.setInt(3, 1); > cstmt_t.registerOutParameter(3, java.sql.Types.INTEGER); > ResultSet rs = cstmt_t.executeQuery(); > > Sent via Deja.com > http://www.deja.com/ > > Sent via Deja.com http://www.deja.com/

↪ replying to ritayung@my-deja.com
Jonathan Leffler — — source: Informix-list mailing list archive (1991-1998)

ritayung@my-deja.com wrote: > I tried your suggested select statement but DBAccess returns 'Illegal > SQL statement in SPL routine.' You need a semi-colon after "RETURNING int". That should generate some sort of error, such as the ubiquitous -201 syntax error. > However, my problem is related to the prepareCall statement in Infromix > JDBC. As soon as the program tries to execute the prepareCall statement > it throws out an exception at runtime. > > --------------Excerpt of the code ------------------------------- > > String command = "{ ? = call unsubscribe_email_address > > (? , ?, ?)} "; > > > > System.out.println("prepareCall...preparing"); > > CallableStatement cstmt_t = null; > > cstmt_t = tcon.prepareCall(command); The curly brackets denote a comment in Informix's dialect of SQL. Unless they mean something special to JDBC, I think the command string above is doomed to do nothing. > CREATE FUNCTION subscribe_email_address(email_address_p varchar(255),> listname_p varchar(80), OUT effected_p i > nt) > RETURNING int > DEFINE effected_p INT; > INSERT INTO rita_test values (email_address_p, 'TEXT', 'Y');> > LET effected_p = 1; > RETURN 0; > END FUNCTION > ; > > In article <91l7ti$fvj$1@news.xmission.com>, > Csom Gyula <Csom@interface.hu> wrote: > > > > Hi, > > could you show the original query? Otherwise you should use this > > sortof syntax: > > > > SELECT * FROM mytable > > WHERE subscribe(mycol1, mycol2, effected_p # INT)>0 > > AND effected_p=100;> > > > The SLV syntax is: slv_name # data_type > > Note: the function call must exist int he WHERE clause if you use > > its OUT paramter. > > > > Hope it helps. > > > > Csomi > > > > -----Original Message----- > > From: ritayung@my-deja.com [mailto:ritayung@my-deja.com] > > Sent: Friday, December 15, 2000 11:52 PM > > To: informix-list@iiug.org > > Subject: JDBC - calling a function with a OUT parameter > > > > I created a function in the Informix Dynamic Server 9.21 server as: > > > > CREATE FUNCTION subscribe_email_address(email_address_p varchar(255), > > listname_p varchar(80), OUT effected_p int) > > RETURNING int> > .. > > .. > > END FUNCTION > > > > Executing the function using JDBC gives me the following error : > > java.sql.SQLException: Argument must be a Statement Local Variable for > > an OUT parameter. > > at java.lang.Throwable.fillInStackTrace(Native Method) > > at java.lang.Throwable.fillInStackTrace(Compiled Code) > > at java.lang.Throwable.<init>(Compiled Code) > > at java.lang.Exception.<init>(Compiled Code) > > at java.sql.SQLException.<init>(SQLException.java:43) > > at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java) > > at com.informix.jdbc.IfxSqli.errorDone(IfxSqli.java) > > at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java) > > at com.informix.jdbc.IfxSqli.receiveMessage(Compiled Code) > > at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java) > > at com.informix.jdbc.IfxResultSet.executePrepare > > (IfxResultSet.java) > > at com.informix.jdbc.IfxPreparedStatement.<init> > > (IfxPreparedStatement.java) > > at com.informix.jdbc.IfxCallableStatement.<init> > > (IfxCallableStatement.java) > > at com.informix.jdbc.IfxSqliConnect.prepareCall > > (IfxSqliConnect.java) > > at DbPing.main(DbPing.java:453) > > > > Can anyone shed some light on my problem? > > --------------Excerpt of the code ------------------------------- > > String command = "{ ? = call unsubscribe_email_address > > (? , ?, ?)} "; > > > > System.out.println("prepareCall...preparing"); > > CallableStatement cstmt_t = null; > > cstmt_t = tcon.prepareCall(command); > > System.out.println("prepareCall (" + command > + ")...okay"); > > System.out.println(""); > > System.out.println(""); > > System.out.println("Warnings on CallableStatement > > object: "); > > > > System.out.println("setString1"); > > cstmt_t.setString(1, "emailAddress@yahoo.com"); > > System.out.println("setString2"); > > cstmt_t.setString(2, "listname"); > > System.out.println("setInt3"); > > cstmt_t.setInt(3, 1); > > cstmt_t.registerOutParameter(3, java.sql.Types.INTEGER); > > ResultSet rs = cstmt_t.executeQuery(); -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"

Related threads