How to connect MySQL5 via ODBC by C programming .

3 Days for trying to fix this problem.. Last week, I needed to write a C program to connect the MySQL database via ODBC. The big problem is in part of function “SQLDriverConnect” because there are many arguments that I must put truly. Finally, I can find an exactly argument … let me explain.. now.


[ad#ad-post-1]
First of all, we should know the importance function which we should create/use before:

  • SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env) : We need to allocate an environment handle.
  • SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0) : We need to specific version of ODBC. Today is version 3.
  • SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc) : We need to allocate the connection for communication between our program (that we are developing) and MySQL.
  • SQLDriverConnect : Start to create the connection (I FOUND THE BIG PROBLEM HERE!!!)

Let me describe my environment before we go to the part of C programming.

  • Windows XP Service pack 2 (Of course, I installed it on VirtualBox 3.2.6 r63112.)
  • MySQL version “mysql Ver 14.14 Distrib 5.1.48, for Win32 (ia32)” — You can check your MySQL version from command line “mysql –version”
  • MySQL connector (We need it because it has ODBC) version “v5.01.0006
  • Microsoft visual studio 2008 (version 9)

After you install all of software in your computer you should create the “Data Source” first. It is very easy to do that you can see many tutorial in WWW or click here. In my this project, my DSN is “DSNMySQL”. (You will see this “string” in my source code below.)

Next…I wish to show at the first of step even how to create a project for C programming in Visual Studio C++. Let go —

  1. File -> New -> Project -> Visual C++ -> CLR Empty project -> Enter (Don’t forget to put your name project — Up to you 😛 )
  2. Create a new source code inside “Source code” directory.. (Right click on there first!!)
  3. Visual C++ – > Code -> C++ File (.cpp) BUT please put your source by .C extension.

Put your this 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;

 /* 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");
 printf("Returned connection string was:\n\t%ls\n", outstr);
 if (ret == SQL_SUCCESS_WITH_INFO) {
 printf("Driver reported the following diagnostics\n");
 extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
 }
 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
){};

Then try to compile.. (Right click on your current source code then you will see command for compiling.) I think you should found error like below:

------ Build started: Project: CODBCMySQL, Configuration: Debug Win32 ------
Compiling...
cl : Command line error D8045 : cannot compile C file '.\CODBCMySQL.c' with the /clr option

Fix it by clicking on your project in “Solution Explorer” -> Property -> Configuration Properties -> General -> Common Language Runtime Support -> Select “No Common Language Runtime support” and try to compile again, sure you can compile BUT when you click “Start debugging” (F5) then you should be found below error :

------ Build started: Project: CODBCMySQL, Configuration: Debug Win32 ------
Linking...
CODBCMySQL.obj : error LNK2019: unresolved external symbol _SQLFreeHandle@8 referenced in function _main
CODBCMySQL.obj : error LNK2019: unresolved external symbol _SQLDisconnect@4 referenced in function _main
CODBCMySQL.obj : error LNK2019: unresolved external symbol _SQLDriverConnectW@32 referenced in function _main
CODBCMySQL.obj : error LNK2019: unresolved external symbol _SQLSetEnvAttr@16 referenced in function _main
CODBCMySQL.obj : error LNK2019: unresolved external symbol _SQLAllocHandle@12 referenced in function _main

Surely, we can fix them.. clicking on your project in “Solution Explorer” -> Property -> Configuration Properties -> Linker -> Input -> Addition Dependencies, then put below libraries (Don’t ask me, i don’t know why? — I think our program needs them..)

kernel32.lib
user32.lib
gdi32.lib
winspool.lib
comdlg32.lib
advapi32.lib
shell32.lib
ole32.lib
oleaut32.lib
uuid.lib
odbc32.lib
odbccp32.lib

OK… Try to “Start debugging” again. YES you can compile your program.

THE VERY IMPORTANCE for connecting to MySQL database:

1. In part of function “SQLDriverConnect”  DON’T FORGET to put “L” before your “DSN” string.

(SQLWCHAR *)L"DSN=DSNMySQL"

2. You NEED to set “default-character-set=utf8” to your MySQL database, you can set by command line. Go to command line and type :

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqladmin  --default-character-set=utf8

Have fun in C programming but for me is enough … 😛

[ad#post-image]

Special thanks :

http://www.easysoft.com/developer/languages/c/odbc_tutorial.html

http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-dsn-windows.html

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q294169

http://support.unify.com/supportforum/viewtopic.php?f=20&t=2497

http://lists.mysql.com/myodbc/11184