--_3d99cf55-332b-400f-9192-5412338ba999_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

I'm getting an error trying to run an insert action due to the DATE or DATE=
TIME datatype. I couldn't find anything on it, and was wondering if anyone=
could point me in the right direction -- Thanks

I'm trying to run an insert ... the date value is giving me errors. Here's=
the code:


my $sql =3D qq{ INSERT INTO perfdata VALUES ( ?, ?, ?, ?, ?, ? ) };
my $sth =3D $dbh->prepare( $sql );
for ( [at] record ) {
eval {
$sth->bind_param( 1, [at] $_->[0], SQL_VARCHAR );
$sth->bind_param( 2, [at] $_->[1], SQL_VARCHAR );
$sth->bind_param( 3, [at] $_->[2], SQL_DATETIME );
$sth->bind_param( 4, [at] $_->[3], SQL_INTEGER );
$sth->bind_param( 5, [at] $_->[4], SQL_CHAR );
$sth->bind_param( 6, [at] $_->[5], SQL_VARCHAR );
$sth->execute();
$dbh->commit();
};
if ( $ [at] ) {
warn "Database error: $DBI::errstr\n";
$dbh->rollback(); #just die if rollback is failing
}
}
$sth->finish();

where record is:

push [at] record, [$src_swt, $dst_swt, otime(), $latency, $reachability, $path]=
;

sub otime {
my $m =3D (split /\s/, gmtime)[1];
my ($seconds, $minutes, $hours, $day_of_month, $month,
$year, $wday, $yday, $n) =3D gmtime;
if ($hours > 12) {
$hours -=3D 12;
$n =3D 'PM';
} else {
$n =3D 'AM';
}
return sprintf("%04d-%02d-%02d %02d:%02d:%02d",
$year+1900, $month, $day_of_month, $hours, $minutes, $seconds) . " $n=
";
}

results in:
SQL type 9 for ':p3' is not fully supported, bound as SQL_VARCHAR instead a=
t ./atmping.pl line 124.
DBD::Oracle::st execute failed: ORA-01861: literal does not match format st=
ring
(DBD ERROR: error possibly near <*> indicator at char 42 in ' INSERT INTO p=
erfdata
VALUES ( :p1, :p2, :<*>p3, :p4, :p5, :p6 ) ') [for Statement " INSERT INTO =
perfdata
VALUES ( ?, ?, ?, ?, ?, ? ) " with ParamValues: :p5=3D1, :p3=3D'2007-06-26 =
09:59:14 PM',
:p6=3D'', :p1=3D'bimatm1rsf4', :p4=3D'110', :p2=3D'beratm1rsf1'] at ./atmpi=
ng.pl line 128.
Database error: ORA-01861: literal does not match format string (DBD ERROR:=
error possibly
near <*> indicator at char 42 in ' INSERT INTO perfdata VALUES ( :p1, :p2, =
:<*>p3, :p4,
:p5, :p6 ) ')

I also tried 24 hr time format resulting in the same error:
SQL type 9 for ':p3' is not fully supported, bound as SQL_VARCHAR instead a=
t ./atmping.pl line 124.
DBD::Oracle::st execute failed: ORA-01861: literal does not match format st=
ring
(DBD ERROR: error possibly near <*> indicator at char 42 in ' INSERT INTO p=
erfdata
VALUES ( :p1, :p2, :<*>p3, :p4, :p5, :p6 ) ') [for Statement " INSERT INTO =
perfdata
VALUES ( ?, ?, ?, ?, ?, ? ) " with ParamValues: :p5=3D1, :p3=3D'2007-06-26 =
22:10:24',
:p6=3D'', :p1=3D'dtsatm1rsf1', :p4=3D'10', :p2=3D'braatm2rsf2'] at ./atmpin=
g.pl line 128.
Database error: ORA-01861: literal does not match format string (DBD ERROR:=
error
possibly near <*> indicator at char 42 in ' INSERT INTO perfdata VALUES ( :=
p1, :p2,
:<*>p3, :p4, :p5, :p6 ) ')

I also tried using the SQL_VARCHAR rather than SQL_DATETIME, but the same r=
esults
Also, here's my table definition:

