best way to insert date/time into oracle table

best way to insert date/time into oracle table

Post by James Ki » Sun, 16 Jun 2002 01:40:21



Hi
I want to insert records from an array iteration. not sure of the best way
to enter the date and time. i read the docs, but thought i would check to
see if someone has experience with this and could give some ideas. code..
--
my $sth = $dbh->prepare( "INSERT INTO stats VALUES (?,?,?,?,?)" )
        or die "Cannot prepare SQL statements from $DBI::errstr\n";


        chomp;
        ($host, $user $cpu_pct, $mem_pct = split( /,/ );
        $sth->execute( DATE/TIME,$host, $user $cpu_pct, $mem_pct );

Quote:}

---
what are my options for inserting DATE/TIME into each record ?

Thanks
Jim

 
 
 

best way to insert date/time into oracle table

Post by Mark Charsh » Sun, 16 Jun 2002 02:07:50


Some date inserts I have experience with regarding Oracle and mssql (if this
helps).

Mark

--Oracle
insert into order_details (OrderID, ProductID, UnitPrice, Quantity,
Discount, OrderDate, ProductName)
    VALUES (5,1,1,1,1,TO_DATE ('4/01/1953', 'MM/DD/YYYY'),'name') ;

--mssql
insert into "order details" (OrderID, ProductID, UnitPrice, Quantity,
Discount, OrderDate, ProductName)
    VALUES (5,1,1,1,1,'4/01/1953','name') ;
-or-
insert into "order details" (OrderID, ProductID, UnitPrice, Quantity,
Discount, OrderDate, ProductName)
    VALUES (5,1,1,1,1,'apr 1, 1953','name') ;

-----Original Message-----

Sent: Friday, June 14, 2002 9:53 AM

Subject: RE: best way to insert date/time into oracle table

can i simply do :

        chomp;
        ($host, $user $cpu_pct, $mem_pct = split( /,/ );
        $sth->execute(  sysdate(),  $host, $user $cpu_pct, $mem_pct );
}

or do i have to do another query like
select SysDate from DUAL;

thanks Michael !!

> > what are my options for inserting DATE/TIME into each record ?
>       SYSDATE works fine for us here.  Make sure your oracle
> server has
> the correct system time/date.

> Cheers,

> Michael


 
 
 

best way to insert date/time into oracle table

Post by James Ki » Sun, 16 Jun 2002 01:52:36


can i simply do :

        chomp;
        ($host, $user $cpu_pct, $mem_pct = split( /,/ );
        $sth->execute(  sysdate(),  $host, $user $cpu_pct, $mem_pct );

Quote:}

or do i have to do another query like
select SysDate from DUAL;

thanks Michael !!

Quote:

> > what are my options for inserting DATE/TIME into each record ?
>       SYSDATE works fine for us here.  Make sure your oracle
> server has
> the correct system time/date.

> Cheers,

> Michael

 
 
 

best way to insert date/time into oracle table

Post by James Ki » Sun, 16 Jun 2002 02:06:57


yes, any ideas help. just looking for the best way. thank you !!
> -----Original Message-----

> Sent: Friday, June 14, 2002 1:08 PM
> To: Kipp, James
> Cc: Dbi-Users
> Subject: RE: best way to insert date/time into oracle table

> Some date inserts I have experience with regarding Oracle and
> mssql (if this
> helps).

> Mark

> --Oracle
> insert into order_details (OrderID, ProductID, UnitPrice, Quantity,
> Discount, OrderDate, ProductName)
>     VALUES (5,1,1,1,1,TO_DATE ('4/01/1953', 'MM/DD/YYYY'),'name') ;

> --mssql
> insert into "order details" (OrderID, ProductID, UnitPrice, Quantity,
> Discount, OrderDate, ProductName)
>     VALUES (5,1,1,1,1,'4/01/1953','name') ;
> -or-
> insert into "order details" (OrderID, ProductID, UnitPrice, Quantity,
> Discount, OrderDate, ProductName)
>     VALUES (5,1,1,1,1,'apr 1, 1953','name') ;

 
 
 

