I'm trying to SELECT a LONG RAW column in an Oracle table using the OCI library.
For reasons that go beyond the scope of this question, I prefer to fetch the data by pieces, not using a callback therefore.
The execution of the statement returns, as expected, OCI_NEED_DATA but the first call to OCIStmtFetch causes a system error.
ErrorCode 5: Access violation reading address 0 in oracommon12.dll.
I got client 12.1 and connect to Oracle 10.g
Below a minimal program that reproduces the error.
Everything goes well until line 43 (result 10): It prints 99 which equals 'OCI_NEED_DATA'.
The program crashes the line after.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>
static const char uid[] = "XXXX";
static const char pswd[] = "XXXX";
static const char conn[] = "XXXX";
static const char fileid[] = "XXXX";
int main(int argc, char argv[])
{
OCIEnv* p_env;
OCIError* p_err;
OCISvcCtx* p_svc;
OCIStmt* p_sql;
OCIDefine* p_dfn;
OCIBind* p_bnd;
int rc;
char stmt[256];
ub1 buffer[8192];
rc = OCIInitialize(OCI_DEFAULT, NULL, (dvoid* (*)(dvoid*, size_t))NULL, (dvoid* (*)(dvoid*, dvoid*, size_t))NULL, (void (*)(dvoid*, dvoid*))NULL);
printf("RESULT 1: %d\n", rc);
rc = OCIEnvInit(&p_env, OCI_DEFAULT, 0, NULL);
printf("RESULT 2: %d\n", rc);
rc = OCIHandleAlloc(p_env, (void**)&p_err, OCI_HTYPE_ERROR, 0, NULL);
printf("RESULT 3: %d\n", rc);
rc = OCIHandleAlloc(p_env, (void**)&p_svc, OCI_HTYPE_SVCCTX, 0, NULL);
printf("RESULT 4: %d\n", rc);
rc = OCILogon(p_env, p_err, &p_svc, (text*)uid, (ub4)strlen(uid), (text*)pswd, (ub4)strlen(pswd), (text*)conn, (ub4)strlen(conn));
printf("RESULT 5: %d\n", rc);
rc = OCIHandleAlloc(p_env, (void**)&p_sql, OCI_HTYPE_STMT, 0, NULL);
printf("RESULT 6: %d\n", rc);
sprintf(stmt, "SELECT content FROM td_planimetrie WHERE id_file = :x");
rc = OCIStmtPrepare(p_sql, p_err, (text*)stmt, (ub4)strlen(stmt), OCI_NTV_SYNTAX, OCI_DEFAULT);
printf("RESULT 7: %d\n", rc);
rc = OCIBindByName(p_sql, &p_bnd, p_err, (text*)":x", -1, (text*)fileid, (sb4)strlen(fileid) + 1, SQLT_STR, NULL, NULL, NULL, 0, NULL, OCI_DEFAULT);
printf("RESULT 8: %d\n", rc);
rc = OCIDefineByPos(p_sql, &p_dfn, p_err, 1, NULL, 0, SQLT_LBI, NULL, NULL, NULL, OCI_DYNAMIC_FETCH);
printf("RESULT 9: %d\n", rc);
rc = OCIStmtExecute(p_svc, p_sql, p_err, 1, 0, NULL, NULL, OCI_DEFAULT);
printf("RESULT 10: %d\n", rc); // <-- rc equals 99=OCI_NEED_DATA
rc = OCIStmtFetch(p_sql, p_err, 1, 0, OCI_DEFAULT); // <-- This crashes
printf("RESULT 11: %d\n", rc);
while (rc == OCI_NEED_DATA)
{
void* hndlp;
ub4 type;
ub1 in_out;
ub4 iter;
ub4 idx;
ub1 piece;
ub4 alen;
printf("RESULT 12a\n");
rc = OCIStmtGetPieceInfo(p_sql, p_err, &hndlp, &type, &in_out, &iter, &idx, &piece);
printf("RESULT 12b %d, %d, %d, %d, %d, %d\n", (int)rc, (int)type, (int)in_out, (int)iter, (int)idx, (int)piece);
alen = sizeof(buffer);
rc = OCIStmtSetPieceInfo(hndlp, type, p_err, buffer, &alen, piece, NULL, NULL);
printf("RESULT 12c %d, %d\n", (int)rc, (int)alen);
rc = OCIStmtFetch(p_sql, p_err, 1, OCI_FETCH_NEXT, OCI_DEFAULT);
printf("RESULT 12d: %d\n", rc);
}
printf("RESULT 13: %d\n", rc);
rc = OCILogoff(p_svc, p_err);
rc = OCIHandleFree(p_sql, OCI_HTYPE_STMT);
rc = OCIHandleFree(p_svc, OCI_HTYPE_SVCCTX);
rc = OCIHandleFree(p_err, OCI_HTYPE_ERROR);
return (0);
} // main
I figured it out myself:
First of all, the parameter iters within the call to OCIStmtExecute must be set to 0.
This avoids the crash but doesn't resolve the whole problem.
Furthermore, the parameter value_sz within the call to OCIDefineByPos must be set to the actual size of the column being read.
Finally, the buffer that is passed as the 4th parameter of OCIStmtSetPieceInfo can be read only after the successive call to OCIStmtFetch.
This results in the following code:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>
static const char uid[] = "XXXX";
static const char pswd[] = "XXXX";
static const char conn[] = "XXXX";
static const char fileid[] = "XXXX";
int main(int argc, char argv[])
{
OCIEnv* p_env;
OCIError* p_err;
OCISvcCtx* p_svc;
OCIStmt* p_sql;
OCIDefine* p_dfn;
OCIBind* p_bnd;
int rc;
char stmt[256];
unsigned char buffer[8192];
int counter;
long filesize;
// Connect to DB
rc = OCIInitialize(OCI_DEFAULT, NULL, (dvoid* (*)(dvoid*, size_t))NULL, (dvoid* (*)(dvoid*, dvoid*, size_t))NULL, (void (*)(dvoid*, dvoid*))NULL);
printf("RESULT 1: %d\n", rc);
rc = OCIEnvInit(&p_env, OCI_DEFAULT, 0, NULL);
printf("RESULT 2: %d\n", rc);
rc = OCIHandleAlloc(p_env, (void**)&p_err, OCI_HTYPE_ERROR, 0, NULL);
printf("RESULT 3: %d\n", rc);
rc = OCIHandleAlloc(p_env, (void**)&p_svc, OCI_HTYPE_SVCCTX, 0, NULL);
printf("RESULT 4: %d\n", rc);
rc = OCILogon(p_env, p_err, &p_svc, (text*)uid, (ub4)strlen(uid), (text*)pswd, (ub4)strlen(pswd), (text*)conn, (ub4)strlen(conn));
printf("RESULT 5: %d\n", rc);
// Determine size of data
rc = OCIHandleAlloc(p_env, (void**)&p_sql, OCI_HTYPE_STMT, 0, NULL);
printf("RESULT 6: %d\n", rc);
sprintf(stmt, "SELECT filesize FROM td_planimetrie WHERE id_file = :x");
rc = OCIStmtPrepare(p_sql, p_err, (text*)stmt, (ub4)strlen(stmt), OCI_NTV_SYNTAX, OCI_DEFAULT);
printf("RESULT 6a: %d\n", rc);
rc = OCIBindByName(p_sql, &p_bnd, p_err, (text*)":x", -1, (text*)fileid, (sb4)strlen(fileid) + 1, SQLT_STR, NULL, NULL, NULL, 0, NULL, OCI_DEFAULT);
printf("RESULT 6b: %d\n", rc);
rc = OCIDefineByPos(p_sql, &p_dfn, p_err, 1, &filesize, sizeof(filesize), SQLT_INT, NULL, NULL, NULL, OCI_DEFAULT);
printf("RESULT 6c: %d\n", rc);
rc = OCIStmtExecute(p_svc, p_sql, p_err, 1, 0, NULL, NULL, OCI_DEFAULT);
printf("RESULT 6d: %d %ld\n", rc, filesize);
rc = OCIHandleFree(p_sql, OCI_HTYPE_STMT);
printf("RESULT 6d: %d\n", rc);
// Prepare reading data
rc = OCIHandleAlloc(p_env, (void**)&p_sql, OCI_HTYPE_STMT, 0, NULL);
printf("RESULT 7: %d\n", rc);
sprintf(stmt, "SELECT content FROM td_planimetrie WHERE id_file = :x");
rc = OCIStmtPrepare(p_sql, p_err, (text*)stmt, (ub4)strlen(stmt), OCI_NTV_SYNTAX, OCI_DEFAULT);
printf("RESULT 7a: %d\n", rc);
rc = OCIBindByName(p_sql, &p_bnd, p_err, (text*)":x", -1, (text*)fileid, (sb4)strlen(fileid) + 1, SQLT_STR, NULL, NULL, NULL, 0, NULL, OCI_DEFAULT);
printf("RESULT 8: %d\n", rc);
rc = OCIDefineByPos(p_sql, &p_dfn, p_err, 1, NULL, filesize, SQLT_LBI, NULL, NULL, NULL, OCI_DYNAMIC_FETCH);
printf("RESULT 9: %d\n", rc);
rc = OCIStmtExecute(p_svc, p_sql, p_err, 0, 0, NULL, NULL, OCI_DEFAULT);
printf("RESULT 10: %d\n", rc);
rc = OCIStmtFetch(p_sql, p_err, 1, 0, OCI_DEFAULT);
printf("RESULT 11: %d\n", rc);
// Read data
unsigned long size_left = filesize;
while (rc == OCI_NEED_DATA)
{
void* hndlp;
ub4 type;
ub1 in_out;
ub4 iter;
ub4 idx;
ub1 piece;
ub4 alen;
rc = OCIStmtGetPieceInfo(p_sql, p_err, &hndlp, &type, &in_out, &iter, &idx, &piece);
printf("RESULT 12a %d, %d, %d, %d, %d, %d\n", (int)rc, (int)type, (int)in_out, (int)iter, (int)idx, (int)piece);
alen = sizeof(buffer);
memset(buffer, 0xDA, sizeof(buffer)); // some arbitrary data .....
rc = OCIStmtSetPieceInfo(hndlp, type, p_err, buffer, &alen, piece, NULL, NULL);
printf("RESULT 12b %d, %d\n", (int)rc, (int)alen);
rc = OCIStmtFetch(p_sql, p_err, 1, OCI_DEFAULT, OCI_DEFAULT);
printf("RESULT 12c: %d\n", rc);
// We can use the buffer over here
long nr_read = (size_left < alen) ? size_left : alen;
// save 'nr_read' bytes within the buffer
size_left -= nr_read;
}
printf("Size left: %lu\n", size_left);
// Cleaning up...
rc = OCILogoff(p_svc, p_err);
rc = OCIHandleFree(p_sql, OCI_HTYPE_STMT);
rc = OCIHandleFree(p_svc, OCI_HTYPE_SVCCTX);
rc = OCIHandleFree(p_err, OCI_HTYPE_ERROR);
return (0);
} // main