| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Insert statement |
« View previous topic :: View next topic » |
| Author |
Message
|
| mayur2378 |
 Posted: Sun Sep 19, 2004 9:57 am Post subject: Insert statement |
 |
|
Apprentice
Joined: 26 May 2004
Posts: 47
|
Hey guys,
I have this problem . Am doing a insert with a passthru...My problem is i have a data comming in as blob ..the statement looks sometin like
SET Environment.Patterns.DBConnSQL='INSERT INTO bulletin (received_dt,create_dt,uuid,tt
,aa,ii,cccc,yygggg,bbb_group
,awips_id,file_name,file_path,src_seq_num
,source_id,chk_sum,datalength,bulletin_data)
VALUES ('||cSQ||''||Cast(Environment.Variables.ReceivedDateTime as Char)||''||cSQ||','||Cast(mydt as Char)||','||cSQ||''||Environment.Variables.UUID ||''||cSQ||'
,'||cSQ||''||Cast(tt as Char)||''||cSQ||','||cSQ||''||Cast(aa as Char)||''||cSQ||'
,'||cSQ||''||Cast(ii as Char)||''||cSQ||','||cSQ||''||Environment.Variables.CCCC||''||cSQ||'
,'||cSQ||''||CAST(Environment.Variables.YYGGgg as char)||''||cSQ||','||cSQ||''||Environment.Variables.BBB||''||cSQ||'
,'||cSQ||''||Cast(Environment.Variables.AWIPSId as Char)||''||cSQ||','||cSQ||''||FileName||''||cSQ||'
,'||cSQ||''||FilePath||''||cSQ||','||Cast(Environment.Variables.SourceSeqNbr as Char)||','||CAST(Environment.Variables.SourceId as Char)||'
,'||Cast(Environment.Variables.Checksum as Char)||','||Cast(Environment.Variables.BulletinLength as Char)||'
,'||Cast(Environment.Variables.Bulletin as char)||')';
set Environment.Patterns.DBConnResults[]
=passthru(Environment.Patterns.DBConnSQL);
but it seems to have a problem with the bulletin part..it throws the followin exception
'[IBM][CLI Driver][DB2/NT] SQL0103N The numeric literal "4142414b33322050414e4320313631313330205041420d0d0a535752414b200d0d0a50" is not valid. SQLSTATE=42604 '
Cant figure out whats wrong..any help would be appreciated
Mayur |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Sun Sep 19, 2004 12:01 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
You need to specify a CCSID on your casts.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| kirani |
| Posted: Sun Sep 19, 2004 12:07 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
How are you setting other local variables? like, cSQ, tt, ii, etc.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| mgk |
| Posted: Sun Sep 19, 2004 1:37 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
Irrespective of the problem you are currently having, this form of passthru is a bad idea. If you are planning of changing the string for each message through (as your example suggests) you will get much better performance if you use parameter markers instead of hard-coding literals instead.
Cheers,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| mayur2378 |
| Posted: Sun Sep 19, 2004 3:57 pm Post subject: |
|
|
Apprentice
Joined: 26 May 2004
Posts: 47
|
The encoding and the ccsid ddnt work.
Kirani, abt the tt,aa,ii----those are all headers which i have caught and seperated out as tt,aa,ii based on te requirement.
Abt the design aspect, i know i didnt do a good job of explainin it. Am implementing error handling so basically the insert statement is stored in the variable and then passed to a compute node in a subflow but would love to know what you meant by parameter markers..
anyways anymore suggestions would help a lot...
thankz
Mayur |
|
| Back to top |
|
|
| kirani |
| Posted: Sun Sep 19, 2004 10:28 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Mayur,
What he is saying is you should pass the variables as parameter to your insert statement. For example,
| Code: |
PASSTHRU('INSERT INTO MyTable(col1, col2, col3) VALUES (?,?,?)',val1, val2, val3);
|
In your case you can construct the string like this.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| 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
|
|
|
|
