数字类型

数字类型有整数类型、任意精度数字类型、浮点类型和序数类型。下面用整数类型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);

VastbaseG100

数字类型

弹框头部