Recent

Author Topic: How to save data from DBGRID to the oracle database  (Read 9343 times)

prcstb

  • New member
  • *
  • Posts: 8
How to save data from DBGRID to the oracle database
« on: June 19, 2021, 10:15:20 am »
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

  • New member
  • *
  • Posts: 8
Re: How to save data from DBGRID to the oracle database
« Reply #1 on: June 19, 2021, 10:17:47 am »
And of course how to do it?

Soner

  • Full Member
  • ***
  • Posts: 240
Re: How to save data from DBGRID to the oracle database
« Reply #2 on: June 19, 2021, 12:57:20 pm »
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][+][-]
  1.   //open tdbf
  2.  
  3.   //connect to oracle db.  You need TSQLQuery
  4.   // and connector T...Connection (I don't know which one is for oracle
  5.   // maybe TODBCConnection
  6.  
  7.   tdbf1.first;
  8.   while not tdbf1.eof do begin
  9.  
  10.      // add current row to oracle db.
  11.  
  12.      tdbf1.next; // scroll to the next row
  13.   end;
  14.  
  15.   // commit inserts to oracle db.
  16.  
  17.  //close databases
  18.  

wp

  • Hero Member
  • *****
  • Posts: 8555
Re: How to save data from DBGRID to the oracle database
« Reply #3 on: June 19, 2021, 01:03:01 pm »
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.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

prcstb

  • New member
  • *
  • Posts: 8
Re: How to save data from DBGRID to the oracle database
« Reply #4 on: June 20, 2021, 12:25:44 pm »
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

  • Hero Member
  • *****
  • Posts: 8555
Re: How to save data from DBGRID to the oracle database
« Reply #5 on: June 20, 2021, 02:18:41 pm »
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.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

prcstb

  • New member
  • *
  • Posts: 8
Re: How to save data from DBGRID to the oracle database
« Reply #6 on: June 20, 2021, 06:02:09 pm »

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

  • New member
  • *
  • Posts: 8
Re: How to save data from DBGRID to the oracle database
« Reply #7 on: June 20, 2021, 07:27:47 pm »
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

  • Hero Member
  • *****
  • Posts: 627
Re: How to save data from DBGRID to the oracle database
« Reply #8 on: June 21, 2021, 08:56:55 am »
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?
If the SQLExporter uses INSERT/UPDATE, why not use Parameters?
Nota Bene: haven't looked at SQLExporter, so no idea, of it supports Parameters
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircraft

wp

  • Hero Member
  • *****
  • Posts: 8555
Re: How to save data from DBGRID to the oracle database
« Reply #9 on: June 21, 2021, 10:40:48 am »
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"'.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

egsuh

  • Hero Member
  • *****
  • Posts: 783
Re: How to save data from DBGRID to the oracle database
« Reply #10 on: June 21, 2021, 11:13:04 am »
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

  • Hero Member
  • *****
  • Posts: 627
Re: How to save data from DBGRID to the oracle database
« Reply #11 on: June 21, 2021, 11:43:59 am »
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"'.

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)
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircraft

prcstb

  • New member
  • *
  • Posts: 8
Re: How to save data from DBGRID to the oracle database
« Reply #12 on: July 03, 2021, 12:13:51 pm »
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

  • New member
  • *
  • Posts: 8
Re: How to save data from DBGRID to the oracle database
« Reply #13 on: July 04, 2021, 11:56:46 am »
OK, I changed the source from fpSQLExporter. In the function TCustomSQLExporter.SQLValue I added what not what:

was:

Code: Pascal  [Select][+][-]
  1. Function TCustomSQLExporter.SQLValue(F : TField) : String;
  2.  
  3. begin
  4.   Result:=FormatField(F);
  5.   If (F.DataType in BlobFieldTypes+StringFieldTypes+MemoFieldTypes+DateFieldTypes) then
  6.     Result:=''''+QuoteFIeld(Result)+'''';
  7. end;  

now is:

