Binding parameters to select query for varchar column

[Margaret Lawson]

I am attempting to perform a select query where a varchar column has a particular value. This works if I hard-code in the search string (e.g., name='max'), but is not working when I attempt to bind the parameter as a SQL_TEXT+1 or a SQL_VARYING+1 parameter. In my attempt to get it to work, I have also tried changing it to a like query, or even wrapping the search term in single quotes (which, I know, should only be used for hard-coded strings). An example is included below. As always, any help would be appreciated.

#################################################################

#include <stdlib.h>

#include <string.h>
#include <ibase.h>
#include <stdio.h>
#include <set>
#include <iostream>
#include <vector>

#define RC_OK 0
#define STRING_SIZE 256

using namespace std;

#define SQL_DIALECT_VERSION 3
#define DB_HANDLE_COUNT 1
#define USE_XSQLDA 1


#ifndef NDEBUG
#   define ASSERT(condition, message) \
    do { \
        if (! (condition)) { \
            std::cerr << "Assertion `" #condition "` failed in " << __FILE__ \
                      << " line " << __LINE__ << ": " << message << std::endl; \
            std::terminate(); \
        } \
    } while (false)
#else
#   define ASSERT(condition, message) do { } while (false)
#endif

typedef struct DB_ENTRY {
    string data;
    string name;

    DB_ENTRY(const string &my_data, const string &my_name) {
        data = my_data ;
        name =  my_name;
    }

    DB_ENTRY() {}

} db_entry;

typedef struct SQL_VARCHAR {
    short len;
    char data[STRING_SIZE+1];
} sql_varchar;

isc_db_handle db = NULL;
isc_stmt_handle stmt = NULL;
size_t num_cols;

void bind_varchar(XSQLVAR *param, int &i, sql_varchar *val, short *is_null, bool write = false) {
    param->sqltype = SQL_VARYING + 1;
    param->sqldata = (char*)val;
    param->sqlind = is_null;
    if(write) {
        param->sqllen = val->len;
    }
    i++;             
}

void bind_text(XSQLVAR *param, int &i, char *val, short *is_null, bool write = false, long unsigned int size = 0) {
    param->sqltype = SQL_TEXT + 1;
    param->sqldata = val;
    param->sqlind = is_null;
    if(write) {
        param->sqllen = size;
    }
    i++;             
}


int initialize_db() {
    int rc;
    ISC_STATUS_ARRAY status; /* status vector */
    isc_tr_handle trans = NULL; /* transaction handle */
    //fix
    string db_name = "correctness_test_varchar.fdb";
    string create_db_query = "CREATE DATABASE '" + db_name + "' PAGE_SIZE=8192";

    /*
     *    Create a new database.
     *    The database handle is zero.
     */
       if (isc_dsql_execute_immediate(status, &db, &trans, 0, create_db_query.c_str(), SQL_DIALECT_VERSION,
                                   NULL))
    {
        /* Extract SQLCODE from the status vector. */
        long sqlcode = isc_sqlcode(status);

        /* Print a descriptive message based on the SQLCODE. */
        if (sqlcode == -902)
        {
            printf("\nDatabase already exists.\n");
            // printf("Remove %s before running this program.\n\n", new_dbname);
            rc = isc_attach_database(status, 0, db_name.c_str(), &db, 0, NULL);
            ASSERT(rc == RC_OK, isc_print_status(status));
        }
    }

    isc_commit_transaction(status, &trans);
    printf("Created database '%s'.\n\n", db_name.c_str());

    return rc;
}


//designed to create one at a time, so we don't have to worry about cross-table interference
int create_table() {
    int rc;
    ISC_STATUS_ARRAY status; /* status vector */
    isc_tr_handle trans = NULL; /* transaction handle */
    string create_table_query;

    char query_buffer[512] = "recreate table my_table (name varchar (50) not null, data varchar(256))";

    /* Create table. */
    isc_start_transaction(status, &trans, DB_HANDLE_COUNT, &db, 0, NULL);
    rc = isc_dsql_execute_immediate(status, &db, &trans, 0, query_buffer, SQL_DIALECT_VERSION, NULL);
    ASSERT(rc == RC_OK, isc_print_status(status));
    isc_commit_transaction(status, &trans);

    return rc;
}
       
