Database Connections in Fortran

Database Connections in Fortran

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

30 posts / 0 new
Last post
For more complete information about compiler optimizations, see our Optimization Notice.

You may want to take a look at my Flibs project - http://flibs.sf.net - which has ODBC bindings
for Fortran. I am not sure the CVS repository is available again (SF has to recoverfrom ahostile intrusion
last week, but otherwise I can send you the code.

Regards,

Arjen

Thanks for the advice but I can't find the ODBC files. I downloaded Flibs and am looking in the src folder. I see directories for several of the modules but don't see ODBC. Would it have been taken out for some reason? Or am I just not looking in the right place?

Do I need to do anything other than find the right source code? It looks like there is a lot of other stuff that comes with it like configure.bat.

Also have you used any of the string routines. This seems like it might be useful as well?

Thanks

Hm, you got the tar ball/zip file? It is a fairly recent addition and I have not taken the time yet to
put it all in a new version. You will have to access the CVS repository instead - or I can send them
to you

As for the string routines, yes, they are useful. I still have to stress-test them in a situation I often
use a scripting/dynamic language for, but they come in handy at times.

Regards,

Arjen

I'm having trouble figuring out how to use the CVS repository. Maybe it would be easiest to send them to me. Can you add them to the post or do you need an email address?

Thanks for the help

Hi Arjen, I had similar problems when trying to get the ODBC sources from the link you provided. I got sidetracked onto something else but would still be interestedin having a look at your FORTRAN bindings. I have been trying to find a DLL that I could use to generate these bindings using the FORTRAN Module Wizard to so far I've been unsuccessful.

I can supply an e-mail address if you prefer not to attach it to the forum.

Many Thanks

Steve ("Dannycat")

Hi Steve, Craig,

I just tried to add a zip file to this message, but I did not succeed - the dialogue is
talking about folders to create, but not a simple file to attach.

Any idea how I should do this?

Regards,

Arjen

Follow the steps in the link below.

Steve - Intel Developer Support

Thanks Steve,

I overlooked the fact that I can click on the folder's name - just saw "Edit" and "Delete".

The files are there now - the zip-file includes an example of how to use the interface. I did not
have a convenient makefile available, but it should be straightforward enough and otherwise
just ask.

Attachments: 

AttachmentSize
Downloadapplication/zip fodbc.zip11.21 KB

Thanks Arjen I was able to get the files.

Unfortunately I'm new to wrapping c code with Fortran projects and have a couple questions.

Do I need a c compiler to compile the "codbc.c" code?

If not where should the c code "codbc.c"be placed in my project (I assume just a general project file rather than source file or something else)?

I'm getting the following error when compiling the test program.
I get this on the calls to odbc_exec_c.
"The CALL statement is invoking a function subprogram as a subroutine"

Do you have any ideas of what my problem may be?

Thanks again for you time.
Craig

Hi Craig,

codbc.c is a C source file and you do indeed require a C compiler that is compatible with
the Fortran compiler you use (or vice versa).

Oh darn! The interface to odbc_exec_c as defined in the routine odbc_prepare_select and others
is wrong - it should read:

interface
subroutine odbc_exec_c( ... )
...
end interface

I will repair it. I can create a complete library that you can just link against - that will safe you
the use of a C compiler. I will post it here (I will try this over the weekend)

Regards,

Arjen

Got it - I had to make some small changes to cater for Intel Fortran (I had used CVF before and some other
compilers).
I have assumed you use Intel Fortran 11.x, then the libraries should work fine. Link with odbc32.lib as additional library. (If you use a newer version, you may need to recompile the Fortran source - that has only changed to get rid of the above mistake)

Regards,

Arjen

PS I made a mistake wrt the placement of the hidden string length arguments (and no easy way to test the resulting programatm), so I updated the zip-file

Attachments: 

AttachmentSize
Downloadapplication/zip fodbc_lib.zip34.34 KB

Arjen
I was able to fix the interface to odbc_exec_c. If you have time I would appreciate the library that you mention above.

Thanks and no rush.
Craig

Hi Craig,

it is an attachment to my previous mail.

Let me know ifyou run into trouble or have some questions (I will be away next week, back on March 7)

Regards,

Arjen

Arjen
Sorry for the earlierfollow-up asking for something you already provided. For some reason I didn't see the other responses.

I was able to download the libraries but am still having trouble getting it to work. My question seems pretty simple but am fairly new to Fortran and not sure how to link the other libraries.

What directory do I save the .lib and .mod files to?
Do I just add the .lib files to my project as general files and use an include statement?
Do I need to make any library property or linking property changes to my project?
How do I add odbc32.lib?
Any additional explanation of how to do this would be appreciated.

I'm getting some unrecognized token errors in codbc.lib.

Thanks
Craig

Hi Craig,

No problem ;)

To answer your questions:
probably best to save the files in a separate directory in the vicinity of the rest of your
code - like: c:\myprogram\lib

Via the project properties dialogue:
- Add that directory to the INCLUDE directories for the Fortran compiler
- Add that directory to the additional library paths for the linker
- Add the libraries to the INPUT libraries for the the linker

