[SOLVED]Issues with MSSQL Stored Procedures under linux.....

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

[SOLVED]Issues with MSSQL Stored Procedures under linux.....

Post by Begby »

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

Code: Select all

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.

Code: Select all

$conn =& get_conn();
$sql ="exec psp_ProcessOrders {$weborderkey}";
$result = mssql_query( $sql, $conn );
Last edited by Begby on Wed Jan 03, 2007 8:58 am, edited 1 time in total.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

On my Sybase procs, when I pass them, I usually pass them like

Code: Select all

<?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:

Code: Select all

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?
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

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.

Code: Select all

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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

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.
TroyWolf
Forum Newbie
Posts: 1
Joined: Wed May 18, 2016 9:40 pm

Re: [SOLVED]Issues with MSSQL Stored Procedures under linux.

Post by TroyWolf »

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!
Post Reply