int prepare_statements() {
    int rc;
    ISC_STATUS_ARRAY status; /* status vector */
    isc_tr_handle trans = NULL; /* transaction handle */
    rc = isc_dsql_allocate_statement(status, &db, &stmt);

    string query = "insert into my_table (name, data) values (?, ?)";
    num_cols = 2;

    rc = isc_dsql_prepare(status, &trans, &stmt, 0, query.c_str(), SQL_DIALECT_VERSION, NULL);
    ASSERT(rc == RC_OK, isc_print_status(status));

    return rc;
}


XSQLDA *init_stmt_input_params(int num_input_params) {
    XSQLDA *stmt_input_params = (XSQLDA *) malloc(XSQLDA_LENGTH(num_input_params));
    stmt_input_params->sqln = stmt_input_params->sqld = num_input_params;
    stmt_input_params->version = 1;
    return stmt_input_params;
}

XSQLDA *init_stmt_output_params(int num_output_params) {
    XSQLDA *stmt_output_params = (XSQLDA *) malloc(XSQLDA_LENGTH(num_output_params));
    stmt_output_params->sqln = stmt_output_params->sqld = num_output_params;
    stmt_output_params->version = 1;
    return stmt_output_params;
}

int perform_write(const db_entry &entry) {
    int rc;
    ISC_STATUS_ARRAY status; /* status vector */
    isc_tr_handle trans = NULL; /* transaction handle */
    short is_null[num_cols];
    size_t length[num_cols];
    sql_varchar name_buffer;
    sql_varchar data_buffer;

    XSQLDA *stmt_input_params = (XSQLDA *) malloc(XSQLDA_LENGTH(num_cols));
    stmt_input_params->sqln = stmt_input_params->sqld = num_cols;
    stmt_input_params->version = 1;

    int i = 0;
    strncpy(name_buffer.data, entry.name.c_str(), entry.name.size()+1); /* string  */
    name_buffer.len = entry.name.size();
    is_null[i] = (name_buffer.len == 0);
    bind_varchar(&stmt_input_params->sqlvar[i], i, &name_buffer, &is_null[i], true);
    strncpy(data_buffer.data, entry.data.c_str(), entry.data.size()+1); /* string  */
    data_buffer.len = entry.data.size();
    is_null[i] = (data_buffer.len == 0);
    bind_varchar(&stmt_input_params->sqlvar[i], i, &data_buffer, &is_null[i], true);

    /* Declare the cursor. */
    isc_dsql_set_cursor_name(status, &stmt, "write md", 0);

    rc = isc_start_transaction(status, &trans, DB_HANDLE_COUNT, &db, 0, NULL);
    ASSERT(rc == RC_OK, isc_print_status(status));

    rc = isc_dsql_execute(status, &trans, &stmt, SQL_DIALECT_VERSION, stmt_input_params);
    ASSERT(rc == RC_OK, isc_print_status(status));

    rc = isc_commit_transaction(status, &trans);
    ASSERT(rc == RC_OK, isc_print_status(status));

    free(stmt_input_params);

    return rc;
}


void print_db_entry(const db_entry &entry) {
    std::cout << "\t\tentry - name: " << entry.name << ", data: " << entry.data << endl;
}

void print_db_entries(const std::vector<db_entry> &entries) {
    for(const db_entry &entry : entries) {
        print_db_entry(entry);
    }
    cout << endl;
}


int retrieve_matching_entries(isc_stmt_handle *stmt, XSQLDA *stmt_input_params, vector<db_entry> &entries) {
    int rc;
    ISC_STATUS_ARRAY status; /* status vector */
    isc_tr_handle trans = NULL; /* transaction handle */
    db_entry entry;

    sql_varchar name_buffer;
    sql_varchar data_buffer;

    short is_null[num_cols];
    XSQLDA *stmt_output_params = init_stmt_output_params(num_cols);
    rc = isc_dsql_describe(status, stmt, USE_XSQLDA, stmt_output_params);
    ASSERT(rc == RC_OK, isc_print_status(status));

    int i  = 0;
    bind_varchar(&stmt_output_params->sqlvar[i], i, &name_buffer, &is_null[i]);
    bind_varchar(&stmt_output_params->sqlvar[i], i, &data_buffer, &is_null[i]);

    rc = isc_start_transaction(status, &trans, DB_HANDLE_COUNT, &db, 0, NULL);
    ASSERT(rc == RC_OK, isc_print_status(status));

    rc = isc_dsql_execute(status, &trans, stmt, USE_XSQLDA, stmt_input_params);
    ASSERT(rc == RC_OK, isc_print_status(status));

    while (!isc_dsql_fetch(status, stmt, USE_XSQLDA, stmt_output_params)) {
        entry.name = name_buffer.data;
        entry.data = data_buffer.data;
        entries.push_back(entry);
    }
    rc = isc_dsql_free_statement (status, stmt, DSQL_close);
    ASSERT(rc == RC_OK, isc_print_status(status));

    rc = isc_commit_transaction(status, &trans);
    ASSERT(rc == RC_OK, isc_print_status(status));

    free(stmt_output_params);
    return rc;
}