What tokens are reported?

Regards,

Arjen

I think I've figured out how to add the three things you mention above. I'm now only getting one link error.

Unable to load c2.dll

I assume this is something the codbc.lib uses?

Thanks
Craig

Hi Craig,

sorry to respond so late. I checked: there is a reference in the file codbc.lib to a c2 DLL. It seems
to be part of the MSVC 9.0 compiler. That means I should turn the stuff into aDLL, so as to make it
more independent of the various compiler libraries.

I wil have a look next week.

Regards,

Arjen

I hope I may point to ForDBC here. ForDBC is provides Intel Visual Fortran programmers with the means to access databases via ODBC directly. Here is a link for more information:
http://qtsoftware.de/vertrieb/db/fordbc_e.htm.

ForDBC comes with examples that show how to read from and to write to MS/Access database files.
Hope this helps.

Kind regards,

Jrg Kuthe

I have almost managed to get FORTRAN to communicate with Microsoft Access via ODBC API functions. However I have hit a brick wall when it comes to retrieving data. I think Ihave tracked down the problem to the SQLBindCol function. When I substitute the F90SQL version of this function for the APIthe test prgram (attached) works and information is retrieved, but using the native API does not result in any data being retrieved although no erros are flagged.
My test program calls f90_**** wrapper subroutines/functions that can be used to switch between API and F90SQL. Below is a couple of extracts from the wrapper module. Full code is in the attachment.

interface
  integer(SQLRETURN_KIND) function SQLBindCol_f(StatementHandle,ColumnNumber,TargetType, &
                                                TargetValuePtr,BufferLength,StrLen_or_Ind)
  !DEC$ ATTRIBUTES DEFAULT, STDCALL, DECORATE, ALIAS: 'SQLBindCol' :: SQLBindCol_f
  use f90SQLConstantsx
  integer(SQLHSTMT_KIND),intent(in)     :: StatementHandle
  integer(SQLUSMALLINT_KIND),intent(in) :: ColumnNumber
  integer(SQLSMALLINT_KIND),intent(in)  :: TargetType
  !DEC$ ATTRIBUTES REFERENCE            :: TargetValuePtr
  integer(SQLPOINTER_KIND),intent(in)   :: TargetValuePtr
  integer(SQLLEN_KIND),intent(in)       :: BufferLength
  !DEC$ ATTRIBUTES REFERENCE            :: StrLen_or_Ind
  integer(SQLLEN_KIND),intent(in)       :: StrLen_or_Ind
  end function

end interface

contains

logical function f90_SQLBindCol(StatementHandle,ColumnNumber,TargetType, &
TargetValuePtr,BufferLength,StrLen_or_Ind)
!**************************************************************************
! Binds application data buffers to columns in the result set.
!**************************************************************************

implicit none

! Arguments
integer(SQLHSTMT_KIND),intent(in) :: StatementHandle
integer(SQLUSMALLINT_KIND),intent(in) :: ColumnNumber
integer(SQLSMALLINT_KIND),intent(in) :: TargetType
integer(SQLPOINTER_KIND),intent(in) :: TargetValuePtr
integer(SQLLEN_KIND),intent(in) :: BufferLength
integer(SQLLEN_KIND),intent(in) :: StrLen_or_Ind

integer(SQLRETURN_KIND) :: iRet

! Initialise
iRet = 0

!DEC$ IF DEFINED (F90SQLW32)
call f90SQLBindCol(StatementHandle,ColumnNumber,TargetType, &
TargetValuePtr,BufferLength,StrLen_or_Ind,iRet)
!DEC$ ELSE
iRet = SQLBindCol_f(StatementHandle,ColumnNumber,TargetType, &
TargetValuePtr,BufferLength,StrLen_or_Ind)
!DEC$ ENDIF

F90_SQLBindCol = SQLSAS_Chk(iRet)
if(.not.SQLSAS_Chk(iRet)) then
call SQL_ShowDiags(SQL_HANDLE_STMT,StatementHandle)
return
endif

return
end function
end module
Any assistance in solving this would be greatly appreciated. My intention is to replace
all F90SQL dependencies from my code so that I can migrate to 64-bit configurate. All the required
functions are set up in the wrapper module.
Thanks in advance.
Steve

Attachments: 

AttachmentSize
Downloadapplication/zip Test_File.zip3.08 MB

I have solved the problem at last. The interface definition of SQLBindCol should not have any modifiers setting the arguments to REFERENCE. These arguments are addresses and I assumed wrongly that they should be passed by reference. When I remove these directives the function works fine and data is retrieved when the SQLFetch function is called.

It is quite straightforward to access ODBC functionality from FORTRAN although it would be much easier if the Module Wizard could have been used to generate the interfaces. I was unsuccessful in finding the server application from which to Wizard could generate the code.

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

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"

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

Attachments: 

AttachmentSize
Downloadapplication/zip fodbc_dll.zip24.92 KB

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

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!

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

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?

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.

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

Leave a Comment

Please sign in to add a comment. Not a member? Join today