Issues

ZF-905: Create a Zend_Db adapter for ODBC

Description

Create a new adapter that supports the PHP ODBC API.

Comments

Assigning to Darby.

Correcting mistake in Summary field.

Sorry for the switcheroo - I thought I was looking at a different JIRA issue.

How far is the status for this issue ?

Actually there is no way to connect to a problematic mssql database where only odbc works like stated within PHP's own PDO documentation.

Do we have any taskplan for realisation ?

I attached an ODBC system Adapter for PDO. Not fully tested, but connects, and processes queries.

I tested it only with systemdns. Set the option 'dbname' for the database to connect to. Not listed connections should also work, but I didn't test it.

Any response would be great. I could add this to incubator if others are willing to test it also.

Yes, we have been wanting an ODBC adapter for some time, and this would be a great start. I would be willing to test this, definitely. Since this is a DB adapter, no proposal is needed, according to the historical record. Thanks, Thomas!

Hidden costs: there exists a great variety of ODBC drivers that may have subtle incompatibilities or specialized options. Need to test with unixODBC and FreeTDS as well as Windows ODBC driver manager.

I don't know what you mean with hidden costs about the code I added but related to incompatibilities the only working solution in my eyes would be to add an table to the documentation with additional data.

We would have to define a sort of testbed which will be tested against different database connections. So the result could look like this:

DB / Select / Order / Left Join / Transaction Mssql / X / X / X / --- Access / X / X / --- / ---

Wil copied and pasted the "hidden costs" comment from an email I sent him some time ago.

I meant the cost of testing multiple configurations, and supporting them. Some people will try to use the ODBC adapter with unsupported brands of RDBMS. This may become a growing support cost to respond to these users, troubleshoot their issues (i.e. install a strange brand of RDBMS to reproduce the user's issue), and enhance the Zend_Db_Adapter_Odbc to satisfy them.

How many brands of RDBMS back-end should be included in the test suite? Should you test ODBC connected to Microsoft SQL Server only, or also test ODBC connected to MySQL, Oracle, PostgreSQL, DB2, and SQLite? Given the current architecture of the Zend_Db tests, this will be complex, because different tests need to be skipped depending on the SQL behavior of the back-end. If you test only MS SQL Server, how will you respond to a user who reports a bug when using Zend_Db_Adapter_Odbc connecting to Oracle?

Regarding incompatibilities or specialized options, I meant things like driver-specific connection properties and driver-specific extensions, etc. Not necessarily SQL features supported by the RDBMS. Character set support is a good example, because some different RDBMS use different names for the same charsets.

Well I don't know how ODBC works under Unix but under Windows you normally define systemdns and you will not have any additional connection properties because you define them within the ODBC manager and not within your PHP code.

We are able to connect to undefined dbs like shown within PHP's documentation, but we are not able to provide informations to connect to all possible ones. Maybe some examples but this is basically a PHP problem and not a ZF one.

Same thing with supported features. This is why I said to add a chapter to the documentation describing which dbs have been successfully tested and what won't actually work.

I would not want to add over 100 different ODBC connection tests... it would be better to have the testsuite configurable to everyone could test his ODBC connection. Complete testsuits would only be done for the main RDBMS.

Anyway... if all is too much complicated I would propose not to include the ODBC adapter and let him in the labratory.

I just can say that I was not able to connect with any adapter to my MSSQL database... this was the reason to create the ODBC adapter. And I think that several other out there would like to have this adapter included.

Yes, the MS SQL Server compatibility is the primary motive for implementing an ODBC adapter. We talked to a developer at Microsoft and she admitted that the pdo_mssql driver has issues and it is not being maintained any more.

I recommend designing the unit tests for the ODBC adapter with the assumption of a connection to MS SQL Server. In the documentation, describe this as the purpose for the ODBC adapter. Also state in the doc that using the ODBC adapter with any other RDBMS is not officially tested or supported. No need to write a comprehensive compatibility chart, because this would be a very long document.

That's my feedback for what it's worth.

I like what Bill says here. Any objection to moving toward resolution this way?

I agree because the MSSQL Server was my intention to begin the development for this adapter.

So we would just support a way to connect also with non-configured RDBMS additionally to System-DSN. But Zend_Db would only be tested and officially working with MSSQL. Other RDBMS would be good but are not the goal of this adapter.

Isn't the future of connecting to SQL Server from PHP the driver that was announced at ZendCon? http://microsoft.com/downloads/details.aspx/… If MSSQL Server access is the issue, why not build an adapter on top of this driver?

I could still see an ODBC driver like the one that Thomas has put together, but I'm very reluctant to add anything to core with such a large caveat (it's only been tested against some databases and db configs). I think it's obviously a highly useful addition, but AFAIK we haven't had to make such a caveat before and it erodes the high-quality claim of ZF core somewhat; if we proceed, I would want to discuss among the team and community whether it's more appropriate for lab or incubator.

