UnixODBC and MySQL Sample Program

Context

C (or C++) and DBMS application programming.

Using ODBC (rather than direct connectivity)

Components

• C language – gcc 3.4.6
• OS – Red Hat Enterprise Linux ES release 4 (Nahant Update 7)
• UnixODBC – 2.2.14 (An opensource ODBC driver manager)
• MySQL ODBC driver

Downloads

MySQL ODBC Driver

From

http://www.mysql.com/downloads/

Look for MySQL Connectors-> Connector/ODBC

Unpack   *.tar.gz

gunzip <file_name>.tar.gz. Generates filename <file_name>.tar

tar –xf <file_name>.tar. Generates the complete directory structure.

File name format (for MySQL ODBC driver)

libmyodbc5.so (the location of this file is used in setup)

ODBC Driver (may come standard with Linux distribution, try locate libodbc.so, if not)

From

http://www.unixodbc.org/

Library (after unpacking and building the package through configure & make)

libodbc.so (Used for linking of sample program)

Setup UnixODBC

File $HOME/.odbc.ini

Changes

Add a new ODBC Instance in file (create the file if it does not exists).

.odbc.ini (under $HOME)

Template	Example
[<ODBC_Instance_Name>]	[MySQL_Test]
Driver            = <Database_ODBC_Driver>	Driver            = /opt/driver/mysql/mysqlodbc5.so
DATABASE   = <MySQL_Database_Name>	DATABASE   = temp
USER              = <DB_Username>	USER              = scott
PASSWORD  = <DB_Password>	PASSWORD  = tiger
SOCKET         = <Location of mysql.sock file>	SOCKET         = /var/lib/mysql/mysql.sock

Testing the setup

Run isql -v <ODBC_Instance_Name> i.e.

isql -v MySQL_Test

If successful one should see,

[root@localhost ~]$ isql -v MySQL

+—————————————+

| Connected!                                     |

|                                                             |

| sql-statement                                |

| help [tablename]                         |

| quit                                                    |

|                                                             |

+—————————————+

SQL>

At which stage it allows all MySQL’s commands.

If the above prompt is not seen the program may not be successful.

Sample Program

Uses

sql.h && sqlext.h (part of UnixODBC distribution)

Key steps (also abstractions)

ODBC Environment allocation & setting ODBC version.

DBC (Database Connection) allocation & connection

Code

(Error handling code removed)

#include <stdio.h>

#include <sql.h>
#include <sqlext.h>

int main() {

	SQLHENV env;
	SQLHDBC dbc;
	long    res;

	// Environment
                // Allocation
		SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);

               // ODBC: Version: Set
		SQLSetEnvAttr( env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);

	// DBC: Allocate
		SQLAllocHandle( SQL_HANDLE_DBC, env, &dbc);

	// DBC: Connect
		res = SQLConnect( dbc, (SQLCHAR*) "MySQL_Test", SQL_NTS,
						       (SQLCHAR*) "scott", SQL_NTS,
						       (SQLCHAR*) "tiger", SQL_NTS);

		printf("RES: %i \n", res);

	//
		SQLDisconnect( dbc );
		SQLFreeHandle( SQL_HANDLE_DBC, dbc );
		SQLFreeHandle( SQL_HANDLE_ENV, env );

	printf("\n");
	return 0;
}

Tags: UnixODBC, MySQL, Sample Program, First Program, Startup Program.