SQL Server Driver for PHP Forum

Hint: querying stored procedures with php driver 1.1 with output parameters

Fri, 20 Nov 2009 12:35:05 Z

I had a nasty problem for the last two days. I have some stored procedures on my sql server 2008 with (interger) output parameters.
I was able to query those procedures without any error, but I wasn´t getting back any result values in my output parameters.

I now have a solution I want to share with this forum.

This problem occurs if inside the stored procedure are any nested queries results. You can see if there are additional results inside the procedure by testing the stored procedure in SQL Management studio and getting back several "XXX rows affected" lines in the messages window.

If you follow the PHP driver Programming Guide for Directional Parameters you will fail in the described situation.

The source of the problem is how the sql server protocol is handling the parameter transfer:
http://msdn.microsoft.com/en-us/library/ms403283.aspx

The problem is, the parameters are transfered as the last packet over the network. The driver is traversing the results one by one, with the ouput parameters read AFTER all other results have been read. Each "XXX rows affected" will become a result to be (manually, triggerd) read from the server - if you read this "garbage" resuls with sqlsrv_fetch_array or similar you will see something like "the result set contains no data" as an returned server error.

You just have to skip this "garbage" to get your output parameters as results. You can skip those with the following commands:

        $tsql_callSP = "{call dbo.aStoredProcWithOutputParam(?)}";

        $params = array(
              array (
                 $outputvar, SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_INT, SQLSRV_SQLTYPE_INT,
               )
            );

        $stmt = sqlsrv_query( $con, $tsql_callSP, $params);

        --- Now you have to skip all garbage
        $loop = true;
        while ($loop)
        {
          $loop = sqlsrv_next_result( $stmt );
        }

         This will iterate through all results until (internally in the driver) SQLMoreResults returns SQL_NO_DATA -> now Output Parameters are ready to be read because the last result data packet has been transfered from the server to the client, which contains the output parameters.

       echo $outputvar

       It is NECESSARY that you loop through with a while clause because there can be several "garbage" results.

       If you don´t do the described iteration, you will not have a result in your output parameter php variables. I am not sure wether this problem only occurs in the case the output parameter is an integer (as described in the Microsoft article "Process Return Codes and Output Parameter"). In my case the output parameters were of an integer type.

Please be careful if you have results from a select command as a stored proc result. Make sure that you don´t skip them if you need them. I doesn´t tested this situation so I just want to warn you that you may get the output parameters but accidentially skip the other results from the stored proc.

Errors appear twice in sqlsrv_errors()

Tue, 24 Nov 2009 17:54:03 Z

First day with SQL Server Driver for PHP...

I'm checking the result of the call to sqlsrv_connect() so I can throw an exception in my code. I enter wrong credentials and I call to sqlsrv_errors() to get the error message. Curiously, I always get the same error messages twice. E.g., a wrong password:

Array

