虚谷文档 v1.0.1

适用虚谷数据库版本

v12.9



适用虚谷数据库版本

v12.9


示例 ​

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

连接的建立与释放 ​

C
#include <stdio.h>
#include "xgci.h"

void printError(XGCIHANDLE handle)
{
    char errCode[7] = { 0 };
    char errMsg[256] = { 0 };
    int len = 0;
    XGCIErrors(handle, errCode, errMsg, &len);
    printf("[%s] %s\n", errCode, errMsg);
}

int main()
{
    XGCIHANDLE henv;
    XGCIHANDLE hserver;
    XGCIHANDLE hsession;
    XGCIHANDLE hstmt;

    unsigned short port = 5138;
    int ret;

    XGCIHandleAlloc(NULL, &henv, HT_ENV);
    XGCIHandleAlloc(henv, &hserver, HT_SERVER);
    XGCIHandleAttrSet(hserver, XGCI_ATTR_SRV_IP, "127.0.0.1", XGCI_NTS);
    XGCIHandleAttrSet(hserver, XGCI_ATTR_SRV_PORT, &port, 0);
    XGCIHandleAttrSet(hserver, XGCI_ATTR_SRV_DBNAME, "SYSTEM", XGCI_NTS);

    XGCIHandleAlloc(hserver, &hsession, HT_SESSION);
    XGCIHandleAlloc(hsession, &hstmt, HT_STATEMENT);

    //建立连接
    ret = XGCISessionBegin(hsession, (char*)"SYSDBA", (char*)"SYSDBA");
    if (ret == XGCI_SUCCESS) {
        //释放连接
        XGCISessionEnd(hsession);
    } else {
        printError(hsession);
    }

    XGCIHandleFree(hstmt);
    XGCISessionEnd(hsession);
    XGCIHandleFree(hserver);
    XGCIHandleFree(henv);
    return ret;
}

按位参数绑定插入 ​

C
void bindParamByPos(XGCIHANDLE hstmt)
{
    const char* sql = "INSERT INTO tab_test (id, name, created) VALUES(?, ?, ?)";
    XGCIPrepare(hstmt, (char*)sql, XGCI_NTS);

    int id = 1;
    char name[20] = "张三";
    DATE_STRUCT date = { 2000, 1, 1 };
    int len1 = 1; //定长类型，取值 > 0 即可
    int len2 = strlen(name); //变长类型，取实际值
    int len3 = 1; //定长类型，取值 > 0 即可
    XGCIBindParamByPos(hstmt, 1, PARAM_IN, &id, 0, XG_C_INTEGER, NULL, &len1, SQL_INTEGER);
    XGCIBindParamByPos(hstmt, 2, PARAM_IN, name, 20, XG_C_CHAR, NULL, &len2, SQL_CHAR);
    XGCIBindParamByPos(hstmt, 3, PARAM_IN, &date, 0, XG_C_DATE, NULL, &len3, SQL_DATE);

    int ret = XGCIExecute(hstmt);
    if (ret != XGCI_SUCCESS)
        printError(hstmt);
    else
        printf("ok\n");
}

按名参数绑定的插入 ​

C
void bindParamByName(XGCIHANDLE hstmt)
{
    const char* sql = "INSERT INTO TAB_test (id, name, created) VALUES(:id, :name, :created)";
    XGCIPrepare(hstmt, (char*)sql, XGCI_NTS);

    int id;
    char name[20];
    DATE_STRUCT date = { 2000, 1, 1 };
    int len1 = 1;
    int len2 = 0;
    int len3 = 1;
    XGCIBindParamByName(hstmt, (char*)"id", PARAM_IN, &id, 0, XG_C_INTEGER, NULL, &len1, SQL_INTEGER);
    XGCIBindParamByName(hstmt, (char*)"name", PARAM_IN, name, 20, XG_C_CHAR, NULL, &len2, SQL_CHAR);
    XGCIBindParamByName(hstmt, (char*)"created", PARAM_IN, &date, 0, XG_C_DATE, NULL, &len3, SQL_DATE);

    // insert 10 rows
    for (int i = 0; i < 10; i++) {
        id = i + 1;
        len2 = sprintf(name, "张三%d", i);
        date.day = i + 1;
        ret = XGCIExecute(hstmt);
        if (ret != XGCI_SUCCESS)
            printError(hstmt);
        else
            printf("ok\n");
    }
}

按名参数绑定的批量插入 ​

C
void insertBatch(XGCIHANDLE hstmt)
{
    const char* sql = "insert into tab_test (id, name, created) values(:id, :name, :created)";
    int ret;

    int idArr[3] = { 1,2,3 };
    const char* nameArr[3] = { "AAA", "BBB", "CCC" };
    DATE_STRUCT dateArr[3] = { {2000,1,1}, {2000,1,2}, {2000,1,3} };
    int len1[3] = { 1,1,1 };
    int len2[3] = { 3,2,1 };
    int len3[3] = { 1,0,1 };

    XGCIBindParamArrayByName(hstmt, (char*)"id", 3, PARAM_IN, 3, idArr, 0, XG_C_INTEGER, NULL, len1, SQL_INTEGER);
    XGCIBindParamArrayByName(hstmt, (char*)"name", 3, PARAM_IN, 3, nameArr, 1, XG_C_CHAR, NULL, len2, SQL_CHAR);
    XGCIBindParamArrayByName(hstmt, (char*)"created", 3, PARAM_IN, 3, dateArr, 0, XG_C_DATE, NULL, len3, SQL_DATE);

    ret = XGCIExecDirectBatch(hstmt, (char*)sql, XGCI_NTS);
    if (ret != XGCI_SUCCESS)
        printError(hstmt);
}

