I've seen similar error messages, but most have to do with comparing int or float to uniqueidenifier, which makes sense why you'd get an error. My error is this:
SQLSTATE[22018]: Invalid character value for cast specification: 206 [Microsoft][SQL Server Native Client 11.0][SQL Server]Operand type clash: text is incompatible with uniqueidentifier (SQLExecute[206] at /usr/src/php-5.4.8/ext/pdo_odbc/odbc_stmt.c:254)
I'm building a PHP ZF2 application and attempting to call a user-defined function with parameters. The first four parameters are UNIQUEIDENTIFIER values. The last four are BIT values. Here is my code:
public function getCustomerInspectionDocuments($fkCustomer) {
/** @var \Zend\Db\ResultSet\ResultSet $result */
$result = $adapter->query("
SELECT
createUser.FullName AS CreateUser,
udf.CreateTime,
udf.CompleteTime,
modifyUser.FullName AS ModifyUser,
udf.ModifyTime,
udf.Source,
udf.id AS InstanceID
FROM
udfDocumentInstances(
:fkCustomer,
:fkDocumentQueue,
:fkDocumentType,
:fkADUser,
:Completed,
:Deleted,
:LinkByXXX,
:LinkByOwnership
) udf
LEFT JOIN ADUser createUser
ON udf.fkCreateUser = createUser.pkid
LEFT JOIN ADUser deleteUser
ON udf.fkDeleteUser = deleteUser.pkid
LEFT JOIN ADUser modifyUser
ON udf.fkModifyUser = modifyUser.pkid
ORDER BY
ModifyTime DESC
", array(
':fkCustomer' => $fkCustomer,
':fkDocumentQueue' => '57B5829B-3EAE-46FF-8130-8A432176DE2A',
':fkDocumentType' => '7E5D5187-B38A-E211-B52D-0F1256A21434',
':fkADUser' => null,
':Completed' => null,
':Deleted' => 0,
':LinkByXXX' => 0,
':LinkByOwnership' => 0,
));
// do some other stuff and return the records
}
This code works absolutely fine when executed against SQL Server 2012, but when I switch it back to 2008, it chokes with that error message. I'm using the same driver, so the only thing that changes is the server.
The problem seems to be a combination of the fact it's executed against a UDF and that I'm binding the parameters via PDO. If I change it to a simple SELECT query (from a normal table), it works. Or if I put the parameter values directly into the query, that also works. But I need to call this UDF, and I would prefer to use the parameterized query.
Is there a way to see exactly what is being sent to the SQL Server (i.e. the completely assembled query)? Or is it possible that it's passed to the server as a parameterized query and it's the SQL Server that's assembling it?
Based on what I found out from SQL Profiler (thanks @davek), it looks like the query is assembled on the SQL Server side, and PDO is passing all of the parameters as TEXT, which cannot be implicitly converted to UNIQUEIDENTIFIER or BIT. I'm not sure how to get it to pass them as CHAR by default. I'm also not sure why it doesn't cause an issue when using a parameterized query with a normal table.
Here is the temporary workaround I've come up with. It's ugly, but it works for now. Hopefully someone else can come up with a better answer.
'...
FROM
dbo.udfDocumentInstances(
CAST(:fkCustomer AS CHAR(36)),
CAST(:fkDocumentQueue AS CHAR(36)),
CAST(:fkDocumentType AS CHAR(36)),
CAST(:fkADUser AS CHAR(36)),
CAST(:Completed AS CHAR(1)),
CAST(:Deleted AS CHAR(1)),
CAST(:LinkByDEA AS CHAR(1)),
CAST(:LinkByOwnership AS CHAR(1))
) udf
...'
So I'm casting the values from TEXT to CHAR, and then the SQL Server will implicitly convert them to UNIQUEIDENTIFIER or BIT.
If anyone can come up with a way to pass them as CHAR instead of TEXT, without having to explicitly cast each one, please post your response.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments