David,
Thanks for the suggestion. It seems that $DBI::errstr is indeed empty after
the INSERT statement that attempt to insert a string into an int column
(example + output is below). One solution to this to not use DBI, and instead
use the LOAD DATA statement via a Perl system statement with output directed to
a file. That way I can keep track of errors when data is inserted into the
database.
--
EXAMPLE:
my $table='people';
my $schema=EOF;
CREATE TABLE $table (
name varchar(32) NOT NULL,
age int NULL
);
EOF
$dbh-do(DROP TABLE IF EXISTS $table) or die Error dropping table $table:
$DBI::errstr;
$dbh-do($schema) or die Error initializing table $table: $DBI::errstr;
my $insert=EOF;
INSERT INTO $table VALUES (Bob,abc);
EOF
$dbh-do($insert) or die Error inserting data into table $table: $DBI::errstr;
print Successful do operation: $insert;
defined $DBI::errstr ? print DBI::errstr=$DBI::errstr\n : print DBI::errstr
is not defined\n;
print Goodbye\n;
exit 0;
OUTPUT:
Successful do operation: INSERT INTO people VALUES (Bob,abc);
DBI::errstr is not defined
Goodbye
--
Scott
-Original Message-
From: David Goodman [mailto:dtzgd...@yahoo.com]
Sent: Tuesday, July 28, 2009 11:19 AM
To: dbi-users@perl.org
Subject: Re: Retrieving warnings after executing a MySQL INSERT statement
Hello Scott:
I suggest that you check to see if the string $DBI::errstr is empty rather than
just whether the 'do' function executed correctly.
It seems that the SQL is correctly submitted from the DBI side but the database
server actually produces an error message. So this is a server side error
message rather than a client side error message.
regards,
David
--- On Tue, 7/28/09, Saccone, Scott ssacc...@wustl.edu wrote:
From: Saccone, Scott ssacc...@wustl.edu
Subject: Retrieving warnings after executing a MySQL INSERT statement
To: dbi-users@perl.org dbi-users@perl.org
Date: Tuesday, July 28, 2009, 11:19 AM
Hello, I was wondering if the DBI can determine if there
are warnings after an INSERT statement. For example,
here's some MySQL code:
mysql INSERT INTO test(id,name,age) VALUES
(1,Bob,abc);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql show warnings;
+-+--+--+
| Level | Code | Message
|
+-+--+--+
| Warning | 1366 | Incorrect integer value: 'sdc' for
column 'age' at row 1 |
+-+--+--+
1 row in set (0.00 sec)
If I do the same thing in the DBI, I don't seem to see any
errors or warnings raised:
$code=EOF;
INSERT INTO test(id,name,age)
VALUES(1,Bob,abc);
EOF
$dbh-do($code) or die Error loading data:
$DBI::errstr;
Thanks,
Scott
Scott Saccone, Ph.D.
Department of Psychiatry, Box 8134
Washington University School of Medicine
660 South Euclid Avenue
Saint Louis, Missouri 63110-1093
Voice: (314) 286-2581
FAX: (314) 286-2577
Email: ssacc...@wustl.edumailto:ssacc...@wustl.edu
The materials in this message are private and may contain
Protected Healthcare Information or other information of a
sensitive nature. If you are not the intended recipient, be
advised that any unauthorized use, disclosure, copying or
the taking of any action in reliance on the contents of this
information is strictly prohibited. If you have received
this email in error, please immediately notify the sender
via telephone or return mail.
The materials in this message are private and may contain Protected Healthcare
Information or other information of a sensitive nature. If you are not the
intended recipient, be advised that any unauthorized use, disclosure, copying
or the taking of any action in reliance on the contents of this information is
strictly prohibited. If you have received this email in error, please
immediately notify the sender via telephone or return mail.
