perist sqlquery result into database?
lazarus / 16-Databases

----------------------------------------------------------------------
tatamata · 2020-04-05 07:40

Hello, please suggest best way to persist an TSQLQuery result into a
database (e.q PostgreSQL) table, besides creating new query with
"CREATE TABLE AS..." statement?
Btw, it is sqlquery without predefined fielddefs, the sql string is
defined dynamically.

----------------------------------------------------------------------
eljo · 2020-04-05 10:20

SQLQuery has a number sql Statement properties
1) SQL for select
2) InsertSQL used when you call the Qry.Post method after a new record
is inserted/appended.
3) UpdateSQL used when you call the Qry.Post method after a record was
edited.

By providing the proper sql for the two above persister sql statement
you can control how the data are stored or not.

----------------------------------------------------------------------
egsuh · 2020-04-06 02:56

Try dynamically creating view, and then selecting from the view. I
have started with "try" because I think this is possible
theoretically, but not have done myself yet .

Or you may copy it to TBufDataSet, and save the TBufDataSets as
separate files.

----------------------------------------------------------------------
SamC · 2020-04-14 05:51

Hello,

here are 3 simple procedures i use often to do the job:


// -------------------------------------------------------------------
-----------
procedure TMainForm.buildSql(tn: String; d: TDataSet; s: TStrings);
var
  r: integer;
  sl: TStringList;
const
  LIMIT = 10000;
begin

  sl:=TStringList.Create;

  with d do begin
    DisableControls;
    r:=0;
    first;

    while not eof do
    begin

      // build sql-statement for insert into mysql-table
      if r=0 then
      begin
        sl.Add(Format('replace into %s (%s)',[tn,
getFieldNameList(d)]));
        // sl.Add(Format('insert into %s (%s)',[tn,
getFieldNameList(d)]));
        sl.Add(Format(' values (%s)',[getValueList(d)]));
        next;
        inc(r);
      end;

      // build VALUES for every line in the resultset
      while (not eof) and (r<LIMIT) do
      begin
        sl.Add(format('      ,(%s)',[getValueList(d)]));
        next;
        inc(r);
      end;

      sl.Add(';');
      r:=0;

    end;
    EnableControls;
  end;

  s.assign(sl);
  sl.Free;

end;


// -------------------------------------------------------------------
-----------
function TMainForm.getFieldNameList(d: TDataSet): String;
var
  fl: TStringList;
  f: TField;
begin

  fl:=TStringList.Create;
  for f in d.Fields do
    fl.Add(f.FieldName);
  result:=fl.CommaText;
  fl.Free;

end;


// -------------------------------------------------------------------
-----------
function TMainForm.getValueList(d: TDataSet): String;
var
  i: integer;
  f: TField;
begin

  result:='';

  f:=d.Fields[0];
  if f.DataType in [ftInteger] then result:=f.asString
  else if f.DataType in [ftDate, ftDateTime]
     then result:=QuotedStr(getSqlDateTime(f.asString))
  else result:=QuotedStr(f.asString);

  for i:=1 to d.Fields.Count-1 do
  begin
    f:=d.Fields;
    if f.DataType in [ftInteger] then result:=result+','+f.asString
    else if f.DataType in [ftDate, ftDateTime]
       then result:=result+','+QuotedStr(getSqlDateTime(f.asString))
    else result:=result+','+QuotedStr(f.asString);
  end;

end;



// --------- helper function
function TMainForm.getSqlDateTime(s: String): String;
var
  y,m,d,h,mi,se,ms: word;
  dt: TDateTime;
begin
  dt:=StrToDateTime(s);
  DecodeDateTime(dt,y,m,d,h,mi,se,ms);
  result:=Format('%.04d-%.02d-%.02d
%.02d:%.02d:%.02d',[y,m,d,h,mi,se]);
end;


If you need to just insert the records, switch statement.


If you only want to save your query-result use another TQuery and set
the SQL to:

qry.SQL.assign(yourResultQuery.SQL);
qry.SQL.Insert('CREATE TABLE [desired_tablename]', 0);


Good Luck :-)

----------------------------------------------------------------------
mangakissa · 2020-04-14 07:08

Quote from: tatamata on April 05, 2020, 09:40:51 amHello, please
suggest best way to persist an TSQLQuery result into a database (e.q
PostgreSQL) table, besides creating new query with "CREATE TABLE
AS..." statement?
Btw, it is sqlquery without predefined fielddefs, the sql string is
defined dynamically.
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 begin 
SQLQuery1.SQL.text := 'insert into mytable (val1, val2, val3) values
(:val1, :val2, :val3)';  SQLQuery1.parambyname('val1').AsString :=
'TSQLQuery';  SQLQuery1.parambyname('val2').AsString := 'is'; 
SQLQuery1.parambyname('val3').AsString := 'SQLdb'; 
SQLQuery1.execsql;  if SQLQuery1.rowsaffected = -1 then   
SQLTransaction1.rollback  else    SQLTransaction1.commit;end; Even by
defining your query automatically, use params !!