Just a quick update to let you know what has happened over the last few days. I didn't get as much free programming time as I would have hoped but at least I got some stuff done.
- I changed all of the original rserver2.bas (on which sql3server is based) code that deals with sockets and connections from fixed arrays to linked lists. This removes the arbitrary limits that are imposed at the time the sql3server source code is compiled. For example, instead of limiting the number of connections to 10 or 1000, there is no limit because each new connection is dynamically created on-the-fly and added to a linked list.
- sql3server now reads a special configuration database on startup called sql3server.db3 (which is itself an SQLite3 database) in order to retrieve the defined list of servers. In order to define one or more servers you can use the sql3admin.exe program.
- Currently, the sql3admin.exe administration program only allows you to create servers. I still need to add the functionality to create users, passwords, database and user rights, etc... Small steps at a time. :)
- The original rserver2 code had a default port hard coded via an equate. This limitation is now removed. When a server is created you can specify whatever port you wish to use.
- I still have not implemented the buffering of recordset results in "chunks". In ADO, this type of thing is done by specifying a buffer cache when the recordset is created. Records are sent to the client from the server based on this buffer size as needed. I will probably modify the sql3_select funciton to add a fourth parameter "CacheSize" which will represent the number of rows (records) to buffer. This only affects recordsets in situations where you are connected to a server. Recordsets created on local databases always have access to the full recordset immediately.
- Added sql3_fix as a new function that the programmer can call. This function simple double escapes any embedded single quote characters in a string. This helps prevent errors when a single quote is present in a string that forms part of an SQL statement.
- Changed sql3_use to include a 3rd parameter call nUseMode. That parameter specifically controls how the database will be treated (i.e. whether it will be opened for use on the local client computer or if it will be opened for use on the server computer). The values can be %SQL3_LOCAL, %SQL3_REMOTE. If neither of these values is specified then a %ERR_BADUSEMODE error is returned.
That's about it for now. One good side effect of writing the sql3admin program is that I get to find plenty of holes in my new FireFly 3 code so I am fixing those along the way as well. :)
Just to let you know that I am one of those waiting anxiously for the successful completion of this project. From your post you seems to be going quite well. Edwin Knoppert might be of help in these project 'cos he included some SQLite wrapper functions in PwrDev since version one of that IDE. I feel he knows some stuff about SQLite. Just a suggestion.
Thanks Paul, we are all benefiting from your programming genius.