How to save data from DBGRID to the oracle database
lazarus / 16-Databases

----------------------------------------------------------------------
prcstb · 2021-06-19 08:15

I have a form with dbgrid, TDbf and TCSVDataset. Depending on the
situation, I read data from csv file or dbf file to dbgrid. And up to
this point, I have no problem. My question is, is it possible to put
this data into the oracle database after filling in the dbgrid as an
insert into an existing table? Second case: by creating a new table
with data from DBGrid.

The path looks like this: eg CSV File -> DBGrid -> Oracle Database
Table(?).

----------------------------------------------------------------------
prcstb · 2021-06-19 08:17

And of course how to do it?

----------------------------------------------------------------------
Soner · 2021-06-19 10:57

DBGrid does not contain any data. TDbf or TCSVDataset contain data and
DBGrid shows it via TDataSource.
You must connect to oracle database with SQLDB and then go TDbf from
first to the last row and insert it in Oracle.

Look at the database wiki and the tutorial.

This is how it works (pseudo code):
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   //open
tdbf    //connect to oracle db.  You need TSQLQuery   // and connector
T...Connection (I don't know which one is for oracle  // maybe
TODBCConnection   tdbf1.first;  while not tdbf1.eof do begin      //
add current row to oracle db.       tdbf1.next; // scroll to the next
row  end;   // commit inserts to oracle db.  //close databases

----------------------------------------------------------------------
wp · 2021-06-19 11:03

You are thinking in the wrong way. The DBGrid is just a component to
display a dataset, it only loads the records in the vicinity of those
shown in the visible part of the grid.

Your problem in reality is an import/export problem. In Lazarus you
can find dedicated components for that purpose (page, "Data Export",
https://wiki.freepascal.org/lazdbexport,
https://wiki.freepascal.org/fpdbfexport). In your case the dataset
from which data are exported is the TCSVDataset, and the database
receiving the data is the Orace db which can be addressed by the
TOracleConnection and the other components of the SQLdb tab.
Therefore, you should use the TSQLExporter from the DataExport tab
which does all the work for you: Assign the CSVDataset to the Dataset
property, specify the fields to be exported in "ExportFields" and call
the method "Execute" of the exporter -- there are also demos in folder
compoents/dbexport/demo of your Lazarus installation.

----------------------------------------------------------------------
prcstb · 2021-06-20 10:25

OK wp,
The option from SQLExporter sounds cool and is simple (excluding
tables with the size of millions of records) - generating inserts for
StringStream and then executing them on the database solves the
problem if the table exists. What about the case when we have a
CSVDataset and we want to throw it into a non-existing table in a
similar way - how to quickly / easily create a table that will be
compatible with this Dataset? It is possible?
(I mean, for example, to quickly create temporary tables based on a
CSV file, and then process this data before migrating to the right
tables - this is the main reason why I want to do something like this
(I create a tool supporting work on ORACLE databases, data
migration/export/import etc. This tool i opensource btw).

----------------------------------------------------------------------
wp · 2021-06-20 12:18

I quickly scanned the SQLExporter code and found that there are only
INSERT and UPDATE SQL statements, no CREATETABLE. Therefore, I guess
you must first check whether your oracle DB already contains the new
table and, if not, create the table yourself before you can activate
the SQLExporter.

----------------------------------------------------------------------
prcstb · 2021-06-20 16:02

I know SQLExporter does not have a CREATE TABLE. I meant some other
way: creating SQL from CREATE TABLE from DataSet, maybe somehow with
Metadata, or something else, maybe there is some component that can
help with this?

----------------------------------------------------------------------
prcstb · 2021-06-20 17:27

I have one more question: how to handle dates in SQLExporter, in the
sense that the standard date looks like '2021-01-01' or '2021-01-01
12:23:00'. What should I do for the insert to be to_date
('2021-01-01', 'YYYY-MM-DD') and to_date ('2021-01-01 12:23:00',
'YYYY-MM-DD HH24: MI : SS ')? Is there any function / procedure /
property for this?

----------------------------------------------------------------------
Zvoni · 2021-06-21 06:56

Quote from: prcstb on June 20, 2021, 07:27:47 pmI have one more
question: how to handle dates in SQLExporter, in the sense that the
standard date looks like '2021-01-01' or '2021-01-01 12:23:00'. What
should I do for the insert to be to_date ('2021-01-01', 'YYYY-MM-DD')
and to_date ('2021-01-01 12:23:00', 'YYYY-MM-DD HH24: MI : SS ')? Is
there any function / procedure / property for this?
If the SQLExporter uses INSERT/UPDATE, why not use Parameters?
Nota Bene: haven't looked at SQLExporter, so no idea, of it supports
Parameters

----------------------------------------------------------------------
wp · 2021-06-21 08:40

I'm not the author of the DataExport components and, in fact, never
used them. But scanning through the code I don't see usage of SQL
parameters. Each exporter class, however, has a property
FormatSettings with - among others - a DateFormat, TimeFormat and
DateTimeFormat mask string. They default to the values of the
SysUtils.DefaultFormatSettings, but if you need an ISO datetime, for
example, you can set DateTimeFormat to 'YYYYMMDD"T"HHNNSS"Z"'.

----------------------------------------------------------------------
egsuh · 2021-06-21 09:13

It should not be difficult to create new table, as field name and data
type of each field are given from TDataSet and its descendants.

----------------------------------------------------------------------
Zvoni · 2021-06-21 09:43

Quote from: wp on June 21, 2021, 10:40:48 amI'm not the author of the
DataExport components and, in fact, never used them. But scanning
through the code I don't see usage of SQL parameters. Each exporter
class, however, has a property FormatSettings with - among others - a
DateFormat, TimeFormat and DateTimeFormat mask string. They default to
the values of the SysUtils.DefaultFormatSettings, but if you need an
ISO datetime, for example, you can set DateTimeFormat to
'YYYYMMDD"T"HHNNSS"Z"'.

Yeah, i looked through it, too, and you're right: No Parameters.
And it actually makes sense that there are no Parameters, since
TSQLExporter creates a plain textfile with SQL-Statements (which can
be loaded into a Database, specific SQL-Dialect not withstanding)
I misunderstood op's question, since to me it read that he wants to
export to oracle on the fly (using a second connection-trinity)

----------------------------------------------------------------------
prcstb · 2021-07-03 10:13

You don't understand. SQL Exporter creates an insert of the form:
INSERT INTO test (AGDAT) VALUES ('2011-04-05 10:08:25');
such an insert will never be made on the Oracle database, to be
executed it must have the form: INSERT INTO pacj (AGDAT)
 VALUES (to_date ('2011-04-05 10:08:25', 'YYYY-MM-DD HH24: MI: SS');
My question was how to get such an insert using SQLExporter in a
simple way, without writing special / dedicated functions, is there
any parameter / function / procedure for it?

----------------------------------------------------------------------
prcstb · 2021-07-04 09:56

OK, I changed the source from fpSQLExporter. In the function
TCustomSQLExporter.SQLValue I added what not what:

was:

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 Function
TCustomSQLExporter.SQLValue(F : TField) : String; begin 
Result:=FormatField(F);  If (F.DataType in
BlobFieldTypes+StringFieldTypes+MemoFieldTypes+DateFieldTypes) then  
Result:=''''+QuoteFIeld(Result)+'''';end; 
now is:

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 Function
TCustomSQLExporter.SQLValue(F : TField) : String;const  LIT_PREF =
'to_date(''';  LIT_SUFF = ''',''YYYY-MM-DD HH24:MI:SS'')'; 
LIT_DATE_TYPES = [ftDateTime,ftTimeStamp];begin 
Result:=FormatField(F);  If (F.DataType in
BlobFieldTypes+StringFieldTypes+MemoFieldTypes+DateFieldTypes) then  
if F.DataType in LIT_DATE_TYPES then      Result:=
LIT_PREF+QuoteFIeld(Result)+LIT_SUFF    else     
Result:=''''+QuoteFIeld(Result)+'''';end;
now it's ok
I am writing this because it may be useful to someone.

----------------------------------------------------------------------
wp · 2021-07-04 10:36

Rather than modifying the TCustomSQLExporter sources I would create a
derived explorer class (TOracleSQLExporter = class(TCustomSQLExporter)
which frees me from the burdon of updating the code with every Lazarus
update. Unfortunately SQLValue is a private static function and thus
cannot be modified by a descendant. Looking at the sources you see
that SQLValue is called by two methods, ExportField() and
DoDataRowEnd(). Both of them are marked as "override" which means that
they will be replaced by the implementations in your descendant class.
You could simply copy their code from TCustomSQLExporter into your
descendant class, and you could put your new SQLValue here which
should be called now.

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 type 
TOracleSQLExporter = class(TCustomSQLExporter)  protected   
FCurrentRow: String;    function SQLValue(F: TField): String;   //
virtual; declaring it virtual would allow you to derive another
descendant class from the OracleExporter    procedure ExportField(EF :
TExportFieldItem); override;    procedure DoDataRowEnd; override;   
procedure DoDataRowStart; override;  end;...   function
TOracleSQLExporter.SQLValue(F: TField): String;  begin   
Result:=FormatField(F);    If (F.DataType in
BlobFieldTypes+StringFieldTypes+MemoFieldTypes+DateFieldTypes) then  
  Result:=''''+QuoteFIeld(Result)+'''';    end; procedure
TOracleSQLExporter.ExportField(EF : TExportFieldItem); // just copy
the code of the same method from TCustomSQLExporterVar  S :
string;begin  If (FSK<>skUpdate) or (Not
TSQLExportFieldItem(EF).KeyField) then    begin    If
(FCurrentRow<>'') then      FCurrentRow:=FcurrentRow+', ';   
S:=SQLValue(EF.FIeld);    If FSK<>skUpdate then     
FCurrentRow:=FCurrentRow+S    else     
FCurrentRow:=FCurrentRow+MaybeQuote(EF.ExportedName)+'='+S;   
end;end; procedure TOracleSQLExporter.DoDataRowStart;begin 
FCurrentRow:=''end; procedure TOracleSQLExporter.DoDataRowEnd; //
again copy the code of the samVar  S,T : String;  I   : Integer;  EF
 : TExportFieldItem;begin  If FSK<>skUpdate then    begin   
S:='INSERT INTO '+MaybeQuote(FTN);    If FSK=skFullInsert then     
begin      S:=S+' (';      T:='';      For I:=0 to
ExportFields.Count-1 do        begin        EF:=ExportFields[i];     
 If EF.Enabled then          begin          If (T<>'') then          
T:=T+', ';          T:=T+MaybeQuote(EF.ExportedName);          end;  
    end;      S:=S+T+')';      OutputRow(S);      S:=''      end;   
S:=S+' VALUES ('+FCurrentRow+');';    end  else    begin    S:='UPDATE
'+MaybeQuote(FTN)+' SET '+FCurrentRow;    OutputRow(S);    S:='(';   
For I:=0 to ExportFields.Count-1 do      begin     
EF:=ExportFields[i];      If TSQLExportFieldItem(EF).KeyField then   
   begin        If (S<>'(') then          S:=S+') AND (';       
S:=S+MaybeQuote(EF.ExportedName)+' = '+SQLValue(EF.Field);       
end;      end;    S:=' WHERE '+S+');';    end;  OutputRow(S);end;
I did not test this (since I don't have access to Oracle databases),
but I think it should work; the code also takes care of the field
FCurrentRow which is private in TCustomSQLExporter (I get the
impression that at least the SQL exporter component has not been
design with extendability in mind - I think declaring SQLValue as a
virtual protected method and FCurrentRow as protected (rather than
private) would make the component much more flexible).

----------------------------------------------------------------------
prcstb · 2021-07-05 14:30

SQLExporter is completely unsuitable for Oracle, maybe it is ok on
other databases, but for Oracle it requires too much rework to be good
.... can't handle null, can't handle dates, etc ...
Maybe you know something else?