Data Manager for Btrieve and Pervasive SQL

Configurable data management for export, reporting, merge, rebuild,and recover.

Enterprise Edition Data Management Features

That product is the Data Manager for Btrieve and Pervasive SQL Enterprise. Our goal was to create a tool that allowed someone to drive data from any version (new or old) of any Btrieve or Pervasive SQL data file into any other database or text file on the planet. It does notot require any ODBC or other middleware product to read the data and do it at lightning speed. And to provide configurable export that can be saved to run over and over again to export data for reporting, integration or database conversions. So even if you are doing a one time export you can test it out, save it and reliably run the same export over and over again.

A database database export can be set up in just a couple of minutes. You can do everything from one single application. Create a DDF, Browse and/or edit your DDF and data, set up filters, export or create an export configuration. The Data Manager does not use ODBC to read the Btrieve database files. It will export data to Microsoft Access, SQL Server, Any ODBC DSN, OLE DB Providers and any fixed or delimited text files. And because it uses native access for reading the Btrieve data files it can work with any version of Btrieve or Pervasive data file new or old. And it can read that data faster than anything else available anywhere. All of the complexities of the Btrieve side are taken care of. You just have to decide where you want your data to go.

The Data Manager for Btrieve and Pervasive SQL Enterprise combines everything from the Standard edition with the added ability to configure and save exports to any database or file format.

High Performance exports and a special Super Fast Load into SQL Server

The Configurable Data Management Tool is so Easy to Use

1) Directly edit the configuration grid. For special columns the grid has built in pick lists and pop up forms for configuring individual cells.

 

2) Select Options from the view menu. With this you can perform mass changes to every cell in selected columns. This allows you to make mass changes without having to edit every column/row in the configuration grid. Very useful and time saving if you have lots of tables to export.

 

3) Under the tools menu select the Configuration Wizard. This wizard sets up a configuration for an individual table. Select the table row in the configuration grid and then tools/configuration wizard from the menu. The first form has common table configuration attributes and a setting for the target. The setting for the target (database, text file...) determines the next configuration forms to complete the configuration with the wizard. At most there are three forms required for a database configuration.

Examples

Example 1: How to use the Accellerated and SQL Server Fast Load performace options.

Example 2: Export an entire database to tab delimited text file format.

Example 3: Export an entire database to a Microsoft Access database using global change option form.

Example 4: Export to an ODBC DSN using the Configuration Wizard.

Example 5: Export to a SQL Server database using the Configuration Wizard.

Example 6: How to use the new Accellerated and Fast Load Options for SQL Server.

Example 7: Export to a Oracle database using OLEDB and the Configuration Wizard.

Example 8: Using the Data Manager Enterprise for integrating Btrieve data into a reporting system.

For more information on the Data Manager download the context sensitive HTML help.

Example 1: How to Automate and run your exports for database integration, conversion and reporting.

Export configurations can be setup quickly and easily. With included configuration wizards and tool you can setup an export in minutes. For databases you can test out logins and connections, create tables and even databases. Only a few steps are required for any database or file format. And it includes a global change feature so that you hardly have to do any editing. Even for exports of entire databases with very large numbers of tables.

Example 2: Export an entire database to tab delimited text file format

It only takes slightly more effort to export to a Microsoft Access database than exporting to a set of tab delimited files. For tab delimited files you basically have to do nothing more than pick the tables you want to export. By default the program is already setup to do a tab delimited export. Other text formats include, CSV, comma delimited, fixed formats for FTP and mainframe, and custom delimited format. Basically, any text format to fit your needs.

In the first example the database to be exported to tab delimited format is a Great Plains accounting database. In the second example the database to be exported to Microsoft Access is a Maximizer database. The Great Plains and Maximizer databases were chosen only demonstrate some of the features of this product. This product will work with any and all Pervasive SQL or Btrieve databases.

The Data Manager for Btrieve and Pervasive SQL Enterprise is really two programs in one. The main part of the program has all of the same functions of the Standard edition. The Enterprise edition adds a separate export configuration and execution tool. It also provides other programming interfaces and components to execute export configuration files from within other programming tools report report writers, etc.

2) From the Data Management screen you have to start with a data dictionary (DDF). After you have loaded a DDF you can save your work into a command description file (.cds). And later on you can reload this file and continue where you left off or execute it. The following screen shot shows a DDF (or previously saved .cds file) loaded into the Data Management screen.

