>>1. Is there a way using SQL code alone to open databases, or will >>that always be done by my native (Delphi) code calling the DLL? >> >>I suppose another way to ask that question is: Would "scripts" >>which could manage ALL database activities require embedded >>SQL in another language, or could SQL do the job alone? >> >> >Opening a database is a function of the library, not SQL. You need to >have the database open for there to be something for the SQL to act on.
Good to know. I'd have figured that out eventually, of course, but sooner is better than later. >>2. If I wish to write and test SQL code "live" can/should I open >>one or more databases (however) and manipulate them via SQL at >>leisure, or should each invocation of SQL code open, run, then >>close the files? >> >> >It's perfectly fine to execute multiple queries on a single connection. By "connection" I'm understanding you to mean that my code can call SQLite3_Open and leave it open while I fiddle around with various bits and pieces of SQL, trying this and that INSERT or SELECT or whatever. IOW, I don't have to call _Open in a procedure, run the SQL (however many statements) then call the _Close immediately in the same code block. Yesterday I began to experiment with sending multiple SQL lines to SQLite, testing for error after each line before proceeding. Presumably I should also be testing for completion of the activity of each line before proceeding. Which API calls would be required for a complete assessment of readiness? Would sqlite3_busy_handler be a candidate? I presume callbacks would be involved as well. >In fact I use the sqlite[3] client to test out my SQL while my program >is running. Can you give a rough idea of the algorithm you use to present your SQL to the DLL? So far I've simply given it one line after another, checking for error after each line. With INSERTs but no BEGIN, the data made its way into the file up until the line with the error. With a BEGIN before the SQL INSERTS, I could invoke a ROLLBACK from Delphi on error and nothing would end up in the file. Interesting, but I'm not sure how to use this behavior, i.e., what should be left to SQL and what should be done in Delphi. My goal is to leave as much to SQL code as possible, to have an SQL-friendly environment which gives the user as much SQL syntax and error information as possible, >>3. If I want to exchange data between files, what's the general >>algorithm for opening, doing transactions, then closing? As an >>extension to this question, if one wished to backup data by doing >>a record-for-record copy during application idle time, what would >>be a good strategy for doing this quickly yet safely (given the >>possibility of premature computer power-down)? >> >> >> >The answer here depends on how you want your backups. If you want to >make sure that nothing is committed until all of the data has been >copied, starting a transaction, copying all of the data, then commiting >the transaction would be a good bet. It you're willing to risk >partially incomplete data, smaller transactions for single tables are >suitable and probably provide a better backup of your data. For that >matter, simply copying the file is often suitable. With a compression >utility you can even make a relatively compact backup of your data. Thanks, Clay. I'll keep all this in mind. Nathan
