更新日期：2024年09月11日
函数说明
将参数datetime 转换为字符串，并进行格式化输出。
参数说明
表5- 12 参数说明
格式化参数
含
义
, . ; :
除了左面标准的几个，还允许用文字作为分隔符号。例如年月日日
期分隔符。用于格式化输出日期。
AD
即拉丁文Anno Domini 的简写，
表示公元，
会根据nls 的不同转换为
公元或者AD 等。
如果是公元后的日期，显示AD。
如果是公元前的日期，显示BC。
AM
上午的简写，中文环境输出为上午。
如果是上午，返回AM。
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
712
格式化参数
含
义
如果是下午，返回PM。
BC
即拉丁文Before Christ 的简写，表示公元前，会根据nls 的不同转换
为公元或者BC 等。
如果是公元后的日期，显示AD。
如果是公元前的日期，显示BC。
CC
返回世纪，以阿拉伯数字表示。
D
一周之中的第几天，返回的是序号（1～7）。
DAY
返回日期中的DAY 部分。返回的是英文全拼形式，首字母大写。
DD
同DAY，但是返回的是数字形式（01～31）。
DDD
日期中的日是一年当中的第几天，返回的是序号001～366。
DY
同DAY,但是返回的是英文形式，返回前三个字母。首字母大写。
FF[n]
就是毫秒,如果不加数字就是用默认的精度，
默认6 位精度。
1 ≤n ≤9。
只能用于timestamp 类型的。
FM
删除日期开头和结尾处的空格。
示例：FM Month
FX
固定模式全局选项。
示例：FX MONTH DD DAY
HH[12 | 24]
表示小时，默认12 小时制。
HH12，12 小时制。返回（01～12）。
HH24，24 小时制。返回（00～23）。
IW
ISO 标准的一年中的第几周（1～52,或者1～53）。
MI
返回分钟数（00～59）。
MM
返回月份，返回阿拉伯数字。
MON
返回月份，返回的是英文简写，三个英文字母，首字母大写。
MONTH
返回月份，返回的是英文全拼。首字母大写。
PM
下午的简写,中文环境输出为下午。
Q
返回季度，取值为1～4。
RM
用罗马数字表示的月份。罗马数字全部大写。
RR 或RRRR
返回2 位或者4 位年。
YY 或YYYY
返回2 位或者4 位年。
SCC
返回数字形式表示的世纪。
SS
返回秒（0～59）。
SSSSS
一天从午夜开始的累积秒数（0～86399）。
TS
返回带有AM 或者PM 的时分秒形式的时间。
W
一个月中的第几周，其算法局限在datetime 参数所属于的月份之内
而已。
WW
同IW。
示例
示例1：将NOW()转换为FORMAT 中对应的日期格式。
gbase> SELECT TO_CHAR(NOW(),'YYYY/MM/DD') FROM dual;
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
713
+-----------------------------+
| TO_CHAR(NOW(),'YYYY/MM/DD') |
+-----------------------------+
| 2020/04/01
|
+-----------------------------+
1 row in set
gbase> SELECT TO_CHAR(NOW(),'YYYY-MM-DD') FROM dual;
+-----------------------------+
| TO_CHAR(NOW(),'YYYY-MM-DD') |
+-----------------------------+
| 2020-04-01
|
+-----------------------------+
1 row in set
gbase> SELECT TO_CHAR(NOW(),'YYYY,MM,DD') FROM dual;
+-----------------------------+
| TO_CHAR(NOW(),'YYYY,MM,DD') |
+-----------------------------+
| 2020,04,01
|
+-----------------------------+
1 row in set
gbase> SELECT TO_CHAR(CURDATE(),'YYYY;MM;DD') FROM dual;
+---------------------------------+
| TO_CHAR(CURDATE(),'YYYY;MM;DD') |
+---------------------------------+
| 2020;04;01
|
+---------------------------------+
1 row in set
gbase> SELECT TO_CHAR(NOW(),'YYYY"年"MM"月"DD"日"') FROM
dual;
+---------------------------------------+
| TO_CHAR(NOW(),'YYYY"年"MM"月"DD"日"') |
+---------------------------------------+
| 2020 年04 月01 日
|
+---------------------------------------+
1 row in set
示例2：将CURDATE()转换为FORMAT 中对应的日期格式。
gbase> SELECT TO_CHAR(CURDATE(),'AD
YYYY-MM-DD') FROM
dual;
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
714
+-------------------------------------+
| TO_CHAR(CURDATE(),'AD
YYYY-MM-DD') |
+-------------------------------------+
| AD
2020-04-01
|
+-------------------------------------+
1 row in set
示例3：比较NOW()和将NOW()转换为“AM HH12:MI:SS” 后的格式。
gbase> SELECT NOW(),TO_CHAR(NOW(),'AM HH12:MI:SS') FROM
dual;
+---------------------+--------------------------------+
| NOW()
| TO_CHAR(NOW(),'AM HH12:MI:SS') |
+---------------------+--------------------------------+
| 2020-04-01 16:35:43 | PM 04:35:43
|
+---------------------+--------------------------------+
1 row in set
gbase> SELECT
NOW(),TO_CHAR(TIMESTAMPADD(HOUR,8,NOW()),'AM
HH12:MI:SS') AS f_FormatShow FROM dual;
+---------------------+--------------+
| NOW()
| f_FormatShow |
+---------------------+--------------+
| 2020-04-01 16:36:03 | AM 12:36:03
|
+---------------------+--------------+
1 row in set
示例4：返回CURDATE()的世纪数。
gbase> SELECT TO_CHAR(CURDATE(),'CC') FROM dual;
+-------------------------+
| TO_CHAR(CURDATE(),'CC') |
+-------------------------+
| 21
|
+-------------------------+
1 row in set
示例5：系统默认周日为每周第一天，“2020-04-01”是周三。
FORMAT 的值为“D”，返回值为6。
gbase> SELECT CURDATE(),TO_CHAR(CURDATE(),'D') FROM dual;
+------------+------------------------+
| CURDATE()
| TO_CHAR(CURDATE(),'D') |
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
715
+------------+------------------------+
| 2020-04-01 | 4
|
+------------+------------------------+
1 row in set
FORMAT 的值为“DAY”，返回周三的英文全拼形式，首字母大写。
gbase> SELECT CURDATE(),TO_CHAR(CURDATE(),'DAY') FROM dual;
+--------------------+-----------------------------------+
| date('2020-04-01') | TO_CHAR(date('2020-04-01'),'DAY') |
+--------------------+-----------------------------------+
| 2020-04-01
| Wednesday
|
+--------------------+-----------------------------------+
1 row in set
FORMAT 的值为“DD”，返回值为11。
gbase> SELECT CURDATE(),TO_CHAR(CURDATE(),'DD') FROM dual;
+------------+-------------------------+
| CURDATE()
| TO_CHAR(CURDATE(),'DD') |
+------------+-------------------------+
| 2020-04-01 | 01
|
+------------+-------------------------+
1 row in set
FORMAT 的值为“DDD”，返回“2020-04-01”是2020 年的第几天。
gbase> SELECT CURDATE(),TO_CHAR(CURDATE(),'DDD') FROM dual;
+------------+--------------------------+
| CURDATE()
| TO_CHAR(CURDATE(),'DDD') |
+------------+--------------------------+
| 2020-04-01 | 092
|
+------------+--------------------------+
1 row in set
FORMAT 的值为“DY”，返回周三的英文形式的前三个字母，首字母大写。
gbase> SELECT CURDATE(),TO_CHAR(CURDATE(),'DY') FROM dual;
+------------+-------------------------+
| CURDATE()
| TO_CHAR(CURDATE(),'DY') |
+------------+-------------------------+
| 2020-04-01 | Wed
|
+------------+-------------------------+
1 row in set
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
716
示例6：查询当前时间的毫秒，默认为6 位。
gbase> SELECT
CURRENT_TIMESTAMP(),TO_CHAR(CURRENT_TIMESTAMP(),'FF')
FROM dual;
+---------------------+-----------------------------------+
| CURRENT_TIMESTAMP() | TO_CHAR(CURRENT_TIMESTAMP(),'FF') |
+---------------------+-----------------------------------+
| 2020-04-01 16:37:51 | 000000
|
+---------------------+-----------------------------------+
1 row in set
gbase> SELECT
CURRENT_TIMESTAMP(),TO_CHAR(CURRENT_TIMESTAMP(),'FF9')
FROM dual;
+---------------------+------------------------------------+
| CURRENT_TIMESTAMP() | TO_CHAR(CURRENT_TIMESTAMP(),'FF9') |
+---------------------+------------------------------------+
| 2020-04-01 16:38:15 | 000000000
|
+---------------------+------------------------------------+
1 row in set
示例7：FORMAT 为“FX”或“FM”。
gbase> SELECT
CURRENT_TIMESTAMP(),TO_CHAR(CURRENT_TIMESTAMP(),'FX
YYYY-MM-DD') FROM dual;
+---------------------+----------------------------------------------+
| CURRENT_TIMESTAMP() | TO_CHAR(CURRENT_TIMESTAMP(),'FX
YYYY-MM-DD') |
+---------------------+----------------------------------------------+
| 2020-04-01 16:38:39 |
2020-04-01
|
+---------------------+----------------------------------------------+
1 row in set
gbase> SELECT
CURRENT_TIMESTAMP(),TO_CHAR(CURRENT_TIMESTAMP(),'FM
YYYY-MM-DD') FROM dual;
+---------------------+----------------------------------------------+
| CURRENT_TIMESTAMP() | TO_CHAR(CURRENT_TIMESTAMP(),'FM
YYYY-MM-DD') |
+---------------------+----------------------------------------------+
| 2020-04-01 16:39:12 |
2020-04-01
|
+---------------------+----------------------------------------------+
1 row in set
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
717
示例8：FORMAT 为“HH”，返回小时。
gbase> SELECT
CURRENT_TIMESTAMP(),TO_CHAR(CURRENT_TIMESTAMP(),'HH')
FROM dual;
+---------------------+-----------------------------------+
| CURRENT_TIMESTAMP() | TO_CHAR(CURRENT_TIMESTAMP(),'HH') |
+---------------------+-----------------------------------+
| 2021-06-03 15:00:58 | 03
|
+---------------------+-----------------------------------+
1 row in set (Elapsed: 00:00:00.01)
FORMAT 为“HH12”，返回12 小时制的小时。
gbase> SELECT NOW(),TO_CHAR(NOW(),'HH12') FROM dual;
+---------------------+-----------------------+
| NOW()
| TO_CHAR(NOW(),'HH12') |
+---------------------+-----------------------+
| 2021-06-03 15:02:00 | 03
|
+---------------------+-----------------------+
1 row in set (Elapsed: 00:00:00.02)
FORMAT 为“HH24”，返回24 小时制的小时。
gbase> SELECT
NOW(),TO_CHAR(TIMESTAMPADD(HOUR,8,NOW()),'HH24') FROM
dual;
+---------------------+--------------------------------------------+
| NOW()
|
TO_CHAR(TIMESTAMPADD(HOUR,8,NOW()),'HH24') |
+---------------------+--------------------------------------------+
| 2021-06-03 15:03:43 | 23
|
+---------------------+--------------------------------------------+
1 row in set
示例9：FORMAT 为“IW”，返回一年中的第几周。
gbase> SELECT TO_CHAR(NOW(),'IW') FROM dual;
+---------------------+
| TO_CHAR(NOW(),'IW') |
+---------------------+
| 14
|
+---------------------+
1 row in set
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
718
示例10：FORMAT 为“MI”，返回分钟数。
gbase> SELECT NOW(),TO_CHAR(NOW(),'MI') FROM dual;
+---------------------+---------------------+
| NOW()
| TO_CHAR(NOW(),'MI') |
+---------------------+---------------------+
| 2020-04-01 17:00:33 | 00
|
+---------------------+---------------------+
1 row in set
示例11：FORMAT 为“MM”，
“MON”，
“MONTH”，以不同形式返回月份。
gbase> SELECT NOW(),TO_CHAR(NOW(),'MM') FROM dual;
+---------------------+---------------------+
| NOW()
| TO_CHAR(NOW(),'MM') |
+---------------------+---------------------+
| 2020-04-01 17:01:09 | 04
|
+---------------------+---------------------+
1 row in set
gbase> SELECT NOW(),TO_CHAR(NOW(),'MON') FROM dual;
+---------------------+----------------------+
| NOW()
| TO_CHAR(NOW(),'MON') |
+---------------------+----------------------+
| 2020-04-01 17:01:31 | Apr
|
+---------------------+----------------------+
1 row in set
gbase> SELECT NOW(),TO_CHAR(NOW(),'MONTH') FROM dual;
+---------------------+------------------------+
| NOW()
| TO_CHAR(NOW(),'MONTH') |
+---------------------+------------------------+
| 2020-04-01 09:44:50 | April
|
+---------------------+------------------------+
1 row in set
示例12：FORMAT 为“PM HH12:MI:SS”。
gbase> SELECT NOW(),TO_CHAR(NOW(),'PM HH12:MI:SS') FROM
dual;
+---------------------+--------------------------------+
| NOW()
| TO_CHAR(NOW(),'PM HH12:MI:SS') |
+---------------------+--------------------------------+
| 2020-04-01 17:06:21 | PM 05:06:21
|
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
719
+---------------------+--------------------------------+
1 row in set
gbase> SELECT
NOW(),TO_CHAR(TIMESTAMPADD(HOUR,8,NOW()),'PM HH12:MI:SS')
AS f_FormatShow FROM dual;
+---------------------+--------------+
| NOW()
| f_FormatShow |
+---------------------+--------------+
| 2020-04-01 10:40:45 | PM 06:40:45
|
+---------------------+--------------+
1 row in set
示例13：FORMAT 为“Q YYYY-MM-DD”，返回NOW() 中的日期是第几季度。
gbase> SELECT NOW(),TO_CHAR(NOW(),'Q YYYY-MM-DD') FROM
dual;
+---------------------+-------------------------------+
| NOW()
| TO_CHAR(NOW(),'Q YYYY-MM-DD') |
+---------------------+-------------------------------+
| 2020-04-01 17:07:00 | 2 2020-04-01
|
+---------------------+-------------------------------+
1 row in set
示例14：FORMAT 为“RM”，返回用罗马数字表示的月份。
gbase> SELECT NOW(),TO_CHAR(NOW(),'RM') FROM dual;
+---------------------+---------------------+
| NOW()
| TO_CHAR(NOW(),'RM') |
+---------------------+---------------------+
| 2020-04-01 16:46:52 | IV
|
+---------------------+---------------------+
1 row in set
示例15：FORMAT 为“RR”、“RRRR”，返回2 位或4 位的年。
gbase> SELECT TO_CHAR(NOW(),'RR') FROM dual;
+---------------------+
| TO_CHAR(NOW(),'RR') |
+---------------------+
| 20
|
+---------------------+
1 row in set
gbase> SELECT TO_CHAR(NOW(),'RRRR') FROM dual;
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
720
+-----------------------+
| TO_CHAR(NOW(),'RRRR') |
+-----------------------+
| 2020
|
+-----------------------+
1 row in set
gbase> SELECT
TO_CHAR(TIMESTAMPADD(YEAR,-1200,NOW()),'RRRR') FROM dual;
+------------------------------------------------+
| TO_CHAR(TIMESTAMPADD(YEAR,-1200,NOW()),'RRRR') |
+------------------------------------------------+
| 0820
|
+------------------------------------------------+
1 row in set
示例16：FORMAT 为“SCC”，返回日期所属的世纪数。
gbase> SELECT NOW(),TO_CHAR(NOW(),'SCC') FROM dual;
+---------------------+----------------------+
| NOW()
| TO_CHAR(NOW(),'SCC') |
+---------------------+----------------------+
| 2020-04-01 17:08:12 |
21
|
+---------------------+----------------------+
1 row in set
gbase> SELECT TIMESTAMPADD(YEAR,-20,NOW()) AS
f_DATETIME,TO_CHAR(TIMESTAMPADD(YEAR,-20,NOW()),'SCC') AS
f_AD FROM dual;
+---------------------+------+
| f_DATETIME
| f_AD |
+---------------------+------+
| 2000-04-01 17:08:26 |
20
|
+---------------------+------+
1 row in set
示例17：FORMAT 为“SSSSS”，返回一天从午夜开始的累积秒数。
gbase> SELECT
NOW(),TO_CHAR(NOW(),'SS'),TO_CHAR(NOW(),'SSSSS') FROM dual;
+---------------------+---------------------+------------------------+
| NOW()
| TO_CHAR(NOW(),'SS') |
TO_CHAR(NOW(),'SSSSS') |
+---------------------+---------------------+------------------------+
| 2020-04-01 17:08:40 | 40
| 61720
|
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
721
+---------------------+---------------------+------------------------+
1 row in set
示例18：FORMAT 为“TS”，返回带有AM 或者PM 的时分秒形式的时间。
gbase> SELECT NOW(),TO_CHAR(NOW(),'TS') FROM dual;
+---------------------+---------------------+
| NOW()
| TO_CHAR(NOW(),'TS') |
+---------------------+---------------------+
| 2020-04-01 17:11:38 | 05:11:38 PM
|
+---------------------+---------------------+
1 row in set
gbase> SELECT TIMESTAMPADD(HOUR,8,NOW()) AS
f_now,TO_CHAR(TIMESTAMPADD(HOUR,8,NOW()),'TS') AS f_now_ts
FROM dual;
+---------------------+-------------+
| f_now
| f_now_ts
|
+---------------------+-------------+
| 2020-04-02 01:11:48 | 01:11:48 AM |
+---------------------+-------------+
1 row in set
示例19：FORMAT 为“W”，返回日期所在月份的第几周。
gbase> SELECT NOW(),TO_CHAR(NOW(),'W') FROM dual;
+---------------------+--------------------+
| NOW()
| TO_CHAR(NOW(),'W') |
+---------------------+--------------------+
| 2020-04-01 17:12:14 | 1
|
+---------------------+--------------------+
1 row in set
代码示例 locreate.c 展示如何创建智能大对象。
在 UNIX™ 平台上的 %GBS_HOME%/demo/clidemo 中，以及在 Windows™ 环境中
的 %GBS_HOME%\demo\odbcdemo 目录中，可找到 locreate.c 文件。在同一位置，您还
可以找到关于如何构建 odbc_demo 数据库的指导。
/*
** locreate.c
**
** To create a smart large object
**
** OBDC Functions:
** SQLAllocHandle
** SQLBindParameter
** SQLConnect
** SQLFreeStmt
** SQLGetInfo
** SQLDisconnect
** SQLExecDirect
*/
#include
#include
GBase 8s ODBC Driver 程序员指南
南大通用数据技术股份有限公司
- 94 -
#include
#ifndef NO_WIN32
#include
#include
#include
#endif /*NO_WIN32*/
#include "infxcli.h"
#define BUFFER_LEN 12
#define ERRMSG_LEN 200
UCHAR defDsn[] = "odbc_demo";
int checkError (SQLRETURN rc,
SQLSMALLINT handleType,
SQLHANDLE handle,
char *errmsg)
{
SQLRETURN retcode = SQL_SUCCESS;
SQLSMALLINT errNum = 1;
SQLCHAR sqlState[6];
SQLINTEGER nativeError;
SQLCHAR errMsg[ERRMSG_LEN];
SQLSMALLINT textLengthPtr;
if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO))
{
while (retcode != SQL_NO_DATA)
{
retcode = SQLGetDiagRec (handleType, handle, errNum, sqlState,
&nativeError, errMsg, ERRMSG_LEN, &textLengthPtr);
if (retcode == SQL_INVALID_HANDLE)
{
fprintf (stderr, "checkError function was called with an
invalid handle!!\n");
return 1;
}
GBase 8s ODBC Driver 程序员指南
南大通用数据技术股份有限公司
- 95 -
if ((retcode == SQL_SUCCESS) || (retcode ==
SQL_SUCCESS_WITH_INFO))
fprintf (stderr, "ERROR: %d: %s : %s \n", nativeError,
sqlState, errMsg);
errNum++;
}
fprintf (stderr, "%s\n", errmsg);
return 1; /* all errors on this handle have been reported */
}
else
return 0; /* no errors to report */
}
int main (long argc,
char *argv[])
{
/* Declare variables
*/
/* Handles */
SQLHDBC hdbc;
SQLHENV henv;
SQLHSTMT hstmt;
/* Smart large object file descriptor */
long lofd;
long lofd_valsize = 0;
/* Smart large object pointer structure */
char* loptr_buffer;
short loptr_size;
long loptr_valsize = 0;
/* Smart large object specification structure */
char* lospec_buffer;
short lospec_size;
long lospec_valsize = 0;
/* Write buffer */
char* write_buffer;
short write_size;
GBase 8s ODBC Driver 程序员指南
南大通用数据技术股份有限公司
- 96 -
long write_valsize = 0;
/* Miscellaneous variables */
UCHAR dsn[20];/*name of the DSN used for connecting to the
database*/
SQLRETURN rc = 0;
int in;
FILE* hfile;
char* lo_file_name = "advert.txt";
char colname[BUFFER_LEN] = "item.advert";
long colname_size = SQL_NTS;
long mode = LO_RDWR;
long cbMode = 0;
char* insertStmt = "INSERT INTO item VALUES (1005, 'Helmet', 235,
'Each', ?, '39.95')";
/* STEP 1. Get data source name from command line (or use default).
** Allocate environment handle and set ODBC version.
** Allocate connection handle.
** Establish the database connection.
** Allocate the statement handle.
*/
/* If (dsn is not explicitly passed in as arg) */
if (argc != 2)
{
/* Use default dsn - odbc_demo */
fprintf (stdout, "\nUsing default DSN : %s\n", defDsn);
strcpy ((char *)dsn, (char *)defDsn);
}
else
{
/* Use specified dsn */
strcpy ((char *)dsn, (char *)argv[1]);
fprintf (stdout, "\nUsing specified DSN : %s\n", dsn);
}
/* Allocate the Environment handle */
GBase 8s ODBC Driver 程序员指南
南大通用数据技术股份有限公司
- 97 -
rc = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (rc != SQL_SUCCESS)
{
fprintf (stdout, "Environment Handle Allocation failed\nExiting!!");
return (1);
}
/* Set the ODBC version to 3.5 */
rc = SQLSetEnvAttr (henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3, 0);
if (checkError (rc, SQL_HANDLE_ENV, henv, "Error in Step 1 --
SQLSetEnvAttr failed\nExiting!!"))
return (1);
/* Allocate the connection handle */
rc = SQLAllocHandle (SQL_HANDLE_DBC, henv, &hdbc);
if (checkError (rc, SQL_HANDLE_ENV, henv, "Error in Step 1 -- Connection
Handle Allocation failed\nExiting!!"))
return (1);
/* Establish the database connection */
rc = SQLConnect (hdbc, dsn, SQL_NTS, "", SQL_NTS, "", SQL_NTS);
if (checkError (rc, SQL_HANDLE_DBC, hdbc, "Error in Step 1 -- SQLConnect
failed\n"))
return (1);
/* Allocate the statement handle */
rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt);
if (checkError (rc, SQL_HANDLE_DBC, hdbc, "Error in Step 1 -- Statement
Handle Allocation failed\nExiting!!"))
return (1);
fprintf (stdout, "STEP 1 done...connected to database\n");
/* STEP 2. Get the size of the smart large object specification
** structure.
** Allocate a buffer to hold the structure.
** Create a default smart large object specification structure.
** Reset the statement parameters.
*/
/* Get the size of a smart large object specification structure */
GBase 8s ODBC Driver 程序员指南
南大通用数据技术股份有限公司
- 98 -
rc = SQLGetInfo (hdbc, SQL_INFX_LO_SPEC_LENGTH, &lospec_size,
sizeof(lospec_size), NULL);
if (checkError (rc, SQL_HANDLE_DBC, hdbc, "Error in Step 2 -- SQLGetInfo
failed\n"))
goto Exit;
/* Allocate a buffer to hold the smart large object specification
structure*/
lospec_buffer = malloc (lospec_size);
/* Create a default smart large object specification structure */
rc = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT_OUTPUT,
SQL_C_BINARY,
SQL_INFX_UDT_FIXED, (UDWORD)lospec_size, 0, lospec_buffer,
lospec_size, &lospec_valsize);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 2 --
SQLBindParameter failed\n"))
goto Exit;
rc = SQLExecDirect (hstmt, "{call ifx_lo_def_create_spec(?)}", SQL_NTS);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 2 --
SQLExecDirect failed\n"))
goto Exit;
/* Reset the statement parameters */
rc = SQLFreeStmt (hstmt, SQL_RESET_PARAMS);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 2 --
SQLFreeStmt failed\n"))
goto Exit;
fprintf (stdout, "STEP 2 done...default smart large object specification
structure created\n");
/* STEP 3. Initialise the smart large object specification structure
** with values for the database column where the smart large
** object is being inserted.
** Reset the statement parameters.
*/
/* Initialise the smart large object specification structure */
rc = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR,
BUFFER_LEN, 0, colname, BUFFER_LEN, &colname_size);
GBase 8s ODBC Driver 程序员指南
南大通用数据技术股份有限公司
- 99 -
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 3 --
SQLBindParameter failed (param 1)\n"))
goto Exit;
lospec_valsize = lospec_size;
rc = SQLBindParameter (hstmt, 2, SQL_PARAM_INPUT_OUTPUT,
SQL_C_BINARY,
SQL_INFX_UDT_FIXED, (UDWORD)lospec_size, 0, lospec_buffer,
lospec_size, &lospec_valsize);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 3 --
SQLBindParameter failed (param 2)\n"))
goto Exit;
rc = SQLExecDirect (hstmt, "{call ifx_lo_col_info(?, ?)}", SQL_NTS);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 3 --
SQLExecDirect failed\n"))
goto Exit;
/* Reset the statement parameters */
rc = SQLFreeStmt (hstmt, SQL_RESET_PARAMS);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 3 --
SQLFreeStm failed\n"))
goto Exit;
fprintf(stdout, "STEP 3 done...smart large object specification
structure initialised\n");
/* STEP 4. Get the size of the smart large object pointer structure.
** Allocate a buffer to hold the structure.
*/
/* Get the size of the smart large object pointer structure */
rc = SQLGetInfo (hdbc, SQL_INFX_LO_PTR_LENGTH, &loptr_size,
sizeof(loptr_size), NULL);
if (checkError (rc, SQL_HANDLE_DBC, hdbc, "Error in Step 4 --
SQLGetInfo failed\n"))
goto Exit;
/* Allocate a buffer to hold the smart large object pointer structure */
loptr_buffer = malloc (loptr_size);
GBase 8s ODBC Driver 程序员指南
南大通用数据技术股份有限公司
- 100 -
fprintf (stdout, "STEP 4 done...smart large object pointer structure
allocated\n");
/* STEP 5. Create a new smart large object.
** Reset the statement parameters.
*/
/* Create a new smart large object */
rc = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_INFX_UDT_FIXED, (UDWORD)lospec_size, 0, lospec_buffer,
lospec_size, &lospec_valsize);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 5 --
SQLBindParameter failed (param 1)\n"))
goto Exit;
rc = SQLBindParameter (hstmt, 2, SQL_PARAM_INPUT, SQL_C_SLONG,
SQL_INTEGER, (UDWORD)0, 0, &mode, sizeof(mode), &cbMode);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 5 --
SQLBindParameter failed (param 2)\n"))
goto Exit;
loptr_valsize = loptr_size;
rc = SQLBindParameter (hstmt, 3, SQL_PARAM_INPUT_OUTPUT,
SQL_C_BINARY,
SQL_INFX_UDT_FIXED, (UDWORD)loptr_size, 0, loptr_buffer,
loptr_size, &loptr_valsize);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 5 --
SQLBindParameter failed (param 3)\n"))
goto Exit;
rc = SQLBindParameter (hstmt, 4, SQL_PARAM_OUTPUT, SQL_C_SLONG,
SQL_INTEGER, (UDWORD)0, 0, &lofd, sizeof(lofd), &lofd_valsize);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 5 --
SQLBindParameter failed (param 4)\n"))
goto Exit;
rc = SQLExecDirect (hstmt, "{call ifx_lo_create(?, ?, ?, ?)}", SQL_NTS);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 5 --
SQLExecDirect failed\n"))
goto Exit;
GBase 8s ODBC Driver 程序员指南
南大通用数据技术股份有限公司
- 101 -
/* Reset the statement parameters */
rc = SQLFreeStmt (hstmt, SQL_RESET_PARAMS);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 5 --
SQLFreeStmt failed\n"))
goto Exit;
fprintf (stdout, "STEP 5 done...smart large object created\n");
/* STEP 6. Open the file containing data for the new smart large object.
** Allocate a buffer to hold the smart large object data.
** Read data from the input file into the smart large object.
** data buffer
** Write data from the data buffer into the new smart large.
** object.
** Reset the statement parameters.
*/
/* Open the file containing data for the new smart large object */
hfile = open (lo_file_name, "rt");
/* sneaky way to get the size of the file */
write_size = lseek (open (lo_file_name, "rt"), 0L, SEEK_END);
/* Allocate a buffer to hold the smart large object data */
write_buffer = malloc (write_size + 1);
/* Read smart large object data from file */
read (hfile, write_buffer, write_size);
write_buffer[write_size] = '\0';
write_valsize = write_size;
/* Write data from the data buffer into the new smart large object */
rc = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG,
SQL_INTEGER, (UDWORD)0, 0, &lofd, sizeof(lofd), &lofd_valsize);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 6 --
SQLBindParameter failed (param 1)\n"))
goto Exit;
rc = SQLBindParameter (hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR,
(UDWORD)write_size, 0, write_buffer, write_size, &write_valsize);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 6 --
SQLBindParameter failed (param 2)\n"))
GBase 8s ODBC Driver 程序员指南
南大通用数据技术股份有限公司
- 102 -
goto Exit;
rc = SQLExecDirect (hstmt, "{call ifx_lo_write(?, ?)}", SQL_NTS);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 6 --
SQLExecDirect failed\n"))
goto Exit;
/* Reset the statement parameters */
rc = SQLFreeStmt (hstmt, SQL_RESET_PARAMS);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 6 --
SQLFreeStmt failed\n"))
goto Exit;
fprintf (stdout, "STEP 6 done...data written to new smart large
object\n");
/* STEP 7. Insert the new smart large object into the database.
** Reset the statement parameters.
*/
/* Insert the new smart large object into the database */
loptr_valsize = loptr_size;
rc = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_INFX_UDT_FIXED, (UDWORD)loptr_size, 0, loptr_buffer,
loptr_size, &loptr_valsize);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 7 --
SQLBindParameter failed\n"))
goto Exit;
rc = SQLExecDirect (hstmt, insertStmt, SQL_NTS);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 7 --
SQLExecDirect failed\n"))
goto Exit;
/* Reset the statement parameters */
rc = SQLFreeStmt (hstmt, SQL_RESET_PARAMS);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 7 --
SQLFreeStmt failed\n"))
goto Exit;
fprintf (stdout, "STEP 7 done...smart large object inserted into the
GBase 8s ODBC Driver 程序员指南
南大通用数据技术股份有限公司
- 103 -
database\n");
/* STEP 8. Close the smart large object.
*/
rc = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG,
SQL_INTEGER, (UDWORD)0, 0, &lofd, sizeof(lofd), &lofd_valsize);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 8 --
SQLBindParameter failed\n"))
goto Exit;
rc = SQLExecDirect (hstmt, "{call ifx_lo_close(?)}", SQL_NTS);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 8 --
SQLExecDirect failed\n"))
goto Exit;
fprintf (stdout, "STEP 8 done...smart large object closed\n");
/* STEP 9. Free the allocated buffers.
*/
free (lospec_buffer);
free (loptr_buffer);
free (write_buffer);
fprintf (stdout, "STEP 9 done...smart large object buffers freed\n");
Exit:
/* CLEANUP: Close the statement handle
** Free the statement handle
** Disconnect from the datasource
** Free the connection and environment handles
** Exit
*/
/* Close the statement handle */
SQLFreeStmt (hstmt, SQL_CLOSE);
/* Free the statement handle */
SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
GBase 8s ODBC Driver 程序员指南
南大通用数据技术股份有限公司
- 104 -
/* Disconnect from the data source */
SQLDisconnect (hdbc);
/* Free the environment handle and the database connection handle */
SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
SQLFreeHandle (SQL_HANDLE_ENV, henv);
fprintf (stdout,"\n\nHit
in = getchar ();
return (rc);
}