Testing with SQLite3DataSet

[1HuntnMan]

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]

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]

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]

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.

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]

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]

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]

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]

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]

I 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]

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]

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]

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]

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][+][-]

1. procedure TFrmCntriesMgt.BitBtnCloseClick(Sender: TObject);
2. begin
3.   try
4.     SQLite3DSetCntries.Close;
5.   finally
6.     SQLite3DSetCntries.Free;
7.   end;
8.   FrmCntriesMgt.Close;
9.   //FrmCntriesMgt.Free;
10. end;                
11.  

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]

Hi
Here's another way of doing your 'Countries-App'...
-- No Db-aware-components in sight --
Regards Benny

[paweld]

@cdbc: lack of all dependencies: bc_memdataset