| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| WMB6 - problem with DB insert from Java Compute node |
« View previous topic :: View next topic » |
| Author |
Message
|
| Cliff |
 Posted: Tue Oct 18, 2005 6:15 am Post subject: WMB6 - problem with DB insert from Java Compute node |
 |
|
Centurion
Joined: 27 Jun 2001
Posts: 145
Location: Wiltshire
|
Folks,
it seems to be mandatory to use correlation names for the values in an INSERT statement such as this:
MbSQLStatement stmt = createSQLStatement( "DBNAME",
"PASSTHRU('INSERT INTO TABLENAME (TIMESTAMP, BROKER, EXECUTIONGROUP, MESSAGEFLOW, QMGR, QUEUE, MSGID, CORRELID, DIAGNOSTIC_DATA) VALUES (?,?,?,?,?,?,?,?,?)'," +
"Environment.FLOWENV.DateTime, _brokerName, _executionGroupName, _messageFlowName, _queueManagerName, _queueName, _msgId, _correlId, 'DIAGNOSTIC STRING');");
This chokes on _brokerName, a String variable set earlier on. Now either I've missed a trick somewhere or I'll have to stash every variable I want to use in a SQL statement in the Environment (or other valid tree).
Can anyone enlighten me? The manuals can't.
Thanks -
Cliff |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Tue Oct 18, 2005 6:28 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Do you want the value of _brokerName, and etc?
Then should it be more like
| Code: |
MbSQLStatement stmt = createSQLStatement( "DBNAME",
"PASSTHRU('INSERT INTO TABLENAME (TIMESTAMP, BROKER, EXECUTIONGROUP, MESSAGEFLOW, QMGR, QUEUE, MSGID, CORRELID, DIAGNOSTIC_DATA) VALUES (?,?,?,?,?,?,?,?,?)'," +
"Environment.FLOWENV.DateTime,"+ _brokerName+","+ _executionGroupName+","+_messageFlowName+","+ _queueManagerName+","+_queueName+","+_msgId+","+ _correlId+","+"'DIAGNOSTIC STRING');");
|
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Cliff |
| Posted: Wed Oct 19, 2005 12:13 am Post subject: |
|
|
Centurion
Joined: 27 Jun 2001
Posts: 145
Location: Wiltshire
|
Thanks, Jeff.
What you suggest is on the right lines but I'm still having fun getting the syntax right - the string containing the comma-separated variables seems to be being interpreted as a list! I get "Syntax error : ''[''. " at runtime - I'll keep bashing away though and post the result here when I finally get it to work.
Cheers -
Cliff |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed Oct 19, 2005 3:35 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Try building the string outside of the createSQLStatement procedure, and pass it as a parameter explictly.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Cliff |
| Posted: Mon Oct 24, 2005 2:27 am Post subject: |
|
|
Centurion
Joined: 27 Jun 2001
Posts: 145
Location: Wiltshire
|
Jeff,
that's exactly what I'm trying. Still no luck though <sigh>.
I have some other work to do now, but I'll return to this whenever I can.
Cheers -
Cliff |
|
| Back to top |
|
|
| neerav |
| Posted: Wed Jul 19, 2006 7:42 am Post subject: |
|
|
Novice
Joined: 31 May 2006
Posts: 22
|
I am also facing problem while inserting into the DB.
Here's the query :
----------------------------------------------------------------------------
String INSERTquery = "INSERT INTO Database.CRMDBA.CRM_BKR_AUD_LOG (tran_id" +
",msg_id," +
"srvc_name_txt," +
" srvc_vrsn_nbr," +
"msg_typ_txt," +
"pyld_fmt_txt," +
"xchg_prtcl_txt," +
"src_lgcl_txt," +
"src_envmt_txt," +
"src_prtcl_txt," +
"trgt_lgcl_txt," +
"trgt_envmt_txt," +
"trgt_prtcl_txt," +
"trgt_addr_txt," +
"clnt_usr_id," +
"msg_pth_txt," +
"aud_mod_txt," +
"msg_pyld_txt," +
"insert_ts," +
"create_dt," +
"modify_dt) " +
"values('" +tranID+ "','" +msgID+ "','" +serviceName+ "'," +serviceNumber+ ",'" +msgType+ "','" + payloadFormat + "','" +exchangeProtocol+ "','" + sourceLogicalName
+ "','" +sourceEnvironment+ "','" +sourceProtocol+ "','" +targetLogicalName+ "','" +targetEnvironment+ "','" +targetProtocol+ "','" +targetAddress
+ "','" +clientUserID+ "','" +msgPath+ "','" +auditModule+ "','" +msgPayload+ "','" +insertTimeStamp+ "','" +createDt+ "','" +modifyDt+ "') ;" ;
MbSQLStatement state = createSQLStatement(dataSourceName,INSERTquery );
----------------------------------------------------------------------------
Here's the exception:
----------------------------------------
----------------------------------------
2006-07-19 11:26:30,592 (main_JavaCompute.java:134) DEBUG - state.getStatement() : INSERT INTO Database.CRMDBA.CRM_BKR_AUD_LOG (tran_id,msg_id,srvc_name_txt, srvc_vrsn_nbr,msg_typ_txt,pyld_fmt_txt,xchg_prtcl_txt,src_lgcl_txt,src_envmt_txt,src_prtcl_txt,trgt_lgcl_txt,trgt_envmt_txt,trgt_prtcl_txt,trgt_addr_txt,clnt_usr_id,msg_pth_txt,aud_mod_txt,msg_pyld_txt,insert_ts,create_dt,modify_dt) values('AX85070D1338313596','mymsgID','myServiceName',1,'mymsgType','mypayloadFormat','myexchangeProtocol','mysourceLogicalName','mysrcEnv','mysrcProtl','mytargLogNme','mytargEnv','mytargProtl','mytargAddr','myclntUsrID','mymsgPath','BROKER','<mymsgPayload>hello</mymsgPayload>','12-JUL-1234','12-JUL-1234','12-JUL-1234') ;
2006-07-19 11:26:30,592 (main_JavaCompute.java:142) DEBUG -
2006-07-19 11:26:30,592 (main_JavaCompute.java:147) INFO - Executing INSERTquery
2006-07-19 11:26:30,795 (main_JavaCompute.java:164) ERROR - Exception caught thru MbException: <com.ibm.broker.plugin.MbFatalException class:JNI method:SqlStatementGroup::execute source:BIPv600 key:2488 >
2006-07-19 11:26:30,795 (main_JavaCompute.java:165) ERROR - MbException Trace: Error detected, rethrowing
2006-07-19 11:26:30,795 (main_JavaCompute.java:166) ERROR -
2006-07-19 11:26:30,795 (main_JavaCompute.java:167) ERROR - ******************************************************
May i know what the problem is? I have tried to execute this query directly on the DB client (removing Database. and table alias) and it worked.
Reg,
N |
|
| 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
|
|
|
|
