Почему OCILobWrite возвращает OCI_INVALID_HANDLE?

Question

Я хочу вставить CLOB в тестовую таблицу. Но OCILobWrite() всегда возвращается OCI_INVALID_HANDLE,

Что здесь не так? Заранее спасибо!

sword rc;
OraText* query;
OCIEnv* environment;
OCIError* error;
OCISvcCtx* serviceContext;
OCIStmt* statement;
OCILobLocator* lobLocator;

rc = OCIEnvCreate(&environment, OCI_DEFAULT, 0, 0, 0, 0, 0, 0);
rc = OCIHandleAlloc(environment, (dvoid**) &error, OCI_HTYPE_ERROR, 0, 0);
rc = OCIHandleAlloc(environment, (dvoid**) &serviceContext, OCI_HTYPE_SVCCTX, 0, 0);

rc = OCILogon(environment, error, &serviceContext, (OraText*) username,
                strlen(username), (OraText*) password, strlen(password), (OraText*) database, strlen(database));

query = (OraText*) "DROP TABLE test_clob";
rc = OCIHandleAlloc(environment, (dvoid **) &statement, OCI_HTYPE_STMT, 0, 0);
rc = OCIStmtPrepare(statement, error, query, strlen((const char*) query), OCI_NTV_SYNTAX, OCI_DEFAULT);
rc = OCIStmtExecute(serviceContext, statement, error, 1, 0, NULL, NULL, OCI_DEFAULT);
rc = OCIHandleFree(statement, OCI_HTYPE_STMT);

query = (OraText*) "CREATE TABLE test_clob (text CLOB)";
rc = OCIHandleAlloc(environment, (dvoid **) &statement, OCI_HTYPE_STMT, 0, 0);
rc = OCIStmtPrepare(statement, error, query, strlen((const char*) query), OCI_NTV_SYNTAX, OCI_DEFAULT);
rc = OCIStmtExecute(serviceContext, statement, error, 1, 0, NULL, NULL, OCI_DEFAULT);
rc = OCIHandleFree(statement, OCI_HTYPE_STMT);

const char* text = "arbitrary CLOB content";
query = (OraText*) "INSERT INTO test_clob VALUES (:text)";
ub4 size = strlen(text);
rc = OCIHandleAlloc(environment, (dvoid **) &statement, OCI_HTYPE_STMT, 0, 0);
rc = OCIStmtPrepare(statement, error, query, strlen((const char*) query), OCI_NTV_SYNTAX, OCI_DEFAULT);
rc = OCIDescriptorAlloc(environment, (dvoid**) &lobLocator, OCI_DTYPE_LOB, 0, 0);
rc = OCILobWrite(serviceContext, error, lobLocator, &size, 1, (void*) text, size, OCI_ONE_PIECE, 0, 0, 0, SQLCS_IMPLICIT);

if (rc == OCI_INVALID_HANDLE) printf("why???");

Answer 1

Тем временем я решил свою LOB-проблему. У меня работает следующий код. Он демонстрирует чтение и запись во временные большие объекты.

sword rc;
OraText* query;
OCIEnv* environment;
OCIError* error;
OCISvcCtx* serviceContext;
OCIStmt* statement;
OCILobLocator* lobLocator;
OCIBind* bind = NULL;
OraText errorMessage[OCI_ERROR_MAXMSG_SIZE];

rc = OCIEnvCreate(&environment, OCI_DEFAULT, 0, 0, 0, 0, 0, 0);
rc = OCIHandleAlloc(environment, (dvoid**) &error, OCI_HTYPE_ERROR, 0, 0);
rc = OCIHandleAlloc(environment, (dvoid**) &serviceContext, OCI_HTYPE_SVCCTX, 0, 0);

rc = OCILogon(environment, error, &serviceContext, (OraText*) username,
                strlen(username), (OraText*) password, strlen(password), (OraText*) database, strlen(database));

query = (OraText*) "DROP TABLE test_clob";
rc = OCIHandleAlloc(environment, (dvoid **) &statement, OCI_HTYPE_STMT, 0, 0);
rc = OCIStmtPrepare(statement, error, query, strlen((const char*) query), OCI_NTV_SYNTAX, OCI_DEFAULT);
rc = OCIStmtExecute(serviceContext, statement, error, 1, 0, NULL, NULL, OCI_DEFAULT);
rc = OCIHandleFree(statement, OCI_HTYPE_STMT);

