Re: DBD::ODBC fails after "Execute immediate success with info"

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