DB to Script

The DB to Script (Database to Script) module allows you to generate SQL scripts from table contents.

These INSERT or UPDATE scripts are saved as *.sql files and can be imported to a differen database, for example.

This can find application, for example, for transferring tables from a test database to a productive database or for sending settings tables (settings, FIBU configurations, etc.) to es2000 Support for analysis.

 

es2000 does not make any recommendation regarding the suitability of the following information. Errors and exceptions are excluded and no responsibility is accepted for the information provided. The statements are simply descriptions and do not guarantee the consistency of the products. The information may also in part be regarded as an attempt to provide you with assistance for a task even if the product was not actually intended for this special purpose.

To open the DB to Script module, click on the DB to Script icon button in the esdbm program window.

Alias

Select a database alias from the list.

 

The alias ESWin is entered by default. You can also select a different alias.

The database aliases shown are read out from the dbxconnections.ini file in the esoffice para directory. You can view / edit the database connections under Connection settings.

 

Log in data

An esoffice user login is required when a module is opened or a function that acesses an esoffice database is executed.

The user requires 110 - Basic settings authorization.

If you have successfully logged in to an esoffice database (alias) in esdbm, your log in data will not be requested again when you access the database alias again.

Format

The Format selection determines the target format for the script to be created.

You can choose between Firebird and MSSQL SQL database systems.

 

It is important that the correct target database system is selected because the SQL syntax for the two database systems differs in parts.

'Mode' area

The target script mode is set in this area.

Insert

When you select Insert, a script with the selected tables is created which will replace the previous contents of the target tables. A delete command (Firebird: DELETE FROM, MSSQL: TRUNCATE TABLE) is generated in an Insert script first. This empties the tables in the target system before the contents from the script are written in the table by the INSERT SQL command

 

When an INSERT script is run, data will be deleted from the target database system before the data is inserted.

You should therefore take appropriate measures (for example by backing up the database) to avoid losing data if an unsuitable script is run on a table.

 

Example Firebird Insert script

Example MSSQL Insert script

Update

When you select Update, a script with the selected tables is created which will update the previous contents of the target tables. The existing data sets in the target system are updated by the Update SQL command in an Update script.

 

Example Firebird Update script

Example MSSQL Update script

'Tables' area

The database tables to be exported as a script are entered in the Tables area.

Enter one table name per line. These tables will then be exported as a database script in a file.

You can filter the tables by entering a space followed by an SQL filter after the table name.

 

Filter conditions can be expanded using AND or OR.

 

A more in depth knowledge of databases, such as the reference integrities of the tables, may be required in order to create individual table and filter conditions. Although errors in the order of the tables in respect of the references would not have any effect on export within this module, they may cause problems later when the SQL script is run.

 

Basic settings

EinAllg

EinAuArt

EinFile

EinGlob

EinPers

EinPersM

Project forms

REPVARS

REPVARSD

REPTEXT

REPTEXTD

REPMEMO

REPFORM

User / Group authorizations

PROGMOD

PERS_ZUG

PERSZUGR

PRMOGRUP

PROGGRUP

Data grid settings

GRIDJOIN

GRIDJOID

GRIDTAB

 

You can save table and filter conditions as a definition for later use.

Also see Save DB to Script Definition

Start export

When you have made all the settings, you can start creation of the script by clicking the Start export button.

A file selection dialog opens where you can specify the path and file name for the script.

 

Assign a meaningful name to the script file and include the mode in the file name (INSERT or UPDATE), and also the target database system (Firebird or MSSQL) if required.

Click on the Save button to create the script.

A progress bar is displayed next to the Start export button. The process is complete when the bar reaches the right hand edge.

Error messages are displayed if syntax errors are found in the SQL commands.

The SQL file that is generated can then be viewed using an editor (such as Notepad) for example.

Save DB to Script Definition

The table and filter conditions of a DB to Script query can be saved as a file for later use.

Save DB2Script Definition

  1. Enter the database tables and filter conditions in the Tables area.

  2. Select the Save as command in the File menu.

  3. You can specify a path and a file name in the file selection window.

    The DB to Script Definition is saved as a *.db2sql file.

     

    You can open files in *.db2sql format (DB to Script Definition) using the Open file command in the File menu.

    The DB to Script module opens when you open a *.db2sql file.