Copyright: Copyright (c) 1997-1998 OCERIS, Inc.


What is AutoSQL?

If you've ever developed a database application, there has probably been a time when you needed to use the SQL CREATE TABLE statement, Delphi code, or C++Builder code to create table structures programmatically. Unfortunately, there was no easy way to create statements and code for existing tables. Now with AutoSQL, you can take existing Paradox, dBASE, Access 95 and Access 97 (requires Jet Database Engine version 3.0 or 3.5 respectively) files and create complete SQL statements for tables, indexes, and data, and Delphi and C++Builder code to create tables using Table components. Select as many tables as you want, push a button and get the results.

Delphi, C++Builder, Paradox and dBASE are registered trademarks of Inprise Corporation.

Access and Jet Database Engine are registered trademarks of Microsoft Corporation.

AutoSQL Program and all documentation

Copyright (c) 1997-1999 OCERIS, Inc.


AutoSQL Features

Features:

•	NEW in 2.1! Create native C++Builder code to create tables using Table components, FieldDefs and IndexDefs.  Simply cut and paste into C++Builder!

•	NEW in 2.1! Create native Delphi code to create tables using Table components, FieldDefs and IndexDefs.  Simply cut and paste into Delphi!

•	NEW in 2.0! Create INSERT INTO statements for the data in the tables!

•	NEW in 2.0! Save your table lists to files for later use!

•	NEW in 2.0! Works with Access 97 tables! (requires Microsoft Jet Database Engine 3.5)

•	Does not require knowledge of CREATE TABLE and CREATE INDEX structures

•	Save tremendous amounts of time by automatically creating complete CREATE TABLE and CREATE INDEX statements that can cut and pasted into your code!

•	Works with all Paradox, dBASE tables  as well as Microsoft Access 95 database (requires Microsoft Jet Database Engine version 3.0).

•	Creates statements for tables with spaces in the table name, or in the field name

•	Optionally creates statements for primary indexes

•	Optionally creates statements for secondary indexes

•	Cut and Paste results from AutoSQL to your code, or save the results to a text file

•	The results are for Paradox, dBase or Access, but the table structure is there.  Change the resulting statements some and you can port your tables to Oracle, SQL Server, InterBase and other SQL databases.

InterBase, Paradox and dBASE are registered trademarks of Borland International.

Access and Jet Database Engine are registered trademarks of Microsoft Corporation.

Oracle is a registered trademark of Oracle Corp.

SQL Server is a registered trademark of Microsoft Corp.


AutoSQL Revisions

2.2 - 01/16/1999

-Fixed display problem when Windows was set to use large fonts.

-Changed the results memo field to a richtext field.  Memo fields in Windows 95 and 98 only store around 65k, so when processing many tables, some of the code might get cut off.  This has been fixed.

-Added the ability to create AppendRecord commands for Delphi.  This will transfer existing data from tables to code for appending after the table has been created.  C++Builder AppendRecord support should be in the next release.


Contacting OCERIS, Inc.

Check our web site for updates to AutoSQL or any of our products.  Our web site has secure online ordering, so If you'd like to order AutoSQL or any of our other products, you can use your credit card over a secure connection.

If you have any problems, bugs, questions, or suggestions about any of our products, please do not hesitate to contact us:

AutoSQL, full version: $49.95

Internet:

Web Site: http://www.oceris.com/

For Ordering Information, Pricing, etc: sales@oceris.com

Technical Support: support@oceris.com

Any General Questions or Suggestions: oceris@oceris.com

Fax:

24 hour fax: (256) 971-9831

Postal:

Mailing Address:

OCERIS, Inc.

PO Box 286

Madison, AL 35758

USA


Where to start

AutoSQL just needs to know the location of the database tables you want to process.  It doesn't matter which are Paradox, dBase, or Access tables.  They can be processed in the same batch and can be mixed and matched.  AutoSQL will automatically figure out what type of database table you are using.  Refer to the topics below for more detailed information on AutoSQL and its sections:

Select Files Tab

Results Tab

Paradox and dBASE are registered trademarks of Borland International.

Access is a registered trademark of Microsoft Corporation.


Select Files Tab

