Future planning

[gregrobson]

This is more of a recommendation/request, rather than an issue.

The Go PHP7 group are looking to make sure that as many extensions as possible are ready for PHP7's release later this year.

Previously the SQL Server driver has been slow to keep up with new releases. Since moving to GitHub there have been a lot of improvements and 5.6 is supported :smile:

It would be really great if SQL Server was supported out of the box when PHP7 is released!

More information about Go PHP7 can be found on their own pages: https://github.com/gophp7/gophp7-ext

[adiramardiani]

whether pdo_sqlsrv for php 7 already available ?

[charles-durrant-tr]

@meet-bhagdev,

We've made our initial port of php_sqlsrv.dll available at:

https://github.com/thomsonreuters/msphpsql/ (it aint pretty!)

It was a perfect Christmas time project and it now meets our very limited needs. We had a lot of problems with memory management related to connections, errors and recordset handles. Primarily due to the MS code storing C structs in ZVALS which looks like it is now a big no to do.

Out of interest are you doing a complete re-write or doing #if PHP7 ()?

Charlie

[meet-bhagdev]

@charles-durrant-tr : Pretty cool project. Looks promising. I will give it a shot tonight! To answer your question, we’re not doing a complete re-write but are upgrading the current code directly because we felt it was quicker and cleaner than having a lot of #ifdef’s everywhere.

[igormx]

this is going to be compatible with linux?

[david-garcia-garcia]

we’re not doing a complete re-write but are upgrading the current code directly

I'm sorry to say that It is worrying to hear this statement at this point.

we felt it was quicker

Sure

and cleaner

I understand this is a typo

Anyways, I really appreciate the effort and are looking forward the PHP7 release.

I guess that if you are sharing the same codebase, some of the fixes will be backported with ease to 5.x and some of the issues in the queue get solved.

[soapergem]

@charles-durrant-tr I tried out your driver today and couldn't get it working. Obviously it's not compatible with 64-bit PHP, but even on 32-bit it doesn't seem to be a drop-in replacement for the PDO drivers and didn't really want to spend the time figuring out which methods would need to change.

Still waiting on @meet-bhagdev and his team to provide the official one.

[robinvda]

@soapergem Note that the driver of @charles-durrant-tr will not port the PDO extension: 1. We are not porting the PDO_SQLSRV extension and targeting only the SQLSRV extension.

I'm also waiting for the official (PDO) driver.

[charles-durrant-tr]

@soapergem

Our driver version is the procedural variant not the PDO variant which is pdo_sqlsrv.dll. We found the pdo driver to be significantly slower than php_sqlsrv.dll. We didn't look a great deal into it though. Additionally PDO has issues with streams - we have a fix pending for PDO (not pdo_sqlsrv) but as yet with many other things we haven't got around to creating a test case.

Also PDO by design encodes dates as strings. Not something we liked, a date is a date not a string. That being said ODBC presents all dates as strings underneath!

Charlie

[charles-durrant-tr]

@david-garcia-garcia

"I guess that if you are sharing the same codebase, some of the fixes will be backported with ease to 5.x and some of the issues in the queue get solved."

We went down the ifdef route for the sole reason for sharing fixes between versions. If MS are creating another version, which it appears they are then the versions <7 will have to have to have fixes applied separately. Note that all the extensions we looked shipped with the core don't use ifdefs and simply created a new source from what we could tell. This doesn't bode well for Drupal use since you need things to be consistent.

Charlie

[charles-durrant-tr]

@igormx

Nope, my advice at this point in time is to use PDO_ODBC and FreeTDS. We couldn't get the MS sql server driver unix ODBC to work effectively. FreeTDS worked for us and was much quicker.

@meet-bhagdev

Why was it that the sql server drivers were not compiled for unix given they just use ODBC underneath?

Charlie

[drewclauson]

@charles-durrant-tr We've had problems with using PDO_ODBC and FreeTDS. Things like zero length string fields being returned as " " and characters like the right and left single quote serializing as unknown characters. However, it may also be because we're using Doctrine. We had to move to serving our PHP on Windows and didn't have much luck with Linux. I'm also wondering why MS didn't release a driver for Linux.

[charles-durrant-tr]

@drewclauson

