(Solved) Transferring table contents from one DB to another

[minesadorada]

So far I have used SQLQuery, Transaction and Dataset objects to do standard SQL SELECT, INSERT, ALTER stuff on most of the SQL databases, in an unsophisticated way.  i.e. set SQLQuery.SQL.Text, then SQLQuery.ExecSQL, or using SQL.Open then retrieving records via a DataSet (usually with Transaction control)

Now, all I want to do is open a source database (say, MSAccess), then a destination database (like SQLIte3 or MySQL) and transfer records from the source to the destination.  Source and destination can be any of the SQL databases.

The database and corresponding tables already exist in source and destination.

I already have working code to connect to the sources and destinations, but I have a feeling there is a simple way to use SQLQuery to read the records from source and write them to destination (after deleting existing records in destination) without having to read them into a dataset first.

I lack the Lazarus database experience however, to figure it out.   The solution should work on Windows and Linux.

Any help would be appreciated.

[mangakissa]

The source database doesn't know anything about the destination database. So you need an interface.
I don't think MSAcces can create an export file with queries. Because that's the easiest way to do.
But if you doesn't care about the engines, which are running, you really save to read the dataset  first and then copy each record to the destination database.

[minesadorada]

The source database doesn't know anything about the destination database. So you need an interface.
I don't think MSAcces can create an export file with queries. Because that's the easiest way to do.
But if you doesn't care about the engines, which are running, you really save to read the dataset  first and then copy each record to the destination database.

Thanks, copying records from dataset to dataset that was my first thought, but I had a feeling there might be a more elegant way.

=edit= Actually, I might have what I need from the wiki: http://wiki.freepascal.org/Working_With_TSQLQuery

[garlar27]

This is what I do to synchronize 2 different DB systems:

I define constants this way (NOTE: NOT SHURE IF WHAT POSTED IS 100% COMPATIBLE WITH THE ENGINES I INDICATE):

Code: [Select]

