passing String to stored procedure

[Andrew Filev]

Hello.

Please help, I should use stored procedures from java code, but get errors.
Below is sample code and exception.

import java.sql.*;

class sampleconnection
{
public static void main(String[] args) throws SQLException
{
Connection con=null;
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
}catch(Exception ex){}

try{
con=DriverManager.getConnection("jdbc:odbc:WebDEMO","sa","");
}catch(SQLException ex){}
//DatabaseMetaData dbmd = con.getMetaData();
//System.out.println(dbmd.supportsStoredProcedures());
CallableStatement cstmt = con.prepareCall("{ call sp_TWSUser_INSERT( ?
? ) }" );
cstmt.setString(1,"password");
cstmt.registerOutParameter(2, Types.INTEGER);
int iUC=cstmt.executeUpdate();
System.out.println("procedure returns "+iUC+"\nretval is
"+cstmt.getInt(1));
}
}

Here is outut:

Exception in thread "main" java.sql.SQLException: [Microsoft][ODBC SQL
Server Dr
iver]Invalid character value for cast specification
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6031)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:6188)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:2533)
at
sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedState
ment.java:217)
at
sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(JdbcOdbcPrepare
dStatement.java:139)
at
sampleconnection.main(E:/projects/WebDEMO/WEB-INF/ru/spb/star/TWS/sam
pleconection.java:23)

Would be very grateful on any help.
I'm beating with this problem third our and can't get result.
The server is MS SQL 7.0.
Input parameter of stored procedure should be any castable to varchar(50)
and nvarchar(50).

Here's sample procedure
/*******************************/
CREATE PROCEDURE sp_TWSUser_INSERT(@Password VARBINARY(50),@retval int
output)
AS
SET @retval = 0

BEGIN TRAN

INSERT INTO TWSUser (UserName, UserLogin, Password, UserType)
VALUES ('SAMPLE1444455', 'SAMPLE1444455', 'PASSWORD',
'Manager')

SET @retval = @@IDENTITY

COMMIT TRAN

RETURN (0)

[m...@branchware.de]

Hi Andrew!

> [..]
> CallableStatement cstmt = con.prepareCall

> ("{ call sp_TWSUser_INSERT( ? ? ) }" );

> [..]
Never got this exception, and I'm not sure about this, but does there
not have to a ',' between the two '?'?
Try "{ call sp_TWSUser_INSERT( ?, ? ) }".

HTH,
Manfred.

--
Dipl.-Inf. Manfred Weigl Branchware & Partner GmbH
Tel. : +49 911 27069-46 Rosenpluetstrasse 2
Fax : +49 911 27069-49 90439 Nuernberg
email: m...@branchware.de Germany

Sent via Deja.com
http://www.deja.com/