search

mevdschee / php-crud-api

Single file PHP script that adds a REST API to a SQL database
MIT License
3.59k stars 1.01k forks source link

Add support of MSSQL ODBC #569

Open jtoberling opened 5 years ago

jtoberling commented 5 years ago

As of first congrats for this package - I'm using it quite often & it's a great help.

I'd like to ask a feature request - add support for MSSQL DB. The main official mode of connection recommended by MS is ODBC/PDO. https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017#microsoft-odbc-driver-17-for-sql-server

As far as I've tested it could be pretty the same as 'sqlsrv' driver but the following differences:

Sorry not sending as direct pull request - but I'm new contrib on github, whoever would be happy to help with testing.

mevdschee commented 5 years ago

The main official mode of connection recommended by MS is ODBC/PDO.

Is it? Can you provide a reference? It seems it is SQLSRV, see:

https://docs.microsoft.com/en-us/sql/connect/sql-connection-libraries?view=sql-server-2017

jtoberling commented 5 years ago

You can find some history under the below article: https://docs.microsoft.com/hu-hu/sql/connect/connect-history?view=sql-server-2017

One of the sentences say: "... After SQL Server 2012, the Microsoft ODBC Driver for SQL Server is the driver that is updated with the most recent server features going forward. .."

In some of my production project we were advised to use the ODBC driver with PHP - hence I'm asking.

Anyway I'm trying to fork and put together a pull req for ODBC support .

mevdschee commented 5 years ago

After SQL Server 2012, the Microsoft ODBC Driver for SQL Server is the driver that is updated with the most recent server features going forward.

You make it sound like that is a comparison with SQLSRV, but it is not. It is a comparison with "SQL Server native client". Again it seems like you want me to believe using ODBC in PHP is a good choice.

In some of my production project we were advised to use the ODBC driver with PHP

Who did? And why? That seems like an odd advice to me. It seems it is not in line with Microsoft's advice.

Anyway I'm trying to fork and put together a pull req for ODBC support .

Why don't you instead switch to SQLSRV (afaik the drivers can co-exist)? It is the most popular driver and recommended by Microsoft for usage in PHP.

jtoberling commented 5 years ago

As I see SQLSRV is on overlay above MS native ODBC client - hence just an additional layer onwhere MsODBC is a prerequisite. ( Pls. ref: https://docs.microsoft.com/hu-hu/sql/connect/php/step-1-configure-development-environment-for-php-development?view=sql-server-2017)

The recommendation was by MS Client reps a few years before. They suggested to use ODBC as it had the feature our environment was require for HA clusters/failover and RLS. ( To be honest I did not check if the mentioned features are available on SQLSRV nowadays. )

Another point was on ODBC Driver that you were not required to get it through pecl. According to client's policy self-compilation of production packages were raising issues - as the extension was neither coming from the distro's core repo nor from MS it was outside of standard maintenance scopes. So choosing a MS distributed binary ODBC driver for linux was not a bad choice anyway at least regarding warranty/maintenance.

By providing sort of generic ODBC support (that could enable mssql access also) I think is not against pdo_sqlsrv but also could be an enabler for other drivers that has no native pdo possibility.

mevdschee commented 5 years ago

They suggested to use ODBC as it had the feature our environment was require for HA clusters/failover and RLS.

AFAIK SQLSRV supports HA clusters/failover, see:

https://docs.microsoft.com/en-us/sql/connect/php/connection-options?view=sql-server-2017

I'm see no reason why RLS wouldn't work either.

the extension was neither coming from the distro's core repo nor from MS it was outside of standard maintenance scopes

SQLSRV is now (wasn't it always?) coming from MS. See:

https://www.microsoft.com/en-us/download/details.aspx?id=20098

There seems to be no valid reason (anymore) to use ODBC for PHP to connect to SQL Server and it is not the recommended way of working. I think you may be the only user of this feature and thus I will not add it yet, as it will add maintenance costs to this project. I am willing to reconsider and I will leave the issue open in case anyone wants to vote for (or discuss) adding this.

jtoberling commented 5 years ago

I see your points and understand not picking up due to maintenance issues. Considering not to affect main trunk heavily I reshaped my code and achieved supporting ODBC through extended ReconnectMiddleware functionality. If you have some interest you might pick up some parts - as being generic enough might be useful also for other DB's not in support range yet. https://github.com/jtoberling/php-crud-api#support-for-other-db-drivers-eg-odbc----fork-specific

Offtopic, but during testing I was found that latest nyholm/psr7-1.2.0 was failing PHP 7.0 tests - as using PHP7.1 class private constants.

mevdschee commented 5 years ago

Offtopic, but during testing I was found that latest nyholm/psr7-1.2.0 was failing PHP 7.0 tests - as using PHP7.1 class private constants.

You can run update.php to patch the vendor directory for PHP 7.0 compatibility.