It's not the easiest setup either, I do applaud the people who wrote freetds though. It was magnitudes faster than MS, I assume due to less checking? Who knows.

" I'm also wondering why MS didn't release a driver for Linux."

I expect it is business decisions based on windows server sales. My main dev wants to port our driver to Linux and call MSs ODBC driver in his own time.

As an aside we have decided to re-factor the ms driver. I know it sounds like a typical developer saying "it would be quicker to rewrite it" but in this case we think the current structure makes fixes and porting harder. Re-factoring would mean than the code can work fof PHP7 and lower without too many ifdefs and reduce dual bug fixes. We'll see how it pans out.

Charlie

[sirio3mil]

Microsoft actually have an ODBC driver for linux: https://msdn.microsoft.com/en-us/library/hh568451(v=sql.110).aspx

[charles-durrant-tr]

@sirio3mil

Yes, I know, it has more issues than freetds in our use cases. See 4th post above.

C

[sirio3mil]

@charles-durant-fr also last v13 preview?

[charliedurrant]

@sirio3mil

It was last official release so yes it could be worth a try with the new one.

Charlie

[david-garcia-garcia]

@charles-durrant-tr

We found the pdo driver to be significantly slower than php_sqlsrv.dll. We didn't look a great deal into > it though.

This was supposed to be fixed by the implementation of client buffered queries:

http://blogs.msdn.com/b/sqlphp/archive/2012/03/07/microsoft-drivers-3-0-for-php-for-sql-server-released-to-web.aspx

But they messed up so much with the implementation of buffered queries (problems with floats, null terminated string, etc..) and it was also buggy so this feature although "usable" is not something you do not want to rely on to build anything serious.

[charles-durrant-tr]

@david-garcia-garcia

What is it you mean by buffered queries and does this relate to:

http://www.drupalonwindows.com/en/blog/benchmarking-drupal-7-php-7-dev

From Wez Furlong's presentation about Buffered Queries were simply result->fecthAll:

http://wezfurlong.org/images/PDO.pdf

from

http://stackoverflow.com/questions/578665/php-pdo-buffered-query-problem

Nothing more different than calling ->fetch in a loop apart from the overhead of the php interpreter calls.

What were the issues explicitly?

Charlie

[nickmccally]

@meet-bhagdev Any update on this? I work for a large organization that works very closely with your company and haven't heard any updates.

[meet-bhagdev]

@nickmccally : Still working on it very very hard. Expect to see something from us by the end of the month.

[nickmccally]

@meet-bhagdev I appreciate the feedback. With almost a year of lead time to start developing what caused the lag? This will just be for a beta correct? Do you guys have any expectations for a productionized product? I'm just curious because it looks like this project has been delayed several times, and I want to better go back to my team with commitment on upgraded infrastructure timelines. I know you most likely work as part of a large team, and this is no personal attack. Just trying to get things ready so we can still utilize sql server as our primary database-- instead of looking for alternative solutions.

[nickmccally]

@meet-bhagdev with the month ending in 4 days can you guys make any commitment to a more specific deadline? Our project decision items will be finalized by Monday, so this will help me understand if we need to work towards a new solution or if we will have something to begin testing. Thank you

[david-garcia-garcia]

@nickmccally I would not count on this for production - if it ever happens - for at least the next 6 months. For example, the Wincache PHP7 extension was released on october 2015 and bugs are still being solved. It all depends on how much testing and feedback this thing gets after release, and how responsive the maintainers are at processing that feedback.

[meet-bhagdev]

@nickmccally We are aiming to get the binaries and source on Github on Friday 1/29. Just wrapping up some final testing.

@david-garcia-garcia We would love for you to test this hard and let us know what we need to fix, especially any PHP7 specific issues.

[brainfoolong]

@meet-bhagdev Really looking forward. We want to drop PHP 5.6 as fast as possible. Only reason keeping it is this driver.

[joe-udwin-lrn]

@meet-bhagdev, I am sure @david-garcia-garcia has a vested interest in improving the quality of the driver but as he is a consultant perhaps you can partially acquire his services to assist you? Having a tighter feedback loop will make the QA / fix cycle more efficient.

