Re: Retrieving warnings after executing a MySQL INSERT statement
On 2009-07-28 09:19:05 -0700, David Goodman wrote:
> 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.
The server does not produce an error message. It only produces a
warning. You can see the same behaviour in the mysql command line
client:
mysql> insert into foo(name, age) values('foo2', 'bar2');
Query OK, 1 row affected, 1 warning (0.00 sec)
The query is ok, there is no error, but 1 warning.
mysql> show warnings;
+-+--+---+
| Level | Code | Message
|
+-+--+---+
| Warning | 1366 | Incorrect integer value: 'bar2' for column 'age' at row
1 |
+-+--+---+
1 row in set (0.00 sec)
This is MySQL specific, so you have to check the documentation of
DBD::mysql. And sure enough, the first occurrence of "warning" is:
mysql_warning_count
The number of warnings generated during execution of the SQL
statement.
However, the DBI does know about warnings and information, too:
A driver may return 0 from err() to indicate a warning condition after
a method call. Similarly, a driver may return an empty string to
indicate a ’success with information’ condition. In both these cases
the value is false but not undef. The errstr() and state() methods may
be used to retrieve extra information in these cases.
hp
--
_ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR | eingeprägt stehen: "Ein ewig Rätsel will ich
| | | h...@wsr.ac.at | bleiben, mir und andern."
__/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd
signature.asc
Description: Digital signature
RE: Retrieving warnings after executing a MySQL INSERT statement
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=<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=<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 wrote:
> From: Saccone, Scott
> Subject: Retrieving warnings after executing a MySQL INSERT statement
> To: "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=< 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.edu<mailto: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.
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 wrote:
> From: Saccone, Scott
> Subject: Retrieving warnings after executing a MySQL INSERT statement
> To: "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=< 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.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.
>
