[SOLVED] Testing with SQLite3DataSet lazarus / 16-Databases ---------------------------------------------------------------------- 1HuntnMan · 2025-04-17 21:44 I have a form that used to use a  dBase 7 table.  All that's left on the form is a DBGrid, DBNav and DBEdits.  Just testing I put a SQLite3DataSet component on the form. Not a lot of documentation on the properties so here goes: Table has AutoIncrement field so set AutoIncrementKey to True, FieldDefs=25 items (it deciphered this), FileName= complete path to the Database file\DatabaseFile.sqlite3, IndexFieldNames = empty, MasterFields = empty, MasterSource = (hmmm would that be the DataSource for the SQLite3DataSet?), Name=SQLite3DSCntks, Options=empty, PrimaryKey=CNTKID, SaveOnClose=False, SaveonRefetch=False, SQL=SELECT * FROM CONTACTS ORDER BY LASTNAME,FIRSTNAME,MI ASC, StoreDefs=False, TableName=CONTACTS, Tag=0. If I attempt to click Active to True, errors "SQLite3DSCntks: SQLITE_ERROR - incomplete input". Anyone got this error before, if so, what was the issue? ---------------------------------------------------------------------- CharlyTango · 2025-04-18 08:10 could it be that you are still looking for ways to save your thought pattern from dBase to SQLite? I began with dBase II to dBase3 as interpreted version or compiled under Clipper. Experiemented and used external compound indices. The last dBase-based system had over 2 million records in one table and reindexing takes several hours. I have never regretted switching to SQL DBMS. The possibilities far outweigh the problems. Just the ability to define queries as views directly in the database and simply query them as tables completely changes the way you work. The only real difference for me is to offer the user a good search option to get to the desired data. Scrolling through tables no longer makes sense with such a concept. What has turned out to be quick in practice is that the user gets a very small list of results with a simple search input, from which he can make a quick analogue selection BTW: SQLite2Dataset/SQLite3Laz is between 8 and 18 years old. Use SQLDB or ZEOS instead ---------------------------------------------------------------------- egsuh · 2025-04-18 09:48 Simply drop the TSQLite3DataSet component on the form, type in Database name and table name, and set it active. You do not need SQL with TSQLite3DataSet. ---------------------------------------------------------------------- CharlyTango · 2025-04-18 12:13 Quote from: egsuh on April 18, 2025, 11:48:03 amSimply drop the TSQLite3DataSet component on the form, type in Database name and table name, and set it active. You do not need SQL with TSQLite3DataSet. This is precisely the strategy of preserving a familiar dBase thought pattern. This leads to record-based thinking instead of quantity- orientated thinking and gets you exactly nowhere these days ---------------------------------------------------------------------- 1HuntnMan · 2025-04-18 20:01 Okay, was just testing on a small Contacts Mgt. system I wrote for my daughter and friends. My other app I'm working on, using TSQLConnection, TSQLTransaction and TSQLQuery.  Working thru that learning SQL. Back in the eighties and nineties I was a Clipper head for awhile, even competed in a programming contest.  We had a Clipper club in Research Triangle Park, NC and started this competition with a local business wanting a particular application, excluding any accounting, to compete from 8am - 6pm. Then the winners were announced after a lot of judgement, I came in 4th the second year.  But, about 9pm, all the competitors were hitting the local pub for a beer or 2, Lol! Thanks, that works for the Contacts system. ---------------------------------------------------------------------- 1HuntnMan · 2025-04-20 13:42 Okay, the SQLite3DataSet, Contacts system is completed, moving back to my other app that's using Connect, Transactions, etc. But, my daughter called and she said that the Notes (MEMO) doesn't allow her to enter or edit past about 280 characters???? The field is defined as a MEMO field and the component TDBMemo, the max. length = 0, which is unlimited.  I opened up the Database with DB Browser (SQLite) and the data is all there but in the app. all Memo field data in all the records that are more than around 280 characters are just cut off, the data is in the field.  Anyone run into this before or any ideas.  On the form it's just a TDBMemo. ---------------------------------------------------------------------- cdbc · 2025-04-20 19:27 Hi There ain't no free lunches, mate! Scrap that sqlite3dataset! You have to make the proper connection-transaction-query combo and e.g.: read and write the memo field with a stream... Regards Benny ---------------------------------------------------------------------- 1HuntnMan · 2025-04-21 16:11 That's the way I converted the data, not with SQLite3DataSet.  I converted the data with SQLite3 CLI.  Connected, Transaction, etc. SQLite3 ContactsDB.sqlite3   .mode CSV;   .import Contacts.csv CONTACTS; As I explained earlier this was a Contacts app I wrote for my daughter.  Which was a dBase database I wrote years ago with Delphi 7.  Anyway, she loves the new version designed with Lazarus. Not many records with large Memo data, i.e. Contact Notes. The data is there. I can see all the data in the Memo fields just browsing with DB Browser (SQLite).  So, even if I use a Connect, Query, Transaction, I think the issue is with the TDBMemo or is it.  I'll find out in a few days because I have another app I'm working on converting from a TDbf database to sqlite and I'm not using SQLite3DataSet, I'm just opening the database and connecting, transactions, query, etc.  I'll see if the issue is SQLite3DataSet because I'm not using it. ---------------------------------------------------------------------- dseligo · 2025-04-21 18:59 Quote from: 1HuntnMan on April 21, 2025, 06:11:40 pmI think the issue is with the TDBMemo or is it. I suggest you make minimal program which shows problem with memo and post it here (along with sqlite3 database file). ---------------------------------------------------------------------- 1HuntnMan · 2025-04-22 20:50 Okay, good idea. I'm going to create a table with 4 fields:  IDNo, LastName, FirstName & a TDBMemo. See if that has an issue. Be back later... ---------------------------------------------------------------------- CharlyTango · 2025-04-23 08:55 Just FYI: Do not give the key fields all the same names. Use a unique name for each table. This makes understanding and the relationships in an SQL database clearer. Especially if tools such as data modelers or reverse engineering are also used. The tools used can then show the relationships well from the names of the fields alone. e.g. "IDPerson" as the name for the primary key field in the table named TPerson. It is then also clear how tables work together if the TAddress table has “IDAddress” as the primary key and “IDPerson” as the foreign key ---------------------------------------------------------------------- 1HuntnMan · 2025-04-24 20:54 Thanks, Charley. Also, attached is a sample small project that's shows the problem with the TDBMemo component not displaying all the data in the Memo field after so many characters.  I didn't count the actual cutoff.  If you use DB Browser (SQLite) or SQLite Expert Personal 5, you can see the data that's actually in the Memo field. If you figure this out, please let me know... Thanks ---------------------------------------------------------------------- egsuh · 2025-04-24 21:59 Hi, to you and your cute daughter. It's strange, but with TSQLite3DataSet, your memo field is read as ftStringType, and its size is set to 255. You can check this with... 1) In the database name of SQLite3DSetCntries, simply type CNTRIESDB.sqlite3, not "c:\users\..." unless the path is correct. 2) At the edit mode of Lazarus (without pressing F9), click "Active" checkbox of the object inspector of SQLite3DSetCntries. 3) If successful (if you can see the 255 characters in the DBMemoNotes), then you'll see "3 items" in the FieldDefs property at the object inspector of  SQLite3DSetCntries. 4) And in the tree structured diagram at the upper part of the object inspector, you'll find "FieldDefs" under "SQLite3DSetCntries". You'll see NOTES node there. If you can't see it, click the "..." button next to the "3 items" of FieldDefs property. 5) If you click "NOTES" node, you'll see that DataType is ftString, and Size is set to 255. 6) Even though you change the size there, it has no effect. If you set the dataset inactive and then active again, the size will restore 255. The quick solution: DO NOT USE TSQLite3DataSet  1) I added SQLite3Connection, TSQLTransaction, and TSQLQuery to your program. Select Cntriesdb.sqlite3 as SQLite3connection's database. Set transaction and sqlquery's database to sqlite3connection1. 2) I set the DataSet of DSCntries to SQLQuery1. 3) In the SQL property of SQLQuery1, typed in "SELECT * FROM COUNTRIESDB" 4) Click sqlquery1's active to true. Then you'll see the whole content in the memo field. In the FieldDefs of TSQLQuery, the NOTES field is still ftString type and its size is 255 but the whole content is displayed. I may attach my program here, but I believe you can do that without it. If you have any difficulty then reply again. Other notes: followings are unnecessary, if you dropped components from component pallette. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TFrmCntriesMgt.BitBtnCloseClick(Sender: TObject);begin  try    SQLite3DSetCntries.Close;  finally    SQLite3DSetCntries.Free;  end;  FrmCntriesMgt.Close;  //FrmCntriesMgt.Free;end;               In the long term, you'll have to move to SQLQueries. But if the final purpose of any application is managing small sized, limited number of tables, then TSQLite3DataSet may be sufficient. It's not the issue of "sollen" nor "sein". Simply issue of convenience. And this is a good example of SQL "select * from tablename" equals opening a whole table. Other note:  I think it's not a good idea to use the name cntriesdb to both sqlite3 database file and a table. You had better distinguish table and db. ---------------------------------------------------------------------- cdbc · 2025-04-25 07:51 Hi Here's another way of doing your 'Countries-App'... -- No Db-aware-components in sight -- Regards Benny ---------------------------------------------------------------------- paweld · 2025-04-25 11:58 @cdbc: lack of all dependencies: bc_memdataset ---------------------------------------------------------------------- Hansvb · 2025-04-25 15:00 QuoteTDBMemo component not displaying all the data in the Memo field after so many characters Hi, I see the same amount of text in a dbmemo field as in a sqlite databrowser app. This seems to be the complete text. But the data grid does cut the text at 256 characters. See attachment, ---------------------------------------------------------------------- cdbc · 2025-04-25 17:40 Hi @paweld: Sorry mate, I must have missed that one... It's 'sowieso' time for it to vanish from 'TLiteDb'  Did you get it to work afterall?!? Regards Benny Edit: fixed & renamed the 'bc.litedb.pas' to use 'TBufDataset' instead and now it runs just fine & dandy... ---------------------------------------------------------------------- paweld · 2025-04-26 11:42 Hi Benny, now it work, thanks. Have a nice weekend. ---------------------------------------------------------------------- 1HuntnMan · 2025-04-27 13:44 Yup ---------------------------------------------------------------------- 1HuntnMan · 2025-04-27 16:14 Okay, my daughter and her fellow employees aren't happy, I had to send here the dBase version that I gave her 3 years ago because of this Memo issue.  I attempted to change the field definitions but whether I save or anything, every field is a string field, even the date fields. I'm going to try to recreate the field defs. via SQLite and maybe it will save.  If I just use the FieldDefs and change the Memo NOTES field from string to memo and the 2 date fields from string to date, then save and run nothing is saved. After I exit the app back to Laz and look at the field defs, they didn't save, they are back to all strings except the primary field which is integer.  I guess I made a mistake trying to fix this.  This is not the table definition. I don't know how anyone would use SQLite3DataSet with this type of behavior.  It just has a mind of it's own. I'm going to try one more thing.  I'm thinking that if you are designing an app with SQLite3DataSet, you have to create your database and tables with SQLite3DataSet before it can operate properly.  I'm copying the data to a backup and then recreating the table with SQLite3DataSet and add one or 2 new records with a lot of memo text and see if it will then SEE the actual data. Mmmmmm, OMG! ---------------------------------------------------------------------- 1HuntnMan · 2025-04-27 16:46 Tried that, if it's broke, then scrap it.  No more SQLite3DataSet, I don't know how anyone would use it esp. if you have a DataBase already defined before you create the app as I did with SQLite3 CLI. SQLite3DataSet totally doesn't see a Memo as a Memo, it converts everything to string fields except it didn't change the Primary Field from integer to string. To much time trying to fix broken so going to just use Connection, Query, Transaction and DataSource. ---------------------------------------------------------------------- paweld · 2025-04-27 17:12 As you have been advised more than once: give up SQLite3DataSet. Use the standard components from the SQLdb palette. Attached is your reworked example ---------------------------------------------------------------------- 1HuntnMan · 2025-04-28 13:53 PAWELD, thanks my friend! Take care... ---------------------------------------------------------------------- egsuh · 2025-04-29 03:17 In this particular case, TSQLite3DataSet is not useful, because it treats memo/blob field as text field with length of 255. But this is a kind of bug of TSQLite3DataSet, I think. The usage of SQL statements instead of direct operations on each table is right direction. We cannot use Dbf files or Paradox files any more. But this does not mean that we have to use SQL Queries in our applications. If possible, I would prefer defining views within database, and accessing it with something like TSQLite3DataSet. The views must be defined using SQL statements, using relations among tables. And any modifications on the views must be handled with triggers. The final application developer don't have to know the internal relationship within database itself. I do not have any similar component like TSQLite3DataSet as I use Firebird. I used to open tables directly using "select * from table where .... ", but now I have defined stored procedures and my Lazarus applications call the procedures even for simple selections like "select * from table1 where pid='100001'". This is because even though I modify the internal structure of database (e.g. splitting the table into two or more tables), I do not have to change any code of Lazarus application. So some of the rationales are misled, I think. Using relational database has large merits instead of using individual tables or for example CSV sheets (as mentioned somewhere else). But this does not mean that the relations are defined within our applications. It had better be avoided, if possible. ---------------------------------------------------------------------- 1HuntnMan · 2025-05-02 14:25 Okay, SQLite3DataSet is gone, thanks from all of you. Question for egsuh, in the countriesApp you sent me I'm using it to retrain my brain away from TDbf and SQLite3DataSet. I'm back revamping my main project from TDbf to SQLite3, not SQLite3DataSet. I'm just using the Connect, Transaction and Query per your demonstration. In the Countries app, you had added the sqlite3laz to the main program file.  I tried that but the compiler errors. My Uses in the opening unit has DB, SQLDB. What is the SQLite3Laz in the main program file, it doesn't error in your demo but it does in mine? ---------------------------------------------------------------------- cdbc · 2025-05-02 15:56 Hi Remove "SQLite3Laz". Regards Benny ---------------------------------------------------------------------- CharlyTango · 2025-05-03 09:16 Quote from: egsuh on April 24, 2025, 11:59:43 pmAnd this is a good example of SQL "select * from tablename" equals opening a whole table. And at the same time an example one should never do dealing with SQL. ---------------------------------------------------------------------- Thaddy · 2025-05-03 10:22 Quote from: egsuh on April 29, 2025, 05:17:36 amIn this particular case, TSQLite3DataSet is not useful, because it treats memo/blob field as text field with length of 255. But this is a kind of bug of TSQLite3DataSet, I think. By no means the only bug. The worst bug is that if the sqlite3 shared library is the wrong version or not installed it crashes the application. If you still want to use it(not like everybody else, almost everybody warned you), store blobs base64 encoded. Best way to do that is with a type helper for encoding and decoding Base64. I guess this component should really be removed, but that would require users from the past to rewrite their application using TSqlDb and family. There is one way to use this component a bit safer (a lot): that is to link sqlite statically so it does not rely on the shared library. ---------------------------------------------------------------------- egsuh · 2025-05-03 11:57 QuoteWhat is the SQLite3Laz in the main program file, it doesn't error in your demo but it does in mine? It is automatically added when I drop TSQLite3DataSet component on the form. It remains even after I removed TSQLite3DataSet component, and causes no problem in compiling . QuoteIf you still want to use it I do not use it.