From 2 previous post about C programming, MySQL and ODBC.
- How to connect MySQL5 via ODBC by C programming .
- 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..
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. 