Marc,
Remember, whatever the hurdles may be, stick to the basic
principles of success, NEVER give up!
Based upon my last reply, I have prepared a sample application
with two databases (MasterDB and ArchDB) along with a command
file to help you understand the swift technique to migrate
data between two databases using the DSN-Less connection.
Sample Application: Migrating Data using DSN-Less Connection
Supported Versions: R:BASE 7.6 and Turbo V-8 for Windows
Date Posted: October 28, 2008
URL: http://www.razzak.com/sampleapplications
-----------------------------------------------------------------------
-- Using R:BASE 7.6 for Windows
-----------------------------------------------------------------------
-- Migrating_Data_Using_DSNLessConnection76.RMD
-- Migrating data between two databases using DSN-Less Connection
-- Using R:BASE 7.6 Database ODBC Driver
-- Author: A. Razzak Memon
-- Date Created: October 28, 2008
-- Related Database: MasterDB, ArchDB
-- Common table to migrate data in both databaes: Contact
-- Assure the connection of correct database
IF (CVAL('DATABASE')) <> 'ArchDB' OR (CVAL('DATABASE')) IS NULL THEN
CONNECT ArchDB IDENTIFIED BY NONE
ENDIF
-- Make sure that previously SATTACHed table is detached
SDETACH tContact NOCHECK
-- Make sure that the previously DSN-Less Database is Disconnected
SDISCONNECT ';Driver=R:BASE 7.6 Database Driver (*.rb1);dbq=MasterDB'
-- Now Connect the Database Using the DSN-Less Connection
SCONNECT ';Driver=R:BASE 7.6 Database Driver (*.rb1);dbq=MasterDB'
-- SAttach Table(s)
SATTACH Contact AS tContact USING ALL
-- Review all records in SAttached tContact table (MasterDB Database)
BROWSE * FROM tContact
-- Review all records in actual Contact table (ArchDB Database)
BROWSE * FROM Contact
-- Project a temporary table with non matching <> records in ArchDB
SET ERROR MESSAGE 2038 OFF
DROP TABLE tmpContact
SET ERROR MESSAGE 2038 ON
PROJECT TEMPORARY tmpContact FROM tContact USING ALL +
WHERE ContID NOT IN (SELECT ContID FROM Contact)
INSERT INTO Contact +
(CustID,ContID,ContFName,ContLName,ContPhone, +
ContFax,ContCell,ContPager,ContEMail,ContInfo, +
LastContactDate) +
SELECT +
CustID,ContID,ContFName,ContLName,ContPhone, +
ContFax,ContCell,ContPager,ContEMail,ContInfo, +
LastContactDate +
FROM tempContact
-- Now review all records in actual Contact table (ArchDB Database)
BROWSE * FROM Contact
-- Drop Temporary Table
DROP TABLE tmpContact
-- Make sure that SATTACHed table is detached
SDETACH tContact NOCHECK
-- Make sure that DSN-Less Database is Disconnected
SDISCONNECT ';Driver=R:BASE 7.6 Database Driver (*.rb1);dbq=MasterDB'
RETURN
-----------------------------------------------------------------------
-- Using R:BASE Turbo V-8 for Windows
-----------------------------------------------------------------------
-- Migrating_Data_Using_DSNLessConnection80.RMD
-- Migrating data between two databases using DSN-Less Connection
-- Using R:BASE Turbo V-8 Database ODBC Driver
-- Author: A. Razzak Memon
-- Date Created: October 28, 2008
-- Related Database: MasterDB, ArchDB
-- Common table to migrate data in both databaes: Contact
-- Assure the connection of correct database
IF (CVAL('DATABASE')) <> 'ArchDB' OR (CVAL('DATABASE')) IS NULL THEN
CONNECT ArchDB IDENTIFIED BY NONE
ENDIF
-- Make sure that previously SATTACHed table is detached
SDETACH tContact NOCHECK
-- Make sure that the previously DSN-Less Database is Disconnected
SDISCONNECT ';Driver=R:BASE 8.0 Database Driver (*.rx1);dbq=MasterDB'
-- Now Connect the Database Using the DSN-Less Connection
SCONNECT ';Driver=R:BASE 8.0 Database Driver (*.rx1);dbq=MasterDB'
-- SAttach Table(s)
SATTACH Contact AS tContact USING ALL
-- Review all records in SAttached tContact table (MasterDB Database)
BROWSE * FROM tContact
-- Review all records in actual Contact table (ArchDB Database)
BROWSE * FROM Contact
-- Project a temporary table with non matching <> records in ArchDB
SET ERROR MESSAGE 2038 OFF
DROP TABLE tmpContact
SET ERROR MESSAGE 2038 ON
PROJECT TEMPORARY tmpContact FROM tContact USING ALL +
WHERE ContID NOT IN (SELECT ContID FROM Contact)
INSERT INTO Contact +
(CustID,ContID,ContFName,ContLName,ContPhone, +
ContFax,ContCell,ContPager,ContEMail,ContInfo, +
LastContactDate) +
SELECT +
CustID,ContID,ContFName,ContLName,ContPhone, +
ContFax,ContCell,ContPager,ContEMail,ContInfo, +
LastContactDate +
FROM tempContact
-- Now review all records in actual Contact table (ArchDB Database)
BROWSE * FROM Contact
-- Drop Temporary Table
DROP TABLE tmpContact
-- Make sure that SATTACHed table is detached
SDETACH tContact NOCHECK
-- Make sure that DSN-Less Database is Disconnected
SDISCONNECT ';Driver=R:BASE 8.0 Database Driver (*.rb1);dbq=MasterDB'
RETURN
Have fun!
Very Best R:egards,
Razzak.