If we have to wait 6 months for a robust SQL Server driver there will be a number of projects who ditch SQL Server which will mean lost revenue for Microsoft.

[charles-durrant-tr]

@Joe-U-Questionmark

Your comment is spot on. No matter how many test cases you write having a live application to execute to test the driver is best.

Our driver is coming on very well and our refactoring is making the code cleaner and simpler (well we think so!).

Looking forward to seeing the MS version though - it's not an easy task hence our refactor. The MS code didn't port well - just bad luck in the way the PHP core changed for V7.

Charlie

p.s. "If we have to wait 6 months for a robust SQL Server driver " - in my opinion I think it will be the same for PHP7 as with any new tech.

[tschallacka]

@meet-bhagdev One day away... will you make the deadline you set yourself? Can you also explain WHY it takes a year to write a driver? Why the delays? Imho a year for a driver for a vastly popular framework, that woudl aid SQL server sales and support fees, is too long, especially for a large company like Microsoft...

If you'd guys have worked via github or even your own open access repository this would have been done much faster I think because people would contribute to it to speed stuff up, freeing up crucial core activities for your team to tackle...

At least it's something you guys share end code...

[tschallacka]

And eh... will mssql finally support UTF-8? that would be nice... like... really really really nice...

[charles-durrant-tr]

@tschallacka it does support UTF8 - all data is converted to UTF-8

[tschallacka]

Well, I have a utf dependant site running on sqlserver 2012, and it all gets converted internally to windows 1251 or someting like that... gave me a few headbreakers, until I figured out there is no solution for it and that I just had to accept that all german characters turn to gibberish. I have so many catch blocks in my code to check for that and to turn it into the proper text encoding when retrieving from database that it gets tiresome to constantly having to account for it...

[serbanm33]

@charles-durrant-tr Please try to get from DB a Romanian character like "ș", "ț", "ă", and you will see the big Microsoft support on this.

[tschallacka]

using nvarchar does help a bit.. but not greatly... there is still a lot getting lost in translation. Would be cool if the driver would help, or there'd be a fix for sql server to support this WIDELY use thing called utf8 instead of the MUCH HATED windows codepages.

[serbanm33]

I save in DB something like {A}, {B}, etc. instead of ă, ș, etc. and just search and replace this with HTML code for the required character. It makes no deference if I use nvarchar or char. By the way, in mysql driver this is working just fine and I can get the special characters from DB.

[theodorejb]

@tschallacka Connect using the UTF-8 CharacterSet option (see https://msdn.microsoft.com/en-us/library/cc626307.aspx). Unicode characters in nvarchar fields work fine for me when I do this.

[david-garcia-garcia]

We should be thanked that SOMEONE is working on this. How do you know this is not a MS employee doing this in his free time? I believe - I might be mistaken - that neither MSSQL PDO, Wincache or other PHP/Windows related things have official MS endorsement.

Try to file a support ticket with MS regarding this driver (you know you can do so by cashing out some bucks right?) and see what happens.

Pressure should be done on the other side of the funnel. Sign up for Azure and tell customer support that you want to move your xxxx€/month PHP workload to Azure and that you can't due to lack of support/compatiblity. Then they will tell you that you can use use Nix and MySQL and you should answer that to use that you are better off at AWS. That *might yield better results than complaining here (which I appologize for having done before too).

And if you find issues with the driver, prepare sample test scripts and fill a bug in the issue queue. That is a very valuable type of contribution to open source.

After all this is open source and it is up to MS to evaluate the ROI and strategical impact of the efforts put in here.

@meet-bhagdev Looks like there's a lot of people here willing to test the driver and make the necessary effort to provide useful feedback in the form of properly reported bugs with repro scripts. At least they should if they want anything to be fixed because a bug that is not reported is a bug that does not exist. But please, if people take the time to do so, make sure to work on it otherwise they won't submit more feedback :)

[tschallacka]

(not supported in the PDO_SQLSRV driver) guess what i'm using @theodorejb

[nickmccally]

@david-garcia-garcia @meet-bhagdev my organization spends a significant yearly amount with Microsoft contracts. I'll have to look at the plan this year, but I'll reach out to some contacts and see if we can put a formal request in. The Microsoft tower is just across the street from my office in Washington DC/ Chevy chase.

[reyronald]

