The reason for not using a C wrapper is:
1. I don't write C.
2. All the examples I've seen only send their SELECT output to stdout, which I find very unuseful, instead of allowing you to read it one row at a time into your program.
3. I still don't write C <G>.
My plan is to create a library of GnuCOBOL callable routines. More than likely they will have a macro wrapper to make them easier to use.
It looks as if my open routine works OK. It creates a new file that the SQLite3 command is happy with (the sqlite_master table exists and you can access it; you can create tables and insert/select/update rows).
All of my other routines, even close, return a status 21 which is "Attempting to use an SQLite interface in an undefined/unsupported way.".
I think that the error might be caused by the way I have defined (s9(4) comp) the variable to hold the 'handle' to the database which is returned by the open and used by most of the other routines.
I have attached a copy of my test program source and request that somebody who is conversant with calling C routines from GnuCOBOL have a look at it and give me a hint on what my mistake is.
I don't know the sqlite C API well at all and I won't be able to see errors with this but on a 64 bit system I have had good luck with usage is binary-long, maybe give that a try ...
-Pat
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
one other thing... I have patterned most of my calls in to C after Brian's work. Even if your not into GTK you might find some good tips looking through his work.
I threatened to try and code up user functions for SQLite3 a year or so ago, but that todo hasn't bubbled its way to the top of the pile yet.
One of the main things when interfacing to libraries is the issue of C's view of the handle versus the COBOL view of the handle.
Your sqlite3-open works fine, as the C library expects and address of where to store a handle.
BY REFERENCE db-handle
Most calls after that, expect the contents of that db-handle, not the address of the COBOL field. Use
BY VALUE db-handle
in almost all later calls (including sqlite3-close) and you'll be fine. C wants the "C view" of the handle, not the COBOL by reference address.
I haven't changed all the code yet, but tried quickly with 2 things.
Changed db-handle to POINTER and (or here on a 64 bit machine s9(18) comp would accomplish the proper sizing. I find pointer easier, as it just does the right thing.
(I didn't modify any other code, so the handle management is still wrong for the close call etc...)
You are on the right track, Robert. Make changes like the one listed above for prepare_v2 and you'll be wielding sqlite3 like its a nodachi ready to take on the hordes.
Cheers,
Brian
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
A few minutes making your suggested changes and the addition of some more code has left me with a program that can do the following:
Open an existing or new database.
Create a table.
Insert data into the table.
Select data from the table (the routine reports that data has been returned although it has not done anything with it, its sitting in a 'virtual' array waiting to be retrieved).
Close the database (automatically saving it if it is new).
The routine code used to do items 2 & 3 is the same. The only difference is in the SQL statement that you pass to it. This means that deleting and updating data only requires a different SQL statement.
Now to do the hard part. Extract the data from the 'virtual' array and return it to the program one row at a time.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Nice to hear, Robert. It gets flowing usually, and then the brain scratchers surface. :-)
For that hard part. USAGE POINTER BASED is your friend. Take a pointer from a sqlite3 function, set the address of your based pointer to it, and you can fairly effectively dereference C addresses and then wield the data from COBOL, by setting the address of a
01 sql-row pic x(1024) based.
field. Then string the data delimited by null into an actual working store field. Then unstring that into the row's columns. An issue with SQLite; It is a willy nilly typed database. Just because you create a field as integer, does not mean you can only stash integers in it. Each row can have different types and different lengths for each and every column. The data typing and validation is squarely on the shoulders of the application writer, the engine will not complain when variant data is stored in a field (except for a single primary key integer rowid field, all fields can hold any data, and I think recent releases even got rid of that rule). Having said that, if you always write 20 characters to the second field, you will always get 20 characters back. (Until someone opens your table and inserts something different in the column). What you see in .schema is just a (very weak) suggestion and has no teeth in SQLite tables.
And one point on based pointers. (And I've learned the hard way a few times). If you set a pointer address and "do things" with it, like SET addr UP BY or DOWN BY you may be modifying data you don't own, and that can lead to some real head scratching. So, it's usually worthwhile to include a third field, not based, that you set from the based pointer and then UP and DOWN with that instead. You'll be safely modifying working-storage and not some external library's internal pointer.
As a for instance. I was mucking around trying to display the process environment space, environ is a pointer to an array of pointers, so I used the BASED pointer idiom. Then happily traversed the array, and displayed the strings. Then smiled and posted code.
Turns out the code was modifying the process global environ pointer. And other programs in the process space went kablooey. Mysteriously and quite loudly, kablooey. Lesson learned, then forgotten, then learned again, and forgotten a second time. I think it has stuck now.
USAGE POINTER BASED is to be treated as a readonly field. :-)
Cheers
Last edit: Brian Tiffin 2015-11-16
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
One of those 'brain scratchers' has just surfaced (speak of the devil and he WILL appear).
I've spent about 2 hours on trying to work out how I get the value from a text column. Have worked out part of it but the last part is being a bit of a b*#$h.
where stmt is usage pointer, iCol is s9(4) comp & tCol is x(1024). iCol is the number of bytes in the column as returned by sqlite3_column_bytes.
The conclusion I have come to is that sqlite3_column_text is actually returning an address and I have to move the data starting at that address, for iCol bytes, to tCol. Am I getting close?
Last edit: Robert W.Mills 2015-11-16
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
stmt, you need to pass BY VALUE. It was by reference during prepare but now the engine wants the content of your pointer, not the address of your pointer.
And it returns a pointer, which you'll need to dereference, ala
sqlite-data will be very transitory, so get it out and into working-storage as soon as you can. By that I mean, don't leave it lying around while you do other work, best to snag it out immediately after the call. Once its in working-store at safer-data you can trust it not to shuffle out under foot because somebody sneezed. Oh, and don't modify sqlite-data, it's memory owned by the library and changing it is a no-no. Treat it as read only. Well fenced code might actually
setaddressofsqlite-datatoNULL
as soon as the STRING operation was complete, to protect the SQLite library space from unintentional change.
that actually returns a count of characters printed, but just about everybody ignores that (although RETURN-CODE will have the number if you need it or are curious).
And explaining the printf "%s" displays a null terminated string. the x"0a00" passes in a newline and null which in C usually looks like "%s\n". COBOL needs to hardcode the 10, and the terminating 0 for the printf format spec. printf is a "vararg" kind of function. The spec determines how many values you need to put in the call frame. In the %s case, just the one address will do. But you could
call "printf"usingbycontent "sq3: %s and %d"&x"0a00"byvaluepColbyvalueiCol
etc... the % marker in the spec determines how many other fields to pass (in general, there are edge case, but by and large, the number of % symbols determines how many fields to pass).
And don't pass iCol to a %s, as C will then try to dereference your integer and try to access memory at (for instance) address 1, which is a kablooey address.
Cheers,
Brian
Last edit: Brian Tiffin 2015-11-16
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
10 minutes after my last post on this I decided to add the code to get the column names. The second line of the sqlite3_column_name page reads
"The sqlite3_column_name() interface returns a pointer to a zero-terminated UTF-8 string..."
The moment I saw this I understood. If they had inculded this sentence in the page for sqlite3_column_text it would have saved a lot of head scratching.
To top it all I lost my broadband connection, just got it back :), and couldn't let you know I'd found the answer.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Yeah, some API docs require other API docs which lead back to docs about stuff you are already supposed to know, but couldn't know because the first page led to the ...
:-)
Richard Hipp is a genius, and high level usage materials are usually top-notch, and world-class easy to understand, but internals can take a little digging. He may view the world as being two types, grandma and guru. But he seems to forget to leave the ladder grandma needs to climb to get to see the guru. Fossil is the same way. Nice and easy to use, but to make a change or look under the hood means taking apart the whole car.
And no worries on not being able to respond earlier, which let me make that intermediate post. It might help someone else in the not too distant future.
Cheers
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Find attached the latest version of my test program before I turn it into a library of some sorts. Feel free to give it a try and make changes. Please let me know if you find any problems. There is loads of comments (about 80..90 lines worth at the end) ala Brian style.
prompt$ ./SQL3I4GC-test
line_no line_text
------- ------------------------------------------------------------
0001 this is line 1
0002 this is line 2
0003 this is line 3
-- End of Report --
Nice. The GnuCOBOL world gets a little bit bigger.
I've attached the output from
make SQL3I4GC.html
after two small edits to let rst deal with some indents/dedents (in the description of IS_DB_OPEN) and adding a !rst-marker! onto the >>ELSE line.
Inline docs are awesome.
By the by, you don't really need to use the underscores for the COBOL names, go dash, easier on the eyes and the fingers. And don't worry about being able to get at the names from libraries, GnuCOBOL does reasonable mangling, and COBOL folk don't even have to worry, as the naming during call is all handled as well.
I already had a foo table in test.sdb, from earlier testing. You should probably just prepare a "drop table foo;" statement to avoid
*E* Compile of SQL statement failed.
table foo already exists
SQLite3 Status Code: +0001
for your demo, seeing as your just going to create it again anyway for the next run. Or, to accumulate records, go with
One last one, though this could be make work. Dave Nicolette has written up a marvelous cobol-unit-test framework that lets you setup unit tests of individual paragraphs.
All in a nice test domain specific language that reads like COBOL. A few minutes to set up (ok, maybe an hour to get to grips with it), a few minutes to write the test cases, and you'll have automated regression testing at whim from that point forward. Worth a look I think, Robert. http://opencobol.add1tocobol.com/gnucobol/#what-is-cobol-unit-test
My answer to your question is "I don't know. I no longer have access to a Windows box (moved from WinXP to Linux Mint last year) and have never used VS.".
I hope that somebody who uses GnuCOBOL and VS sees your question and responds.
Anybody?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Just a hint, especially for Robert: I'd highly suggest to use CALL STATIC 'cfunc' as this resolves all dependencies at compile time (needs a 2.x cobc one).
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Time is ticking away... this was just merged recently (after rc1)... another thing that I want to add is "include C headers", I may check this week if this works fine.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Just FYI: The generation of function declarations for removing C compiler warnings was done by Ron in [r581] and [r582]. It's currently not merged into 2.0 (will be, together with the missing test cases).
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Looking more and more forward to SQLite in GnuCOBOL. Richard Hipp is a genuis. Recent addition of Common Table Expressions (CTE) makes for some wonky fun SQL.
I'm trying to use SQLite3 natively with GnuCOBOL.
The reason for not using a C wrapper is:
1. I don't write C.
2. All the examples I've seen only send their SELECT output to stdout, which I find very unuseful, instead of allowing you to read it one row at a time into your program.
3. I still don't write C <G>.
My plan is to create a library of GnuCOBOL callable routines. More than likely they will have a macro wrapper to make them easier to use.
It looks as if my open routine works OK. It creates a new file that the SQLite3 command is happy with (the sqlite_master table exists and you can access it; you can create tables and insert/select/update rows).
All of my other routines, even close, return a status 21 which is "Attempting to use an SQLite interface in an undefined/unsupported way.".
I think that the error might be caused by the way I have defined (s9(4) comp) the variable to hold the 'handle' to the database which is returned by the open and used by most of the other routines.
I have attached a copy of my test program source and request that somebody who is conversant with calling C routines from GnuCOBOL have a look at it and give me a hint on what my mistake is.
Note: I am developing this on a 32-bit system.
Last edit: Robert W.Mills 2015-11-16
Hi Robert
I don't know the sqlite C API well at all and I won't be able to see errors with this but on a 64 bit system I have had good luck with usage is binary-long, maybe give that a try ...
-Pat
one other thing... I have patterned most of my calls in to C after Brian's work. Even if your not into GTK you might find some good tips looking through his work.
I think I have more of his current code on my machine but here is a link I could find at the moment:
https://sourceforge.net/p/open-cobol/contrib/HEAD/tree/trunk/tools/cobweb/cobweb-gtk/
Looks nice Robert, and please continue. :-)
I threatened to try and code up user functions for SQLite3 a year or so ago, but that todo hasn't bubbled its way to the top of the pile yet.
One of the main things when interfacing to libraries is the issue of C's view of the handle versus the COBOL view of the handle.
Your sqlite3-open works fine, as the C library expects and address of where to store a handle.
Most calls after that, expect the contents of that db-handle, not the address of the COBOL field. Use
in almost all later calls (including sqlite3-close) and you'll be fine. C wants the "C view" of the handle, not the COBOL by reference address.
I haven't changed all the code yet, but tried quickly with 2 things.
Changed db-handle to POINTER and (or here on a 64 bit machine s9(18) comp would accomplish the proper sizing. I find pointer easier, as it just does the right thing.
Gives
Thats ok, as there is no foo to select on. After running
testsqlite3 now runs as
(I didn't modify any other code, so the handle management is still wrong for the close call etc...)
You are on the right track, Robert. Make changes like the one listed above for prepare_v2 and you'll be wielding sqlite3 like its a nodachi ready to take on the hordes.
Cheers,
Brian
Brian,
Thanks for the encouragement and the info.
A few minutes making your suggested changes and the addition of some more code has left me with a program that can do the following:
The routine code used to do items 2 & 3 is the same. The only difference is in the SQL statement that you pass to it. This means that deleting and updating data only requires a different SQL statement.
Now to do the hard part. Extract the data from the 'virtual' array and return it to the program one row at a time.
Nice to hear, Robert. It gets flowing usually, and then the brain scratchers surface. :-)
For that hard part. USAGE POINTER BASED is your friend. Take a pointer from a sqlite3 function, set the address of your based pointer to it, and you can fairly effectively dereference C addresses and then wield the data from COBOL, by setting the address of a
field. Then string the data delimited by null into an actual working store field. Then unstring that into the row's columns. An issue with SQLite; It is a willy nilly typed database. Just because you create a field as integer, does not mean you can only stash integers in it. Each row can have different types and different lengths for each and every column. The data typing and validation is squarely on the shoulders of the application writer, the engine will not complain when variant data is stored in a field (except for a single primary key integer rowid field, all fields can hold any data, and I think recent releases even got rid of that rule). Having said that, if you always write 20 characters to the second field, you will always get 20 characters back. (Until someone opens your table and inserts something different in the column). What you see in .schema is just a (very weak) suggestion and has no teeth in SQLite tables.
And one point on based pointers. (And I've learned the hard way a few times). If you set a pointer address and "do things" with it, like SET addr UP BY or DOWN BY you may be modifying data you don't own, and that can lead to some real head scratching. So, it's usually worthwhile to include a third field, not based, that you set from the based pointer and then UP and DOWN with that instead. You'll be safely modifying working-storage and not some external library's internal pointer.
As a for instance. I was mucking around trying to display the process environment space, environ is a pointer to an array of pointers, so I used the BASED pointer idiom. Then happily traversed the array, and displayed the strings. Then smiled and posted code.
Turns out the code was modifying the process global environ pointer. And other programs in the process space went kablooey. Mysteriously and quite loudly, kablooey. Lesson learned, then forgotten, then learned again, and forgotten a second time. I think it has stuck now.
USAGE POINTER BASED is to be treated as a readonly field. :-)
Cheers
Last edit: Brian Tiffin 2015-11-16
One of those 'brain scratchers' has just surfaced (speak of the devil and he WILL appear).
I've spent about 2 hours on trying to work out how I get the value from a text column. Have worked out part of it but the last part is being a bit of a b*#$h.
The C code says
I translated this to
where stmt is usage pointer, iCol is s9(4) comp & tCol is x(1024). iCol is the number of bytes in the column as returned by sqlite3_column_bytes.
The conclusion I have come to is that sqlite3_column_text is actually returning an address and I have to move the data starting at that address, for iCol bytes, to tCol. Am I getting close?
Last edit: Robert W.Mills 2015-11-16
stmt, you need to pass BY VALUE. It was by reference during prepare but now the engine wants the content of your pointer, not the address of your pointer.
And it returns a pointer, which you'll need to dereference, ala
sqlite-data will be very transitory, so get it out and into working-storage as soon as you can. By that I mean, don't leave it lying around while you do other work, best to snag it out immediately after the call. Once its in working-store at safer-data you can trust it not to shuffle out under foot because somebody sneezed. Oh, and don't modify sqlite-data, it's memory owned by the library and changing it is a no-no. Treat it as read only. Well fenced code might actually
as soon as the STRING operation was complete, to protect the SQLite library space from unintentional change.
For debugging, this can come in handy.
that actually returns a count of characters printed, but just about everybody ignores that (although RETURN-CODE will have the number if you need it or are curious).
And explaining the printf "%s" displays a null terminated string. the x"0a00" passes in a newline and null which in C usually looks like "%s\n". COBOL needs to hardcode the 10, and the terminating 0 for the printf format spec. printf is a "vararg" kind of function. The spec determines how many values you need to put in the call frame. In the %s case, just the one address will do. But you could
etc... the % marker in the spec determines how many other fields to pass (in general, there are edge case, but by and large, the number of % symbols determines how many fields to pass).
And don't pass iCol to a %s, as C will then try to dereference your integer and try to access memory at (for instance) address 1, which is a kablooey address.
Cheers,
Brian
Last edit: Brian Tiffin 2015-11-16
10 minutes after my last post on this I decided to add the code to get the column names. The second line of the sqlite3_column_name page reads
"The sqlite3_column_name() interface returns a pointer to a zero-terminated UTF-8 string..."
The moment I saw this I understood. If they had inculded this sentence in the page for sqlite3_column_text it would have saved a lot of head scratching.
To top it all I lost my broadband connection, just got it back :), and couldn't let you know I'd found the answer.
Yeah, some API docs require other API docs which lead back to docs about stuff you are already supposed to know, but couldn't know because the first page led to the ...
:-)
Richard Hipp is a genius, and high level usage materials are usually top-notch, and world-class easy to understand, but internals can take a little digging. He may view the world as being two types, grandma and guru. But he seems to forget to leave the ladder grandma needs to climb to get to see the guru. Fossil is the same way. Nice and easy to use, but to make a change or look under the hood means taking apart the whole car.
And no worries on not being able to respond earlier, which let me make that intermediate post. It might help someone else in the not too distant future.
Cheers
Find attached the latest version of my test program before I turn it into a library of some sorts. Feel free to give it a try and make changes. Please let me know if you find any problems. There is loads of comments (about 80..90 lines worth at the end) ala Brian style.
More cool, Robert.
Worked the charm here.
prompt$ ./SQL3I4GC-test line_no line_text ------- ------------------------------------------------------------ 0001 this is line 1 0002 this is line 2 0003 this is line 3 -- End of Report --Nice. The GnuCOBOL world gets a little bit bigger.
I've attached the output from
after two small edits to let rst deal with some indents/dedents (in the description of IS_DB_OPEN) and adding a !rst-marker! onto the >>ELSE line.
Inline docs are awesome.
By the by, you don't really need to use the underscores for the COBOL names, go dash, easier on the eyes and the fingers. And don't worry about being able to get at the names from libraries, GnuCOBOL does reasonable mangling, and COBOL folk don't even have to worry, as the naming during call is all handled as well.
I already had a foo table in test.sdb, from earlier testing. You should probably just prepare a "drop table foo;" statement to avoid
for your demo, seeing as your just going to create it again anyway for the next run. Or, to accumulate records, go with
One last one, though this could be make work. Dave Nicolette has written up a marvelous cobol-unit-test framework that lets you setup unit tests of individual paragraphs.
All in a nice test domain specific language that reads like COBOL. A few minutes to set up (ok, maybe an hour to get to grips with it), a few minutes to write the test cases, and you'll have automated regression testing at whim from that point forward. Worth a look I think, Robert.
http://opencobol.add1tocobol.com/gnucobol/#what-is-cobol-unit-testEdit: oops, old link, habit...
http://open-cobol.sourceforge.net/faq/#what-is-cobol-unit-test will be the new home, as of a few minutes ago.
Cheers
Last edit: Brian Tiffin 2015-11-18
Looks interesting, but what changes do I have to implement to run if my GnuCOBOL is compiled with MS Visual Studio 2012?
Stefan,
My answer to your question is "I don't know. I no longer have access to a Windows box (moved from WinXP to Linux Mint last year) and have never used VS.".
I hope that somebody who uses GnuCOBOL and VS sees your question and responds.
Anybody?
As the GNU/Linux tectonics are:
The Windows tectonics are
In both cases it is likely a good idea to add
-L /path/to/sqlite/libto cobc command line.Just realised that a function to test if the database is open/closed is not required. Just add the following 88 level to the db-handle variable:
then all you have to do is:
or
KISS is your friend.
Last edit: Robert W.Mills 2015-11-19
Just a hint, especially for Robert: I'd highly suggest to use
CALL STATIC 'cfunc'as this resolves all dependencies at compile time (needs a 2.x cobc one).Simon, Am getting the following messages when I add STATIC after the CALL
If I remove the STATIC everything is OK.
Last edit: Robert W.Mills 2015-11-19
I thought we have fixed that, maybe its one of the missing merges from reportwriter-branch, I'll check and report in the next days.
Simon, hold off on that check. I'm still on r411, downloaded r658 but not had a chance to update to it yet.
Time is ticking away... this was just merged recently (after rc1)... another thing that I want to add is "include C headers", I may check this week if this works fine.
Thanks for mentioning this Simon, this will be really valuable.
-Pat
Just FYI: The generation of function declarations for removing C compiler warnings was done by Ron in [r581] and [r582]. It's currently not merged into 2.0 (will be, together with the missing test cases).
Any chance of the time frame for this along with the RW addons?
Should point out that on commits for 664 I had failures during NIST
testing on both gnu and rw branches.
Mod edit: remove some reply-to
Last edit: Brian Tiffin 2015-11-20
Looking more and more forward to SQLite in GnuCOBOL. Richard Hipp is a genuis. Recent addition of Common Table Expressions (CTE) makes for some wonky fun SQL.
and
prompt$ sqlite3 sqlite> .read mandle.sql ....# ..#*.. ..+####+. .......+####.... + ..##+*##########+.++++ .+.##################+. .............+###################+.+ ..++..#.....*#####################+. ...+#######++#######################. ....+*################################. #############################################... ....+*################################. ...+#######++#######################. ..++..#.....*#####################+. .............+###################+.+ .+.##################+. ..##+*##########+.++++ .......+####.... + ..+####+. ..#*.. ....# +.Fractal SQL.
Cheers,
Brian