虚谷文档 v1.0.2

适用虚谷数据库版本

v12.9



适用虚谷数据库版本

v12.9


使用示例 ​

📄字数 6.9K
👁️阅读量 加载中...

连接数据库 ​

NCI 连接数据库有两种方式，第一种如示例 1，此方式较为繁琐。第二种如示例 2，此方式隐藏了部分细节，使用相对方便。

示例 1

C
#include <stdio.h>
#include <string.h>
#include "oci.h"

void printError(NCIError* err)
{
    sb4 errCode;
    char errMsg[128];
    NCIErrorGet(err, 1, NULL, &errCode, errMsg, 128, NCI_HTYPE_ERROR);
    printf("[%d]%s\n", errCode, errMsg);
}

int main()
{
    NCIEnv* env;
    NCIError* err;
    NCIServer* server;
    NCISvcCtx* ctx;
    NCISession* session;
    NCIStmt* stmt;
    sword ret;
    NCIEnvCreate(&env, NCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL);
    NCIHandleAlloc(env, &err, NCI_HTYPE_ERROR, 0, NULL);
    NCIHandleAlloc(env, &server, NCI_HTYPE_SERVER, 0, NULL);
    const char* dblink = "127.0.0.1:5138/SYSTEM";
    ret = NCIServerAttach(server, err, dblink, strlen(dblink), NCI_DEFAULT);

    NCIHandleAlloc(env, &ctx, NCI_HTYPE_SVCCTX, 0, NULL);
    NCIAttrSet(ctx, NCI_HTYPE_SVCCTX, server, 0, NCI_ATTR_SERVER, err);

    NCIHandleAlloc(env, &session, NCI_HTYPE_SESSION, 0, NULL);
    const char* user = "SYSDBA";
    const char* pwd = "SYSDBA";
    NCIAttrSet(session, NCI_HTYPE_SESSION, user, strlen(user), NCI_ATTR_USERNAME, err);
    NCIAttrSet(session, NCI_HTYPE_SESSION, pwd, strlen(pwd), NCI_ATTR_PASSWORD, err);
    NCIAttrSet(ctx, NCI_HTYPE_SVCCTX, session, 0, NCI_ATTR_SESSION, err);

    ret = NCISessionBegin(ctx, err, session, NCI_CRED_RDBMS, NCI_DEFAULT);
    if (ret != NCI_SUCCESS)
    {
        printError(err);
        goto _end;
    }

    NCIHandleAlloc(env, &stmt, NCI_HTYPE_STMT, 0, NULL);

    // do something to database

    NCIHandleFree(stmt, NCI_HTYPE_STMT);

_end:
    NCILogoff(ctx, err);
    NCIServerDetach(server, err, NCI_DEFAULT);
    NCIHandleFree(session, NCI_HTYPE_SESSION);
    NCIHandleFree(ctx, NCI_HTYPE_SVCCTX);
    NCIHandleFree(server, NCI_HTYPE_SERVER);
    NCIHandleFree(err, NCI_HTYPE_ERROR);
    NCIHandleFree(env, NCI_HTYPE_ENV);
    return 0;
}

示例 2

C
#include <stdio.h>
#include <string.h>
#include "oci.h"

void printError(NCIError* err)
{
    sb4 errCode;
    char errMsg[128];
    NCIErrorGet(err, 1, NULL, &errCode, errMsg, 128, NCI_HTYPE_ERROR);
    printf("[%d]%s\n", errCode, errMsg);
}

int main()
{
    NCIEnv* env;
    NCIError* err;
    NCISvcCtx* ctx;
    NCIStmt* stmt;
    sword ret;
    NCIEnvCreate(&env, NCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL);
    NCIHandleAlloc(env, &err, NCI_HTYPE_ERROR, 0, NULL);

    const char* user = "SYSDBA";
    const char* pwd = "SYSDBA";
    const char* db = "127.0.0.1:5138/SYSTEM";
    ret = NCILogon(env, err, &ctx, user, strlen(user), pwd, strlen(pwd), db, strlen(db));
    if (ret != NCI_SUCCESS)
    {
        printError(err);
        goto _end;
    }
    NCIHandleAlloc(env, &stmt, NCI_HTYPE_STMT, 0, NULL);

    // do something to database

    NCIHandleFree(stmt, NCI_HTYPE_STMT);
_end:
    NCILogoff(ctx, err);
    NCIHandleFree(ctx, NCI_HTYPE_SVCCTX);
    NCIHandleFree(err, NCI_HTYPE_ERROR);
    NCIHandleFree(env, NCI_HTYPE_ENV);
    return 0;
}

