| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| MbSQLStatement - Oracle Date Insert |
« View previous topic :: View next topic » |
| Author |
Message
|
| jboller001 |
 Posted: Mon Aug 28, 2006 2:34 pm Post subject: MbSQLStatement - Oracle Date Insert |
 |
|
Apprentice
Joined: 31 May 2006
Posts: 40
|
I'm using the MbSQLStatement class and am receiving an "undefined function" error on the following insert statement:
INSERT into Database.DTHDBA.DTH_MCI_INDIVIDUAL (WMS_CLIENT_ID, HISTORY_SEQ_NBR, SOURCE_SYSTEM_TXT, FIRST_NM, LAST_NM, MIDDLE_NM, SUFFIX_NM, BIRTH_DT, SOCIAL_SEC_NBR, GENDER_CD_TXT,SWMS_CONVERSION_DT, CREATE_DT, CREATE_USR_ID, DELETE_IND) VALUES ('JB10008', 1, 'UPS', 'John', 'Smith', 'D', 'Jr', TO_DATE('1976-03-20', 'YYYY-MM-DD'),111223333, 'SX1', CURRENT_DATE,CURRENT_DATE, 'JBOLLER', 'N')
Error Message:
ERROR - MbException thrown [BIPv600:2558]BIP2558E: (null, ) : Undefined function '1.296.'.
The program is attempting to call a function which is not defined or is not in the schema path.
Correct the syntax of your ESQL expression in node 'null', around line and column '', then redeploy the message flow. : Unknown function
2006-08-28 08:19:32,953 (CreateIndividualRequest.java:110) ERROR - MbException trace: Unknown function
2006-08-28 08:19:32,953 (MessageTransformer_JavaCompute.java:66) ERROR - MbException Message: [BIPv600:2558]BIP2558E: (null, ) : Undefined function '1.296.'.
The program is attempting to call a function which is not defined or is not in the schema path.
-------
Using TOAD I'm able to copy and execute the same insert statement successfully.
I removed the BIRTH_DT column and the TO_DATE('1976-03-20', 'YYYY-MM-DD') function from the statement and it works.
Does anyone know the correct syntax for the TO_DATE function? |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Mon Aug 28, 2006 2:40 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
TO_DATE is not a valid ESQL function.
It may be a valid Oracle function, but if so you will need to properly identify it by schema and etc.
And even then, it really won't work because you can't call database procedures in this manner from an ESQL Insert. You can only call ESQL procedures.
You could replace your INSERT with a PASSTHRU and then it would work. But you'd take a small performance hit.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| jboller001 |
| Posted: Tue Sep 19, 2006 10:23 am Post subject: |
|
|
Apprentice
Joined: 31 May 2006
Posts: 40
|
Hi Jeff,
I tried two approaches without much luck.
1. Replaced TO_DATE with the ESQL Function:
CAST('" + birthDate+ "' AS DATE FORMAT 'YYYY-MM-DD')
2. Used PASSTHRU ESQL Function:
String insertStmt = "PASSTHRU('INSERT into Database.DTHDBA.DTH_MCI_INDIVIDUAL (WMS_CLIENT_ID, " +
"HISTORY_SEQ_NBR, SOURCE_SYSTEM_TXT, FIRST_NM, LAST_NM, MIDDLE_NM, SUFFIX_NM, BIRTH_DT, " +
"SOCIAL_SEC_NBR, GENDER_CD_TXT,SWMS_CONVERSION_DT, CREATE_DT, CREATE_USR_ID, DELETE_IND) " +
"VALUES ('" +legacyId + "', 1, " + "'" + legacySystem + "', '" +
firstName + "', '" + lastName + "', '" + middleName + "', '" +
suffixName + "', " + "TO_DATE('" + birthDate + "', 'YYYY-MM-DD'), " +
ssn + ", '" + genderCode + "', CURRENT_DATE," +
"CURRENT_DATE, '" + createId + "', 'N')')";
Does anything with either of these two statements look out of place to you?
I was hoping the ESQL CAST function would solve my problem. |
|
| Back to top |
|
|
| jboller001 |
| Posted: Tue Sep 19, 2006 11:18 am Post subject: |
|
|
Apprentice
Joined: 31 May 2006
Posts: 40
|
Nevermind, I changed the ESQL CAST to the following and it worked.
CAST('" + birthDate+ "' AS DATE) |
|
| 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
|
|
|
|
