Solved

Invalid parameter binding(s) - calling a SQL Server 2000 stored procedure via JDBC

Posted on 2004-04-30
20
8,160 Views
Last Modified: 2011-08-18
Hi

Please help, the small amount of hair I have remaining is being torn out!!!

I am trying to call a stored procedure in SQL Server 2000 using JDBC but keep getting the following message from JDBC.
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Invalid parameter binding(s).

The java code looks like this:

datasource=JDBCHelper.getDataSource();
connection=datasource.getConnection();

callableStatement=connection.prepareCall("{call ? = spSimpleDesk(?,?,?,?,?,?,?,?,?)}");

// register Output Parameter
callableStatement.registerOutParameter(1,Types.INTEGER);
callableStatement.setInt(2,obj.getId());
callableStatement.setString( 3,obj.getBuilding());
callableStatement.setString( 4,obj.getFloor());
callableStatement.setString( 5,obj.getBlock());
callableStatement.setString( 6,obj.getDeskNum());
callableStatement.setString( 7,obj.getComments());
callableStatement.setString( 8,obj.getLastUpdatedBy());
callableStatement.setDate(9,(java.sql.Date)obj.getLastUpdated());

callableStatement.execute();


The stored procedure looks like this:
CREATE PROCEDURE spSimpleDesk

@returnValue int OUTPUT,
@Id int,
@Building char(5),
@Floor char(3),
@Block char(3),
@DeskNum char(3),
@Comments varchar(255),
@LastUpdatedBy varchar(50),
@LastUpdated smalldatetime

AS

      INSERT INTO      tblDesk
       (
      Building,
      Floor,
      Block,
      DeskNum,
      Comments,
      LastUpdatedBy,
      LastUpdated
      )
      VALUES
      (      
       @Building,
       @Floor,
       @Block,
       @DeskNum,
       @Comments,
       @LastUpdatedBy,
       @LastUpdated
      )
      
SET @returnValue=@@IDENTITY
GO

I have tried this without the OUPUT parameter and it works fine. As soon as I attempt to retrieve the return parameter I get the error message.

Can anyone help me out with this?

Thanks

SteadyEddy
0
Comment
Question by:SteadyEddy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 5
20 Comments
 
LVL 13

Expert Comment

by:copyPasteGhost
ID: 10960186
I think it's a bug...

http://mail.opencms.org/pipermail/opencms-dev/2003q2/004803.html

check this out,
Ghost
0
 

Author Comment

by:SteadyEddy
ID: 10960619
Looks like the guy who was getting the same error message as me didn't arrive at any solution. I can't believe that there aren't any JDBC users successfully calling a SQL 2000 stored procedure.
0
 
LVL 13

Expert Comment

by:copyPasteGhost
ID: 10960880
doing this..

  Delete the following SQl statement in
com\opencms\file\mssql\query.properties will help you
  1)C_RESOURCES_GET_FILESINFOLDER
  2)C_RESOURCES_GET_SUBFOLDER

didn't help?
Ghost
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:SteadyEddy
ID: 10961052
I don't have this properties file - I'm not using openCms - I think that file is part of the openCms system.
0
 
LVL 13

Expert Comment

by:copyPasteGhost
ID: 10961112
oh well it will be hard to edit if you don't have it :)
well
are you sure the problem is not with your stored procedures?
Ghost
0
 
LVL 13

Expert Comment

by:copyPasteGhost
ID: 10961990
This means you are trying to pass invalid parameter types.
Make sure the datatypes are the same as the datatypes in your  CREATE PROCEDURE

For more info on Types see: http://java.sun.com/j2se/1.3/docs/api/java/sql/Types.html
For more info on CallableStatement : http://java.sun.com/j2se/1.3/docs/api/java/sql/CallableStatement.html

Cheers,
Ghost
0
 
LVL 92

Expert Comment