This is the place to start when using AutoSQL.  This screen simply shows a list of all table files (complete with path) to be processed.  You need only add the .db or .dbf files.  Index files and memo supplement files are read automatically when the appropriate table file is added.  Files may be added to this list in two ways.  First you may drag and drop files from Windows Explorer, or secondly, you may click the Add Files button.

Buttons

Add Files

Remove

Clear All

Execute

Paradox and dBASE are registered trademarks of Borland International.

Access is a registered trademark of Microsoft Corporation.


Results Tab

The Results Tab containes a text box that show the results of the SQL scripting process.  All table files that were able to be read will have an SQL CREATE TABLE statement, along with all index statements needed to create the indexes that the table had.

SQL Statements can be extracted from the text box using the Ctrl-C keystroke for copying or by pressing the buttons explained below.

Buttons

Save To File

Copy To Clip


Processing Options

The Processing Options form pops up after you click the Execute button on the main screen.  It allows you to select the type of output you are wanting to get, as well as options for that output.  For more information on the types of output and the options associated with them, see the topics below.

Creating SQL Statements

Creating Delphi Code

Creating C++Builder Code


Creating SQL Statements

Select the Create SQL tab to create SQL CREATE TABLE statements for the tables you have selected.  Here you are provided with a few options for changing how the results come out.  Manipulating these options can save you a large amount of time when transfering the results to your code.

Index Options

Process Primary Indexes

Process Secondary Indexes

Primary index uses CONSTRAINT keyword

Prefix and Suffix

Prefix

Suffix

Miscellaneous

Include Table Filename Extenstions (Paradox and dBase Only)

Enclose names with spaces in brackets instead of quotes

Create INSERT INTO statements for table data


Create Delphi Code

The Create Delphi Code tab will create Pascal code that is compatible with Delphi to create the tables you have selected.  After the code has been created, you can cut and paste the results directly into Delphi.  For more information on a certain option or field, see below.

Index Options

Process Primary Indexes

Process Secondary Indexes

Database Options

Database Name

Table Type

Miscellaneous

Database Name


Create C++Builder Code

The Create C++Builder Code tab will create C++ code that is compatible with C++Builder to create the tables you have selected.  After the code has been created, you can cut and paste the results directly into Delphi.  For more information on a certain option or field, see below.

Index Options

Process Primary Indexes

Process Secondary Indexes

Database Options

Database Name

Table Type


Select Tables

When adding an Access file, the Select Tables form will pop up to allow you to select which tables out of the Access file to process.  Use <shift> to select a range and <control> to select an individual table.  Use the Select All button to select all tables.


Switching Between Access 95 and Access 97 Compatibility

AutoSQL supports both Access 95 and Access 97 tables, provided you have both Microsoft DAOs installed on your system.  You can only uses one compatibility at a time, and must pick which one it will be.  To change between 95 and 97, select Tools -> Database Administrator from the AutoSQL Main Menu.  Refer to the screenshot above to locate the DLL32 setting for the MSACCESS drivers.  Choose IDDA3532.DLL from the list to use Access 97 tables, or select IDDA032.DLL to user Access 95 tables.  You must restart AutoSQL for these changes to take effect!


Error: <File> is not a valid Paradox or dBase file, or the table has a corrupt index.

The file specified by <File> is either not a valid Paradox or dBase file, it is a corrupt file, or the file is locked.  This error message is also used by AutoSQL if the file could not be found, or could not be opened.

Paradox and dBASE are registered trademarks of Borland International.

Access is a registered trademark of Microsoft Corporation.


Error: There is not a compatible Jet Database Engine installed on this system

AutoSQL supports both Access 95 and Access 97 tables.  You must be in the correct compatibility mode to access the version of table you want.  If you get this error, and you know you have the Microsoft DAO installed that can read your file, refer to Switching Between Access 95 and Access 97 Compatibility.

Access and Jet Database Engine are registered trademarks of Microsoft Corporation.


Add Files

Pops up a dialog box to select Paradox (*.db) or dBase (*.dbf) files.  You may select multiple files by holding down the control key when selecting files.


Remove

Removes the currently selected file from the list of files to process.  NOTE: The remove button is only enabled when a file is highlighted in the list.


Clear All

Removes all files from the list of files to process.  NOTE: The clear all button is only enabled when there are files in the list.


Execute

