Data Management > SQL Environment

This will bring you in a complete SQL environment where you can do many tasks on ALL your 'SQL' Data Bases

dmc_sql_envir_mt_query

You have a setting to Read SQL Views (or not) - a different ICON is used to help you differentiate them

You have 4 Lists on this screen plus a manual or "assisted" query builder

The first List contains all registered Data Bases in DMC (ODBC connections)

A double-click on any DB Name will list in the Table name list all the existing Tables and VIEWS (depending on setting)

Again a double-click on a Table will List the KEYS and COLUMN in the last two Lists

(you will also have the number of records in the selected table displayed onscreen)

When you have selected a table you can either :

1. Directly open this table in the Viewer (full mode with Mass Update and QBE etc ...) and REVERT back to this screen with a special button in the Viewer

(perform any work needed on the Table here or use the "Back to SQL Editor" button to switch back)

2. Generate random Data for the selected table with the corresponding button and see it displayed on screen and switch back again to the SQL environment screen.

After selecting the number of records to generate, a progress bar will keep you informed of the process

(this screen will open up to let you visualize the generated data)

(use the "Back to SQL Editor" button to revert back to the main screen)

3. Use the Query Builder - Editor to work on any Table(s) in the selected Data Base

From the Table KEY and Column Lists simply use the drag and drop possibility to add (at cursor position) whatever "element" the query requires

Click on "Execute" and depending on success or not a different message will popup to let you know.

(the query remains in memory and on screen, which means you can use it several times changing only part elements from it)

You can of course create much more complicated queries like this one to obtain Multi Table subsets which you will then be able to export or to CREATE a TPS Table or SQL Table or an SQL VIEW directly from the subset returned.(use the "Table Structures Infos" TAB to export the Structure to TXD / DCTX)

When you click on "Execute" - after the success message - a new Tab will open up with the resulting data subset displayed

You can export to predefined formats with the "Send To" button OR ....

Click on the "SQL Script Editor" Tab to create a NEW Table (TPS or SQL in the queried DB) with the corresponding structure of the extracted subset

You have now the possibility to export this subset either to TPS or SQL (in the current Data base) Table after defining a valid UNIQUE Table Name and clicking on the corresponding button

After which this new table will be created and all data exported automatically.

(a confirmation message will let you carry on or cancel) and the Table will be displayed on screen for you

You can CREATE DATABASE also if you know the syntax : connect to the Master DB for example and type in CREATE DATABASE 'DBNAME' and click on "Execute" - then go to ODBC connections and create a new one to connect to this new DB

When you have selected a DataBase you can create a NEW DataBase

When you have selected with a double-click any existing connection (except Access and HyperFile), you will be able to use a "Create DB" button just underneath the "Execute" one

This will open up a popup message asking for confirmation and a DB Name to be filled in

DMC will then proceed onto creating the DataBase for you AND also creating a NEW connection this this newly created DB AND displaying it onscreen immediately

When an SQL VIEW is selected there is a TAB where the server side DEFINITION is displayed

dmc_sql_view_definition

(the SQL Data TAB allows you to read the data returned in this VIEW)