Interface to SQLite relational database system

Has anyone interfaced to SQLite? There is a Fortran interface in flibs (by Arjen Markus - http://fortranwiki.org/fortran/show/FLIBS) and the Fortran source code is available, but this seems to require calls to the SQLite C library. I haven't interfaced to C libraries before, and not quite sure how to go about it. Would appreciate some advice. :? I've seen the documentation on C_EXTERNAL etc. in the FTN95 help file - but the flibs library is all in Fortran. I guess I may need to edit the flibs source code.

So far I have managed to implement a number of operations using START_PROCESS to execute the SQLite command-line utility sqlite3.exe - but only those which don't return data to, or require data from my own Fortran programs.

flibs does a lot of other things too, so when I've cracked it for SQLite, it shouldn't be too difficult to make it work for all the other stuff as well.

Why using serverless SQLite?

Citing: 'database engine that uses a server can provide better protection from bugs in the client application - stray pointers in a client cannot corrupt memory on the server. And because a server is a single persistent process, it is able to control database access with more precision, allowing for finer-grained locking and better concurrency. '

You can use server version instead and setup client-server communication with FTN95 via some ports you chose. Then FTN95 will send the commands to this port and database receive and send the data to the harddrive where FTN95 will pick then up it. Ideally of course would be that FTN95 team go one step further adopting the whole WinINET like Intel Fortran did. Then you would communicate with Fortran completely over the internet.

Hi, Dan -

You make very valid points. However, this is for an application which needs to be self-contained and installed on a stand-alone system. It is for an EU project much of which will take place in areas where the Internet is unavailable (in underground mines and in remote locations without either landline or mobile signal). I considered a 'standard' DBMS (even selected PostGreSQL as the front-runner) but finally rejected this on two counts: (a) steep learning curve for users unfamiliar with relational databases (and for me, unfamiliar with client/server programming) (b) overkill: such systems provide far more functionality than is needed in the project. For example there are a huge number of data types in PostGreSQL or Oracle, while SQLite has just 5. This reduces the programming effort very substantially. (OK, I'm just lazy!). I don't need to be concerned with problems of concurrency or locking since it is a single-user system.

Data will come from instrumentation on robots (using ROS/Linux) with conversion software coded in C/C++. There are also other applications which will be written (by other consortium members) for Windows in C/C++. You may ask - why am I using Fortran at all? Simple reason is to minimise software conversion because we need to use some Fortran legacy applications such as multivariate statistics, code which I already have 'off the shelf'.

The thing i am talking about does not require internet, all can be set on one single PC. Server part of database application permanently sniffs specified port and when local FTN95 program sends the commands on this port (this command can be sent from anywhere in the world but you do not need that) the server will act probably exactly the same way as with what you plan to do with any other ways connecting to the database. You will just completely remove any multilanguage programming. Each FTN95 program command to server consists of just one single line of Fortran text CALL READ_URL@(command to the server)

Here is an example of command i made some decent already time ago to communicate with the server application of some thirdparty software which has server set in it

call READ_URL@('http://127.0.0.1:16144/req?EnumIndices()','z01EnumIndices.d',0,ier)

it listens to the port 16144. The command set with /req? was to do something (here enumerate some indices etc). Server got it and uploaded what was asked for to harddrive. File z01EnumIndices.d is not used, it can be used for getting something from the internet or even from server, but here only stuff in the request to the server is used to provoke server for some actions. After server and database trying completing the request you may ask it few times to check if the request was done and if yes you proceed with loading into your FTN95 program database's data. I used the intermediate medium -- harddrive and RAMDRIVE, whatever the databse can do

silicondale,

I've done a great deal of interfacing with 'C' routines and FTN95. I ported the Proj4 coordinate transformation library and libHaru PDF creator software and have interfaced it with my FTN95 code. It works great. And it takes a bit of additional work to make the interfaces simple and easily debuggable. I'll give an example.

The PDF library (libHaru) is really a fairly low-level library. You have to wrap some of your code around it to make the library useful. For creating graphs, etc. even more code has to be wrapped around it. I chose to make the interface for report generation (simple text page formatting) usable through FTN95. I have a PDF create routine that takes a few arguments (filename and the like), a line-output routine (recognizes Fortran line printer commands), and a closing routine once the output is done.

Could I have written the interface to all the lower level routines? Yes, but it would have take a LOT of extra time.

I'd be happy to share some of what I have found to be successful, if you'd like. I'd post the FTN side and the 'C' side along with some of the 'C' code to show why I don't go low level from the FTN unless I have to.

Many thanks, both, for your helpful comments and suggestions! Dan - the SQLite decision is already made, though it is possible (it's always possible!) that we may need to re-visit this question later. I now have a workaround that will transfer SQLite tables to and from my Fortran environment, though a C interface could make it faster.

One of the problems for interfacing Fortran with (any) database management systems is that of dealing with data structures that are not known in advance. The fewer data types to deal with, the better, and this is where SQLite scores. The only significant problem is that SQLite text fields are of undefined length.

Wahorger - many thanks for your offer. Have you had to edit any of the C code or have you used the libraries as supplied? (I don't have a C compiler, and have never programmed in C, so would rather not have to modify any of the C code!). Your PDF create application sounds interesting in itself: I have done some Fortran low-level device and interchange format coding myself in the past and know how fiddly it can be.

I am proficient in 'C' and C++, but did not have to modify the libraries to get them to work. The Proj4 library used a coding technique that I had to 'overcome' at compile time - long story.

Some experience with the language is helpful, certainly. If you wish to create a DLL from the 'C' code, then some additional knowledge and expertise is definitely helpful.

You actually DO have a 'C' compiler (called SCC.EXE) supplied with the FTN95 compiler. That is what I am using for almost all the 'C' code that I interface with. I have one library that SCC will not successfully compile, so I use CodeLite as the GUI to build a DLL using the MINGW64 'C' compiler.

The PDF routine is written as a FORTRAN driver [OPEN(DRIVER=...)]. So all the FORTRAN side needs to know is that a WRITE() statement does exactly what you would expect it to do, and all the low-level details are hidden from the application. Using the DRIVER= has opened many other uses with my applications. There are limitations using a driver, but all in all, it works great!

Many thanks for the advice and encouragement. I had totally forgotten that SCC came with the FTN95 system: never used it. Now wondering what else I have that I've also never used 😃

Thanks John - I guess that is the question I was struggling to answer. Maybe I misunderstood something, but I finished up with a lot of unresolved references. Will look again.

Quoted from wahorger silicondale,

I've done a great deal of interfacing with 'C' routines and FTN95. I ported the Proj4 coordinate transformation library and libHaru PDF creator software and have interfaced it with my FTN95 code. It works great. And it takes a bit of additional work to make the interfaces simple and easily debuggable. I'll give an example.

The PDF library (libHaru) is really a fairly low-level library. You have to wrap some of your code around it to make the library useful. For creating graphs, etc. even more code has to be wrapped around it. I chose to make the interface for report generation (simple text page formatting) usable through FTN95. I have a PDF create routine that takes a few arguments (filename and the like), a line-output routine (recognizes Fortran line printer commands), and a closing routine once the output is done.

Could I have written the interface to all the lower level routines? Yes, but it would have take a LOT of extra time.

I'd be happy to share some of what I have found to be successful, if you'd like. I'd post the FTN side and the 'C' side along with some of the 'C' code to show why I don't go low level from the FTN unless I have to.

Sorry to resurrect this, but was searching for libharu and silverfrost. Does your wrapper allow printing also of images? It would be of great help if you could share it... Thanks

StamK,

My wrapper does not perform the image ingestion, but the lower level routines to do that are definitely in the library. All it would require is your routine that would do the interface for you. Usually, those routines are a few lines of code. My libharu version is 2.3.0, so the Wiki is current.

If memory serves me (sometimes, it does not), the image gets loaded from a file, the position for the image is set on the page, then the image is rendered to the page. The interface routine can be as simple as 4 arguments, the filename and the length of the filename string, the 'X' and 'Y' coordinates as integers.

You could get more complicated by parsing the file name to determine the file type (png, jpg, ...) to access the appropriate image load function, or provide a separate interface to the appropriate routines in your FTN95 code.

You should get the image size (useful to know how much of the page will be used). Again, a simple interface to return a value using the REF attribute in the 'C' definition within your FTN95 program.

As I said years ago, I'm happy to share the code I have created for the interface, as well as the library and SCC compile steps/Plato Project that can compile the library and the interface 'C' routines.

Wahorger, many thanks for the quick reply. It would be good if you could share your code as it has been proven to work, and then from there I can add other calls to routines from libHaru. I was also wondering if it is possible to make 'long' PDF pages with libharu or whether it automically breaks them up. Thanks again

Nothing is automatic with libHaru! You'll see in the routines that just generate text reports that I have to place every single line, account for likely margins, etc.

I will also pass along the device driver that interfaces with FTN95, allowing you to use FORTRAN WRITE's to output text. While that might not meet your needs, it is a good way to learn about device specific drivers. If I can be of help explaining any nuances of the code or how FTN95 handles things with the library, I'll be happy to tell you.

I will reply to this message when I get the pieces loaded into a ZIP for you, and place in my DropBox.

Here is a link to the library and some support routines. https://www.dropbox.com/s/3qphu44ximt2291/libHaru-FTN95.zip?dl=0

If you have questions, I'll be happy to answer them. Or, you can PM me and we can take it offline.

Bill

Many thanks Bill, will try it out (and many thanks for the clear instructions!) Stam