Uploading Local DMS
From ICISWiki
Contents |
MICROSOFT ACCESS DATABASE
MYSQL DATABASE
This approach of uploading a local DMS into central DMS in MysQL database uses procedures and requires version 5.0 or higher. The procedures can be called just like any MySQL query.
STEPS IN UPLOADING LOCAL DMS TO CENTRAL DMS
1. Transfer MS Access Central and Local Database to MySQL
Click here.
2. Create the procedures if it does not exist
Scripts to upload a local DMS to central
Script to upload local DMS to central
3. Call the upload DMS procedures
call updateDMSwithQueries ('DMS_centralDatabaseName','DMS_localDatabaseName','GMS_localDatabaseName')
call uploadDMSwithQueries ('DMS_centralDatabaseName','DMS_localDatabaseName')
Procedure Description :
This procedure should be executed after the execution of checkCentralTMS procedure. UploadDMS assumes that all values remaining in the Trait, Method and Scale tables are negative values. The procedure does the following:
- gets the GIDs from local GMS using an UPDATEGID table and update central GIDs
- gets all maximum ids in the central database
- creates new tables that will serve as the lookup table for new IDs
- inserts all negative IDs from the local DMS to the newly created tables
- generates new IDs (maximum ID from central + an auto_increment number) which will be used to replace the negative values
- create index in the newly created tables for faster searching of values
- updates the negative IDs in the local DMS tables with the newly generated IDs from the new tables
uploadDMS with upload queries
The procedure contains a prepared statement that inserts local DMS values into the central DMS tables. Example of an query in the procedure.
SET @upload = concat('INSERT INTO ',centraldb,'.study ( StudyID, SNAME, PMKEY, Title, OBJECTIV, STYPE, SDATE, EDATE, USERID )
SELECT STUDYID, SNAME, PMKEY, TITLE,OBJECTIV, STYPE, SDATE, EDATE,USERID
FROM ',localdb,'.study');
PREPARE stmnt1 FROM @upload;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;
Issue commands for ROLLBACK
START TRANSACTION;
call procedure();
ROLLBACK;
OR
COMMIT;
Observed MySQL bugs for some versions
- Auto_increment in version 5.0.15-nt - If there are more than one auto_increment field, the next auto_increment field will begin with the last value of the first auto_increment field.
- Error in Usage of Union and INTO statement in version 5.1.22 - values from a UNION of tables cannot be stored into a variable.