(

    [0] => Array

        (

            [0] => 28000

            [SQLSTATE] => 28000

            [1] => 18456

            [code] => 18456

            [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Error de inicio de sesión del usuario 'sa'.

            [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Error de inicio de sesión del usuario 'sa'.

        )



    [1] => Array

        (

            [0] => 28000

            [SQLSTATE] => 28000

            [1] => 18456

            [code] => 18456

            [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Error de inicio de sesión del usuario 'sa'.

            [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Error de inicio de sesión del usuario 'sa'.

        )



)

Now wrong database:

Array
(
    [0] => Array
        (
            [0] => 28000
            [SQLSTATE] => 28000
            [1] => 18456
            [code] => 18456
            [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Error de inicio de sesión del usuario 'sa'.
            [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Error de inicio de sesión del usuario 'sa'.
        )

    [1] => Array
        (
            [0] => 42000
            [SQLSTATE] => 42000
            [1] => 4060
            [code] => 4060
            [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]No se puede abrir la base de datos "xxxICCL_ITCWEB_WEB" solicitada por el inicio de sesión. Error de inicio de sesión.
            [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]No se puede abrir la base de datos "xxxICCL_ITCWEB_WEB" solicitada por el inicio de sesión. Error de inicio de sesión.
        )

    [2] => Array
        (
            [0] => 28000
            [SQLSTATE] => 28000
            [1] => 18456
            [code] => 18456
            [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Error de inicio de sesión del usuario 'sa'.
            [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Error de inicio de sesión del usuario 'sa'.
        )

    [3] => Array
        (
            [0] => 42000
            [SQLSTATE] => 42000
            [1] => 4060
            [code] => 4060
            [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]No se puede abrir la base de datos "xxxICCL_ITCWEB_WEB" solicitada por el inicio de sesión. Error de inicio de sesión.
            [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]No se puede abrir la base de datos "xxxICCL_ITCWEB_WEB" solicitada por el inicio de sesión. Error de inicio de sesión.
        )

)

My code is pretty straightforward:

<?php

$conn = sqlsrv_connect('server\\instance', array(
	'UID' => 'user',
	'PWD' => 'password',
	'Database' => 'database',
));
if( !$conn ){
	$e = sqlsrv_errors();
	print_r($);
}

?>

Why do they show up twice?

Problem with sqlsrv_num_rows in 'CTE' MSSQL selects. (strange return value -1)

Tue, 24 Nov 2009 10:10:31 Z

Hello

What I want:

I want to get data records from database using „CTE - Common Table Expression”.

Sample CTE (listing 1):

„$sql = ";WITH unsorted_cte AS


(


  SELECT


    *


  FROM


    xxx


  WHERE


    yyy


)


SELECT


  u_cte.*


FROM


  [unsorted_cte] u_cte


ORDER BY


    u_cte.[zzzz] ASC";”

When I test this query in sql console the @@rowcount have proper value, but when I use this select in php script function sqlsrv_num_rows returns strange value -1 . This value is not even in functinon docs http://msdn.microsoft.com/en-us/library/ee376931(SQL.90).aspx.

I need to know the proper amount of returned rows before I will fetch rows data from database.

Sample PHP code (listing 2):

// make query with proper coursor

$result = sqlsrv_query($link, $sql, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET));


// var dump shows us proper resource

var_dump($result);

resource(3) of type (SQL Server Statement)


// sqlsrv_num_rows show us wrong rows count

var_dump(sqlsrv_num_rows($result));

int(-1)


// sqlsrv_errors are empty

var_dump(sqlsrv_errors());

NULL

Whats curious: when we loop the $result object before using sqlsrv_num_rows() function (listing 3):

// make query with proper coursor

$result = sqlsrv_query($link, $sql, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET));

// empty loop with it only iterates $result object with no assigns

while($row = sqlsrv_fetch_object($result)) {

}
// sqlsrv_num_rows show us proper rows amount

var_dump(sqlsrv_num_rows($result));

int(16)

I tried to come around this problem by counting returned rows in empty loop (listing 3) and then in application make another loop with Selecting Rows in a Result Set by SQLSRV_SCROLL_ABSOLUTE or return scroll pointer to first by SQLSRV_SCROLL_FIRST. Unfortunately the only Selecting Rows parameter that works is default SQLSRV_SCROLL_NEXT, so I cant get to rows data again in applications.

My Environment:

PHP 5.3

MSSQL 2005

Windows Server

SQL Server Driver for PHP 1.1 ( http://msdn.microsoft.com/en-us/library/ee229548(SQL.10).aspx )

Database encoding: unicode

Please help me, how can I get proper value of sqlsrv_num_rows() function with CTE queries .

query timing out

Wed, 04 Nov 2009 02:55:45 Z

I'm running a php script from the command line against SQL server 2005 using the MS driver for PHP and getting time outs. The query takes about 2 minutes from SQL Server Management Studio and returns > 300,000 rows. There are multiple queries in the script and for each one I do a sqlsrv_connect(), execute the query and then sqlsrv_free_stmt() and sqlsrv_close() Output from sqlsrv_errors():

Array (
  [0] => Array (
    [0] => 08S01
    [SQLSTATE] => 08S01
    [1] => 258
    [code] => 258
    [2] => [Microsoft][SQL Native Client]Shared Memory Provider: Timeout error [258]. 
    [message] => [Microsoft][SQL Native Client]Shared Memory Provider: Timeout error [258].
  )
  [1] => Array (
    [0] => 08S01
    [SQLSTATE] => 08S01
    [1] => 258
    [code] => 258
    [2] => [Microsoft][SQL Native Client]Communication link failure
    [message] => [Microsoft][SQL Native Client]Communication link failure
  )
  [2] => Array (
    [0] => 08S01
    [SQLSTATE] => 08S01
    [1] => 0
    [code] => 0
    [2] => [Microsoft][SQL Native Client]Communication link failure
    [message] => [Microsoft][SQL Native Client]Communication link failure
  )
)

storing UTF-8 data in VARCHAR or NVARCHAR?

Thu, 19 Nov 2009 08:59:31 Z

I am able to successfully store and retrieve UTF-8 Chinese characters in a SQL Server 2008 database using the PHP driver, version 1.1 (SQLNCLI10.DLL).

According to the PHP driver documentation, using NVARCHAR and "CharacterSet" => "UTF-8" is required for UTF-8. However, I am using neither of these. I am using VARCHAR fields which require half of the disk space.

When I query the database using MSSQL Management Studio, the text looks like junk. However, my webpages display the Chinese characters stored in the database correctly as long as I specifiy <meta content="text/html; charset=utf-8" http-equiv="Content-Type" /> in the header section.

Is the documentation incorrect? Or is what I am doing a hack which will cause problems for me later? Thanks.

Nested stored procedure silent timeout?

Thu, 22 Oct 2009 15:45:38 Z

Hi,

Is there any form of implicit timeout for the execution of a stored procedure via the SQL PHP driver?

I am calling to a stored procedure that updates values that already exist and inserts new ones. For the insert part, there is a nested call to another stored procedure within the first which performs the actual insertion of a data point given a value and some metadata. I am finding that often the script reports success (and there is no sqlsrv_errors() output aside from '1' for success), but checking with the database, only a subset of values have been inserted. If I then run the same stored procedure in management studio on the same machine, all the values are picked up and inserted successfully.

If it helps, I am updating values in the first instance via a single UPDATE statement across a join. The insert part is then a loop through all the rows that are new which are then passed to this nested stored procedure.

I have tried this call using both the raw EXEC syntax and the recommended { CALL xxx } syntax but I see the same problems.

Lastly I am not seeing this same problem via any of my other inline queries (i.e. those not using stored procedures).

I am using SQL Server 2005 and PHP 5.3 with the 1.1 non-thread-safe dll compiled for VC6. I have the latest SQL 2008 native client installed from the April update pack.

Many thanks for any potential help.

Thanks,
-Will

connecting multiple databases with sqlsrv_connect

Fri, 06 Nov 2009 04:07:44 Z

May I know how to connect to different tables from 2 different database instances in in one sqlsrv_connect function? I would like to query 2 different tables, one containing invoice information, and another one containing my customers' billing details. Please help!

For example:

select * from dbase1.invoices a, dbase2.customers b where a.cust_id = b.cust_id

SQL Server Driver for PHP 1.1 is now available

Wed, 19 Aug 2009 21:57:38 Z

SQL Server Driver for PHP 1.1 is now available

SQL Server Driver for PHP 1.1 has released!

The SQL Server Driver for PHP team would like to thank everyone who has provided feedback and bug reports throughout the development cycle for the driver.

This release marks a very big milestone in our continued pursuit for interoperability while also providing support for PHP ver 5.3. This driver will enable developers to build PHP applications with relational database capabilities to both SQL Server as well as SQL Azure databases. There are some key performance improvements as well as new features such as support for UTF-8 encoding and scrollable result sets.

What about the source?

The driver is available for download on the MSDN download site. We encourage you to download the driver and explore the sample application described in the .chm file. If you'd prefer to access our documentation on-line, it's available on the MSDN site. We welcome your feedback, feature requests and bug reports on our MSDN Forum.

What about the source?

Microsoft has published the source code to the driver on the CodePlex site. There, you can download the source code and provide feedback in the form of bug reports and feature requests.

We understand that many developers will download the source code from CodePlex to create their own build(s) of the driver. However, Microsoft supports only the signed version of the driver from the MSDN download site. If you're using a custom built version of the driver and encounter problems, please reproduce the problem with the Microsoft-built driver before contacting Microsoft support.

How will Microsoft respond to feedback and update the driver?

We will be responsive to feedback on the MSDN Forum, Codeplex, and through our blog, weighing in on both feature requests and bug reports.

Apache crashes with An unhadled win32 exception encountered at httpd.exe.

Tue, 03 Nov 2009 11:45:26 Z

Hiii,

I'm using PHP5.2.6 (Thread safe version), with apache 2.2.8 where PHp is running as a module. on a windows server 2003 machine.

I'm using Sql Server driver for PHP.

But while i'm uploading csv files to server and inserting its data into sql server , sometimes i get "Just in time debugger eroor " which states that "An unhandled Win32 exception occurred in httpd.exe".

and after that server stops responding , but after restarting Apcahe few times the error goes off and evrything work smoothly.

The Apache error logs shows

[Mon Nov 02 18:08:19 2009] [notice] Child 5880: Child process is running

[Mon Nov 02 18:08:19 2009] [notice] Child 5880: Acquired the start mutex.

[Mon Nov 02 18:08:19 2009] [notice] Child 5880: Starting 64 worker threads.

[Mon Nov 02 18:08:19 2009] [notice] Child 5880: Starting thread to listen on port 80.

[Mon Nov 02 18:09:06 2009] [notice] Parent: Received restart signal -- Restarting the server.

[Mon Nov 02 18:09:06 2009] [notice] Child 5880: Exit event signaled. Child process is ending.

and on debug it show "faulting module php5ts.dll version 5.2.6, fault address 0x0000afef (which is random address"...

Plz help me sort out the issue as its just freaking me out.....

Plzzzzzzzzzzzzz

Specified driver could not be loaded due to system error 127 (SQL Server Native Client 10.0).

Fri, 18 Sep 2009 16:07:39 Z

My configuration is:
PHP 5.3
SQL Server 2005 Express
SQL Server Driver for PHP 1.1
Microsoft SQL Server 2008's SQL Server Native Client

When I run the sql server connection function in php, i get the following error message:
"Specified driver could not be loaded due to system error 127 (SQL Server Native Client 10.0)."

SQLSTATE: IM003
Code: 160

How can i fix this error?

Visual Studio Data source not found

Wed, 28 Oct 2009 04:07:33 Z

In MS Visual Studio I am getting the message: Data Source was not found, yet when I test my connection, it tells me it succeeded.
What am I doing wrong?

how to solve my problem??

Wed, 21 Oct 2009 02:15:04 Z

i think the problem is from my table... anybody can help me please?
its very important.. the insert query not have a problem because i already test it using command promt and successfull.. after i aplly it into my form.. its cannot function. and this my fisrt new database using.. postgre, using php...

//this my form//
    <form action="f_loan_sport.php" method="post" name="myForm" class="style4">



        <td><p align="center" class="style4"> </p>

          <div align="left">            <br>

    <table width="386" border="0">
              <tr>
                <td width="157">Select Your Item Borrow:</td>
                <td width="124"><select name="choose_equ" id="choose_equ">
                  <option>-SELECT-</option>
                  <option value="B">BADMINTON</option>
                  <option value="BJ">BOLA JARING</option>
                  <option value="T">TENNIS</option>
                  <option value="BS">BOLA SEPAK</option>
                  <option value="BL">BOLA BALING</option>
                  <option value="BK">BOLA KERANJANG</option>
                  <option value="H">HOKI</option>
                  <option value="KB">KEPERLUAN BILIK</option>
                                </select></td>
                <td width="83" bgcolor="#FFFFFF"><input type="submit" name="Submit" value="SELECT" ></td>
              </tr>
              <tr>
                <td>Date Borrow:</td>
                <td><input id=MyDate name=MyDate type="text" size=15 >                  <a href="JavaScript:;" onClick="toggleCalendar('MyDate')"><img src="images/cal.gif" width="28" height="27" border="0"></a></td>
              </tr>
            </table>
<?php
session_start();

$stud_id=$_SESSION['stud_id'];
$stud_name=$_SESSION['stud_name'];

$dbh = pg_connect("host=localhost dbname=laon user=postgres password=zuraihanim");

if(isset($_POST['Submit'])){

$choose_equ=$_POST['choose_equ'];
}
$sql = "SELECT * FROM equ_detail where equ_type_detail = '$choose_equ' ORDER BY equ_name , equ_se_num ASC ";
echo "<table border=0 >";
$result1 = pg_query($sql);

echo $stud_id;

echo "<tr bgcolor='#FFFF33'><th > |-No-| </th><th> |-Item Equipment-|</th><th>|-Series Item-| </th><th>|- Check-| </th></tr>";
while ($row = pg_fetch_array($result1))
{
   $loan = ++$loan;
echo "<tr>";
       echo "<td bgcolor='#FFFF99' cellpadding='0' cellspacing='0'>" .$loan. "</td>";
// echo "<input type='hidden' name='equ_id' id='equ_id' value='$equ_id' >";
    echo "<td bgcolor='#FFFF99' cellpadding='0' cellspacing='0'>". "<input name='equ_name' id='equ_name' type='text' value='$row[equ_name]' readonly>" ."</td>";
    echo "<td bgcolor='#FFFF99' cellpadding='0' cellspacing='0'>". "<input name='equ_se_num' id='equ_se_num' size='7' type='text' value='$row[equ_se_num]' readonly >" ."</td>";
    echo "<td bgcolor='#FFFF99' cellpadding='0' cellspacing='0'>". "<input type='checkbox' name='check1[]' id='check1' size='7' value='yes' readonly>" ."</td>";
echo "</tr>";
    }
echo "</table>";
echo "<INPUT type='button' value='SAVE' name=button2 onclick='return OnButton2();'>";
echo "<input type='reset' name='Reset' value='RESET'>";

pg_free_result($result1);
pg_close($dbh);

require "n_call";
?>

//form finish//

//insert query

<?php
session_start();

$stud_id = pg_escape_string($_POST['stud_id']);
$loan_id = pg_escape_string($_POST['loan_id']);

    $dbconn = pg_connect ("host=localhost dbname=laon user=postgres password=zuraihanim");

    $loan_id =0;

    if(isset($_POST['Submit']))
    {
    $loan_id=$_POST['loan_id'];
    $equ_name=$_POST['equ_name'];
    $equ_se_num=$_POST['equ_se_num'];
    $check1=$_POST['check1'];
?>
    <html>
    <style type="text/css">

<script language="Javascript1.2">

</script>

    </style>
<body onload="printpage()">
       <div align="center">
       <table width="748" height="269" border="0" align="center" cellpadding="0" cellspacing="0">
        <tr>
          <td><img src="images/banner1.jpg" width="800" height="150"></td>
        </tr>
        <tr>
          <td class="style4"><div align="center"><span class="style4"><br>
          <br>
<?php

    echo $stud_id;
    echo "<table border=0 >";
    echo "<font size=12 span class='style4'> ".'List Equipment That You Want To Borrow'."</font>".'<br>'.'<br>';
    echo "<tr bgcolor='#FFFF33' size=12 span class='style4'><th > |-No.-| </th><th> |-Item Equipment-|</th><th>|-Series Item-| </th><th>|- Date Borrow-| </th></tr>";

foreach($check1 as $cValue)
    {
    $sql=pg_query("INSERT INTO loan_detail(loan_id,equ_name,equ_se_num,check1) VALUES ('".$loan_id."','".$equ_name."','".$equ_se_num."','".$check1."')");
    $result=mysql_query($sql);
    }
    $ty++;
    echo "<tr>";

       echo "<td bgcolor='#FFFF99' cellpadding='0' cellspacing='0'>". $ty. "</td>";
    echo "<input type='hidden' name='loan_id' id='loan_id' value='loan_id'>";
    echo "<td bgcolor='#FFFF99' cellpadding='0' cellspacing='0'>". "<input name='equ_name' id='equ_name' type='text' value='$equ_name' readonly >" ."</td>";
    echo "<td bgcolor='#FFFF99' cellpadding='0' cellspacing='0' >". "<input name='equ_se_num' id='equ_se_num' type='text' value='$equ_se_num' size='7' readonly >" ."</td>";

echo "</tr>";


    echo "</table>";

    echo '<br>';
    echo '<br>';
    echo "<a href='JavaScript:window.print();'>Print</a>";

    pg_close();
    require "n_call";

?>
              </span>                        </div>
            <p> </p>
          <div align="center"></div></td>
        </tr>
        <tr>
          <td><div align="center"><img src="images/footer.jpg" width="650" height="75"></div></td>
        </tr>
      </table>
       </div>
    //insert query finish..

problem DateTime Select query no result

Tue, 06 Oct 2009 20:58:12 Z

Vista=32bit, W2008 R2 64bit, SQLEXPRESS 2008 SP1 64bit
XAMPP 1.7.2 php 5.3
PHP Driver 1.1
There is a valid day in database, but it does not filter it. With SQL Management Studio SELECT is ok and returns a record, but with php there is no record
Is there a language problem SETLOCALE or a config problem ?.
The same code is working on a Vista machine (returns one record) , but on W2008 not (returns no record).

perhaps SETLOCAL(..) ?
Thank you very much.

$tsql = "Select * from Preise Where (Day=?) ";
$params = array($Day);

$stmt = sqlsrv_query( $conn, $tsql, $params);
if ( $stmt )
{
}
else
{
echo "Error in statement execution.\n";
die( print_r( sqlsrv_errors(), true));
}
while($Row = sqlsrv_fetch_array($stmt))

Selecting "real" columns makes queries fail silently

Thu, 15 Oct 2009 20:52:08 Z

Selecting any column of type "real" makes my query fail silently. This seems like a major problem to me.

I posted another question earlier today, with code and explanations, but it turns out it's as simple as this. Casting to float "fixes" the problem, except you can't always do that (for example with stored procedures).

Why do some tables return rows, while others don't?

Thu, 15 Oct 2009 17:34:49 Z

Since I switched my project from the mssql_* library to the sqlsrv_* library, some tables (apparently) contain no rows when I do a SELECT on them.

Both types of table (the ones that return rows and the ones that don't) are in the same database, so it's not a connection problem. My query works if I copy and paste it into SQL Server.

I get no error message, no exception, nothing. Just no rows when there ARE rows in that table.

Here's the code I use for getting rows from the database:

---

class db
{
  // ...

  static function query_filter($database, $query)
  {
    $connection = db::connect($database);
    $query_result = sqlsrv_query($connection, $query);
    
    if( $query_result === false )
    {
      throw new db_Sql_Exception($query);
    }

    $result = array();
    while( $row = sqlsrv_fetch_array($query_result) ) 
    {
      $result[] = $row;
    }
    return $result;	
  }

  // ...
}

---

And here's the test script I used to make sure I wasn't crazy:

---

require "db.php";

$query = "
  SELECT * FROM STA_LogInterfacesOperateursCompteursVitesses
";

$rows = db::query_filter('TestDB', $query);

foreach( $rows as $row )
{
  print "<p>" . print_r($row) . "</p>";
}

---

If I switch the table name in my query, everything suddenly works, assuming I pick one of the tables that the SQL Server driver likes.

I'm using the 1.1 / October 2009 version of the driver. I've been trying to fix this since yesterday, so any help would be appreciated.

PHP and SQL Server Express 2008

Sat, 24 Oct 2009 00:41:52 Z

I was wondering if someone could give me a hand in getting php to work with SQL Server 2008 express. I have PHP 5.3.0 installed and it does not have support for mssql. I noticed on the Microsoft sight that their web installer uses 5.2.11 so I am wondering if I should remove 5.3.0 and install 5.2.11. I tried just adding the driver, but the instructions load the dll dynamically using the php dl() function and in 5.3.0 that is not available for me to use. Any help would be appreciated.

SQL Server Driver for non-Windows Platforms (Round 2)

Thu, 22 Oct 2009 18:35:58 Z

Greetings SSDfP Team,

Last year, I asked a question regarding getting the SQL Server Driver available for other Operating Systems besides Windows. At the time, version 1.0 was in the works, and all attention was on a smooth release with current tasks.

Later, I saw some discussion prior to v1.1 about Unix support, and the answer was that there were some other top features that had priority.

Now that v1.1 has been officially released, can you tell us your plans and/or potential timeframes (by the end of the year? Q1 2010?) to expand support to other Operating Systems? I do use SQL Server 2008 on parts of my website, and I'd really like to explore Azure (FreeTDS has trouble with it).

Thanks very much for your time and dedication to bringing us official SQL Server support. :)
-Jason Young

Please Provide a PDO Driver

Thu, 30 Apr 2009 00:10:44 Z

We are using Drupal one of the top three Open Source CMS systems (link), and would love to be able to use SQL Server. We are not alone in this, however several community leaders have been very clear that a PDO driver is needed to make this happen. A long discussion regarding the desire and the need for PDO is available here. So, on behalf of the Drupal community I ask you to please provide a PDO driver. Thank you.

fatal error undefined function sql_num_rows already have 1.1 driver also added cursor by examples??????

Sat, 17 Oct 2009 05:09:02 Z

I get basic queries to run but this page i am on now has multiple queries and for s&g i want to make it work.
i keep running across this sqlsrv_num_rows fatal error Undefined function but i can connect and run queries the code involved is below
$getUser = $loginConnector->query("SELECT * FROM cmsusers WHERE user = '".$_SESSION['user']."' AND pass = '".$_SESSION['pass']."' AND thegroup <= '".$group."' AND enabled = 1",array(),array("Scrollable"=>SQLSRV_CURSOR_KEYSET));

   if ($loginConnector->getNumRows($getUser) > 0){
    // Existing user ok, continue
    if ($goodRedirect != '') {
     header("Location: ".$goodRedirect."?".strip_tags(session_id())) ;
    }
    return true;
   }else{
    // Existing user not ok, logout
    $this->logout();
    return false;
   }

getNumRows() is from an include DbConnector Class the function follows

function getNumRows($result){
return sqlsvr_num_rows($result);
}

mssql connectivity with php5

Fri, 09 Oct 2009 11:49:50 Z

Hi everyone
i am developing website in php5 with database mssql.
i am new with mssql
When i am trying to connect with mssql i am getting this error.

Warning: mssql_connect() [function.mssql-connect]: message: Login failed for user 'NT

AUTHORITY\ANONYMOUS LOGON'. (severity 14) in C:\wamp\www\nimspl\conn\config.php on line 23

Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: VBSERVER\MSSQL in

C:\wamp\www\nimspl\conn\config.php on line 23
Could not connect to SQL Server on VBSERVER\MSSQL Login failed for user 'NT AUTHORITY\ANONYMOUS

LOGON'.

I am using MSSQL server 2005.
Using Microsoft SQL Server Mgt. Studio Express i am able to connect database with Authentication :

SQL Server Authentication

I have enable in php

1. extension=php_mssql.dll in php.ini file.
2. mssql.allow_persistent = On
3. mssql.secure_connection = On

Moreover i have download latest file "ntwdblib.dll" and kept in my php folder.

Now pls guide me for this.
I am stuck in this.

Thanks

SQLSRV is undefined only when one file calls it, works fine elsewhere

Tue, 13 Oct 2009 19:31:14 Z

SQLSRV is installed. I know it's installed because only one php file is giving this error. Every other file on the server is working fine, using the same commands.

The set up:

home.php uses sqlsrv to check the database for sections the user can access and display files in each section. That page works fine.

When you click on one of the files, getfile.php, which is stored in the same folder as home.php, first connects to the database without issue, and then crashes on sqlsrv_fetch_array, with the above error message.

The code originally, was:

$Query = "SELECT * FROM Sections WHERE ID='" . $_GET['s'] . "'";
$Section = sqlsrv_fetch_array(sqlsrv_query($DB, $Query));

home.php runs a while on the array fetching, so on it, the code is:

$Query = "SELECT * FROM Sections";
$Sections = sqlsrv_query($DB, $Query);

$JID = 1;

while ($Section = sqlsrv_fetch_array($Sections)){

So I tried changing the code on getfile.php to:

$Query = "SELECT * FROM Sections WHERE ID='" . $_GET['s'] . "'";
$Temp = sqlsrv_query($DB, $Query);
$Section = sqlsrv_fetch_array($Temp);

And that still errors out:

Fatal error: Call to undefined function sqlsrv_query() in [Path]\getfile.php on line 31

Keep in mind, before telling me sqlsrv is undefined, it uses it to connect to the database without any problems, and uses the exact same code in a file in the same folder without any problems.

I've tried restarting IIS with no luck.

Why can't a output variable be set to null?

Thu, 24 Sep 2009 21:27:29 Z

Hi,

Why is this "Variables that are initialized or updated to null , DateTime , or stream types cannot be used as output parameters." not possible? In particular, updated to null.

Memory usage by May CTP

Wed, 04 Jun 2008 20:18:20 Z

Using the same test as before, Apache httpd doesn't seem to grow without bound any more. That's good. This is with a standard Apache and PHP5 combination. Thread safe version of the driver.

When I run the non-thread safe version on a computer that uses the Zend framework, then the php-cgi.exe program has its memory use growing. I don't think that's good since that's our production environment.

Deadlock problem with TEXT/NTEXT/VARCHAR(MAX)/NVARCHAR(MAX)

Tue, 16 Jun 2009 13:44:18 Z

Hi, we ran into a situation where a single process deadlock itself indefinitely. The situation is:

Try to update the currently fetched record
WHEN the SELECT query has an ORDER BY clause
AND the resultset contains a minimum of records
AND an CLOB (TEXT/VARCHAR(MAX)) column is part of the resultset

Basically, the scenario is a batch processing job that act like that:

SELECT all records from Table WHERE record has to be processed ORDER BY priority
FOR EACH RECORD
- Process record
- Mark record as processed (UPDATE table SET processed = 1 where id = ...)

Notes:

There is no explicit "begin transactions"
we always use the same connection reference

Here is all the steps to reproduce the problem in a controled environment:

Create a test table using this script:

CREATE TABLE [dbo].[channel]
(
    [id] [int] NOT NULL,
    [title] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [description] [VARCHAR](MAX) COLLATE Latin1_General_CS_AS NULL,
    [rank] [int] NULL,
    [mustGenerate] [bit] NULL,
    CONSTRAINT [PK_channel] PRIMARY KEY CLUSTERED  ([id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Create a PHP script (ex: sqlsrv_test.php) that will accept a "step" parameter as HTTP GET

Paste the following code:

function getConnection()
{
    //TODO: ENTER YOUR DATABASE CONNECTION INFORMATION HERE
    $serverName = "xxx.xxx.xxx.xxx";
    $connectionInfos = array('Database' => 'SQLPHP', 'UID' => 'user', 'PWD' => 'password');
    $conn = sqlsrv_connect($serverName, $connectionInfos);
    return ($conn);
}

function fillDatabase($conn, $startId, $endId)
{
    $sql = "INSERT INTO channel (id, title, description, rank, mustGenerate) VALUES (?, ?, ?, ?, ?)";
    for ($id = $startId; $id <= $endId; $id++)
    {
        $params = array($id, 'title ' . $id, 'description ' . $id, $id * 10, 1);
        sqlsrv_query($conn, $sql, $params);
    }
    echo "Database filled with IDs from $startId to $endId<br/>";
}

function testQueryLock($conn)
{
    $query = "SELECT * FROM channel ORDER BY rank ASC";
    $stmt = sqlsrv_query($conn, $query);
    $count = 0;
    $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
    while (false !== $row)
    {
        $count++;
        $id = $row['id'];
        if (!empty($id))
        {
            echo $id;
            //NOTE: THIS IS THE UPDATE THAT WILL EVENTUALLY END-UP CREATING A DEADLOCK
            $update = "UPDATE channel SET mustGenerate = 0 WHERE id = $id";
            $updateResult = sqlsrv_query($conn, $update);
            if ($updateResult)
                echo " - ok <br/>";
            else
                echo " = FAILED <br/>";
        }

        $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
    }
    echo "count = $count";

    sqlsrv_free_stmt($stmt);
}
//---------------------------------------------------------------
if ('1' == $_GET['step'])
{
    $conn = getConnection();
    fillDatabase($conn, 1, 200);
    sqlsrv_close($conn);
}
//---------------------------------------------------------------
if ('2' == $_GET['step'])
{
    $conn = getConnection();
    testQueryLock($conn);
    sqlsrv_close($conn);
    echo "Success, not enough record in resultset to generate the problem!";
}
//---------------------------------------------------------------
if ('3' == $_GET['step'])
{
    $conn = getConnection();
    fillDatabase($conn, 201, 500);
    sqlsrv_close($conn);
}
//---------------------------------------------------------------
if ('4' == $_GET['step'])
{
    $conn = getConnection();
    testQueryLock($conn);
    sqlsrv_close($conn);
    echo "This will never get echoed!";
}

To run the test just navigate each step:

http://localhost/sqlsrv_test.php?step=1
=> database filled with 200 records
http://localhost/sqlsrv_test.php?step=2
=> success
http://localhost/sqlsrv_test.php?step=3
=> database filled with additional records
http://localhost/sqlsrv_test.php?step=4
=> script is never ending. Deadlock in driver.

Now if you remove the "ORDER BY rank ASC" in the testQueryLock function and repeat step 4 you will see the string "This will never get echoed!" being echoed.

Again, (put back the "ORDER BY rank ASC" if you removed it) and run this and execute step 1 to 4, that will still work:

DELETE FROM channel
ALTER TABLE channel ALTER COLUMN description VARCHAR(250)

I did test with TEXT, NTEXT, VARCHAR(MAX), NVARCHAR(MAX) and all these types end-up creating the deadlock situation.

Running Environment:

Windows XP Professional
ZendCore 2.5.2 (PHP 5.2.6, Zend Engine 2.2.0, Apache 2.2.10)
SQLSRV 1.0.1924

I remember getting this problem with a Wamp Installation over Windows XP and using SQLSRV 1.0.1307

error when calling stored procedure

Mon, 05 Oct 2009 20:51:37 Z

Trying to test this with ID "42", but I get errors. Stored Procedure works at command line if I type:

exec

usp_matchalgorithm 42

$sqlTypePro = "EXECUTE dbo.usp_matchalgorithm ?";

$params = array(
                             array(42, SQLSRV_PARAM_IN)
                           );


$stmt = sqlsrv_query($conn, $sqlTypePro, $params);

if( $stmt === false ) {
    die( "<pre>".print_r(sqlsrv_errors(), true)."</pre>" );
} else {

    $data = sqlsrv_fetch_array($stmt);
    var_dump($data);
}

ERRORS:

Array
(
    [0] => Array
        (
            [0] => 01000
            [SQLSTATE] => 01000
            [1] => 0
            [code] => 0
            [2] => [Microsoft][SQL Native Client][SQL Server]5
            [message] => [Microsoft][SQL Native Client][SQL Server]5
        )

    [1] => Array
        (
            [0] => 01000
            [SQLSTATE] => 01000
            [1] => 0
            [code] => 0
            [2] => [Microsoft][SQL Native Client][SQL Server]0
            [message] => [Microsoft][SQL Native Client][SQL Server]0
        )

)

Stored Procedure Insert adds junk to varchar output

Tue, 29 Sep 2009 09:35:38 Z

Hi SQLSRV Support!

We found another problem and isolated it into a test case. The problem is that if a stored procedure does an insert into a table, and sets a varchar output param, that param will have added junk on it to fill it to a length of 100 - varchar(100). In the end, we traced this down to one missing line in the Stored Proc... SET NOCOUNT ON, once that was in, the output varchars did not get filled.

PHP Code:

	//connect
	$options = array(
		'UID' => 'xxx',
		'PWD' => 'xxx',
		'Database' => 'Test DB'
	);
	$host = '127.0.0.1,1433';	
	$conn = sqlsrv_connect($host, $options);
	if ($conn) {
		echo "Connected to $host\n";
	} else {
		echo "Not Connected $host\n";
		echo print_r(sqlsrv_errors(), true)."\n";
	}
	
	//Execute a stored procedure
	$sql = "{call TestSqlSrv(?,?,?)}";
	$variables = array(
		'Result' => '',
		'LastID' => 0,
	);
	$params = array(
		array(&$variables['Result'], SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY), SQLSRV_SQLTYPE_VARCHAR(100)),
		array(&$variables['LastID'], SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_INT, SQLSRV_SQLTYPE_INT),
	);
	
	//display before variables
	echo "SP Variables Defined.\n" . print_r($variables, true)."\n";	
	
	//run query
	$stmt = sqlsrv_query($conn, $sql, $params);
	if ($stmt) {
		echo "Query executed: $sql\n";
	} else {
		echo "Query failed\n" . print_r(sqlsrv_errors(), true)."\n";
	}
	
	//clear any results
	do {
		$next = sqlsrv_next_result($stmt);
	} while ($next);
	
	//clean up
	sqlsrv_free_stmt($stmt);
	sqlsrv_close($conn);
	
	//print the variables
	echo "SP Variables Returned.\n" . print_r($variables, true)."\n";

SQL TABLE:

USE [Test DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Temp](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](100) NOT NULL,
	[Data] [varchar](1000) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

SQL PROCEDURE:

USE [Test DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TestSqlSrv]
@Result varchar(100) OUTPUT, @LastID int OUTPUT
AS
SET NOCOUNT ON --added as was causing the junk fill problem!
BEGIN
	INSERT INTO dbo.Temp ([Name],[Data]) VALUES (N'test name',N'test data')
	SET @LastID = SCOPE_IDENTITY()
	SET @Result = '1'
END

If you comment out the line SET NOCOUNT ON, then the @Result output will come out 100% correctly. Why???

G

INOUT paramter not working, what is wrong?

Thu, 24 Sep 2009 15:04:13 Z

Hi,

We are testing the sqlsrv with stored procedures and inout paramters. However, we keep getting the error "String data, right truncation".

Stored Procedure:

ALTER PROCEDURE [dbo].[TestSqlSrv]
@In varchar(100), @Out varchar(100) OUTPUT, @InOut varchar(100) OUTPUT
AS
SET NOCOUNT ON
BEGIN
	SET @Out = @InOut;
	SET @InOut = 'abc';
	RETURN
END

Then the php code/variables are:

	$sql = "{call TestSqlSrv(?,?,?)}";
	$variables = array(
		'In' => 'one',
		'Out' => '',
		'InOut' => 'two'
	);
	$params = array(
		array(
			&$variables['In'], 
			SQLSRV_PARAM_IN, 
			SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR), 
			SQLSRV_SQLTYPE_VARCHAR(100)
		),
		array(
			&$variables['Out'], 
			SQLSRV_PARAM_OUT, 
			SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR), 
			SQLSRV_SQLTYPE_VARCHAR(100)
		),
		array(
			&$variables['InOut'], 
			SQLSRV_PARAM_INOUT, 
			SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR), 
			SQLSRV_SQLTYPE_VARCHAR(100)
		)
	);

Can anyone put some light on this problem?

Thanks,
G.

Desperate for help connecting to MSSQL from PHP

Mon, 08 Dec 2008 16:34:51 Z

I'm completely new and unfamiliar with mssql, so I'm going to try and describe every little thing here to see if I missed something along the way... At the very end of this file (below the code), I've included the mssql settings from my php.ini file and also some of the sql server 2008 properties that I thought might be relevant.

I have xampp installed on my computer, it has the mssql driver and PEAR::MDB2 installed. extension=php_mssql.dll is uncommented in my php.ini file, as is extension=php_pdo_mssql.dll.

I have Microsoft SQL Server 2008 installed on my computer. I've launched the application (does that mean that the server is running when I do that?), and the username is jadmin and it uses Windows Authentication to connect.

Below is the code that I'm trying to use. I've tried using LPTP, MSSQLSERVER, [ipnumber],1433, [ipnumber].1433, and 127.0.0.1(1434) for the hostname, but no matter what happens, I get MDB2 Error: connect failed.

Please help! This is my first time doing this and I have *no* idea how to use mssql

Also, here's what I get when I do netstat -an, in case it's relevant:
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

Active Connections

Proto Local Address Foreign Address State
TCP 0.0.0.0:80 0.0.0.0:0 LISTENING
TCP 0.0.0.0:135 0.0.0.0:0 LISTENING
TCP 0.0.0.0:443 0.0.0.0:0 LISTENING
TCP 0.0.0.0:445 0.0.0.0:0 LISTENING
TCP 0.0.0.0:2383 0.0.0.0:0 LISTENING
TCP 0.0.0.0:3306 0.0.0.0:0 LISTENING
TCP 0.0.0.0:8080 0.0.0.0:0 LISTENING
TCP 127.0.0.1:80 127.0.0.1:1977 TIME_WAIT
TCP 127.0.0.1:80 127.0.0.1:1981 TIME_WAIT
TCP 127.0.0.1:1028 0.0.0.0:0 LISTENING
TCP 127.0.0.1:1434 0.0.0.0:0 LISTENING

Code Snippet

<?php
require_once('MDB2.php');

class Database
{
   private $_s_username = '';
   private $_s_password = '';
   private $_s_database = '';
   private $_s_server    = '';
   private $_s_type    = '';
   private $_r_connection = null;

   public function __construct($username = null, $password = null, $database = null, $server = null, $type = null)
   {
      try
      {
         if ((strlen($username) == 0)
            || (strlen($password) == 0)
            || (strlen($database) == 0)
            || (strlen($server) == 0))
         {
            $this->_s_username = 'jadmin';
            $this->_s_password = '';
            $this->_s_database = 'testdb';
            $this->_s_server = 'LPTP';
         }
         else
         {
            $this->_s_username = $username;
            $this->_s_password = $password;
            $this->_s_database = $database;
            $this->_s_server = $server;
         }
         if (strlen($type) == 0) {$this->_s_type = 'mssql';}
         else {$this->_s_type = $type;}
      }
      catch(exception $error)
      {
print($error->getMessage());
      }
   }

   public function __destruct()
   {
      $this->_r_connection->disconnect();
   }

   private function connect()
   {
      try
      {
         $dsn = $this->_s_type . '://' . $this->_s_username . ':' . $this->_s_password . '@' . $this->_s_server . '/' . $this->_s_database;
         $db = MDB2::connect($dsn);
         if (PEAR::isError($db)) {throw new Exception($db->getMessage());}
         else
         {
            $this->_r_connection = $db;
            return true;
         }
      }
      catch(exception $error)
      {
print($error->getMessage());
         return false;
      }
   }

   private function disconnect()
   {
      $this->_r_connection->disconnect();
   }

   public function query($querystring)
   {
      $r_recordset = null;

      try
      {
         $this->connect();
         $r_recordset = $this->_r_connection->query($querystring);
         if (PEAR::isError($r_recordset)) {throw new Exception($r_recordset->getMessage());}

         return $r_recordset;
      }
      catch (Exception $error)
      {
print($error->getMessage());
         return false;
      }
      $this->disconnect();
   }

   public function execute($query_string)
   {
      $r_result = null;
      $db = null;
      try
      {
         $this->connect();
         $db = $this->_r_connection;
print('<br><br>');
var_dump($db);
print('|');
var_dump($this->_r_connection);
print('<br><br>');
         $r_result = $db->execute($querystring);
         if (PEAR::isError($r_result)) {throw new Exception($r_result->getMessage());}

         return $r_result;
      }
      catch (Exception $error)
      {
print("<br>|" . $error->getMessage() . "|");
         return false;
      }
      $this->disconnect();
   }
}

$db = new Database;
?>

From my php.ini file
-------------------------------------------
[MSSQL]
; Allow or prevent persistent links.
mssql.allow_persistent = On

; Maximum number of persistent links. -1 means no limit.
mssql.max_persistent = -1

; Maximum number of links (persistent+non persistent). -1 means no limit.
mssql.max_links = -1

; Minimum error severity to display.
mssql.min_error_severity = 10

; Minimum message severity to display.
mssql.min_message_severity = 10

; Compatability mode with old versions of PHP 3.0.
mssql.compatability_mode = Off

; Connect timeout
;mssql.connect_timeout = 5

; Query timeout
;mssql.timeout = 60

; Valid range 0 - 2147483647. Default = 4096.
;mssql.textlimit = 4096

; Valid range 0 - 2147483647. Default = 4096.
;mssql.textsize = 4096

; Limits the number of records in each batch. 0 = all records in one batch.
;mssql.batchsize = 0

; Specify how datetime and datetim4 columns are returned
; On => Returns data converted to SQL server settings
; Off => Returns values as YYYY-MM-DD hh:mms
;mssql.datetimeconvert = On

; Use NT authentication when connecting to the server
mssql.secure_connection = Off

; Specify max number of processes. -1 = library default
; msdlib defaults to 25
; FreeTDS defaults to 4096
;mssql.max_procs = -1

SQL SERVER SERVICES
Taken from Sql Server Configuration
-------------------------------------------
Name State Start Mode Log On As Process ID Service Type
SQL Server Integration Services 10.0 Running Automatic NT AUTHORITY\NETWORK SERVICE 1072 SSIS Server
SQL Full-text Filter Daemon Launcher (MSSQLSERVER) Running Manual NT AUTHORITY\LOCAL SERVICE 3708 Full-text Filter Daemon Launcher
SQL Server (MSSQLSERVER) Running Automatic NT AUTHORITY\NETWORK SERVICE 1584 SQL Server
SQL Server Analysis Services (MSSQLSERVER) Running Automatic NT AUTHORITY\NETWORK SERVICE 1532 Analysis Server
SQL Server Reporting Services (MSSQLSERVER) Running Automatic NT AUTHORITY\NETWORK SERVICE 1220 ReportServer
SQL Server Browser Stopped Other (Boot, System, Disabled or Unknown) NT AUTHORITY\LOCAL SERVICE 0 SQL Browser
SQL Server Agent (MSSQLSERVER) Running Manual NT AUTHORITY\NETWORK SERVICE 4712 SQL Agent

SQL SERVER NETWORK CONFIGURATION
Taken from Sql Server Configuration
------------------------------------------
Protocol Name Status
Shared Memory Enabled
Named Pipes Disabled
TCP/IP Enabled
VIA Disabled

SQL NATIVE CLIENT 10.0 CONFIGURATION
Taken from Sql Server Configuration
-------------------------------------------
Name Order Enabled
Shared Memory 1 Enabled
TCP/IP 2 Enabled
Named Pipes 3 Enabled
VIA Disabled

SERVER PROPERTIES
This is taken from "Server Properties" from Microsoft SQL Server Management Studio
--------------------------------------------------------------------------------------
Name   LPTP
Product   Microsoft SQL Server Enterprise Edition
Operating System Microsoft Windows NT 5.1 (2600)
Platform  NT INTEL x86
Version   10.0.1600.22
Language  English (United States)
Memory   1271 MB
Processors  1
Root Directory  C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL
Server Collation SQL_Latin1_General_CP1_CI_AS
Is Clustered  FALSE

Driver 1.1 CTP and SQL Azure

Tue, 18 Aug 2009 21:02:04 Z

When trying to connect to SQL Azure using the following code, I get "Unable to connect". No further warnings or errors:

$connectionInfo = array( "UID"=>'xxxxxxxxx',
"PWD"=>'yyyyyyyyyyy',
"Database"=>"wordpress");

$conn = sqlsrv_connect('tcp:xxxxxxxxx.ctp.database.windows.net', $connectionInfo);

if( $conn === false )
{
echo "Unable to connect.</br>";
die( print_r( sqlsrv_errors(), true));
}

I'm using the latest SQL2008 native client on my machine.

SQL Azure and PHP on Azure

Tue, 29 Sep 2009 19:19:23 Z

I have a test PHP program working on Azure, except that I can't connect to my SQL Azure database. For some reason the sqlsrv extension isn't active. I was previously trying to use the thread-safe version, but have switched to the non-thread-safe versions of both PHP and the SQL Server extension driver.

The program works using the "local cloud", connecting to the SQL Azure database remotely, it just doesn't work when I upload it to Azure.

Currently I have a page that displays phpinfo and it doesn't show SQLSRV. I also have some code that checks for the extension being active, and it does die, indicating that the extension isn't active. Here's the code:

if (!extension_loaded("sqlsrv"))

{

die("sqlsrv extension not loaded");

}

I've put the following lines in php.ini:

[PHP_SQLSRV]

extension=php_sqlsrv_53_nts_vc9.dll

and php.ini is marked as "content" and "always copy"

the php_sqlsrv_53_nts_vc9.dll file is included in the PHP\ext subdirectory and is marked as 'content' and 'copy always'

MONEY column showing .0000 for value 0... Used to be 0.00, kinda

Wed, 23 Sep 2009 20:09:03 Z

Execute this in SQL Server Management Tools:

select CAST(0 as money) as dollars;

You will get 0.00 as a result. This is also what you receive with the php/mssl_* functions.

With the SQL Server Driver for PHP you get .0000

This is annoying. Anything I can do about it?

I am using v1.1 of the driver w/SQLSERVER2008. This affects the display of what is returned in hundreds of stored procedures.

Use this code to see it in php:

$conn = sqlsrv_connect(...);
$result = sqlsrv_query($conn, 'select CAST(0 as money) dollars;');
while($ar = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
    print_r($ar);
}

Inconsistent performance of driver vs Management Studio queries

Wed, 23 Sep 2009 20:28:42 Z

I'm using PHP 5.3 (non-thread safe, FastCGI, IIS7, Server 2008) and the latest PHP Driver for SQL Server (1.1 CTP, non-thread safe, VC9). I'm seeing a major performance issues with executing stored procedures from PHP vs. from the Management Studio. The queries in PHP are timing out after 5 minutes and never return a result set whereas when the queries are ran from the Management Studio, they return in less than 1 second. The result set is only 25 rows by 4 columns in size. Has anyone else seen any related scenarios or have any suggestions? Thanks!

Do you have to use streams?

Tue, 22 Sep 2009 13:37:40 Z

Is is imperritive that say a Text type field returned in a resultset must be retrieved as a stream?

For example, with the following code:

	public function fetchResults($free)
	{
		if ($this->hasResults()) {
			$results = array();
			$row = sqlsrv_fetch_array($this->query, SQLSRV_FETCH_ASSOC);
			if ($row) {
				do {
					$results[] = $row;
					$row = sqlsrv_fetch_array($this->query, SQLSRV_FETCH_ASSOC);
				} while($row);
			}
			if ($free) {
				sqlsrv_free_stmt($this->query);
			}
			return $results;
		} else {
			return false;
		}
	}

Will this retrieve any stream field types?

G

php_mssql compiled for PHP 5.3 - windows_xp

Tue, 22 Sep 2009 19:03:38 Z

anybody can provide me the php_mssql.dll for PHP 5.3 for Windows xp ?
the date of the php_mssql.dll that i have is 22/01/2004 and give this error
when i try to connect: Unable to load dynamic llibrary c: \PHP\ext\php_mssql.dl,
ins't a win32 application.

I don't know C++ to compile from source

Thanks very much.

undefined SQLSRV_PHPTYPE_INTEGER

Tue, 22 Sep 2009 13:23:28 Z

Hi Guys,

Tried searching online for this, but can't find anything.

The SQLSRV_PHPTYPE_INTEGER constant specified in the documentation does not exist. I believe they actually meant SQLSRV_PHPTYPE_INT. Just incase anyone else gets the undefined error.

Hopefully the documentation gets updated.

G

connecting SQL SERVER 2005 via Sql Server Driver for PHP 1.1

Sun, 20 Sep 2009 15:41:26 Z

is this an impossible mission ? -or- does nobody use the Windows-Apache-PHP-Mssql with Sql server driver environmet?

- I have this platform: Windows XP, Apache 2.2, PHP 5.3, Sql Native Client 2008, Sql server driver 1.1 (Abri/2009)
- In PhpInfo() the sqlsrv is active
- I've all the configurations:
    Sql server manager:   protocol TCP/IP enable
                              named pipe enable enable, name pipe: \\.\pipe\MSSQL$SQLEXPRESS\sql\query
                  IPall: TCP Dynamic port = null
                  TCP port: 1433
    Client Protocols :   Client protocol: TCP/IP enable
                              named pipe enable enable, name pipe: \\.\pipe\MSSQL$SQLEXPRESS\sql\query
                  Aliases: Invitado, Protocol TCP/IP, Port 1433, server RAUL\SQLEXPRESS

    Sql Surface Area Configuration: service MSSQL$SQLEXPRESS is running
                    Remote connections: local and remote connections, using both
                                TCP/IP and named pipes

- In PHP script i've tried all the alternatives:
    - $serverName = ".\SQLEXPRESS";
    $serverName = "RAUL\SQLEXPRESS";
    $serverName = "RAUL,1433";
        - $UID    = "raul";
          $PWD    = "asdfe12";
          $UID    = "";
          $PWD    = "";
        - $connectionOptions = array("Database"=>"Contable", "UID" => $UID, "PWD" => $PWD);
          $connectionOptions = array("Database"=>"Contable.mdf", "UID" => $UID, "PWD" => $PWD);
          $connectionOptions = array("Database"=>"C:\Sistemas\Contables\Contable");
          $connectionOptions = array("Database"=>"Contable");
    - $conn = sqlsrv_connect( $serverName, $connectionOptions);
    $conn = sqlsrv_connect( $serverName);

but allways i have the same error: "Connection could not be established"

- In addition i've included the trace options: "TraceOn" => true, 'TraceFile' => 'C:/sqlsrv_trace.txt'


any help?. Thank for your answer

sqlsrv_connect no connecting with or without windows authentication

Thu, 10 Sep 2009 22:15:34 Z

finally getting somewhere
I discovered that I needed to do the rest of the install for fast cgi 1.5 in the following link:
http://learn.iis.net/page.aspx/247/using-fastcgi-to-host-php-applications-on-iis-60

PHP 5.3
using: php-5.3.0-Win32-VC9-x86.zip and
SQL SERVER DRIVER : php_sqlsrv_53_ts_vc9.dll

neither of the following work:
SQL Server 2008 error
error: Login failed for user 'LANDON\IWAM_LANDON'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

also failed: http://209.33.25.167:8080/phpinfo.php
error:

	The website cannot display the page
	HTTP 500
	Most likely causes:

maybe I should check the permissions for IWAM_LANDON as I did IUSR_LANDON

I checked and that was NOT the problem.

I have diddled everything in php.ini until my keyboard is worn out.
I downloaded and installed IIS 6 and it is running with 5.1 stopped.

Yes remote connection allowed in 2008
I can connect to the 2008 database via server management studio using windows authentication

<?php

/*

Connect to the local server using Windows Authentication and specify

the AdventureWorks database as the database in use. To connect using

SQL Server Authentication, set values for the "UID" and "PWD"

 attributes in the $connectionInfo parameter. For example: 

*/

//$connectionInfo = array("UID" => "sa", "PWD" => "01234");

$serverName = "LANDON\SQLEXPRESSLMK";

$connectionInfo = array( "Database"=>"logbookid");

$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn )

{

    echo "Connection established.\n";

    sqlsrv_close( $conn);

}

else

{

     echo "Connection could not be established.\n";

     //die( print_r( sqlsrv_errors(), true));

}



//-----------------------------------------------

// Perform operations with connection.

//-----------------------------------------------

/* Close the connection. */

//sqlsrv_close( $conn);

?>

_____________________________________
<?php

/*

Connect to the local server NOT using Windows Authentication and specify

the AdventureWorks database as the database in use. To connect using

SQL Server Authentication, set values for the "UID" and "PWD"

 attributes in the $connectionInfo parameter. For example: 

*/

$connectionInfo = array("UID" => "sa", "PWD" => "01234");

$serverName = "LANDON\SQLEXPRESSLMK";

$connectionInfo = array( "Database"=>"logbookid");

$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn )

{

     echo "Connection established.\n";

     sqlsrv_close( $conn);

}

else

{

     echo "Connection could not be established.\n";

     //die( print_r( sqlsrv_errors(), true));

}

//-----------------------------------------------

// Perform operations with connection.

//-----------------------------------------------

/* Close the connection. */

//sqlsrv_close( $conn);

?>




; Directory in which the loadable extensions (modules) reside.
; http://php.net/extension-dir
; extension_dir = "./"
;extension_dir = "C:\PHP\ext"
; On windows:
extension_dir = "ext"

; cgi.force_redirect is necessary to provide security running PHP as a CGI under
; most web servers.  Left undefined, PHP turns this on by default.  You can
; turn it off here AT YOUR OWN RISK
; **You CAN safely turn this off for IIS, in fact, you MUST.**
; http://php.net/cgi.force-redirect
cgi.force_redirect = 0

; Directory in which the loadable extensions (modules) reside.



; http://php.net/extension-dir



; extension_dir = "./"



;extension_dir = "C:\PHP\ext"        this is the way it was before 5.3



; On windows:



extension_dir = "ext"







; The root of the PHP pages, used only if nonempty.



; if PHP was not compiled with FORCE_REDIRECT, you SHOULD set doc_root



; if you are running php as a CGI under any web server (other than IIS)



; see documentation for security issues.  The alternate is to use the



; cgi.force_redirect configuration below



; http://php.net/doc-root



doc_root = "C:\Inetpub\wwwroot"

Converting from PHPLib mssql class

Wed, 09 Sep 2009 09:54:50 Z

Hi, I am using the old mssql class from PHPLib. I hoped I could just replace old mssql-functions with new sqlsrv-functions, but it was not so easy.
Eg. in the old dblib connection I use is not the first record available after a query is performed, which it is in the new driver
Have anyone already done this convertion? Is there a framework out there which has implemented SQLServerDriverForPHP?

x64 support

Fri, 18 Sep 2009 12:29:17 Z

Currently source codes do not compile for x64 platform. Is there any plans for x64 version?

files downloaded for SQL Server Driver for PHP 1.1 same as the files for SQL Server Driver for PHP 1.0

Thu, 17 Sep 2009 20:16:46 Z

http://www.microsoft.com/downloads/thankyou.aspx?familyId=ccdf728b-1ea0-48a8-a84a-5052214caad9&displayLang=en

'ReturnDatesAsStrings'=>true included in connection string works with 1.1
the following didn't work with the addition of 'ReturnDatesAsStrings'=>true

$connectionInfo = array("Database"=>"logbookid", "UID" => "sa", "PWD" => "01234",
'ReturnDatesAsStrings'=>true);

http://social.msdn.microsoft.com/Forums/en-US/sqldriverforphp/thread/42503986-b14b-4301-b142-96f8275ebc7a

Yes:
(1) I restarted IIS5.1
(2) copied the new (?) file into C:\PHP\ext

Maybe it didn't like PHP 5.2.6

I did it twice so I was sure I was making no mistake! BUT! I must have!