Order of bind variables in CAPI.Exec()

HFCDev

Hi,

I was writing a function that does either an Insert or an Update, depending on whether there is already a row for that UID. The table is simple - consisting of an ID numeric field and a CLOB field. In my SQL, I use bind variables, :A1 and :A2 for the two pieces of data. Depending on whether it's an insert or an update, I have them in different order.

What I observed was regardless of the ordering in my SQL, it processes the arguments in the order that they are found in the args list of CAPI.Exec and it seems to be ignoring the bind order specified by the number on the bind variable. For example in the case of an insert, I'm doing

INSERT INTO MY_TABLE VALUES(:A1, :A2)

and in the case of an update, I'm doing

UPDATE MY_TABLE SET VALUE=:A2 where NODEID=:A1

Both are executed with the statement

CAPI.Exec(conn, stmt, nodeID, value) where nodeID is an integer, and value is of type Dynamic

Initially I was finding that the INSERT worked while the UPDATE failed with error ORA-01722. I then tried flipping the bind variables around so that the INSERT had them as :A2, :A1 and Update had them as :A1 and :A2, and flipped the order of nodeID and value. When I did that, I found that the INSERT would now fail.

I'm a bit surprised at this behaviour because I could swear this worked properly in older versions of Content Server (this is CS 21.2). So my question to the field and to OT development is this: Is it possible to specify the order of bind variables in a CAPI.Exec() statement similar to what you can do in a Str.Format() statement? Oddly, the connect log seemed to indicate that the bind was processing the arguments in the correct order.

Also, one trick I tried (so that I could have only one CAPI.Exec as it needed to be done in a transaction) was to pass a list of args to the CAPI.Exec(). According to the documetation, that should work. What I observed is that the CSIDE thread mysteriously stopped on that statement when I tried to run it.

In the end, I had to write two separate sub-functions - one for the insert and one for the update so that I could have two separate CAPI.Exec statements with the arguments in the correct order and to avoid that ORACLE error.

So, am I going mad, or can bind variables only be processed in the order they are found in the arg list regardless of the numbering in your SQL statement?

-Hugh

sıɹɥɔ

Hi Hugh,

As you suspect, the order of the parameters is what determines how values are bound to the statement. After reading OScript code for so many years it's natural to attach some significance to the bind parameter names, but there isn't any.

Just some background for others:

Various database management systems use different ways to bind values to parameters in SQL. The standard way is positional, and uses a question mark (?). Some vendors extend this to have named parameters, and use an at-sign (@) or a colon character (:). The OScript CAPI package provides independence from the underlying dbms, and it finds a middle ground: parameters are positional, however names can be used to aid the developer in matching up values in the statement with their code. A colon character is used and anything following it is up to the developer.

By convention OScripters use a name that begins with "A" (as the documentation suggests) and then follow it with a positional number. For example ":A4" would be used to suggest this is the fourth parameter. This is simply a convenience; CAPI doesn't enforce it. You could equally use parameters :A, :B, :C, and :D, or even :this, :that, and :other. It's important to realize such names don't refer to OScript variables with the same names though. Hence the convention to use positional numbers in the names.

-chris