logo - 跳到首页

VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

处理数据类型

二进制类型

Vastbase提供两种不同的方法存储二进制数据。二进制数据可以使用二进制数据类型 BYTEA存储在表中，或者使用大对象特性以一种特殊的格式将二进制数据存储在一个独立的表中，然后通过在表中保存一个类型为 OID 的值来引用该表。

BYTEA

使用BYTEA类型存储二进制数据，例如：

CREATE TABLE lo_test_tab (id int, img bytea);
插入记录
/*二进制数据*/
char param1[20] = { 1, 2, 3, 4, 5, 6, 7, 8 };
SQLLEN cbParam1;
/*分配语句句柄*/
rc = SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, &V_OD_hstmt);
if (!SQL_SUCCEEDED(rc))
{
	printf("failed to allocate stmt handle");
	exit(1);
}
/*预编译SQL*/
rc = SQLPrepare(V_OD_hstmt, (SQLCHAR *) "INSERT INTO lo_test_tab VALUES (1, ?)", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLPrepare failed", V_OD_hstmt);
/*绑定参数*/
cbParam1 = 8;
rc = SQLBindParameter(V_OD_hstmt, 1, SQL_PARAM_INPUT,
					SQL_C_BINARY,	/* value type */
					SQL_LONGVARBINARY,	/* param type */
					200,			/* column size */
					0,			/* dec digits */
					param1,		/* param value ptr */
					0,			/* buffer len */
					&cbParam1		/* StrLen_or_IndPtr */);
CHECK_STMT_RESULT(rc, "SQLBindParameter failed", V_OD_hstmt);

/* 执行*/
rc = SQLExecute(V_OD_hstmt);
CHECK_STMT_RESULT(rc, "SQLExecute failed", V_OD_hstmt);

在这里，SQLBindParameter时指定参数类型为SQL_LONGVARBINARY, 再将参数值的指针(char *类型)传入函数，即可将二进制数据写入。

读取记录
char buf[100];
SQLLEN ind;
/*分配句柄*/
rc = SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, &V_OD_hstmt);
/*执行查询*/
SQLExecDirect(V_OD_hstmt, (SQLCHAR *) "SELECT id, img FROM lo_test_tab WHERE id = 1", SQL_NTS);
/*获取结果*/
rc = SQLFetch(V_OD_hstmt);
CHECK_STMT_RESULT(rc, "SQLFetch failed", V_OD_hstmt);
/*读取数据*/
rc = SQLGetData(V_OD_hstmt, 2, SQL_C_BINARY, buf, sizeof(buf), &ind);
CHECK_STMT_RESULT(rc, "SQLGetData failed", V_OD_hstmt);

大对象

使用大对象存储二进制数据，例如：

create table image(name text, img oid);
插入记录
/* 分配语句句柄 */
SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, &V_OD_hstmt);        
/* 使用服务端函数lo_import创建一个大对象，将其返回的大对象OID存入image.img字段中； /tmp/bfile.from 是预先准备好的服务端文件；执行用户需要具有sysadmin权限。*/
SQLExecDirect(V_OD_hstmt,(SQLCHAR *)"insert into image values('test image', lo_import('/tmp/bfile.from'))",SQL_NTS);

注意：这里使用的lo_import是服务端函数，不是客户端的libpq接口。

读取记录
/* 将大对象导出到服务端文件/tmp/bfile.to中。*/
SQLExecDirect(V_OD_hstmt,(SQLCHAR *)"select lo_export(image.img, '/tmp/bfile.to') from image where name = 'test image'",SQL_NTS);

注意：这里使用的lo_export是服务端函数，不是客户端的libpq接口。

字符类型

Vastbase中TEXT类型与VARCHAR类型都是可变长的字符类型，区别在于VARCHAR类型通过VARCHAR(n)中的n来限制最大长度，而TEXT类型没有。TEXT类型与VARCHAR类型几乎没有性能差别，TEXT类型最多可存储1G数据。

使用TEXT类型存储数据时，可用getString()，getCharacterStream(),setString(), setCharacterStream()来进行读写。

例如：

