Invalid parameter binding(s)--Please Help!

[Russell Ray]

I am real new to SQL Server. We are using SQL Server 2000.

My objective is to get a list of all store procedures for specific database. To accomplish this, I programmatically create a connection to the 'master' database where the sp resides, create a callablestatement for 'sp_stored_procedures' ({call sp_stored_procedures(?,?,?)}). I am passing in null, null, and the database name. When I execute the query, I get the below error:

   java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Invalid parameter binding(s).
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)

When I made the connection to the master database, I used one of the existing users from the master database.

I looked at the store procedure and verified the sp has 3 input parameter. The first two can be null.

I am not sure why I can not run this sp. I am wondering if we have the database setup properly. Any help or suggestions would be well worth it at this point.


Thanks in advance for reading my post.

Russ

[stu derby]

I am passing in null, null, and the database name.

You think you are. Post code, and we'll see.

[Russell Ray]

Here is what I have for this specific area. This is my understanding on how to go about setting up the CallableStatement.

 // Interface from dbUnit
 private IDatabaseConnection con2 = null;
 
   //getting a connection
  Connection sysJDBCConnection = DriverManager.getConnection( "jdbc:microsoft:sqlserver://servername:1433;DatabaseName=master;selectMethod=cursor", "user", "password");
  con2=new DatabaseConnection(sysJDBCConnection);
 
 String sp2 = createCall("sp_stored_procedures", 3);
 // what is returned and set for sp2 = {call sp_stored_procedures(?,?,?)}
 call2= con2.getConnection().prepareCall(sp2);
 
 //setting parameters
 call2.setString(0, null);
 call2.setString(1, null);
 call2.setString(2, "dbname");
 ResultSet rs = call2.executeQuery();
 

[stu derby]

 call2.setString(0, null);
 call2.setString(1, null);

A Java null (absence of reference) isn't a database null (absence of value); you're getting the correct error for what you're doing...

Try:

 call2.setNull(0);
 call2.setNull(1);

[Russell Ray]

 
  //call2.setString(0);  << syntax error and will not compile
  //call2.setString(1,); << same here   :banghead:  
  call2.setString(2, "dbname");
 
  //just doing this does not get pass the error either.
  call2.setString(2, "dbname");
 
  

[stu derby]

Originally posted by Russell Ray:

 
  //call2.setString(0);  << syntax error and will not compile
  //call2.setString(1,); << same here    :banghead:   
  call2.setString(2, "dbname");
 
  //just doing this does not get pass the error either.
  call2.setString(2, "dbname");
 
  

Yep, that's true...

Reread my earlier response, that's not what I suggested.

[Russell Ray]

Whoops........

Okay I when back and re-tried it.

It's the same issue just different method.

call2.setNull(0); << Syntax error
call2.setNull(1); << same
call2.setString(2, "dbName");


// came problem
call2.setNull(0, Types.VARCHAR); <<Invalid parameter binding
call2.setNull(1, Types.VARCHAR);
call2.setString(2, "dbName");


I checked on the types too.

Based on this link :SQL Server TO JDBC Types

NVARCHAR = VARCHAR
SYSNAME = VARCHAR

[vishwanath nadimpally]

Originally posted by Russell Ray:
Here is what I have for this specific area. This is my understanding on how to go about setting up the CallableStatement.

 // Interface from dbUnit
 private IDatabaseConnection con2 = null;
 
   //getting a connection
  Connection sysJDBCConnection = DriverManager.getConnection( "jdbc:microsoft:sqlserver://servername:1433;DatabaseName=master;selectMethod=cursor", "user", "password");
  con2=new DatabaseConnection(sysJDBCConnection);
 
 String sp2 = createCall("sp_stored_procedures", 3);
 // what is returned and set for sp2 = {call sp_stored_procedures(?,?,?)}
 call2= con2.getConnection().prepareCall(sp2);
 
 //setting parameters
 call2.setString(0, null);
 call2.setString(1, null);
 call2.setString(2, "dbname");
 ResultSet rs = call2.executeQuery();
 

why do you want to pass 'null' parameters? If you don't want those parameters just get rid of the first two parameters and define them locally in the stored proc. Just have the 'dbname' as a parameter.

I know this doesn't answer your question, but I think this is a better way of doing this is either not have those parameters or have int params where you can pass '0' and handle the rest in the stored proc. Ofcourse, this applies only if you are allowed to change the signature of the stored proc.

[Russell Ray]

yes, there is a better way without going through all this pain....


simple select statement will get the information

SELECT name FROM sysobjects where type='P'

I did not uncover this until just recently.

thanks for everyone's help....