Reusing prepared statement hangs

[AIIleG]

Hi,

a note in api.h says:

A statement can be prepared once and executed as many times as needed (see Binding variables section)

Unfortunately that's exactly what's not working. At the second OCI_Execute() it hangs and has to be killed by force. While it hangs the cpu load is constantly 100% for the core which is running it.
I'm using ocilib 4.7.6 with the 64bit oracle 12.1 instant client libs on osx. Here is my test program:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <string>
#include <ocilib.h>

using namespace std;

int main() {
	OCI_Connection* cn;
	OCI_Statement* st;

	OCI_Date *date;
	OCI_Lob *lob;

	string tmp;

	OCI_Initialize(NULL, NULL, OCI_ENV_CONTEXT);
	cn = OCI_ConnectionCreate("1.2.3.4:1521/DB", "xxx", "xxx", OCI_SESSION_DEFAULT);

	if (!cn) {
		perror(OCI_ErrorGetString(OCI_GetLastError()));
		exit(1);
	}

	st = OCI_StatementCreate(cn);
	OCI_ExecuteStmt(st, "ALTER SESSION SET current_schema = xxx, NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'");
	OCI_Prepare(st, "insert into tester (clobber, date_) values(:bclob, :bdate)");

	// first run
	date = OCI_DateCreate(NULL);
	OCI_DateFromText(date, "2016-04-13 20:38:14", "YYYY-MM-DD HH24:MI:SS");
	OCI_BindDate(st, ":bdate", date);
	lob = OCI_LobCreate(cn, OCI_CLOB);
	tmp = "clob 1 is cool";
	OCI_LobWrite(lob, &tmp[0], tmp.length());
	OCI_BindLob(st, ":bclob", lob);
	OCI_Execute(st);
	OCI_DateFree(date);
	OCI_LobFree(lob);

	// second run
	date = OCI_DateCreate(NULL);
	OCI_DateFromText(date, "2016-04-14 20:38:14", "YYYY-MM-DD HH24:MI:SS");
	OCI_BindDate(st, ":bdate", date);
	lob = OCI_LobCreate(cn, OCI_CLOB);
	tmp = "clob 2 is cooler";
	OCI_LobWrite(lob, &tmp[0], tmp.length());
	OCI_BindLob(st, ":bclob", lob);
	OCI_Execute(st); // <= hangs here!
	OCI_DateFree(date);
	OCI_LobFree(lob);

	OCI_Commit(cn);
	OCI_StatementFree(st);
	OCI_ConnectionFree(cn);
	OCI_Cleanup();
	exit(0);
}

and the debugger output:

Process 932 stopped
* thread #1: tid = 0x501f, 0x00000001023f8c0d libclntsh.dylib.12.1`OCIDateAssign + 29, queue = 'com.apple.main-thread', stop reason = EXC_BAD_ACCESS (code=EXC_I386_GPFLT)
    frame #0: 0x00000001023f8c0d libclntsh.dylib.12.1`OCIDateAssign + 29
libclntsh.dylib.12.1`OCIDateAssign + 29:
-> 0x1023f8c0d:  movq   (%rsi), %rsi
   0x1023f8c10:  movq   %rsi, (%rdx)
   0x1023f8c13:  popq   %rbp
   0x1023f8c14:  retq   
(lldb) bt
* thread #1: tid = 0x501f, 0x00000001023f8c0d libclntsh.dylib.12.1`OCIDateAssign + 29, queue = 'com.apple.main-thread', stop reason = EXC_BAD_ACCESS (code=EXC_I386_GPFLT)
  * frame #0: 0x00000001023f8c0d libclntsh.dylib.12.1`OCIDateAssign + 29
    frame #1: 0x00000001000426d9 libocilib.4.dylib`OcilibStatementBindCheck + 329
    frame #2: 0x000000010003d0be libocilib.4.dylib`OcilibStatementExecuteInternal + 782
    frame #3: 0x000000010003daf3 libocilib.4.dylib`OcilibStatementExecute + 51
    frame #4: 0x0000000100001c7a a.out`main + 1930 at ora_ocilib.cxx:56
    frame #5: 0x0000000104fbd5fd libdyld.dylib`start + 1

[vrogier]

Hi,

This is a misuse of the API.
Host bind variables must remain valid during the lifetime of the prepared statement.
In your code you are deleting these variables while still being bounded to the statement.
As you did not set the option to allow rebinding variables, second binding calls fail and the statement still uses previously bounded and destroyed host variables leading accessing dandling pointers and thus undefined behaviour.
The concept of bind variables is to bind them once and reuse them across executions. You just need to update their content.

Regards,

Vincent

[AIIleG]

Oh I see, the binding is still part of the prepare stage so to say. Well, that works with one exception: I have to use OCI_LobTruncate() for every consecutive run because OCI_LobWrite() appends the text otherwise.
In practice however, at least for things like number and varchar, using OCI_AllowRebinding() is much better because declaring all variables in advance (the insert values are often generated within loops) is usually not possible or would require to copy the content to the already bound variables every time. The latter is the case for lob or date anyway so for them it doesn't matter.

Thanks for the reply and sorry for the false bugreport.