Why SQL Parameter Sequence Is Important

Question

I think this is an entry level, but I have not received any response.

When building queries, we need to use: 1 :: 2 ... in absolute sequence? From my tests it seems yes. But doesn't stmt-> setXXX (n, val) just set the nth parameter in the instruction with val? How is this implemented?

See my example below:

if (bNewContent) //new { sql = "BEGIN PackProductManagement.procAddOTTContent(:1, :2, :3, :4, :5, :6, :7); END; "; } else //update { sql = "UPDATE OTT_Content SET ContentID = :1, ContentType = :2, FingerPrint = :3, IsHighLevelSafe = :4, "; sql += "OutProtection = :5, OfflinePlayback = :6, ModifyTime = sysdate "; sql += "WHERE ContentID = :1"; } try { OpenStatement(sql); stmt->setString(1, ac->ContentDesc.ContentID); stmt->setUInt(2, ac->ContentDesc.ContentType); stmt->setUInt(3, ac->ContentDesc.FingerPrint); stmt->setUInt(4, ac->ContentDesc.HighLevelSafe); stmt->setUInt(5, ac->ContentDesc.OutputProtection); stmt->setUInt(6, ac->ContentDesc.OfflinePlayback); if (bNewContent) { stmt->setUInt(7, 0); //only used if new } stmt->execute(); CloseStatement(true); } 

In this example, bNewContent always FALSE, so we always run the update statement. The above query is working fine. But if I change the update request as shown below (deleted ContentID = :1, at the beginning of the update statement), I will get ORA-01722 INVALID_NUMBER. Why can't I: 2: 3: 4: 5: 6: 1? If setXXX is implemented as a queue, then why above: 1: 2: 3: 4: 5: 6: 1 work ??

 sql = "UPDATE OTT_Content SET ContentType = :2, FingerPrint = :3, IsHighLevelSafe = :4, "; sql += "OutProtection = :5, OfflinePlayback = :6, ModifyTime = sysdate "; sql += "WHERE ContentID = :1"; 

Thanks in advance!

Edited by:

Test results below: (based on ZZa's answer)

 sql = "UPDATE OTT_Content SET ContentID = :x ContentType = :x, FingerPrint = :x, IsHighLevelSafe = :x, "; sql += "OutProtection = :x, OfflinePlayback = :x, ModifyTime = sysdate "; sql += "WHERE ContentID = :x"; 

Above code does not work with 6 parameters.

 sql = "UPDATE OTT_Content SET ContentID = :1 ContentType = :x, FingerPrint = :x, IsHighLevelSafe = :x, "; sql += "OutProtection = :x, OfflinePlayback = :x, ModifyTime = sysdate "; sql += "WHERE ContentID = :1"; 

It works with 6 parameters on the code.

Answer 1

According to the ORACLE documentation, binding variables are used in the order in which they were placed, but not in the way they were named. That is why you get an exception. So, you just need to set the parameters in the order in which they were mentioned ( ContentType first to be mentioned if the first is deleted), and it does not matter how they are named.

Your code might look like this and it will still work:

 if (bNewContent) //new { sql = "BEGIN PackProductManagement.procAddOTTContent(:x, :x, :x, :x, :x, :x, :x); END; "; } else //update { sql = "UPDATE OTT_Content SET ContentID = :x, ContentType = :x, FingerPrint = :x, IsHighLevelSafe = :x, "; sql += "OutProtection = :x, OfflinePlayback = :x, ModifyTime = sysdate "; sql += "WHERE ContentID = :x"; } try { OpenStatement(sql); stmt->setString(1, ac->ContentDesc.ContentID); stmt->setUInt(2, ac->ContentDesc.ContentType); stmt->setUInt(3, ac->ContentDesc.FingerPrint); stmt->setUInt(4, ac->ContentDesc.HighLevelSafe); stmt->setUInt(5, ac->ContentDesc.OutputProtection); stmt->setUInt(6, ac->ContentDesc.OfflinePlayback); if (bNewContent) { stmt->setUInt(7, 0); //only used if new } stmt->execute(); CloseStatement(true); } 

Answer 2

Answer: it depends.

If you use .net and odbc, you must use positional parameters. If you are using .net server and sql, you can use named parameters. If you use the cfstoredproc tag in ColdFusion, you must use positional parameters.

And so on and so forth.