DCI支持Object自定义类型解决了金仓数据库KingbaseES应用兼容Object类型问题
关键字
DCI、Object、oracle
问题描述
应用可以通过DCI接口访问Object类型，使用方式兼容oracle数据库常用方式。
问题分析
应用端缺少DCI接口支持兼容oracle数据库的Object类型的访问方式。
解决方案
在使用DCI接口（DCI兼容Oracle OCI接口）操作KingbaseES数据库时，可以使用DCIBindObject、DCIStmtExecute、DCIDefineObject等接口操作Object类型的数据，包括DML、DQL等功能。下面是使用DCI进行KingbaseES数据库Oracle模式中Object类型的操作的关键示例代码：
- 插入Object类型的数据：
// Object类型定义
struct addressinfo
{
OCIString *state;
OCIString *zip;
};
typedef struct addressinfo addressinfo;
struct null_addressinfo
{
sb2 null_adt;
sb2 null_state;
sb2 null_zip;
};
typedef struct null_addressinfo null_addressinfo;
// Object类型操作的SQL语句
static const text *const insstmt = (text *)
"INSERT INTO customerval (custno, addr) values (:custno, :addr)";
checkerr(errhp, DCIStmtPrepare(stmthp, errhp, (text *) insstmt,
(ub4) strlen((char *)insstmt),
(ub4) DCI_NTV_SYNTAX, (ub4) DCI_DEFAULT));
// 绑定Object类型数据
checkerr(errhp, DCIBindByName(stmthp, &bnd2p, errhp, (text *) ":addr",
(sb4) -1, (dvoid *) 0,
(sb4) 0, SQLT_NTY, (dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) DCI_DEFAULT));
checkerr(DCIDescribeAny(svchp, errhp, (text *)"ADDRESS_VALUE",
(ub4) strlen((char *)"ADDRESS_VALUE"), DCI_OTYPE_NAME,
(ub1)1, (ub1) DCI_PTYPE_TYPE, dschp));
checkerr(DCIDescribeAny(svchp, errhp, (text *)"ADDRESS_VALUE",
(ub4) strlen((char *)"ADDRESS_VALUE"), DCI_OTYPE_NAME,
(ub1)1, (ub1) DCI_PTYPE_TYPE, dschp));
// 固定对象
checkerr(errhp, DCIObjectPin(envhp, errhp, type_ref, (DCIComplexObject *) 0,
DCI_PIN_ANY, DCI_DURATION_SESSION, DCI_LOCK_NONE, (dvoid **)&addr_tdo));
// 设置Object类型绑定所需的附加属性
checkerr(errhp, DCIBindObject(bnd2p, errhp, addr_tdo, (dvoid **) &addr,
(ub4 *) 0, (dvoid **) &naddr, (ub4 *) 0));
for(i = 0; i <= nrows; i++)
{
addr->state = (DCIString *) 0;
sprintf(buf, "%cA", 65+i%27);
checkerr(errhp, DCIStringAssignText(envhp, errhp,
(CONST text*) buf, 2, &addr->state));
addr->zip = (DCIString *) 0;
sprintf(buf, "94%d", i+455);
checkerr(errhp, DCIStringAssignText(envhp, errhp, (CONST text*) buf, 10, &addr->zip));
naddr->null_adt = 0;
naddr->null_state = 0;
naddr->null_zip = 0;
checkerr(errhp, DCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,(DCISnapshot *) NULL, (DCISnapshot *) NULL, (ub4) DCI_DEFAULT));
}
checkerr(errhp, DCITransCommit(svchp, errhp, (ub4) 0));
- 查询包含Object类型数据的结果：
// Object类型操作的SQL语句
static const text *const selvalstmt = (text *)
"SELECT custno, addr FROM customerval order by custno";
checkerr(errhp, DCIStmtPrepare(stmthp, errhp, (text *) selvalstmt,
(ub4) strlen((char *)selvalstmt),
(ub4) DCI_NTV_SYNTAX, (ub4) DCI_DEFAULT));
// Object类型变量定义
checkerr(errhp, DCIDefineByPos(stmthp, &defn2p, errhp, (ub4) 2, (dvoid *) 0,
(sb4) 0, SQLT_NTY, (dvoid *) 0, (ub2 *)0,
(ub2 *)0, (ub4) DCI_DEFAULT));
DCIDescribeAny(svchp, errhp, (text *)"ADDRESS_VALUE",
(ub4) strlen((char *)"ADDRESS_VALUE"), DCI_OTYPE_NAME,
(ub1)1, (ub1) DCI_PTYPE_TYPE, dschp);
// 固定对象
checkerr(errhp, DCIObjectPin(envhp, errhp, type_ref, (DCIComplexObject *) 0,
DCI_PIN_ANY, DCI_DURATION_SESSION, DCI_LOCK_NONE, (dvoid **)&addr_tdo));
// 定义Object类型所需的附加属性
checkerr(errhp, DCIDefineObject(defn2p, errhp, addr_tdo, (dvoid **) &addr,
(ub4 *) 0, (dvoid **) 0, (ub4 *) 0));
checkerr(errhp, DCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
(DCISnapshot *) NULL, (DCISnapshot *) NULL, (ub4) DCI_DEFAULT));
// 获取Object类型数据
do
{
if (addr)
printf("custno = %d address.state = %s address.zip = %s\n", custno,
DCIStringPtr(envhp, addr->state), DCIStringPtr(envhp, addr->zip));
str_rslt_state = (char *)DCIStringPtr(envhp, addr->state);
str_rslt_zip = (char *)DCIStringPtr(envhp, addr->zip);
else
printf("custno = %d fetched address is NULL\n", custno);
addr = (addressinfo *)NULL;
}
while ((status = DCIStmtFetch2((DCIStmt *)stmthp, (DCIError *)errhp,
(ub4) 1, (ub4) DCI_FETCH_NEXT, (sb4) 0,
(ub4) DCI_DEFAULT)) == DCI_SUCCESS ||
status == DCI_SUCCESS_WITH_INFO);
请注意，上述示例中Object成员使用了OCIString类型，此类型与数据库中文本类型Object成员相对应，DCI Object成员还兼容了数值、时间等基本数据类型，您可以根据自己的需求进行适当的调整和扩展。 在使用这些示例代码之前，您需要确保已正确加载KingbaseES DCI驱动程序，并且已经建立了与数据库的连接。 更多信息，参见help.kingbase.com.cn/v8/index.ht…
