Siva's Tech Journal

UnixODBC and MySQL Sample Program

Posted in DB Programming, Programming by bvssiva on April 18, 2010

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.

About these ads

10 Responses

Subscribe to comments with RSS.

  1. Ben said, on January 26, 2011 at 6:39 pm

    It may be good to note that you will need to link with the libodbc.so library when compiling the sample above. For example, when I save the code above to “sample.cpp”, I can compile with:

    g++ -g -Wall sample.cpp -o sample -lodbc

  2. Gerald Bauer said, on April 16, 2012 at 3:41 pm

    Any ideas on what permissions of libodbc.so need to be? root or mysql?
    I’m having troubles getting this to work on a CentOS machine (PHP5). We keep getting the:

    “[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
    [ISQL]ERROR: Could not SQLConnect”

    error repeatedly. We have the proper .odbc.ini file created with accurate MySQL credentials.

    Thanks in advance!

  3. Credo Mooper said, on July 24, 2012 at 10:17 pm

    Another site full of technical jargon… not much use for newbies…..

    Probably written by old unix farts who retired many years ago

    plain simple language needed please

    Tahnk you kindly

    • bvssiva said, on July 24, 2012 at 11:01 pm

      Nice to see some disrespect. Which is hard to find.

    • Dr. Watson said, on April 20, 2013 at 10:05 pm

      Sorry, but if you find this description “technical jargon” then you have absolutely no chance to develop real software – even very simple. This is clear sample and description what to do – not a popular article for the masses. Buy a fat book for C#/.Net and be a “newbie”. Linux & ODBC/SQL is too choice to make first steps.

      • Dr. Watson said, on April 20, 2013 at 10:06 pm

        …correction: “… is bad choice to make first steps.”

  4. sanjeev said, on May 22, 2013 at 6:16 am

    sir it’s great work ,but how i work using SQLite i can’t understand ,so please help …..

    • Dr. Watson said, on May 28, 2013 at 8:29 pm

      Install SQLite ODBC driver – it exists on Windows, not sure for Unix.

  5. geologists said, on March 29, 2014 at 2:03 pm

    I’m really enjoying the design and layout of
    your website. It’s a very easy on the eyes which makes it much more enjoyable for me
    to come here and visit more often. Did you hire
    out a developer to create your theme? Fantastic work!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: