Array Items Update and Retrieval using ODBC

Array types and, in particular integer arrays are not supported by the ODBC standard. The eXtremeDB allows array types declared in the schema. Therfore passing array through the ODBC interface is sometimes beneficial. The eXtremeDB SQL engine provides the ability to parse integer array assisting in the the following workaround. First the array ought to be defined as a string. For example:

"[2,4,6,8]"
   

The string is then parsed by the SQL engine into an actual array.

In the following code snippet the array_val value is printed into the int8_arr_str character array and this C string is used for an update (error checking and other validations are omitted for clarity)

class Person {
    char<64> name;
    int4     age;
    float    weight;
    int8     array_val[10];

    tree<name> by_name;
};
   

  char int8_arr_str[MAX_INT8_ARRAY_STR_LENGTH];

//.... 



// Update record
void updatePersonData(Person* p)
{
    SQLHSTMT hStmt;

    SQLAllocStmt(hDbc, &hStmt)); // allocate statement

    SQLPrepare(hStmt, (SQLCHAR*)"update Person set age=?, weight=?, array_val=? where name=?", SQL_NTS); // perpare query


    // Bind parameters
    SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &p->age, 0, NULL);
    SQLBindParameter(hStmt, 2, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_REAL, 0, 0, &p->weight, 0, NULL);

    // PASSING ARRAY AS A STRING -------------
    SQLBindParameter(hStmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0, (SQLPOINTER)p->int8_arr_str, 0, NULL);

    SQLBindParameter(hStmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0, (SQLPOINTER)p->name, 0, NULL);

    SQLExecute(hStmt); // execute prepared statement
    SQLFreeStmt(hStmt, SQL_DROP); // drop statement
}

//...


    p.name = "John Smith";
    p.age = 36;
    p.weight = 75.2f;

    c = p.int8_arr_str;
    *c = '\0';
    c += sprintf(c, "[");
    for (int i = 0; i < 10; i++) {
        c += sprintf(c, "%lld", 100LL + i);
        if (i != 9)
            c += sprintf(c, ",");
    }
    c += sprintf(c, "]");

    updatePersonData(&p);