Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8370
  • Last Modified:

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

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
SteadyEddy
Asked:
SteadyEddy
  • 9
  • 6
  • 5
1 Solution
 
copyPasteGhostCommented:
I think it's a bug...

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

check this out,
Ghost
0
 
SteadyEddyAuthor Commented:
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
 
copyPasteGhostCommented:
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
Independent Software Vendors: 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!

 
SteadyEddyAuthor Commented:
I don't have this properties file - I'm not using openCms - I think that file is part of the openCms system.
0
 
copyPasteGhostCommented:
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
 
copyPasteGhostCommented:
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
 
objectsCommented:
> 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
 
SteadyEddyAuthor Commented:
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
 
copyPasteGhostCommented:
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
 
objectsCommented:
what types are all the parameters you are passing?
0
 
SteadyEddyAuthor Commented:
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
 
objectsCommented:
what sql are u using?
0
 
SteadyEddyAuthor Commented:
MS SQL Server 2000
0
 
SteadyEddyAuthor Commented:
Sorry posted that before thinking - the sql call is

callableStatement=connection.prepareCall("{call ? = spSimpleInsert(?)}");
0
 
objectsCommented:
did u try my earlier suggestion?

callableStatement=connection.prepareCall("{? = call spSimpleInsert(?)}");
0
 
SteadyEddyAuthor Commented:
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
 
objectsCommented:
actually looking at your sp it should perhaps be:

callableStatement=connection.prepareCall("{call spSimpleInsert(?, ?)}");
0
 
SteadyEddyAuthor Commented:
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
 
objectsCommented:
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
 
SteadyEddyAuthor Commented:
Objects - I appreciate what you're saying but the ? = syntax works with OUTPUT parameters in our Sybase system.

Ta
0

Featured Post

Independent Software Vendors: 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!

  • 9
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now