forums.silverfrost.com Forum Index forums.silverfrost.com
Welcome to the Silverfrost forums
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Reading values from MS Access Database

 
Post new topic   Reply to topic    forums.silverfrost.com Forum Index -> General
View previous topic :: View next topic  
Author Message
dmcmillan



Joined: 04 Jul 2006
Posts: 22

PostPosted: Wed Nov 29, 2006 8:52 am    Post subject: Reading values from MS Access Database Reply with quote

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
View user's profile Send private message
silverfrost
Site Admin


Joined: 29 Nov 2006
Posts: 191
Location: Manchester

PostPosted: Wed Nov 29, 2006 5:21 pm    Post subject: Reading values from MS Access Database Reply with quote

How are you reading from the database?

------------
Administrator
Silverfrost Forums
Back to top
View user's profile Send private message Visit poster's website
dmcmillan



Joined: 04 Jul 2006
Posts: 22

PostPosted: Thu Nov 30, 2006 2:37 am    Post subject: Reading values from MS Access Database Reply with quote

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
View user's profile Send private message
MERO



Joined: 25 Apr 2006
Posts: 41
Location: Wuerzburg, Germany

PostPosted: Thu Nov 30, 2006 3:09 am    Post subject: Reading values from MS Access Database Reply with quote

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
View user's profile Send private message Visit poster's website
dmcmillan



Joined: 04 Jul 2006
Posts: 22

PostPosted: Thu Nov 30, 2006 3:21 am    Post subject: Reading values from MS Access Database Reply with quote

Is there any open-source software I could use for this?
Back to top
View user's profile Send private message
MERO



Joined: 25 Apr 2006
Posts: 41
Location: Wuerzburg, Germany

PostPosted: Thu Nov 30, 2006 3:27 am    Post subject: Reading values from MS Access Database Reply with quote

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

Klaus
Back to top
View user's profile Send private message Visit poster's website
sparge



Joined: 11 Apr 2005
Posts: 371

PostPosted: Thu Nov 30, 2006 4:15 am    Post subject: Reading values from MS Access Database Reply with quote

That's what I use - but it's not open source.
Back to top
View user's profile Send private message Send e-mail
dmcmillan



Joined: 04 Jul 2006
Posts: 22

PostPosted: Tue Dec 05, 2006 11:56 am    Post subject: Reply with quote

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

Thanks for the assistance.

David McMillan
Back to top
View user's profile Send private message
dmcmillan



Joined: 04 Jul 2006
Posts: 22

PostPosted: Thu Dec 07, 2006 3:24 pm    Post subject: f90SQL-lite Reply with quote

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
View user's profile Send private message
DrTip



Joined: 01 Aug 2006
Posts: 74
Location: Manchester

PostPosted: Thu Dec 07, 2006 6:02 pm    Post subject: reading from a database Reply with quote

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
View user's profile Send private message
dmcmillan



Joined: 04 Jul 2006
Posts: 22

PostPosted: Fri Dec 08, 2006 12:39 pm    Post subject: And for my next problem .... Reply with quote

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
View user's profile Send private message
dmcmillan



Joined: 04 Jul 2006
Posts: 22

PostPosted: Wed Dec 13, 2006 2:49 pm    Post subject: Small steps... Reply with quote

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
View user's profile Send private message
qt



Joined: 23 Aug 2005
Posts: 46
Location: Berlin, Germany

PostPosted: Thu Feb 08, 2007 10:41 am    Post subject: FTN95 and Access (database access via ODBC) Reply with quote

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
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    forums.silverfrost.com Forum Index -> General All times are GMT + 1 Hour
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group