OleDB protocol for non-SQL Server connections question.

[MJFShark]

I've been using the OleDB protocol driver exclusively for MS SQL Server and it works very well. I ran a test using the OleDB protocol choice but changing the provider to the provider for ODBC (which I normal use through the ADO protocol choice) and got an error when it makes a query of:

SELECT DATABASEPROPERTYEX('', 'Collation') as DatabaseCollation, COLLATIONPROPERTY(CAST(DATABASEPROPERTYEX('', 'Collation') as NVARCHAR(255)), 'Codepage') as Codepage'

Which appears to be a SQL Server specific query (for my test I'm trying the Firebird odbc driver.)

So my question is, is this a bug, or is the OleDB zeoslib protocol specific to SQL Server?

Also, one of the negatives of using the OleDB protocol choice for SQL Server is the generic datatypes shown in the metadata. I wonder if it would be possible to use the DBLib metadata object combined with OleDB specifically for SQL Server? My understanding is that the OleDB driver is now the recommended way to connect to SQL Server (though they appear to keep changing their minds on it.)

-Mark

[marsupilami]

Hello Mark,

the OLEDB driver should work for all databases. Usually this query should only be executed by the OleDB driver if the server can be expected to be MS SQL Server. So maybe this is a bug that comes from bridging over the ODBC layer. Maybe it makes sense to use the ibprovider?
https://www.ibprovider.com/eng/

On the other hand, if you want to use the ODBC driver, why don't you use odbc_w in Zeos? Note: The odbc driver expects a odbc connection string in the database property and not the name of a DSN.

Best regards,

Jan

[marsupilami]

Just out of curiosity: Why do you try to use OLEDB with Firebird instead of using the firebird driver in Zeos?

[MJFShark]

Hi Jan,

I do use the firebird protocol and it's fantastic. I was testing the ADO protocol and found a performance issue that I mentioned in my other post, so then I attempted to use OleDB to see if it helped and found this issue. Basically I was testing differences between using the ADO and OLEDB Zeos protocols. I've been testing all the various protocols and I assumed reporting issue would be one way I can help.

I am planning on checking odbc_w as well, thanks for the tip on the connection string!

-Mark

[EgonHugeist]

Hello Mark,

yet the OleDB (ODBC_A/ODBC_W too) protocol is tested against SQLServer only. It's very stable and fast. Honestly i never had time to test it using other providers. We will add this limitation for the release notes of Zeos8. Later we can invest some time to get the protocols running for other providers too. Most things a logically are prepared already, so it won't be a huge problem doing this. Yet it's a low priority for !me!. Don't hassitate to provide patches to make it work!

p.s. First bugs for other providers removed: https://sourceforge.net/p/zeoslib/code-0/7025/
The select as you mentioned shouldn't happen anymore..

[MJFShark]

Thanks for that fix! The change allows me to test using OleDB as a replacement for the ADO protocol and I can report it is very fast and seems to work well with the Firebird ODBC driver. I do agree with your other post that the ADO protocol could be removed and the OleDB protocol used as a replacement. The main issue I see is that currently the OleDB protocol defaults to spMSSQL for any providers not in the ProviderNamePrefix list and also sets the provider to spMSSQL for the 'MSDASQL' (Microsoft OleDB Provider for ODBC) choice which should be spUnknown (especially since it's re-checked later with your fix.) Here's my patch (just two changes marked with "// Change" that I've been using and I think could make OleDB work in all the cases that currently needs the ADO protocol.

Code: Select all

function ProviderNamePrefix2ServerProvider(const ProviderNamePrefix: String): TZServerProvider;
type
  TDriverNameAndServerProvider = record
    ProviderNamePrefix: String;
    Provider: TZServerProvider;
  end;
const
  KnownDriverName2TypeMap: array[0..13] of TDriverNameAndServerProvider = (
    (ProviderNamePrefix: 'ORAOLEDB';      Provider: spOracle),
    (ProviderNamePrefix: 'MSDAORA';       Provider: spOracle),
    (ProviderNamePrefix: 'SQLNCLI';       Provider: spMSSQL),
    (ProviderNamePrefix: 'SQLOLEDB';      Provider: spMSSQL),
    (ProviderNamePrefix: 'SSISOLEDB';     Provider: spMSSQL),
    (ProviderNamePrefix: 'MSDASQL';       Provider: spUnknown), // Change: OleDB Provider for ODBC.
    (ProviderNamePrefix: 'MYSQLPROV';     Provider: spMySQL),
    (ProviderNamePrefix: 'IBMDA400';      Provider: spAS400),
    (ProviderNamePrefix: 'IFXOLEDBC';     Provider: spInformix),
    (ProviderNamePrefix: 'MICROSOFT.JET.OLEDB'; Provider: spMSJet),
    (ProviderNamePrefix: 'MICROSOFT.ACE'; Provider: spMSJet),
    (ProviderNamePrefix: 'IB';            Provider: spIB_FB),
    (ProviderNamePrefix: 'POSTGRESSQL';   Provider: spPostgreSQL),
    (ProviderNamePrefix: 'CUBRID';        Provider: spCUBRID)
    );
var
  I: Integer;
  ProviderNamePrefixUp: string;
begin
  Result := spUnknown;  // Change default, was spMSSQL
  ProviderNamePrefixUp := UpperCase(ProviderNamePrefix);
  for i := low(KnownDriverName2TypeMap) to high(KnownDriverName2TypeMap) do
    if StartsWith(ProviderNamePrefixUp, KnownDriverName2TypeMap[i].ProviderNamePrefix) then begin
      Result := KnownDriverName2TypeMap[i].Provider;
      Break;
    end;
end;

Thanks! -Mark

[marsupilami]

Hello Mark,

I applied the patch. It will show up on github tomorrow.

Best regards,

Jan

[EgonHugeist]

Thx guys,

i added same logic to ODBC too.
Mark could you provide the Connecton string as an example for everbody?

[MJFShark]

The connection string I'm using for the OleDB protocol choice is just:

Provider=MSDASQL.1;Persist Security Info=False;Data Source=MyDSN

MSDASQL is the "Microsoft OleDB Provider for ODBC" and the DSN I'm using is a System DSN setup using the Firebird ODBC driver. Let me know if I've misunderstood your question.

-Mark