void print_query_results(const string &query) {
    int rc;
    vector<db_entry> entries;
    ISC_STATUS_ARRAY status; /* status vector */
    isc_tr_handle trans = NULL; /* transaction handle */
    isc_stmt_handle stmt = NULL;
    isc_dsql_allocate_statement(status, &db, &stmt);    
    rc = isc_dsql_prepare(status, &trans, &stmt, 0, query.c_str(), SQL_DIALECT_VERSION, NULL);
  
    rc = retrieve_matching_entries(&stmt, NULL, entries);
    cout << "db entries where name = 'max':" << endl;
    print_db_entries(entries);
}

void find_matching() {
    string query;
    query = "select * from my_table where name = 'max'";
    print_query_results(query);
}

void find_matching_w_bound_params(const string &to_search_for, bool use_varchar, bool like = false) {
    int rc, i = 0;
    vector<db_entry> matching_entries;
    ISC_STATUS_ARRAY status; /* status vector */
    isc_tr_handle trans = NULL; /* transaction handle */
    isc_stmt_handle stmt = NULL;
    size_t num_input_params = 1;
    string query;
    if(like) {
        query = "select * from my_table where name like ?";       
    }
    else {
        query = "select * from my_table where name = ?";       
    }

    isc_dsql_allocate_statement(status, &db, &stmt);    
    rc = isc_dsql_prepare(status, &trans, &stmt, 0, query.c_str(), SQL_DIALECT_VERSION, NULL);

    XSQLDA *stmt_input_params = init_stmt_input_params(num_input_params);

    short is_null[num_input_params];
    if(use_varchar) {
        sql_varchar text_buffer;
        strncpy(text_buffer.data, to_search_for.c_str(), to_search_for.size()+1);
        text_buffer.len = to_search_for.size();
        bind_varchar(&stmt_input_params->sqlvar[i], i, &text_buffer, &is_null[i], true);       
    }
    else {
        char text_buffer[256];
        strncpy(text_buffer, to_search_for.c_str(), to_search_for.size()+1);
        long unsigned int str_len = to_search_for.size();
        bind_text(&stmt_input_params->sqlvar[i], i, text_buffer, &is_null[i], str_len);
    }

    rc = retrieve_matching_entries(&stmt, stmt_input_params, matching_entries);
    if(use_varchar) {
        cout << "using varchar, ";
    }
    else {
        cout << "using text, ";
    }

    if(like) {
        cout << "db entries where have bound name like " << to_search_for << ":" << endl;
    }
    else {
        cout << "db entries where have bound name = " << to_search_for << ":" << endl;
    }
    print_db_entries(matching_entries);

}

  
int main() {

    string name_buffer0 = "max";
    string name_buffer1 = "note_freq";

    string data_buffer_str = "I discovered made a very interesting observation that XXXXXX";
    string data_buffer_dim0_min = "7830992237.586059";
    string data_buffer_dim0_max = "2777747108.031878";
    string data_buffer_dim1_min = "5134009101.956155";
    string data_buffer_dim1_max = "6069688762.570586";
    string data_buffer_dim2_min = "4009443942.461835";

    db_entry entry0(data_buffer_dim0_min, name_buffer0);
    db_entry entry1(data_buffer_str, name_buffer1);
    db_entry entry2(data_buffer_dim0_max, name_buffer0);
    db_entry entry3(data_buffer_dim1_min, name_buffer0);
    db_entry entry4(data_buffer_str, name_buffer1);
    db_entry entry5(data_buffer_dim1_max, name_buffer0);
    db_entry entry6(data_buffer_dim2_min, name_buffer0);

    initialize_db();
    create_table();
    prepare_statements();
    perform_write(entry0);
    perform_write(entry1);
    perform_write(entry2);
    perform_write(entry3);
    perform_write(entry4);
    perform_write(entry5);
    perform_write(entry6);

    find_matching();
    find_matching_w_bound_params("max", true);
    find_matching_w_bound_params("'max'", true);
    find_matching_w_bound_params("%max%", true, true);
    find_matching_w_bound_params("'%max%'", true, true);
    find_matching_w_bound_params("max", false);
    find_matching_w_bound_params("'max'", false);
    find_matching_w_bound_params("%max%", false, true);
    find_matching_w_bound_params("'%max%'", false, true);

}

