Converting a Project from TDbf to SQLite [SOLVED]

[TRon]

I made a very simple program to test SQLite with minimal typing of codes.

Yeah, sublime

[egsuh]

I made another example, without TSQLite3Connect. This uses only TSQLite3DataSet components. Database is the same Chinook.db file, which I uploaded before. I started this with the purpose of various tests, but as there have been many questions on SQLite3 database, like "changes are not stored", etc., I hope this example helps those who try to use SQLite.

First I tried to use artist and album tables, but the album table causes problems, possibly because it has a blob field. Hope this is checked. 

So I used customer and invoice tables, and name of TSQLite3DataSet for each table is tblCustomer and tblInvoice. Datasource name for each dataset is dsCustomer and dsInvoice.

These two tables are master-slave relationship. Invoices are for each customer, linked by the CustomerID field.

The key points here are with tblInvoice :

        MasterCource : tblInvoice
        MasterFields :  CustomerID
        IndexFieldNames :  CustomerID
 
When executed, the right grid, which is for tblInvoice, will show only for each employee selected at left grid, which is for tblCustomer. It's little bit weird that every customer has 7 invoices.

To modify the content of invoice table here, I set following properties of tblInvoice.

        PrimaryKey : InvoiceID
        AutoincrementKey: true

With this, when I insert a record by pressing (+) button of dbnavigator, Invoice ID was filled in automatically. But not other fields like address, country, etc. And I keyed in total amount.

With this, I could modify the content at dbgrid and post, but the changes were not saved. When I go to other customers and came back, the changes were not saved. So I set

        SaveOnRefresh : true
        SaveOnClose: true

And then I tried again... and at this time, error message popped up. InvoiceDate field should not be null. This is strange that the field is not filled in.

Up to this, there were no typing of codes at all in unit file.

To make this simple program working, I added following codes.

Code: Pascal  [Select][+][-]

1. procedure TForm1.tblInvoiceBeforePost(DataSet: TDataSet);
2. begin
3.     tblInvoice.FieldByName('InvoiceDate').AsDateTime:= now;
4.     tblInvoice.fieldbyName('Billingaddress').asstring := tblCustomer.FieldByName('Address').AsString;
5. end;

Of course I can add other fields like country, city, etc. of invoice table, copying from customer table.

And now, it seems to work, at least inserting and deleting. This seems quite simple way to manage relational database. I'd like to make a program to manage my own customers and contacts.

[egsuh]

@1HuntnMan

I think you might have transformed all dbf tables to sqlite tables already. Anyway I found an easy way. DBeaver provides the function of importing csv file into sqlite table. If table structure is not predefined, table is automatically created but I had some difficulty to setting its properties like not null field, etc.

[egsuh]

It's does not seem a good idea to let any datasets open at design time. Had better to insert DataSet.Active :=true at any of FormCreate or FormActivate or FormShow.

[1HuntnMan]

TRon, EGSuh, et.al., awesome information, thanks. Oh, keep forgetting to let you know TRon, when I created the Database for this app when I decided to change it from Dbf to SQLite3, I used DBCopier which is nifty for doing that. But, when I decided to create a Database with just one table, i.e. countries to learn how to connect, etc. and display the data and finally uploaded it for you to examine what I was doing wrong. You made it work but the DBGrid was displaying MEMO instead of the data. I had no problem with this within the main app. I finally looked back and the Countries table was created with the fields type as TEXT.  In may main app. all the text type fields are defines as VarChar(length), i.e. for Country Abbrev.VarChar(2) and Country Name VarChar(35).  Now this can be an issue if you are creating a table and you are learning with DB Browser for SQLite. If you start creating a table in a database and you add a field name and then for Type and click the drop-down it displays INTEGER, TEXT, BLOB, REAL and NUMERIC.  So, if you aren't coding the table create and use DB Browser for SQLite, you can just not use the drop-down for Type and just key-in VarChar(length) and it will execute the SQL Code accordingly.  Just thought I'd pass that on.
Now, approaching the Clients form which uses 6 tables and a one-to-many relation Client to Contacts. The other tables are lookup tables: States, Countries, FirmType and Referral Type.

[wcage03]

You can likely get away with copying the dll from another location. The benefits of installing SQLite on your computer instead is that it will be available should you write another program where you want to use SQLite.

[1HuntnMan]

egsuh, thanks good recommendation.

[JanRoza]

@1Huntsman: If you want a really good database manager for SQLite give SQLiteExpert Personal a try (https://www.sqliteexpert.com).
I use it for years now and can do anything I like with my SQLite databases.
The Personal version is completely free.

[Zvoni]

TRon, EGSuh, et.al., awesome information, thanks. Oh, keep forgetting to let you know TRon, when I created the Database for this app when I decided to change it from Dbf to SQLite3, I used DBCopier which is nifty for doing that. But, when I decided to create a Database with just one table, i.e. countries to learn how to connect, etc. and display the data and finally uploaded it for you to examine what I was doing wrong. You made it work but the DBGrid was displaying MEMO instead of the data. I had no problem with this within the main app. I finally looked back and the Countries table was created with the fields type as TEXT.  In may main app. all the text type fields are defines as VarChar(length), i.e. for Country Abbrev.VarChar(2) and Country Name VarChar(35).  Now this can be an issue if you are creating a table and you are learning with DB Browser for SQLite. If you start creating a table in a database and you add a field name and then for Type and click the drop-down it displays INTEGER, TEXT, BLOB, REAL and NUMERIC.  So, if you aren't coding the table create and use DB Browser for SQLite, you can just not use the drop-down for Type and just key-in VarChar(length) and it will execute the SQL Code accordingly.  Just thought I'd pass that on.
Now, approaching the Clients form which uses 6 tables and a one-to-many relation Client to Contacts. The other tables are lookup tables: States, Countries, FirmType and Referral Type.

Notabene: The (MEMO)-Issue, if the Datatype of the Column is TEXT, is documented, and a search in the Forum will return a gazillion entries (and workarounds for it).
That said: i've been using DB Browser for years as a "cheating"-tool
I create my tables (and VIEWS, TRIGGERS, INDEXES and whatever else) in it, then i copy the CREATE-Statement from it, adjust it to suit my needs (Replace TEXT with Varchar(XX), insert a IF NOT EXISTS after CREATE TABLE etc.), and put the Statement in my Front-End-Code!!
It's a simple routine at Startup:
Check if DB exists: Yes/no --> If No, create it
Check if tables exists: Yes/No --> If no, use the Create-Statement
Run.....

That way you are not dependent on DB Browser or any other Tool if something goes wrong while "on the road"

EDIT: and btw: for something like "VARCHAR(50)", SQLite completely IGNORES the "(50)" Part....
There will be no difference between "VARCHAR(20)" and "VARCHAR(200)" on the SQLite-side.
Not sure about the SQLite-Components/Classes in Freepascal though, since i'm not sure what of SQLite's Metadata is retrieved.

e.g. You declare the column with "VARCHAR(20)", but SQLite is not going to prevent you to insert a Value longer than 20 Characters (Say a text with 100 Characters).
But FreePascal's SQLite-Components MIGHT read that Metadata-Value (the "(20)"), and set the Field-Size of the Dataset to it.
Have a guess what you might get back from the DB....

[jcmontherock]

As well (with SQLCpher):

https://sqlitebrowser.org/

[1HuntnMan]

I've been using DB Browser (SQLite) for viewing and checking tables during this conversion. Mostly finished, no Dbf's, all gone. But, still in a major learning curve learning SQL. I have relations on 4-5 of the forms, esp. in the Proposals, Clients, Sales Orders, Invoices.  I've been using the W3 Schools SQL Tutorial. But, I've downloaded A Beginner Guide to SQLite, SQLite Tutorials 0-4, etc. Mostly teaching you for one table, not a Database with 19 tables and 44 Indices which I'm eliminating a lot of those indexes. Looking at what ESUG wrote about TSQLite3DataSet. I'm using a lot of DataSet after connection for manipulating but learning by errors. For example, instead of this:

Code: Pascal  [Select][+][-]

1. tblInvoice.FieldByName('InvoiceDate').AsDateTime:= now;
2. this,
3. procedure TFrmInvoices.JDBCurrencyAmtDueExit(Sender: TObject);
4. begin
5.   if DSInvces.DataSet.FieldByName('AMTDUE').IsNull then
6.     ShowMessage('Amount Due($) must be a valid $ amount...!!!');
7.   JDBCurrencyAmtPaid.SetFocus;
8. end;
9.  

A lot is compiling but isn't running correctly but like I said, it's a learning curve. I attempted to install  TSQLite3DataSet components but something went wrong, it just disappeared. Tomorrow I'm going to uninstall Lazarus and reinstall to see if I can get it installed.

[egsuh]

If you have 19 tables, I think you'd better move to SQL. You may try some trials and errors with TSQliteDataSet, but that will not work if you have to join several tables or any table come to have large number rows, possibly over tens of thousands.

To learn SQL from the start I recommend the contents in link below. It's on Interbase and Firebird will operate in the same way, but SQLite does not support stored procedure. The SQL examples are very well organized, from creating tables, through inserting/deleting/updating records, and selecting records (inc. joining tables) to trigger programming.

https://docwiki.embarcadero.com/InterBase/2020/en/InterBase_Quick_Start

[Zvoni]

19 Tables? That's.... cute......
Just looking at our production Database there are over 5,000 tables (Yes... 5K)..

Everything said: Yes, SQL is the way to go. Don't do that DataSet-juggling you're probably used to from your DBF-Days....
If you need help with SQL-Statements, give a shout

*snip*...but SQLite does not support stored procedure. ...*snip*

Yes, that's one downside to SQLite, but honestly, i've never seen much use for it, but that's just me....

[Thaddy]

No, no stored procedures, neither does dBase btw, but user defined functions come close and that is encouraged and is supported.

And they are easy to write in Freepascal.

User defined functions can behave just like stored procedures for local databases, e.g. you can write them such that users can not modify/commit where they should not.
It is a feature many people forget, wrongly interpret or simply did not know about for lack of reading documentation....<sigh>

[Zvoni]

No, no stored procedures, neither does dBase btw, but user defined functions come close and that is encouraged and is supported.

And they are easy to write in Freepascal.

User defined functions can behave just like stored procedures for local databases, e.g. you can write them such that users can not modify/commit where they should not.
It is a feature many people forget, wrongly interpret or simply did not know about for lack of reading documentation....<sigh>

Behave? Yes!
Are equal? Definitely No.
A stored Procedure in this context is a "real" Database-Object (like a Table, View, Trigger etc.) and can be called as such, e.g "CALL MyProcedure(MyArgument)"

A UDF is more along the lines of
"SELECT MyOwnFunction(SomeColumn) AS Test FROM SomeTable"
The prominent one in SQLite the REGEXP-operator/Function.

A UDF can only ever be called/used WITHIN a SQL-Statement.