TSQLExporter proporties
lazarus / 16-Databases

----------------------------------------------------------------------
Hansvb · 2016-02-20 20:45

Is there some tutorial about the TSQLExporter? I want to know how to
use the next properties:

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 formatsetti
ngs.BooleanFalse;formatsettings.BooleanTrue;formatsettings.DateFormat;
formatsettings.IntegerFormat;
I hope that i can change for a field with a data type the result with
the formatsettings.DateFormat;

Now the export with a date type field =
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 INSERT INTO
TEST_TABLE VALUES (88804, '0199077D8B75B6A8E0500A0A573222E7',
 '28-2-2014');
I musts be
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 INSERT INTO
TEST_TABLE VALUES (88804, '0199077D8B75B6A8E0500A0A573222E7',
 to_date('28-2-2014', 'DD-MM-YYYY')); 
The last one will insert in Oracle without warnings. The first one
does not insert.

----------------------------------------------------------------------
rvk · 2016-02-20 22:09

Actually, I think its better to export a date directly as
'2014-02-28'.
As far as I know all databases interpret that format always correctly.

----------------------------------------------------------------------
Hansvb · 2016-02-20 22:41

How can that be done?

----------------------------------------------------------------------
rvk · 2016-02-20 22:47

I find it strange that it's not done already like that for a date
format because it's the most used date format and never goes wrong
(which is probably why most databases accept that format). I can't
look at the component at the moment but if it uses displayformat of
the field for exporting you could set that to YYYY-MM-DD. Otherwise I
would need to look into it later (or somebody else might know before
that).

----------------------------------------------------------------------
Mike.Cornflake · 2016-02-21 02:55

Quote from: rvk on February 20, 2016, 11:09:26 pmActually, I think its
better to export a date directly as '2014-02-28'.
As far as I know all databases interpret that format always correctly.

I have had issues with this in Oracle.  The issue is that the default
Oracle date format is dd-MON-yy (or was for my system), but this can
be overridden by editing the config.  And this is the reason that
to_date() is generally used.  To_date always returns its result in the
default date format configured.

Quote from: Hansvb on February 20, 2016, 09:45:49 pmI hope that i can
change for a field with a data type the result with the
formatsettings.DateFormat;

Now the export with a date type field =
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 INSERT INTO
TEST_TABLE VALUES (88804, '0199077D8B75B6A8E0500A0A573222E7',
 '28-2-2014');
I musts be
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 INSERT INTO
TEST_TABLE VALUES (88804, '0199077D8B75B6A8E0500A0A573222E7',
 to_date('28-2-2014', 'DD-MM-YYYY')); 
The last one will insert in Oracle without warnings. The first one
does not insert.

Your call is a good one.  As for the answer: No idea :-)  I never even
knew TSQLExporter existed.

----------------------------------------------------------------------
Hansvb · 2016-02-21 08:31

With integers i have the same problem. The decimalseperator is not
used it seems.

----------------------------------------------------------------------
Hansvb · 2016-02-21 09:32

Since the result of TSQLExporter is a tekst file i wrote a simple
tekst export function.
All the fields are quoted so i can import them in a table where all
the fields are from the type varchar. It not ideal but it works.

If some knows how to use the TSQLExporter i prefer that.

----------------------------------------------------------------------
rvk · 2016-02-21 11:13

For you manual export I have two remarks:
First it would still be preferable if you export as
to_date('2014-02-28', 'YYYY-MM-DD'). And you need to make sure you
fill the date part as 2014-02-28. Otherwise it could go wrong with a
computer with different data-settings. You could for instance export 
to_date('2/28/2014', 'YYYY-MM-DD') on another computer and that would
still go wrong. So always make sure you set the dateformat for export
to YYYY-MM-DD and format the date accordingly.

Next. You could leave out the to_date() part if you set an extra line
for Oracle to interpret the data as YYYY-MM-DD for that session. Set
this line as first line in the file:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i ALTER
SESSION SET nls_date_format='yyyy-mm-dd';That way, if someone wants to
import it to another DB (other than Oracle) (s)he just needs to remove
the first line and can imort the file without problems (because the
rest of the file doesn't contain Oracle specific command).

You can make TSQLExporter export the date correctly by setting
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
SQLExporter1.FormatSettings.DateFormat:='YYYY-MM-DD';
Furthermore, you could set the '.' as decimal separator by doing:
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
SQLExporter1.FormatSettings.DecimalSeparator:='.';But there is a
problem. Some fields could be ftCurrency, ftBCD or ftFMTBCD which
TSQLExporter doesn't handle (so the '.' formatting doesn't work on
those). You need to set the DisplayFormat for these. (If TSQLExporter
doesn't know the fieldtype it will use ffield.displaytext.)

Another option would be to set
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  
FormatSettings.DecimalSeparator:='.'; 
FormatSettings.ThousandSeparator:=',';
But now for the bad news... I didn't find a way to write to the file
before TSQLExporter does. And because you would need to write that
ALTER SESSION line in the file it might not be such a bad idea to just
go with you own export-function. I find a lot of things missing in
TSQLExporter and for your own use, your own procedure would be just as
fast (if not faster), lean and clean.

Then you can write the ALTER SESSION-line and use YYYY-MM-DD as
dateformat. And you can use the asString and check the correct
formatting for integers/float/bcd etc are all correct (i.e. with
decimal point as decimal separator and without thousand separator).

----------------------------------------------------------------------
Hansvb · 2016-02-21 12:36

Thanks, I will look at it and will go on with my own function i think.