• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to set up ODBC driver

 
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm trying for the first time to connect to a local MySQL database using ODBC driver on a Ubuntu 20.04 machine.
This is the code taken from a textbook:


which compiles using

gcc conexion.c -o conexion -lodbc -std=gnu99


if I replace nFilas and nIdicator variables type from SQLINTEGER to SQLLEN
 
Rancher
Posts: 508
15
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Fernando - sorry, can you explain what the problem is? You've shown some that code that appears to compile; doesn't it do what you want it to do?

Edit: ok, you've edited your post (again). Finished now?
 
Fernando Sanchez
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

John Matthews wrote:Hi Fernando - sorry, can you explain what the problem is? You've shown some that code that appears to compile; doesn't it do what you want it to do?

Edit: ok, you've edited your post (again). Finished now?



The question is how to get a successful connection to the db?
 
Saloon Keeper
Posts: 27762
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Oh, man, I haven't heard of ODBC in years. I had to go check to see if it was even still a thing on Linux.

Went to the official website (https://docs.snowflake.com/en/user-guide/odbc-linux.html), read the docs and this is what I determined:

1. You have defined 2 DSNs in your odbc.ini, but only one of them is actually properly qualified (Mysql-odbc). The myodbc8a DSN isn't.

2. The Mysql-odbc DSN's Driver value isn't a Linux filesystem path to a driver. That is probably your main problem.
 
Fernando Sanchez
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tim Holloway wrote:Oh, man, I haven't heard of ODBC in years. I had to go check to see if it was even still a thing on Linux.

Went to the official website (https://docs.snowflake.com/en/user-guide/odbc-linux.html), read the docs and this is what I determined:

1. You have defined 2 DSNs in your odbc.ini, but only one of them is actually properly qualified (Mysql-odbc). The myodbc8a DSN isn't.

2. The Mysql-odbc DSN's Driver value isn't a Linux filesystem path to a driver. That is probably your main problem.



I think my problems is I'm clueless about what my odbc.ini should be like.
 
Tim Holloway
Saloon Keeper
Posts: 27762
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Are you familiar with the basic syntax of ".ini" files? While they are all over Microsoft Windows™, they can also be found in many Linux products as well. They're not as flexible as JSON, YAML or XML, but more than Java properties.

The top section of the odbc.ini lists the names of the datasources you are defining and their associated logical driver names. Each datasource is defined in detail in a section whose heading is the same as a name that was defined in the top section (ODBC Data Sources).

The odbcinst.ini file does not define a datasource. Its purpose is to tell the OS as a whole what your logical driver names are and where the actual driver is in the filesystem. There is one thing that seems off in the Snowflake docs, incidentally, since the odbcinst.ini file defines logical driver names, but their odbc.ini is using absolute driver paths. Other sources would seem to indicate that that should be the logical driver name, not its path. And, incidentally, don't put spaces in names like that. It could bite you.

The main reason that I'd expect the logical driver name to be what goes on the "Driver =" line is that it allows the driver to be relocated for all ODBC apps by simply editing odbcinst.ini. Otherwise if you changed driver files you'd have to edit every ODBC app that used it.
 
Marshal
Posts: 79177
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

John Matthews wrote:. . . ok, you've edited your post (again). . . .

FS: Please don't edit posts after they have been replied to; that causes no end of confusion about what question the answers are to. I have reverted the changes. Please show us any changes in a new post.
 
Campbell Ritchie
Marshal
Posts: 79177
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I merged your stuff with the following thread. I hope that is okay by you.
 
Fernando Sanchez
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm trying for the first time to connect to a local MySQL database using ODBC driver.
This is done on a Ubuntu 20.04 machine.

This is the code taken from a textbook:


I managed it to compile after modifying lines 21, 22 (changing types from SQLINTEGER to SQLLEN) and 32 (changing %u to %lu):

gcc conexion.c -o conexion -lodbc -std=gnu99



Which generates the excutable conexion file.

The purpose of the program is to connet to local db employees located at a local MySQL 8.0 server and launch a query.
The db is correctly installed and the query intended to be executed works fine:

profe@profe-VirtualBox:~/Downloads$ mysql -u root -s employees -p

Enter password:

mysql> select * from departments;

dept_no dept_name

d009 Customer Service

d005 Development

d002 Finance

d003 Human Resources

d001 Marketing

d004 Production

d006 Quality Management

d008 Research

d007 Sales

mysql>



The textbook comments both odbcinst.ini and odbc.ini files at /etc should be edited so that SQLDriverConnect() function be able to connect to the database.

My odbc.ini was created automatically by MySQL 8.0.31 odbc connector installation process and seems to have installed two drivers one for Unicode and other for ANSI codifications:

profe@profe-VirtualBox:~/Downloads$ cat /etc/odbcinst.ini

[MySQL ODBC 8.0 Driver]

Driver=/usr/local/lib/libmyodbc8w.so

UsageCount=1



[MySQL ODBC 8.0]

Driver=/usr/local/lib/libmyodbc8a.so

UsageCount=1



I guess that file is perfectly fine (but is quite different from the one shown in the textbook which used a MySQL 5 db).

I am clueless (despite having spent hours trying to figure it out) how my odbc.ini should be, I'm only found at MySQL documentation that it should be sth like this:

profe@profe-VirtualBox:~/Downloads$ cat /etc/odbc.ini

[ODBC Data Sources]

myodbc8w     = MyODBC 8.0 UNICODE Driver DSN

myodbc8a     = MyODBC 8.0 ANSI Driver DSN



[myodbc8w]

Driver       = /usr/local/lib/libmyodbc8w.so

Description  = Connector/ODBC 8.0 UNICODE Driver DSN

SERVER       = localhost

PORT         =

USER         = root

Password     =

Database     = test

OPTION       = 3

SOCKET       =



[myodbc8a]

Driver       = /usr/local/lib/libmyodbc8a.so

Description  = Connector/ODBC 8.0 ANSI Driver DSN

SERVER       = localhost

PORT         =

USER         = root

Password     =

Database     = test

OPTION       = 3

SOCKET       =



Which I adapted to:

profe@profe-VirtualBox:~/Downloads$ cat /etc/odbc.ini

[ODBC Data Sources]

myodbc8w     = MyODBC 8.0 UNICODE Driver DSN

myodbc8a     = MyODBC 8.0 ANSI Driver DSN



[myodbc8w]

Driver       = /usr/local/lib/libmyodbc8w.so

Description  = Connector/ODBC 8.0 UNICODE Driver DSN

SERVER       = localhost

PORT         =

USER         = root

Password     =

Database     = test

OPTION       = 3

SOCKET       =



[myodbc8a]

Driver       = /usr/local/lib/libmyodbc8a.so

Description  = Connector/ODBC 8.0 ANSI Driver DSN

SERVER       = localhost

PORT         =

USER         = root

Password     =

Database     = test

OPTION       = 3

SOCKET       =



But there is obviously sth wrong with this file since the connectecion to the employees db is never established when running the compiled code:

profe@profe-VirtualBox:~/Downloads$ ./conexion

NO HA SIDO POSIBLE LA CONEXION



How should my odbc.ini look like?






 
Campbell Ritchie
Marshal
Posts: 79177
377
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Which textopok? Please always Quote Your Sources.
 
Fernando Sanchez
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Campbell Ritchie wrote:Which textopok? Please always Quote Your Sources.


Acceso a Datos (2nd Edition) by Alicia Ramos Marion and Maria Jesus Ramos Martin, year 2016, Editorial Garceta.
 
Fernando Sanchez
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tim Holloway wrote:Are you familiar with the basic syntax of ".ini" files?



No, I'm not familiar with that syntax at all, I'm totally clueles. I just copied the odbc.ini file from what MySQL documentation says a odbc.ini file should look like https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-dsn-unix.html and added the datbase name and user's password.
 
Tim Holloway
Saloon Keeper
Posts: 27762
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK. Well here's a good explanation of how .ini files work, and especially odbcinst.ini:

https://www.mankier.com/5/odbcinst.ini

The official manual page for odbc.ini itself can be found here:

https://www.mankier.com/5/odbc.ini
 
Fernando Sanchez
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tim Holloway wrote:OK. Well here's a good explanation of how .ini files work, and especially odbcinst.ini:

https://www.mankier.com/5/odbcinst.ini

The official manual page for odbc.ini itself can be found here:

https://www.mankier.com/5/odbc.ini



After expending more time than reasonable trying to understand how these configuration files work, I've reached the conclussion (surely wrong) that there's nothing wrong with my odbcinst.ini and odbc.ini current files:

profe@profe-VirtualBox:~$ cat /etc/odbcinst.ini
[MySQL ODBC 8.0 Driver]
Driver=/usr/local/lib/libmyodbc8w.so
UsageCount=1

[MySQL ODBC 8.0]
Driver=/usr/local/lib/libmyodbc8a.so
UsageCount=1

profe@profe-VirtualBox:~$ cat /etc/odbc.ini
[MyDSN]
Description = Your MySQL database
Driver = MySQL ODBC 8.0 Unicode Driver
Server = 127.0.0.1
User = root
Password = ASIR2
Port = 3306
Database = employees



Despite that, the program continues to fail to connect to the database, no idea why:

profe@profe-VirtualBox:~/Downloads$ ./conexion
ret= 0
ret= 0
ret= 0
ret= -1
SQL_NTS= -3
SQL_DRIVER_COMPLETE= 1
NO HA SIDO POSIBLE LA CONEXION





 
Fernando Sanchez
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The problem seems to be that I don't have libmyodbc installed because it is not longer compatible with modern Ubuntu versions:

profe@profe-VirtualBox:~$ sudo apt-get install unixodbc unixodbc-dev libmyodbc
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
Package libmyodbc is not available, but is referred to by another package.
This may mean that the package is missing, has been obsoleted, or
is only available from another source

E: Package 'libmyodbc' has no installation candidate



According to this https://unix.stackexchange.com/questions/359357/install-libmyodbc-on-ubuntu-yakkety-16-10 libmyodbc is not compatible with modern versions of MySQL.



 
Tim Holloway
Saloon Keeper
Posts: 27762
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This might steer you towards a solution: https://www.datasunrise.com/professional-info/myodbc-on-ubuntu-16-04/

But I think it's time to step back and reflect on whether you've got an ideal approach. Do you actually need ODBC in particular, or is this a strictly MySQL app?

MySQL has a native API: https://dev.mysql.com/doc/c-api/8.0/en/ which while not portable the way ODBC is, is likely going to be easier to work with.

Likewise, is C the ideal language for the app. Many other Linux languages have database APIs that are are least somewhat cleaner than ODBC.

The gold standard, of course, when it comes to working with databases is Java, as JDBC is a true "write-once/run-anywhere" database interface. The downside being that you have to have a JVM installed, of course (though it's likely pre-installed on current Ubuntu distros).
 
Fernando Sanchez
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tim Holloway wrote:This might steer you towards a solution: https://www.datasunrise.com/professional-info/myodbc-on-ubuntu-16-04/

But I think it's time to step back and reflect on whether you've got an ideal approach. Do you actually need ODBC in particular, or is this a strictly MySQL app?

MySQL has a native API: https://dev.mysql.com/doc/c-api/8.0/en/ which while not portable the way ODBC is, is likely going to be easier to work with.

Likewise, is C the ideal language for the app. Many other Linux languages have database APIs that are are least somewhat cleaner than ODBC.

The gold standard, of course, when it comes to working with databases is Java, as JDBC is a true "write-once/run-anywhere" database interface. The downside being that you have to have a JVM installed, of course (though it's likely pre-installed on current Ubuntu distros).



Thank you for the feedback, I'll try that solution.
(In case you are curious, this code was taken from a textbook, with a whole chapter dedicated to database connectors from applications in general, with plenty of JDBC examples, this one was simply the example of using ODBC from code). I'm not unfamiliar with JDBC, I just wanted this rare example to work, I didn't expect it was going to take me so long.
 
Rancher
Posts: 326
14
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
As you posted the book is from 2016 - but even back then odbc was already obsolete in favor of language-native drivers.

I'm not sure if you read about odbc before but it's a somewhat outdate technology used as a unified middleware in between the application and the database. So unless there's some special usecase where the database server can only connected to via odbc caused by lack of a modern language-native driver it should not be used to write new applications.

I suggest to use a language-native driver - in this case the mysql/c connector.
 
Tim Holloway
Saloon Keeper
Posts: 27762
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
ODBC was originally designed as a database-neutral interface for Windows, and I'm fairly certain that the concept may have influenced JDBC, though not the implementation.

But this was during a period when Microsoft started emitting a new database interface almost weekly, much to my frustration. ODBC I think has been pretty much obsoleted on Windows and at last count, COM/DCOM (formerly OLE) was the accepted mechanism.

ODBC was never really comfortable on Linux, however, since it was designed for Windows.

Native drivers are not a new thing, and in fact, they can be a real pain because they tie a particular database instance to your application at a fundamental level. PHP was originally designed with a different API for every interface it supported and that caused a lot of grief, resulting in PEAR, although the damage was done and I still see a lot of PHP done with native API's. Though in fairness, I no longer see a lot of PHP — most PHP systems I work with these days are canned solutions whose source code I rarely get into deeper than setting up their config file.
 
What a stench! Central nervous system shutting down. Save yourself tiny ad!
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic