The "JDBC Call Procedure" activity fails while calling a stored procedure in MSSQL DB if a local variable is used in the SELECT query to perform a result set retrieval operation.
Article ID: KB0094014
Updated On:
| Products | Versions |
|---|---|
| TIBCO ActiveMatrix BusinessWorks | - |
| Not Applicable | - |
Description
Description:
There is an issue with a "JDBC Call Procedure" activity throwing a "java.lang.NumberFormatException:" exception with a MSSQL stored procedure in the event the stored procedure uses s local variable in the SELECT query to perform the result set retrieval operation. A sample stored procedure is shown below.
++++
CREATE PROCEDURE [dbo].[GetTestRecord]
@Id int
AS
BEGIN
DECLARE @Vstring nvarchar(50)
DECLARE @Vinteger integer
DECLARE @VdateTime datetime
SELECT @Vstring = vString, @Vinteger = vInteger, @VdateTime = vDatetime
FROM dbo.SpTestTable
WHERE @Id = id
SELECT @Vstring, @Vinteger, @VdateTime
END
++++
Symptoms:
The following exception will be logged on the console in Studio.
+++++++++++++++++++++
11:16:08.189 ERROR [bwEngThread:In-Memory Process Worker-2] com.tibco.bw.core - TIBCO-BW-CORE-500050: The BW process [testproblems.TestProcess] instance faulted, JobId [bw0a100], ProcessInstanceId [bw0a100], ParentProcessInstanceId [-], Module [TestProblems:1.0.0.qualifier], Application [TestProblems.application:1.0].
<CausedBy> TIBCO-BW-CORE-500051: Activity [JDBCCallProcedure] fault.
<CausedBy> com.tibco.bw.palette.jdbc.runtime.faults.JDBCSQLException: SQL Exception occurred (SQL STATE = null) - detailMessage - An error occurred while converting the nvarchar value to JDBC data type INTEGER.-{ActivityName=JDBCCallProcedure, ProcessName=testproblems.TestProcess, ModuleName=TestProblems}
<CausedBy> com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred while converting the nvarchar value to JDBC data type INTEGER.
<CausedBy> java.lang.NumberFormatException: For input string: "string"
+++++++++++++++++++++
+++++++++++++++++++++
11:19:09.434 ERROR [bwEngThread:In-Memory Process Worker-4] com.tibco.bw.core - TIBCO-BW-CORE-500050: The BW process [testproblems.TestProcess] instance faulted, JobId [bw0a101], ProcessInstanceId [bw0a101], ParentProcessInstanceId [-], Module [TestProblems:1.0.0.qualifier], Application [TestProblems.application:1.0].
<CausedBy> TIBCO-BW-CORE-500051: Activity [JDBCCallProcedure] fault.
<CausedBy> com.tibco.bw.palette.jdbc.runtime.faults.JDBCSQLException: SQL Exception occurred (SQL STATE = null) - detailMessage - An error occurred while converting the nvarchar value to JDBC data type TIMESTAMP.-{ActivityName=JDBCCallProcedure, ProcessName=testproblems.TestProcess, ModuleName=TestProblems}
<CausedBy> com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred while converting the nvarchar value to JDBC data type TIMESTAMP.
<CausedBy> java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
+++++++++++++++++++++
Cause:
A local variable is used in the SELECT query to perform the result set retrieval operation.
Resolution
Modify the SP as shown below to use an alias for the column names.
+++++++++++++++++++++
CREATE PROCEDURE [dbo].[GetTestRecord]
@Id int
AS
BEGIN
DECLARE @Vstring nvarchar(50)
DECLARE @Vinteger integer
DECLARE @VdateTime datetime
SELECT @Vstring = vString, @Vinteger = vInteger, @VdateTime = vDatetime
FROM dbo.SpTestTable
WHERE @Id = id
SELECT @Vstring {color:red}as vstring{color}, @Vinteger {color:red}as vinteger{color}, @VdateTime {color:red}as vdatetime{color}
END
+++++++++++++++++++++
Issue/Introduction
The "JDBC Call Procedure" activity fails while calling a stored procedure in MSSQL DB if a local variable is used in the SELECT query to perform a result set retrieval operation.
Feedback
Yes
No
Powered by
