Database Connections in Fortran

[craig_h]

I need to connect to a MS Access database with Fortran. Is ODBC the most logical choice? Can I use C source header files like sql.h? I've read where people have used f90sql but it sounds like it is no longer supported.

Any advice on the best way to connectto a database from Fortran would be appreciated.
Thanks

[craig_h]

dannycat
Thanks for the post and glad you got it figured out. Ihave a couple questions.

Are there other places the REFERENCE statements should be removed or is the SQLBindCol interface the only one?

When I try your test program the following subroutine gives an error. I don't see where the database name is defined. Maybe thats why the connection is failing.
f90_SQLDriverConnect

Should I now be able to use this code without using F90SQL? I'm not sure if the licensing of F90SQL would prevent me from using they're code. I assume f90SQLConstantsx and f90SQLStructuresx are also part of the F90SQL package? And I guess for that matter do I have your permission to use your code?
I see a lot of the following statements but where is F90SQLW32 defined?
!DEC$ IF DEFINED (F90SQLW32)

Thanks to everyone for the help.
Craig

[dannycat]

Hi Craig,
I will try to answer your questions and help explain what I believe to be the solution to the connection with ODBC databases from FORTRAN.

the REFERENCE attribute should be used for:
a) passing character strings
b) any variable where the function returns data (usually specified with * in C argument definition.) Not deferred output though)

note the when passing addresses (SQLPOINTER) these should be passed by value. This was my mistake.
Use loc() function to obtain the address. This will be integer*4 on 32-bit and integer*8 on 64-bit.

Refer to Microsoft MSDN ODBC online documentation for details of all functions that are available to you. Remember my code is just a small subset used for reading and updating existing databases. If you need to add more functionalityjust create the required interface and an assiociatedwrapper subroutine or function to check arguments and convert data (if required) before calling the C routine.

Make sure the ODBC libraries are included in the Linker Properties and it should work fine.

You need to Create a connection to the database using ODBC facility in Control Panel/ Administative Tools.
Select the User Tab, pick a Micosoft Access Driver and click Add. Select the database give it a DSN Name (referred to the SQLDriverConnect) and description and away you go. Database must already exist.

Note if you are compiling 64-bit you will have to install 64-bit drivers (for MS Access 2010). To do this you Have to firstly uninstall Office 2007/2010 and then reinstall after installing the ODBC drivers. These are available from Microsoft - search for AccessDatabaseEngine_x64.exe.
Note also the on 64-bit computers there is an Data Source ODBC application for 32-bit drivers in C:\Windows\SysWOW64.

You should be able to use the code without F90SQL so you shouldn't need a license besides it is no longer supported so I doubt if you could get one anyway. You could get the Lite Version from elsewhere on this forum but I'm not sure what's missing from this.

The f90Constantsx and f90Structuresx files are derived from F90SQL files but I've modified them to use the correct int_ptr_kind() when refering to addresses so that it works properly with 64-bit code. I don't think I actually need to use the F90Structuresx file but may require it for future use.

The F90SQLW32 compiler directives were used to switch between F90SQL and my interfaces. Again these could all be removed now it appears to work.

You can use my code as you wish.

I hope this helps.

Steve aka "dannycat"

[Arjen_Markus]

Done, the library I have attached was compiled with Intel Fortran 11.1 - there are a few
dependencies from MSVC/C++ but only the redistributable ones, so this ought to work.

Regards,

Arjen

[craig_h]

Thanks to both of you. I think I have found a solution.
Craig

[mamey4]

Hi,

I was just trying to test the programs in dannycat's zip-file. I opened the included project file with Visual Studio 2008 and Intel Visual Fortran Compiler 11, and wanted to see how it works. The program seems to get stuck at the connect line:

call f90_SQLDriverConnect(hdbc,0, "DSN=Locations", OutString, 0, retcode);

I tried to change the string, which I think of as the DB connection string, to f.e.:

call f90_SQLDriverConnect(hdbc,0, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Test_File.mdb", OutString, 0, retcode);
call f90_SQLDriverConnect(hdbc,0, "Provider=Microsoft.Jet.OLEDB.4.0;DSN=Test_File.mdb", OutString, 0, retcode);

The return code is always -1. The Test_File.mdb is in the same directory as the project files.
Can you tell me what I've done wrong?

Thanks in advance for your answer!

[dannycat]

It's a while since I did this so I've forgotten most of it. I will try to help if I can.
1) Have you assigned your database as an ODBC datasource using Control Panel/Administrative Tools->Data Sources (ODBC)?
2)Whetheror not you areusing 32 or 64 bit program configuation will need to be taken into account. 32-bit is straightforward but for 64-bit you will need to install new drivers, reinstall Access. I think this is described somewhere else in this thread.

Good Luck

[mamey4]

I was just surprised that nowhere in the code the name of the mdb file could be found; does that mean that you gave your ODBC source the name "Locations"?
Here on my Win7x64 PC there seem to be problems with adding another MS Access DB as an ODBC source, most definitely, as you already said, because of the 32/64bit issue. Anyway, I probably won't use the Access format after all, so I'll create another DB to test the program against.

Edit:
With a little workaround I could create the 32bit Access ODBC source, named it "Locations", and vila, no more errors in the program. However, the data displayed on screen is not what's stored in the mdb file, but only zeros (AnalID, Version) and spaces (Analysis). Anything else I've forgotten?

[mamey4]

I came back to this problem just recently and tried different things to resolve my problem of only receiving blanks and zeros from the DB. I tried different data types in the BindCol statement, and for the variables that shall receive the DB values every time the Fetch command is executed. That gave no improvement, however. It would have surprised me anyway, since the test program seems to work perfectly for you.

[joerg_kuthe]

Hi all,

sorry for being late with my reply regarding connecting to databases in Fortran.

My company (QT software GmbH, Germany) has just completed the port of ForDBC for Intel Visual Fortran 64-bit. So, ForDBC, a "Fortran only solution" (does not need a C or any other compiler, just IVF 32- or 64-bit). ForDBC is available from us or from Polyhedron in the U.K.

Regards,

Joerg
QT software GmbH