[Dimitry Sibiryakov]

05.04.2021 07:43, Margaret Lawson wrote:
> but is not working when I attempt to bind the parameter as a SQL_TEXT+1 or a SQL_VARYING+1
> parameter.

You still bind it wrong. This time you forgot to initialize null indicators which are
important if you use SQL_TEXT/SQL_VARYING+1 because "+1" means "nullable".

Also if you initialize SQLDA by isc_dsql_prepare/isc_dsql_describe_bind() don't try to
overwrite any SQLVAR field except sqldata and sqlnull.

Suggestion to use Trace for debugging is still valid.

--
WBR, SD.

[Margaret Lawson]

Thank you. I wasn't sure if I needed to initialize null indicators for reading, but I have fixed this and the reading is now working. I am now running into one final problem (which I can post as a separate problem if you prefer). All of my columns are working properly except for the smallint column, which always returns zero.

#######################################################################################

#include <stdlib.h>
#include <string.h>
#include <ibase.h>
#include <stdio.h>
#include <set>
#include <iostream>
#include <vector>

#define RC_OK 0
#define STRING_SIZE 256

using namespace std;

#define SQL_DIALECT_VERSION 3
#define DB_HANDLE_COUNT 1
#define USE_XSQLDA 1

#define NUM_DIMS 3

#ifndef NDEBUG
#   define ASSERT(condition, message) \
    do { \
        if (! (condition)) { \
            std::cerr << "Assertion `" #condition "` failed in " << __FILE__ \
                      << " line " << __LINE__ << ": " << message << std::endl; \
            std::terminate(); \
        } \
    } while (false)
#else
#   define ASSERT(condition, message) do { } while (false)
#endif

typedef std::vector<double> point;
typedef std::pair<point, point> bbox;

typedef struct DB_ENTRY {
    string string0;
    size_t long0;
    string string1;
    bbox bb;
    short short0;
    string string2;

    DB_ENTRY(const string &my_string0, size_t my_long0, const string &my_string1, const bbox &my_bb, short my_short, const string &my_string2) {
        string0 = my_string0;
        long0 = my_long0;
        string1 = my_string1;
        bb = my_bb;
        short0 = my_short;
        string2 = my_string2;

    }

    DB_ENTRY() {}

} db_entry;

typedef struct SQL_VARCHAR {
    short len;
    char data[STRING_SIZE+1];
} sql_varchar;

isc_db_handle db = NULL;
isc_stmt_handle stmt = NULL;
size_t num_cols;


void bind_varchar(XSQLVAR *param, int &i, sql_varchar *val, short *is_null, bool write = false) {

    param->sqldata = (char*)val;
    param->sqlind = is_null;
    if(write) {
        param->sqltype = SQL_VARYING + 1;

        param->sqllen = val->len;
    }
    else {
        *is_null = 0;

    }
    i++;             
}

void bind_text(XSQLVAR *param, int &i, char *val, short *is_null, bool write = false, long unsigned int size = 0) {

    param->sqldata = val;
    param->sqlind = is_null;
    if(write) {
        param->sqltype = SQL_TEXT + 1;

        param->sqllen = size;
    }
    i++;             
}

void bind_text(XSQLVAR *param, int &i, char *val, short *is_null, long unsigned int size) {
    bind_text(param, i, val, is_null, true, size);
}

void bind_double(XSQLVAR *param, int &i, double *val, short *is_null, bool write = false) {
    param->sqldata = (char *)val;
    *is_null = 0;

    param->sqlind = is_null;
    if(write) {

        param->sqltype = SQL_DOUBLE;
        param->sqllen = sizeof(*val);
    }
    i++;             
}

void bind_long(XSQLVAR *param, int &i, size_t *val, short *is_null, bool write = false) {
    param->sqldata = (char *)val;
    *is_null = 0;

    param->sqlind = is_null;
    if(write) {

        param->sqltype = SQL_INT64;
        param->sqllen = sizeof(*val);
    }
    i++;             
}

template <class T>
void bind_tiny(XSQLVAR *param, int &i, T *val, short *is_null, bool write = false) {
    param->sqldata = (char *)val;
    *is_null = 0;

    param->sqlind = is_null;
    if(write) {

        param->sqltype = SQL_SHORT;
        param->sqllen = sizeof(*val);
    }
    i++;             
}


