Hi experts, I am testing sacapi of SQL Anywhere17, my test version is "SQLA17Developer.exe". When batch insert with NULL values, failed with "Code: -700 Reason: SQLDA fields inconsistent for a multi-row SQLDA". I set the row#2, col#1 to NULL with col_val1.is_null[row_no] = 1. Is something wrong? Here is source code #include "sacapidll.h" SQLAnywhereInterface api; //create table andy.t_varchar(id integer, name varchar(40)); void testBatchInsert(a_sqlany_connection* sqlany_conn) { a_sqlany_stmt* stmt = NULL; char reason[SACAPI_ERROR_SIZE]; int code = 0; stmt = api.sqlany_prepare(sqlany_conn, "insert into andy.t_varchar(id, name) values(?, ?)"); if (stmt == NULL) { code = api.sqlany_error(sqlany_conn, reason, sizeof(reason)); printf("stmt parse failed. Code: %d Reason: %s\n", code, reason); return; } //set batch size to 3 const int batch_size = 3; //1. bind params const int param_cnt = 2; a_sqlany_bind_param param0; a_sqlany_bind_param param1; //api.sqlany_describe_bind_param(stmt, 0, ¶m0); param0.direction = DD_INPUT; a_sqlany_data_value& col_val0 = param0.value; col_val0.is_null = new sacapi_bool[batch_size]; col_val0.is_address = 0; col_val0.length = new size_t[batch_size]; col_val0.type = A_UVAL32; col_val0.buffer_size = sizeof(unsigned int); col_val0.buffer = new char[batch_size * col_val0.buffer_size]; for (int batch_no = 0; batch_no < batch_size; batch_no++) { col_val0.length[batch_no] = col_val0.buffer_size; col_val0.is_null[batch_no] = 0; } api.sqlany_bind_param(stmt, 0, ¶m0); //api.sqlany_describe_bind_param(stmt, 1, ¶m1); param1.direction = DD_INPUT; a_sqlany_data_value& col_val1 = param1.value; col_val1.is_null = new sacapi_bool[batch_size]; col_val1.is_address = 0; col_val1.length = new size_t[batch_size]; col_val1.type = A_STRING; col_val1.buffer_size = 64; col_val1.buffer = new char[batch_size * col_val1.buffer_size]; for (int batch_no = 0; batch_no < batch_size; batch_no++) { col_val1.length[batch_no] = 0; col_val1.is_null[batch_no] = 0; } api.sqlany_bind_param(stmt, 1, ¶m1); //2. set array values char s_val[128]; int len = 0; int row_no = 0; //row#0 row_no = 0; col_val0.is_null[row_no] = 0; //not null *((unsigned int*)col_val0.buffer + row_no) = row_no; col_val1.is_null[row_no] = 0;//not null sprintf(s_val, "str val%u", row_no); len = strlen(s_val); memcpy(col_val1.buffer + row_no * col_val1.buffer_size, s_val, len); col_val1.length[row_no] = len; //row#1 row_no = 1; col_val0.is_null[row_no] = 0; //not null *((unsigned int*)col_val0.buffer + row_no) = row_no; col_val1.is_null[row_no] = 0;//not null sprintf(s_val, "str val%u", row_no); len = strlen(s_val); memcpy(col_val1.buffer + row_no * col_val1.buffer_size, s_val, len); col_val1.length[row_no] = len; //row#2 row_no = 2; col_val0.is_null[row_no] = 0; //not null *((unsigned int*)col_val0.buffer + row_no) = row_no; col_val1.is_null[row_no] = 1;//THIS COLUMN VALUE is NULL, report "batch stmt failed. Code: -700 Reason: SQLDA fields inconsistent for a multi-row SQLDA" sprintf(s_val, "str val%u", row_no); len = strlen(s_val); memcpy(col_val1.buffer + row_no * col_val1.buffer_size, s_val, len); col_val1.length[row_no] = len; //3. execute sacapi_bool success_execute = api.sqlany_set_batch_size(stmt, batch_size); if (!api.sqlany_execute(stmt)) { code = api.sqlany_error(sqlany_conn, reason, sizeof(reason)); printf("batch stmt failed. Code: %d Reason: %s\n", code, reason); } else { int row_cnt = api.sqlany_affected_rows(stmt); printf("batch stmt insert [%u] OK.\n", row_cnt); } //commit if (!api.sqlany_commit(sqlany_conn)) { code = api.sqlany_error(sqlany_conn, reason, sizeof(reason)); printf("commit failed. Code: %d Reason: %s\n", code, reason); } api.sqlany_free_stmt(stmt); } asked 27 Jan '18, 21:07 andy |