Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Error converting data type varchar to int

Author  Topic 

russray
Starting Member

27 Posts

Posted - 2006-07-24 : 14:28:04
I have a problem with getting the error code from the callable statement.

I declare position 14 in the callable statement as an INTEGER for the error code. The store procedure runs and comes back, but I receive this error when I try to access the data:


[7/24/06 13:01:17:906 CDT] 7d7d00a2 SystemErr R java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Error converting data type varchar to int.
[7/24/06 13:01:17:906 CDT] 7d7d00a2 SystemErr R at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
[7/24/06 13:01:17:906 CDT] 7d7d00a2 SystemErr R at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
[7/24/06 13:01:17:906 CDT] 7d7d00a2 SystemErr R at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
[7/24/06 13:01:17:906 CDT] 7d7d00a2 SystemErr R at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
[7/24/06 13:01:17:906 CDT] 7d7d00a2 SystemErr R at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
[7/24/06 13:01:17:906 CDT] 7d7d00a2 SystemErr R at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
[7/24/06 13:01:17:906 CDT] 7d7d00a2 SystemErr R at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
[7/24/06 13:01:17:906 CDT] 7d7d00a2 SystemErr R at com.microsoft.jdbc.base.BaseStatement.getNextResultType(Unknown Source)
[7/24/06 13:01:17:906 CDT] 7d7d00a2 SystemErr R at com.microsoft.jdbc.base.BaseCallableStatement.getAndValidateOutParameter(Unknown Source)
[7/24/06 13:01:17:906 CDT] 7d7d00a2 SystemErr R at com.microsoft.jdbc.base.BaseCallableStatement.getString(Unknown Source)
[7/24/06 13:01:17:906 CDT] 7d7d00a2 SystemErr R at com.microsoft.jdbcx.base.BaseCallableStatementWrapper.getString(Unknown Source)

Here is the source code for setting up the CallableStatement. All the other parameters are Strings or floats.


call.registerOutParameter(14, java.sql.Types.INTEGER);

/*Executing SP*/
call.execute();



/*Check ErroCode*/
checkDBErrorCode(
fails here >>>>>>>>> call.getInt(14),
"<firstFail>"
+ call.getString(12)
+ "</firstFail> - "
+ call.getString(13),
CLASS_NAME,methodName);




The Store Procedure inputs looks like this:

[code]
CREATE PROCEDURE BatchQuickProcess
@p_taskids VARCHAR(600),
@p_qpstatus VARCHAR(1),
@p_qpreviewoper CHAR(8),
@p_actdesc CHAR(10),
@p_dptdesc CHAR(10),
@p_tskdesc CHAR(10),
@p_table VARCHAR(1),
@p_defaultwd FLOAT,
@p_defaultwt FLOAT,
@p_wrkbskt VARCHAR(8),
@p_operid VARCHAR(8),
@p_curtskid VARCHAR(11) OUTPUT,
@p_reterror VARCHAR(255) OUTPUT,
@p_retcode INT OUTPUT,
@p_commit CHAR(1) = 'Y'


I am using SQL Server 2000 with JDBC Drivers
Version 2.2.0040, Service Pack 3

Any help or insight would be greatly appreciated.


Russ

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-24 : 14:47:26
Do you have some sample data from your real table? Do you have some DDL? Can you post the query?
Help us help you.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

russray
Starting Member

27 Posts

Posted - 2006-07-24 : 15:14:03
I am kind of stuck here because I can not go into any more depth...please forgive me. I am hoping one can see the statement is being declared properly and sugest some things to look at within the store procedure. I think the code is good.

Thanks for your understanding.
Go to Top of Page

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-24 : 15:18:52
Considering the scarce info...I think you should try extracting the value first, then converting it to an int, and getting that value.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-24 : 15:29:08
quote:

Error converting data type varchar to int



This error could be coming from the code inside the stored procedure and not necessarily when you are calling it. It's impossible for us to help without seeing the code. You could hide your proprietary code by changing table names, column names, etc...

Tara Kizer
Go to Top of Page

russray
Starting Member

27 Posts

Posted - 2006-07-24 : 15:31:35
I declared the specific parameter as an integer in the java code--position 14.

call.registerOutParameter(14, java.sql.Types.INTEGER);

Next I call the data specifically trying to get the value from the callable statement.

call.getInt(14)

This is where I get error in the JAva code. However, when you go look at the declaration of the store procedure, one can see the 14 postion is in fact an int, so everything SHOULD work.

CREATE PROCEDURE BatchQuickProcess
@p_taskids VARCHAR(600),
@p_qpstatus VARCHAR(1),
@p_qpreviewoper CHAR(8),
@p_actdesc CHAR(10),
@p_dptdesc CHAR(10),
@p_tskdesc CHAR(10),
@p_table VARCHAR(1),
@p_defaultwd FLOAT,
@p_defaultwt FLOAT,
@p_wrkbskt VARCHAR(8),
@p_operid VARCHAR(8),
@p_curtskid VARCHAR(11) OUTPUT,
@p_reterror VARCHAR(255) OUTPUT,
@p_retcode INT OUTPUT,
@p_commit CHAR(1) = 'Y'



Any suggestions?

Thank you for reading my post.........
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-24 : 15:46:59
I have no idea if your Java code is correct. Perhaps you should post your Java code on a Java forum. We can only help with the T-SQL side of things, but you haven't posted enough information for us to help with that side.

If you run the stored procedure inside Query Analyzer, passing the same parameters as your application, do you get the error there? If you do, then you know the problem is inside the stored procedure. If you don't, then it's in your Java code.

Tara Kizer
Go to Top of Page

russray
Starting Member

27 Posts

Posted - 2006-07-24 : 16:04:38
thank you...

Yes..... I did run the store procedure with the inputs from Query Analyzer... I all looks good there.......darn.....you didn't have to tell me that.......
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-07-24 : 16:34:17
The issue is probably in the code within your stored procedure, then.

- Jeff
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-24 : 16:38:24
quote:
Originally posted by jsmith8858

The issue is probably in the code within your stored procedure, then.

- Jeff



Huh? It works fine in Query Analyzer, so isn't the problem inside his Java code?

Tara Kizer
Go to Top of Page
   

- Advertisement -