执行 DDL 语句 ​

c
void createTable(NCISvcCtx* ctx, NCIStmt* stmt, NCIError* err)
{
    sword ret;
    const char* sql = "CREATE TABLE tab_test (id INT, name VARCHAR(20), created DATE, lob BLOB)";
    ret = NCIStmtPrepare(stmt, err, sql, strlen(sql), NCI_NTV_SYNTAX, NCI_DEFAULT);
    if (ret != NCI_SUCCESS) printError(err);

    ret = NCIStmtExecute(ctx, stmt, err, 1, 0, NULL, NULL, NCI_DEFAULT);
    if (ret != NCI_SUCCESS) printError(err);
}

执行 INSERT 语句 ​

c
void insert(NCISvcCtx* ctx, NCIStmt* stmt, NCIError* err)
{
    sword ret;
    const char* sql = "INSERT INTO TAB_TEST (id, name, created) VALUES(:id, :name, :created)";
    ret = NCIStmtPrepare(stmt, err, sql, strlen(sql), NCI_NTV_SYNTAX, NCI_DEFAULT);

    NCIBind* bind[3];
    int id = 1;
    const char* name = "Tom";
    NCIDate created = { 2000,1,1 };
    NCIBindByName(stmt, &bind[0], err, "id", 2, &id, 4, SQLT_INT, NULL, NULL, NULL, 0, NULL, NCI_DEFAULT);
    NCIBindByName(stmt, &bind[1], err, "name", 4, name, sizeof(name), SQLT_CHR, NULL, NULL, NULL, 0, NULL, NCI_DEFAULT);
    NCIBindByName(stmt, &bind[2], err, "created", 7, &created, sizeof(created), SQLT_ODT, NULL, NULL, NULL, 0, NULL, NCI_DEFAULT);

    ret = NCIStmtExecute(ctx, stmt, err, 1, 0, NULL, NULL, NCI_DEFAULT);
}

批量插入 ​

C
void insertBatch(NCISvcCtx* ctx, NCIStmt* stmt, NCIError* err)
{
    sword ret;
    const char* sql = "INSERT INTO tab_test (id, name, created) VALUES(:id, :name, :created)";
    ret = NCIStmtPrepare(stmt, err, sql, strlen(sql), NCI_NTV_SYNTAX, NCI_DEFAULT);

    NCIBind* bind[3];
    int id[2] = { 1, 2 };
    char name[2][20] = { "Tom", "Jerry" };
    NCIDate created[2] = { { 2000,1,1 }, {2000,1,2} };
    NCIBindByName(stmt, &bind[0], err, "id", 2, id, 4, SQLT_INT, NULL, NULL, NULL, 0, NULL, NCI_DEFAULT);
    NCIBindByName(stmt, &bind[1], err, "name", 4, name, sizeof(name[0]), SQLT_CHR, NULL, NULL, NULL, 0, NULL, NCI_DEFAULT);
    NCIBindByName(stmt, &bind[2], err, "created", 7, created, 8, SQLT_ODT, NULL, NULL, NULL, 0, NULL, NCI_DEFAULT);

    ret = NCIStmtExecute(ctx, stmt, err, 2, 0, NULL, NULL, NCI_DEFAULT);
    if (ret != NCI_SUCCESS) printError(err);
}

执行查询 ​

