PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Thu Jul 27, 2017 9:49 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 5 posts ] 
Author Message
PostPosted: Fri Dec 29, 2006 9:59 am 
Offline
Forum Regular

Joined: Wed Dec 13, 2006 11:28 am
Posts: 575
We are having a problem where any stored procedures called from MSSQL fail under php 5.1.x on linux. This only happens on stored procedures where a temp table is used. All other stored procedures work fine.

Some facts.

Tried it on Debian and Fedora Core 6

Tried both ctlib and freetds compiled and also tried it with rpms/debian packages

The exact queries work fine under php 4 on a debian server, as soon as you run the site on linux php 5 all the stored procedures fail but the rest of the site works perfectly.

The stored procedures work fine, have always worked, and are used by other platforms and languages throughout an enterprise.


Here is the error
Syntax: [ Download ] [ Hide ]
Cannot insert the value NULL into column 'NewOrderID', table
'tempdb.dbo.#Orders__________________00010002FA09';
column does not allow nulls. INSERT fails. (severity 16, procedure N/A) in
/var/home/slc.ironkeep.net/includes/class/class.gardenweasel.inc.php on
line 634

Warning: mssql_query() [function.mssql-query]: Sybase: Server message:
Cannot insert the value NULL into column '', table ''; column does not
allow nulls. INSERT fails. (severity 16, procedure psp_ProcessOrders) in
/var/home/slc.ironkeep.net/includes/class/class.gardenweasel.inc.php on
line 634

Warning: mssql_query() [function.mssql-query]: Sybase: Server message:
Procedure 'psp_AssocAccountInsertOrdDetail' expects parameter '@Orderid',
which was not supplied. (severity 16, procedure
psp_AssocAccountInsertOrdDetai) in
/var/home/slc.ironkeep.net/includes/class/class.gardenweasel.inc.php on
line 634



Here is a simple example of the code that is failing. Note, we have tried this in every way possible, using every mssql command in the book including passing the variable and type wtih bind. We are 100% certain that the query as below is coming out correct, when we echo the $sql and paste it into a query manager it works fine.
Syntax: [ Download ] [ Hide ]
$conn =& get_conn();

$sql ="exec psp_ProcessOrders {$weborderkey}";

$result = mssql_query( $sql, $conn );


Last edited by Begby on Wed Jan 03, 2007 9:58 am, edited 1 time in total.

Top
 Profile  
 
 Post subject:
PostPosted: Fri Dec 29, 2006 11:31 am 
Offline
Site Administrator
User avatar

Joined: Tue Sep 09, 2003 6:04 pm
Posts: 14293
Location: Fremont, CA, USA
On my Sybase procs, when I pass them, I usually pass them like

Syntax: [ Download ] [ Hide ]
<?php

$sql = "dbname..proc_name @param=$value, @param2=$value2";

?>


But the message you are getting sounds a little like a database permission issue OR a datatype matching issue. These messages:

Syntax: [ Download ] [ Hide ]
column does not allow nulls.

Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails

Procedure 'psp_AssocAccountInsertOrdDetail' expects parameter '@Orderid', which was not supplied.


are what lead me to think that. The last error message specifically, is something I get when I use the wrong input param reference (like @user_id when it expects @userid). Are there other procs that this is built on?


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jan 03, 2007 9:58 am 
Offline
Forum Regular

Joined: Wed Dec 13, 2006 11:28 am
Posts: 575
After much trial and error we figured it out. For some reason under php5 you need to first specify this command before doing any stored procedures involving temp tables where you try to insert null values.

Syntax: [ Download ] [ Hide ]
set ANSI_NULL_DFLT_ON ON


Most client libraries will turn this on by default, for some reason the libraries under php 5 do not. That command makes it so columns in temporary tables by default allow null values, if its not set a temporary table will by default have all columns set to non null which is where the problem was arising.

What a tremendous pain in the ass.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jan 03, 2007 11:08 am 
Offline
Site Administrator
User avatar

Joined: Tue Sep 09, 2003 6:04 pm
Posts: 14293
Location: Fremont, CA, USA
That is a good tip to have. Sorry you had to go through that mess, but I am sure others will benefit from your solution. Congratulations.


Top
 Profile  
 
PostPosted: Wed May 18, 2016 9:44 pm 
Offline
Forum Newbie

Joined: Wed May 18, 2016 9:40 pm
Posts: 1
OH MY GOSH. I WASTED MANY HOURS today trying to figure out why in the heck my stored proc is not returning any rows to my PHP5 Linux client via Freetds. Just as you suggested, all I had to do was add this to the top of my stored proc:

set ANSI_NULL_DFLT_ON ON;

Hard to believe something posted in 2007 found it's way to my rescue in 2016!

Thank you!


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group