Sqlite and controls

[Petrus Vorster]

Hi All

This is where the real difference between Basic and Pascal starts.
I am used to SQlite databases in Basic.

I have noticed a great number of controls dealing with data access, and had none of those in any Basic dialect I used. I am however clueless what to connect to what and how.

I have made a basic Sqlite.db3 database with four tables and a few entries.
Would anyone here please just show me which control to use & how to use it?
For now, perhaps populate a combobox with the names of Branch offices in a table?

The write and commit we will deal with a bit later.

Thank you for all the help.

Regards, Peter

[Handoko]

You can use TComboBox or TDBComboBox. If you use TComboBox, you need to manually load the data and store them to the combobox. But if you use TDBComboBox, you just need set the links. Each of them has their own use case. And you also need TSQLite3Connection and TDatasource.

Usually you have 2 ways to connect and load the database data, manually or using visual components. If you choose access the database manually, you usually don't need TDatasource. TDatasource is needed if you want to set the link to visual components.

Have you managed to connect to the database? If you haven't, here has basic SQL tutorials (see the see also section):
https://wiki.freepascal.org/SQLdb_Tutorial1#See_also

Here are some SQL demos you can download and try:
https://forum.lazarus.freepascal.org/index.php/topic,65185.msg496461.html#msg496461
https://forum.lazarus.freepascal.org/index.php/topic,67245.msg517135.html#msg517135
https://forum.lazarus.freepascal.org/index.php/topic,65185.msg496448.html#msg496448

And here are some video tutorials:
https://wiki.freepascal.org/Lazarus_videos#Database

[Petrus Vorster]

Thank you for the response.

I like the idea of the visual controls. It should make life easier.
I have the Sqliteconnection3 and selected the database in the database field.

I also have the Tdatasource control and a TsqlQuery control.
I want to query the Table BranchesTable with one columns called BranchNames.

In the Tdatasource I added the Tsqlquery in which i wrote a simple command :
Select * From BranchesTable

Then this needs to go to a DBcombobox.

Its clearly a case of connect, query and display, but since i am new to visual controls, I need to just get one example working.

I appreciate the assistance.

-Peter

[cdbc]

Hi
You need a 'TDataSource' component on your form... On the 'Data Access' tab above in Lazarus, it's the very first component you can choose, pick that and drop it on your form.
'TDataSource' has a property called 'Dataset' -> connect that to your 'SqlQuery1' component.
Then connect your assorted DBxxxControl's 'DataSource' property to that 'DataSource1' component.
Maybe you'll need a 'TDBNavigator'(found first on tab 'Data Controls'), to browse your data -> connect its 'DataSource' property to 'DataSource1'.
That's the most basic setup to view data...
edit: When connected to datasource, you'll find in 'DBComboBox' a property called 'Field' or 'Column'(can't remember which), here you select what data it should show.
HTH
Regards Benny

[Petrus Vorster]

Hi

Yes, i found reading here that you need to connect to the database then the datasource connects to that and then the query control to that.

I must be missing something really silly, because nothing populates that DB combobox.
I used the SQlite3connection, the Dataset and the query, but the fields gives an error with "Unable to retrieve fields definition from dataset".

I might be right there on the answer, it may just be something small.

Thanks a million.

Peter

[kapibara]

For TDBComboBox you write the code for populating yourself. TDBLookupComboBox on the other hand, does it automatically. Here's step by step how to populate TDBComboBox:

https://forum.lazarus.freepascal.org/index.php/topic,24582.msg148163.html#msg148163

[cdbc]

Hi
Query1.Active:= true; ?!?
SQlite3connection.Connected:= true; ?!?
Regards Benny

[Petrus Vorster]

Hi All

There is some progress. Your examples has helped a great deal.
If i check the SQLQUERY in the editor is clearly connects to the database.
It does find the Table.It returns the correct number of entries.

However they all says MEMO and not the actual entries.

From there on it all seems logical.
I just need to work trough all the examples first. I have wasted too much of your time.

-Peter

[dsiders]

There is some progress..
However they all says MEMO and not the actual entries.

If the table was created without explicit types for the columns, they are treated as the SQL TEXT type. The data-aware controls treat TEXT as the MEMO field type.

