PHP with MSSQL Insert NULL error

Question

I'm trying to use PHP with Microsoft SQL Server 2008 R2 and it makes me want to die. I have a stored procedure that works perfectly via FreeTDS that I run in the terminal on my Mac, works perfectly in Visual Studio, and works perfectly via ASP.NET on a FrontPage server. It fails miserably through PHP 5.3.2 on an Ubuntu web server, however. In the procedure, I set up a few temp tables and insert into them from a giant table, which invariably contains some nulls here and there. The error I get is

Cannot insert the value NULL into column 'Notes', table 'tempdb.dbo.#termtable1__00000000BE0B'; column does not allow nulls. INSERT fails.

only with a billion more underscores. Someone posted this as a bug here, but it hasn't got much attention. My best attempt was specifying a NULL constraint on every column of every temp table, but it didn't help. Also tried SET ANSI_NULLS ON. I don't have much access to any of these servers as far as patches and drivers go, nor do I have a heck of a lot of access to the people who do. I don't think they're going to want to do much to help me out, but if someone has a really good idea, I'll try to get it done. Oh, and, it should be noted that the same procedure in SquirrelSQL (via Microsoft JDBC driver) gives bizarre, half-correct results. Thanks.

EDIT:

I fixed the link, actually just copied and pasted the wrong URL altogether. Here's the T-SQL where the temp table gets declared. I tried putting NULL after each datatype. There are five of them just like this.

    CREATE TABLE #termtable1
        (SerialNumber varchar(50), SR varchar(50), LeaseTag varchar(50), Component varchar(50), 
        Fund int, Org int, Program varchar(50), FirstName varchar(80), LastName varchar(30), 
        Department varchar(50), Location varchar(50), UserID nvarchar(20), Notes nvarchar(MAX), 
        Manufacturer varchar(50), Model varchar(50), Maintenance bit, OrderNumber varchar(50), 
        ContractNumber varchar(50), ParentTag varchar(50));

They get inserted into via dynamic SQL, which is why I can't use a table variable instead (scope issues):

DECLARE @sqlstring nvarchar(MAX);
DECLARE @params nvarchar(200) = N'@term1 varchar(100), @term2 varchar(100), @term3 varchar(100), @term4 varchar(100), @term5 varchar(100)';
DECLARE qryCursor CURSOR FOR SELECT * FROM @terms;
OPEN qryCursor;
FETCH NEXT FROM qryCursor INTO @term1, @term2, @term3, @term4, @term5;
DECLARE @termrow int = 1;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @sqlstring = N'INSERT INTO #termtable' + CAST(@termrow AS varchar) + N' SELECT * FROM dbo.NewUserInfo WHERE ';
    DECLARE @i int = 1;
    WHILE (@i <= 5)
    BEGIN
        SET @sqlstring = @sqlstring +
        N'(SerialNumber LIKE @term' + CAST(@i AS varchar) + N' OR ' +
         N'SR LIKE @term' + CAST(@i AS varchar) + N' OR ' +
         N'LeaseTag LIKE @term' + CAST(@i AS varchar) + N' OR ' +
         N'Component LIKE @term' + CAST(@i AS varchar) + N' OR ' +
         N'Fund LIKE @term' + CAST(@i AS varchar) + N' OR ' +
         N'Org LIKE @term' + CAST(@i AS varchar) + N' OR ' +
         N'Program LIKE @term' + CAST(@i AS varchar) + N' OR ' +
         N'FirstName LIKE @term' + CAST(@i AS varchar) + N' OR ' +
         N'LastName LIKE @term' + CAST(@i AS varchar) + N' OR ' +
         N'Department LIKE @term' + CAST(@i AS varchar) + N' OR ' +
         N'Location LIKE @term' + CAST(@i AS varchar) + N' OR ' +
         N'Notes LIKE @term' + CAST(@i AS varchar) + N' OR ' +
         N'Manufacturer LIKE @term' + CAST(@i AS varchar) + N' OR ' +
         N'Model LIKE @term' + CAST(@i AS varchar) + N' OR ' +
         N'Maintenance LIKE @term' + CAST(@i AS varchar) + N' OR ' +
         N'OrderNumber LIKE @term' + CAST(@i AS varchar) + N' OR ' +
         N'ContractNumber LIKE @term' + CAST(@i AS varchar) + N' OR ' +
         N'ParentTag LIKE @term' + CAST(@i AS varchar) + N') OR ';
        SET @i = @i + 1;
    END
    SET @sqlstring = @sqlstring + N'(0 = 1)';
    EXECUTE sp_executesql @sqlstring, @params, @term1=@term1, @term2=@term2, @term3=@term3, @term4=@term4, @term5=@term5;
    SET @termrow = @termrow + 1;
    FETCH NEXT FROM qryCursor INTO @term1, @term2, @term3, @term4, @term5;
END

The PHP is simple. $result = mssql_query("EXEC QuickSearch '" . $search . "';"); It only gives the error when it finds a NULL in the NewUserInfo table. Frequently in "Notes."

If you want input you'll need to show the code of your stored procedure (at least the part that creates and populates #termtable1) and how you're calling the procedure from PHP. You'll also probably want to elaborate on whatever you mean by "bizarre, half-correct results" (though perhaps in a different question). Sounds like you have more than one problem. — Aaron Bertrand
– Aaron Bertrand, Commented Apr 25, 2012 at 15:32
Your link is password protected. Whatever, it's hard to help without a single line of code (PHP or T-SQL). — Álvaro González
– Álvaro González, Commented Apr 25, 2012 at 15:33
Sounds like you need a hack - is it possible to not use temp tables? — dfb
– dfb, Commented Apr 25, 2012 at 15:33
Updated with lots of code and more details. Thanks everyone. — Andrew
– Andrew, Commented Apr 26, 2012 at 18:33

Andrew · Accepted Answer · 2012-04-26 20:17:01Z

1

Ok guys, I'm sorry. Putting NULL after every datatype did work. I must have missed a couple when I tried it the first time. The table declaration should be like this:

CREATE TABLE #termtable1
        (SerialNumber varchar(50) NULL, SR varchar(50) NULL, LeaseTag varchar(50) NULL, Component varchar(50) NULL, 
        Fund int NULL, Org int NULL, Program varchar(50) NULL, FirstName varchar(80) NULL, LastName varchar(30) NULL, 
        Department varchar(50) NULL, Location varchar(50) NULL, UserID varchar(20) NULL, Notes varchar(MAX) NULL, 
        Manufacturer varchar(50) NULL, Model varchar(50) NULL, Maintenance bit NULL, OrderNumber varchar(50) NULL, 
        ContractNumber varchar(50) NULL, ParentTag varchar(50) NULL);

answered Apr 26, 2012 at 20:17

Andrew

5,5063 gold badges41 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom Page · Accepted Answer · 2014-04-14 21:55:11Z

0

If you create the temporary table would be to use a SELECT ... INTO Statement then you can be sure that it will match the columns of the NewUserInfo table even if you late make changes to that table

 SET @sqlstring = N'SELECT * FROM dbo.NewUserInfo INTO #termtable' + CAST(@termrow AS varchar) + N' WHERE 1=0';

answered Apr 14, 2014 at 21:55

Tom Page

1,2511 gold badge7 silver badges8 bronze badges

Collectives™ on Stack Overflow

PHP with MSSQL Insert NULL error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related