?
Solved

php mssql stored procedure Options

Posted on 2009-02-23
9
Medium Priority
?
1,724 Views
Last Modified: 2013-12-13
I am running PHP5.2.1 on a Windows 2003 server. I have a small
application that connects to a MSSQL database and executes a query
which works just fine, however when I try to execute a stored
procedure I get no results and see the following error in the logs:

[Mon Feb 23 17:35:21 2009] [error] [client 169.143.76.43] PHP
Warning:  mssql_query() [<a href='function.mssql-query'>function.mssql-
query</a>]: message: Cannot insert the value NULL into column '',
table ''; column does not allow nulls. INSERT fails. (severity 16) in
C:\\Program Files\\Apache Software Foundation\\Apache2.2\\htdocs\\cisco
\\b.php on line 29
[Mon Feb 23 17:35:21 2009] [error] [client 169.143.76.43] PHP
Warning:  mssql_query() [<a href='function.mssql-query'>function.mssql-
query</a>]: message: Cannot insert the value NULL into column '',
table ''; column does not allow nulls. INSERT fails. (severity 16) in
C:\\Program Files\\Apache Software Foundation\\Apache2.2\\htdocs\\cisco
\\b.php on line 29
[Mon Feb 23 17:35:21 2009] [error] [client 169.143.76.43] PHP
Warning:  mssql_query() [<a href='function.mssql-query'>function.mssql-
query</a>]: Query failed in C:\\Program Files\\Apache Software
Foundation\\Apache2.2\\htdocs\\cisco\\b.php on line 29
[Mon Feb 23 17:35:21 2009] [error] [client 169.143.76.43] PHP
Warning:  mssql_fetch_array(): supplied argument is not a valid MS SQL-
result resource in C:\\Program Files\\Apache Software Foundation\
\Apache2.2\\htdocs\\cisco\\b.php on line 43


Now if I execute this same query on the MSSQL server it runs just
fine. It seems to be an issue executing a stored procedure within php.


I do not have access to modify the stored procedure as many people
rely on it, but is there something additional I need to do within PHP
to get this to work?



Thanks!
0
Comment
Question by:master44
9 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
ID: 23718761
can you please show the relevant php code, and the procedure definition (the first line with the arguments definitions?), please?
0
 
LVL 3

Assisted Solution

by:albrieu
albrieu earned 200 total points
ID: 23718776
column does not allow nulls,

i think this is a problem with a field that is missing in the query or omitted.

 check the table structure and the fields whit not default values that s not present in the query
0
 
LVL 4

Assisted Solution

by:davidsperling
davidsperling earned 1000 total points
ID: 23720882
>Now if I execute this same query on the MSSQL server it runs just
fine.

Just to make shure, are you running *exactly* the same query?

I usually  "echo" the sql to screen and copy/paste it into sql-tool, just to be on the safe side :-)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Assisted Solution

by:davidsperling
davidsperling earned 1000 total points
ID: 23720941
Are you using mssql_bind() or similar?

"mssql_bind  Adds a parameter to a stored procedure or a remote stored procedure"

Se examples in link.

mssql_bind():
http://se.php.net/manual/en/function.mssql-bind.php

mssql general:
http://se.php.net/manual/en/book.mssql.php
0
 
LVL 4

Assisted Solution

by:davidsperling
davidsperling earned 1000 total points
ID: 23720979
Some time has passed since I used mssql but...

I recall that procedures that return recordsets should be declared as "table valued stored functions" or something.
0
 

Author Comment

by:master44
ID: 23723246
Thanks for all the replies...

When I say I ran the same query on the MSSQL server, I did echo out the $query and cut and paste from the browser to the MSSQL query analyzer.

Below is the code snippet wher I am executing the query:

The stored procedures I am trying to run are out of the box stored procedures from a Cisco phone system, there are approximately 10 different out of the box stored procedures that I am trying to run from within PHP, and all fail in the same way (I can run it just fine from the MSSQL server using query analyzer, but fail within PHP)
<?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();
 
?>

Open in new window

0
 
LVL 4

Assisted Solution

by:davidsperling
davidsperling earned 1000 total points
ID: 23725721
As far as  I can see you're using plain mssql_query() to fetch your results.

I'd check out the examples in the links above, that demonstrate how to access MSSQL stored procs from php.
0
 
LVL 4

Assisted Solution

by:davidsperling
davidsperling earned 1000 total points
ID: 23734696
http://www.daniweb.com/forums/thread112159.html
// execute SQL Server store procedure
function MsExecuteProcedure($procedure)
{
$proc = mssql_init($this->_db."..".$procedure);
$rez = mssql_execute($proc) or die(mssql_get_last_message());
return $rez;
}
 
// function that adds a parameter to a SQL Server stored procedure
function MsAddParameterToProcedure($procedure, $msParamName, $paramValue, $type, $output = null)
{
if(!$output)
mssql_bind($procedure, $msParamName, $paramValue, $type);
else 
mssql_bind($procedure, $msParamName, $paramValue, $type, true, false, 40);
}
 
// add parametter to a procedure
MsAddParameterToProcedure($proc, "@username", $username, SQLVARCHAR);
 
// execute procedure
$rez = MsExecuteProcedure($proc);
 
// get values 
$arr = mssql_fetch_assoc($rez);

Open in new window

0
 

Accepted Solution

by:
master44 earned 0 total points
ID: 23739739
Here is what I did that works:
- 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 <see code snippet>


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.
<?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);
?>

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month14 days, 2 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question