const
   //          //PsgrSQL//MSSQL                   //PostgreSQL             //
   CAST_PFX    = ''    ;//'cast(';                //'cast(';               // FOR UNSUPPORTED TYPES
   CAST_SFX_AS = ' as ';//' as nvarchar(36)) as ';//' as varchar(36)) as ';// FOR UNSUPPORTED TYPES

                                              //MSSQL   //PostgreSQL   //
   TID_PFX = ''; //< TABLE IDENTIFIER PREFIX  //'[';    //'public';    //
   TID_SFX = ''; //< TABLE IDENTIFIER SUFIX   //']';    //''      ;    //
   IDF_PFX = ''; //< IDENTIFIER PREFIX        //'[';    //'"'     ;    //
   IDF_SFX = ''; //< IDENTIFIER SUFIX         //']';    //'"'     ;    //
   SELECT_FMT       = 'SELECT %s FROM %s'; //   SELDOM USED....
   //   SOME "STANDARD" ASSIGNATIONS/COMPARITIONS   //
   FIELD_ENABLED_EQUALS_TO_TRUE  = 'ENABLED = ''1''';
   FIELD_ENABLED_EQUALS_TO_FALSE = 'ENABLED = ''0''';
   LOGIC_CHECK_IS_TRUE_FMT       = '%S = ''1''';
   LOGIC_CHECK_IS_FALSE_FMT      = '%S = ''0''';
   BEGIN_TRANSACTION             = 'BEGIN TRANSACTION' ;
   END_TRANSACTION               = 'COMMIT TRANSACTION';


   //                                                                                                                                     //
   //   TABLE PARAMETER                                                                                                                   //
   //_____________________________________________________________________________________________________________________________________//
   PARAMETER_TABLE_NAME          = TID_PFX+ 'PARAMETER'    +TID_SFX;
   PARAMETER__ID                 = IDF_PFX+ 'PARAMETER_ID' +IDF_SFX;
   PARAMETER_VALUE               = IDF_PFX+ 'VALUE'        +IDF_SFX;
   PARAMETER_DATA_TYPE           = IDF_PFX+ 'DATA_TYPE'    +IDF_SFX;
   PARAMETER_COMMENTS            = IDF_PFX+ 'COMMENTS'     +IDF_SFX;

   PARAMETER_Q__ID               = PARAMETER_TABLE_NAME +'.'+ PARAMETER__ID      ;
   PARAMETER_Q_VALUE             = PARAMETER_TABLE_NAME +'.'+ PARAMETER_VALUE    ;
   PARAMETER_Q_DATA_TYPE         = PARAMETER_TABLE_NAME +'.'+ PARAMETER_DATA_TYPE;
   PARAMETER_Q_COMMENTS          = PARAMETER_TABLE_NAME +'.'+ PARAMETER_COMMENTS ;

   PARAMETER_NONKEY_FIELD_LIST =       PARAMETER_VALUE
                               + ', '+ PARAMETER_DATA_TYPE
                               + ', '+ PARAMETER_COMMENTS
                               ;
   PARAMETER_FIELD_LIST =       PARAMETER__ID
                        + ', '+ PARAMETER_NONKEY_FIELD_LIST
                        ;
   PARAMETER_INSERT_VALUE_LIST_FMT = '%s, %s, %s, %s';
   PARAMETER__ID_EQUALS_TO_FMT = PARAMETER__ID +' = %s';
   PARAMETER_Q__ID_EQUALS_TO_FMT = PARAMETER_Q__ID +' = %s';


   PARAMETER_SELECT_FIELD_LIST = PARAMETER__ID
                               + ', '+ PARAMETER_VALUE
                               + ', '+ PARAMETER_DATA_TYPE
                               + ', '+ PARAMETER_COMMENTS
                               ;

   PARAMETER_SELECT_Q_FIELD_LIST = PARAMETER_Q__ID
                                 + ', '+ PARAMETER_Q_VALUE
                                 + ', '+ PARAMETER_Q_DATA_TYPE
                                 + ', '+ PARAMETER_Q_COMMENTS
                                 ;

   SQL_PARAMETER_INSERT_FMT = 'INSERT INTO '+ PARAMETER_TABLE_NAME +' ('+ PARAMETER_FIELD_LIST +') VALUES ('+ PARAMETER_INSERT_VALUE_LIST_FMT +');';

Later:

Code: [Select]

   SqlText := '';
   SourceDataSetPARAMETERS.First;
   while not SourceDataSetPARAMETERS.EOF do begin
      SqlText := SqlText + Format(SQL_PARAMETER_INSERT_FMT,
                                 [SourceDataSetPARAMETERS.FieldByName(PARAMETER__ID      ).AsString
                                 ,SourceDataSetPARAMETERS.FieldByName(PARAMETER_VALUE    ).AsString
                                 ,SourceDataSetPARAMETERS.FieldByName(PARAMETER_DATA_TYPE).AsString
                                 ,SourceDataSetPARAMETERS.FieldByName(PARAMETER_COMMENTS ).AsString
                                 ]);

      SourceDataSetPARAMETERS.Next;
   end;

   //   AFTER THIS LINE YOU KNOW IF EVERYTHING IS "IN" OR EVERYTHING IS "OUT"   //
   //   SqlText can be a HUUUUUUUUUUUUUUGE string   //
   SQLDestConnection.ExecuteDirect(BEGIN_TRANSACTION
                              +SqlText
                              +END_TRANSACTION
                              );


[minesadorada]

Just an Update: (geddit?)

I was toying with trying out InsertSQL, UpdateSQL and DeleteSQL methods of SQLQuery, but in the end I looped through the source dataset and did a plain SQLQuery.ExecSQL within a Transaction.

This is the easiest multiplatform code for me - with lots of different conversions between DBs for the simple tables I'm dealing with.

Thanks for the tips, @mangakissa and @garlar27.