On 06/11/13 12:36, Jan Holčapek wrote:
Hi Martin,
3. now run your basic script and send me /tmp/unixodbc.log
attached is the log file you've requested. Please let me know your
findings, thanks!
--Jan
Hi Jan,
Your log shows:
[ODBC][7270][1383740710.126962][SQLExecDirect.c][240]
Entry:
Statement = 0x2266860
SQL = [drop table if exists foo][length = 24 (SQL_NTS)]
[ODBC][7270][1383740710.130936][SQLExecDirect.c][503]
Exit:[SQL_SUCCESS_WITH_INFO]
Here SQL_SUCCESS_WITH_INFO was returned. ODBC specifies that this state should
only be returned if the driver succeeded in performing your action but perhaps
with some notable event like, I did what you asked but I had to change it
slightly, or in this case, I could not drop the table foo because it does not
exist but the net effect is the same - foo does not exist.
[ODBC][7270][1383740710.131029][SQLError.c][352]
Entry:
Statement = 0x2266860
SQLState = 0x7fff72663d80
Native = 0x7fff72663978
Message Text = 0x7fff72663980
Buffer Length = 1023
Text Len Ptr = 0x7fff7266397e
[ODBC][7270][1383740710.131053][SQLError.c][389]
Exit:[SQL_NO_DATA]
We tried to find out the additional information on the statement handle but it
returned nothing.
[ODBC][7270][1383740710.131079][SQLError.c][434]
Entry:
Connection = 0x2097c60
SQLState = 0x7fff72663d80
Native = 0x7fff72663978
Message Text = 0x7fff72663980
Buffer Length = 1023
Text Len Ptr = 0x7fff7266397e
[ODBC][7270][1383740710.131094][SQLError.c][471]
Exit:[SQL_NO_DATA]
We tried again on the connection handle - same result.
[ODBC][7270][1383740710.131122][SQLError.c][514]
Entry:
Environment = 0x2097670
SQLState = 0x7fff72663d80
Native = 0x7fff72663978
Message Text = 0x7fff72663980
Buffer Length = 1023
Text Len Ptr = 0x7fff7266397e
[ODBC][7270][1383740710.131140][SQLError.c][551]
Exit:[SQL_NO_DATA]
we tried again on the environment handle - same result.
As you can see DBD::ODBC tried very hard to find out what the informational msg
was.
At this point DBD::ODBC considered this an error and issued the error that
SQLExecDirect returned SQL_SUCCESS_WITH_INFO but no informational message could
be found.
I believe this is a bug in your ODBC driver.
However, I took the decision in DBD::ODBC to report this an error and some
might argue differently. In this case it is fairly innocuous as you tried to
drop a table which did not exist and afterwards it does not exist - although
what happens if it was an error in your application and the table should have
existed? In other cases it is more clear cut - what if you tried to set a
statement attribute like a cursor to type A and the driver said, I've set a
cursor, but not to type A, I've set it to type B - an app would likely want to
know this.
I can see it might be inconvenient for you the way it is now as your forced to
do something like:
$h->{RaiseError} = 1;
eval {
$h->do(q/drop table foo/);
};
if ($@ && $h->errstr =~ /Unable to fetch information about the error/) {
# assume the drop was ok and the table did not exist
# but that is not really a safe assumption to make
} elsif ($@) {
# it is a real error
}
What should have happened is that the driver returned SQL_SUCCESS_WITH_INFO and an
informational msg was retrieved. execute/do would return success. DBD::ODBC would
have posted an informational msg and if you'd examined $h->err it would contain ''
(the empty string) to indicate an informational msg was available in $h->errstr.
I could be swayed to change this to a warning (which would be less inconvenient
to you since you could disable warnings when you are dropping a table) but I'd
need to be persuaded. This is one of those occasions when I'm damned either
way. If I don't report it as an error I end up debugging peoples ODBC logs only
to tell them their driver is broken and if I do report an error people come
back to me saying why is this an error.
However, to reiterate, I think your driver is broken in the following ways:
1. although it is perfectly reasonable for a driver to behave differently in a
transaction to out of one, yours is inconsistent. In a txn, it reports
SQL_SUCCESS_WITH_INFO and no informational msg, and out it reports
SQL_SUCCESS_WITH_INFO and gives an informational message that your table does
not exist.
2. I'd expect:
drop table foo if not exists;
to be successful, even if foo does not exist - it isn't for you
whereas with
drop table foo;
it would be reasonable to return SQL_SUCCESS_WITH_INFO saying foo did not exist.
BTW, are you sure your db can actually rollback a drop table, and if it cannot,
then why bother doing it in a txn?
Martin
