I need to execute a query in mssql what’s no problem with SQL management studio but Yii doesnt want me to do it.
The following query in mssql works fine in sql studio
IF NOT EXISTS (SELECT id FROM storage_folder_output WHERE storageId = 2 AND importdate = '2011-10-05')
BEGIN
INSERT INTO storage_folder_output (storageId, size, importdate)
VALUES (2,2242,'2011-10-05')
END;
In yii I use the following code
$command = Yii::app()->db->createCommand("IF NOT EXISTS (SELECT id
FROM storage_folder_output
WHERE storageId = :storageid
AND importdate = :importdate)
BEGIN
INSERT INTO storage_folder_output (storagelocationId, size, importdate)
VALUES (:storageid,:size,:importdate)
END;");
$command->bindParam(":storageid", $storageid);
$command->bindParam(":importdate", $importdate);
$command->bindParam(":size", $size);
$command->execute();
But that gives me the following error
CDbException
CDbCommand failed to execute the SQL statement: SQLSTATE[07002]: [Microsoft][SQL Server Native Client 10.0]COUNT field incorrect or syntax error. The SQL statement executed was:
IF NOT EXISTS (SELECT id FROM storage_folder_output WHERE storageId = :storageid AND importdate = :importdate)
BEGIN
INSERT INTO storage_folder_output (storagelocationId, size, importdate)
VALUES (:storageid,:size,:importdate)
END;
Someone knows the answer to this problem? ok i can make a stored procedure without a problem but i want to do it this way.
in case you or anybody else landing on this page about this issue, it looks like the error is the following: You are binding a parameter with the same name twice in the same query.
In your case you’re binding two parameters twice: :storageid and :importdate. Although these appear twice in the query, they’re bound (through bindParam function) only once.
This appears to work well for MySQL or even the FreeTDS driver against SQL Server, but sqlsrv native windows driver doesn’t like it. It seems that the sqlsrv driver expects duplicate variables to be bound two times, it only finds it once and it considers a mismatch (or wrong counts for the occurences of the variable in the string and the the bindParams).
The obvious workaround in this is to name and bind every occurence of your params differently, even if they’re actually the same thing. In your case this would be as follows:
$command = Yii::app()->db->createCommand("IF NOT EXISTS (SELECT id
FROM storage_folder_output
WHERE storageId = :selectStorageid
AND importdate = :selectImportdate)
BEGIN
INSERT INTO storage_folder_output (storagelocationId, size, importdate)
VALUES (:insertStorageid,:size,:insertImportdate)
END;");
$command->bindParam(":selectStorageid", $storageid);
$command->bindParam(":selectImportdate", $importdate);
$command->bindParam(":insertStorageid", $storageid);
$command->bindParam(":insertImportdate", $importdate);
$command->bindParam(":size", $size);
$command->execute();
What I did was prepend the two "conflicting" variables with the words "select" and "insert" for their occurrence in the select and insert part of your query respectively. Then I bound the same values to the two redundant sets of variables.
It worked for me, so I’ll be happy to know if it worked for you, too.