template <class T>
void bind_spatial(XSQLDA *stmt_input_params, int &i, const T &entry, short *is_null, bool write = false) {
    int rc;
    for(size_t dim  = 0; dim < NUM_DIMS; dim++) {
        bind_double(&stmt_input_params->sqlvar[i], i, (double*)&entry.bb.first[dim], &is_null[i], write);
        bind_double(&stmt_input_params->sqlvar[i], i, (double*)&entry.bb.second[dim], &is_null[i], write);

    }
}


int initialize_db() {
    int rc;
    ISC_STATUS_ARRAY status; /* status vector */
    isc_tr_handle trans = NULL; /* transaction handle */
    //fix

    string db_name = "/vscratch1/mlawso/database_evals/firebird/databases/correctness_test.fdb";

                        "(id bigint generated always as identity primary key not null, string0 varchar (50) not null, long0 bigint not null, string1 varchar (50) not null, "
                        "d0_min double precision, d0_max double precision, d1_min double precision, d1_max double precision, d2_min double precision, d2_max double precision, "
                        "short0 smallint not null, string2 varchar(256)"

                        ")";

    /* Create table. */
    isc_start_transaction(status, &trans, DB_HANDLE_COUNT, &db, 0, NULL);
    rc = isc_dsql_execute_immediate(status, &db, &trans, 0, query_buffer, SQL_DIALECT_VERSION, NULL);
    ASSERT(rc == RC_OK, isc_print_status(status));
    isc_commit_transaction(status, &trans);

    return rc;
}
       
int prepare_statements() {
    int rc;
    ISC_STATUS_ARRAY status; /* status vector */
    isc_tr_handle trans = NULL; /* transaction handle */
    rc = isc_dsql_allocate_statement(status, &db, &stmt);

    string query = "insert into my_table (string0, long0, string1, d0_min, d0_max, d1_min, d1_max, d2_min, d2_max, short0, string2) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    num_cols = 12; //add one for id

    rc = isc_dsql_prepare(status, &trans, &stmt, 0, query.c_str(), SQL_DIALECT_VERSION, NULL);
    ASSERT(rc == RC_OK, isc_print_status(status));

    return rc;
}


XSQLDA *init_stmt_input_params(int num_input_params) {
    XSQLDA *stmt_input_params = (XSQLDA *) malloc(XSQLDA_LENGTH(num_input_params));
    stmt_input_params->sqln = stmt_input_params->sqld = num_input_params;
    stmt_input_params->version = 1;
    return stmt_input_params;
}

XSQLDA *init_stmt_output_params(int num_output_params) {
    XSQLDA *stmt_output_params = (XSQLDA *) malloc(XSQLDA_LENGTH(num_output_params));
    stmt_output_params->sqln = stmt_output_params->sqld = num_output_params;
    stmt_output_params->version = 1;
    return stmt_output_params;
}

int perform_write(const vector<db_entry> &entries) {
    size_t num_cols_to_write = num_cols - 1;

    int rc;
    ISC_STATUS_ARRAY status; /* status vector */
    isc_tr_handle trans = NULL; /* transaction handle */

    db_entry entry;

    entry.bb.first.resize(NUM_DIMS);
    entry.bb.second.resize(NUM_DIMS);

    sql_varchar string0_buffer;
    sql_varchar string1_buffer;
    sql_varchar string2_buffer;
    short short0;

    vector<unsigned long> length(num_cols_to_write);
    short is_null[num_cols_to_write];

    XSQLDA *stmt_input_params = init_stmt_input_params(num_cols_to_write);

    rc = isc_start_transaction(status, &trans, DB_HANDLE_COUNT, &db, 0, NULL);
    ASSERT(rc == RC_OK, isc_print_status(status));

    for(int entry_index  = 0; entry_index < entries.size(); entry_index++) {
        entry = entries.at(entry_index);

        int i = 0;
        strncpy(string0_buffer.data, entry.string0.c_str(), entry.string0.size()+1); /* string  */
        string0_buffer.len = entry.string0.size();
        is_null[i] = (string0_buffer.len == 0);
        bind_varchar(&stmt_input_params->sqlvar[i], i, &string0_buffer, &is_null[i], true);
        bind_long(&stmt_input_params->sqlvar[i], i, &entry.long0, &is_null[i], true);
        strncpy(string1_buffer.data, entry.string1.c_str(), entry.string1.size()+1); /* string  */
        string1_buffer.len = entry.string1.size();
        is_null[i] = (string1_buffer.len == 0);
        bind_varchar(&stmt_input_params->sqlvar[i], i, &string1_buffer, &is_null[i], true);
        bind_spatial(stmt_input_params, i, entry, is_null, true);
        bind_tiny(&stmt_input_params->sqlvar[i], i, &entry.short0, &is_null[i], true);
        strncpy(string2_buffer.data, entry.string2.c_str(), entry.string2.size()+1); /* string  */
        string2_buffer.len = entry.string2.size();
        is_null[i] = (string2_buffer.len == 0);
        bind_varchar(&stmt_input_params->sqlvar[i], i, &string2_buffer, &is_null[i], true);

        isc_dsql_set_cursor_name(status, &stmt, "write md", 0);

        rc = isc_dsql_execute(status, &trans, &stmt, USE_XSQLDA, stmt_input_params);
        ASSERT(rc == RC_OK, isc_print_status(status));

    }

    rc = isc_commit_transaction(status, &trans);
    ASSERT(rc == RC_OK, isc_print_status(status));

    free(stmt_input_params);

    return rc;
}