If you want real field types, then use explicit column types in the create statement. Like:

Code: MySQL  [Select][+][-]

1. create table example(
2. id integer,
3. name varchar(80),
4. address varchar(80),
5. city varchar(40),
6. state varchar(2),
7. postalcode varchar(10),
8. comments text);

This causes the columns to have useful metadata and  the data controls will be able to use this to assign field types other than MEMO.

[Petrus Vorster]

Hi
Thanks all for the responses.

I Use a tool called DB Browser to create the initial database.
That has only choices for TEXT, REAL, INTEGER, BLOB & Numeric.
This tool gives Powerful database setup and control.

I get everything from 'Transaction not set' now.
Its getting a bit frustrating.
Let me give my connection details:
TSQLITECONNECTION. Correct database , selected CONNECTED.
Then comes the TDATASOURCE. : Nothing to select there in the dropdowns.
So I assume a TSQLQUERY : DATABASE I selected the TSQLITECONNECTION.
Change TDATASOURCE DATASET to SQLQUERY1.
Enter the SQL command in the SQLcommand field.
Cannot set active : Gets : Transaction not set.

I just need to go read some more examples on the forum.

-Regards, Peter

[Zvoni]

that "(MEMO)" is the downside to DB-Bound controls for SQLite3.
1) Use Non-DB-bound controls, and fill them from code (it's what i do. Never used DB-Bound controls in 20 years of vb6/vba and 7 years of FPC/Lazarus)
2) Cast the TEXT-Columns in your SQL-Statement to CHAR/VARCHAR

Code: SQL  [Select][+][-]

1. SELECT CAST(SomeTextField AS CHAR) AS SomeField FROM SomeTable

[Petrus Vorster]

I finally figured out what connects to what.
But then there is that nasty MEMO response.

I used to write in Basic with Sqlite because its so simple, but I always fancied the idea of Drop-click and go. I am more studying the process than developing.
So here are two questions:

1) Are there any successful SQLITE projects here that uses data controls?
2) What other simple SQLdatabase tools do you suggest that CAN use these controls?

It doesnt HAVE to be Sqlite3 if its problematic.

Some pointers would be great.

-Peter

[paweld]

that "(MEMO)" is the downside to DB-Bound controls for SQLite3.
1) Use Non-DB-bound controls, and fill them from code (it's what i do. Never used DB-Bound controls in 20 years of vb6/vba and 7 years of FPC/Lazarus)
2) Cast the TEXT-Columns in your SQL-Statement to CHAR/VARCHAR

Code: SQL  [Select][+][-]

1. SELECT CAST(SomeTextField AS CHAR) AS SomeField FROM SomeTable

@Zvoni - After the recent changes in sqlite3conn this will not work (not only convert the type from text, but even merging columns is detected by the component as (MEMO)).
The only way out with SqlDB and SQLite is to assign a OnGetText event for each such field. Alternatively, you can use Zeos.

[Zvoni]

that "(MEMO)" is the downside to DB-Bound controls for SQLite3.
1) Use Non-DB-bound controls, and fill them from code (it's what i do. Never used DB-Bound controls in 20 years of vb6/vba and 7 years of FPC/Lazarus)
2) Cast the TEXT-Columns in your SQL-Statement to CHAR/VARCHAR

Code: SQL  [Select][+][-]

1. SELECT CAST(SomeTextField AS CHAR) AS SomeField FROM SomeTable

@Zvoni - After the recent changes in sqlite3conn this will not work (not only convert the type from text, but even merging columns is detected by the component as (MEMO)).
The only way out with SqlDB and SQLite is to assign a OnGetText event for each such field. Alternatively, you can use Zeos.

Ahh….damn. Forgot about that.
You’re right

[Petrus Vorster]

Hello everyone

I am starting to see the light. Had to start rock bottom with a normal DBF file and just the Tdatasource & Tdbf control.
Populate the Tlookupcombo and the Tdbgrid correctly.

Interestingly, the TDBF has a file path where you set the path to the database.
I would like it to be in the folder wherever i copy the project to.
If i use the folder settings in the control, it goes looking for that dbf in that folder after compile.

So how do I set that path to be just the local folder that there is no issues when i copy the finished project somewhere else?

Regards,

Peter