topic Re: Connect to oracle/mysql DB through ODBC in Intel® Fortran Compiler

Connect to oracle/mysql DB through ODBC

Mattia — Mon, 18 Jan 2021 15:03:29 GMT

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...

Re: Connect to oracle/mysql DB through ODBC

Arjen_Markus — Mon, 18 Jan 2021 15:13:48 GMT

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 ;)).

Re: Connect to oracle/mysql DB through ODBC

Mattia — Mon, 18 Jan 2021 15:56:08 GMT

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.

Re: Connect to oracle/mysql DB through ODBC

Arjen_Markus — Mon, 18 Jan 2021 16:03:50 GMT

I will send you a private message regarding this.

Re: Connect to oracle/mysql DB through ODBC

Arjen_Markus — Mon, 18 Jan 2021 16:16:35 GMT

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?

Re: Connect to oracle/mysql DB through ODBC

Mattia — Mon, 18 Jan 2021 16:25:59 GMT

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

Re: Connect to oracle/mysql DB through ODBC

Arjen_Markus — Mon, 18 Jan 2021 17:01:21 GMT

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.

Re: Connect to oracle/mysql DB through ODBC

Mattia — Mon, 18 Jan 2021 18:10:33 GMT

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

Re: Connect to oracle/mysql DB through ODBC

Arjen_Markus — Tue, 19 Jan 2021 07:47:54 GMT

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 ;)).

Re: Connect to oracle/mysql DB through ODBC

Mattia — Tue, 19 Jan 2021 08:13:28 GMT

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...

Re: Connect to oracle/mysql DB through ODBC

Arjen_Markus — Tue, 19 Jan 2021 09:21:12 GMT

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.

Re: Connect to oracle/mysql DB through ODBC

Johannes_Rieke — Tue, 19 Jan 2021 10:14:18 GMT

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.

Re: Connect to oracle/mysql DB through ODBC

Arjen_Markus — Tue, 19 Jan 2021 11:15:03 GMT

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.

Re: Connect to oracle/mysql DB through ODBC

Mattia — Tue, 19 Jan 2021 16:37:18 GMT

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.

Re: Connect to oracle/mysql DB through ODBC

Arjen_Markus — Tue, 19 Jan 2021 18:03:46 GMT

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

Re: Connect to oracle/mysql DB through ODBC

Arjen_Markus — Tue, 19 Jan 2021 18:42:37 GMT

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.

Re: Connect to oracle/mysql DB through ODBC

Johannes_Rieke — Wed, 20 Jan 2021 13:44:13 GMT

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.

Re: Connect to oracle/mysql DB through ODBC

Johannes_Rieke — Wed, 20 Jan 2021 13:46:06 GMT

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

Re: Connect to oracle/mysql DB through ODBC

Arjen_Markus — Wed, 20 Jan 2021 13:51:37 GMT

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).