Testing with SQLite3DataSet

[Hansvb]

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

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]

Hi Benny,
now it work, thanks.
Have a nice weekend.

[1HuntnMan]

Yup

[1HuntnMan]

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]

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]

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]

PAWELD, thanks my friend! Take care...

[egsuh]

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]

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]

Hi
Remove "SQLite3Laz".
Regards Benny

[CharlyTango]

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

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.

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]

What 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 .

If you still want to use it

I do not use it.