by:objects
ID: 10964656
> callableStatement=connection.prepareCall("{call ? = spSimpleDesk(?,?,?,?,?,?,?,?,?)}"

that should be:

callableStatement=connection.prepareCall("{? = call spSimpleDesk(?,?,?,?,?,?,?,?,?)}"

you also have 10 parameters in that call, and you only set 9 of them. Which also doesn't appear to match your sp.
0
 

Author Comment

by:SteadyEddy
ID: 10984980
I've changed the call to callableStatement=connection.prepareCall("{call ? = spSimpleDesk(?,?,?,?,?,?,?,?)}");
and itlooks like this has moved the problem on! Still any progress is welcome. I'm now getting the message
      
[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Error converting data type nvarchar to int

I've checked all the parameters of the stored proc and table design and I am not using any nvarchars

any ideas?
0
 
LVL 13

Expert Comment

by:copyPasteGhost
ID: 10985964
I think you are trying to store a variable of type string into a java int..
don't forget that you have to parse String values into ints before you can store them

String myString = "4";
int myInt = Integer.parseInt(myString);

Cheers,
Ghost
0
 
LVL 92

Expert Comment

by:objects
ID: 10992076
what types are all the parameters you are passing?
0
 

Author Comment

by:SteadyEddy
ID: 10994362
OK what I've done now it bring everything down to the bare bones so the table is tblSimple

CREATE TABLE [dbo].[tblSimple] (
      [SimpleId] [int] IDENTITY (1, 1) NOT NULL ,
      [SimpleText] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

The stored proc is:

CREATE PROCEDURE spSimpleInsert

@returnValue int OUTPUT,
@simpleText varchar(50)

AS

INSERT INTO tblSimple (SimpleText) values (@simpleText)

SET @returnValue=@@IDENTITY
GO

The JDBC Code is:

    callableStatement.registerOutParameter(1,Types.INTEGER);
    callableStatement.setString(2,"simple as pie " + new Date().toString());
    callableStatement.execute();


Unfortunately the error is:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Error converting data type nvarchar to int.

I am not using nvarchar anywhere - could it be something to do with the COLLATE SQL_Latin clause in the CREATE TABLE?


0
 
LVL 92

Expert Comment

by:objects
ID: 10994591
what sql are u using?
0
 

Author Comment

by:SteadyEddy
ID: 10994594
MS SQL Server 2000
0
 

Author Comment

by:SteadyEddy
ID: 10994600
Sorry posted that before thinking - the sql call is

callableStatement=connection.prepareCall("{call ? = spSimpleInsert(?)}");
0
 
LVL 92

Expert Comment

by:objects
ID: 11000580
did u try my earlier suggestion?

callableStatement=connection.prepareCall("{? = call spSimpleInsert(?)}");
0
 

Author Comment

by:SteadyEddy
ID: 11003625
Yes, tried this

callableStatement=connection.prepareCall("{? = call spSimpleInsert(?)}");
callableStatement.registerOutParameter(1,Types.INTEGER);
callableStatement.setString(2,"simple as pie " + new Date().toString());
callableStatement.execute();


unfortunately no change to the resulting error it still comes back with

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Error converting data type nvarchar to int.
0
 
LVL 92

Accepted Solution

by:
objects earned 500 total points
ID: 11003664
actually looking at your sp it should perhaps be:

callableStatement=connection.prepareCall("{call spSimpleInsert(?, ?)}");
0
 

Author Comment

by:SteadyEddy
ID: 11003875
Yee Haa - this works but the syntax is completely different to the examples where you have to use ? = for output parameters. Why is this? And why does the error message talk about nvarchars ???

Thanks for you help anyway.
0
 
LVL 92

Expert Comment

by:objects
ID: 11010359
Your sp doesn't return a value, it instead has an output parameter. They are different things.

http://www.objects.com.au/staff/mick

0
 

Author Comment

by:SteadyEddy
ID: 11012866
Objects - I appreciate what you're saying but the ? = syntax works with OUTPUT parameters in our Sybase system.

Ta
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
This video teaches viewers about errors in exception handling.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question