Mysql, Postgresql, Oracle, SQLite3, MSSQL, Sybase ASE export help
requested
lazarus / 16-Databases

----------------------------------------------------------------------
BigChimp · 2014-06-24 08:24

Hi all,

As part of a dataset export tool, in dataset2sql.pas in [1] I have
this
Code: [Select]const
  SQLConnTypesNames: array [TSQLConnType] of string[19] =
    ('MYSQL40', 'MYSQL41', 'MYSQL50', 'MYSQL51', 'MYSQL55',
'POSTGRESQL',
    'INTERBASE' {actually meant for Firebird}, 'ODBC', 'ORACLE',
'SQLITE3', 'MSSQL', 'SYBASE',
    'ODBC_MSACCESS');
type
  TFieldMapping = record
    FieldClass: TFieldClass;
    SQL: array[TSQLConnType] of string;
  end;
const
  // Todo: this certainly needs verification/updates
  FieldMappings: array[0..Fields - 1] of TFieldMapping =
    ((FieldClass: TStringField; SQL: (' VARCHAR(', ' VARCHAR(', '
VARCHAR(', ' VARCHAR(', ' VARCHAR(',
    ' VARCHAR(', ' VARCHAR(', ' VARCHAR(', ' VARCHAR(', ' VARCHAR(', '
VARCHAR(', ' VARCHAR(', ' VARCHAR(')), // + size
    (FieldClass: TIntegerField; SQL: (' INTEGER ', ' INTEGER ', '
INTEGER ', ' INTEGER ', ' INTEGER ',
    ' INTEGER ', ' INTEGER ', ' INTEGER ', ' INTEGER ', ' INTEGER ', '
INTEGER ', ' INTEGER ', ' INTEGER ')),
    (FieldClass: TAutoIncField; SQL: (' INTEGER ', ' INTEGER ', '
INTEGER ', ' INTEGER ', ' INTEGER ',
    ' INTEGER ', ' INTEGER ', ' INTEGER ', ' INTEGER ', ' INTEGER ', '
INTEGER ', ' INTEGER ', ' INTEGER ')),
    (FieldClass: TLongIntField; SQL: (' INTEGER ', ' INTEGER ', '
INTEGER ', ' INTEGER ', ' INTEGER ',
    ' INTEGER ', ' INTEGER ', ' INTEGER ', ' INTEGER ', ' INTEGER ', '
INTEGER ', ' INTEGER ', ' INTEGER ')),
    (FieldClass: TSmallIntField; SQL: (' SMALLINT ', ' SMALLINT ', '
SMALLINT ', ' SMALLINT ', ' SMALLINT ',
    ' SMALLINT ', ' SMALLINT ', ' SMALLINT ', ' SMALLINT ', ' SMALLINT
', ' SMALLINT ', ' SMALLINT ', ' SMALLINT ')),
    (FieldClass: TFloatField; SQL: (' FLOAT ', ' DOUBLE ', ' DOUBLE ',
' DOUBLE ', ' DOUBLE ', ' DOUBLE ',
    ' DOUBLE PRECISION ', ' DOUBLE ', ' DOUBLE ', ' DOUBLE ', ' DOUBLE
', ' DOUBLE ', ' DOUBLE ')),
    //Firebird float: 32 bit,single precision, approx 7 decimal digits
    //Firebird double precision: 64 bit,double precision, approx 15
decimal digits
    (FieldClass: TDateTimeField; SQL: (' TIMESTAMP ', ' TIMESTAMP ', '
TIMESTAMP ', ' TIMESTAMP ',
    ' TIMESTAMP ', ' TIMESTAMP ', ' TIMESTAMP ', ' TIMESTAMP ', '
TIMESTAMP ', ' TIMESTAMP ', ' DATETIME ', ' DATETIME ', ' DATETIME
')),
    (FieldClass: TDateField; SQL: (' DATE ', ' DATE ', ' DATE ', '
DATE ', ' DATE ', ' DATE ', ' DATE ',
    ' DATE ', ' DATE ', ' DATE ', ' DATE ', ' DATE ', ' DATE ')),
    (FieldClass: TTimeField; SQL: (' TIME ', ' TIME ', ' TIME ', '
TIME ', ' TIME ', ' TIME ', ' TIME ',
    ' TIME ', ' TIME ', ' TIME ', ' TIME ', ' TIME ', ' TIME ')),
    (FieldClass: TCurrencyField; SQL: (' DECIMAL ', ' DECIMAL ', '
DECIMAL ', ' DECIMAL ', ' DECIMAL ',
    ' DECIMAL ', ' DECIMAL ', ' DECIMAL ', ' DECIMAL ', ' DECIMAL ', '
DECIMAL ', ' DECIMAL ', ' DECIMAL ')),
    (FieldClass: TBooleanField; SQL: (' BOOLEAN ', ' BOOLEAN ', '
BOOLEAN ', ' BOOLEAN ', ' BOOLEAN ', ' BOOLEAN ',
    ' INTEGER ', ' BOOLEAN ', ' BOOLEAN ', ' BOOLEAN ', ' BIT ', ' BIT
', ' BOOLEAN ')),
    (FieldClass: TMemoField; SQL: (' VARCHAR ', ' VARCHAR ', ' VARCHAR
', ' VARCHAR ', ' VARCHAR ', ' VARCHAR ',
    ' BLOB SUB_TYPE TEXT ', ' VARCHAR ', ' VARCHAR ', ' VARCHAR ', '
VARCHAR ', ' VARCHAR ', ' LONGTEXT ')),
    (FieldClass: TGraphicField; SQL: (' BLOB ', ' BLOB ', ' BLOB ', '
BLOB ', ' BLOB ', ' BLOB ', ' BLOB ',
    ' BLOB ', ' BLOB ', ' BLOB ', ' BLOB ', ' BLOB ', ' BLOB ')),
    (FieldClass: TBlobField; SQL: (' BLOB ', ' BLOB ', ' BLOB ', '
BLOB ', ' BLOB ', ' BLOB ', ' BLOB ',
    ' BLOB ', ' BLOB ', ' BLOB ', ' BLOB ', ' BLOB ', ' BLOB '))
    );
which is used to export dataset types to various databases using SQL
statements for field types.
The Firebird and Access versions are reasonably ok - I'd appreciate
suggestions for the others.

Thanks a lot!

Regards,
Reinier

[1]
https://bitbucket.org/reiniero/smalltools/src
directory BankConvert

----------------------------------------------------------------------
LacaK · 2014-06-25 05:24

HTH:
http://en.wikipedia.org/wiki/Comparison_of_relational_database_managem
ent_systems#Data_types