$sql =3D qq{ CREATE TABLE perfdata ( source_swt VARCHAR2(64) NOT NULL,
dest_swt VARCHAR2(64) NOT NULL,
time DATE NOT NULL,
latency NUMBER(6,2),
reachable CHAR(1),
path VARCHAR2(128)
) };
$dbh->do( $sql );

BTW - I am using Perl 5.8.5 for x86_64, DBI 1.58, DBD::Oracle 1.19, DBD::OD=
BC 1.13


Craig
____________________________________________________________ _____
See what you=92re getting into=85before you go there.
http://newlivehotmail.com=

--_3d99cf55-332b-400f-9192-5412338ba999_--

>-----Original Message-----
>From: Craig Metzer [mailto:ucantspamthis [at] hotmail.com]
>Sent: Thursday, July 26, 2007 5:36 PM
>I'm getting an error trying to run an insert action due to the DATE
>or DATETIME datatype. I couldn't find anything on it, and was
wondering
>if anyone could point me in the right direction -- Thanks
>I'm trying to run an insert ... the date value is giving me errors.
Here's the code:
> my $sql =3D qq{ INSERT INTO perfdata VALUES ( ?, ?, ?, ?, ?, ? ) };
> my $sth =3D $dbh->prepare( $sql );
> for ( [at] record ) {
> eval {
> $sth->bind_param( 1, [at] $_->[0], SQL_VARCHAR );
> $sth->bind_param( 2, [at] $_->[1], SQL_VARCHAR );
> $sth->bind_param( 3, [at] $_->[2], SQL_DATETIME );
> $sth->bind_param( 4, [at] $_->[3], SQL_INTEGER );
> $sth->bind_param( 5, [at] $_->[4], SQL_CHAR );
> $sth->bind_param( 6, [at] $_->[5], SQL_VARCHAR );
> $sth->execute();
> $dbh->commit();
> };
> if ( $ [at] ) {
> warn "Database error: $DBI::errstr\n";
> $dbh->rollback(); #just die if rollback is failing
> }
> }
> $sth->finish();

Perhaps...I'm no expert...you need to use the TO_Date function
and specify the format your string is in.
I have code from an insert process that used the following SQL
statement,
unfortunately the input data file no longer exists so I can't look at
what the format was.
HTH, Paula
(watch out for text wrapping of code by mail client)

sub prepareInsStmt { #set up and prepare the SQL statement
my $seConn =3D $_[0];
my $stmt1 =3D 'INSERT INTO SEWEB.logfile ';
my $stmt2 =3D '(userid, update_ts, category) ';
my $stmt3 =3D "values (?,TO_Date(?,'YYYY-MM-DD HH24:MI:SS'),?)";
my $stmt =3D $stmt1.$stmt2.$stmt3;
my $sth =3D $seConn->prepare( $stmt ) or
processError("PrepareFailed","$stmt",$seConn);
printIt("Insert statement prepared:\n\t$stmt");
return $sth; #return the prepared statement handle
}
This subroutine was called via:
my $isth =3D prepareInsStmt($seConn); #prepare INSERT w/ placeholder

And the inserts processed via:
while (<HIST>) {
chomp;
my ($uid,$datets,$category,$desc) =3D split /,/,$_;
$isth->execute($uid,$datets,$category)
or processError("ExecuteFailed",$isth,$seConn);
}

Craig Metzer wrote:
> I'm getting an error trying to run an insert action due to the DATE or
> DATETIME datatype. I couldn't find anything on it, and was wondering
> if anyone could point me in the right direction -- Thanks
>
> I'm trying to run an insert ... the date value is giving me errors.
> Here's the code:
>
> my $sql =3D qq{ INSERT INTO perfdata VALUES ( ?, ?, ?, ?, ?, ? ) };
> my $sth =3D $dbh->prepare( $sql );
> for ( [at] record ) {
> eval {
> $sth->bind_param( 1, [at] $_->[0], SQL_VARCHAR );

Why the ' [at] ' here? $_->[0] would be less confusing (to me, anyway).

> $sth->bind_param( 2, [at] $_->[1], SQL_VARCHAR );
> $sth->bind_param( 3, [at] $_->[2], SQL_DATETIME );
> $sth->bind_param( 4, [at] $_->[3], SQL_INTEGER );
> $sth->bind_param( 5, [at] $_->[4], SQL_CHAR );
> $sth->bind_param( 6, [at] $_->[5], SQL_VARCHAR );
> $sth->execute();
> $dbh->commit();
> };
> if ( $ [at] ) {
> warn "Database error: $DBI::errstr\n";
> $dbh->rollback(); #just die if rollback is failing
> }
> }
> $sth->finish();

