Home Dashboard Directory Help
Search

Cannot bind parameters with PHP pdo_odbc and SQL Native Client 11.0 by Frozen-Solid


Status: 

Active


4
0
Sign in
to vote
Type: Bug
ID: 737751
Opened: 4/19/2012 11:31:29 AM
Access Restriction: Public
0
Workaround(s)
view
2
User(s) can reproduce this bug

Description

Using the SQL Native Client 11.0 for Linux, pdo_odbc will not allow you to bind paramters.

Details
Sign in to post a comment.
Posted by androok on 4/2/2013 at 1:08 PM
This issue also occurs when:
Using SQL Native Client 10 or 11 on Windows, while accessing an MS SQL server via PDO's ODBC interface.

One might ask why you'd want to do this, when you can just use the PDO -> sqlsrv interface...well, the pdo->odbc->sql server method has performed as much as 100 times faster for us on windows.

Please fix this.
Posted by Microsoft on 4/1/2013 at 9:44 AM
This is most likely due to 32-bit and 64-bit mismatch. Unless more details are provided we will not be able to investigate this further.
Posted by Microsoft on 5/25/2012 at 3:04 PM
Hi,

A dev investigated this, and has the following information to add:
I can’t make much headway without an ODBC trace, but I suspect they have a mismatch in the size of the ODBC data type SQLLEN.

Our driver needs a 64-bit SQLLEN. The default UnixODBC DriverManager has a 32-bit SQLLEN (even though it is a 64-bit DM). Hence, we need a custom build of the DM with SQLLEN set to 64-bit.

If they’ve setup the DM as per our guidelines, then sqlcmd and bcp should work as expected.

However, they will probably need to rebuild pdo_odbc with the correct flags to have a 64-bit SQLLEN. I don’t know if they’ve done that.

When they create an ODBC trace, if they see this with the connect call (SQLConnect or SQLDriverConnect) then their DM is correctly built:
[ODBC][28783][1321576347.077780][SQLDriverConnectW.c][290]
        Entry:
            Connection = 0x17c858e0
            Window Hdl = (nil)
            Str In = [DRIVER=…]
            Str Out = (nil)
            Str Out Max = 0
            Str Out Ptr = (nil)
            Completion = 0

Please check how your PDO_ODBC extension is compiled and let us know if this helps.

Thanks,

Jonathan
Posted by bitofsky on 4/25/2012 at 9:57 PM
hello :)
I am encountering this issue.

running on..
CentOS-5
Microsoft SQL Server ODBC Driver 1.0 for Linux (RHEL-5)
unixODBC 2.3.0
PHP 5.3.5 (yum installed)
php53.x86_64                             5.3.3-5.el5                 installed
php53-cli.x86_64                         5.3.3-5.el5                 installed
php53-common.x86_64                        5.3.3-5.el5                 installed
php53-mssql.x86_64                         5.3.3-1.el5                 installed
php53-odbc.x86_64                         5.3.3-5.el5                 installed
php53-pdo.x86_64                         5.3.3-5.el5                 installed

phpinfo()
PDO support    enabled
PDO drivers    dblib, odbc, sqlite

/etc/odbcinst.ini
[SQL Server Native Client 11.0]
Description=Microsoft SQL Server ODBC Driver V1.0 for Linux
Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0
Threading=1
UsageCount=1

/etc/odbc.ini
[MSTEST]
Driver=SQL Server Native Client 11.0
Description=My Test
Trace=Yes
Server=<mssql sql server 2008 r2 ip..>
Port=1433
Database=TestDB

My simple php source code here
<?php
ini_set('display_errors', true);
ini_set('error_reporting', E_ALL ^ E_NOTICE );
try{
$dbh = new PDO("odbc:MSTEST", "test", 3045);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$query = "SELECT * FROM TestDB.dbo.test_table WHERE c2 = ?";
$stmt = $dbh->prepare($query);
$stmt->bindParam(1, $v=5, PDO::PARAM_INT);
$result = $stmt->execute();
print_r( $stmt->fetch() );
}catch(Exception $e){
die($e->getMessage());
}
?>

Error message
SQLSTATE[22018]: Invalid character value for cast specification: 0 [Microsoft][SQL Server Native Client 11.0]Invalid character value for cast specification (SQLExecute[0] at /builddir/build/BUILD/php-5.3.3/ext/pdo_odbc/odbc_stmt.c:254)

Sql server profiler
User Error Message    데이터베이스 컨텍스트가 'TestDB'(으)로 변경되었습니다.            test                        58    2012-04-26 13:54:55.907            
User Error Message    언어 설정이 한국어(으)로 변경되었습니다.            test                        58    2012-04-26 13:54:55.907            
Audit Login    -- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language 한국어
set dateformat ymd
set datefirst 7
set transaction isolation level read committed
            test                        58    2012-04-26 13:54:55.907        0X2000002838F4010000000000    
SP:CacheHit     set fmtonly on select c2 from dbo.test_table where 1=2 set fmtonly off            test                        58    2012-04-26 13:54:55.907            
SQL:BatchStarting     set fmtonly on select c2 from dbo.test_table where 1=2 set fmtonly off            test                        58    2012-04-26 13:54:55.907            
SQL:StmtStarting    set fmtonly on             test                        58    2012-04-26 13:54:55.907            
SQL:StmtCompleted    set fmtonly on             test    0    0    0    0        58    2012-04-26 13:54:55.907    2012-04-26 13:54:55.907        
SQL:StmtStarting    select c2 from dbo.test_table where 1=2             test                        58    2012-04-26 13:54:55.907            
Audit Schema Object Access Event    select c2 from dbo.test_table where 1=2             test                        58    2012-04-26 13:54:55.907            
SQL:StmtCompleted    select c2 from dbo.test_table where 1=2             test    0    0    0    0        58    2012-04-26 13:54:55.907    2012-04-26 13:54:55.907        
SQL:StmtStarting    set fmtonly off            test                        58    2012-04-26 13:54:55.907            
SQL:StmtCompleted    set fmtonly off            test    0    0    0    0        58    2012-04-26 13:54:55.907    2012-04-26 13:54:55.907        
SQL:BatchCompleted     set fmtonly on select c2 from dbo.test_table where 1=2 set fmtonly off            test    0    0    0    0        58    2012-04-26 13:54:55.907    2012-04-26 13:54:55.907        
Audit Logout                test    0    0    0    3        58    2012-04-26 13:54:55.907    2012-04-26 13:54:55.910        
Sign in to post a workaround.