Cant update the table with a date

[batcard]

Hi

Im using DOA with Oracle and I get a simple problem. Im trying to store a date in a variable parameter using ToracleQuery.SetVariable.

I use a declarevariable(0,otdate);
and a setvariable(0,date);

I have the following SQL that it generates in the sql just before the execute

update PT_CONTACT_RATE_ALERTS set
CONT_ID = '000000000075',
RATE_ALERT_ID = 22,
ALERT_DATE = TO_DATE('05/09/2005','MM/DD/YYYY'),
COMPLETED = NULL
where
CONT_ID = :CONTID and
RATE_ALERT_ID = :ALERTID and
ALERT_DATE = ATEID

the problem it seems when I look at the Getvariable(2) ie of the date it just has '01/01/2005' and none of the TO_DATE Stuff,, how can I get the ToracleQUery generate the right date format for oracle?

thanks in advance

 

[Marco Kalter]

My guess would be that 0 rows are affected by the update. You can check TOracleQuery.RowsProcessed after executing it.

 

[al0]

You just need not that format - your variable is declared as date so it is really date and not its string represantation, so data is transferred to Oracle in date internal format and not as string, so formatting is not applicable there. BTW, it is much more preferrableand safer way comparing to used in SET clause of your operator. Yur update shall look as follows

Originally posted by batcard:
Hi

Im using DOA with Oracle and I get a simple problem. Im trying to store a date in a variable parameter using ToracleQuery.SetVariable.

I use a declarevariable(0,otdate);
and a setvariable(0,date);

I have the following SQL that it generates in the sql just before the execute

update PT_CONTACT_RATE_ALERTS set
CONT_ID = '000000000075',
RATE_ALERT_ID = 22,
ALERT_DATE = TO_DATE('05/09/2005','MM/DD/YYYY'),
COMPLETED = NULL
where
CONT_ID = :CONTID and
RATE_ALERT_ID = :ALERTID and
ALERT_DATE = ATEID

the problem it seems when I look at the Getvariable(2) ie of the date it just has '01/01/2005' and none of the TO_DATE Stuff,, how can I get the ToracleQUery generate the right date format for oracle?

thanks in advance