String Grid to SQL
lazarus / 16-Databases

----------------------------------------------------------------------
pdhcentral · 2020-08-19 14:12

Hi,

I have a string grid that I'd like to copy to SQL table. Can you give
me some info on a way to do this?

Thanks.

----------------------------------------------------------------------
madref · 2020-08-19 21:26

your Grid has an sql-statement somewhere I presume.
To create a view you have to do something like below
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     //
qry_Scheidsrechters    cSQL := '';    cSQL := 'CREATE VIEW
qry_Scheidsrechters AS SELECT ' +            'Scheids_ID, ' +        
  'REPLACE(RTRIM(COALESCE(Voornaam || " ", "") || ' +           
'COALESCE(Tussenvoegsel || " ", "") || ' +           
'COALESCE(Achternaam, "")), "  ", " ") AS Scheidsrechter ' +         
 'FROM tbl_Scheidsrechters';   
Connect_RefereeDB.ExecuteDirect(cSQL);So your statement would look
something like
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 cSQL :=
'CREATE VIEW Qry_SomeName AS ' +
SomewhereWhereYouHaveStoredYourStatement

----------------------------------------------------------------------
egsuh · 2020-08-20 02:17

I guess you are looking for something like following.


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
qr1.sql.text := 'insert into yourtablename (f1, f2, ... fn) values
(:v1, :v2, .. :vn)';  transaction1.Active := true; for ti := 1 to
stringgrid1.rowcount-1 do begin     qr1.Close;     qr1.prepare;     
for tj := 1 to stringgrid1.colcount-1 do          
qr1.params[tj-1].AsString := stringgrid1.cells[ti, tj];   
qr1.execSQL; end;      Transaction1.Commit;
I'm assuming that head row and column of your stringgrid do not have
data.

----------------------------------------------------------------------
pdhcentral · 2020-08-20 22:01

Quote from: egsuh on August 20, 2020, 04:17:32 amI guess you are
looking for something like following.


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
qr1.sql.text := 'insert into yourtablename (f1, f2, ... fn) values
(:v1, :v2, .. :vn)';  transaction1.Active := true; for ti := 1 to
stringgrid1.rowcount-1 do begin     qr1.Close;     qr1.prepare;     
for tj := 1 to stringgrid1.colcount-1 do          
qr1.params[tj-1].AsString := stringgrid1.cells[ti, tj];   
qr1.execSQL; end;      Transaction1.Commit;
I'm assuming that head row and column of your stringgrid do not have
data.

Thanks, thats pretty much what I'm after. It has the columns and rows
swapped around but I think I can change that.

----------------------------------------------------------------------
TRon · 2020-08-20 22:30

Quote from: pdhcentral on August 21, 2020, 12:01:33 amAre the values
:v1, v2, etc like that, as in do I type that and they get filled in by
the params loop later on? Will have to play and learn.
Yes.

To be exact this line...
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          
qr1.params[tj-1].AsString := stringgrid1.cells[ti, tj]; .. uses Code:
[Select]params[x].AsString := to set the parameter value (by using an
index to the paramerlist, e.g. tj-1).

That also means that it really doesn't matter how you name the
parameters as they are not set by using
params.ParamByName('NameOfParameter').AsString.

You would have to make sure the parameter names in the query are
unique though

----------------------------------------------------------------------
egsuh · 2020-08-21 02:47

Quote It has the columns and rows swapped around
Oh sorry....   TStringGrid.Cells [columnIndex, rowIndex] is right.  
I seldom use TStringGrid, so I always make errors.

----------------------------------------------------------------------
pdhcentral · 2020-08-27 21:15

Quote from: egsuh on August 21, 2020, 04:47:01 amQuote It has the
columns and rows swapped around
Oh sorry....   TStringGrid.Cells [columnIndex, rowIndex] is right.  
I seldom use TStringGrid, so I always make errors.

Sorry for the long delay. The code does appear to work and the DB
table gets populated. The issue I have is that once its done, I get a
'Can't perform operation on inactive transaction' message. I take it
that I'm missing a close or something but don't know. I've tried a
couple of things but doesn't seem to work. Any ideas? Thanks.

----------------------------------------------------------------------
egsuh · 2020-08-28 06:59

Quote'Can't perform operation on inactive transaction' message.
This is related with transaction. Check following statements. 

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
 transaction1.active := true;   ......  transaction1.commit;