MZSQL: Why is the included code fail to update a csv file?

[EganSolo]

I must be missing something obvious, so please bear with me: I haven't used DB technology much before.
I've included a simple and complete project for testing purposes: I can't seem to get a SQL insert statement to update the underlying csv file while using ZMSQL.

The suspect code reads as follows (found in the inserted project)

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

1.   with CSVSQLDataSet Do
2.   begin
3.     SQL.Text := Memo1.Lines.Text; // Memo1 contains one line: INSERT INTO Test ('Mary', 'James', '44')
4.     QueryExecute;
5.     //What is missing so that the data is stored in the file? A mention is made of Commit but there is no Commit method in the
6.     //the TZMQueryDataSet ...
7.   End;
8.   //CSVFileDataSet is a second TMZSQLDataSet with TableName set to Test. If the code above worked (which it's not), then
9.   //the next command should update the content in the DBGrid on the main form.
10.   CSVFileDataSet.LoadFromTable;
11.  

PLEASE NOTE: There is a constant at the start of the code which points to the folder where you will unzip the attached file.

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

1. const
2.   MyDBPath = 'C:\Temp'; //<=== Change that value to point to the folder where you want to unzip the attached file
3.  

Your assistance would be greatly appreciated. Thanks,

Egan

[speter]

The general form of SQL insert is:

Code: [Select]

insert into tablename (fieldname1, fieldname2, fieldname3) values ('foo','bar','bong')That may be your problem... (the syntax of your SQL is wrong).

cheers
S.

[EganSolo]

While speter was right (thank you for your kind answer  ), the SQL statement is wrong, it wasn't the end of the story.
Even after fixing that SQL statement and running it, the result was the same: the file wasn't updated.

I'm writing this in case it should help someone else. If you're like me and have been using DBs for a while, you know that when you execute a valid SQL statement, the effect of that statement is seen in the database. For instance, If you execute an INSERT inside a LAz program and then you use a separate app to look at the content of the DB, you would see the new record there. That DOES NOT WORK in MZSQL.

Here's the difference:

In traditional DB you have the following configuration  Laz Program --> SQL Client --> SQL Server. Your program passes the SQL statement to the SQL client which executes it against the server. Whether that client is in the driver or in your code is irrelevant. The point here is that that chain of command ends-up by persisting the effect of a SQL statement into the database on disk.

With MZSQL you have the following configuration: LAZ Program --> MZSQL (in MEM).
So when you perform an INSERT the effect of the SQL statement is felt ... in memory (duh!). That's because MZSQL is an in-memory database. If you want the effect of that SQL to be felt on disk you have two options:
1. You can set TMZQueryDataSet.PersistentSave to true, which will then cause the data set to save its content to disk after each change.
2. You can call  TMZQueryDataSet.SaveToTable to see the effect on disk.

One more important difference: In traditional SQL, if I use a SQL dataset to execute Insert into table (f1, f2, f3) values (1,2,3), I DON'T NEED (I think! I haven't checked that in a while) to have the table first loaded inside that data set! The data set is used as a conduit to talk to the SQL client which will execute that statement in the database.

Not so with MZSQL because ... it's an in-memory database! In other words, MZSQL on its own will not go out of its way to load the csv file first, then execute the SQL statement. You have to have that CSV loaded in the Dataset for that SQL statement to execute properly. In which case, it's most likely faster to simply use the Append method.

Most likely, many of you will read this and shake your heads thinking that's it's as obvious as the sun in the sky. I guess, I had that traditional SQL pattern stuck in my head and it was interfering with my understanding of how MZSQL works.

Egan.