best way to insert date/time into oracle table

Post by Ian Haris » Sun, 16 Jun 2002 02:48:20


Are you using this for just a timestamp for record insertion?  If so, SYSDATE is your best bet.  You can use SYSDATE like so:

prepare("insert into table1 (field1, field2, timestamp) values(?, ?, SYSDATE)");

execute("Harry", "Potter");

Your execute statement can then be looped through with different values.

Ian

yes, any ideas help. just looking for the best way. thank you !!

> -----Original Message-----

> Sent: Friday, June 14, 2002 1:08 PM
> To: Kipp, James
> Cc: Dbi-Users
> Subject: RE: best way to insert date/time into oracle table

> Some date inserts I have experience with regarding Oracle and
> mssql (if this
> helps).

> Mark

> --Oracle
> insert into order_details (OrderID, ProductID, UnitPrice, Quantity,
> Discount, OrderDate, ProductName)
>     VALUES (5,1,1,1,1,TO_DATE ('4/01/1953', 'MM/DD/YYYY'),'name') ;

> --mssql
> insert into "order details" (OrderID, ProductID, UnitPrice, Quantity,
> Discount, OrderDate, ProductName)
>     VALUES (5,1,1,1,1,'4/01/1953','name') ;
> -or-
> insert into "order details" (OrderID, ProductID, UnitPrice, Quantity,
> Discount, OrderDate, ProductName)
>     VALUES (5,1,1,1,1,'apr 1, 1953','name') ;

 
 
 

best way to insert date/time into oracle table

Post by James Ki » Sun, 16 Jun 2002 03:27:27


i am using it for a timestamp record insertion :-)
the date/time is one of the fields in the table where i will be inserting
the records.
the table stats has the fields: sdate(date), host, user %cpu, %mem
i will give this a try:

my $sth = $dbh->prepare( "INSERT INTO stats VALUES (?,?,?,?,?)" )
        or die "Cannot prepare SQL statements from $DBI::errstr\n";


        chomp;
        ($host, $user $cpu_pct, $mem_pct = split( /,/ );
        $sth->execute( SYSDATE, $host, $user $cpu_pct, $mem_pct );

Quote:}

--

Thank You
Jim

> -----Original Message-----

> Sent: Friday, June 14, 2002 1:48 PM

> Subject: RE: best way to insert date/time into oracle table

> Are you using this for just a timestamp for record insertion?
>  If so, SYSDATE is your best bet.  You can use SYSDATE like so:

> prepare("insert into table1 (field1, field2, timestamp)
> values(?, ?, SYSDATE)");

> execute("Harry", "Potter");

> Your execute statement can then be looped through with
> different values.

> Ian

 
 
 

best way to insert date/time into oracle table

Post by Jeff Seg » Sun, 16 Jun 2002 03:34:54


Don't put SYSDATE in the execute.  Put it in the prepared statement:

my $sth = $dbh->prepare( "INSERT INTO stats VALUES (SYSDATE,?,?,?,?)" )
        or die "Cannot prepare SQL statements from $DBI::errstr\n";


        chomp;
        ($host, $user $cpu_pct, $mem_pct = split( /,/ );
        $sth->execute( $host, $user $cpu_pct, $mem_pct );

Quote:}

--

> i am using it for a timestamp record insertion :-)
> the date/time is one of the fields in the table where i will be inserting
> the records.
> the table stats has the fields: sdate(date), host, user %cpu, %mem
> i will give this a try:

> my $sth = $dbh->prepare( "INSERT INTO stats VALUES (?,?,?,?,?)" )
>    or die "Cannot prepare SQL statements from $DBI::errstr\n";


>    chomp;
>    ($host, $user $cpu_pct, $mem_pct = split( /,/ );
>    $sth->execute( SYSDATE, $host, $user $cpu_pct, $mem_pct );
> }
> --