By default it is already set up for tab delimited export. You do not need to do anything else. From here you can run it by selecting the Operation tab and pressing the execute button, or save this configuration into a file and run it from the command line or via the included ActiveX control. It is really that easy. You can install the software and set up the export in just a matter of minutes. The product also runs faster than anything else available because it makes use of the fastest available native Btrieve API calls. It does not use ODBC or any other middleware software for reading Pervasive SQL or Btrieve files. So you also do not have to spend any time installing, configuring or tinkering with anything else. And there is nothing else to buy or license. It runs right out of the box.

Example 3: Export an entire database to a Microsoft Access database using global change option form

3) Configuration. There are 35 configurable columns in the grid. There are a lot of columns because the product can handle most anything for any particular database or file format. However, most can be left at default settings or are only applicable for a particular database format. So you usually have to change or set at most just a handfull of columns. The grid automatically configures itself on a row by row basis for easy data entry. And there is a configuration wizard for configuring any particular row. So you can just walk through at most 3 forms for configuring an export for any particular table. The main ones are:

• The tables that are to be included in the configuration. This is determined by the Included column.
   
• The associated data file for each table.
   
• For each table what fields you want to use. For every field use "*" (default).
   
• A Filter to determine what records you want to include or exclude. If you want all of the records do not set a filter (default).
   
• Where is the data to go; text file, sequential file, another Btrieve file, DDF table, or a different database like Microsoft Access, SQL Server, ODBC DSN, ....
   

In this case we are going to export an entire database to a Microsoft Access database. To export to a Microsoft Access database only 3 columns need to be changed for every table:

 
• The Target column needs to be changed to Microsoft Access.
   
• The Output Location column needs to be changed to point to the same .mdb file. We want all of the tables to be exported into the same Access database .mdb file. Each Pervasive or Btrieve table will go into its own Access table. But all of the Access tables will be in the same .mdb file.
   
• The Operation column must be changed to Append for every table except for the first. When the first table is processed it will create the .mdb file overwriting any existing one. All of the following tables will be added to the same .mdb file.
   

This DDF has over hundreds of files. To make that change manually would be very time consuming. To make this kind of change simple we added in a global option change. From the View pull down menu select Options. On this form you simply set the options you want and check the box next to it to indicate that this is to be applied to every table. As you can see in the following screen shot the Target, Operation and output Location fields are to be set for every table.

Example 4: Export to an ODBC DSN using the Configuration Wizard

The ODBC Connection Attributes (next) form collects connection information. You can either enter a standard ODBC connection string or set individual settings that form the connection string. In this case we just need to pick the Foxpro DSN from the list. Then select Next.

The Database Table Attributes (next) form collects table information. Most of the time you only need to set the Table Operation column. This is because CREATE TABLE, CTEATE INDEX and DELETE statements are internally generated whenever they are needed for many databases. So unless you need to change field names and/or modify the target tables structure you just need to set the Table Operation value and select Finish.

For easy testing and ease of use this form supports creating and dropping tables. For supported databases you can also use this form to generate the internally generated CREATE TABLE and CREATE INDEX statements. These can be used as a template for creating tables with different table structures from the default translation. Only if the field names are different between the source Btrieve table and the destination database table is Field Mapping necessary.

• Table Name. The table name in the database that is the target for the export.
   
• Field Mapping. If the field names between the two tables are identical then you do not need to set this. In this case the field names are different. There is a special form for mapping fields between tables.
   
• Table Operation. The operation you want to perform on the export. Overwrite, Append, Create if not exist and Delete. In this case we will choose Delete. If the table does not exist it will be created. Otherwise empty the file prior to adding new records.
   
• Create Statement. Optional for many databases. Used when a Table Operation is specified that results in a database table being created. If you do not provide a create statement one will be internally generated for selected databases. CREATE TABLE statements will automatically be generated for Access, Sql Server, Oracle, DB2, Foxpro, Excel, DBase, and Text. Indexes are also translated and CREATE INDEX statements are generated. You can provide a Create Statement of your own to create the table if it does not exist. You can also view and/or modify the internally generated CREATE TABLE and CREATE INDEX statements. We are exporting to a Foxpro database and the table already exists so there is no need for a Create statement in this case.
   
• Delete Statement. Optional. If you select Delete as the opeation you can provide a delete statement. If you do not provide one then one is internally generated to delete all of the rows. In this case we will let all rows be deleted.

Example 5: Export to a SQL Server database using the Configuration Wizard.

From the Tools menu select Configuration Wizard. On the first screen select SQL Server as your Target. And then select Next.