Code: Pascal  [Select][+][-]
  1. Function TCustomSQLExporter.SQLValue(F : TField) : String;
  2. const
  3.   LIT_PREF = 'to_date(''';
  4.   LIT_SUFF = ''',''YYYY-MM-DD HH24:MI:SS'')';
  5.   LIT_DATE_TYPES = [ftDateTime,ftTimeStamp];
  6. begin
  7.   Result:=FormatField(F);
  8.   If (F.DataType in BlobFieldTypes+StringFieldTypes+MemoFieldTypes+DateFieldTypes) then
  9.     if F.DataType in LIT_DATE_TYPES then
  10.       Result:= LIT_PREF+QuoteFIeld(Result)+LIT_SUFF
  11.     else
  12.       Result:=''''+QuoteFIeld(Result)+'''';
  13. end;

now it's ok
I am writing this because it may be useful to someone.

wp

  • Hero Member
  • *****
  • Posts: 8555
Re: How to save data from DBGRID to the oracle database
« Reply #14 on: July 04, 2021, 12:36:58 pm »
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][+][-]
  1. type
  2.   TOracleSQLExporter = class(TCustomSQLExporter)
  3.   protected
  4.     FCurrentRow: String;
  5.     function SQLValue(F: TField): String;   // virtual; declaring it virtual would allow you to derive another descendant class from the OracleExporter
  6.     procedure ExportField(EF : TExportFieldItem); override;
  7.     procedure DoDataRowEnd; override;
  8.     procedure DoDataRowStart; override;
  9.   end;
  10. ...
  11.  
  12.   function TOracleSQLExporter.SQLValue(F: TField): String;
  13.   begin
  14.     Result:=FormatField(F);
  15.     If (F.DataType in BlobFieldTypes+StringFieldTypes+MemoFieldTypes+DateFieldTypes) then
  16.       Result:=''''+QuoteFIeld(Result)+'''';  
  17.   end;
  18.  
  19. procedure TOracleSQLExporter.ExportField(EF : TExportFieldItem);
  20. // just copy the code of the same method from TCustomSQLExporter
  21. Var
  22.   S : string;
  23. begin
  24.   If (FSK<>skUpdate) or (Not TSQLExportFieldItem(EF).KeyField) then
  25.     begin
  26.     If (FCurrentRow<>'') then
  27.       FCurrentRow:=FcurrentRow+', ';
  28.     S:=SQLValue(EF.FIeld);
  29.     If FSK<>skUpdate then
  30.       FCurrentRow:=FCurrentRow+S
  31.     else
  32.       FCurrentRow:=FCurrentRow+MaybeQuote(EF.ExportedName)+'='+S;
  33.     end;
  34. end;
  35.  
  36. procedure TOracleSQLExporter.DoDataRowStart;
  37. begin
  38.   FCurrentRow:=''
  39. end;
  40.  
  41. procedure TOracleSQLExporter.DoDataRowEnd;
  42. // again copy the code of the sam
  43. Var
  44.   S,T : String;
  45.   I   : Integer;
  46.   EF  : TExportFieldItem;
  47. begin
  48.   If FSK<>skUpdate then
  49.     begin
  50.     S:='INSERT INTO '+MaybeQuote(FTN);
  51.     If FSK=skFullInsert then
  52.       begin
  53.       S:=S+' (';
  54.       T:='';
  55.       For I:=0 to ExportFields.Count-1 do
  56.         begin
  57.         EF:=ExportFields[i];
  58.         If EF.Enabled then
  59.           begin
  60.           If (T<>'') then
  61.             T:=T+', ';
  62.           T:=T+MaybeQuote(EF.ExportedName);
  63.           end;
  64.         end;
  65.       S:=S+T+')';
  66.       OutputRow(S);
  67.       S:=''
  68.       end;
  69.     S:=S+' VALUES ('+FCurrentRow+');';
  70.     end
  71.   else
  72.     begin
  73.     S:='UPDATE '+MaybeQuote(FTN)+' SET '+FCurrentRow;
  74.     OutputRow(S);
  75.     S:='(';
  76.     For I:=0 to ExportFields.Count-1 do
  77.       begin
  78.       EF:=ExportFields[i];
  79.       If TSQLExportFieldItem(EF).KeyField then
  80.         begin
  81.         If (S<>'(') then
  82.           S:=S+') AND (';
  83.         S:=S+MaybeQuote(EF.ExportedName)+' = '+SQLValue(EF.Field);
  84.         end;
  85.       end;
  86.     S:=' WHERE '+S+');';
  87.     end;
  88.   OutputRow(S);
  89. 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).
   

Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

 

TinyPortal © 2005-2018