Connect to oracle/mysql DB through ODBC

[Mattia]

I know that this topic has been discussed many and many times, but this problem is concerning me since 5 years and I still have not found a solution.

I am using a virtual machine with Compaq visual Fortran under Windows XP because I need to compile a F project using the SQL tools from Canaima. My code works with Oracle/MySWL dbs. Now I want to move to Visual Studio and the Intel Fortran Compiler, but I do not know how to connect to a sql db.

Is there a solution for this? Of course, I would like to continue using the Canaima libraries, but if not possible I can switch to other tools...

[Arjen_Markus]

Several years ago I worked on an interface for SQLite and, derived from that, for ODBC. You can find the code here: http://flibs.sourforge.net There is also an interface specific to MySQL. It may or may not be sufficient for your needs. (I should probably update it, because the interfacing with C predates ISO_C_BINDING ;)).

[Mattia]

Thank you, I have seen this. The use I make of the DB is quite basic, I think this may help but I am not sure how to use it...

I have downloaded a file called flibs-0.9.zip, and then I have found a sqlite.f90 file containing a module.

I do not really know how to make this work.

[Arjen_Markus]

I will send you a private message regarding this.

[Arjen_Markus]

Hm, that seems not to be possible any more. Then I will have to do it this way.

I have not updated the zip files in a long time now, so the best way to get the code is to get a copy of the source code via the SVN repository. For the ODBC interface, you need to get the files fodbc.f90 and codbc.c and add them to your program. I see I have forgotten to include the sample makefile, when I moved the sources to a cleaner set-up.

The easiest way to help you wih this will be to prepare a small test program with all the necessary bits and pieces. Do you use Visual Studio or makefiles or the like?

[Mattia]

Thank you a lot for this, I use Visual Studio.

[Arjen_Markus]

Well, it took a bit of work to assemble all the pieces, but here at least is a zip file containing the source code and a very basic batch file to link the program. More to follow (that is: a VS solution).

The trst program does not do all that much, as it wants to read an MS Access file, but it ought to ilustrate the use.

[Mattia]

I will wait for the VS solution, the batch is not working because cl and ifort are not recognized as commands

[Arjen_Markus]

What I do in such cases, especially with small programs like this one, is open the console window - "Compiler ... environment". That sets up the path and environment variables to use the command-line tools. I hope to be able to set up the VS solution today (that always takes me more time than writing a batch file ;)).

[Mattia]

Thanks, I realized that It was an environment path issue. So I run the Intel oneAPI command prompt, and it worked, I have got the exe.

The VS solution would help a lot, thank you...

[Arjen_Markus]

Well, the attached zip file contains my example VS solution. I hope the set-up is clear enough. FYI: I needed to add the option _CRT_SECURE_NO_WARNINGS to avoid error messagea about the use of strcpy() and I needed to add the odbc32.lib library as a dependency.

[Johannes_Rieke]

Hi,

This text below is not connecting through ODBC but another solution. So, maybe offtopic.

Some years ago I build successfully interfaces based on FLIBS/ Daniel Krafts wrappers to the MySQL c-API. One of my old threads regarding this topic can be found here and another thread here.

FLIBS can be found still: https://github.com/philip-peterson/flibs or http://flibs.sourceforge.net/

It worked totally fine for me. I have modified the files and could provide my version, if it would help.

[Arjen_Markus]

Interesting :).I am definitely interested in that.

Aside: I had some trouble with the ODBC library for MinGW-w64/MSYS2, as that used to be accessible via "-lodbc", but apparently a different name is now used (or at least a different name was used by the package nanodbc I had to install). And some other stuff relating to C data types specific to SQL. That used not to be a problem. Well, another lesson learned. Things may change, so you have to check now and again that other things still work.

[Mattia]

Thank you a lot for this.

The code is compiling, but the test on the available drivers is returning this output. Also, I tried to connect to the odbc with a connection string and using odbc_connect, but it does not work. Now I am using a mysql community server database. I think I will give up with this... unless the solution is simple.

[Arjen_Markus]

Hm, odd, but I understand. Perhaps the direct connection to MySQL that Johannes Rieke mentioned is a solution?

[Arjen_Markus]

I have attached the code in Flibs that Johannes referred to. It does not contain his adaptations ;).

I am puzzled by the output you got. On my system I get:

Overview of installed drivers:

SQL Server                              APILevel=2
SQL Server Native Client 11.0           UsageCount=1

Overview of data sources:


Opening a particular database file:
List of tables:
 Create a (new) table:
 08003     [Microsoft][ODBC Driver Manager] Connection not open
 08003     [Microsoft][ODBC Driver Manager] Connection not open
 Check the contents:
 08003     [Microsoft][ODBC Driver Manager] Connection not open
 Data:
 Columns of table "example"
 08003     [Microsoft][ODBC Driver Manager] Connection not open
Example with MS Excel:
List of tables:

(This is on Windows 10.)

Obviously on your system things are quite different.

[Johannes_Rieke]

Hi, sorry has been very busy. I had to seperate the modified files from other dependencies. Since I used the code in 2015 with mysql libs from this time in x64 GNU/Linux + ifort. I cannot say, whether my modified code is working, still. I attached it anyways. It's based on the FLIBS version. I've no idea about rights and license. Please contact in this case the owner of FLIBS or Daniel Kraft.

[Johannes_Rieke]

Second try, first time, the attachment has been erased.

[Arjen_Markus]

As the "owner" of Flibs, I thank you for this contribution. The license for all the stuff in that project is "BSD", I could have been clearer about that (by inserting the license text in the source files).