SQLDb - Insert SQL not working [Solved]
lazarus / 16-Databases

----------------------------------------------------------------------
JanRoza · 2017-05-29 11:32

Hi,

I use the following code to fill a SQLite table:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
DataModule1.qryStatistiekTypen.Active := False; 
DataModule1.qryStatistiekTypen.InsertSQL.Text := 'INSERT INTO
StatTypen (Type) SELECT DISTINCT Type FROM Registraties'; 
DataModule1.qryStatistiekTypen.Active := True; 
DataModule1.qryStatistiekTypen.Insert; 
DataModule1.qryStatistiekTypen.ApplyUpdates; 
DataModule1.DbConnection.Transaction.CommitRetaining;  
DataModule1.qryStatistiekTypen.Active := False; 
DataModule1.qryStatistiekTypen.SQL.Text := 'SELECT * FROM StatTypen
ORDER BY Type';  DataModule1.qryStatistiekTypen.Active := True; 
DataModule1.qryStatistiekTypen.ExecSQL; 
DataModule1.qryStatistiekTypen.First; 
Unfortunately the program stops at the .First statement with an error
message saying operation cannot be performed on an inactive dataset.
Of course the dataset is active as it is activated just above that
statement.
It turns out that insert statement did not insert any records (whereas
it should insert some 20 records according to the content of table
Registraties)

I also tried:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
DataModule1.qryStatistiekTypen.Active := False; 
DataModule1.qryStatistiekTypen.SQL.Text := 'INSERT INTO StatTypen
(Type) SELECT DISTINCT Type FROM Registraties'; 
DataModule1.qryStatistiekTypen.Active := True; 
DataModule1.qryStatistiekTypen.ExecSQL; 
DataModule1.qryStatistiekTypen.ApplyUpdates; 
DataModule1.DbConnection.Transaction.CommitRetaining; 
But then I get an error message telling me that it cannot execute a
non-select SQL statement.

What is the proper solution to get distinct values inserted from one
table into another table?
I tested the SQL statement in SQLite Expert and it generated exactly
the records I expected into the table so the SQL statement is correct
for SQLite.
Hope anyone can help me back on track here.

----------------------------------------------------------------------
rvk · 2017-05-29 11:37

Don't use DataModule1.qryStatistiekTypen.ExecSQL; but use
DataModule1.qryStatistiekTypen.Open; or Active := true; to open a
dataset with SELECT.
Also... make sure to set .SQL.Text together with the InsertSQL.Text.
When doing Active := true on qryStatistiekTypen the SELECT (.SQL.Text)
needs to be correct to open the Dataset.
Also... There is no need to do Active := true AND Open; . They are the
same. So you just need to remove the ExecSQL line and all will work
fine. (Except that your Insert statement isn't correct for .Insert,
see later)

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
DataModule1.qryStatistiekTypen.Active := False; 
DataModule1.qryStatistiekTypen.SQL.Text := 'SELECT * FROM StatTypen
ORDER BY Type';  DataModule1.qryStatistiekTypen.InsertSQL.Text :=
'INSERT INTO StatTypen (Type) SELECT DISTINCT Type FROM
Registraties';  DataModule1.qryStatistiekTypen.Active := True; // <-
.SQL.Text needs to be correctly set here 
DataModule1.qryStatistiekTypen.Insert; 
DataModule1.qryStatistiekTypen.ApplyUpdates; 
DataModule1.DbConnection.Transaction.CommitRetaining;  
DataModule1.qryStatistiekTypen.Active := False;  //
DataModule1.qryStatistiekTypen.SQL.Text := 'SELECT * FROM StatTypen
ORDER BY Type'; // <--- Already set above 
DataModule1.qryStatistiekTypen.Active := True;  //
DataModule1.qryStatistiekTypen.Open; // <- Active := true or Open will
do both what you want  DataModule1.qryStatistiekTypen.First;
But what you want doesn't work here.

