在std::字符串或字符数组中间包含NULL字符的UPDATE

Question

我们使用Oracle 12c数据库和带有OCCI的CentOS7进行连接。我们试图在数据库中插入一个字符数组，但这个字符数组在中间有一个NUL字符。当我们使用语句->setString函数时，更新是成功的，但是一旦它看到NUL字符，它只会在后面放NUL字符。请参阅此示例代码及其输出。

使用setString的示例代码：

static void Run(const std::string &connectionString, const std::string &user, const std::string &pwd)
{
Environment *env = Environment::createEnvironment();
Connection *conn = env->createConnection(user, pwd, connectionString);
Statement *stmt = conn->createStatement("UPDATE my_customers SET first_name = :1 WHERE customer_id = :2");
stmt->setString(1, std::string("GEORGE              ", 20));
stmt->setInt(2, 10);
stmt->setString(1, std::string(adrs_first_name, sizeof(adrs_first_name)));
oracle::occi::Statement::Status status = stmt->execute();
conn->terminateStatement(stmt);
conn->commit();
}

更新后访问数据库：

SELECT first_name FROM my_customers WHERE customer_id = 10;
GEO
SELECT rawtohex(first_name) FROM my_customers WHERE customer_id = 10;
47454F0000000000000000000000000000000000

然而，我本以为是

47454F0047452032322020202020202020202020

所以我尝试使用oracle:：occi:：Bytes——出现了这个错误

ORA-12899：值对于列"MAIN_USER"太大。"MY_CUSTOMERS"。"FIRST_NAME"(实际值：40，最大值：20)

使用setBytes的示例代码：

static void Run(const std::string &connectionString, const std::string &user, const std::string &pwd)
{
Environment *env = Environment::createEnvironment();
Connection *conn = env->createConnection(user, pwd, connectionString);
Statement *stmt = conn->createStatement("UPDATE my_customers SET first_name = :1 WHERE customer_id = :2");
std::string s("GEORGE              ", 20);
oracle::occi::Bytes bytes((unsigned char *)s.c_str(), 20, 0, env);
stmt->setBytes(1, bytes);
stmt->setInt(2, 10);
try
{
oracle::occi::Statement::Status status = stmt->execute();
}
catch (oracle::occi::SQLException &e)
{
std::cout << "Error " << e.getErrorCode() << ": " << e.what() << std::endl;
}
conn->terminateStatement(stmt);
conn->commit();
}

输出：

Error 12899 : ORA - 12899 : value too large for column "MAIN_USER"."MY_CUSTOMERS"."FIRST_NAME" (actual : 40, maximum : 20)

因此，我尝试通过将oracle:：occi:：bytes构造函数的第二个参数更改为10来发送一半的字节，但它成功了，但在从数据库中读取值后，我意识到它是字符十六进制值的字符串表示。因此，我现在的问题是，当我传递oracle:：occi:Bytes时，Oracle12c为什么要将十六进制值作为字符串。

使用一半实际长度和setBytes的示例代码：

static void Run(const std::string &connectionString, const std::string &user, const std::string &pwd)
{
Environment *env = Environment::createEnvironment();
Connection *conn = env->createConnection(user, pwd, connectionString);
Statement *stmt = conn->createStatement("UPDATE my_customers SET first_name = :1 WHERE customer_id = :2");
std::string s("GEORGE              ", 20);
oracle::occi::Bytes bytes((unsigned char *)s.c_str(), 10, 0, env);
stmt->setBytes(1, bytes);
stmt->setInt(2, 10);
try
{
oracle::occi::Statement::Status status = stmt->execute();
}
catch (oracle::occi::SQLException &e)
{
std::cout << "Error " << e.getErrorCode() << ": " << e.what() << std::endl;
}
conn->terminateStatement(stmt);
conn->commit();
}

更新后访问数据库：从customer_id=10的my_customers中选择first_name；

47454F00524745202020

注意：这个查询没有用rawtohex包装——这是数据库中的实际char数组值

以下是表格定义：

DESCRIBE MAIN_USER.MY_CUSTOMERS
Name                           Null Type
------------------------------ ---- --------------
CUSTOMER_ID                         NUMBER(10)
FIRST_NAME                          CHAR(20 CHAR)

以下是我们的Oracle实例信息：Oracle Database 12c Enterprise Edition 12.1.0.2.0版-64位生产版有了分区、实际应用程序集群、自动存储管理、OLAP、，高级分析和实际应用程序测试选项

我们使用的是Oracle occi客户端12.1 64位

Answer 1

对于那些可能偶然发现这一点的人，我最终与Oracle支持部门进行了交谈，他们告诉我没有办法做我想做的事情(正如预期的那样)，然而Byte的解决方法让我们更接近了，我们能够将setBytes传递的十六进制字符串转换为原始数据，然后将该原始数据转换为varchar2，最终"工作"——然而我不知道我们稍后会遇到什么影响，但到目前为止，它似乎奏效了。

代码：

static void Run(const std::string &connectionString, const std::string &user, const std::string &pwd)
{
Environment *env = Environment::createEnvironment();
Connection *conn = env->createConnection(user, pwd, connectionString);
Statement *stmt = conn->createStatement("UPDATE my_customers SET first_name = utl_raw.cast_to_varchar2(hextoraw(:1)) WHERE customer_id = :2");
std::string s("GEORGE              ", 20);
oracle::occi::Bytes bytes((unsigned char *)s.c_str(), 20, 0, env);
stmt->setBytes(1, bytes);
stmt->setInt(2, 10);
try
{
oracle::occi::Statement::Status status = stmt->execute();
}
catch (oracle::occi::SQLException &e)
{
std::cout << "Error " << e.getErrorCode() << ": " << e.what() << std::endl;
}
conn->terminateStatement(stmt);
conn->commit();
}

然后检查数据库：

SELECT rawtohex(first_name) FROM my_customers WHERE customer_id = 10;
47454F0052474520202020202020202020202020

所以它似乎工作