You really don't need to specify the bind_type parameters.

Using Oracle, you will either need to use the TO_DATE() function:
insert into perfdata (time)
values (to_date(?,'YYYY-MM-DD HH24:MI:SS'))

Or, set the NLS_DATE_FORMAT session variable, which Oracle uses
implicitly to convert strings into dates:
/* just once, after you connect */
alter session set nls_date_format =3D 'YYYY-MM-DD HH24:MI:SS'

insert into perfdata (time) values (?)

Then, just bind as strings (which is the default):
$sth->execute( [at] $_);

Regards,
Philip

-----Original Message-----
From: Craig Metzer [mailto:ucantspamthis [at] hotmail.com]
Sent: Thursday, July 26, 2007 6:36 PM
To: dbi-users [at] perl.org
Subject: datatype error

I'm getting an error trying to run an insert action due to the DATE or
DATETIME datatype. I couldn't find anything on it, and was wondering if
anyone could point me in the right direction -- Thanks

I'm trying to run an insert ... the date value is giving me errors.
Here's the code:


my $sql =3D qq{ INSERT INTO perfdata VALUES ( ?, ?, ?, ?, ?, ? ) };
my $sth =3D $dbh->prepare( $sql );
for ( [at] record ) {
eval {
$sth->bind_param( 1, [at] $_->[0], SQL_VARCHAR );
$sth->bind_param( 2, [at] $_->[1], SQL_VARCHAR );
$sth->bind_param( 3, [at] $_->[2], SQL_DATETIME );
$sth->bind_param( 4, [at] $_->[3], SQL_INTEGER );
$sth->bind_param( 5, [at] $_->[4], SQL_CHAR );
$sth->bind_param( 6, [at] $_->[5], SQL_VARCHAR );
$sth->execute();
$dbh->commit();
};
if ( $ [at] ) {
warn "Database error: $DBI::errstr\n";
$dbh->rollback(); #just die if rollback is failing
}
}
$sth->finish();

where record is:

push [at] record, [$src_swt, $dst_swt, otime(), $latency, $reachability,
$path];

sub otime {
my $m =3D (split /\s/, gmtime)[1];
my ($seconds, $minutes, $hours, $day_of_month, $month,
$year, $wday, $yday, $n) =3D gmtime;
if ($hours > 12) {
$hours -=3D 12;
$n =3D 'PM';
} else {
$n =3D 'AM';
}
return sprintf("%04d-%02d-%02d %02d:%02d:%02d",
$year+1900, $month, $day_of_month, $hours, $minutes, $seconds) . "
$n";
}

results in:
SQL type 9 for ':p3' is not fully supported, bound as SQL_VARCHAR
instead at ./atmping.pl line 124.
DBD::Oracle::st execute failed: ORA-01861: literal does not match format
string
....

I also tried 24 hr time format resulting in the same error:
....


Not sure why you are doing "eval"... plus I've rarely had to bind with
the "type" for Oracle...

But to the date issue... it has been my experience that you either set
the date format for the session or add a "to_date" function to the sql
then bind the "string" data (date) and let Oracle do the work... at
least that's easier for me...

Example:

....
$sth->prepare(q{select to_date(?,'Mon-yyyy') as First_of_Month from
dual}) or die"...";
$sth->execute('Jul-2007') or die "...";
($bom) =3D $sth->fetch;
....
Or...

$sth->prepare(q{select add_months(to_date(?,'Mon-yyyy'),1) - 1 as
lastDay from dual}) or die"...";
$sth->execute() or die "...";
Foreach my $date ('Jan-2007', 'Feb-2007', ...) {
$sth->bind_param($date);
my ($eom) =3D $sth->fetch;
}

Not tested...

Hope this helps...

jwm

> Not sure why you are doing "eval"... plus I've rarely had to bind with
> the "type" for Oracle...