template <class T>
void print_bbox(const std::pair<std::vector<T>,std::vector<T>> &box, bool suppress_newline = false) {
    std::cout << "bbox: (";
    for(size_t i = 0; i < box.first.size(); i++) {
        std::cout << box.first[i];
        if(i != box.first.size()-1) {
            std::cout << ", ";
        }
    }
    std::cout << ")-(";
    for(size_t i = 0; i < box.second.size(); i++) {
        std::cout << box.second[i];
        if(i != box.second.size()-1) {
            std::cout << ", ";
        }
    }
    std::cout << ")";
    if(!suppress_newline) {
        std::cout << std::endl;
    }
}


void print_db_entry(const db_entry &entry) {
    std::cout << "\t\tentry - long0: " << entry.long0 << ", string0: " << entry.string0 << ", string1: " << entry.string1;
    std::cout << ", short0: " << entry.short0 << ", string2: " << entry.string2;
    print_bbox(entry.bb);   

}

void print_db_entries(const std::vector<db_entry> &entries) {
    for(const db_entry &entry : entries) {
        print_db_entry(entry);
    }
    cout << endl;
}


int retrieve_matching_entries(isc_stmt_handle *stmt, XSQLDA *stmt_input_params, vector<db_entry> &entries) {
    int rc;
    ISC_STATUS_ARRAY status; /* status vector */
    isc_tr_handle trans = NULL; /* transaction handle */

    size_t id;
    db_entry entry;
    entry.bb.first.resize(NUM_DIMS);
    entry.bb.second.resize(NUM_DIMS);

    sql_varchar string0_buffer;
    sql_varchar string1_buffer;
    sql_varchar string2_buffer;

    short is_null[num_cols];
    XSQLDA *stmt_output_params = init_stmt_output_params(num_cols);
    rc = isc_dsql_describe(status, stmt, USE_XSQLDA, stmt_output_params);
    ASSERT(rc == RC_OK, isc_print_status(status));

    int i  = 0;

    bind_long(&stmt_output_params->sqlvar[i], i, &id, &is_null[i]);
    bind_varchar(&stmt_output_params->sqlvar[i], i, &string0_buffer, &is_null[i]);
    bind_long(&stmt_output_params->sqlvar[i], i, &entry.long0, &is_null[i]);
    bind_varchar(&stmt_output_params->sqlvar[i], i, &string1_buffer, &is_null[i]);
    bind_spatial(stmt_output_params, i, entry, is_null);
    bind_tiny(&stmt_output_params->sqlvar[i], i, &entry.short0, &is_null[i]);
    bind_varchar(&stmt_output_params->sqlvar[i], i, &string2_buffer, &is_null[i]);

    rc = isc_start_transaction(status, &trans, DB_HANDLE_COUNT, &db, 0, NULL);
    ASSERT(rc == RC_OK, isc_print_status(status));

    rc = isc_dsql_execute(status, &trans, stmt, USE_XSQLDA, stmt_input_params);
    ASSERT(rc == RC_OK, isc_print_status(status));

    while (!isc_dsql_fetch(status, stmt, USE_XSQLDA, stmt_output_params)) {

        entry.string0 = string0_buffer.data;
        entry.string1 = string1_buffer.data;
        entry.string2 = string2_buffer.data;

        entries.push_back(entry);
    }
    rc = isc_dsql_free_statement (status, stmt, DSQL_close);
    ASSERT(rc == RC_OK, isc_print_status(status));

    rc = isc_commit_transaction(status, &trans);
    ASSERT(rc == RC_OK, isc_print_status(status));

    cout << "number of results: " << entries.size() << endl;
    print_db_entries(entries);

    return rc;
}


