Reading values from MS Access Database

Hello,

I am attempting to read into my fortran 95 program from a .mdb file. When I try to use normal methods I get gibberish - do I have to do someting specific to interface with files of this type?

It seems a bit strange as I am able to write to excel without much of a problem.

Thank You

David McMillan

How are you reading from the database?

Administrator Silverfrost Forums

I think I am treating it too simplistically: attempting to use a read command after I have opened the file (like you would use a text file).

I think there may be some sort of interface needed - but I have little experience with interfacing Fortran with databases.

Apologies if this seems trivial!

Thank You

David McMillan

Hi I don't think you can do that just by Fortran. You need a third party tool. I use Winteracter for reading Access Database. It work fine and is easy to use. Klaus

Is there any open-source software I could use for this?

Please see http://www.canaimasoft.com/f90sql/

Klaus

That's what I use - but it's not open source.

There is a lite version of the software which is open source.

Thanks for the assistance.

David McMillan

I have been using the f90SQL-lite software to interafce with the access database, however I have a problem:

1. The maximmum number of rows I can access is 128, a limitation built into the lite version.

2. The suggested work-around for this is to generate a new statement handle for every reading: when I do this, the program starts to read from the start of the file again (instead of where it left off).

The solution would seem to be to use 'f90SQLSetPos' or some other function to set the pointer (cursor?) in the file once the new statement handle is generated. However this function is not included in the lite library.

Can anyone help?

Thanks

David McMillan

I have some experience of this.

I have two possible routes one is to rethink your use of F90Sql which I am no fan of.

depending on your architecture you can use .net classes to access databases with very little coding of course these aren't fortran and you have to consider the .NET / WIN32 performance playoff.

if you want to have most of your code native this route would involve coding a linker library in C++ to pass the results through which might be to involved for you. but this is all free.

The .net framwork is great for accessing databases. In some respects this is its main strength!

if you don't want that route and want to continiue with F90Sql lite I would suggest that rather than trying to shift position of your pointer, that you alter the sql statement that you are sending using a parameter that limits the results you are reading, this will probably be quicker to code and run quicker.

to clarify

submit queries along the line of

charcater *500 sql

sql = ''

do i 0,n

sql = 'select * from table1
where index > (i*127) and index < (i+1)*127 '

call reader (sql)

end do

where reader is your reading procedure

read the results in batches

obviously you have to have have a suitable index to adopt this approach

but chances are you probably have one since your using fortran which is quite number orientated!

I hope this at least gives you some ideas

Carl

Hello again,

I seem to have corrected the problems above, and now have a new one.

I have a seemingly random error with the fetch routine (using f90SQL): I am reading 2 columns (2 and 33) scanning down the rows which works fine until I get to entry 2844 when this pops up:

'Error fetching data 22002: Indicator variable required but not supplied on column number 33'

Does anyone recognise this?

Many thanks again,

David

It turns out that the above error is caused because of an empty field in the data ... can anyone tell me how to deal with empty fields using the fetch command in f90SQL (without crashing the program!)?

Thanks

David

Empty fields cause SQL_NULL_DATA to be returned in the variable StrLen_or_IndPtr specified in the call of

SQLBindCol( SQLHSTMTStatementHandle, SQLUSMALLINTColumnNumber, SQLSMALLINTTargetType, SQLPOINTERTargetValuePtr, SQLINTEGERBufferLength, SQLINTEGER *StrLen_or_IndPtr);

(excerpt from WinAPI)

after the return from SQLFetch().

Regarding the availability of a tool to read/write Access (and other databases supporting ODBC) I can offer a tool named ForDBC which is available for FTN95. Unfortunately the documentation is still in German: http://www.qtsoftware.de/vertrieb/db/fordbc.htm If there is more interest in this, please let me know ( admin@qtsoftware.de ) and this might push me a bit to have an English manual as well soon.

Joerg Kuthe

QT software GmbH Konstanzer Strasse 10 D-10707 Berlin Tel. +49(0)30/9290087-0 Fax. -2 eMail: admin@qtsoftware.de WWW http://www.qtsoftware.de