DataModule1.qryStatistiekTypen.Insert; will only put the dataset in
insert mode and you need to set the fields after that (according to
your insert statement) followed by a POST. But your insert statement
doesn't have input-fields.

You can either loop through Registraties and insert all records
manually or you can use ExecSQL with .SQL.Text set.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
DataModule1.qryStatistiekTypen.Active := False; 
DataModule1.qryStatistiekTypen.SQL.Text := 'INSERT INTO StatTypen
(Type) SELECT DISTINCT Type FROM Registraties'; 
DataModule1.qryStatistiekTypen.ExecSQL; // <-- now .SQL.Text is
executed !!  DataModule1.qryStatistiekTypen.ApplyUpdates; 
DataModule1.DbConnection.Transaction.CommitRetaining;  
DataModule1.qryStatistiekTypen.Active := False; // should already be
false  DataModule1.qryStatistiekTypen.SQL.Text := 'SELECT * FROM
StatTypen ORDER BY Type';  DataModule1.qryStatistiekTypen.Active :=
True; // <-- same as .Open  DataModule1.qryStatistiekTypen.First;

----------------------------------------------------------------------
JanRoza · 2017-05-29 12:01

QuoteDataModule1.qryStatistiekTypen.Insert; will only put the dataset
in insert mode and you need to set the fields after that (according to
your insert statement) followed by a POST. But your insert statement
doesn't have input-fields.

I know, that's why I tried the insert via the SQL.Text with ExecSQL
but then sqlDb refuses to cooperate as it is a non-select statement.
As the SQL itself is correct and work on my database via any sql
front-end I'm looking for a way to get it executed in my program, in
my opinion ExecSQL should also accept other statements than just
select statements. But alas it does not.
Looping through the parent dataset and 'manually' filling the other
dataset is what I hoped to prevent by using the DISTINCT option ....
it saves work.
Looking in wikipedia
(http://wiki.freepascal.org/Working_With_TSQLQuery) it gives an
example of an insert statement executed by execsql but that is either
wrong or sqldb has a bug. That was exactly what I tried to do.

----------------------------------------------------------------------
rvk · 2017-05-29 12:06

Quote from: JanRoza on May 29, 2017, 02:01:59 pmI know, that's why I
tried the insert via the SQL.Text with ExecSQL but then sqlDb refuses
to cooperate as it is a non-select statement.
ExecSQL will work perfectly but when setting SQL.Text (with your
INSERT statement) you should NOT .Open or Active := true the dataset.

So either set SQL.Text with a SELECT statement and use .Open or Active
:= true
or set SQL.Text with your INSERT and execute the statement with
.ExecSQL.

(But you mixed those things and got the error)

In that Insert query example with ExecSQL you don't see Open of Active
:= true. You did use Active := true before ExecSQL and that doesn't
work because you can only use Active := true with SELECT.

----------------------------------------------------------------------
JanRoza · 2017-05-29 12:55

You are right, all ingredients were correct but the order in which I
ran them was wrong.
I've now changed the coding to:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   // Maak
een record per type aan in qryStatistiekTypen 
DataModule1.qryStatistiekTypen.Active := False; 
DataModule1.qryStatistiekTypen.SQL.Text := 'DELETE FROM StatTypen'; 
DataModule1.qryStatistiekTypen.ExecSQL; 
DataModule1.qryStatistiekTypen.SQL.Text := 'INSERT INTO StatTypen
(Type) SELECT DISTINCT Type FROM Registraties'; 
DataModule1.qryStatistiekTypen.ExecSQL; 
DataModule1.qryStatistiekTypen.SQL.Text := 'SELECT * FROM StatTypen
ORDER BY Type';  DataModule1.qryStatistiekTypen.Active := True; 
DataModule1.qryStatistiekTypen.First; 
and this gives exactly the result I expected.
Thanks for pointing me in the right direction.