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..
[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]
