datatype error

--_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
=20
I'm trying to run an insert ... the date value is giving me errors. Here's=
the code:
=20
=20
my $sql =3D qq{ INSERT INTO perfdata VALUES ( ?, ?, ?, ?, ?, ? ) };
my $sth =3D $dbh->prepare( $sql );
for ( @record ) {
eval {
$sth->bind_param( 1, @$_->[0], SQL_VARCHAR );
$sth->bind_param( 2, @$_->[1], SQL_VARCHAR );
$sth->bind_param( 3, @$_->[2], SQL_DATETIME );
$sth->bind_param( 4, @$_->[3], SQL_INTEGER );
$sth->bind_param( 5, @$_->[4], SQL_CHAR );
$sth->bind_param( 6, @$_->[5], SQL_VARCHAR );
$sth->execute();
$dbh->commit();
};
if ( $@ ) {
warn "Database error: $DBI::errstr\n";
$dbh->rollback(); #just die if rollback is failing
}
}
$sth->finish();
=20
where record is:
=20
push @record, [$src_swt, $dst_swt, otime(), $latency, $reachability, $path]=
;
=20
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=
";
}
=20
results in:
SQL type 9 for ':p3' is not fully supported, bound as SQL_VARCHAR instead a=
t ./atmping.pl line 124. =20
DBD::Oracle::st execute failed: ORA-01861: literal does not match format st=
ring=20
(DBD ERROR: error possibly near <*> indicator at char 42 in ' INSERT INTO p=
erfdata=20
VALUES ( :p1, :p2, :<*>p3, :p4, :p5, :p6 ) ') [for Statement " INSERT INTO =
perfdata=20
VALUES ( ?, ?, ?, ?, ?, ? ) " with ParamValues: :p5=3D1, :p3=3D'2007-06-26 =
09:59:14 PM',=20
: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=20
near <*> indicator at char 42 in ' INSERT INTO perfdata VALUES ( :p1, :p2, =
:<*>p3, :p4,=20
:p5, :p6 ) ')
=20
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=20
(DBD ERROR: error possibly near <*> indicator at char 42 in ' INSERT INTO p=
erfdata=20
VALUES ( :p1, :p2, :<*>p3, :p4, :p5, :p6 ) ') [for Statement " INSERT INTO =
perfdata=20
VALUES ( ?, ?, ?, ?, ?, ? ) " with ParamValues: :p5=3D1, :p3=3D'2007-06-26 =
22:10:24',=20
: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=20
possibly near <*> indicator at char 42 in ' INSERT INTO perfdata VALUES ( :=
p1, :p2,=20
:<*>p3, :p4, :p5, :p6 ) ')
=20
I also tried using the SQL_VARCHAR rather than SQL_DATETIME, but the same r=
esults
Also, here's my table definition:
=20
$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 );
=20
BTW - I am using Perl 5.8.5 for x86_64, DBI 1.58, DBD::Oracle 1.19, DBD::OD=
BC 1.13
=20
=20
Craig
____________________________________________________________ _____
See what you=92re getting into=85before you go there.
http://newlivehotmail.com=

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

RE: datatype error

-----Original Message-----
From: Craig Metzer [mailto:ucantspamthis@hotmail.com]=20
Sent: Thursday, July 26, 2007 6:36 PM
To: dbi-users@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
=20
I'm trying to run an insert ... the date value is giving me errors.
Here's the code:
=20
=20
my $sql =3D qq{ INSERT INTO perfdata VALUES ( ?, ?, ?, ?, ?, ? ) };
my $sth =3D $dbh->prepare( $sql );
for ( @record ) {
eval {
$sth->bind_param( 1, @$_->[0], SQL_VARCHAR );
$sth->bind_param( 2, @$_->[1], SQL_VARCHAR );
$sth->bind_param( 3, @$_->[2], SQL_DATETIME );
$sth->bind_param( 4, @$_->[3], SQL_INTEGER );
$sth->bind_param( 5, @$_->[4], SQL_CHAR );
$sth->bind_param( 6, @$_->[5], SQL_VARCHAR );
$sth->execute();
$dbh->commit();
};
if ( $@ ) {
warn "Database error: $DBI::errstr\n";
$dbh->rollback(); #just die if rollback is failing
}
}
$sth->finish();
=20
where record is:
=20
push @record, [$src_swt, $dst_swt, otime(), $latency, $reachability,
$path];
=20
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";
}
=20
results in:
SQL type 9 for ':p3' is not fully supported, bound as SQL_VARCHAR
instead at ./atmping.pl line 124. =20
DBD::Oracle::st execute failed: ORA-01861: literal does not match format
string=20
....
=20
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...=20

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...=20

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

RE: datatype error

>-----Original Message-----
>From: Craig Metzer [mailto:ucantspamthis@hotmail.com]=20
>Sent: Thursday, July 26, 2007 5:36 PM
>I'm getting an error trying to run an insert action due to the DATE=20
>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 ( @record ) {
> eval {
> $sth->bind_param( 1, @$_->[0], SQL_VARCHAR );
> $sth->bind_param( 2, @$_->[1], SQL_VARCHAR );
> $sth->bind_param( 3, @$_->[2], SQL_DATETIME );
> $sth->bind_param( 4, @$_->[3], SQL_INTEGER );
> $sth->bind_param( 5, @$_->[4], SQL_CHAR );
> $sth->bind_param( 6, @$_->[5], SQL_VARCHAR );
> $sth->execute();
> $dbh->commit();
> };
> if ( $@ ) {
> warn "Database error: $DBI::errstr\n";
> $dbh->rollback(); #just die if rollback is failing
> }
> }
> $sth->finish();
=20
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,=20
unfortunately the input data file no longer exists so I can't look at
what the format was.=20
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);=20
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 () {
chomp;
my ($uid,$datets,$category,$desc) =3D split /,/,$_;
$isth->execute($uid,$datets,$category)=20
or processError("ExecuteFailed",$isth,$seConn);
}