void print_query_results(const string &query) {
    int rc;
    vector<db_entry> entries;
    ISC_STATUS_ARRAY status; /* status vector */
    isc_tr_handle trans = NULL; /* transaction handle */
    isc_stmt_handle stmt = NULL;
    isc_dsql_allocate_statement(status, &db, &stmt);    
    rc = isc_dsql_prepare(status, &trans, &stmt, 0, query.c_str(), SQL_DIALECT_VERSION, NULL);
  
    rc = retrieve_matching_entries(&stmt, NULL, entries);

    cout << "db entries:" << endl;
    print_db_entries(entries);
}

void find_matching() {
    cout << "about to find_matching" << endl;
    string query;
    query = "select * from my_table";
    print_query_results(query);
}
  
int main() {
    string string0_buffer0 = "string0_buffer0";
    string string0_buffer1 = "string0_buffer1";

    string string1_buffer0 = "string1_buffer0";
    string string1_buffer1 = "string1_buffer1";
    string string1_buffer2 = "string1_buffer2";
    string string1_buffer3 = "string1_buffer3";

    string string2_buffer0 = "I discovered made a very interesting observation that XXXXXX";
    string string2_buffer1 = "7830992237.586059";
    string string2_buffer2 = "2777747108.031878";
    string string2_buffer3 = "5134009101.956155";
    string string2_buffer4 = "6069688762.570586";
    string string2_buffer5 = "4009443942.461835";

    size_t long0 = 0, long1 = 1;
    double dim0_min = 1.2, dim0_max = 2.3, dim1_min = 3.4, dim1_max = 4.5, dim2_min = 5.6, dim2_max = 6.7;
    unsigned short short0 = 0, short1 = 1;

    db_entry entry0(string0_buffer0, long0, string1_buffer0, bbox(point({dim0_min, dim0_max, dim1_min}), point({dim1_max, dim2_min, dim2_max})), short0, string2_buffer1);
    db_entry entry1(string0_buffer1, long0, string1_buffer0, bbox(point({dim0_min, dim0_max, dim1_min}), point({dim1_max, dim2_min, dim2_max})), short1, string2_buffer0);
    db_entry entry2(string0_buffer0, long0, string1_buffer1, bbox(point({dim0_min, dim0_max, dim1_min}), point({dim1_max, dim2_min, dim2_max})), short0, string2_buffer2);
    db_entry entry3(string0_buffer0, long0, string1_buffer2, bbox(point({dim0_min, dim0_max, dim1_min}), point({dim1_max, dim2_min, dim2_max})), short0, string2_buffer3);
    db_entry entry4(string0_buffer1, long0, string1_buffer2, bbox(point({dim0_min, dim0_max, dim1_min}), point({dim1_max, dim2_min, dim2_max})), short1, string2_buffer0);
    db_entry entry5(string0_buffer0, long0, string1_buffer3, bbox(point({dim0_min, dim0_max, dim1_min}), point({dim1_max, dim2_min, dim2_max})), short0, string2_buffer4);
    db_entry entry6(string0_buffer0, long1, string1_buffer0, bbox(point({dim0_min, dim0_max, dim1_min}), point({dim1_max, dim2_min, dim2_max})), short0, string2_buffer5);

    vector<db_entry> all_entries = {entry0, entry1, entry2, entry3, entry4, entry5, entry6};

    initialize_db();
    create_table();
    prepare_statements();
    perform_write(all_entries);

    find_matching();

}

#######################################################################################

Using tracing, I can confirm that it thinks I am writing zero for each smallint (short0) entry:

#######################################################################################

#######################################################################################

Statement 203:
-------------------------------------------------------------------------------
insert into my_table (string0, long0, string1, d0_min, d0_max, d1_min, d1_max, d2_min, d2_max, short0, string2) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

param0 = varchar(50), "string0_buffer0"
param1 = bigint, "0"
param2 = varchar(50), "string1_buffer0"
param3 = double precision, "1.2"
param4 = double precision, "4.5"
param5 = double precision, "2.3"
param6 = double precision, "5.6"
param7 = double precision, "3.4"
param8 = double precision, "6.7"
param9 = smallint, "0"
param10 = varchar(256), "7830992237.586059"


