perist sqlquery result into database?

[tatamata]

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]

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]

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]

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]

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.

Code: Pascal  [Select][+][-]

1. begin
2.   SQLQuery1.SQL.text := 'insert into mytable (val1, val2, val3) values (:val1, :val2, :val3)';
3.   SQLQuery1.parambyname('val1').AsString := 'TSQLQuery';
4.   SQLQuery1.parambyname('val2').AsString := 'is';
5.   SQLQuery1.parambyname('val3').AsString := 'SQLdb';
6.   SQLQuery1.execsql;
7.   if SQLQuery1.rowsaffected = -1 then
8.     SQLTransaction1.rollback
9.   else
10.     SQLTransaction1.commit;
11. end;
12.  

Even by defining your query automatically, use params !!