| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| WBIA JDBC Adaptor 2.6.8 issue - MS SQL 2005 Server |
« View previous topic :: View next topic » |
| Author |
Message
|
| VivekMeshram |
 Posted: Sun Apr 15, 2012 11:46 pm Post subject: WBIA JDBC Adaptor 2.6.8 issue - MS SQL 2005 Server |
 |
|
Voyager
Joined: 25 Mar 2002
Posts: 83
|
Dear Folks,
I am currently encountering issue with IBM WBIA JDBC version 2.6.8 to insert BO in to MS SQL Server 2005 database (MSSQLServer).
Please see the following requirment scenario:
Source Application --> WBI MB --> JDBC Adapter -->Target System (MQSQL 2005 database)
SOFTWARE: JDBC Adaptor 2.6.8, WBIA Framework version:2.6.0.12
: MS SQL Server 2005 (Database)
: WMQ 6+
: WBI MB 6+
: MQ SQL Server 2005 (Driver Version: Microsoft JDBC Driver 4.0 for SQL Server) Driver Downloaded URL: http://www.microsoft.com/download/en/details.aspx?id=11774
The message broker transforms the data into BO and routes to JDBC Adaptors Request Queue to execute insertion into MS SQL Database table and end up with the following exception:
Time: 2012/04/13 09:46:51.105] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread1 (#1441551852)] [Type: Trace] [Mesg: :[Type: Trace] [MsgID: 90019] [Mesg: Exiting releaseConnection] ]
[Time: 2012/04/13 09:46:51.136] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread1 (#1441551852)] [Type: Trace] [Mesg: :
com.crossworlds.cwconnectorapi.exceptions.VerbProcessingFailedException: [Type: Error] [MsgID: 37004] [Mesg: Execution of Prepared Update Statement Failed : com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.]
at com.crossworlds.connectors.JDBC.JDBCBOhandler.doVerbFor(JDBCBOhandler.java:1593)
at com.crossworlds.cwconnectorapi.CWConnectorBOHandler.doVerbFor(CWConnectorBOHandler.java:127)
at CxCommon.BusinessObject.doVerbFor(BusinessObject.java:2179)
at AppSide_Connector.BusObjJavaInterface.DoVerbFor(BusObjJavaInterface.java:255)
at AppSide_Connector.AppCalls.DoVerbFor(AppCalls.java:266)
at AppSide_Connector.AgentBusinessObjectManager.DoVerbFor(AgentBusinessObjectManager.java:1777)
at CxCommon.Messaging.ClientPacketSyncDrvFormatter.receiverCallback(ClientPacketSyncDrvFormatter.java:1567)
at CxCommon.Messaging.jms.Receiver.processBO(Receiver.java:991)
at CxCommon.Messaging.jms.Receiver.processMsg(Receiver.java:572)
]
[Time: 2012/04/13 09:46:51.136] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread1 (#1441551852)] [Type: Trace] [Mesg: :[9543] DoVerbFor cookie=ConsumeSync rc=-1 rtnObj.status=-1 rtnObj.message=[Type: Error] [MsgID: 37004] [Mesg: Execution of Prepared Update Statement Failed : com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.] ]
[Time: 2012/04/13 09:46:51.136] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread1 (#1441551852)] [Type: Trace] [Mesg: :Receiver.processBO: Status returned from receiverCallback : -1]
I have written a java code to use the MSSQL Driver "com.microsoft.sqlserver.jdbc.SQLServerDriver" and its works very well, we see the row in table with the following exception:
Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:814)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:642)
at TEST.main(TEST.java:22)
I feel like this is something to do with MS SQL driver but not too sure what is causing this issue.
Much apprecaite, if you have any suggestions or come across such a issue while implemeting WBIA JDBC adaptor to connect to MS SQL.
Thanks,
Vivek S Meshram. |
|
| Back to top |
|
 |
| mqsiuser |
| Posted: Mon Apr 16, 2012 1:26 am Post subject: Re: WBIA JDBC Adaptor 2.6.8 issue - MS SQL 2005 Server |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
| VivekMeshram wrote: |
| IBM WBIA JDBC version 2.6.8 to insert BO in to MS SQL Server 2005 database (MSSQLServer). |
Are you sure that Microsoft supports JDBC  ?... do other statements (e.g. SELECT) work ?
| VivekMeshram wrote: |
| Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update. |
I guess, that on an update statement there should be no result set returning... (you have a result set "a couple of rows" with a SELECT but not on an UPDATE ! ).
Look a the result set that was returned, probably it contains error messages.
Also: Make sure that your insert statement works with MS SQL Developer (the DB Tool for SQL-Server).
Reduce the INSERT until it works... e.g. (just for troubleshooting) insert into a single table (not a couple of (connected) tables)
Also make sure SELECT works first (before going on with DELETE/UPDATE/INSERT)
_________________
Just use REFERENCEs |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Apr 16, 2012 1:50 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Abandon WBIA.
Rewrite this in Message Broker. |
|
| Back to top |
|
|
| VivekMeshram |
| Posted: Mon Apr 16, 2012 2:55 am Post subject: |
|
|
Voyager
Joined: 25 Mar 2002
Posts: 83
|
Thanks for the response!
Yes. IBM JDBC adaptor version 2.6x support to connect to MS SQL SERVER 2005 database.
some more trace is below.
[Time: 2012/04/13 10:49:15.483] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :insert into CUSTOMER(ORDER_NUMBER,INSERT_DATE_TIME,CUSTOMER_NUMBER,ORDER_STATUS,CUSTOMER_FIRST_NAME,CUSTOMER_LAST_NAME,DATE_OF_BIRTH,CUSTOMER_HOME_PHONE_NUMBER,CUSTOMER_MOBILE_PHONE_NUMBER,CUSTOMER_WORK_PHONE_NUMBER,CUSTOMER_PRIORITY,CUSTOMER_DISCOUNT_NUMBER,CUSTOMER_EMAIL,CUSTOMER_EMAIL_FORMAT,STAFF_DISCOUNT_NUMBER,BUSINESS_NAME,SERVICE_TYPE,STORE_NUMBER) values(UniqID,13/04/2012 10:49:15 AM,String,N,Jo,Toh,01/01/1968 12:00:00 AM,String,String,String,1,122211,String,html,555555,Web Design ,HD,3001)]
[Time: 2012/04/13 10:49:15.546] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :[Type: Trace] [MsgID: 37016] [Mesg: Values that are being set on the PreparedStatement:] ]
[Time: 2012/04/13 10:49:15.546] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :UniqID]
[Time: 2012/04/13 10:49:15.702] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :13/04/2012 10:49:15 AM]
[Time: 2012/04/13 10:49:15.702] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :String]
[Time: 2012/04/13 10:49:15.718] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :N]
[Time: 2012/04/13 10:49:15.718] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :Jo]
[Time: 2012/04/13 10:49:15.718] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :Toh]
[Time: 2012/04/13 10:49:15.718] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :01/01/1968 12:00:00 AM]
[Time: 2012/04/13 10:49:15.718] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :String]
[Time: 2012/04/13 10:49:15.718] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :String]
[Time: 2012/04/13 10:49:15.718] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :String]
[Time: 2012/04/13 10:49:15.718] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :1]
[Time: 2012/04/13 10:49:15.718] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :122211]
[Time: 2012/04/13 10:49:15.718] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :String]
[Time: 2012/04/13 10:49:15.718] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :html]
[Time: 2012/04/13 10:49:15.718] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :555555]
[Time: 2012/04/13 10:49:15.718] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :Web Design ]
[Time: 2012/04/13 10:49:15.718] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :HD]
[Time: 2012/04/13 10:49:15.718] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :3001]
[Time: 2012/04/13 10:49:15.780] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :<StartHeader>
<Version = 3.0>
<EndHeader>
<StartBO:BO_CUSTOMER>
BusinessObject = BO_CUSTOMER
Verb = Create
Locale = en_US
ORDERNUMBER = UniqID
INSERTDATETIME = 13/04/2012 10:49:15 AM
CUSTOMERNUMBER = String
ORDERSTATUS = N
CUSTOMERFIRSTNAME = Jo
CUSTOMERLASTNAME = Toh
DATEOFBIRTH = 01/01/1968 12:00:00 AM
CUSTOMERHOMEPHONENUMBER = String
CUSTOMERMOBILEPHONENUMBER = String
CUSTOMERWORKPHONENUMBER = String
CUSTOMERPRIORITY = 1
CUSTOMERDISCOUNTNUMBER = 122211
CUSTOMEREMAIL = String
CUSTOMEREMAILFORMAT = html
STAFFDISCOUNTNUMBER = 555555
BUSINESSNAME = Web Design
SERVICETYPE = HD
STORENUMBER = 3001
ObjectEventId = CxIgnore
<EndBO:BO_CUSTOMER>]
[Time: 2012/04/13 10:49:15.780] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :[Type: Trace] [MsgID: 90018] [Mesg: Entering testDBStatus] ]
[Time: 2012/04/13 10:49:15.780] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :[Type: Trace] [MsgID: 35960] [Mesg: Executing pingQuery property for making Agent state Active - Reason : connection failure] ]
[Time: 2012/04/13 10:49:15.780] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :[Type: Trace] [MsgID: 35961] [Mesg: Ping Query : select 1] ]
[Time: 2012/04/13 10:49:15.889] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :[Type: Trace] [MsgID: 90019] [Mesg: Exiting testDBStatus] ]
[Time: 2012/04/13 10:49:15.889] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :[Type: Trace] [MsgID: 90018] [Mesg: Entering releaseConnection] ]
[Time: 2012/04/13 10:49:15.889] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :[Type: Trace] [MsgID: 90019] [Mesg: Exiting releaseConnection] ]
[Time: 2012/04/13 10:49:15.889] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :
com.crossworlds.cwconnectorapi.exceptions.VerbProcessingFailedException: [Type: Error] [MsgID: 37004] [Mesg: Execution of Prepared Update Statement Failed : com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.]
at com.crossworlds.connectors.JDBC.JDBCBOhandler.doVerbFor(JDBCBOhandler.java:1593)
at com.crossworlds.cwconnectorapi.CWConnectorBOHandler.doVerbFor(CWConnectorBOHandler.java:127)
at CxCommon.BusinessObject.doVerbFor(BusinessObject.java:2179)
at AppSide_Connector.BusObjJavaInterface.DoVerbFor(BusObjJavaInterface.java:255)
at AppSide_Connector.AppCalls.DoVerbFor(AppCalls.java:266)
at AppSide_Connector.AgentBusinessObjectManager.DoVerbFor(AgentBusinessObjectManager.java:1777)
at CxCommon.Messaging.ClientPacketSyncDrvFormatter.receiverCallback(ClientPacketSyncDrvFormatter.java:1567)
at CxCommon.Messaging.jms.Receiver.processBO(Receiver.java:991)
at CxCommon.Messaging.jms.Receiver.processMsg(Receiver.java:572)
]
[Time: 2012/04/13 10:49:15.889] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :[9543] DoVerbFor cookie=ConsumeSync rc=-1 rtnObj.status=-1 rtnObj.message=[Type: Error] [MsgID: 37004] [Mesg: Execution of Prepared Update Statement Failed : com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.] ]
[Time: 2012/04/13 10:49:15.889] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :Receiver.processBO: Status returned from receiverCallback : -1]
[Time: 2012/04/13 10:49:15.889] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :In jms.Receiver.processBO(msg), syncDrvCallback.receiverCallback(boMsg) returns a status < 0, indicating a failure has happened in the callback function.]
[Time: 2012/04/13 10:49:15.936] [System: ConnectorAgent] [SS: CN_COPS_JDBCConnector] [Thread: CN.COPS.JDBC.REQUESTJmsThreadPool.thread6 (#459021148)] [Type: Trace] [Mesg: :Sending fault message:
JMS Message class: jms_text
JMSType: mcd://MRM/MS_EAI_ASBO_ORDER_COPS/BO_CUSTOMER?format=CwXML
JMSDeliveryMode: 2
JMSExpiration: 0
JMSPriority: 0
JMSMessageID: ID:414d512056534d514d202020202020201a4b874f20003e10
JMSTimestamp: 1334278154960
JMSCorrelationID:ID:414d512056534d514d202020202020201a4b874f20006510
JMSDestination: null
JMSReplyTo: null
JMSRedelivered: false
Status:-1
status:-1
resubmitTo:CN.COPS.JDBC.REQUEST
keys: ORDERNUMBER=UniqID INSERTDATETIME=13/04/2012 10:49:15 AM CUSTOMERNUMBER=String
description:A failure has happened in the callback function.
Description:[Type: Error] [MsgID: 37004] [Mesg: Execution of Prepared Update Statement Failed : com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.] A failure has happened in the callback function.
<?xml version="1.0" encoding="UTF-8"?>
<?CWRSD status="-1" description="[Type: Error] [MsgID: 37004 |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Mon Apr 16, 2012 3:23 am Post subject: |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
Make sure you delete the entry in the DB-row (select/delete it based on UniqId).
Probably you want to switch from "Create" to "Create or Update"... if possible, though I can't remember if thats possible or what the name of it is.
Also, check which colums can be null: Put in null there (just for testing, just to make it work).
Does your insert/update work in MS-SQL-Developer !?! ... make sure it does first (before going on with the adapter framework).
_________________
Just use REFERENCEs |
|
| Back to top |
|
|
| VivekMeshram |
| Posted: Mon Apr 16, 2012 12:26 pm Post subject: |
|
|
Voyager
Joined: 25 Mar 2002
Posts: 83
|
Hi mqsiuser,
we have deleted all the rows from table before inserting. As mentioned in my frist post, we have also developed a sample java code and it does insert using the same MQ SQL Server database driver which is the same been use in the Connector configuration file.
The BO ( Business Object has the Verb as Create and Update)
We have also used the same insert string which also work using MQ SQL Developer.
Thanks! |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Tue Apr 17, 2012 1:10 am Post subject: |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
Did you ever successfully use UPDATE with your version of MS-SQL-Server... so do you think you just don't make it (work) (this time) or... do you think that you have discovered a product limit/bug ?
The WBI-Adapter framework has a lot of fields to configure and you can also just add and remove some... it is largly untyped with a syntax which is (often) (to me) not clear... then I'd ask a collegue (from IBM), they can help. I suggest you ask IBM 
Or do as mqjeff suggests.
_________________
Just use REFERENCEs |
|
| Back to top |
|
|
| VivekMeshram |
| Posted: Tue Apr 17, 2012 1:42 am Post subject: |
|
|
Voyager
Joined: 25 Mar 2002
Posts: 83
|
Thanks, yes im aware of which field to configure as we use many jdbc adaptors to connect oracle db. This is the first time, im dealing with MSSQL database.
Anyway,
We have figured out the issues and testing is in progress..we have also opened PMR with IBM.
Thanks for all eesponse. |
|
| Back to top |
|
|
| qwertyuiop08 |
| Posted: Wed Apr 18, 2012 2:36 am Post subject: |
|
|
Newbie
Joined: 18 Apr 2012
Posts: 1
|
Hi Vivek, Even we are facing the same issue. Can you pls tell us how you were able to solve this issue.
Thanks in advance.
Ajay |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