Statement 203:
-------------------------------------------------------------------------------
insert into my_table (string0, long0, string1, d0_min, d0_max, d1_min, d1_max, d2_min, d2_max, short0, string2) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

param0 = varchar(50), "string0_buffer1"
param1 = bigint, "0"
param2 = varchar(50), "string1_buffer0"
param3 = double precision, "1.2"
param4 = double precision, "4.5"
param5 = double precision, "2.3"
param6 = double precision, "5.6"
param7 = double precision, "3.4"
param8 = double precision, "6.7"
param9 = smallint, "0"
param10 = varchar(256), "I discovered made a very interesting observation that XXXXXX"


Statement 203:
-------------------------------------------------------------------------------
insert into my_table (string0, long0, string1, d0_min, d0_max, d1_min, d1_max, d2_min, d2_max, short0, string2) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

param0 = varchar(50), "string0_buffer0"
param1 = bigint, "0"
param2 = varchar(50), "string1_buffer1"
param3 = double precision, "1.2"
param4 = double precision, "4.5"
param5 = double precision, "2.3"
param6 = double precision, "5.6"
param7 = double precision, "3.4"
param8 = double precision, "6.7"
param9 = smallint, "0"
param10 = varchar(256), "2777747108.031878"


Statement 203:
-------------------------------------------------------------------------------
insert into my_table (string0, long0, string1, d0_min, d0_max, d1_min, d1_max, d2_min, d2_max, short0, string2) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

param0 = varchar(50), "string0_buffer0"
param1 = bigint, "0"
param2 = varchar(50), "string1_buffer2"
param3 = double precision, "1.2"
param4 = double precision, "4.5"
param5 = double precision, "2.3"
param6 = double precision, "5.6"
param7 = double precision, "3.4"
param8 = double precision, "6.7"
param9 = smallint, "0"
param10 = varchar(256), "5134009101.956155"


Statement 203:
-------------------------------------------------------------------------------
insert into my_table (string0, long0, string1, d0_min, d0_max, d1_min, d1_max, d2_min, d2_max, short0, string2) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

param0 = varchar(50), "string0_buffer1"
param1 = bigint, "0"
param2 = varchar(50), "string1_buffer2"
param3 = double precision, "1.2"
param4 = double precision, "4.5"
param5 = double precision, "2.3"
param6 = double precision, "5.6"
param7 = double precision, "3.4"
param8 = double precision, "6.7"
param9 = smallint, "0"
param10 = varchar(256), "I discovered made a very interesting observation that XXXXXX"


Statement 203:
-------------------------------------------------------------------------------
insert into my_table (string0, long0, string1, d0_min, d0_max, d1_min, d1_max, d2_min, d2_max, short0, string2) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

param0 = varchar(50), "string0_buffer0"
param1 = bigint, "0"
param2 = varchar(50), "string1_buffer3"
param3 = double precision, "1.2"
param4 = double precision, "4.5"
param5 = double precision, "2.3"
param6 = double precision, "5.6"
param7 = double precision, "3.4"
param8 = double precision, "6.7"
param9 = smallint, "0"
param10 = varchar(256), "6069688762.570586"


Statement 203:
-------------------------------------------------------------------------------
insert into my_table (string0, long0, string1, d0_min, d0_max, d1_min, d1_max, d2_min, d2_max, short0, string2) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

param0 = varchar(50), "string0_buffer0"
param1 = bigint, "1"
param2 = varchar(50), "string1_buffer0"
param3 = double precision, "1.2"
param4 = double precision, "4.5"
param5 = double precision, "2.3"
param6 = double precision, "5.6"
param7 = double precision, "3.4"
param8 = double precision, "6.7"
param9 = smallint, "0"
param10 = varchar(256), "4009443942.461835"

#######################################################################################

#######################################################################################

Oddly, if I write fewer columns, the smallint value appears properly. This makes me think there is (still) something wrong my XSQLDA values despite the fact that the other columns are storing and returning the proper values. Any suggestions on how to proceed? I have turned on almost all of the trace functionality, so I am not sure if there is more to be gleaned from that.

Thanks again!

[Dimitry Sibiryakov]

06.04.2021 08:43, Margaret Lawson wrote:
> This makes me think there is (still) something wrong my XSQLDA values despite the fact
> that the other columns are storing and returning the proper values. Any suggestions on how
> to proceed?

Once again: every variable must be initialized. Ever. In this case - sqlscale at least.

--
WBR, SD.