RE: datatype error

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
>=20
> I'm trying to run an insert ... the date value is giving me errors.
> Here's the code:
>=20
> my $sql =3D qq{ INSERT INTO perfdata VALUES ( ?, ?, ?, ?, ?, ? ) };
> my $sth =3D $dbh->prepare( $sql );
> for ( @record ) {
> eval {
> $sth->bind_param( 1, @$_->[0], SQL_VARCHAR );

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

> $sth->bind_param( 2, @$_->[1], SQL_VARCHAR );
> $sth->bind_param( 3, @$_->[2], SQL_DATETIME );
> $sth->bind_param( 4, @$_->[3], SQL_INTEGER );
> $sth->bind_param( 5, @$_->[4], SQL_CHAR );
> $sth->bind_param( 6, @$_->[5], SQL_VARCHAR );
> $sth->execute();
> $dbh->commit();
> };
> if ( $@ ) {
> 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)=20
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(@$_);

Regards,
Philip

RE: datatype error

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

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@dms.myflorida.com
> To: ucantspamthis@hotmail.com; dbi-users@perl.org
>=20
> -----Original Message-----
> From: Craig Metzer [mailto:ucantspamthis@hotmail.com]=20
> Sent: Thursday, July 26, 2007 6:36 PM
> To: dbi-users@perl.org
> Subject: datatype error
>=20
> 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
> =20
> I'm trying to run an insert ... the date value is giving me errors.
> Here's the code:
> =20
> =20
> my $sql =3D qq{ INSERT INTO perfdata VALUES ( ?, ?, ?, ?, ?, ? ) };
> my $sth =3D $dbh->prepare( $sql );
> for ( @record ) {
> eval {
> $sth->bind_param( 1, @$_->[0], SQL_VARCHAR );
> $sth->bind_param( 2, @$_->[1], SQL_VARCHAR );
> $sth->bind_param( 3, @$_->[2], SQL_DATETIME );
> $sth->bind_param( 4, @$_->[3], SQL_INTEGER );
> $sth->bind_param( 5, @$_->[4], SQL_CHAR );
> $sth->bind_param( 6, @$_->[5], SQL_VARCHAR );
> $sth->execute();
> $dbh->commit();
> };
> if ( $@ ) {
> warn "Database error: $DBI::errstr\n";
> $dbh->rollback(); #just die if rollback is failing
> }
> }
> $sth->finish();
> =20
> where record is:
> =20
> push @record, [$src_swt, $dst_swt, otime(), $latency, $reachability,
> $path];
> =20
> 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";
> }
> =20
> results in:
> SQL type 9 for ':p3' is not fully supported, bound as SQL_VARCHAR
> instead at ./atmping.pl line 124. =20
> DBD::Oracle::st execute failed: ORA-01861: literal does not match format
> string=20
> ...
> =20
> I also tried 24 hr time format resulting in the same error:
> ...
>=20
>=20
> Not sure why you are doing "eval"... plus I've rarely had to bind with
> the "type" for Oracle...=20
>=20
> 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...=20
>=20
> Example:
>=20
> ...
> $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...
>=20
> $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;
> }
>=20
> Not tested...
>=20
> Hope this helps...
>=20
> jwm

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

RE: datatype error

--_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.
=20
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:
=20
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.
=20
I chose the latter, added one line of code after I made the DB connection (=
applied to my DBI object)
=20
$dbh =3D DBI->connect( .....);
$dbh->do('ALTER SESSION SET NLS_TIME_FORMAT =3D "YYYYDDMM HH24:MI:SS"');
....
=20
Then instead of using the SQL_DATETIME type, I used the SQL_VARCHAR:
=20
from:=20
$sth->bind_param( 3, @$_->[2], SQL_DATETIME );
=20
to:
$sth->bind_param( 3, @$_->[2], SQL_VARCHAR );
=20
Thanks again,
Craig



> Subject: RE: datatype error> Date: Fri, 27 Jul 2007 08:06:10 -0500> From:=
PCAPACIO@amfam.com> To: ucantspamthis@hotmail.com> CC: dbi-users@perl.org>=
> >-----Original Message-----> >From: Craig Metzer [mailto:ucantspamthis@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 ( @record ) {> > eval {> > $sth->bind_param( 1, @$_->[0], SQL_=
VARCHAR );> > $sth->bind_param( 2, @$_->[1], SQL_VARCHAR );> > $sth->bind_p=
aram( 3, @$_->[2], SQL_DATETIME );> > $sth->bind_param( 4, @$_->[3], SQL_IN=
TEGER );> > $sth->bind_param( 5, @$_->[4], SQL_CHAR );> > $sth->bind_param(=
6, @$_->[5], SQL_VARCHAR );> > $sth->execute();> > $dbh->commit();> > };> =
> if ( $@ ) {> > 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 () {> 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_--