Board index » delphi » DBD/DBI and DATE Type

DBD/DBI and DATE Type

Hi All:

What is the trick to passing dates to Oracle via DBD/DBI ?

I'm trying to do an insert but I'm getting some error
msgs.

I have tried passing:

  1) String like "TO_DATE('29-OCT-1999', 'DD-MM-YYYY')"

  2) String like '29-OCT-1999'

In Bind-param, I've tried specify SQL_TYPE_DATE as my 3rd
parm. There doesn't seem to be too much documentation on
DATE and TIME datatypes.

Thanks in Advance,

Eisen

 

Re:DBD/DBI and DATE Type


In article <s1jh56pics...@corp.supernews.com> on Fri, 29 Oct 1999
16:00:38 GMT, Eisen Chao <ec...@interaccess.com> says...

Quote
> What is the trick to passing dates to Oracle via DBD/DBI ?

> I'm trying to do an insert but I'm getting some error
> msgs.

> I have tried passing:

>   1) String like "TO_DATE('29-OCT-1999', 'DD-MM-YYYY')"

>   2) String like '29-OCT-1999'

I am new at this game, but found a way that works.

Convert the month name to a two-digit integer (by hash lookup, for
example), and squeeze out punctuation:

    tr/0-9//cd;

Then TO_DATE(29101999, 'DDMMYYYY') should work.

Quote
> ... There doesn't seem to be too much documentation on
> DATE and TIME datatypes.

I'll say!

--
(Just Another Larry) Rosler
Hewlett-Packard Laboratories
http://www.hpl.hp.com/personal/Larry_Rosler/
l...@hpl.hp.com

Re:DBD/DBI and DATE Type


On Fri, 29 Oct 1999 16:00:38 GMT,
        Eisen Chao <ec...@interaccess.com> wrote:

Quote
> Hi All:

> What is the trick to passing dates to Oracle via DBD/DBI ?

I'm pretty ignorant about Oracle, but with Sybase it depends on how your
configure your server. You can tell it to accept all kinds of things as
dates. What does the Oracle manual say about the date format that you're
allowed to pass in?

Quote
>   1) String like "TO_DATE('29-OCT-1999', 'DD-MM-YYYY')"

That is just SQL, right? DBD/DBI should pass that on unchanged, which
means it's your server rejecting the date. Did  something happen when
you used the Oracle shell client (is there one?)? Maybe you should ask
this in an Oracle group as well.

Martien
--
Martien Verbruggen              |
Interactive Media Division      | Think of the average person. Half of
Commercial Dynamics Pty. Ltd.   | the people out there are dumber.
NSW, Australia                  |

Re:DBD/DBI and DATE Type


Well, in MySQL it's
mm-dd-yyyy hh:mm:ss

for DateTime fields

--
--------------------------------------
Juvex Web Development

   Michiel Trimpe - General Manager

De Verver 13
5506 BH Veldhoven
The Netherlands
tel:   +31 40 2535539
fax:   +31 40 2543346
www:   http://www.juvex.com
email: i...@juvex.com

Quote
Eisen Chao <ec...@interaccess.com> wrote in message

news:s1jh56picsf93@corp.supernews.com...
Quote
> Hi All:

> What is the trick to passing dates to Oracle via DBD/DBI ?

> I'm trying to do an insert but I'm getting some error
> msgs.

> I have tried passing:

>   1) String like "TO_DATE('29-OCT-1999', 'DD-MM-YYYY')"

>   2) String like '29-OCT-1999'

> In Bind-param, I've tried specify SQL_TYPE_DATE as my 3rd
> parm. There doesn't seem to be too much documentation on
> DATE and TIME datatypes.

> Thanks in Advance,

> Eisen

Re:DBD/DBI and DATE Type


In article <941302772.24989.0.pluto.c29fe...@news.demon.nl> on Sat, 30
Oct 1999 19:02:09 +0200, Michiel Trimpe <i...@juvex.com> says...

Quote
> Well, in MySQL it's
> mm-dd-yyyy hh:mm:ss

> for DateTime fields

Are you quite sure of that?  In *my* MySQL, it's the ISO 8601 date-time
standard:

  yyyy-mm-dd hh:mm:ss

--
(Just Another Larry) Rosler
Hewlett-Packard Laboratories
http://www.hpl.hp.com/personal/Larry_Rosler/
l...@hpl.hp.com

Re:DBD/DBI and DATE Type


Quote
> I have tried passing:

>   1) String like "TO_DATE('29-OCT-1999', 'DD-MM-YYYY')"

it's:
DD-MON-YYYY
unless the month is in numeric format.
Quote

>   2) String like '29-OCT-1999'

> In Bind-param, I've tried specify SQL_TYPE_DATE as my 3rd
> parm. There doesn't seem to be too much documentation on
> DATE and TIME datatypes.

> Thanks in Advance,

> Eisen

Re:DBD/DBI and DATE Type


Thanks to all the people who post and/or sent me e-mail!

All of your solutions were appropriate; my problem was
finally figuring out in order to get this working:

1) You can only put a String or numeric value in
   your Perl variable. This variable then becomes
   bound to your SQL statement, via bind-param().

2) It is up to your SQL statement to convert that
   string to the proper DATE format that Oracle
   can accept. To put or get dates from Oracle,
   use both the TO_CHAR and TO_DATE functions,
   with the necessary date format masks.

This was kind of an interesting problem in that the DBI
/DBD documentation must assume that the reader is familar
with Oracle (I admit it, I'm a newbie to both DBI/DBD &
Oracle!), but it might be helpful if there was some mention
of how to handle DATES and other special Oracle datatypes
in the documentaion. There's no mention in it, nor the
Perl Cookbook, nor several DBI articles I read in TPJ.
All examples are for string or numerics.

All in all, I learned much in the tried-and-true school
of butting my head into the wall.

Thanks again to all who responded!

Eisen
Chicago

Other Threads