| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| PASSTHRU oracle_date question |
« View previous topic :: View next topic » |
| Author |
Message
|
| smallred |
 Posted: Wed Feb 02, 2005 1:41 am Post subject: PASSTHRU oracle_date question |
 |
|
Newbie
Joined: 27 Jan 2005
Posts: 4
Location: china
|
hi all,
my remote db is oracle9 and i need insert a date field.
i do a test about PASSTHRU
test 1
PASSTHRU('INSERT INTO USR_VIP_MANAGER(MANAGERID,NAME,MOBILE,
SEX,CITYID,COUNTYID,DEPT,PHONE,ADDRESS,FAX,EMAIL,FAMILY_PHONE,
FAMILY_ADDRESS,CREATE_TIME,EMP_NO,REMARK,ROLE,OPERDATE)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,
to_date(''' ||
Body.CMSEAI.DETAILS.CREATE_TIME ||
''',''yyyy-mm-dd hh24:mi:ss''),?,?,?,to_date(''' || Body.CMSEAI.DETAILS.OPERDATE ||
''',''yyyy-mm-dd hh24:mi:ss''))',
Body.CMSEAI.DETAILS.MANAGERID,
Body.CMSEAI.DETAILS.NAME,
Body.CMSEAI.DETAILS.MOBILE,
Body.CMSEAI.DETAILS.SEX,
Body.CMSEAI.DETAILS.CITYID,
Body.CMSEAI.DETAILS.COUNTYID,
Body.CMSEAI.DETAILS.DEPT,
Body.CMSEAI.DETAILS.PHONE,
Body.CMSEAI.DETAILS.ADDRESS,
Body.CMSEAI.DETAILS.FAX,
Body.CMSEAI.DETAILS.EMAIL,
Body.CMSEAI.DETAILS.FAMILY_PHONE,
Body.CMSEAI.DETAILS.FAMILY_ADDRESS,
Body.CMSEAI.DETAILS.EMP_NO,
Body.CMSEAI.DETAILS.REMARK,
Body.CMSEAI.DETAILS.ROLE);
result: the data insert correctly.
CREATE_TIME and OPERDATE's value is '2005-02-01 15:20:30'.
test 2
PASSTHRU('INSERT INTO USR_VIP_MANAGER(MANAGERID,NAME,MOBILE,
SEX,CITYID,COUNTYID,DEPT,PHONE,ADDRESS,FAX,EMAIL,FAMILY_PHONE,
FAMILY_ADDRESS,CREATE_TIME,EMP_NO,REMARK,ROLE,OPERDATE)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,
to_date(?,''yyyy-mm-dd hh24:mi:ss''),?,?,?,to_date(?,''yyyy-mm-dd hh24:mi:ss''))',
Body.CMSEAI.DETAILS.MANAGERID,
Body.CMSEAI.DETAILS.NAME,
Body.CMSEAI.DETAILS.MOBILE,
Body.CMSEAI.DETAILS.SEX,
Body.CMSEAI.DETAILS.CITYID,
Body.CMSEAI.DETAILS.COUNTYID,
Body.CMSEAI.DETAILS.DEPT,
Body.CMSEAI.DETAILS.PHONE,
Body.CMSEAI.DETAILS.ADDRESS,
Body.CMSEAI.DETAILS.FAX,
Body.CMSEAI.DETAILS.EMAIL,
Body.CMSEAI.DETAILS.FAMILY_PHONE,
Body.CMSEAI.DETAILS.FAMILY_ADDRESS,
Body.CMSEAI.DETAILS.CREATE_TIME,
Body.CMSEAI.DETAILS.EMP_NO,
Body.CMSEAI.DETAILS.REMARK,
Body.CMSEAI.DETAILS.ROLE,
Body.CMSEAI.DETAILS.OPERDATE);
result: sql exception is throwed
CREATE_TIME value is '2005-02-01 15:20:30', and in trace-log this value is also correct.
test 3
PASSTHRU('INSERT INTO USR_VIP_MANAGER(MANAGERID,NAME,MOBILE,
SEX,CITYID,COUNTYID,DEPT,PHONE,ADDRESS,FAX,EMAIL,FAMILY_PHONE,
FAMILY_ADDRESS,CREATE_TIME,EMP_NO,REMARK,ROLE,OPERDATE)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,
to_date(?,''yyyy-mm-dd),?,?,?,to_date(?,''yyyy-mm-dd"))',
Body.CMSEAI.DETAILS.MANAGERID,
Body.CMSEAI.DETAILS.NAME,
Body.CMSEAI.DETAILS.MOBILE,
Body.CMSEAI.DETAILS.SEX,
Body.CMSEAI.DETAILS.CITYID,
Body.CMSEAI.DETAILS.COUNTYID,
Body.CMSEAI.DETAILS.DEPT,
Body.CMSEAI.DETAILS.PHONE,
Body.CMSEAI.DETAILS.ADDRESS,
Body.CMSEAI.DETAILS.FAX,
Body.CMSEAI.DETAILS.EMAIL,
Body.CMSEAI.DETAILS.FAMILY_PHONE,
Body.CMSEAI.DETAILS.FAMILY_ADDRESS,
Body.CMSEAI.DETAILS.CREATE_TIME,
Body.CMSEAI.DETAILS.EMP_NO,
Body.CMSEAI.DETAILS.REMARK,
Body.CMSEAI.DETAILS.ROLE,
Body.CMSEAI.DETAILS.OPERDATE);
result: no exception is throwed, data insert succeed.
CREATE_TIME value is '2005-02-01.
thats why? i search all threads about PASSTHRU at here, but not find the answer
who can tell me, thanks in advance  |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Wed Feb 02, 2005 4:46 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Is your field defined as a date field only, or as a datetime/timestamp field?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| smallred |
| Posted: Wed Feb 02, 2005 7:30 pm Post subject: |
|
|
Newbie
Joined: 27 Jan 2005
Posts: 4
Location: china
|
jefflowrey
i am using a general xml for the input message, and not format its field to integer,string or date as need.
before executing prep-statment, must i use CAST function to format?
Thanks |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Feb 03, 2005 6:08 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| smallred wrote: |
| i am using a general xml for the input message, and not format its field to integer,string or date as need. |
What is the format of the database column.
_________________
I am *not* the model of the modern major general. |
|
| 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
|
|
|
|