The next screen is the SQL Server Connection Attributes form. For SQL Server all you need to provide the Database Name, Server Name and the user name and password for a logon in order to connect. The Operation column is used to determine whether to create or append to an existing database.

• Operation. Operation to perform for the database.
  • Overwrite. The Database as specified by the Database Name is always recreated. Any existing database of the same name is ovewritten.
  • Append. Connection is made to an existing database as specified by the Database Name.
  • Create is Not Exists. If the database as specified by Database Name does not exist it is created. Otherwise it is appended to.
• Sql Server Master Database. The master database name to use as a template for creating a new database.
• Database Name. SQL Server Database Name.
• Server Name. Name of server where SQL Server database resides.
• User Name. User Name for SQL Server logon.
• Password. Password for SQL Server logon.
• Connection String. If the Connection Type specifies a connect string then a connection string is provided in this field.

The next form is the same one used for Access, ODBC, OLEDB and any other using a database connection. The Database Table Attributes form is used to collect table connection information. Because SQL Server is a supported database the only thing we will set is the Table Operation. In this case to Delete. If the Table does not exist it will be created. Otherwise it will be emptied of any existing records. For more information on this form see Example 4 on configuring an ODBC connection.

Example 6: How to use the new Accellerated and Fast Load Options for SQL Server.

To use the Accellerated or SQL Serrver Fast Load options all you need to do is make one change on the Performance Options for the Configuration Management form. 1) Bring up the Performance Options form by selecting Performance Ooptions from the Configuration Managements form's View menu. 2) Use the Database Export Speed conbo box to select Accellerated or SQL Server Fast Load. 3) Select OK and you are done. When you save the export configuration this setting will be saved as well. Now you exports will run at an accellerated speed. And in the case of SQL Server 10-20X faster.

Example 7: Export to a Oracle database using OLEDB and the Configuration Wizard.

From the Tools menu select Configuration Wizard. On the first screen select OLEDB as your Target. And then select Next.

The next screen is the OLE DB Connection Attributes form. Microsoft OLE DB Provider For Oracle has the following connection string format:

If you provide a full connection string then the above format is what is required for the Oracle driver. However, the provider name keyword and value is optional as it can be selected from the combo box containing the available drivers on your system. In this case we are going to use the individual settings and the provided fields for Data Source, User Name and Password.

The next form is the same one used for Access, ODBC, SQL Server and any other using a database connection. The Database Table Attributes form is used to collect table connection information. Because SQL Server is a supported database the only thing we will set is the Table Operation. In this case to Delete. If the Table does not exist it will be created. Otherwise it will be emptied of any existing records. For more information on this form see Example 4 on configuring an ODBC connection.

Command Line Interface

mngrexec.exe maxim.cde

There are two optional parameters you can specify on the command line.

 

1) /DDF: - This will override the DDF specified by the configuration description file (.cds). This allows you to dynamically specify the path to a DDF at the time the configuration file is executed.

 

2) /OUT: - This will override the output path specified by the configuration description file (.cds). This allows you to dynamically specify the path to output the data at the time the configuration file is executed. If you just specify the file name for the Output Location column then it will be combined with this value to determine the Output Location path and file name when the .cds file is executed.

 

With the command line you can integrate the Data Manager into many diferent kinds of applications. For example you could integrate it into a scheduling application so that it periodically creates a set of Microsoft Access or SQL Server tables for reporting purposes. This is described in the sections that follow.

ActiveX Interface

Example 8: Using the Data Manager Enterprise for integrating Btrieve data into a reporting system.

Why not try a far easier solution. You can easily configure an export of the data from Btrieve or Pervasive files into a Microsoft Access or SQL Server database. Often, this can often be done in a matter of minutes. Then easily set up your reports on the Access or SQL Server tables via Crystal Reports or some other tool that works with Access or SQL Server tables. You will spend almost no time on the Btrieve access. The only work you will have to do is creating the report. Which is the real reason you are doing any work anyways. With the command line and ActiveX interfaces the Data Manager can execute the export configuration at any time. And with the ActiveX and command line interfaces you can easily integrate it into Crystal Reports, a Visual Basic or Access program, .NET, anything that can use ActiveX or call a windows exe file. The end users do not have to do anything. The Data Manager will do all of the Btrieve work invisibly executing the export configuration file (.cde or .cds).

The Data Manager is very fast. And you can filter the records and fields that you want to extract. And you can even create special indexes on the target tables not available on the source Btrieve files. This is analogous or in many cases identical to creating temporary tables within a database system. The only diference is that the target database format is very easy to do reporting off of.

