How to INSERT data into MySQL via ODBC by C programming.

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.

mysql&gt; use zonetic;
Database changed
mysql&gt; 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, &amp;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 &lt;windows.h&gt;
#include &lt;stdio.h&gt;
#include &lt;sql.h&gt;
#include &lt;sqlext.h&gt;
 
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, &amp;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, &amp;dbc);
 /* Connect to the DSN mydsn */
 ret = SQLDriverConnect(dbc, NULL, (SQLWCHAR *)L"DSN=DSNMySQL", SQL_NTS, (SQLWCHAR *)outstr, sizeof(outstr), &amp;outstrlen, SQL_DRIVER_COMPLETE);
 
 if (SQL_SUCCEEDED(ret)) {
 printf("Connected\n");
 
 /* Allocate a statement handle */
 ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &amp;stmt);
 
 if(ret != SQL_SUCCESS &amp;&amp; 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
){};