Setting the Target Release and Fix Versions to None and Unknown. Progress on the implementation of this feature should definitely continue- especially as it seems to be a very popular one- but we should wait until it has been decided to include it in core to set release versions.

Well... I didn't know that driver. It's too new

But there are some problems :

  • It is only for test purposes... explicit not for production environment so for most users not acceptable
  • It does only support MSSQL 2005... so for the often used MSDE or MSSQL 2000 Server we would not have a solution
  • It is not deployed with PHP... it is a third-party driver
  • It is server-exotic... does not work with PDO but introduces once again new commands like the old depreciated way in PHP4 :-(

And related to ODBC... ODBC was created to support almost any database which has an connector to it. I don't see this as problem. But I can live with anything you decide.

But there may be a solution... we could rename it to ODBC_MSSQL or something and allow only to support mssql through the driver-directive.

Unassigned as help was not accepted from the main authors.

Please categorize/fix as needed.

Some notes for others:

For dynamic ODBC DSNs for SQL Server at least, you need a $dsn returned from _dsn() of the form:

"odbc:DRIVER={SQL Server};UID=username;PWD=password;DATABASE=databasename;SERVER=(local)"

Also, in describeTable(), I needed to add ``` after the first call to fetchAll() (3rd line of non-comment code in the function) to prevent a "Connection is busy with results for another hstmt" error with SQL Server 2000.

Regards,

Rob...

Just for you information:

I've worked sometimes on this issue. The dev-team decided to divide this class in multiple classes so we define WHICH ODBC connection is supported.

The problem is that a ODBC adapter would imply to support ALL ODBC Connections which is not possible. But if we divide this into multiple subadapters, one for each possible RDBMS this is no longer a problem.

/Adapter /Adapter/Odbc /Adapter/Odbc/Mssql.php /Adapter/Odbc/Excel.php /Adapter/Odbc/Access.php

and so on. Keep this in mind before integrating the incubatored class to core. The incubatored class was tested with MSSQL2000 and MSSQL2005.

Greetings Thomas

These are just some notes on this class file while testing. Even though this class file is going to get split up, I'm assuming the functionality currently in incubation will get used in the end resulting class files.

  • So far in my testing, the ODBC adapter breaks the ??Zend_Auth_Adapter_DbTable?? functionality when it trys to quote a value in SQL. This is due to the fact that the PDO_ODBC PHP driver does not implement the ??quote?? method. This method is called in the ??_quote?? method on the ??Zend_Db_Adapter_Pdo_Abstract?? class. This causes the ??Zend_Auth_Adapter_DbTable?? to generate invalid SQL when authenticating.

  • When specifying a database name while passing a ??Zend_Config?? instance to the ??Zend_Db::factory?? method, you have to specify "dbname" (for ??Zend_Db??) and "Database" for the MS SQL Server driver DSN. To me this is a bit redundant. Plus if you specify "dbname", the full DSN is not created in the _dsn() method. It assumes you're using an already created ODBC connection in the connection manager.

  • I ran into a problem with running multiple queries on one connection. This was in a comment on the issue from Rob Allen, where it returns the "Connection is busy with results for another hstmt" error message. In MS SQL Server 2005, you can get around this by specifying ??MARS_Connection="yes"?? in the DSN. In MSSQL 2000, you'd have to close the cursor/statement object after every execution. To get around the DSN problem, I modified the Zend_Db_Adapter_Pdo_Odbc to overload the _checkRequiredOptions() method. I just made the method do nothing. This allowed me to get a full DSN generated when connecting.

I hope this helps.

AFAIK the quoting issue is related to php/ext/pdo_odbc/odbc_driver.c : odbc_handle_quoter() which is not implemented as of php 5.2.6. A quick'n' dirty solution, and not even fully tested, is to overload quote() and quoteInto() methods into Odbc.php itself.

As an example I'm attaching a new Odbc.php with these overloads.

Support for quote() and quoteInto()

Reassigning as Ralph is the maintainer of Zend_Db

Related to issue ZF-3493, I provided a patch there for odbc support in the existing mssql adapter and it seems to work, although I did not do extensive testing, so this is very vague.

However, the existing Mssql adapter already supports different drivers. We could do this for any adapter: if you want odbc, specify odbc as the pdoType. Just an idea anyway, given al your considerations above, I feel this might not be feasible.

Why is now N/A priority?

Since the release of SQL Server Driver for PHP, it seems like the primary reasons for attempting to include odbc support have diminished. Is there any reason why we should persue this in light of the Zend_Db Sqlsrv adpater?

-ralph

The only reason one could want ODBC support is if one could use this from a linux box to connect to a remote windows box SQL server instance. On a windows box itself, de SQL Server Driver is the prefered option.

Correct, the SQL Server driver provided by Microsoft is only supported on Windows boxes. Microsoft is deprecating the use of dblib drivers (i.e. unicode field results refused by server to dblib clients).

On Linux boxes, the only options are dblib and ODBC. ODBC is still the preferred option in Linux. That is until Microsoft ports the driver to Linux. Unless there is some reason to believe that such a port is on the horizon, I believe there is still reason to pursue a PDO/ODBC adapter.

On a personal note, I do have a stake in this adapter.

An ODBC adapter would also be useful for other RDBMSs besides SQL Server -- for example IBM DB2. You have to pay for a driver from IBM to access DB2 through PDO IBM. I have rigged up my own adapter for this purpose, based on the work already started here, although it is pretty weak and I would love to be able to use an official ZF adapter.

ok, thats a valid point.

So I need some background. Assuming I am on *nix or mac, and want to enable pdo/odbc, what set of technologies do I have to have installed? How do I get a working stack that will be capable of talking to sql server on windows?

-ralph

Eugene: why cant you use the IBM DB2 RTCL for the client library, thus use the Zend_Db_Adpater_Db2 adpater?

Ralph: AFAIK, the only technologies required (besides those you've already assumed) are:

ODBC Driver Manager

MSSQL Specific ODBC Driver

PHP PDO ODBC module installed

You might find some useful information, and drivers, in these places: http://www.sommarskog.se/mssqlperl/unix.html http://www.sqlsummit.com/ODBCVend.HTM http://www.unixodbc.org/drivers.html

As mentioned in that first link, most of the drivers (at least the good ones) are commercial drivers. However, you may find that most of the vendors offer free trials.

Ralph, Unfortunately because the host machine is an AS/400, it's my understanding that DB2 Connect is required. Looks like RTCL would work great for DB2 running on AIX or something else though.

I think we should re-evaluate the goals here.

It seemed like originally, the goal was to provide access to MSSQL via PDO/ODBC (via freetds/unixODBC). I've examined this route (and it was painful indeed.) Unfortunately, the freetds through unixODBC doesnt even pass 1/2 of the unit tests we have. The majority of them fail on a casting text to int issue that seems pretty common on the internets.

So, for WinPHP -> SQL Server, we have the Sqlsrv extension which is well maintained and supported by MS. For *nix/PHP -> SQL server, we have PDO_Mssql/Dblib (via freetds) which appears to be fairly robust and by my calculations, passes all of the same tests as Pdo_Mssql (via ntwdblib.dll) running from windows.

Ultimately, I don't think we can implement this inside a single adapter. This is b/c mainly, adapter are tied to a specific vendor implementation. That means that the metadata queries like describeTable(), listTables() and limit() are vendor specific. As you can see inside Pdo_Odbc attached, the limit() is very MSSQL centric. If the goal was to provide different vendor implementations, that would have to be done on an adapter-by-adapter basis. So, if someone wanted to provide a working Pdo_odbc adapter for the say Access, Excel or even DB2 driver; then they would have to propose something like this:

Pdo_Odbc_Access Pdo_Odbc_Excel Pdo_Odbc_Db2 and so on.

These drivers will have to pass a great majority of the unit tests as well as work on multiple platforms.

All that said, this is still open for discussion, but I feel like its coming close to "Won't Fix". I am open to working with anyone on specific adapters, but he above requirements would need to be met, and my current research concerning the state of the underlying drivers leaves me skeptical.

Hi,

Any action on this issue the one related? I'm currently looking for a method to connect to a MSSQL server from a unix host.

Thank you,

Florin

@Florin: I use the adapter Pdo_Mssql with the option pdoType = dblib (http://fr2.php.net/pdo-dblib)

See http://framework.zend.com/manual/en/…

yes, i'm using the same adapter at this moment, but from what i see this is experimental:

http://www.php.net/manual/en/ref.pdo-dblib.php