I just pulled this method from a web site. As I understand it, this is don=
e to facilitate the error checking. I asumed this was a best method for thi=
s operation.

http://informatics.umdnj.edu/bioinformatics/courses/5004/Not es/DBI%20Exampl=
es.htm


Craig

----------------------------------------
> Subject: RE: datatype error
> Date: Fri, 27 Jul 2007 08:41:07 -0400
> From: John.Moon [at] dms.myflorida.com
> To: ucantspamthis [at] hotmail.com; dbi-users [at] perl.org
>
> -----Original Message-----
> From: Craig Metzer [mailto:ucantspamthis [at] hotmail.com]
> Sent: Thursday, July 26, 2007 6:36 PM
> To: dbi-users [at] perl.org
> Subject: datatype error
>
> I'm getting an error trying to run an insert action due to the DATE or
> DATETIME datatype. I couldn't find anything on it, and was wondering if
> anyone could point me in the right direction -- Thanks
>
> I'm trying to run an insert ... the date value is giving me errors.
> Here's the code:
>
>
> my $sql =3D qq{ INSERT INTO perfdata VALUES ( ?, ?, ?, ?, ?, ? ) };
> my $sth =3D $dbh->prepare( $sql );
> for ( [at] record ) {
> eval {
> $sth->bind_param( 1, [at] $_->[0], SQL_VARCHAR );
> $sth->bind_param( 2, [at] $_->[1], SQL_VARCHAR );
> $sth->bind_param( 3, [at] $_->[2], SQL_DATETIME );
> $sth->bind_param( 4, [at] $_->[3], SQL_INTEGER );
> $sth->bind_param( 5, [at] $_->[4], SQL_CHAR );
> $sth->bind_param( 6, [at] $_->[5], SQL_VARCHAR );
> $sth->execute();
> $dbh->commit();
> };
> if ( $ [at] ) {
> warn "Database error: $DBI::errstr\n";
> $dbh->rollback(); #just die if rollback is failing
> }
> }
> $sth->finish();
>
> where record is:
>
> push [at] record, [$src_swt, $dst_swt, otime(), $latency, $reachability,
> $path];
>
> sub otime {
> my $m =3D (split /\s/, gmtime)[1];
> my ($seconds, $minutes, $hours, $day_of_month, $month,
> $year, $wday, $yday, $n) =3D gmtime;
> if ($hours > 12) {
> $hours -=3D 12;
> $n =3D 'PM';
> } else {
> $n =3D 'AM';
> }
> return sprintf("%04d-%02d-%02d %02d:%02d:%02d",
> $year+1900, $month, $day_of_month, $hours, $minutes, $seconds) . "
> $n";
> }
>
> results in:
> SQL type 9 for ':p3' is not fully supported, bound as SQL_VARCHAR
> instead at ./atmping.pl line 124.
> DBD::Oracle::st execute failed: ORA-01861: literal does not match format
> string
> ...
>
> I also tried 24 hr time format resulting in the same error:
> ...
>
>
> Not sure why you are doing "eval"... plus I've rarely had to bind with
> the "type" for Oracle...
>
> But to the date issue... it has been my experience that you either set
> the date format for the session or add a "to_date" function to the sql
> then bind the "string" data (date) and let Oracle do the work... at
> least that's easier for me...
>
> Example:
>
> ...
> $sth->prepare(q{select to_date(?,'Mon-yyyy') as First_of_Month from
> dual}) or die"...";
> $sth->execute('Jul-2007') or die "...";
> ($bom) =3D $sth->fetch;
> ...
> Or...
>
> $sth->prepare(q{select add_months(to_date(?,'Mon-yyyy'),1) - 1 as
> lastDay from dual}) or die"...";
> $sth->execute() or die "...";
> Foreach my $date ('Jan-2007', 'Feb-2007', ...) {
> $sth->bind_param($date);
> my ($eom) =3D $sth->fetch;
> }
>
> Not tested...
>
> Hope this helps...
>
> jwm

____________________________________________________________ _____
Missed the show?=A0 Watch videos of the Live Earth Concert on MSN.
http://liveearth.msn.com=

--_445f10ae-d5b1-4015-88ca-4ff54f820805_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Many thanks to all who provided assistance.

