C
1. Prerequisites
The SQL interface in LeanXcale is exposed to the C language through our ODBC connector, so you’ll need to install it first. Please, refer to the documentation to install the ODBC connector for Windows or Linux and then come back here to continue.
2. Quick Start
This section includes a set of code snippets to connect, read, insert, and update information through the LeanXcale ODBC Driver. For more information about ODBC functions, please refer to the ODBC doc.
2.1. Connect and start a session
Connecting to the SQL interface requires some basic information:
-
If you use LeanXcale as a Cloud Service, then you can find the IP or hostname for your instance in the LeanXcale Cloud Console.
-
If you use LeanXcale on-premise, then provide the information from your installation.
In our examples, we include these parameters:
-
IP / hostname:
123.45.67.89 -
Port:
1529 -
DB name:
db -
Username:
APP -
Password:
APP
Connecting to LeanXcale requires the creation of an environment handle. From this environment, you create as many connections as you need. Several connection attributes must be set before connecting.
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
SQLAllocHandle(SQL_HANDLE_DBC, env, &conn);
/* Disable autocommit */
SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_OFF,
0);
rc = SQLDriverConnect(
conn, NULL,
"DSN=LeanXcale;USERNAME=APP;PASSWORD=APP;"
"TRACE=debug;Host=123.45.67.89;"
"Database=db;Port=1529;ENCODING=WCHAR_T;",
SQL_NTS, str, sizeof(str), &strl, SQL_DRIVER_COMPLETE);
CHECK_CONN_RESULT(rc, "SQLDriverConnect failed", conn);Keep in mind that if you are connecting to a LeanXcale database working with security, you will need some changes in your ODBC configuration. Check them here.
In the next examples, the connection details are not included but are still needed for any application session. You create statements from the connection handle to interact with the data, and these queries should be supported by Leanxcale’s SQL dialect.
2.2. Create a table with indexes
To create a new table in LeanXcale, run the following code:
rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
CHECK_CONN_RESULT(rc, "Failed to allocate stmt handle", conn);
rc = SQLExecDirect(hstmt,
"CREATE TABLE person (personId BIGINT AS IDENTITY START "
"WITH 1 INCREMENT BY 1"
", dni VARCHAR, name VARCHAR, lastName BINARY, address "
"VARCHAR, phone VARCHAR, email VARCHAR"
", comment VARCHAR, birthday date, weight float, height "
"float, primary key(personId))",
SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed while creating table", hstmt);
// Non unique index
rc = SQLExecDirect(hstmt, "CREATE INDEX personixbd ON person(birthday)", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed while creating index", hstmt);
rc = SQLExecDirect(hstmt, "CREATE UNIQUE INDEX personixdni ON person(dni)", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed while creating index", hstmt);2.3. Insert, update and delete
2.3.1. Inserting multiple Records using a prepare statement
rc = SQLPrepare(hstmt, (SQLCHAR *) "INSERT INTO person VALUES (?,?,?"
",'UNKNOWN',?,?,'no comments',?,?,?)", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLPrepare failed", hstmt);
char dni[50];
wchar_t name[50];
SQLLEN namesz;
wchar_t lastName[50];
SQLLEN lastnamesz;
char phone[50];
char email[50];
long birthday;
float weight;
float height;
long refdate = 946684800;
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, /* value type */
SQL_CHAR, /* param type */
50, /* column size */
0, /* dec digits */
dni, /* param value ptr */
50, /* buffer len */
0 /* StrLen_or_IndPtr */);
CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_WCHAR, /* value type */
SQL_CHAR, /* param type */
50, /* column size */
0, /* dec digits */
name, /* param value ptr */
sizeof(name), /* buffer len */
&namesz /* StrLen_or_IndPtr */);
CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);
rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_WCHAR, /* value type */
SQL_BINARY, /* param type */
50, /* column size */
0, /* dec digits */
lastName, /* param value ptr */
sizeof(lastName), /* buffer len */
&lastnamesz /* StrLen_or_IndPtr */);
CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);
rc = SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR, /* value type */
SQL_CHAR, /* param type */
50, /* column size */
0, /* dec digits */
phone, /* param value ptr */
50, /* buffer len */
0 /* StrLen_or_IndPtr */);
CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);
rc = SQLBindParameter(hstmt, 5, SQL_PARAM_INPUT, SQL_C_CHAR, /* value type */
SQL_CHAR, /* param type */
50, /* column size */
0, /* dec digits */
email, /* param value ptr */
50, /* buffer len */
0 /* StrLen_or_IndPtr */);
CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);
rc = SQLBindParameter(hstmt, 6, SQL_PARAM_INPUT, SQL_C_LONG, /* value type */
SQL_DATE, /* param type */
0, /* column size */
0, /* dec digits */
&birthday, /* param value ptr */
0, /* buffer len */
0 /* StrLen_or_IndPtr */);
CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);
rc = SQLBindParameter(hstmt, 7, SQL_PARAM_INPUT, SQL_C_FLOAT, /* value type */
SQL_FLOAT, /* param type */
0, /* column size */
0, /* dec digits */
&weight, /* param value ptr */
0, /* buffer len */
0 /* StrLen_or_IndPtr */);
CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);
rc = SQLBindParameter(hstmt, 8, SQL_PARAM_INPUT, SQL_C_FLOAT, /* value type */
SQL_FLOAT, /* param type */
0, /* column size */
0, /* dec digits */
&height, /* param value ptr */
0, /* buffer len */
0 /* StrLen_or_IndPtr */);
CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);
for (size_t inx = 0; inx < 10000; inx++) {
snprintf(dni, 49, "%zd", inx);
swprintf(name, 49, L"%zd", inx % 1000);
namesz = (wcslen(name) + 1) * sizeof name[0];
swprintf(lastName, 49, L"%zd", inx % 3000);
lastnamesz = (wcslen(lastName) + 1) * sizeof lastName[0];
snprintf(phone, 49, "%zd-%zd", inx, inx % 1000);
snprintf(email, 49, "%zd@lx.com", inx % 1000);
birthday = refdate + ((inx % 3000) * 86400);
weight = (float)refdate / (float)(1 + (inx % 30000));
height = (float)refdate / (float)(1 + (inx % 90000));
rc = SQLExecute(hstmt);
CHECK_STMT_RESULT(rc, "SQLExecute failed", hstmt);
}
rc = SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT);
CHECK_CONN_RESULT(rc, "Failed to commit", conn);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
rc = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
CHECK_STMT_RESULT(rc, "SQLFreeHandle failed", hstmt);2.3.2. Inserting using a Sequence
rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
CHECK_CONN_RESULT(rc, "Failed to allocate stmt handle", conn);
rc = SQLExecDirect(
hstmt, "CREATE SEQUENCE seq START WITH 10001 increment by 1", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
rc = SQLExecDirect(
hstmt,
"INSERT INTO person(personId, dni, name, lastName, address, phone, "
"email, comment, birthday, weight, height) VALUES (NEXT VALUE FOR seq, "
"'123456789A','John',X'3537','Mulholland "
"Drive','0034698765432','johndoe@lx.com','secret',DATE "
"'1963-03-14','70','179')",
SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);2.3.3. Updating a Record by the key
rc = SQLExecDirect(
hstmt, "UPDATE person SET comment = NULL WHERE dni = '123456789A'", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);2.3.4. Deleting a Record by the key
rc = SQLExecDirect(hstmt, "DELETE FROM person WHERE dni = '123456789A'", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);2.3.5. Deleting a Record with a Filter
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
rc = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
rc = SQLExecDirect(hstmt, "DELETE FROM person WHERE dni = '1000'", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
rc = SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT);
CHECK_CONN_RESULT(rc, "Failed to commit", conn);
rc = SQLExecDirect(hstmt,
"DELETE FROM person WHERE (weight/power(height,2)) > 26.0",
SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
rc = SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT);
CHECK_CONN_RESULT(rc, "Failed to commit", conn);2.4. Read and scan Data
2.4.1. Selecting with a Group By clause
rc = SQLExecDirect(hstmt,
"SELECT name, count(*) as rep FROM person"
" WHERE name = '123' GROUP BY name ORDER BY rep",
SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
rc = SQLNumResultCols(hstmt, &numcols);
CHECK_STMT_RESULT(rc, "SQLNumResultCols failed", hstmt);
ASSERTEQUAL(numcols, 2);
rc = SQLFetch(hstmt);
CHECK_STMT_RESULT(rc, "SQLFetch failed", hstmt);
rc = SQLGetData(hstmt, 1, SQL_C_CHAR, data, sizeof(data), 0);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
printf("Basic scan name: %s\n", data);
ASSERTSTREQUAL(data, "123");
long ldata = 0;
rc = SQLGetData(hstmt, 2, SQL_C_LONG, &ldata, sizeof(ldata), 0);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
printf("Basic scan count: %ld\n", ldata);
ASSERTEQUAL(ldata, 10);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);2.4.2. Selecting by joining two tables
rc = SQLExecDirect(
hstmt,
"SELECT a.dni, b.dni, a.birthday, a.lastname FROM person a JOIN "
"person b ON a.birthday = b.birthday WHERE a.personid <> "
"b.personid AND a.dni = '123'",
SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
rc = SQLFetch(hstmt);
CHECK_STMT_RESULT(rc, "SQLFetch failed", hstmt);
rc = SQLGetData(hstmt, 1, SQL_C_CHAR, data, sizeof(data), 0);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
printf("Join scan dni: %s\n", data);
ASSERTSTREQUAL(data, "123");
rc = SQLGetData(hstmt, 2, SQL_C_CHAR, data, sizeof(data), 0);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
printf("Join scan dni: %s\n", data);
ASSERTSTREQUAL(data, "3123");
SQL_DATE_STRUCT cdate;
rc = SQLGetData(hstmt, 3, SQL_C_DATE, &cdate, sizeof(cdate), 0);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
printf("Join scan birthday: %d-%d-%d\n", cdate.year, cdate.month, cdate.day);
ASSERTEQUAL(cdate.day, 3);
ASSERTEQUAL(cdate.month, 5);
ASSERTEQUAL(cdate.year, 2000);
wchar_t wstr[50];
rc = SQLGetData(hstmt, 4, SQL_C_WCHAR, &wstr, sizeof(wstr), 0);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
printf("Join scan last name: %ls\n", wstr);
ASSERTWSTREQUAL(wstr, L"123");
rc = SQLFetch(hstmt);
CHECK_STMT_RESULT(rc, "SQLFetch failed", hstmt);
rc = SQLGetData(hstmt, 2, SQL_C_CHAR, data, sizeof(data), 0);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
printf("Join scan dni: %s\n", data);
ASSERTSTREQUAL(data, "6123");
rc = SQLFetch(hstmt);
CHECK_STMT_RESULT(rc, "SQLFetch failed", hstmt);
rc = SQLGetData(hstmt, 2, SQL_C_CHAR, data, sizeof(data), 0);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
printf("Join scan dni: %s\n", data);
ASSERTSTREQUAL(data, "9123");
rc = SQLFetch(hstmt);
ASSERTEQUAL(rc, SQL_NO_DATA);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);2.5. Arrays
2.5.1. Create a table with array fileds
The following example shows how to create a table with arrays columns.
SQLRETURN rc;
HSTMT hstmt = SQL_NULL_HSTMT;
test_connect();
rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
if (!SQL_SUCCEEDED(rc)) {
print_diag(__LINE__, "failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
exit(1);
} CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
rc = SQLExecDirect(hstmt,
(SQLCHAR *) "CREATE TABLE tarry (id INT, longs BIGINT ARRAY,"
" doubles DOUBLE ARRAY, strings VARCHAR ARRAY,"
" PRIMARY KEY(id))", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);2.5.2. Insert, update and delete
We can insert multiple records using a prepare statement. This way, we can insert array rows with a prepare statement.
rc = SQLPrepare(hstmt, (SQLCHAR *)"insert into tarry values(?, ?, ?, ?)", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLPrepare failed", hstmt);
int id = 1;
long longs[] = {1L, 2000L, 4L};
char *strings = malloc(13 * sizeof(char));
strcpy(&strings[0], "1.3");
strings[3] = '\0';
strcpy(&strings[4], "20.57");
strings[9] = '\0';
strcpy(&strings[10], "4.8");
double *doubles = malloc(3 * sizeof(doubles[0]));
doubles[0] = 1.3;
doubles[1] = 20.23;
doubles[2] = 4.2;
SQLLEN cbParam1 = 0;
SQLLEN cbParam2 = sizeof longs;
SQLLEN cbParam3 = 3 * sizeof(doubles[0]);
SQLLEN cbParam4 = 13 * sizeof(char);
rc = SQLBindParameter(
hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, /* value type */
SQL_INTEGER, /* param type */
0, /* column size (ignored for SQL_INTEGER) */
0, /* dec digits (ignored for SQL_INTEGER) */
&id, /* param value ptr */
3, /* buffer len (ignored for SQL_INTEGER) */
&cbParam1 /* StrLen_or_IndPtr (ignored for SQL_INTEGER) */);
CHECK_STMT_RESULT(rc, "\nSQLBindParameter failed", hstmt);
rc = SQLBindParameter(
hstmt, 2, SQL_PARAM_INPUT, SQL_C_BINARY, /* value type */
2104, /* param type */
0, /* column size (ignored for Array type) */
0, /* dec digits (ignored for Array type) */
&longs, /* param value ptr */
sizeof longs, /* buffer len */
&cbParam2 /* StrLen_or_IndPtr (ignored for Array type) */);
CHECK_STMT_RESULT(rc, "\nSQLBindParameter failed", hstmt);
rc = SQLBindParameter(
hstmt, 3, SQL_PARAM_INPUT, SQL_C_BINARY, /* value type */
2108, /* param type */
0, /* column size (ignored for Array type) */
0, /* dec digits (ignored for Array type) */
doubles, /* param value ptr */
cbParam3, /* buffer len */
&cbParam3 /* StrLen_or_IndPtr (ignored for Array type) */);
CHECK_STMT_RESULT(rc, "\nSQLBindParameter failed", hstmt);
rc = SQLBindParameter(
hstmt, 4, SQL_PARAM_INPUT, SQL_C_BINARY, /* value type */
2112, /* param type */
5, /* column size (ignored for Array type) */
0, /* dec digits (ignored for Array type) */
strings, /* param value ptr */
cbParam4, /* buffer len */
&cbParam4 /* StrLen_or_IndPtr (ignored for Array type) */);
CHECK_STMT_RESULT(rc, "\nSQLBindParameter failed", hstmt);
// Execute statement
rc = SQLExecute(hstmt);
CHECK_STMT_RESULT(rc, "\nSQLExecute failed", hstmt);In the following example, we show how to insert an empty values in arrays.
id = 2;
cbParam2 = 0;
cbParam3 = 0;
cbParam4 = 0;
rc = SQLBindParameter(
hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, /* value type */
SQL_INTEGER, /* param type */
0, /* column size (ignored for SQL_INTEGER) */
0, /* dec digits (ignored for SQL_INTEGER) */
&id, /* param value ptr */
3, /* buffer len (ignored for SQL_INTEGER) */
&cbParam1 /* StrLen_or_IndPtr (ignored for SQL_INTEGER) */);
CHECK_STMT_RESULT(rc, "\nSQLBindParameter failed", hstmt);
rc = SQLBindParameter(
hstmt, 2, SQL_PARAM_INPUT, SQL_C_BINARY, /* value type */
2104, /* param type */
0, /* column size (ignored for Array type) */
0, /* dec digits (ignored for Array type) */
&longs, /* param value ptr */
0, /* buffer len */
&cbParam2 /* StrLen_or_IndPtr (ignored for Array type) */);
CHECK_STMT_RESULT(rc, "\nSQLBindParameter failed", hstmt);
rc = SQLBindParameter(
hstmt, 3, SQL_PARAM_INPUT, SQL_C_BINARY, /* value type */
2108, /* param type */
0, /* column size (ignored for Array type) */
0, /* dec digits (ignored for Array type) */
doubles, /* param value ptr */
cbParam3, /* buffer len */
&cbParam3 /* StrLen_or_IndPtr (ignored for Array type) */);
CHECK_STMT_RESULT(rc, "\nSQLBindParameter failed", hstmt);
rc = SQLBindParameter(
hstmt, 4, SQL_PARAM_INPUT, SQL_C_BINARY, /* value type */
2112, /* param type */
5, /* column size (ignored for Array type) */
0, /* dec digits (ignored for Array type) */
strings, /* param value ptr */
cbParam4, /* buffer len */
&cbParam4 /* StrLen_or_IndPtr (ignored for Array type) */);
CHECK_STMT_RESULT(rc, "\nSQLBindParameter failed", hstmt);
// Execute statement
rc = SQLExecute(hstmt);
CHECK_STMT_RESULT(rc, "\nSQLExecute failed", hstmt);We are also able to insert null objects in arrays.
id = 3;
cbParam2 = SQL_NULL_DATA;
cbParam3 = SQL_NULL_DATA;
cbParam4 = SQL_NULL_DATA;
rc = SQLBindParameter(
hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, /* value type */
SQL_INTEGER, /* param type */
0, /* column size (ignored for SQL_INTEGER) */
0, /* dec digits (ignored for SQL_INTEGER) */
&id, /* param value ptr */
3, /* buffer len (ignored for SQL_INTEGER) */
&cbParam1 /* StrLen_or_IndPtr (ignored for SQL_INTEGER) */);
CHECK_STMT_RESULT(rc, "\nSQLBindParameter failed", hstmt);
rc = SQLBindParameter(
hstmt, 2, SQL_PARAM_INPUT, SQL_C_BINARY, /* value type */
2104, /* param type */
0, /* column size (ignored for Array type) */
0, /* dec digits (ignored for Array type) */
0, /* param value ptr */
0, /* buffer len */
&cbParam2 /* StrLen_or_IndPtr (ignored for Array type) */);
CHECK_STMT_RESULT(rc, "\nSQLBindParameter failed", hstmt);
rc = SQLBindParameter(
hstmt, 3, SQL_PARAM_INPUT, SQL_C_BINARY, /* value type */
2108, /* param type */
0, /* column size (ignored for Array type) */
0, /* dec digits (ignored for Array type) */
0, /* param value ptr */
0, /* buffer len */
&cbParam3 /* StrLen_or_IndPtr (ignored for Array type) */);
CHECK_STMT_RESULT(rc, "\nSQLBindParameter failed", hstmt);
rc = SQLBindParameter(
hstmt, 4, SQL_PARAM_INPUT, SQL_C_BINARY, /* value type */
2112, /* param type */
5, /* column size (ignored for Array type) */
0, /* dec digits (ignored for Array type) */
0, /* param value ptr */
0, /* buffer len */
&cbParam4 /* StrLen_or_IndPtr (ignored for Array type) */);
CHECK_STMT_RESULT(rc, "\nSQLBindParameter failed", hstmt);
// Execute statement
rc = SQLExecute(hstmt);
CHECK_STMT_RESULT(rc, "\nSQLExecute failed", hstmt);2.5.3. Read and scan Data
The following examples show how to read data from a table with arrays columns.
// reset the variables
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
rc = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt); /* Check the inserted data */
rc = SQLExecDirect(hstmt, (SQLCHAR *)"SELECT strings, longs, doubles, strings, strings, strings, strings FROM tarry order by id", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
SQLSMALLINT numcols;
rc = SQLNumResultCols(hstmt, &numcols);
ASSERTEQUAL(numcols, 7);
rc = SQLFetch(hstmt);
CHECK_STMT_RESULT(rc, "SQLFetch failed", hstmt);
SQLLEN ind;We can check the long values.
long *dblongs = malloc(2 * sizeof(long));
rc = SQLGetData(hstmt, 2, SQL_C_BINARY, dblongs, 2 * sizeof(long), &ind);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
for (size_t inx = 0; inx < 2; inx++) {
printf("long array[%ld]: expected[%ld], actual [%ld]\n", inx, longs[inx],
dblongs[inx]);
}
dblongs = realloc(dblongs, ind);
rc = SQLGetData(hstmt, 2, SQL_C_BINARY, &dblongs[2], sizeof(long), &ind);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
printf("ind[%ld]\n", ind);
for (size_t inx = 0; inx < ind / sizeof(long); inx++) {
printf("long array[%ld]: expected[%ld], actual [%ld]\n", inx, longs[inx],
dblongs[inx]);
}
free(dblongs);We can check the double values.
double dbdoubles[4];
rc = SQLGetData(hstmt, 3, SQL_C_BINARY, dbdoubles, 4 * sizeof(double), &ind);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
for (size_t inx = 0; inx < ind / sizeof(double); inx++) {
printf("double array[%ld]: expected[%f], actual [%f]\n", inx, doubles[inx],
dbdoubles[inx]);
}We can check the string values. We can read such strings in many ways. Here are some of them.
Initial size just after reading second.
printf("1. initital size just after reading 2nd\n");
char *dbstrings = malloc(10 * sizeof(char));
rc = SQLGetData(hstmt, 1, SQL_C_BINARY, dbstrings, 10 * sizeof(char), &ind);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
dbstrings = realloc(dbstrings, ind);
rc = SQLGetData(hstmt, 1, SQL_C_BINARY, &dbstrings[10], 3 * sizeof(char),
&ind);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
for (size_t inx = 0; inx < 13; inx++) {
printf("string array[%ld]: expected[%c], actual [%c]\n", inx, strings[inx],
dbstrings[inx]);
}
free(dbstrings);Initial size just after reading 2nd but without \0.
printf("2. initital size just after reading 2nd but without '\\0'\n");
dbstrings = malloc(9 * sizeof(char));
rc = SQLGetData(hstmt, 4, SQL_C_BINARY, dbstrings, 9 * sizeof(char), &ind);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
dbstrings = realloc(dbstrings, ind);
rc = SQLGetData(hstmt, 4, SQL_C_BINARY, &dbstrings[9], 4 * sizeof(char), &ind);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
for (size_t inx = 0; inx < 13; inx++) {
printf("string array[%ld]: expected[%c], actual [%c]\n", inx, strings[inx],
dbstrings[inx]);
}
free(dbstrings);Initial size bigger than data.
printf("3. initital size bigger than data\n");
dbstrings = malloc(14 * sizeof(char));
rc = SQLGetData(hstmt, 5, SQL_C_BINARY, dbstrings, 14 * sizeof(char), &ind);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
for (size_t inx = 0; inx < 13; inx++) {
printf("string array[%ld]: expected[%c], actual [%c]\n", inx, strings[inx],
dbstrings[inx]);
}
free(dbstrings);Initial size exact as data.
printf("4. initital size exatct as data\n");
dbstrings = malloc(13 * sizeof(char));
rc = SQLGetData(hstmt, 6, SQL_C_BINARY, dbstrings, 13 * sizeof(char), &ind);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
for (size_t inx = 0; inx < 13; inx++) {
printf("string array[%ld]: expected[%c], actual [%c]\n", inx, strings[inx],
dbstrings[inx]);
}
free(dbstrings);Reading 4 by 4 bytes.
printf("5. reading 4 by 4 bytes\n");
dbstrings = malloc(4 * sizeof(char));
size_t offset = 0;
size_t nelem;
do {
rc = SQLGetData(hstmt, 7, SQL_C_BINARY, dbstrings, 4 * sizeof(char), &ind);
if (rc == SQL_ERROR) {
print_diag(__LINE__, "SQLGetData failed", SQL_HANDLE_STMT, hstmt);
exit(1);
}
if (4 * sizeof(char) > ind) {
nelem = offset + (ind / sizeof(char));
} else {
nelem = offset + 4;
}
for (size_t inx = offset; inx < nelem; inx++) {
printf("string array[%ld]: expected[%c], actual [%c]\n", inx,
strings[inx], dbstrings[inx - offset]);
}
offset = nelem;
} while (SQL_NO_DATA != rc && SQL_ERROR != rc);
free(dbstrings);We can also read empty arrays.
printf("Read empty arrays\n");
char *empty[32];
rc = SQLFetch(hstmt);
CHECK_STMT_RESULT(rc, "SQLFetch failed", hstmt);
// Check longs
rc = SQLGetData(hstmt, 2, SQL_C_BINARY, empty, 32, &ind);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
ASSERTEQUAL(ind, 0);
// Check doubles
rc = SQLGetData(hstmt, 2, SQL_C_BINARY, empty, 32, &ind);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
ASSERTEQUAL(ind, 0);
// Check strings
rc = SQLGetData(hstmt, 2, SQL_C_BINARY, empty, 32, &ind);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
ASSERTEQUAL(ind, 0);In the same way we can read null arrays.
printf("Read null arrays\n");
rc = SQLFetch(hstmt);
CHECK_STMT_RESULT(rc, "SQLFetch failed", hstmt);
// Check longs
rc = SQLGetData(hstmt, 2, SQL_C_BINARY, empty, 32, &ind);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
ASSERTEQUAL(ind, SQL_NULL_DATA);
// Check doubles
rc = SQLGetData(hstmt, 2, SQL_C_BINARY, empty, 32, &ind);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
ASSERTEQUAL(ind, SQL_NULL_DATA);
// Check strings
rc = SQLGetData(hstmt, 2, SQL_C_BINARY, empty, 32, &ind);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
ASSERTEQUAL(ind, SQL_NULL_DATA);
// Close the connection
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);Finally, we can just print the scan result as a char sequence.
rc = SQLExecDirect(hstmt, (SQLCHAR *)"SELECT * FROM tarry order by id", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
print_result(hstmt); // reset the variables
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
rc = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt); rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
if (!SQL_SUCCEEDED(rc)) {
print_diag(__LINE__, "SQLFreeStmt failed", SQL_HANDLE_STMT, hstmt);
exit(1);
}|
Ths driver implements the ODBC 3.0 behavior, so Unicode’s ODBC functions are not supported. |