Starts creating the SQL scripts.  It will show a status of the percentage done.  If AutoSQL runs into any files that it is unable process it will show a message.  These files will not show at all in the results.  Once the process is complete it will show the Results Tab.  NOTE: The execute button is only enabled when there are files in the list.


Save To File

Pops up a dialog box to save the contents of the results box.  Type in a file name and the results are saved in plain ASCII text.


Copy To Clipboard

Copies the currently selected block from the results box to the clipboard.  NOTE: Copy to clip button is only enabled if text is selected.


Select All

Selects all of the results so they can be copied to the clipboard.


Primary Indexes

Check this option to process the primary indexes of the tables.


Secondary Indexes

Check this option to process the secondary indexes of the tables.


Primary index uses CONSTRAINT keyword

Some SQL parsers will allow the PRIMARY KEY keyword to be all that's needed to establish a primary key when creating a table.  Others require that you put a CONSTAINT keyword in there, followed by a unique index name.  AutoSQL automatically generates the primary index name, so as to assure a unique one.  Here are the results:

With CONSTRAINT keyword:

CONSTRAINT TableNamePK PRIMARY KEY (FieldName)

Without CONSTRAINT keyword:

PRIMARY KEY (FieldName)


Prefixes and Suffixes

When using AutoSQL to create SQL statements that will be copied into your source code, there may be times when you'd need something entered in front of every line.  For example, in Delphi, you add the SQL lines to the SQL property in a TTable or TQuery.  This is a TStringList object.  You would probably want to enter SomeTable.SQL.Add(' to the prefix and ');' to the suffix.  Here are the results:

Without Prefixes and Suffixes:

CREATE TABLE Test

(

TestID INTEGER

);

But, add Prefixes and Suffixes:

SomeTable.SQL.Add('CREATE TABLE Test');

SomeTable.SQL.Add('(');

SomeTable.SQL.Add('TestID INTEGER');

SomeTable.SQL.Add(');');

This makes cutting and pasting the SQL statements into your source code much more of a one step process.


Include Extensions

This is only for dBase and Paradox tables.  Access tables are unaffected by this option.  When passing a SQL CREATE TABLE statement to the Borland Database Engine, it looks at the file extension to determine which type of table to create (db for Paradox, dbf for dBase).  If no extension if specified, the BDE uses Paradox by default.  If you are processing dBase or both dBase and Paradox, you'll need check this option.  Otherwise, if only processing Paradox tables, the results are more readable and more standard if you leave this option unchecked.


Enclose names with spaces in brackets instead of quotes

Used when a the database engine you are using requires names with spaces to be surrounded by brackets rather than quotes.  The Microsoft Jet Database Engine takes brackets, while the Borland Database Engine takes quotes.  This setting will affect table and field names.  Here are the results:

Using Brackets:

CREATE TABLE [Name with spaces]

Using Quotes:

CREATE TABLE "Name with spaces"


Create INSERT INTO statements for table data

Check this checkbox to create an INSERT INTO statement for each record in the tables that you are going to process.  This basically makes a carbon copy of the table that you can paste into you code.  The results will have the CREATE TABLE statement, the CREATE INDEX statements if there are any, and finally the INSERT INTO statements if there are records in the table and this checkbox is checked.


Ok

Accepts current settings and continues.


Cancel

Does not accept current settings.


Database Name

Whatever is entered here will be used to set the DatabaseName of the table components used in creating your tables.  DatabaseName is set to EXACTLY what is in the box.  Therefore, if you use a variable for your DatabaseNames such as strDBMain, just enter strDBMain.   However, if you use string literals, enclose the name with single quotes for Delphi, or double quotes for C++Builder.  For example, if you database alias is DBMain, enter 'DBMain' for Delphi and "DBMain" for C++Builder.


Table Type

Select the table type to be created.  This will be directly assigned to the Table components TableType property.  Possible values are Paradox, DBase, and ASCII.  If you are developing for a client/server environment or using ODBC, the table type is ignored by Delphi and C++Builder, so it doesn't matter what it is set to.


Create AppendRecord statements for table data

Check this checkbox to create an AppendRecord statement for each record in the tables that you are going to process.  This basically makes a carbon copy of the table that you can paste into you code.  The results will have the table structure built in code, and finally the AppendRecord statements if there are records in the table and this checkbox is checked.