The Data manager does not use ODBC or any other middleware product to extract the Pervasive SQL or Btrieve Data. It works with ANY and ALL versions of Pervasive SQL and Btrieve. It runs right out of the box. You completely eliminate all of the time consuming and costly development and support problems. And there are a few things we have added to directly support reporting requirements.

• Filtering - You can select only the records you want from the Pervasive or Btrieve files to go into your Access tables. The Filter column is like the WHERE clause of a select statement without the WHERE keyword. And for Dates, Times and Timestamp fields we support some special keywords for reporting. These keywords evaluate to an actual date, time or timestamp when the filter is executed. And you can also do addition and subtraction to these dates. For example if the current date is Oct 11, 2002. then the filter "PostDate >= CURDATE - 10" will evaluate to "Postdate >= 10/01/2002". For time you just add or subtract a fractional amount of 24 hours. So "PostDate >= CURDATE - 10.5" would evaluate to "PostDate >= 9/29/02 12:00:00 PM"
 
• a) Date and Timestamp fields

  i) CURDATE - The current date when the filter is executed.

  ii) BEGININGOFYEAR - The date of the first day of the current year.

  iii) BEGININGOFMONTH - The date of the first day of the current month.

  iv) BEGININGOFWEEK - The date of the first day of the current week.

   
  b) Timestamp fields

  i) CURDATETIME - The current date and time when the filter is executed.

   
  c) Time fields

  i) CURTIME - The current time when the filter is executed.

   
• Fields - You can select only the fields you want to export. You can export only the information you need and want.

You can export only the data you want, so the reporting is done only on the data you need. And with the date, time and timestamp keywords you can configure a reporting system that is always current with the data you want when the reporting is performed.

Get some SPEED!

Rebuild, Merge, Split, Recover and More

And like the operations for export and reporting these configurations can be saved into a .cds file and executed from the command line. So you can do sweeps of data from one Btrieve file or table into another.

We Can Do It For You

Data management grid columns and descriptions.

• Table - This is the table name in the DDF.
 
• File Location - This is the file name to be associated with the Table.
 
• Included - Specifies whether this table is to be included in the management operations.
 
• Key - This specifies the Btrieve file index to use when required for an operation. For example records will be exported along that key path.
 
• Fields - What fields from the table are going to be used for the operation. In the case of export this will be the fields exported. The value "*" means all fields.
 
• Filter - Like the where clause of a SQL statement without the where. Specifies which records are to be included or excluded.
 
• Use Optimal Key - When you set a filter it may be able to make use of the selected key to optimize searching. But this is not always so. And you need to specify a Key value that the Filter can optimize on. This can make a huge difference in performance. The Data Manager can pick a key to optimize on if you do not wish to select one. Setting Use Optimal Key to true tells the Data Manager to select the Key to use. The Use Optimal Key and Key columns are mutually exclusive. If you set Use Optimal Key to true then the Key number column will be ignored no matter what value you set it to.
 
• Case Sensitive - When you set a filter this determines if operations on string fields are done with or without case sensitivity.
 
• Filter Distinct - This determines if duplicate keys are filtered out when processing records.
 
• Oem/Ansi conversion - Determines if Oem to Ansi conversion is perfomed on data fields.
 
• Traversal - The direction in which to read records. Ascending order or descending. This is along the key or physical position of records in the file as specified by the Key column.
 
• Record Errors - What to do in case of an error whether reading or writing a record. Ignore the error or abort the operation entirely.
 
• Field Errors - What to do in case of an error whether reading or writing a field. Ignore the error, skip to the next record or abort the operation entirely.
 
• Target - What is the target of the operation. This is where the data is going to go. text file, Btrieve file, sequential file, Access, SQL Server, ODBC, OLE DB, etc.
 
• Text - If the Target is a text file then this specifies whether the output file uses unicode or ascii.
 
• Text Format - If the Target is a text file then this specifies what is the format of the data. You have a choice of a number of predefined delimited files (tab, csv, comma, etc.), fixed format, or custom delimited files. If you specify custom delimited files then the Custom Prefix, Custom Suffix, and Custom Separator columns specify the format of the file. Fixed format files are commonly used for mainframe and FTP.
 
• Headers - If the Target is a text file then this specifies whether the field names are written out on the first line of the output file.
 
• Sequential - If the Target is a sequential file this specifies whether the file is to be opened in a recover mode.
 
• Operation - Overwrite, Append, Create if not exist. This specifies whether the target as specified by the Target and Output Location columns will be overwritten or appended to. Create if not exists indicates that the database will be created if it does not exist but appended to afterwards.
 
