Michael Hooker wrote: >I simply followed what you yourself say in the Help file: "The native API >creates the smallest and fastest code and is therefore best suited for >time-critical applications." So my first suggestion is that you amend the >Help to say what the real difference is :) Suggested amendment: Compared to the native API, the object layer adds a small executable footprint and minor execution time overhead. Most applications will not notice this overhead with average to large sized databases because disk access will claim most system resources. The object layer performance penalty will hardly exceed 1 or 2 percent, even for very small database sizes. >Ralf wrote: >>If you know there will be just one row, call sqlite3_step just once.< > >Well that's what I did. I just wondered if there was a better way. According >to the Help SQLITE_ROW means "sqlite3_step has *another* row ready." This is >one of the reasons I find using _step confusing for accessing a single record. >"step" implies stepping through a set of records, and "another" clearly >implies that one has been found already and another, ie a second result, is >available. Maybe the Help should simply say "sqlite3 has returned a row of >data" if that is what it in fact means. You refer to just the SQLITE_ROW documentation, but there is more: The sqlite3_step() function covers roughly two printed pages. I believe that just this paragraph explains it fairly well: "If the SQL statement being executed returns any data, then SQLITE_ROW is returned each time a new row of data is ready for processing by the caller. The values may be accessed using the column access functions. sqlite3_step() is called again to retrieve the next row of data." >Ralf wrote: >>sqlite3_check is a convenience function...If you omit sqlite3_check, you can >>get away without try...except.< > >Er.. Let's see if I have this right. sqlite3_check is a system that causes >returned error codes to result in exceptions, and an exception will normally >produce a modal message box which interupts your program until you press OK. >Try...except causes exceptions arising in the try block to skip over any >remaining statements to the except block before they manifest themselves. If >user code in the except block catches the error, the user code can substitute >a process which will replace the process which would normally happen (ie the >message box). If omitting sqlite3_check avoids the modal dialog box popping >up in the first place, then it seems to me that it's more convenient to omit >the "convenience function" :) Large parts of Delphi are based on exceptions, and their usage is well described in the Delphi help. Therefore I believe that library authors can just assume that Delphi programmers are familiar with the concept. You might not find exceptions convenient, but most people do. Therefore DISQLite3 makes it easy to raise exceptions on errors via sqlite3_check(). For them, sqlite3_check() is a concenience function. Btw: sqlite3_check() is not a new concept, you fill find similar functions in other libraries, including the VCL. If you are seeing a unwanted message box as a result of an exception, it means that your application does not handle exceptions properly. Unhandled exceptions usually indicate some sort of error and should not happen unintentionially. You are welcome to omit sqlite3_check() to avoid potential message popups. But if you do, you are responsible to add your own error checks! Depending on what you are aiming for, this can indeed be more convenient. >Ralf wrote: >>Last, but not least: A big time-saver is to avoid sqlite_prepare for each >>query you are running. Instead, prepare ahead of time and sqlite3_bind new >>values and reuse the statment as needed. When done with it, immediately call >>sqlite3_reset to remove any database locks. sqlite3_finalize only before you >>close the database / application< > >OK... But I need more help on sqlite_prepare_v2 and sqlite3_bind please! These functions are documented at length in the DISQLite3 Help. You can look it up through the index easily! > I can't make head nor tail of the Drive_Catalog example, it's much too > complex for me, and the Help is too obscure. Could we please have a <simple> > example of how to prepare and use 'SELECT * FROM TableName WHERE SearchColumn > = "SearchString"', where SearchString is an ordinary Delphi string variable? > And then an example of how to bind the string in to the prepared statement > when required. It doesn't have to be a working program, just a couple of > template code snippets will do. The basic principle is simple. Please take a look at the small demo project attached. >Ralf wrote: >>Any suggestions to improve the code and make it more readable and informative >>are highly welcome!< > >My suggestion is that code examples are included in the Help, as in the Delphi >Help, or in another document. Trying to demonstrate a simple concept by hiding >it in a working program just does not work for many of us. It is too much to >expect us to work out how the entire program works just to see how one aspect >of it is coded. Personally, I simply cannot find the bits I need in >Drive_Catalog. Code snippets, as published on many helpful Delphi websites, >really do explain things a lot better, and you can try them out in your own >skeleton programs which you understand because you wrote them. This minimises >the learning curve for those of us who don't learn so quickly now our hair has >gone white (or gone completely). You can teach an old dog new tricks, but >it's much easier if you teach it one trick at a time in an environment it is >comfortable with :) Having set out the snippets and explained them, you can >then refer to the demo programs in which they are used to see a working >example (and give the line numbers). Interesting point, I will think more about this! >I have the same trouble with nearly all 3rd party components. The authors >often use the demos to show off all the complicated things their component >will do, and it is like trying to learn to swim in the deep end of the >swimming pool. Writing good demos is not easy because they must be a feature and a learning experience IMO. I agree that the DISQLite3_Drive_Catalog is quite advanced, but DISQLite3 has some really unique features and they must be demonstrated somewhere for users to see them! On the simple side, I have added a few small and lean projects, too, like the DISQLite3_StringGrid and DISQLite3_StringList. What's maybe really missing is a demo overview document to help users pick the right demo for their level of experience. Many thanks for sharing your suggestions. I hope to find the time to put them into practice soon! Ralf
{ How to reuse a prepared SELECT statement with DISQLite3. A statement is prepared just once, but is used for different queries by binding different values to it. Run the DISQLite3_Create_Table demo first to create the required table. Visit the DISQLite3 Internet site for latest information and updates: http: //www.yunqa.de/delphi/ Copyright(c)2005 - 2008 Ralf Junker, the Delphi Inspiration < Delphi@xxxxxxxx > -------------------------------------------------------------------------------} program DISQLite3_SELECT_with_bind; {$I DI.inc} {$I DISQLite3.inc} {$APPTYPE CONSOLE} uses SysUtils, DISQLite3Api, DISQLite3_Demos_Common in '..\DISQLite3_Common_Units\DISQLite3_Demos_Common.pas'; //------------------------------------------------------------------------------ { This procedure just prints out the results returned from a prepared statement. After all results are printed, the statement is reset. } procedure ExecutePreparedStatement(const Stmt: TDISQLite3StatementHandle); var i: Integer; begin while sqlite3_check(sqlite3_step(Stmt)) = SQLITE_ROW do begin for i := 0 to sqlite3_column_count(Stmt) - 1 do begin if i > 0 then Write('|'); { We optimize the data output according to the column type. } case sqlite3_column_type(Stmt, i) of SQLITE_INTEGER: Write(sqlite3_column_int64(Stmt, i)); SQLITE_FLOAT: Write(sqlite3_column_double(Stmt, i)); SQLITE_TEXT: Write(sqlite3_column_str(Stmt, i)); SQLITE_BLOB: Write('BLOB'); // Write BLOB indicator. SQLITE_NULL: Write('NULL'); // Write NULL indicator. end; end; WriteLn; end; { Reset the statement immediately after it has been used. This clears any pending database locks and prepares the statement for another round of sqlite3_step(). } sqlite3_check(sqlite3_reset(Stmt)); end; var Stmt: TDISQLite3StatementHandle; begin WriteLn('SQLite Version ', sqlite3_libversion); WriteLn; { Disable FPU exceptions. No need to restore, setting is process specific. } Set8087CW($133F); try Open_Demo_Database; try { Pepare an SQL statment for later use. Notice that this statement is not finalized until just before the database closes. This allows to reuse it during the application lifetime without repreparing. Since preparing SQL statements is quite time consuming, reusing statements speeds up performance, especially when running it many times. } sqlite3_check(sqlite3_prepare_v2( DB, // Handle of the Demo.db3 database file. 'SELECT FirstName, LastName FROM People WHERE FirstName = ?', -1, // Length of SQL statement, pass -1 to autodetect @Stmt, // Variable for the prepared SQL statement nil), // Variable to store beginning of next SQL statement or nil if not needed. DB); try { Bind a value to the 1st parameter of the prepared statement. This effectively replaces the ? parameter with the string value 'Albert'. } sqlite3_bind_str(Stmt, 1, 'Albert'); ExecutePreparedStatement(Stmt); { Do like above, but use the string value 'Leonardo' this time. } sqlite3_bind_str(Stmt, 1, 'Leonardo'); ExecutePreparedStatement(Stmt); finally { All prepared statements must eventually be finalized before closing the database. } sqlite3_finalize(Stmt); end; finally Close_Demo_Database; end; except on e: Exception do WriteLn(e.Message); end; WriteLn; WriteLn('Done - Press ENTER to Exit'); ReadLn; end.