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

[Arjen_Markus]

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

[craig_h]

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

[Arjen_Markus]

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

[craig_h]

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

[dannycat]

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

[Arjen_Markus]

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

[Steven_L_Intel1]

Follow the steps in the link below.

[Arjen_Markus]

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.

[craig_h]

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

[Arjen_Markus]

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

[Arjen_Markus]

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

[craig_h]

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

[Arjen_Markus]

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

[craig_h]

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

[Arjen_Markus]

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

[craig_h]

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

[Arjen_Markus]

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

[joerg_kuthe]

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

[dannycat]

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.

[bash]
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
[/bash]
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

[dannycat]

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.