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

Posted by Thomas Weidner (thomas) on 2007-10-29T18:12:36.000+0000

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 ?

Posted by Thomas Weidner (thomas) on 2007-10-30T02:08:26.000+0000

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.

Posted by Darby Felton (darby) on 2007-10-30T06:12:43.000+0000

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!

Posted by Wil Sinclair (wil) on 2007-11-05T16:29:55.000+0000

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.

Posted by Thomas Weidner (thomas) on 2007-11-06T13:22:31.000+0000

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:

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.

Posted by Thomas Weidner (thomas) on 2007-11-06T15:07:17.000+0000

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.

Posted by Bill Karwin (bkarwin) on 2007-11-06T16:06:55.000+0000

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.

Posted by Darby Felton (darby) on 2007-11-06T16:15:40.000+0000

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

Posted by Thomas Weidner (thomas) on 2007-11-07T01:32:20.000+0000

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.

Posted by Wil Sinclair (wil) on 2007-11-08T21:18:19.000+0000

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.

Posted by Wil Sinclair (wil) on 2007-11-08T21:23:10.000+0000

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.

Posted by Thomas Weidner (thomas) on 2007-11-09T00:56:27.000+0000

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.

Posted by Thomas Weidner (thomas) on 2007-11-22T03:37:29.000+0000

Unassigned as help was not accepted from the main authors.

Posted by Wil Sinclair (wil) on 2008-03-25T20:43:51.000+0000

Please categorize/fix as needed.

Posted by Rob Allen (rob) on 2008-04-08T05:39:46.000+0000

Some notes for others:

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

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...

Posted by Thomas Weidner (thomas) on 2008-07-21T23:45:15.000+0000

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.

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

Posted by Lucas Thomas (ljthomas) on 2008-07-31T11:22:30.000+0000

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.

Posted by Gil Gates (gatesg) on 2008-10-15T06:27:18.000+0000

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.

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.

Posted by Yanko Heinze Yslas (yaheys) on 2009-08-21T13:49:17.000+0000

Why is now N/A priority?

Posted by Ralph Schindler (ralph) on 2009-09-01T08:19:26.000+0000

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?

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.

Posted by Adam VanBerlo (nix0n) on 2009-09-01T08:43:29.000+0000

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.

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.

Posted by Ralph Schindler (ralph) on 2009-09-01T09:03:53.000+0000

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

Posted by Ralph Schindler (ralph) on 2009-09-01T09:10:07.000+0000

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

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.

Posted by Ralph Schindler (ralph) on 2009-09-03T12:59:28.000+0000

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.