Recent

Author Topic: Copy current row & Insert (SQLite3 - ZEOS)  (Read 5791 times)

dsyrios

  • Jr. Member
  • **
  • Posts: 57
Copy current row & Insert (SQLite3 - ZEOS)
« on: February 24, 2017, 07:11:10 pm »
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.
Laz 2.0.2/FPC 3.0.4/ win10/64
Laz 2.0.0/FPC 3.0.4/ mint-mate 19.1

miab3

  • Full Member
  • ***
  • Posts: 145
Re: Copy current row & Insert (SQLite3 - ZEOS)
« Reply #1 on: February 25, 2017, 01:39:23 pm »
Code: Pascal  [Select][+][-]
  1. procedure TForm1.CopyInsertRowBtnClick(Sender: TObject);
  2. var i:integer;
  3.   buff:array of string;
  4. begin
  5.   SetLength(buff,ZQ.FieldCount);
  6.   For i:=1 to ZQ.FieldCount-1 do
  7.     buff[i]:=ZQ.Fields[i].AsString;
  8.   ZQ.Append;
  9.   For i:=1 to ZQ.FieldCount-1 do
  10.     ZQ.Fields[i].AsString:=buff[i];
  11.   ZQ.Post;
  12. end;  

Michal

dsyrios

  • Jr. Member
  • **
  • Posts: 57
Re: Copy current row & Insert (SQLite3 - ZEOS)
« Reply #2 on: February 26, 2017, 06:44:14 am »
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
Laz 2.0.2/FPC 3.0.4/ win10/64
Laz 2.0.0/FPC 3.0.4/ mint-mate 19.1

miab3

  • Full Member
  • ***
  • Posts: 145
Re: Copy current row & Insert (SQLite3 - ZEOS)
« Reply #3 on: February 26, 2017, 01:28:22 pm »
It might AsVariant?

Code: Pascal  [Select][+][-]
  1. procedure TForm1.CopyInsertRowBtnClick(Sender: TObject);
  2. var i:integer;
  3.   buff: array of variant;
  4. begin
  5.   SetLength(buff,ZQ.FieldCount);
  6.   For i:=1 to ZQ.FieldCount-1 do
  7.     buff[i]:=ZQ.Fields[i].AsVariant;
  8.   ZQ.Append;
  9.   For i:=1 to ZQ.FieldCount-1 do
  10.     ZQ.Fields[i].AsVariant:=buff[i];
  11.   ZQ.Post;
  12. end;

Michal

dsyrios

  • Jr. Member
  • **
  • Posts: 57
Re: Copy current row & Insert (SQLite3 - ZEOS)
« Reply #4 on: February 26, 2017, 04:50:29 pm »
Michal,
in the attachment is a part of the main idea.
Laz 2.0.2/FPC 3.0.4/ win10/64
Laz 2.0.0/FPC 3.0.4/ mint-mate 19.1

miab3

  • Full Member
  • ***
  • Posts: 145
Re: Copy current row & Insert (SQLite3 - ZEOS)
« Reply #5 on: February 26, 2017, 05:44:50 pm »
After ZQ.post ZEOS doing almost the same thing you're doing:

Code: Pascal  [Select][+][-]
  1. 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 ""
  2. 2017-02-26 17:33:20 cat: Prepare, proto: sqlite-3, msg: Statement 1 : SELECT * FROM nameT
  3.  
  4. 2017-02-26 17:33:20 cat: Prepare, proto: sqlite-3, msg: Statement 2 : PRAGMA table_info('nameT')
  5. 2017-02-26 17:33:20 cat: Execute prepared, proto: sqlite-3, msg: Statement 2
  6. 2017-02-26 17:33:20 cat: Execute prepared, proto: sqlite-3, msg: Statement 1
  7. 2017-02-26 17:33:23 cat: Prepare, proto: sqlite-3, msg: Statement 3 : INSERT INTO nameT (name1, name2) VALUES (?, ?)
  8. 2017-02-26 17:33:23 cat: Bind prepared, proto: sqlite-3, msg: Statement 3 : (INTERFACE),(INTERFACE),
  9. 2017-02-26 17:33:23 cat: Execute prepared, proto: sqlite-3, msg: Statement 3
  10. 2017-02-26 17:33:23 cat: Prepare, proto: sqlite-3, msg: Statement 4 : SELECT * FROM nameT
  11. 2017-02-26 17:33:23 cat: Execute prepared, proto: sqlite-3, msg: Statement 4
  12. 2017-02-26 17:33:26 cat: Prepare, proto: sqlite-3, msg: Statement 5 : INSERT INTO nameT (name1, name2) VALUES (?, ?)
  13. 2017-02-26 17:33:26 cat: Bind prepared, proto: sqlite-3, msg: Statement 5 : (INTERFACE),(INTERFACE),
  14. 2017-02-26 17:33:26 cat: Execute prepared, proto: sqlite-3, msg: Statement 5
  15. 2017-02-26 17:33:26 cat: Prepare, proto: sqlite-3, msg: Statement 6 : SELECT * FROM nameT
  16. 2017-02-26 17:33:26 cat: Execute prepared, proto: sqlite-3, msg: Statement 6
  17. 2017-02-26 17:33:29 cat: Execute prepared, proto: sqlite-3, msg: Statement 1

Michal

dsyrios

  • Jr. Member
  • **
  • Posts: 57
Re: Copy current row & Insert (SQLite3 - ZEOS)
« Reply #6 on: February 27, 2017, 06:15:53 am »
Michal,
you are right,
for me ... "Old dog new tricks"
Thanks again
Laz 2.0.2/FPC 3.0.4/ win10/64
Laz 2.0.0/FPC 3.0.4/ mint-mate 19.1

miab3

  • Full Member
  • ***
  • Posts: 145
Re: Copy current row & Insert (SQLite3 - ZEOS)
« Reply #7 on: February 27, 2017, 09:57:38 am »
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][+][-]
  1. 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 ""
  2. 2017-02-27 09:40:33 cat: Prepare, proto: sqlite-3, msg: Statement 1 : SELECT * FROM nameT
  3.  
  4. 2017-02-27 09:40:33 cat: Prepare, proto: sqlite-3, msg: Statement 2 : PRAGMA table_info('nameT')
  5. 2017-02-27 09:40:33 cat: Execute prepared, proto: sqlite-3, msg: Statement 2
  6. 2017-02-27 09:40:33 cat: Execute prepared, proto: sqlite-3, msg: Statement 1
  7. 2017-02-27 09:40:37 cat: Prepare, proto: sqlite-3, msg: Statement 3 : INSERT INTO nameT (Nid,name1,name2) VALUES (?,?,?)
  8. 2017-02-27 09:40:37 cat: Bind prepared, proto: sqlite-3, msg: Statement 3 : (NULL),(INTERFACE),(INTERFACE),
  9. 2017-02-27 09:40:37 cat: Execute prepared, proto: sqlite-3, msg: Statement 3
  10. 2017-02-27 09:40:39 cat: Bind prepared, proto: sqlite-3, msg: Statement 3 : (NULL),(INTERFACE),(INTERFACE),
  11. 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
« Last Edit: February 27, 2017, 11:45:04 am by miab3 »

 

TinyPortal © 2005-2018