Solved

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

Posted on 2004-04-30
20
8,064 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
  • 9
  • 6
  • 5
20 Comments
 
LVL 13

Expert Comment

by:copyPasteGhost
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:SteadyEddy
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
> 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
Comment Utility
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
Comment Utility
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
Comment Utility
what types are all the parameters you are passing?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:SteadyEddy
Comment Utility
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
Comment Utility
what sql are u using?
0
 

Author Comment

by:SteadyEddy
Comment Utility
MS SQL Server 2000
0
 

Author Comment

by:SteadyEddy
Comment Utility
Sorry posted that before thinking - the sql call is

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

Expert Comment

by:objects
Comment Utility
did u try my earlier suggestion?

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

Author Comment

by:SteadyEddy
Comment Utility
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
Comment Utility
actually looking at your sp it should perhaps be:

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

Author Comment

by:SteadyEddy
Comment Utility
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
Comment Utility
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
Comment Utility
Objects - I appreciate what you're saying but the ? = syntax works with OUTPUT parameters in our Sybase system.

Ta
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Performance issue while iterating with streams 5 39
countX 22 69
Python Assistance 7 31
GUI builder for Eclipse? 8 7
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…
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now