MQSeries.net :: View topic - Trying to insert date into database

MQSeries.net

Tech Exchange

Education

Certifications

Library

Info Center

SupportPacs

FAQÂ Â

Usergroups

RSS Feed - WebSphere MQ Support

RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Trying to insert date into database

Trying to insert date into database

« View previous topic :: View next topic »

Author

Message

andrewl68

Posted: Tue Sep 30, 2003 12:00 pm Post subject: Trying to insert date into database

Novice

Joined: 23 Sep 2003
Posts: 14

I am trying to insert the following date format into an oracle database
'YYYY/MM/DD HH24:MI:SS'

I am trying to use passthru to alter the Date format to allow it to be inserted but I am getting an error.

SET TIMEFORMAT = 'YYYY/MM/DD HH24:MI:SS';
PASSTHRU('ALTER SESSION SET NLS_DATE_FORMAT = ?',TIMEFORMAT);

Here is the error
[MERANT][ODBC Oracle 8 driver][Oracle 8]ORA-01036: illegal variable name/number

Can anyone help me get around this date issue. I need to insert the date plus the time. I have attempted the todate function without success as well.
Thanks

Craig B

Posted: Thu Oct 02, 2003 3:47 am Post subject:

Partisan

Joined: 18 Jun 2003
Posts: 316
Location: UK

Hi,

If you were issuing this SQL command directly in the database then I imagine it would look something like the following :

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS'

As you can see you would have put single quotes around the RHS of the expression. Without these, the SQL processor would try and interpret this as a verb instead of a character string.

In your ESQL you have defined a character string that contains the value of 'YYYY/MM/DD HH24:MI:SS' and passed this as a parameter to your PASSTHRU statement. However, be aware that the single quotes will not have been inserted into your SQL statement that is constructed.

This will mean that the command that is sent to the database will be :

ALTER SESSION SET NLS_DATE_FORMAT = YYYY/MM/DD HH24:MI:SS

and this may be why you are seeing the error you are.

You need to code your ESQL such that apostrophes are passed in such that the SQL statement is formed with them. Now in ESQL apostrophes are used to denote strings, as so have special meaning. Therefore you have to escape them by using a double apostrophe. For example :

Code:

SET TIMEFORMAT = '''YYYY/MM/DD HH24:MI:SS''';
PASSTHRU('ALTER SESSION SET NLS_DATE_FORMAT = ?',TIMEFORMAT);

OR....

Code:

SET TIMEFORMAT = 'YYYY/MM/DD HH24:MI:SS';
PASSTHRU('ALTER SESSION SET NLS_DATE_FORMAT = ''?''',TIMEFORMAT);

This would be the same if you specified the todate database function in the passthru. This expects to receive a format character string that has to be enclosed in apostophes.
_________________
Regards
Craig

Display posts from previous:

Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Trying to insert date into database

Jump to:

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

Protected by Anti-Spam ACP