C
void query(NCISvcCtx* ctx, NCIStmt* stmt, NCIError* err)
{
    sword ret;
    const char* sql = "SELECT id, name, created FROM tab_test WHERE id = :id";
    ret = NCIStmtPrepare(stmt, err, sql, strlen(sql), NCI_NTV_SYNTAX, NCI_DEFAULT);

    int id = 1;
    char name[20];
    NCIDate created;
    ub2 ind[3] = { 0 };
    ub2 rlen[3];
    NCIBind* bind;
    NCIBindByName(stmt, &bind, err, "id", 2, &id, 4, SQLT_INT, NULL, NULL, NULL, 0, NULL, NCI_DEFAULT);

    NCIDefine* def[3];
    NCIDefineByPos(stmt, &def[0], err, 1, &id, sizeof(id), SQLT_INT, &ind[0], &rlen[0], NULL, NCI_DEFAULT);
    NCIDefineByPos(stmt, &def[1], err, 2, name, sizeof(name), SQLT_STR, &ind[1], &rlen[1], NULL, NCI_DEFAULT);
    NCIDefineByPos(stmt, &def[2], err, 3, &created, sizeof(created), SQLT_ODT, &ind[2], &rlen[2], NULL, NCI_DEFAULT);

    ret = NCIStmtExecute(ctx, stmt, err, 0, 0, NULL, NULL, NCI_DEFAULT);
    if (ret != NCI_SUCCESS) printError(err);

    while ((ret = NCIStmtFetch(stmt, err, 1, NCI_FETCH_NEXT, NCI_DEFAULT)) == NCI_SUCCESS)
    {
        printf("%d, %s, %d-%02d-%02d\n", id, name, created.NCIDateYYYY, created.NCIDateMM, created.NCIDateDD);
    }
}

插入 LOB ​

示例 1 将数据一次性从文件读取并写入 Lob 中，示例 2 通过回调函数多次从文件读取数据片段，再将读取到的数据片段写入 Lob 中。除此之外，在 Oracle 中可以将数据全部读入一个缓冲区中，再将缓冲区以 SQLT_BIN 类型绑定至 INSERT 语句的参数中以实现二进制数据的写入。XuguNCI 暂不兼容此用法，因此不提供此用法的示例。

示例 1

C
void insertLob(NCIEnv* env, NCISvcCtx* ctx, NCIStmt* stmt, NCIError* err)
{
    sword ret;
    const char* sql = "INSERT INTO tab_test(id, lob) VALUES(1, empty_blob())";
    NCIStmtPrepare(stmt, err, sql, strlen(sql), NCI_NTV_SYNTAX, NCI_DEFAULT);
    NCIStmtExecute(ctx, stmt, err, 1, 0, NULL, NULL, NCI_DEFAULT);

    sql = "SELECT id, lob FROM tab_test WHERE id = 1";
    NCIStmtPrepare(stmt, err, sql, strlen(sql), NCI_NTV_SYNTAX, NCI_DEFAULT);

    NCILobLocator* lob;
    NCIDescriptorAlloc(env, &lob, NCI_DTYPE_LOB, 0, NULL);

    NCIDefine* def;
    NCIDefineByPos(stmt, &def, err, 2, &lob, 0, SQLT_BLOB, NULL, NULL, NULL, NCI_DEFAULT);
    NCIStmtExecute(ctx, stmt, err, 1, 0, NULL, NULL, NCI_DEFAULT);

    FILE* fp = fopen("blob.png", "rb");
    fseek(fp, 0, SEEK_END);
    size_t sz = ftell(fp);
    fseek(fp, 0, SEEK_SET);

    void* buf = malloc(sz);
    fread(buf, sz, 1, fp);
    fclose(fp);

    ub4 amt = sz;
    ret = NCILobWrite(ctx, err, lob, &amt, 1, buf, amt, NCI_ONE_PIECE, NULL, NULL, NCI_DEFAULT, SQLT_BIN);
    free(buf);
    if (ret != NCI_SUCCESS) printError(err);

    NCITransCommit(ctx, err, NCI_DEFAULT);
    NCIDescriptorFree(lob, NCI_DTYPE_LOB);
}

示例 2

C
sb4 lobWriteCB(void* ctxp, void* bufp, ub4* lenp, ub1* piece)
{
    *lenp = fread(bufp, 1, 1024, ctxp);
    if (*lenp < 1024)
        *piece = NCI_LAST_PIECE;
    else
        *piece = NCI_NEXT_PIECE;
    return NCI_CONTINUE;
}

void insertLobWithCB(NCIEnv* env, NCISvcCtx* ctx, NCIStmt* stmt, NCIError* err)
{
    sword ret;
    const char* sql = "INSERT INTO tab_test(id, lob) VALUES(1, empty_blob())";
    NCIStmtPrepare(stmt, err, sql, strlen(sql), NCI_NTV_SYNTAX, NCI_DEFAULT);
    NCIStmtExecute(ctx, stmt, err, 1, 0, NULL, NULL, NCI_DEFAULT);

    sql = "SELECT id, lob FROM tab_test WHERE id = 1";
    NCIStmtPrepare(stmt, err, sql, strlen(sql), NCI_NTV_SYNTAX, NCI_DEFAULT);

    NCILobLocator* lob;
    NCIDescriptorAlloc(env, &lob, NCI_DTYPE_LOB, 0, NULL);

    NCIDefine* def;
    NCIDefineByPos(stmt, &def, err, 2, &lob, 0, SQLT_BLOB, NULL, NULL, NULL, NCI_DEFAULT);
    NCIStmtExecute(ctx, stmt, err, 1, 0, NULL, NULL, NCI_DEFAULT);

    FILE* fp = fopen("blob.png", "rb");
    fseek(fp, 0, SEEK_END);
    size_t sz = ftell(fp);
    fseek(fp, 0, SEEK_SET);

    char buf[1024];
    fread(buf, 1024, 1, fp);

    ub4 amt = sz;
    ret = NCILobWrite(ctx, err, lob, &amt, 1, buf, 1024, NCI_FIRST_PIECE, fp, lobWriteCB, NCI_DEFAULT, SQLT_BIN);
    if (ret != NCI_SUCCESS) printError(err);

    NCITransCommit(ctx, err, NCI_DEFAULT);
    NCIDescriptorFree(lob, NCI_DTYPE_LOB);
}

查询 LOB ​

示例 1

C
void queryLob(NCIEnv* env, NCISvcCtx* ctx, NCIStmt* stmt, NCIError* err)
{
    sword ret;
    const char* sql = "SELECT lob FROM tab_test WHERE id = 1";
    NCIStmtPrepare(stmt, err, sql, strlen(sql), NCI_NTV_SYNTAX, NCI_DEFAULT);

    NCIDefine* def;
    NCILobLocator* lob;
    NCIDescriptorAlloc(env, &lob, NCI_DTYPE_LOB, 0, NULL);
    NCIDefineByPos(stmt, &def, err, 1, &lob, 0, SQLT_BLOB, NULL, NULL, NULL, NCI_DEFAULT);

    NCIStmtExecute(ctx, stmt, err, 0, 0, NULL, NULL, NCI_DEFAULT);
    NCIStmtFetch(stmt, err, 1, NCI_FETCH_NEXT, NCI_DEFAULT);

    FILE* fp = fopen("blob_out.png", "wb");

    char buf[1024];
    ub4 amt;
    do
    {
        ret = NCILobRead(ctx, err, lob, &amt, 1, buf, 1024, NULL, NULL, 0, SQLT_BIN);
        fwrite(buf, amt, 1, fp);
    } while (ret == NCI_NEED_DATA);
    fclose(fp);

    NCIDescriptorFree(lob, NCI_DTYPE_LOB);
}

示例 2

C
sb4 lobReadCB(void* ctxp, const void* bufp, ub4 len, ub1 piece)
{
    fwrite(bufp, len, 1, ctxp);
    return NCI_CONTINUE;
}

void queryLobWithCB(NCIEnv* env, NCISvcCtx* ctx, NCIStmt* stmt, NCIError* err)
{
    sword ret;
    const char* sql = "SELECT lob FROM tab_test WHERE id  = 1";
    NCIStmtPrepare(stmt, err, sql, strlen(sql), NCI_NTV_SYNTAX, NCI_DEFAULT);

    NCIDefine* def;
    NCILobLocator* lob;
    NCIDescriptorAlloc(env, (void**)&lob, NCI_DTYPE_LOB, 0, NULL);
    NCIDefineByPos(stmt, &def, err, 1, &lob, 0, SQLT_BLOB, NULL, NULL, NULL, NCI_DEFAULT);

    ret = NCIStmtExecute(ctx, stmt, err, 0, 0, NULL, NULL, NCI_DEFAULT);
    ret = NCIStmtFetch(stmt, err, 1, NCI_FETCH_NEXT, NCI_DEFAULT);

    FILE* fp = fopen("blob_out.png", "wb");
    char buf[1024];
    ub4 amt;
    ret = NCILobRead(ctx, err, lob, &amt, 1, buf, 1024, fp, lobReadCB, 0, SQLT_BIN);
    fclose(fp);

    NCIDescriptorFree(lob, NCI_DTYPE_LOB);
}