CREATE TABLE images (id int, msg text);
插入记录
str=”text value for test”;
SQLLEN cbParam1 = SQL_NTS;
/*分配语句句柄*/
rc = SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, &V_OD_hstmt);
if (!SQL_SUCCEEDED(rc))
{
	printf("failed to allocate stmt handle");
	exit(1);
}
/*预编译SQL*/
rc = SQLPrepare(V_OD_hstmt, (SQLCHAR *) "INSERT INTO images  VALUES (1, ?)", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLPrepare failed", V_OD_hstmt);
/*绑定参数*/
cbParam1 = 8;
rc = SQLBindParameter(V_OD_hstmt, 1, SQL_PARAM_INPUT,
					SQL_C_CHAR,	/* value type */
					SQL_CHAR,	/* param type */
					200,			/* column size */
					0,			/* dec digits */
					str,		/* param value ptr */
					0,			/* buffer len */
					&cbParam1		/* StrLen_or_IndPtr */);
CHECK_STMT_RESULT(rc, "SQLBindParameter failed", V_OD_hstmt);

/* 执行*/
rc = SQLExecute(V_OD_hstmt);
CHECK_STMT_RESULT(rc, "SQLExecute failed", V_OD_hstmt);
读取记录
char buf[100];
SQLLEN ind;
/*分配句柄*/
rc = SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, &V_OD_hstmt);
/*执行查询*/
SQLExecDirect(V_OD_hstmt, (SQLCHAR *) "SELECT id, img FROM images  WHERE id = 1", SQL_NTS);
/*获取结果*/
rc = SQLFetch(V_OD_hstmt);
CHECK_STMT_RESULT(rc, "SQLFetch failed", V_OD_hstmt);
/*读取数据*/
rc = SQLGetData(V_OD_hstmt, 2, SQL_C_CHAR, buf, sizeof(buf), &ind);
CHECK_STMT_RESULT(rc, "SQLGetData failed", V_OD_hstmt);

数字类型

数字类型有整数类型、任意精度数字类型、浮点类型和序数类型。下面用整数类型integer、任意精度数字类型numeric、浮点类型real和double precision举例说明。

插入数据

  • 整数类型integer的插入

    int		rc;
char		buf[200];
SQLINTEGER	intparam;
SQLLEN	cbParam1;

SQLExecDirect(hstmt,(SQLCHAR *)"drop table IF EXISTS test_integer;",SQL_NTS);
SQLExecDirect(hstmt,(SQLCHAR *)"create table test_integer(intg integer);",SQL_NTS);

rc = SQLPrepare(hstmt, (SQLCHAR *) "insert into test_integer values (?);", SQL_NTS);

/* bind param  */
intparam = 3;
cbParam1 = sizeof(intparam);
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
                        SQL_C_SLONG,	/* value type */
                        SQL_INTEGER,	/* param type */
                        0,			/* column size (ignored for SQL_INTEGER) */
                        0,			/* dec digits */
                        &intparam,	/* param value ptr */
                        sizeof(intparam),
                        &cbParam1);

/* Execute */
rc = SQLExecute(hstmt);
if (!SQL_SUCCEEDED(rc))
{
    printf("[failed] test_insert_select: insert into test_integer values (?)");
    exit(1);
}

  • 任意精度数字类型numeric的插入

    SQL_NUMERIC_STRUCT numericparam, numericparamT;
SQLLEN		cbParam_nmr_1;
char		sql[100];

SQLExecDirect(hstmt,(SQLCHAR *)"drop table IF EXISTS test_numeric;",SQL_NTS);
SQLExecDirect(hstmt,(SQLCHAR *)"create table test_numeric(nmr numeric);",SQL_NTS);

rc = SQLPrepare(hstmt, (SQLCHAR *) "insert into test_numeric values (?);", SQL_NTS);

/* 25.212 */
build_numeric_struct(&numericparam, 1, (char *)"7C62", 5, 3);

cbParam_nmr_1 = sizeof(numericparam);
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
                        SQL_C_NUMERIC,	/* value type */ //指时 numericparam 的类型   -- 表示将C语言中的SQL_C_NUMERIC类型变量（在C语言实际是SQL_NUMERIC_STRUCT numericparam）绑定到 SQL_NUMERIC类型的参数中
                        SQL_NUMERIC,	/* param type */ //代绑定的参数类型类型 select ? form ...; 表示代绑定的 ？对应的参数类型
                        0,			/* column size (ignored for SQL_INTERVAL_SECOND) */
                        0,			/* dec digits */
                        &numericparam, /* param value ptr */
                        sizeof(numericparam),
                        &cbParam_nmr_1);

/* Execute */
rc = SQLExecute(hstmt);
if (!SQL_SUCCEEDED(rc))
{
    /* 失败 */
}

上述代码片段中的build_numeric_struct函数实现如下：

static void
build_numeric_struct(SQL_NUMERIC_STRUCT *numericparam,
					 unsigned char sign, char *hexstr,
					 unsigned char precision, unsigned char scale)
{
	int			len;

	/* parse the hex-encoded value */
	memset(numericparam, 0, sizeof(SQL_NUMERIC_STRUCT));

	numericparam->sign = sign;
	numericparam->precision = precision;
	numericparam->scale = scale;

	len = 0;
	while (*hexstr)
	{
		if (*hexstr == ' ')
		{
			hexstr++;
			continue;
		}
		if (len >= SQL_MAX_NUMERIC_LEN)
		{
			fprintf(stderr, "hex-encoded numeric value too long\n");
			exit(1);
		}
		numericparam->val[len] =
			hex_to_int(*hexstr) << 4 | hex_to_int(*(hexstr + 1));
		hexstr += 2;
		len++;
	}
}

  • 浮点类型real和double precision的插入

    SQLDOUBLE	dbParam, dbParamT;
SQLREAL	ftParam, ftParamT;
SQLLEN	cbParam_db_1;
SQLLEN	cbParam_ft_2;

SQLExecDirect(hstmt,(SQLCHAR *)"drop table IF EXISTS test_float;",SQL_NTS);
SQLExecDirect(hstmt,(SQLCHAR *)"create table test_float(dbpre double precision, rl real);",SQL_NTS);

rc = SQLPrepare(hstmt, (SQLCHAR *) "insert into test_float values (?, ?);", SQL_NTS);

/* bind param  */
dbParam = 3.1234;
cbParam_db_1 = sizeof(dbParam);
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
                        SQL_C_DOUBLE,	/* value type */
                        SQL_DOUBLE,	/* param type */
                        0,			/* column size (ignored for SQL_INTEGER) */
                        0,			/* dec digits */
                        &dbParam,		/* param value ptr */
                        sizeof(dbParam),
                        &cbParam_db_1);

ftParam = 4.1234;
cbParam_ft_2 = sizeof(ftParam);
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,
                        SQL_C_FLOAT,	/* value type */
                        SQL_FLOAT,	/* param type */
                        0,			/* column size (ignored for SQL_INTEGER) */
                        0,			/* dec digits */
                        &ftParam,		/* param value ptr */
                        sizeof(ftParam),
                        &cbParam_ft_2);

/* Execute */
rc = SQLExecute(hstmt);
if (!SQL_SUCCEEDED(rc))
{
    printf("[failed] test_insert_select: insert into test_float values (?, ?) \n");
    exit(1);
}
读取数据

  • 整数类型integer的读取

    rc = SQLPrepare(hstmt, (SQLCHAR *) "select intg from test_integer;", SQL_NTS);
/* Execute */
rc = SQLExecute(hstmt);
if (!SQL_SUCCEEDED(rc))
{
    printf("[failed] test_insert_select: select intg from test_integer;");
    exit(1);
}
else
{
    /* print result */
    rc = SQLFetch(hstmt);

    rc = SQLGetData(hstmt, 1, SQL_C_CHAR, buf, sizeof(buf), NULL);

    printf("test_integer.intg:\n    %s\n", buf);
}
printf ("test_insert_select: test_integer end \n\n ");

rc = SQLFreeStmt(hstmt, SQL_CLOSE);

  • 任意精度数字numeric的读取

    rc = SQLExecDirect(hstmt, (SQLCHAR *) "select * from test_numeric", SQL_NTS);

rc = SQLFetch(hstmt);

// 下面是以十六进制显示。
// rc = SQLGetData(hstmt, 1, SQL_C_NUMERIC, &numericparamT, sizeof(numericparamT), NULL);
// printf("[hexadecimal] test_numeric.nmr:\n     sign %u prec %u scale %d val %02X%02X%02X%02X%02X%02X%02X%02X%02X%02X%02X%02X%02X%02X%02X%02X\n",
// 	   numericparam.sign, numericparam.precision, numericparam.scale,
// 	   numericparam.val[0], numericparam.val[1],
// 	   numericparam.val[2], numericparam.val[3],
// 	   numericparam.val[4], numericparam.val[5],
// 	   numericparam.val[6], numericparam.val[7],
// 	   numericparam.val[8], numericparam.val[9],
// 	   numericparam.val[10], numericparam.val[11],
// 	   numericparam.val[12], numericparam.val[13],
// 	   numericparam.val[14], numericparam.val[15]);

rc = SQLGetData(hstmt, 1, SQL_C_CHAR, buf, sizeof(buf), NULL);
printf("[character string] test_numeric.nmr:\n   %s\n", buf);

rc = SQLFreeStmt(hstmt, SQL_CLOSE);

  • 浮点类型real和double precision的读取

    rc = SQLPrepare(hstmt, (SQLCHAR *) "select * from test_float;", SQL_NTS);
/* Execute */
rc = SQLExecute(hstmt);
if (!SQL_SUCCEEDED(rc))
{
    printf("[failed] test_insert_select: select * from test_float;\n");
    exit(1);
}
else
{
    /* print result */
    rc = SQLFetch(hstmt);

    SQLGetData(hstmt, 1, SQL_C_DOUBLE, &dbParamT, sizeof(dbParamT), NULL);
    printf("test_float.dbpre:\n    %lf\n", dbParamT);

    SQLGetData(hstmt, 2, SQL_C_FLOAT, &ftParamT, sizeof(ftParamT), NULL);
    printf("test_float.dbpre:\n    %f\n", ftParamT);
}