The problem was the date format I was using. The system default was "YYYY-=
Mon-DD", which didn't provide the time info I needed. My options were:

1. To specify a format for each transaction using the to_date function.
2. To set the default format for my session using the "alter session set NL=
S_TIME_FORMAT" statement.

I chose the latter, added one line of code after I made the DB connection (=
applied to my DBI object)

$dbh =3D DBI->connect( .....);
$dbh->do('ALTER SESSION SET NLS_TIME_FORMAT =3D "YYYYDDMM HH24:MI:SS"');
....

Then instead of using the SQL_DATETIME type, I used the SQL_VARCHAR:

from:
$sth->bind_param( 3, [at] $_->[2], SQL_DATETIME );

to:
$sth->bind_param( 3, [at] $_->[2], SQL_VARCHAR );

Thanks again,
Craig



> Subject: RE: datatype error> Date: Fri, 27 Jul 2007 08:06:10 -0500> From:=
PCAPACIO [at] amfam.com> To: ucantspamthis [at] hotmail.com> CC: dbi-users [at] perl.org>=
> >-----Original Message-----> >From: Craig Metzer [mailto:ucantspamthis [at] h=
otmail.com] > >Sent: Thursday, July 26, 2007 5:36 PM> >I'm getting an error=
trying to run an insert action due to the DATE > >or DATETIME datatype. I =
couldn't find anything on it, and was> wondering> >if anyone could point me=
in the right direction -- Thanks> >I'm trying to run an insert ... the dat=
e value is giving me errors.> Here's the code:> > my $sql =3D qq{ INSERT IN=
TO perfdata VALUES ( ?, ?, ?, ?, ?, ? ) };> > my $sth =3D $dbh->prepare( $s=
ql );> > for ( [at] record ) {> > eval {> > $sth->bind_param( 1, [at] $_->[0], SQL_=
VARCHAR );> > $sth->bind_param( 2, [at] $_->[1], SQL_VARCHAR );> > $sth->bind_p=
aram( 3, [at] $_->[2], SQL_DATETIME );> > $sth->bind_param( 4, [at] $_->[3], SQL_IN=
TEGER );> > $sth->bind_param( 5, [at] $_->[4], SQL_CHAR );> > $sth->bind_param(=
6, [at] $_->[5], SQL_VARCHAR );> > $sth->execute();> > $dbh->commit();> > };> =
> if ( $ [at] ) {> > warn "Database error: $DBI::errstr\n";> > $dbh->rollback()=
; #just die if rollback is failing> > }> > }> > $sth->finish();> > Perhaps.=
...I'm no expert...you need to use the TO_Date function> and specify the for=
mat your string is in.> I have code from an insert process that used the fo=
llowing SQL> statement, > unfortunately the input data file no longer exist=
s so I can't look at> what the format was. > HTH, Paula> (watch out for tex=
t wrapping of code by mail client)> > sub prepareInsStmt { #set up and prep=
are the SQL statement> my $seConn =3D $_[0];> my $stmt1 =3D 'INSERT INTO SE=
WEB.logfile ';> my $stmt2 =3D '(userid, update_ts, category) ';> my $stmt3 =
=3D "values (?,TO_Date(?,'YYYY-MM-DD HH24:MI:SS'),?)";> my $stmt =3D $stmt1=
..$stmt2.$stmt3;> my $sth =3D $seConn->prepare( $stmt ) or> processError("Pr=
epareFailed","$stmt",$seConn); > printIt("Insert statement prepared:\n\t$st=
mt");> return $sth; #return the prepared statement handle> }> This subrouti=
ne was called via:> my $isth =3D prepareInsStmt($seConn); #prepare INSERT w=
/ placeholder> > And the inserts processed via:> while (<HIST>) {> chomp;> =
my ($uid,$datets,$category,$desc) =3D split /,/,$_;> $isth->execute($uid,$d=
atets,$category) > or processError("ExecuteFailed",$isth,$seConn);> }
____________________________________________________________ _____
Local listings, incredible imagery, and driving directions - all in one pla=
ce! Find it!
http://maps.live.com/?wip=3D69&FORM=3DMGAC01=

--_445f10ae-d5b1-4015-88ca-4ff54f820805_--