Issue with OleDB using the MySQL ODBC driver

[MJFShark]

I've hit an issue which I think may have changed recently (or at least I think I tested this last month with no issues.) I'm testing the OleDB protocol using the OleDb ODBC connector (provider MSDASQL) and a MySQL DSN/ODBC driver. Using this setup running any query returns a

Debugger Exception Notification:
Project ZeosTest.exe raised exception class EZSQLException with message 'SQL Error: OLEDB Error
Error message: The parameter is incorrect.
Source: Microsoft OLE DB Provider for ODBC Drivers
Code: -2147024809 SQL: select * from after_event_results

The error occurs in function TZAbstractOleDBStatement.ExecuteQueryPrepared and it seems like it's related to the FSupportsMultipleResultSets flag which is True in this case. I temporarily set it to false and I can run queries again. I've also tested with the Firebird ODBC driver and it works well with no changes (it sets FSupportsMultipleResultSets to false.)

Any thoughts appreciated!

-Mark

[EgonHugeist]

Thoughts:
MySQL should perfectly be able to support IMultipleResults. Even if the patch make the driver running, i would start from the premisse there is a "unsupported" parameter set before. i.e. SetOleCommandProperties f.e.

Code: Select all

SupportsMultipleResultSets := DatabaseInfo.SupportsMultipleResultSets

is a DatabasInfo-Property and should be true for MySQL,SQLServer and usually false for all others.
What do you think?

[MJFShark]

Thanks! I went through my older versions and I don't believe I ever had it working with the MySQL ODBC driver, just the Firebird one. I'm going through the params to see if I can find anything that seems out of place, but so far nothing has stood out. Obviously this is a lower priority since it makes no real sense to use the OleDB protocol for this when the Native driver works so well (but I'm looking at using the generic OleDB driver as an ADO replacement so I'm testing it with all the various drivers I have access to.)

-Mark

[MJFShark]

Update. I've also installed an Oracle ODBC driver for testing and that fails in exactly the same way as the MySQL ODBC driver. Both drivers support multiple result sets

Code: Select all

OleDB Protocol:
  Using MSOLEDBSQL:
    SupportsMultipleResultSets = True
    Works well
  Using MSDASQL and MySQL ODBC:
    SupportsMultipleResultSets = True
    Fails on any query
  Using MSDASQL and Oracle ODBC:
    SupportsMultipleResultSets = True
    Fails on any query
  Using MSDASQL and Firebird ODBC:
    SupportsMultipleResultSets = False
    Works well.

Do people use other OleDB drivers besides SQL Server? I'd be interested if those work.

I've done the quick and dirty workaround for TZAbstractOleDBStatement.ExecuteQueryPrepared:

Code: Select all

     if FSupportsMultipleResultSets and (Connection.GetServerProvider = spMSSQL) then begin

and at least that gets queries working, but I haven't tested further. There must be some kind of property related to multiple result set support that is SQL Server specific (just a guess.) I've tried to go through them to see but I haven't found anything conclusive.

-Mark

[marsupilami]

Do people use other OleDB drivers besides SQL Server? I'd be interested if those work.

As far as I remember, they have been tested with MS Sql Server. I seem to remember that SQL Server supports multiple result sets to...

[EgonHugeist]

Hello Mark,
that makes me curious.
Could you debug:
ZDbcOleDBMetadata.pas
-> TZOleDBDatabaseInfo.InitilizePropertiesFromDBInfo
-> DBPROP_MULTIPLERESULTS: fSupportsMultipleResultSets := PropSet.rgProperties^.vValue <> DBPROPVAL_MR_NOTSUPPORTED;

Is there something wrong in my code?

OTH can you please repeat the tests and add an Exit into ZDbcOleDBStatement.pas TZOleDBPreparedStatement.SetOleCommandProperties for the Providers you have trouble with. That should open the results in default mode and my fix the issues..
Btw. thanks for testing!

[MJFShark]

Hi Michael,

Thanks. It appears that TZOleDBDatabaseInfo.InitilizePropertiesFromDBInfo works fine, or at least it seems to work the way I'd think and I've stepped through it many times and haven't seen any weird values or settings that seem out of place (though I don't know what all of them do.) Bypassing SetOleCommandProperties does not appear to change the results that I've seen. I do note that the error doesn't occur on FCommand.Execute, but it happens in FMultipleResults.GetResult:

Code: Select all

        if Assigned(FMultipleResults) then begin
          Status := FMultipleResults.GetResult(nil, DBRESULTFLAG(DBRESULTFLAG_ROWSET),
            IID_IRowset, @FRowsAffected, @FRowSet);
          if Failed(Status) then
            FOleDBConnection.HandleErrorOrWarning(Status, LogExecType[fDEFERPREPARE],
              SQL, IImmediatelyReleasable(FWeakImmediatRelPtr), nil);
        end;

Looking at the MSDN info for iMultipleResults.GetResult I experimented with changing the DBRESULTFLAG_ROWSET flag to DBRESULTFLAG_DEFAULT and it appears to fix the issue. However I've never actually used multiple result sets, so I'm not exactly sure how to test that.

I've tested this change using:
OleDB -> Oracle ODBC driver (Provider=MSDASQL)
OleDB -> MySQL ODBC driver (Provider=MSDASQL)
SQLServer (Provider=MSOLEDBSQL)
(these are the ones I have that support multiple result sets.)

The MSDN info for this flag has:

DBRESULTFLAG_DEFAULT
The type of the returned object is defined by riid or by properties set on the command object. If this is ambiguous, the provider should return a rowset. Prior to OLE DB 2.6, providers were required to return E_INVALIDARG when lResultFlag was not zero. Consumers should not pass nonzero values unless the provider is a 2.6 or later provider and has added support for lResultFlag.

I was really just trying all the options, so there may be a good reason not to make this change that I don't understand. Thanks, as always! Btw, is the "King regards" in your signature on purpose? An inside joke perhaps?

-Mark

[EgonHugeist]

Hello Mark,

i changed the DBRESULTFLAGENUM to DBRESULTFLAG_DEFAULT as suggested: https://sourceforge.net/p/zeoslib/code-0/7112/
All tests passing fine, thanks for pointing this out!

Btw, is the "King regards" in your signature on purpose? An inside joke perhaps?

Nope, not a inside joke, just a lack of english knownledge. Changed now. Thanks again.

Could you repeat the tests? Everything fine now?