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

On 06/11/13 14:36, Jan Holčapek wrote:
> Hi Martin,
>
> On Wed, Nov 6, 2013 at 3:05 PM, Martin J. Evans <[email protected]> wrote:
>> I believe this is a bug in your ODBC driver.
>
> I kind of expected that. I'll file a bugreport to Vertica Support.

good, that was part of what I was hoping to achieve when I did this.

>> 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.
>
> IMO current behavior is correct.
>
>> 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
>> }
>
> I've already adjusted the code with this, I also narrowed the
> if-condition with $dbh->state eq 'IM008',
> as I'm getting this state along with the error. Not that it's neat,
> but allows me to work
> around this driver issue for the moment.
>
> BTW I found out IM008 is returned by DBD::ODBC, then checked
> http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#IM008
> and must admit I can't see a relationship between the actual error and
> the state.

hah, my own document comes back to haunt me.

You are right, it is down to:

     if (!error_found && (err_rc != SQL_NO_DATA_FOUND)) {
         if (DBIc_TRACE(imp_xxh, DBD_TRACING, 0, 3))
             TRACE1(imp_dbh, "    ** No error found %d **\n", err_rc);
         DBIh_SET_ERR_CHAR(
             h, imp_xxh, Nullch, 1,
             "    Unable to fetch information about the error", "IM008", Nullch);
     }

and I'm guessing a cut and paste. DBD::ODBC has to invent a state here and I guess it should be HY000. If I were you I'd not rely on IM008 as now you've pointed it out I may change it to HY000 so you might be better looking for IM008|HY000 or "No error found" in errstr. I've just added a comment to the source, not to change that string.

  
>> 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.
>
> Agree.
>
>> BTW, are you sure your db can actually rollback a drop table, and if it
>> cannot, then why bother doing it in a txn?
>
> This is due to the code being used to handle data both in PostgreSQL database
> (which supports transactional DDL) and Vertica (which does not support that).
> We are likely about to consider changing the application logic.
>
> Thank very much for the explanation!
>
> Cheers
> Jan
>

np.

Martin