CODEWITHSUNDEEP
phpzend-frameworksql-server-2000
BILL
BILL

Reputation: 4869

How to connect to MS SQL 2000 from PHP 5.4?

I use ZendFramework and PHP Version 5.4.12, my OS is Windows 7. My parameters for connecting is 

resources.db.adapter = "sqlsrv"
resources.db.params.pdoType = "mssql"

But I have this exception 

Fatal error: Uncaught exception 'Exception' with message '[Microsoft][SQL Server Native Client 11.0]SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions.'

because I use newest php driver.

How to connect to MS SQL Server 2000(version 8.00.760)?

Upvotes: 2

Views: 4603

Answers (2)

Robert Calhoun
Robert Calhoun

Reputation: 5153

I have a handful of databases that are still running SQL Server 2000 and I didn't want to revert to the older driver for everyone else. After some poking around this is the best workaround that I can come up: connect to SQL Server 2000 database using OLEDB via ADO. ADO is a COM component so you need to enable the COM_DOT_NET extension.

I have verified this still works with PHP 5.4 under Windows Server 2008 R2 with Native Client 11 installed.

<?php
$conn =  new COM("ADODB.Connection") or die("Cannot create ADO COM object");
$conn->Open("Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=True;Data Source=LOCALHOST;Initial Catalog=master");
//$conn->Open("Provider=SQLOLEDB;User ID=username;Password=password;Persist Security Info=True;Data Source=host.example.com;Initial Catalog=databasename"); 
$rs = $conn->Execute("SELECT 1 as Number,GetDate() as Date, CAST(0 as bit) as Boolean, 'hello world' as String");
$fieldnames = []; 
$rows = [];
while (!$rs->EOF){
  $fieldnames = []; // wasteful, redoing this each time
  $thisrow = [];
  for ($i=0;$i<($rs->Fields->Count);$i++)  {
    $field = $rs->Fields[$i];
    $fieldname = (string) $field->Name;
    $type = $field->Type;
    // field->Type is the ADO type; due personal weakness I am only handling types I actually need 
    if ($type == 3 || $type == 20) {
      $value = (integer) $field->Value;
    } elseif ($type == 4 || $type==5) {
      $value = (float) $field->Value;
    } elseif ($type == 11) {
      $value = (boolean) $field->Value;
    } else {
    $value = (string) $field->Value;
    }
    $thisrow[] = $value; //array by index
    $thisrow[$fieldname] = $value; // array by name
    $fieldnames[]=$fieldname;
  }
  $rows[] = $thisrow;
  $rs->MoveNext();
}
$rs->Close(); 
$conn->Close();
echo "your fieldnames are, in column order:\n";
var_dump($fieldnames);
echo "your data is, in record order:\n";
var_dump($rows);
?>

Notes:

  • This only works for PHP under Windows.
  • You need to enable the PHP COM_DOT_NET extension. Ugg. See this post on how to do it.
  • This post at devnetwork.net was essential in showing how to use ADO under PHP.
  • The trick with using ADO is that the data is returned back as a COM Variant. The fastest way to use ADO is calling GetRows() and casting the variant returned as a 2D array of variants and going from there, but I could not get PHP to consume that. (This is "DrDev's" problem in aforementioned link.)
  • To avoid problems, I walk through each field of each row and explicitly cast to a data PHP type. (This could be done better, but these were all the ADO types I cared about.)

To-do: parameterized queries. How I wish I could just get these hosts updated to SQL Server 2012 Express Edition instead...

Upvotes: 3

sdespont
sdespont

Reputation: 14025

Because you are using the version 2012 of the SQL Server Native Client (version 11) which no longer support the version 2000 of your SQL Server. Yes, it is a shame.

To fix that, you should download an earlier version that you can found here : http://www.microsoft.com/en-us/download/details.aspx?id=16978

Look for the section

Microsoft® SQL Server® 2008 R2 Native Client

It is still not working, you need to uninstall the 2012 version first.

If it still not working, have a look here : http://www.sqlservercentral.com/Forums/Topic1317581-2799-1.aspx

Upvotes: 1

Related Questions