数据查询 ​

C
void query(XGCIHANDLE hstmt)
{
    const char* sql = "SELECT id, name, created FROM tab_test WHERE id = ?";

    int id = 1;
    XGCIBindParamByPos(hstmt, 1, PARAM_IN, &id, 0, XG_C_INTEGER, NULL, NULL, SQL_INTEGER);

    //使用 XGCIExecDirect 执行带参数的 SQL，不需进行 prepare 操作
    int ret = XGCIExecDirect(hstmt, (char*)sql, XGCI_NTS);

    if (ret != XGCI_SUCCESS) {
        printError(hstmt);
    } else {
        char name[20];
        DATE_STRUCT date;
        int rc[3];
        int len[3];
        XGCIDefineByPos(hstmt, 1, &id, 0, XG_C_INTEGER, &rc[0], &len[0]);
        XGCIDefineByPos(hstmt, 2, name, 20, XG_C_CHAR, &rc[1], &len[1]);
        XGCIDefineByPos(hstmt, 3, &date, 0, XG_C_DATE, &rc[2], &len[2]);

        while ((ret = XGCIFetch(hstmt)) == XGCI_SUCCESS) {
            printf("%d, %s, %d-%d-%d\n", id, name, date.year, date.month, date.day);
        }
    }
}

存储过程调用 ​

SQL
-- 创建存储过程
CREATE OR REPLACE PROCEDURE proc_add(a int, b int, c OUT int)
AS 
BEGIN 
	c := a + b;
END;
C
void procedure(XGCIHANDLE hstmt)
{
    const char* sql = "proc_add(?, ?, ?)";

    int a = 1;
    int b = 2;
    int sum;
    int rc;
    XGCIBindParamByPos(hstmt, 1, PARAM_IN, &a, 0, XG_C_INTEGER, NULL, NULL, SQL_INTEGER);
    XGCIBindParamByPos(hstmt, 2, PARAM_IN, &b, 0, XG_C_INTEGER, NULL, NULL, SQL_INTEGER);
    XGCIBindParamByPos(hstmt, 3, PARAM_OUT, &sum, 0, XG_C_INTEGER, &rc, NULL, SQL_INTEGER);

    int ret = XGCIExecDirect(hstmt, (char*)sql, XGCI_NTS);
    if (ret != XGCI_SUCCESS)
        printError(hstmt);
    else
        printf("%d\n", sum); //sum is 3
}

存储函数调用 ​

SQL
-- 创建函数
CREATE OR REPLACE FUNCTION func_add(a int, b int)
RETURN int
AS 
BEGIN 
	RETURN a + b;
END;
C
void function(XGCIHANDLE hstmt)
{
    const char* sql = "SELECT func_add(?, ?)";

    int a = 1;
    int b = 2;
    int sum;
    XGCIBindParamByPos(hstmt, 1, PARAM_IN, &a, 0, XG_C_INTEGER, NULL, NULL, SQL_INTEGER);
    XGCIBindParamByPos(hstmt, 2, PARAM_IN, &b, 0, XG_C_INTEGER, NULL, NULL, SQL_INTEGER);

    int ret = XGCIExecDirect(hstmt, (char*)sql, XGCI_NTS);
    if (ret != XGCI_SUCCESS) {
        printError(hstmt);
        return;
    }

    XGCIFetch(hstmt);
    XGCIGetData(hstmt, 1, XG_C_INTEGER, &sum, 0, NULL);
    printf("%d\n", sum); //sum is 3
}

LOB 插入 ​

CLOB 与 BLOB 插入方法相同，如下代码仅展示 BLOB 的插入。

C
void insertLob(XGCIHANDLE hstmt)
{
    size_t size;
    void* buf;
    const char* file = "a.png";
    //从文件读取数据
    FILE* fp = fopen(file, "rb");
    fseek(fp, 0, SEEK_END);
    size = ftell(fp);
    fseek(fp, 0, SEEK_SET);
    buf = malloc(size);
    fread(buf, size, 1, fp);
    fclose(fp);

    //将数据写入 lob
    LobLocator lob = XGCILobCreate();
    XGCILobPutData(lob, size, buf);

    //将 lob 插入数据库
    const char* sql = "INSERT INTO tab_test(lob) VALUES(?)";
    XGCIPrepare(hstmt, (char*)sql, XGCI_NTS);
    XGCIBindParamByPos(hstmt, 1, PARAM_IN, lob, 0, XG_C_LOB, NULL, NULL, SQL_BLOB);
    int ret = XGCIExecute(hstmt);
    if (ret != XGCI_SUCCESS)
        printError(hstmt);
    XGCILobRelease(lob);
}

LOB 查询 ​

C
void queryLob(XGCIHANDLE hstmt)
{
    const char* sql = "SELECT lob FROM tab_test WHERE id = 1";
    XGCIExecDirect(hstmt, (char*)sql, XGCI_NTS);

    LobLocator lob = XGCILobCreate();
    XGCIDefineByPos(hstmt, 1, lob, 0, XG_C_LOB, NULL, NULL);
    XGCIFetch(hstmt);

    const char* file = "a_out.png";
    FILE* fp = fopen(file, "wb");

    int offset = 0;
    char buf[1024];
    int len;
    int ret;
    while ((ret = XGCILobGetData(lob, offset, 1024, buf, &len)) == XGCI_SUCCESS && len > 0) {
        fwrite(buf, len, 1, fp);
        offset += len;
    }

    fclose(fp);
    XGCILobRelease(lob);
}