Asked  1 Year ago    Answers:  5   Viewed   10 times

If I understand right I'm using following stack: PHP <-> PDO <-> MS SQL DBLIB <-> freetds <-> MS SQL Server.

$pdo = new PDO('dblib:host=192.168.0.10:1433;dbname=MyDb;charset=UTF-8', 'mydb', 'secret', [
    PDO::ATTR_CASE => PDO::CASE_NATURAL,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
    PDO::ATTR_STRINGIFY_FETCHES => false,
]);
$statement = $pdo->prepare("EXEC [dbo].[sp_MyStoredProcedure] 1, 2, 3");
if ( ! $statement->execute()) {
    throw new ErrorException('Error executing sp_MyStoredProcedure');
}
$data = $statement->fetchAll(PDO::FETCH_ASSOC);

I'm setting ERRMODE to ERRMODE_EXCEPTION, and there is an error 515 happens in stored procedure, but no exception is thrown, why? $data is storing empty array and I was considering this as proper result of executing stored procedure. To overcome this issue I've added check $statement->errorInfo()[1], through I'm not sure if it proper to check error in this way:

if ( ! $statement->execute() || $statement->errorInfo()[1]) {
    throw new ErrorException('Error executing sp_MyStoredProcedure');
}

Do I'm doing this check properly?

Detailed $statement->errorInfo():

array (
  0 => '00000',
  1 => 515,
  2 => 'General SQL Server error: Check messages from the SQL Server [515]  (severity 16) [(null)]',
  3 => -1,
  4 => 16,
)

Also according to article error 515 is an error of inserting NULL into column which is NOT NULL. But why I don't see this error when executing EXEC [dbo].[sp_MyStoredProcedure] 1, 2, 3 in Microsoft SQL Server Management Studio or tsql (freetds-bin)?

 Answers

2

I had a similar problem.

Solution - explicitly specify null-ability for temporary tables columns in your stored procedure.

Before:

create table #resultsTable(
    paging_id int,
    pt_key bigint,
    pt_ctkeyfrom int
)

After:

create table #resultsTable(
    paging_id int NULL,
    pt_key bigint NULL,
    pt_ctkeyfrom int NULL
)

For me the problem was resolved.

Unfortunately I do not know how to solve without changing the procedure code.

Thursday, April 1, 2021
 
MKM
 
MKM
2

PDO allows you to write you code to be reasonably DB-neutral.

If you want truly DB-neutral, you'd want to use a full DB abstraction layer like NotORM -- with plain PDO, you'd still need to be careful about SQL syntax difference, but at least your basic PHP code would be DB-neutral.

Being DB-neutral may not seem important now -- if you're using SQL Server, then you've probably been told that's what is required and nothing else -- but you can't predict how things will change in the future, so if the choice is between a DB-neutral driver and a DB-specific driver, and you don't have any other reason for a preference, then go with the neutral one.... it'll make life a lot easier if your company gets taken over and the new boss wants to use Oracle as the DB!

Also, because it's DB neutral, PDO is more standard and more well-known in the PHP community. You'll get a lot more help with PDO from sites online (like this one) than with the MSSQL driver.

Thursday, April 1, 2021
 
jsuissa
 
5

Had already downloaded the driver and it didn't work. Found a new site for the driver and this one works.

https://github.com/Microsoft/msphpsql/releases

php.ini line added:

extension=php_pdo_sqlsrv_7_nts.dll
Thursday, April 1, 2021
 
njai
 
4

If you only want to match "" as an empty string

WHERE DATALENGTH(COLUMN) > 0 

If you want to count any string consisting entirely of spaces as empty

WHERE COLUMN <> '' 

Both of these will not return NULL values when used in a WHERE clause. As NULL will evaluate as UNKNOWN for these rather than TRUE.

CREATE TABLE T 
  ( 
     C VARCHAR(10) 
  ); 

INSERT INTO T 
VALUES      ('A'), 
            (''),
            ('    '), 
            (NULL); 

SELECT * 
FROM   T 
WHERE  C <> ''

Returns just the single row A. I.e. The rows with NULL or an empty string or a string consisting entirely of spaces are all excluded by this query.

SQL Fiddle

Thursday, July 8, 2021
 
Gil
 
Gil
5

This is too long for a comment. You need to look at the compatibility level that you are using:

select compatibility_level
from sys.databases;

Because the functionality was introduced in SQL Server 2012, you need a level of 110 or above.

The compatibility allows newer versions of SQL Server to emulate previous versions. This can be handy when you need to run code that uses obsoleted features, for instance.

Saturday, December 11, 2021
 
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :