Stored Procedures JDBC

[mighty wayward]

Hi

I'm calling a Teradata stored procedure using JDBC. My code (which someone
has helped me with as I'm not a java person) is below:

<cfset getPageContext().getClass().forName("sun.jdbc.odbc .JdbcOdbcDriver")>

<cfset objDriver = createObject("java","java.sql.DriverManager") >

<cfset conn =
objDriver.getConnection("jdbc:sequelink:odbcsocket ://localhost:19996;serverDatas
ource=odbcTDPR", "USERADMIN", "48HFHFJ") >

<cfset cs = conn.prepareCall("Call USERADMIN.DropUser
('AATEST302','HAYWARDN','testing','v',cast(StmntOu t as Varchar(200)) );") >

<cfset getStmntOutVariable = cs.getString(4)>

<cfoutput>#getStmntOutVariable#</cfoutput>

The code works in so much that it does run the stored procedure and drops the
user however, I need to get the value returned in the StmntOut variable e.g.
"User successfully dropped"

However,the bit "cs.getString(4)" gives me the error message:

[DataDirect][SequeLink JDBC Driver]Unsupported method:
CallableStatement.getString

This is driving me nuts so any help would be massively appreciated.

Thanks,
Neil

[mxstu]

I'm not using teradata, but I believe you need to register the output
parameters for the stored procedure.

I've attached an example that works for sql server. Some of the
CallableStatement methods are overloaded, so I had to use javacast() around
some of the method parameters to get it to work in CF.

HTH




-- sample stored procedure
CREATE PROCEDURE testOutPutParam @num int, @result varchar(50) OUT
AS
BEGIN
SET @Result = 'The input variable is '+ cast(@num as varchar)
END
GO


-- code to call the sample stored procedure
<cfscript>

getPageContext().getClass().forName("com.microsoft .jdbc.sqlserver.SQLServerDriv
er");

//create connection
objDriver = createObject("java", "java.sql.DriverManager");
conn =
objDriver.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=
dbName",
"userName", "password");

cs = conn.prepareCall("{ call testOutPutParam (?,?) }");
//set value of input parameter
cs.setInt(javacast("int", 1), 1237);
//register output parameter
types = createObject("java", "java.sql.Types");
cs.registerOutParameter(javacast("int", 2), types.VARCHAR);

cs.executeUpdate();

//get output parameter
outValue = cs.getString(javacast("int", 2));

//clean up
cs.close();
conn.close();
</cfscript>

[mighty wayward]

Thanks for getting back to me. I've updated my code (see below) but I get the
following error message:

[DataDirect][SequeLink JDBC Driver]Unsupported method:
CallableStatement.registerOutParameter

relating to: cs.registerOutParameter(javacast("string", 5), types.VARCHAR);

Have I missed something simple or could it be that my driver is no good?
Project is now on hold until I figure this out :-(

The weird thing is, I can get the stored procedure to run and drop the user.
The problems arise when I try to retrive the success/failure message(s) that
the SP generates. This alas is cruical.


//*************** CODE ***********************//

getPageContext().getClass().forName("sun.jdbc.odbc .JdbcOdbcDriver");

objDriver = createObject("java", "java.sql.DriverManager");
conn =
objDriver.getConnection("jdbc:sequelink:odbcsocket ://localhost:19996;serverDatas
ource=odbcTDPR", "myUSERNAME", "myPASSWORD");

cs = conn.prepareCall("Call USERADMIN.DropUser
('AATEST302','HAYWARDN','testing','v',cast(StmntOu t as Varchar(200)))");

//register output parameter
types = createObject("java", "java.sql.Types");
cs.registerOutParameter(javacast("string", 5), types.VARCHAR);

cs.executeUpdate();

outValue = cs.getString(javacast("string", 5));

cs.close();
conn.close();

<cfoutput>#outValue#</cfoutput>

//************************************************** ****//

[mxstu]

I really couldn't say about your drivers. You would need to look at their
documentation to determine what features they support. The method
registerOutputParameter is from Sun's spec for CallableStatement
[url]http://java.sun.com/j2se/1.4.2/docs/api/java/sql/CallableStatement.html[/url]

1) Did you first try calling a simple stored procedure (like the one I posted)
as a test?
2) I noticed you're not passing the input/output parameters the same way as in
the example I posted (i.e. { call yourProcedureName(?,?) } ... where each
parameter is indicated by a "?"). Did you try using that syntax, instead of
passing the values directly?

If for some reason, your driver does not support output parameters, you could
also change the stored procedure to return the message as a resultset (i.e.
SELECT @SomeMessage AS OutputMessage...) and retrieve it as a resultset.

[mighty wayward]

Hi,

I've done a couple of things which hopefully will help. Firstly, I've
installed a new Teradata JDBC driver and as a result I'm not getting those
unsupported method errors. Basically, the stored procedure runs correctly and
drops the user but once again I can not display that returned 'message' output
parameter.

In addition, I've got one of the Teradata DBAs to create a simple stored
procedure for test purposes that concatenates a surname with a first name and
outputs the full name. Again, trying to retun the output parameter gives me a
problem. Code is below:

//******** STORED PROCEDURE ************//

Replace PROCEDURE USERADMIN.test
( IN SurName VarChar(25)
, OUT StmntOut Varchar(500) )
CreateUser:
BEGIN
Declare StmntTxt varchar(500);
Set StmntTxt = SurName || SurName;
Set StmntOut=StmntTxt;
END;


//*********** CFML *************//

<cfscript>
getPageContext().getClass().forName("com.ncr.terad ata.TeraDriver");
objDriver = createObject("java","java.sql.DriverManager") ;
conn = objDriver.getConnection("jdbc:teradata://tdpr", "username", "password");
cs = conn.prepareCall("{ Call USERADMIN.test (?,?) }");//doesn't make any
difference if I use ?,? or hardcoded values as before.
cs.setString(javacast("string", 1), "smith");
types = createObject("java", "java.sql.Types");
cs.registerOutParameter(javacast("string", 2), types.VARCHAR);
rs = cs.executeQuery();
outValue = cs.getString(javacast("string", 2));
cs.close();
conn.close();
</cfscript>

<cfoutput>#outValue#</cfoutput>



This is the result I get when I run the CFML template. It doesn't seem to
make any sense. I've googled it but there aren't any obvious solutions. :-/

//******** Error message **********//

500
com.ncr.teradata.jdbc_3.ifjdbc_4.TeraLocalCallable Statement.setString(Ljava/lang
/String;Ljava/lang/String;)V

com.ncr.teradata.jdbc_3.ifjdbc_4.TeraLocalCallable Statement.setString(Ljava/lang
/String;Ljava/lang/String;)V

Once again, any ideas on how to fix this will be gratefully received.

Thanks, Neil

[mxstu]

I think you just need to tweak a few of the statements. The values you're
passing to the java methods are correct, but the value "types" are wrong (i.e.
you're passing a "string" when you need to be passing an "int").

cs.setString(javacast("string", 1), "smith");
should be:
cs.setString(javacast("int", 1), "smith"); //where 1 is the index of the
stored proc input param

cs.registerOutParameter(javacast("string", 2), types.VARCHAR);
should be:
cs.registerOutParameter(javacast("int", 2), types.VARCHAR); //where 2 is the
index of the proc output param


rs = cs.executeQuery();
should be:
cs.executeUpdate(); //because the proc does not return a resultset

outValue = cs.getString(javacast("string", 2));
should be
outValue = cs.getString(javacast("int", 2)); //where 2 is the index of the
output param

[mighty wayward]

Hurray! Mxstu, I have had a word with her royal higness at Buckingham Palace
and we have agreed for you to be knighted. Thank you very very VERY much for
sorting that out for me.

Cheers and all the best.

A very grateful Neil