Re: php mssql stored procedure

trpost@xxxxxxxxx schreef:
On Feb 25, 12:20 am, Erwin Moller
<Since_humans_read_this_I_am_spammed_too_m...@xxxxxxxxxxxxxxxx> wrote:
trp...@xxxxxxxxx schreef:

<snip>



Well if I echo out the PHP query, copy it from the browser and paste
it into SQL Query Analyzer it executes and returns results.
Hmm, annoying indeed.
Possibly MS is doing it's famous trick again: repairing wrong stuff,
like IE 'repairs' bad HTML.

In your case I suspect that the 'NULL' is translated to NULL if you run
it through MS SQL Query Analyzer.
Or did you mean to insert a string 'NULL' or the NULL value?

That would be my first test.

If that doesn't help, you can Google around a little for stored
procedure, MSSQL and PHP. If memory serves me well you are not the first
that has troubles with calling Stored Procedures in MSSQL.

If that doesn't help either, you can have a look at MS SQL drivers for PHP:http://www.microsoft.com/sqlserver/2005/en/us/PHP-Driver.aspx

And as a last resort: Use a serious database, eg Postgresql.
;-)

Please be so kind to post back here with your findings, even if they are
negative. Others might benefit from it in the future.

Good luck.

Regards,
Erwin Moller







Here is my PHP code:
<?php
//SQL Server Database config section
$server = "USDB1\SSQL";
$database = "db_cra"; //database name
//Connect to the database server
$msconnect = mssql_connect($server,$username,$password)
or die("COULD NOT CONNECT TO MS-SQL SERVER.<br>");
echo "Connection Succeded<br>";
echo "Selecting Database ... ...<br>";
$msdb = mssql_select_db($database,$msconnect)
or die("COULD NOT SELECT DATABASE.<br>");
echo "Database Selection Succeded<br>";
echo "Running SQL Query ... ...<br>";
$php_errormsg = "";
$msquery = "exec db_cra.dbo.sp_csq_activity '02/22/2009
00:00:00','02/22/2009 23:59:59', 0,'OR|Retail_CSQ' ,'NULL'";
echo $msquery;
$msresults = mssql_query($msquery, $msconnect);
echo "<br>Any errors: $php_errormsg<br>";
echo "Query finished<br><br>";
echo "-----<br>";
echo "<PRE>";
var_dump($msresults);
echo "</PRE>";
echo "-----<br>";
echo "<h2>Query Results</h2>";
while ($row = mssql_fetch_array($msresults))
{
echo "<PRE>";
var_dump($row);
echo "</PRE>";
}
echo "!!!DONE!!!";
mssql_close();
?>
--
"There are two ways of constructing a software design: One way is to
make it so simple that there are obviously no deficiencies, and the
other way is to make it so complicated that there are no obvious
deficiencies. The first method is far more difficult."
-- C.A.R. Hoare- Hide quoted text -

- Show quoted text -

Thanks!! Your suggestion to use the MS SQL drivers for PHP worked:
http://www.microsoft.com/sqlserver/2005/en/us/PHP-Driver.aspx

Here is what I did:
- Downloaded drivers from http://www.microsoft.com/sqlserver/2005/en/us/PHP-Driver.aspx
- Copied php_sqlsrv.dll to c:\php\ext\ and c:\windows\system32
- Restarted Apache
- Installed MS SQL Server Native Client from
http://www.microsoft.com/downloads/details.aspx?FamilyId=50b97994-8453-4998-8226-fa42ec403d17&DisplayLang=en
- Conneccted as follows:

<?php
/* Specify the server and connection string attributes. */
$serverName = "(local)";
$connectionInfo = array( "Database"=>"AdventureWorks");

/* Connect using Windows Authentication. */
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
{
echo "Unable to connect.</br>";
die( print_r( sqlsrv_errors(), true));
}

/* Query SQL Server for the login of the user accessing the
database. */
$tsql = "SELECT CONVERT(varchar(32), SUSER_SNAME())";
$stmt = sqlsrv_query( $conn, $tsql);
if( $stmt === false )
{
echo "Error in executing query.</br>";
die( print_r( sqlsrv_errors(), true));
}

/* Retrieve and display the results of the query. */
$row = sqlsrv_fetch_array($stmt);
echo "User login: ".$row[0]."</br>";

/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>

This worked, so I don't know why, but the php msssql functions balk
when I run a stored procedure, but by installing the Microsoft
libraries and using the sqlsrv functions all works fine.

Thanks for all the responses, hope this helps someone else.


Hi,

Glad it works now. :-)
I am also glad to hear that MS/PHP driver works allright.

Thanks for posting the installation.
I am sure somebody will come across it while googling for a solution and is happy with it. ;-)

Regards,
Erwin Moller


--
"There are two ways of constructing a software design: One way is to make it so simple that there are obviously no deficiencies, and the other way is to make it so complicated that there are no obvious deficiencies. The first method is far more difficult."
-- C.A.R. Hoare
.

Relevant Pages

  • Re: Precision of retrieved data
    ... I SQL via SQL Query Analyzer, I get the value of 0.654321. ... Which Microsoft database? ... What is the DisplayFormat property of the field object set to? ...
    (borland.public.delphi.database.ado)
  • Re: php mssql stored procedure
    ... Possibly MS is doing it's famous trick again: repairing wrong stuff, ... In your case I suspect that the 'NULL' is translated to NULL if you run it through MS SQL Query Analyzer. ... If that doesn't help, you can Google around a little for stored procedure, MSSQL and PHP. ... Use a serious database, ...
    (comp.lang.php)
  • Re: Like Bug with Sql Server 2005
    ... Are you 100% sure they are both pointing to the same server and database? ... I am using SQL Server Management Studio to query a SQL Server 2000 ... If I do it in Sql Query Analyzer from the Sql2000 client side, ...
    (microsoft.public.sqlserver.clients)
  • Re: Truncate log file
    ... Under SQL Query Analyzer, I run following statement, ... But get error messages like, ... Could not open new database 'backoff_0801'. ... Device activation error. ...
    (microsoft.public.sqlserver.server)
  • LRe: Frustrated trying to use "pure" VS.NET to access database properties
    ... In the table that's returned using the GetSchema method there "appears to be" a column that someone intended to be populated with the default value but I have never seen anything in it (and I KNOW the database definition specifies one). ... If you have SQL Query Analyzer, you can use an index search in it's help documentation to search for sp_columns to review what each field in the results collection represents, and map from there:) ...
    (microsoft.public.dotnet.languages.vb)