> Thank You
> Jim

> > -----Original Message-----

> > Sent: Friday, June 14, 2002 1:48 PM

> > Subject: RE: best way to insert date/time into oracle table

> > Are you using this for just a timestamp for record insertion?
> >  If so, SYSDATE is your best bet.  You can use SYSDATE like so:

> > prepare("insert into table1 (field1, field2, timestamp)
> > values(?, ?, SYSDATE)");

> > execute("Harry", "Potter");

> > Your execute statement can then be looped through with
> > different values.

> > Ian

 
 
 

best way to insert date/time into oracle table

Post by James Ki » Sun, 16 Jun 2002 03:43:19


THANKS! I just found that out the hardway :)
Thanks all for your help. SYSDATE is the best way to go for my situation
> -----Original Message-----

> Sent: Friday, June 14, 2002 2:35 PM
> To: Kipp, James

> Subject: RE: best way to insert date/time into oracle table

> Don't put SYSDATE in the execute.  Put it in the prepared statement:

> my $sth = $dbh->prepare( "INSERT INTO stats VALUES
> (SYSDATE,?,?,?,?)" )
>         or die "Cannot prepare SQL statements from $DBI::errstr\n";


>         chomp;
>         ($host, $user $cpu_pct, $mem_pct = split( /,/ );

 
 
 

best way to insert date/time into oracle table

Post by David N Murr » Sun, 16 Jun 2002 03:58:40


Why not just put it as a constraint on the table to supply a default value
of SYSDATE for the column? Then you don't have to deal with it from the
program side at all.

On Jun 14, Kipp, James scribed:

> THANKS! I just found that out the hardway :)
> Thanks all for your help. SYSDATE is the best way to go for my situation

> > -----Original Message-----

> > Sent: Friday, June 14, 2002 2:35 PM
> > To: Kipp, James

> > Subject: RE: best way to insert date/time into oracle table

> > Don't put SYSDATE in the execute.  Put it in the prepared statement:

> > my $sth = $dbh->prepare( "INSERT INTO stats VALUES
> > (SYSDATE,?,?,?,?)" )
> >         or die "Cannot prepare SQL statements from $DBI::errstr\n";


> >         chomp;
> >         ($host, $user $cpu_pct, $mem_pct = split( /,/ );

 
 
 

best way to insert date/time into oracle table

Post by Tim Hars » Sun, 16 Jun 2002 06:24:10


True.  I agree it is better, but one thing I ran into recently here is
that another coder looked at some of my code where a table had a default
for inserts and a trigger for updates for maintaining the date and she
didn't understand why I could get away with not supplying a value for
that column.   A problem easily fixed by adding a comment to the code.
E.g.

# date modified field has in-server mechanism to maintain values, no
need for application do so...

(something to that effect)

> -----Original Message-----

> Sent: Friday, June 14, 2002 11:59 AM
> To: Kipp, James

> Subject: RE: best way to insert date/time into oracle table

> Why not just put it as a constraint on the table to supply a
> default value
> of SYSDATE for the column? Then you don't have to deal with
> it from the
> program side at all.

> On Jun 14, Kipp, James scribed:

> > THANKS! I just found that out the hardway :)
> > Thanks all for your help. SYSDATE is the best way to go for
> my situation

> > > -----Original Message-----

> > > Sent: Friday, June 14, 2002 2:35 PM
> > > To: Kipp, James

> > > Subject: RE: best way to insert date/time into oracle table

> > > Don't put SYSDATE in the execute.  Put it in the prepared
> statement:

> > > my $sth = $dbh->prepare( "INSERT INTO stats VALUES
> > > (SYSDATE,?,?,?,?)" )
> > >         or die "Cannot prepare SQL statements from
> $DBI::errstr\n";


> > >         chomp;
> > >         ($host, $user $cpu_pct, $mem_pct = split( /,/ );