rc = SQLFreeStmt(hstmt, SQL_CLOSE);

时间/日期类型

日期/时间类型有timestamp 、date、time 、interval ；下面以timestamp 、date、time 举例说明。

插入数据
   SQLHENV         hEnv    = SQL_NULL_HENV;
    SQLHDBC         hDbc    = SQL_NULL_HDBC;
    SQLHSTMT        hStmt   = SQL_NULL_HSTMT;
    SQLRETURN       rc      = SQL_SUCCESS;
    SQLINTEGER      RETCODE = 0;

    TIMESTAMP_STRUCT sTime, sTimeT ;
    DATE_STRUCT dTime, dTimeT ;
    TIME_STRUCT tTime, tTimeT ;
    SQLLEN cbID = 0;
    SQLCHAR result[200]={0};

    rc = SQLExecDirect(hStmt,(SQLCHAR*)"drop table IF EXISTS test_timestamp_date_time",SQL_NTS);
    if (!SQL_SUCCEEDED(rc))
        goto exit;
        
    rc = SQLExecDirect(hStmt,(SQLCHAR*)"create table test_timestamp_date_time(tmsp timestamp, dt date, tm time)",SQL_NTS);
    if (!SQL_SUCCEEDED(rc))
        goto exit;

    rc = SQLExecDirect(hStmt,(SQLCHAR*)"insert into test_timestamp_date_time values('2012-12-14 12:12','2013-11-15', '04:12:03')",SQL_NTS);
    if (!SQL_SUCCEEDED(rc))
        goto exit;

    SQLPrepare(hStmt,(SQLCHAR*)"insert into test_timestamp_date_time values(?, ?, ?)",SQL_NTS);

    sTime.year = 1992;
    sTime.month = 12;
    sTime.day = 12;
    sTime.hour = 9;
    sTime.minute = 10;
    sTime.second = 31;
    SQLBindParameter(hStmt,1,SQL_PARAM_INPUT,SQL_C_TYPE_TIMESTAMP,SQL_TYPE_TIMESTAMP,100,0,(SQLPOINTER)&sTime,sizeof(TIMESTAMP_STRUCT),&cbID);

    dTime.year = 1982;
    dTime.month = 12;
    dTime.day = 11;
    SQLBindParameter(hStmt,2,SQL_PARAM_INPUT,SQL_C_TYPE_DATE ,SQL_TYPE_DATE ,100,0,(SQLPOINTER)&dTime,sizeof(DATE_STRUCT),&cbID);

    tTime.hour = 10;
    tTime.minute = 10;
    tTime.second = 22;
    SQLBindParameter(hStmt,3,SQL_PARAM_INPUT,SQL_C_TYPE_TIME ,SQL_TYPE_TIME ,100,0,(SQLPOINTER)&tTime,sizeof(TIME_STRUCT),&cbID);

    rc = SQLExecute(hStmt);
    if ((!SQL_SUCCEEDED(rc)))
            goto exit;
读取数据
   rc = SQLExecDirect(hStmt,(SQLCHAR*)"select * from test_timestamp_date_time;",SQL_NTS);
    if (!SQL_SUCCEEDED(rc))
        goto exit;

    rc = SQLFetch(hStmt);
    while(rc != SQL_NO_DATA)
    {
        /* test SQLGetData */
        SQLGetData(hStmt,1,SQL_C_TYPE_TIMESTAMP,(SQLPOINTER)&sTimeT,sizeof(sTimeT),&cbID);
        sprintf((char*)result, "%.4d-%.2d-%.2d %.2d:%.2d:%.2d", sTimeT.year, sTimeT.month, sTimeT.day,sTimeT.hour,sTimeT.minute,sTimeT.second);
        printf("TIMESTAMP = %s\n\n",result);

        SQLGetData(hStmt,2,SQL_C_TYPE_DATE ,(SQLPOINTER)&dTimeT,sizeof(dTimeT),&cbID);
        sprintf((char*)result, "%.4d-%.2d-%.2d", dTimeT.year, dTimeT.month, dTimeT.day);
        printf("DATE = %s\n\n",result);

        SQLGetData(hStmt,3,SQL_C_TYPE_TIME ,(SQLPOINTER)&tTimeT,sizeof(tTimeT),&cbID);
        sprintf((char*)result, "%.2d:%.2d:%.2d", tTimeT.hour,tTimeT.minute,tTimeT.second);
        printf("TIME = %s\n\n",result);

        rc = SQLFetch(hStmt);  
    };

    if (!SQL_SUCCEEDED(rc) && rc != SQL_NO_DATA)
        goto exit;