Copy current row & Insert (SQLite3 - ZEOS)
lazarus / 16-Databases

----------------------------------------------------------------------
dsyrios · 2017-02-24 18:11

Hi,
I have not much experience, but I'd like
using DBGrid, SQLite3 and Zeos
to copy the current row and insert it in the same table
by clicking a button.
The table is from ...
CREATE TABLE [nameT](
    [Nid] INTEGER PRIMARY KEY NOT NULL UNIQUE,
    [name1] VARCHAR,
    [name2] VARCHAR);

Note that the first column [Nid] is Autoincrement
The attachments have this simple project
without sqlite3.dll because of the attachments restrictions.
Laz 1.6.2/win32

Thanks in advance.

----------------------------------------------------------------------
miab3 · 2017-02-25 12:39

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 procedure
TForm1.CopyInsertRowBtnClick(Sender: TObject);var i:integer; 
buff:array of string;begin  SetLength(buff,ZQ.FieldCount);  For i:=1
to ZQ.FieldCount-1 do    buff[i]:=ZQ.Fields[i].AsString;  ZQ.Append; 
For i:=1 to ZQ.FieldCount-1 do    ZQ.Fields[i].AsString:=buff[i]; 
ZQ.Post;end;  
Michal

----------------------------------------------------------------------
dsyrios · 2017-02-26 05:44

Thanks Michal,
I've done something like that but with Record Type instead of Buf
because at my real base the fields are strings, numbers, boolean etc

----------------------------------------------------------------------
miab3 · 2017-02-26 12:28

It might AsVariant?

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 procedure
TForm1.CopyInsertRowBtnClick(Sender: TObject);var i:integer;  buff:
array of variant;begin  SetLength(buff,ZQ.FieldCount);  For i:=1 to
ZQ.FieldCount-1 do    buff[i]:=ZQ.Fields[i].AsVariant;  ZQ.Append; 
For i:=1 to ZQ.FieldCount-1 do    ZQ.Fields[i].AsVariant:=buff[i]; 
ZQ.Post;end;
Michal

----------------------------------------------------------------------
dsyrios · 2017-02-26 15:50

Michal,
in the attachment is a part of the main idea.

----------------------------------------------------------------------
miab3 · 2017-02-26 16:44

After ZQ.post ZEOS doing almost the same thing you're doing:

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 2017-02-26
17:33:20 cat: Connect, proto: sqlite-3, msg: CONNECT TO
"C:\lazar\sql_insert_a_copy_current_row\names.db" AS USER ""2017-02-26
17:33:20 cat: Prepare, proto: sqlite-3, msg: Statement 1 : SELECT *
FROM nameT 2017-02-26 17:33:20 cat: Prepare, proto: sqlite-3, msg:
Statement 2 : PRAGMA table_info('nameT')2017-02-26 17:33:20 cat:
Execute prepared, proto: sqlite-3, msg: Statement 22017-02-26 17:33:20
cat: Execute prepared, proto: sqlite-3, msg: Statement 12017-02-26
17:33:23 cat: Prepare, proto: sqlite-3, msg: Statement 3 : INSERT INTO
nameT (name1, name2) VALUES (?, ?)2017-02-26 17:33:23 cat: Bind
prepared, proto: sqlite-3, msg: Statement 3 :
(INTERFACE),(INTERFACE),2017-02-26 17:33:23 cat: Execute prepared,
proto: sqlite-3, msg: Statement 32017-02-26 17:33:23 cat: Prepare,
proto: sqlite-3, msg: Statement 4 : SELECT * FROM nameT2017-02-26
17:33:23 cat: Execute prepared, proto: sqlite-3, msg: Statement
42017-02-26 17:33:26 cat: Prepare, proto: sqlite-3, msg: Statement 5 :
INSERT INTO nameT (name1, name2) VALUES (?, ?)2017-02-26 17:33:26 cat:
Bind prepared, proto: sqlite-3, msg: Statement 5 :
(INTERFACE),(INTERFACE),2017-02-26 17:33:26 cat: Execute prepared,
proto: sqlite-3, msg: Statement 52017-02-26 17:33:26 cat: Prepare,
proto: sqlite-3, msg: Statement 6 : SELECT * FROM nameT2017-02-26
17:33:26 cat: Execute prepared, proto: sqlite-3, msg: Statement
62017-02-26 17:33:29 cat: Execute prepared, proto: sqlite-3, msg:
Statement 1
Michal

----------------------------------------------------------------------
dsyrios · 2017-02-27 05:15

Michal,
you are right,
for me ... "Old dog new tricks"
Thanks again

----------------------------------------------------------------------
miab3 · 2017-02-27 08:57

I will also add that in the simplest cases(simple SQL), there is no
need at all to use TZUpdateSQL.
ZEOS himself will build the appropriate INSERT, DELETE and MODIFY SQL.
So in your example without ZUp(TZUpdateSQL) also it works properly:

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 2017-02-27
09:40:33 cat: Connect, proto: sqlite-3, msg: CONNECT TO
"C:\lazar\sql_insert_a_copy_current_row\names.db" AS USER ""2017-02-27
09:40:33 cat: Prepare, proto: sqlite-3, msg: Statement 1 : SELECT *
FROM nameT 2017-02-27 09:40:33 cat: Prepare, proto: sqlite-3, msg:
Statement 2 : PRAGMA table_info('nameT')2017-02-27 09:40:33 cat:
Execute prepared, proto: sqlite-3, msg: Statement 22017-02-27 09:40:33
cat: Execute prepared, proto: sqlite-3, msg: Statement 12017-02-27
09:40:37 cat: Prepare, proto: sqlite-3, msg: Statement 3 : INSERT INTO
nameT (Nid,name1,name2) VALUES (?,?,?)2017-02-27 09:40:37 cat: Bind
prepared, proto: sqlite-3, msg: Statement 3 :
(NULL),(INTERFACE),(INTERFACE),2017-02-27 09:40:37 cat: Execute
prepared, proto: sqlite-3, msg: Statement 32017-02-27 09:40:39 cat:
Bind prepared, proto: sqlite-3, msg: Statement 3 :
(NULL),(INTERFACE),(INTERFACE),2017-02-27 09:40:39 cat: Execute
prepared, proto: sqlite-3, msg: Statement 3
So that my procedure is to some extent universal.
In the margin:
In SQLite, you can define the size of the string field. Eg. Varchar
(30).
Then even a simple dbgrids also deal with them well.

Michal