Recent

Author Topic: String Grid to SQL  (Read 2031 times)

pdhcentral

  • Newbie
  • Posts: 4
String Grid to SQL
« on: August 19, 2020, 04:12:31 pm »
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

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: String Grid to SQL
« Reply #1 on: August 19, 2020, 11:26:20 pm »
your Grid has an sql-statement somewhere I presume.
To create a view you have to do something like below
Code: Pascal  [Select][+][-]
  1.     // qry_Scheidsrechters
  2.     cSQL := '';
  3.     cSQL := 'CREATE VIEW qry_Scheidsrechters AS SELECT ' +
  4.             'Scheids_ID, ' +
  5.             'REPLACE(RTRIM(COALESCE(Voornaam || " ", "") || ' +
  6.             'COALESCE(Tussenvoegsel || " ", "") || ' +
  7.             'COALESCE(Achternaam, "")), "  ", " ") AS Scheidsrechter ' +
  8.             'FROM tbl_Scheidsrechters';
  9.     Connect_RefereeDB.ExecuteDirect(cSQL);
So your statement would look something like
Code: Pascal  [Select][+][-]
  1. cSQL := 'CREATE VIEW Qry_SomeName AS ' + SomewhereWhereYouHaveStoredYourStatement
« Last Edit: August 19, 2020, 11:29:20 pm by madref »
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: String Grid to SQL
« Reply #2 on: August 20, 2020, 04:17:32 am »
I guess you are looking for something like following.


Code: Pascal  [Select][+][-]
  1. qr1.sql.text := 'insert into yourtablename (f1, f2, ... fn) values (:v1, :v2, .. :vn)';
  2.  
  3. transaction1.Active := true;
  4. for ti := 1 to stringgrid1.rowcount-1 do begin
  5.     qr1.Close;
  6.     qr1.prepare;
  7.  
  8.     for tj := 1 to stringgrid1.colcount-1 do
  9.           qr1.params[tj-1].AsString := stringgrid1.cells[ti, tj];
  10.     qr1.execSQL;
  11. end;    
  12.  
  13. Transaction1.Commit;

I'm assuming that head row and column of your stringgrid do not have data.
« Last Edit: August 20, 2020, 04:20:05 am by egsuh »

pdhcentral

  • Newbie
  • Posts: 4
Re: String Grid to SQL
« Reply #3 on: August 21, 2020, 12:01:33 am »
I guess you are looking for something like following.


Code: Pascal  [Select][+][-]
  1. qr1.sql.text := 'insert into yourtablename (f1, f2, ... fn) values (:v1, :v2, .. :vn)';
  2.  
  3. transaction1.Active := true;
  4. for ti := 1 to stringgrid1.rowcount-1 do begin
  5.     qr1.Close;
  6.     qr1.prepare;
  7.  
  8.     for tj := 1 to stringgrid1.colcount-1 do
  9.           qr1.params[tj-1].AsString := stringgrid1.cells[ti, tj];
  10.     qr1.execSQL;
  11. end;    
  12.  
  13. 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.
« Last Edit: August 21, 2020, 12:18:33 am by pdhcentral »

TRon

  • Hero Member
  • *****
  • Posts: 2435
Re: String Grid to SQL
« Reply #4 on: August 21, 2020, 12:30:50 am »
Are 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][+][-]
  1.           qr1.params[tj-1].AsString := stringgrid1.cells[ti, tj];
  2.  
.. 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

  • Hero Member
  • *****
  • Posts: 1273
Re: String Grid to SQL
« Reply #5 on: August 21, 2020, 04:47:01 am »
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

  • Newbie
  • Posts: 4
Re: String Grid to SQL
« Reply #6 on: August 27, 2020, 11:15:13 pm »
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.

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

  • Hero Member
  • *****
  • Posts: 1273
Re: String Grid to SQL
« Reply #7 on: August 28, 2020, 08:59:25 am »
Quote
'Can't perform operation on inactive transaction' message.

This is related with transaction. Check following statements. 

Code: Pascal  [Select][+][-]
  1.  transaction1.active := true;
  2.  
  3.  ......
  4.  
  5.  transaction1.commit;

 

TinyPortal © 2005-2018