Getting a bind position from a bind name in OCI

Question

When using OCIStmtPrepare() and OCIBindByName(), is there a way to do the bind by name, then get the position of that bind as an int? OCIStmtGetBindInfo() doesn't seem to do it. Thanks!

Answer 1

There doesn't appear to be an easy way to do it. I tried using the undocumented (as in I couldn't find it in the help docs but it is in the oci.h header) OCI_ATTR_HANDLE_POSITION using OCIAttrGet() on the bind handle:

ub4 bpos = 0;
OCIBind *bindp;
OCIAttrGet(bindp, OCI_HTYPE_BIND, &bpos, 0, OCI_ATTR_HANDLE_POSITION, errhp);

Unfortunately, that seems to work for bind handles that you bound positionally, but return 0 for any you bound by name.

So it seems like you would have to use the OCIStmtGetBindInfo() call to have it fill an array of bind variable names then iterate through it to find the position(s) for each named bind, either by comparing the bind name to the values in the bind variable names array (the values of which will be uppercased):

sb4 found = 0;
text* bvns[100];
ub1 bvnls[100];
text* invs[100];
ub1 invls[100];
ub1 dupls[100];
OCIBind* bhnds[100];
OCIStmtGetBindInfo(stmthp, errhp, (ub4)100, (ub4)1, &found, bvns, bvnls, invs, invls, dupls, bhnds);
for (unsigned int col = 0; col < found; col++)
{
    printf("%p is bound to name: %s", bhnds[col], bvns[col]);
}

One interesting thing to note about multiple placeholders in a statement is that it behaves differently if your statement is an anonymous block or not. That is:

insert into foo (bar, baz) values (:bar, :bar)

Will fill your found output variable with 2 (and the arrays with info for 2 binds), whereas:

begin insert into foo (bar, baz) values (:bar, :bar); end;

Will fill your found output variable with 1 (and the arrays with info for 1 bind). Nonetheless, a single call to OCIBindByName() will bind both in either case.