Hi,

Using SQLite3, Lazarus 1.6.2 & FPC 3.0.0 on Peppermint Linux 7.

I have a small problem when using the db aware components & sqlite. After countless googling I understand why the components do this but can not find a simple solution! (BTW I this is my first time working with DBs & SQLite seemed the simplest and meets my needs (embeddable)).

My problem is described below, but I'll ask my questions first:

1. Are there any tricks anybody knows of so I can show the text via code in lazarus, or
2. Are there any tricks anybody knows of so I can show the text by altering the sqlite database (other than switching to another database entirely)?

As this affects all db aware components, it's probably something fundamental I am missing if it exists at all.

From what I understand, SQLite TEXT type has no set length so db aware

components treat it as a memo. Therefore when I assign a TEXT field to a db aware component, all that appears in the component is "(MEMO)", even though most of my TEXT fields are only up to 10-30 characters long. Solutions offered on-line suggest using a dbmemo (which I have for my longer TEXT fields as that's what I want), but I need to use dbgrids, dbedits, dbcomboboxs, etc for the shorter TEXT fields.

I found a solution for dbgrids, using the dbgridpreparecanvas event, but not for the other db aware component types.

I can extract the text, using:

DataSource.DataSet.FieldByName('field').AsString

and I could use that to manually write the values to non db aware components as needed, but that seems a waste when we have these superb db aware components to use.

For information, I am coupling the data to the components simply by assigning the datasource & datafield to the db aware component. I'm using SQLite3Connection (as opposed to SQLite3Dataset), SQLQuery, SQLTransaction & DataSource.

Thanks for your help.

Martin

I have found a solution to my problem, with thanks to the following link:

http://www.tweaking4all.com/software-development/lazarus-development/lazarus-pascal-getting-started-with-sqlite/#ATEXTfieldshowsamemoinaDBGrid

The answer was altering my SQL Query text, from something like this:

SELECT
"Filename",
"Date"
FROM

  "mytable";

to this:

SELECT
CAST( "Filename" AS VARCHAR) AS "Filename",
"Date"
FROM

  "mytable";

Casting it as a different type in the SQL statement worked! My bad and inexperience with databases!

Hope this will help someone else one day! :-)

Thanks & best regards,

Martin

Dňa 1.12.2016 o 21:40 Martin Collins via Lazarus napísal(a):

You are not required to that if you have declared table "mytable" as: CREATE TABLE "mytable" (
FileName varchar(100),
Date datetime
)

-Laco

Thank you Laco. Just tried that out and it works. The database I am using was created using a couple of visual database managers - exported from MS Access to an Excel file and then saved as CSV before the 4200 rows x 51 columns of the main data were imported into a prepared sqlite table. I used the visual managers as I am not experienced in creating tables & fields by SQL or importing large amounts of data.

The weird thing is that when creating my fields the visual database managers did not have the option of VARCHAR only TEXT (plus INTEGER, BLOB, REAL & NUMERIC), but I just found I can create a VARCHAR field by executing your SQL statement above! Further reading on sqlite seems to suggest that VARCHAR isn't really stored that way as SQLite just treats it as unlimited TEXT?

It's all a bit confusing and probably getting a bit off-topic for lazarus/freepascal, but I am getting there. Thanks for you help.

Martin

SQL lite, is not a database, it is a sloppy untyped dumb text storage system, that is called a database even though it really is not...

It's perfect for when you need something a little more advanced than plain text files, but in no way, is it an sql or relational database, even though it accepts SQL language to access it..

Basically, it's one little step better than using plain text files... and it is easier to ship than mysql embedded or other tools that require more complex setups. Actually I haven't looked at mysql embedded recently, to see what they are up to.... And as we all know mysql has a reputation of not being a proper relational database either!

But despite sqlite being a poorly typed, weakly typed, basically dumb database which isn't even a database... It is still a useful tool, and one step better than Xml files, or plain text storage in plain files.

Whenever choosing sqlite, I always tell myself: remember, SQL is NOT a relational database even though it claims to be one, so be prepared to use it carefully, as a dumb text storage system just like plain text files. With this warning in mind it is a very useful tool.

Fabian Pascal has something to say about it, if anyone claims it's relational.. or typed... The typing system in it is a joke, it basically ignores types. As do plain text files when you save stuff to them.. You can reinvent your own typing system though by doing careful checks. When using sqlite it's almost like a static/strong type programmer choosing to use a weakly typed programming language with dynamic types: it feels odd and strange that there is not this sterility involved with proper checks in place. But that's exactly kind of what like using plain text files is like... you have to store plain text and do checks at run time for issues... So sql lite is basically, a plain text organization system. It is, I repeat, NOT a relational nor an sql database... And Fabian Pascal can say it much better than I can with his attack on it.

Still, a useful tool: just be warned.