File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Invalid parameter binding(s)--Please Help! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Invalid parameter binding(s)--Please Help!" Watch "Invalid parameter binding(s)--Please Help!" New topic
Author

Invalid parameter binding(s)--Please Help!

Russell Ray
Ranch Hand

Joined: Apr 25, 2005
Posts: 116
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:




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
Ranch Hand

Joined: Dec 15, 2005
Posts: 333

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


You think you are. Post code, and we'll see.
Russell Ray
Ranch Hand

Joined: Apr 25, 2005
Posts: 116
Here is what I have for this specific area. This is my understanding on how to go about setting up the CallableStatement.


stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333


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:
Russell Ray
Ranch Hand

Joined: Apr 25, 2005
Posts: 116
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by Russell Ray:


Yep, that's true...

Reread my earlier response, that's not what I suggested.
Russell Ray
Ranch Hand

Joined: Apr 25, 2005
Posts: 116
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
Ranch Hand

Joined: Jan 25, 2005
Posts: 116
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.




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
Ranch Hand

Joined: Apr 25, 2005
Posts: 116
yes, there is a better way without going through all this pain....


simple select statement will get the information



I did not uncover this until just recently.

thanks for everyone's help....
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Invalid parameter binding(s)--Please Help!