batch insert failed. Code: -700 Reason: SQLDA fields inconsistent for a multi-row SQLDA

[Former Member]

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 <stdlib.h>

#include <stdio.h>

#include <string.h>

#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);

}

>

[michael_loop]

If you look at the preparing_statements.cpp sample under .\SDK\dbcapi\examples folder of your install, you will see the line:

param.value.is_null     = NULL;   // use NULL if not interested in nullability

So I believe your problem is coming from the fact that you are defining col_val1 as NOT NULL(able) for rows 0 and 1 and NULL(able) for row 2. These are two different datatypes. If you want to be able to toggle IS_NULL, I think you need to set

col_val1.is_null = NULL;

I haven't tested this, but give it a try.

Regards,

Mike Loop
SAP Senior Support Engineer
SAP Product Support