• Output Location - Path and file name of target.
 
• Table Operation - Overwrite, Append, Create if not exist, and Delete. This specifies whether the Operation to be done on the table as specified by Target and Output Table Name. Overwrite specifies that the table wil be created every time the export is performed. Append indicates that exported rows will be appended to an existing table. Create if not exist indicates that the table will be created if it does not exist but appended to afterwards. Delete indicates that the table will be created if it does not exist but all rows of data will be deleted before inserting new ones.
 
• Table Types - If the Target is a database then this specifies whether the data in the tables will be mapped to a corresponding database type or all defined as strings.
 
• Output Table Name - If the Target is a database then this specifies the name of the database table. By default it is defined as the name of the table in the DDF.
 
• User Name - If the Target is a database then this specifies the user name to be used when accessing the database
 
• Password - If the Target is a database then this specifies the password to be used when accessing the database
 
• Generate Primary Key - If the Target is a database then this specifies whether you want the program to generate a primary key for you. If this is not specified then the program will atempt to generate a primary key from one of the existing indexes on the Btrieve file. Because of translation issues this may not work for all of the data in a particular file. If you have the program generate a primary key it will be based on the record number.
 
• Create Statement - If you are connecting to a database and the Output Table Operation column specifies overwriting the table then this is the create statement used to create a new table. Optional for many databases. If you do not provide a create statement one will be internally generated for selected databases. Create statements will automatically be generated for Access, Sql Server, Oracle, DB2, Foxpro, Excel, DBase, and Text. Indexes are also translated and CREATE INDEX statements are generated. You can provide a Create Statement of your own to create the table if it does not exist. You can also view and/or modify the internally generated CREATE TABLE and CREATE INDEX statements.
 
• Delete Statement - If you are connecting to a database and the Output Table Operation column specifies delete the table data then this is the delete statement used to delete the data in the table. This is optional. If you do not spec ify a delete statement then one will be generated and all data will be deleted from the specified table. This allows for selective deletion of records before new ones are added.
 
• DSN Type - If the target is ODBC then this determines what type of DSN connection.
 
• DSN - If the target is ODBC then this determines the DSN to connect to. The DSN Type must either be DSN or File DSN.
 
• Driver - If the target is ODBC and the DSN Type is DSN less connection then this is the driver to use.
 
• Server - Name of the database server to connect to.
 
• Field Mapping - If the target is another database then this provides the field mapping between the two database tables. By default the mapping is to use the same field name for both tables. So if this is the case then you do not need to do anything. However, if you need to map fields with different names in each table then you need to specify a field mapping. The general form is:
 
• Btrieve Table Field Name = Target Table Field Name;
 
• The semicolon is a separator and must be specified even if you only have one field. If you can establish a connection to the target database then there is a popup form for field mapping in the configuration grid. Just select the Field Mapping column in the grid and select the button to bring up the popup configuration form for mapping fields. This is an easy to use graphical interface for mapping the fields. Alternatively, select the Btrieve table row in the grid and select the configuration wizard from the tools menu. Field mapping is available from the Table configuration form.
 
• Connection String - If you are connecting to any database then this is a connection string to use. Connections to databases are managed via individual settings or the connection string. If the connection string has any value at all t hen it will be used for connection information instead of individual settings.
 
• Batch Insert Size - If the target is another database then this determines how the data that is exported is inserted into the target database. If the value is set to zero then records are inserted into the target database as they are exported. If it is a value greater than zero then that number of records are batched up and then inserted as one operation. If it is set to -1 then all of the records are batched up. Very useful in a client server environment where the records can be batched up at the client and transmitted to the server in large chunks. The Client can be one server with Btrieve or Pervasive SQL running on it and the Server can be a different server with the target database running on it.

The Data manager for Btrieve and Pervasive SQL comes with everything you need.

We can help you get the job done.

Our products support all Btrieve and Pervasive SQL based applications and tools

All specifications subject to change without notice.
Data Manager for Btrieve and Pervasive SQL, Developers Toolkit for Btrieve and Pervasive SQL, 32 Bit Power Pack for Btrieve, Classic Data Control for Btrieve, Btrv++, Btrvgen++, DDF Maker, and VBtrv Toolbox Controls for Btrieve are trademarks of Classic Software, Inc.
Btrieve and Pervasive SQL are a registered trademark of Pervasive Software.
Other products and companies referred to herein are trademarks or registered trademarks of their respective companies or mark holders.