Archive for the ‘DataFlex’ Category

CL2SQL: Command Line Tool for Unattended Migration

Wednesday, January 20th, 2010

Mertech provides a command line tool to allow migration of data using Flex2SQL features without the GUI interface. The tool, called cl2SQL, is located in the Mertech\bin directory and supports all the options of Flex2SQL through command line parameters.

A good use of this tool would be to convert an existing DataFlex databases to SQL without any attendants. Cl2SQL can be used along with the CONVER_DAT (see http://blog.flex2sql.com/index.php/2009/02/write-your-own-migration-routines-using-the-convert_dat_file-macro/) command to develop unattended migration process.

To see all the options supported by CL2SQL, click on cl2SQL to run it and an information window will be displayed with all the options. These options are listed below:

 -?,-h,-help           : This information screen.

 -d <drv>,             : Use driver <drv> where drv is one of our supported drivers.
 -driver <drv>           Such as ORA_DRV, MDS_DB2, SQL_DRV, MDSMYSQL and MDSPGSQL.

 -f <fs>,              : The fileset passed as a parameter in <fs> on which you
 -fileset <fs>           want to operate.

 -file <n>             : The filenumber passed as a parameter in <n> on which you
                         want to operate. You can only use this option with a fileset
                         This allows you to override the set of files on which you
                         want to operate with another file.

 -prefix <ABC>,        : The prefix <ABC> and postfix <ABC> options are  to be used
 -postfix <ABC>,         for the tablename prefix or postfix as an override on what is
                         defined in the fileset.
                         You can only use this option along with a fileset.

 -s <server>,          : Database <server> to connect to, where <server> can be either
 -server <server>        the server name or IP used.

 -database <db>        : If you want to use another database as is specified in the
                         configuration file, then you can override the setting with
                         the value in <db>.

 -u <user>,            : User id to login with.
 -user <user>            You should not set this when you want to login using
                         a trusted connection.

 -p <pass>,            : Password to use for logging in.
 -password <pass>        

 -port <n>             : Use an alternative port to connect to on the database server.

 -c <cfg>,-config <cfg>: Use the provided configuration in <cfg> file

 -l,-login             : Automatically log in using the last used/saved parameters.
                         This is the default behaviour if no login details are passed.

 -r <rpt>,-report <rpt>: Write a migration report to file <rpt>.

 -rc,-rptclear         : Erase migration report file <rpt> (from the -report option).
                         before the actual report is being written hereby opening the
                         ability to not append to an existing report and start fresh.

Tags:
Posted in DataFlex, Development Tools, Flex2SQL, Tools | No Comments »

Multiple embedded SQL statements

Tuesday, June 30th, 2009

While complex embedded SQL statements will let you do a lot of work in one statement, it is sometimes necessary to nest SQL statements to get the job done.

Using cursor handling lets you easily nest multiple embedded SQL statements.

Consider the scenario where we want to iterate through all orders with a specific status, and for each order we want to calculate the total of that order.

Of course, in this case it would be pretty easy to write one SQL statement that returns everything, but for the example we will use two nesting calls:


// Example
Use Mertech.inc
Integer iCursor1 iCursor2
Integer iCustomer
String sName
Number nOrder nTotal

login 'Server' 'User' 'Passwd' 'Driver'

set_database_name to 'DataBase'
sql_use_database 'DataBase'

Sql_Open_Cursor_Stmt to iCursor1
Sql_Set_Stmt to "select customer_number,name from customer order where status = 'Y' by customer_number"
Sql_Prepare_Stmt
Sql_Execute_Stmt

Repeat
// Back to the main cursor
Sql_Set_Current_Cursor_Stmt to iCursor1
Sql_Fetch_Next_Row into iCustomer sName
If (Found) Begin
// Open a new cursor to the new stmt
Sql_Open_Cursor_Stmt to iCursor2

Sql_Set_Stmt To ("Select sum(order_total) from Orderhea where customer_number = " + trim(iCustomer))
Sql_Prepare_Stmt
Sql_Execute_Stmt
Sql_Fetch_Next_Row into nOrder
If (Not(Found)) Move 0 to nTotal

Sql_Close_Cursor_Stmt iCursor2

// nTotal has the Order total for this current customer
// Your code here
Indicate Found True
End
Until (Not(Found))
// End Example

The keys in the example are the calls to Sql_Open_Cursor_Stmt and Sql_Set_Current_Cursor_Stmt which allows you to switch between two open recordsets.

Tags:
Posted in Code Examples, DB2, DBMS, DataFlex, Microsoft SQL Server, MySQL, Oracle, PostgreSQL | No Comments »

Accessing existing non-DF tables, synonyms or views in DataFlex

Thursday, February 26th, 2009

There are two ways to do this: (1) by using INTERMEDIATE FILES and (2) by changing the root name of the Filelist entry to Universal Naming Convention (UNC) syntax.

 Using Intermediate Files (recommended)

Flex2SQL provides an easy way to generate .INT, .FD and .TAG files from an existing table or view:

 Using Flex2SQL, login to the database backend (if you are not already logged on).

Select Generate | .INT File from Table/View/Synonym from the menu. This will bring up a list of all the tables/view/synonyms that exist on the server (Figure: 10.1).

Select the tables you want to generate the intermediate file for and press OK. The .INT and .TD files will be generated and added to your Filelist with the driver prefix (SQL_DRV: or ORA_DRV:).

In order to generate the .FD and .TAG file, check the appropriate options in the “.INT File From Table Grouping” dialog to enable the file generations in Filelist | Options.

Without Using Intermediate Files
To access an existing table without generating an intermediate file, a new entry must be added in the Filelist with the root name obeying the UNC convention.

 ora_drv:\\servername\schemaname*tablename

 Where SCHEMA Name is same as the creator of the table.

Now an open TABLE_NAME command will use the table on the Oracle server.

Following our login example, to open a table called EMP created by user SCOTT (also called schema name) on an Oracle server, we will have the following entry:

 ora_drv:\\servername\scott*emp

 Where “*” (asterisk or star) is used to distinguish between schema name and table name.

Since the calls to retrieve index information from the database system are resource intensive, you might notice a slower opening of files compared to using .INT file method.

In the case of MS SQL Server, you also need to specify the database name sql_drv:\\server name\databasename*schemaname*tablename.

 Steps to Access Existing Tables Using VDF

  • Open Flex2SQL and select Generate | .INT File from Table/View/Synonym.
  • Select a table or view, and press OK. In the “Convert DAT to Table” dialog, change the available options as needed.
  • When prompted, select a Filelist slot for the file.
  • Open DBBuilder and select the file. Make sure it has the driver prefix before it.
  • The Mertech “Login” dialog pops-up. Enter the login information. After login, DBBuilder shows the file structure information.
  • Generate the Data Dictionary files (.DD files) for the table.
  • Open VDF IDE and generate a view with the view wizard using the .DD generated in the previous step.

 You are ready to compile and run your DataFlex program.

Tags: ,
Posted in DataFlex, Development Tools, Flex2SQL | No Comments »