Yesterday, we talked about “How to connect MySQL5 via ODBC by C programming.” I recommend if you are not expert in C programming please see that post firstly. Because many part of source in this post are come from that post.
Today, I need to show how to insert data after we can create a connection between our program and MySQL.
Before you go to to see my source code, I would like to show my “data table” inside MySQL database.
[ad#ad-post-1]
mysql> use zonetic;
Database changed
mysql> describe location_country;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| code | varchar(2) | YES | | NULL | |
| X | varchar(7) | YES | | NULL | |
| Y | varchar(7) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.20 sec)First of all, we need to allocate a “statement handle” so as to reserve memory for put our SQL command statement like this:
ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
Second, we need to create a SQL statement like this:
SQLWCHAR* SQL = L"INSERT INTO location_country(code, X, Y) VALUES('ru','1234567','7654321')";
DON’T FORGET to put “L” at first of SQL Command.
Third, we put our SQL statement in function”SQLExecDirect” so as to execute SQL command.
ret = SQLExecDirect(stmt, SQL, SQL_NTS);Finally, we need to free our memory after we send our command by fuction “SQLFreeHandle”
SQLFreeHandle(SQL_HANDLE_STMT, stmt);Below is my 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;
/* 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");
/* Allocate a statement handle */
ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
if(ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
printf("Error Allocating Handle: %d\n", ret);
}else {
SQLWCHAR* SQL = L"INSERT INTO location_country(code, X, Y) VALUES('jp','1234567','7654321')";
ret = SQLExecDirect(stmt, SQL, SQL_NTS);
if(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
printf("Insert data OK!!. SQL was:\n\n%s\n\n");
}
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
){};[ad#post-image]
Special thanks:
http://www.informit.com/library/content.aspx?b=Visual_C_PlusPlus&seqNum=183
http://cs.ua.edu/components/integrated/handouts/08-odbc-example.pdf
http://www.easysoft.com/developer/languages/c/odbc-tutorial-fetching-results.html