query = (OraText*) "CREATE TABLE test_clob (text CLOB)";
rc = OCIHandleAlloc(environment, (dvoid **) &statement, OCI_HTYPE_STMT, 0, 0);
rc = OCIStmtPrepare(statement, error, query, strlen((const char*) query), OCI_NTV_SYNTAX, OCI_DEFAULT);
rc = OCIStmtExecute(serviceContext, statement, error, 1, 0, NULL, NULL, OCI_DEFAULT);
rc = OCIHandleFree(statement, OCI_HTYPE_STMT);

ub4 textSize = 9 * 1024 * 1024;
char* text = (char*) calloc(textSize, 1);

srand(time(NULL));
for (ub4 i = 0; i < textSize; i++) text[i] = 97 + (rand() % 26);

query = (OraText*) "INSERT INTO test_clob VALUES(:text)";
rc = OCIHandleAlloc(environment, (dvoid **) &statement, OCI_HTYPE_STMT, 0, 0);
rc = OCIStmtPrepare(statement, error, query, strlen((const char*) query), OCI_NTV_SYNTAX, OCI_DEFAULT);
rc = OCIDescriptorAlloc(environment, (dvoid**) &lobLocator, OCI_DTYPE_LOB, 0, 0);
rc = OCILobCreateTemporary(serviceContext, error, lobLocator, OCI_DEFAULT, SQLCS_IMPLICIT, OCI_TEMP_CLOB, 0, OCI_DURATION_SESSION);
rc = OCIBindByName(statement, &bind, error, (OraText*) "text", strlen("text"), &lobLocator, sizeof (OCILobLocator *), SQLT_CLOB, 0, 0, 0, 0, 0, OCI_DEFAULT);
rc = OCILobWrite(serviceContext, error, lobLocator, &textSize, 1, (void*) text, textSize + 1, OCI_ONE_PIECE, 0, 0, 0, SQLCS_IMPLICIT);
rc = OCIStmtExecute(serviceContext, statement, error, 1, 0, NULL, NULL, OCI_DEFAULT);
rc = OCILobFreeTemporary(serviceContext, error, lobLocator);
rc = OCIDescriptorFree(lobLocator, OCI_DTYPE_LOB);
rc = OCIHandleFree(statement, OCI_HTYPE_STMT);

rc = OCITransCommit(serviceContext, error, OCI_DEFAULT);

query = (OraText*) "SELECT text FROM test_clob";
rc = OCIHandleAlloc(environment, (dvoid **) &statement, OCI_HTYPE_STMT, 0, 0);
rc = OCIStmtPrepare(statement, error, query, strlen((const char*) query), OCI_NTV_SYNTAX, OCI_DEFAULT);
rc = OCIStmtExecute(serviceContext, statement, error, 0, 0, 0, 0, OCI_DEFAULT);

ub4 columnNumber = 1;
ub2 columnType;
OCIParam* columnTypeParameter;
OCIParamGet(statement, OCI_HTYPE_STMT, error, (dvoid **) &columnTypeParameter, columnNumber);
OCIAttrGet(columnTypeParameter, OCI_DTYPE_PARAM, &columnType, 0, OCI_ATTR_DATA_TYPE, error);
OCIDescriptorFree(columnTypeParameter, OCI_DTYPE_PARAM);

OCIDefine* define;
short nullIndicator;
rc = OCIDescriptorAlloc(environment, (dvoid**) &lobLocator, OCI_DTYPE_LOB, 0, 0);
rc = OCIDefineByPos(statement, &define, error, columnNumber, &lobLocator, sizeof (OCILobLocator *), SQLT_CLOB, &nullIndicator, 0, 0, OCI_DEFAULT);

rc = OCIStmtFetch2(statement, error, 1, OCI_DEFAULT, 0, OCI_DEFAULT);

ub4 lobSize;
rc = OCILobGetLength(serviceContext, error, lobLocator, &lobSize);

char* valuep = (char*) calloc(1, lobSize + 1);
rc = OCILobRead(serviceContext, error, lobLocator, &textSize, 1, valuep, lobSize + 1, 0, 0, 0, SQLCS_IMPLICIT);
rc = OCIDescriptorFree(lobLocator, OCI_DTYPE_LOB);

rc = OCILogoff(serviceContext, error);