@nickmccally It seems to me then that you have a good chance of making something official happen. Please take the flag and let us know Microsoft's response, if any.

I'm not taking away credit from this project, but it would be nice to know where does Microsoft stand on this if approached by an organization like yours on the matter. I'm sure everyone here will be very thankful.

[tsaockham]

FYI... another way to get around the UTF-8 character encoding is to send a Hex encoded string (without quotes) for everything that's not a date or numeric, which is then parsed automatically on the SQL server side into varchar. You get some pretty crazy stored procedure params that look like @my_param=0x5ABC123456... and so on...

function mssql_escape($data) { if(is_numeric($data) || validateDate($data) || validateDate($data,'Y-m-d H:i:s') || validateDate($data,'H:i') || validateDate($data,'H:i:s') || validateDate($data,'Y-m-d H:i:s.u') || validateDate($data,'Y-m-d H:i:s.0000000') || validateDate($data,'H:i.0000000') || validateDate($data,'H:i:s.0000000') || validateDate($data,'m/d/Y') || validateDate($data,'d/m/Y') || validateDate($data,'n/j/Y') || validateDate($data,'j/n/Y')) { return "'".$data."'"; } $unpacked = unpack('H*hex', $data); return '0x' . $unpacked['hex']; }

function validateDate($date, $format = 'Y-m-d') { $d = DateTime::createFromFormat($format, $date); return $d != null && $d && $d->format($format) == $date; }

Then if you need to make sure it comes back correctly when do do a query using iconv...

iconv("CP1252", "UTF-8", $data_string_from_select_query_here);

Hope that helps somebody...

[tschallacka]

Uhu, or base64encode your stuff you wish to encode, or escape htmlspecialchars and decodehtmlspecialchars... there are aways around it, but it clutters up your code and is contra intiuitive and ridicilous. Basically all software supports utf8, heck, even notepad supports utf8. The web for westeren countries relies on utf-8 as encoding, IIS sends files as UTF-8 when requested apache sends as UTF-8, ngix sends as UTF-8

[brainfoolong]

Hey guys, sorry if i'm kind a spoilsport, but can we focus on the PHP7 future planning for the SQL driver here? I'm not very interested in all of your coding problems but i want to be up2date with the actual ticket. Now i'm getting tons of mails and notifications for this ticket that are not really related to this ticket.

[nickmccally]

Agreed with @brainfoolong we will lose relevance to the developers staying off topic.

@meet-bhagdev Any update on the progress sir?

[rloone]

https://github.com/Azure/msphpsql/tree/PHP-7.0 seems to be going up now...

[meet-bhagdev]

@david-garcia-garcia @nickmccally @tschallacka @reyronald @charles-durrant-tr and rest. I agree with you that 1 year is a long time to release a new driver. The remark about this not being acceptable is completely justifiable. In the past, we have not done the best we could have to listen to your needs. Going forward, we plan to change that. This includes an early technical preview of the PHP sqlsrv driver for Windows that we released on GitHub today

Myself and the engineers developing the driver work at Microsoft on the SQL Engineering team. We plan on following this release with pdo_sqlsrv for Windows, porting the drivers to Linux, feature completeness, and bug fixes. We will share our progress by publishing new code and binaries incrementally on GitHub every 3-6-weeks.

We sincerely apologize about any inconvenience this has caused and will continue to try our best to avoid repeating this going forward.

If you have any concerns or questions, please reach out to us over email:

• Tony Petrossian | tonypet@microsoft.com | Linkedin: http://bit.ly/1P32wfN
• Meet Bhagdev | meetb@microsoft.com | Linkedin: http://bit.ly/1SPbzH7

Thanks, Meet

[rloone]

Thanks Meet

[leejunkit]

Thanks @meet-bhagdev really appreciate you coming through with your deadline. I personally need PDO support but it's heartening to see the progress.

[nickmccally]

@meet-bhagdev I appreciate your follow up and your dedication to the project. We often love what we do and work late into the night as programmers... your love for what you do shows! Thanks for the commitment to your customers!

[camrada]

Thanks @meet-bhagdev... But like @leejunkit, really need the PDO version...

[david-garcia-garcia]

Awesome to see something released. Also need PDO here.