How to query data from MySQL via ODBC by C programming.

From 2 previous post about C programming, MySQL and ODBC.

1. How to connect MySQL5 via ODBC by C programming .
2. How to INSERT data into MySQL via ODBC by C programming.

And today, I will show how to query and display value on screen. Many part of below source code come form those articles so you should read them before..

[ad#ad-post-1]

First, we should create “struct” variable for keeping data from database when we fetch all of data which depends on condition in SQL command. Example :

  struct {
	SQLINTEGER id;
        SQLCHAR code[3];
	SQLCHAR X[8];
	SQLCHAR Y[8];
  } row;

Second, we need to allocate memory & execute SQL command which you can see in previous post (I don’t want to show them again..:P ).

Third, after our software sends command to server, the server should return some data back and put all of data into “SQLRETURN” variable (in this case, the name of variable is a “ret”).

Now, we need to map the field of data table to “Struct” variable which was prepared already.(Now, we don’t get data from database yet, just prepare some space for data.) Like this:

// Map data from all columns with struct variable //
ret = SQLBindCol(stmt, 1, SQL_C_SLONG, &row.id, sizeof(row.id), NULL);
ret = SQLBindCol(stmt, 2, SQL_C_CHAR, &row.code, sizeof(row.code), NULL);
ret = SQLBindCol(stmt, 3, SQL_C_CHAR, &row.X, sizeof(row.X), NULL);
ret = SQLBindCol(stmt, 4, SQL_C_CHAR, &row.Y, sizeof(row.Y), NULL);

Finally, we need to fetch all of data inside the “SQLHSTMT” variable (in this example define a name is “ret”) and show them on display:

while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) {
     printf("%d|%s|%s|%s|\n", row.id, row.code, row.X, row.Y);
}

Below is example output. (You can stop for seeing output on display by using debug mode. (red ball).)

Connected
Selected OK!!. SQL was:
1|th|1234567|7654321|
2|jp|1234567|7654321|
3|ru|1234567|7654321|
4|ru|1234567|7654321|
5|ru|1234567|7654321|
6|ru|1234567|7654321|
7|ru|1234567|7654321|
8|ru|1234567|7654321|
9|ru|1234567|7654321|

And this is a full source code..

#include <windows.h>
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>

static void extract_error(
 char *fn,
 SQLHANDLE handle,
 SQLSMALLINT type);

main() {
 SQLHENV env;
 SQLHDBC dbc;
 SQLRETURN ret; /* ODBC API return status */
 SQLWCHAR outstr[1024];
 SQLSMALLINT outstrlen;    
 SQLHSTMT stmt;

 struct {
 SQLINTEGER id;
 SQLCHAR code[3];
 SQLCHAR X[8];
 SQLCHAR Y[8];
 } row;

 /* Allocate an environment handle */
 SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
 /* We want ODBC 3 support */
 SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
 /* Allocate a connection handle */
 SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
 /* Connect to the DSN mydsn */
 ret = SQLDriverConnect(dbc, NULL, (SQLWCHAR *)L"DSN=DSNMySQL", SQL_NTS, (SQLWCHAR *)outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_COMPLETE);

 if (SQL_SUCCEEDED(ret)) {
 printf("Connected\n");

 // DISPLAY DATA //
 /* Allocate a statement handle */
 ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
 if(ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
 //    char message[200];
 printf("Error Allocating Handle: %d\n", ret);
 }else {
 SQLWCHAR* SQL = L"SELECT * FROM location_country";
 ret = SQLExecDirect(stmt, SQL, SQL_NTS);
 if(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
 printf("Selected OK!!. SQL was:\n");

 // Map data from all columns with struct variable //
 ret = SQLBindCol(stmt, 1, SQL_C_SLONG, &row.id , sizeof(row.id), NULL);
 ret = SQLBindCol(stmt, 2, SQL_C_CHAR, &row.code , sizeof(row.code), NULL);
 ret = SQLBindCol(stmt, 3, SQL_C_CHAR, &row.X , sizeof(row.X), NULL);
 ret = SQLBindCol(stmt, 4, SQL_C_CHAR, &row.Y , sizeof(row.Y), NULL);
 
 while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) {
 printf("%d|%s|%s|%s|\n", row.id, row.code, row.X, row.Y);
 }
 }
 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 }

 SQLDisconnect(dbc);        /* disconnect from driver */
 } else {
 fprintf(stderr, "Failed to connect\n");
 extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
 }
 /* free up allocated handles */
 SQLFreeHandle(SQL_HANDLE_DBC, dbc);
 SQLFreeHandle(SQL_HANDLE_ENV, env);
}

static void extract_error(
 char *fn,
 SQLHANDLE handle,
 SQLSMALLINT type
){};

Ei ei … Good night. 😛

[ad#post-image]

C/C++ MySQL ODBC