Php Move To Pdo Causes Incompatible Type Clashes

[iarp]

In an effort to be a little bit safer and better with my queries i moved from mssql_* functions to PDO using sqlsrv.

 

One thing i did not take into account is the use of column type "image" that is used in a few areas.

 

I have a few dozen tables that have a column named NOTE of type "image"

 

I receive this error "Operand type clash: nvarchar is incompatible with image" and i'm wondering if anyone knows what i can do from here. I will note now, there is no changing the database in any fashion.

 

While writing this, i decided to google one last thing and ran across this, https://bugs.php.net/bug.php?id=36961 , which scares me because it's 6 years old, open and i'm wondering if it's the same error.

Edited November 24, 2012 by iarp

[PFMaBiSmAd]

Are you specifically binding the input parameters/value without specifying a data type attribute or are you passing the data values as an array to the ->execute(...) method? Both of these default to string data types.

 

After a little research, the image type, which is now the varbinary(max) type, are large object data types. You would need to specifically bind the value/parameter and specify PDO::PARAM_LOB as the 3rd parameter in the bind method.

[iarp]

Added PDO::PARAM_LOB and it still passes data through as NVARCHAR. I tried PARAM_STR just for the hell of it, NVARCHAR too.

 

Thinking these image types will require the old mssql_ functions.

[PFMaBiSmAd]

It would really help if you posted code that reproduces the problem so that someone could, well, reproduce the problem to see if they can find a solution.

[iarp]

CREATE TABLE [dbo].[NOTES](
   [CREATEDDATE] [datetime] NOT NULL,
   [uSERID] [varchar]( NOT NULL,
   [NOTE] [image] NULL,
   [id] [varchar](15) NOT NULL
)

$values = array(
   'CREATEDDATE' => date('Y-m-d H:i:s.000'),
   'USERID' => 'CLIENT',
   'NOTE' => array($noteBody, 'LOB'),
   'id' => $id
);
Database::insert('NOTES', $values);


class Database
{
   public function conn() {
       try {
           $dsn = 'sqlsrv:server=127.0.0.1;Database = Tester';
           $user = 'sa';
           $pass = 'password';
           $conn = new PDO($dsn, $user, $pass);
           $conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
           return $conn;
       } catch(PDOException $e) {
           die($e->getMessage());
       }
   }

   public function insert($table, $values = array())
   {
       $dbh = self::conn();

       foreach ($values as $field => $v)
           $ins[] = ':' . $field;

       $ins = implode(',', $ins);
       $fields = implode(',', array_keys($values));
       $sql = "INSERT INTO $table ($fields) VALUES ($ins)";

       $sth = $dbh->prepare($sql);
       foreach ($values as $f => $v)
       {
           if (is_array($v)) {
               switch($v[1]){
                   case 'LOB':
                       $sth->bindValue(':' . $f, $v[0], PDO::PARAM_LOB);
                       break;
                   case 'INT':
                       $sth->bindValue(':' . $f, $v[0], PDO::PARAM_INT);
                       break;
                   case 'STR':
                   default:
                       $sth->bindValue(':' . $f, $v[0], PDO::PARAM_STR);
                       break;
                }
           } else {
               $sth->bindValue(':' . $f, $v, PDO::PARAM_STR);
           }
       }
       return $sth->execute();
       //return $this->lastId = $dbh->lastInsertId();
   }
}

 

Well, it would seem like the editor doesn't like my tabs for indentation.

Edited November 24, 2012 by iarp

[kicken]

From what I understand reading the docs, if you use PARAM_LOB, then the variable is supposed to be a stream resource (ie, fopen() or similar) rather than just the raw data.  So the code would end up looking something like this:

 

switch($v[1]){
case 'LOB':
	if (!is_resource($v[0])){
		$fp= tmpfile();
		fwrite($fp, $v[0]);
		rewind($fp);
		$v[0] = $fp;
	}
	$sth->bindValue(':' . $f, $v[0], PDO::PARAM_LOB);
	break;

 

That will open a temporary file to write the variable data too.  When the script finishes the file will be automatically deleted.

 

[iarp]

Before adding the if statement using tmpfile:

 

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 nvarchar(23),@P2 nvarchar(6),@P3 nvarchar(35),@P4 nvarchar(15)',N'INSERT INTO NOTES (CREATEDDATE,USERID,NOTE,id) VALUES (@P1,@P2,@P3,@P4)',N'2012-11-24 09:28:54.000',N'CLIENT',N'test test',N'7T0adxMbhDlve4S'
select @p1

 

Afterwards, using the tmpfile:

 

declare @p1 int
set @p1=NULL
exec sp_prepare @p1 output,N'@P1 nvarchar(23),@P2 nvarchar(6),@P3 nvarchar(max),@P4 nvarchar(15)',N'INSERT INTO NOTES (CREATEDDATE,USERID,NOTE,id) VALUES (@P1,@P2,@P3,@P4)',1
select @p1

 

So it would seem that, it's either not preparing the statement correctly, or not executing (even though i call $sth->execute() ). I checked all running statements against the database, that is the only one that calls sp_prepare instead of sp_prepexec.

Edited November 24, 2012 by iarp

[PFMaBiSmAd]

I'm pretty sure (untested) you will need to use bindParam and the 5th parameter looks like it would also need to be PDO::SQLSRV_ENCODING_BINARY

[iarp]

Thank-you, that works.

 

$sth->bindParam(':' . $f, $v[0], PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY);

 

No need to use tmpfile system to read it, it takes raw data.

Edited November 25, 2012 by iarp