View previous topic :: View next topic |
Author |
Message |
dmcmillan
Joined: 04 Jul 2006 Posts: 22
|
Posted: Wed Nov 29, 2006 8:52 am Post subject: 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
|
|
Back to top |
|
|
silverfrost Site Admin
Joined: 29 Nov 2006 Posts: 191 Location: Manchester
|
Posted: Wed Nov 29, 2006 5:21 pm Post subject: Reading values from MS Access Database |
|
|
How are you reading from the database?
------------
Administrator
Silverfrost Forums |
|
Back to top |
|
|
dmcmillan
Joined: 04 Jul 2006 Posts: 22
|
Posted: Thu Nov 30, 2006 2:37 am Post subject: Reading values from MS Access Database |
|
|
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 |
|
Back to top |
|
|
MERO
Joined: 25 Apr 2006 Posts: 41 Location: Wuerzburg, Germany
|
Posted: Thu Nov 30, 2006 3:09 am Post subject: Reading values from MS Access Database |
|
|
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 |
|
Back to top |
|
|
dmcmillan
Joined: 04 Jul 2006 Posts: 22
|
Posted: Thu Nov 30, 2006 3:21 am Post subject: Reading values from MS Access Database |
|
|
Is there any open-source software I could use for this? |
|
Back to top |
|
|
MERO
Joined: 25 Apr 2006 Posts: 41 Location: Wuerzburg, Germany
|
|
Back to top |
|
|
sparge
Joined: 11 Apr 2005 Posts: 371
|
Posted: Thu Nov 30, 2006 4:15 am Post subject: Reading values from MS Access Database |
|
|
That's what I use - but it's not open source. |
|
Back to top |
|
|
dmcmillan
Joined: 04 Jul 2006 Posts: 22
|
Posted: Tue Dec 05, 2006 11:56 am Post subject: |
|
|
There is a lite version of the software which is open source.
Thanks for the assistance.
David McMillan |
|
Back to top |
|
|
dmcmillan
Joined: 04 Jul 2006 Posts: 22
|
Posted: Thu Dec 07, 2006 3:24 pm Post subject: f90SQL-lite |
|
|
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 |
|
Back to top |
|
|
DrTip
Joined: 01 Aug 2006 Posts: 74 Location: Manchester
|
Posted: Thu Dec 07, 2006 6:02 pm Post subject: reading from a database |
|
|
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 |
|
Back to top |
|
|
dmcmillan
Joined: 04 Jul 2006 Posts: 22
|
Posted: Fri Dec 08, 2006 12:39 pm Post subject: And for my next problem .... |
|
|
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 |
|
Back to top |
|
|
dmcmillan
Joined: 04 Jul 2006 Posts: 22
|
Posted: Wed Dec 13, 2006 2:49 pm Post subject: Small steps... |
|
|
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 |
|
Back to top |
|
|
qt
Joined: 23 Aug 2005 Posts: 46 Location: Berlin, Germany
|
Posted: Thu Feb 08, 2007 10:41 am Post subject: FTN95 and Access (database access via ODBC) |
|
|
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
-------------------------------------------------------------- |
|
Back to top |
|
|
|