Copyright: Paradox for Windows SQL Tools Help, v.5.xx
Citation: Copyright ® 1985, 1994 Borland International.


SQL Tools Help

Paradox for Windows 5.0 includes a set of tools for creating, indexing, and populating SQL tables interactively.  The SQL Tools enable you to move data between the following types of tables:

dBASE -- dBASE III+, dBASE IV

Paradox -- Paradox 3.5, Paradox for Windows 4.5 or greater

SQL (requires Borland SQL Links) -- Informix 4.1 or greater, InterBase 3.3, Oracle 6.0 or greater, Sybase/Microsoft SQL Server 4.10 or greater


SQL Tools Contents

Adding SQL tables

Copying SQL tables

Creating SQL tables

Displaying the structure of an SQL table

Deleting SQL tables

Locating aliases

Managing SQL table indexes

Using the SQL Tools SpeedBar


SQL Tools SpeedBar
The SQL Tools SpeedBar

Opening the SQL Tools SpeedBar

To open the SQL Tools SpeedBar, choose Tools | SQL Tools.

The first time you open the SQL Tools SpeedBar it checks your system for SQL driver information.  If necessary (for example, if you have installed a new SQL driver), SQL Tools regenerates the files it needs.

Closing the SQL Tools SpeedBar

To close the SQL Tools SpeedBar, double-click its control button.


Aliases Tool

The Aliases Tool opens the Paradox Alias Manager dialog box. You can use this dialog box to create, delete, or modify aliases, or to connect to or disconnect from an SQL server.


Creating a New SQL Table

To create a new table in your SQL database:

1.	Complete the SQL Create Table dialog box to define the structure of the table you are creating.

2.	If you want to see the new table automatically after it is created, check View New Table.

2.	Choose Save to create the table.

If you checked Borrow Indexes, Paradox displays:

Indexes borrowed but not created.
Do you want to create them?

Click Yes to continue; No to create the new table without indexes.

If there are any problems creating the table, Paradox displays an error message.  Where possible, this includes the SQL server's native error code and message.


Create Table Tool

See also

The Create Table Tool opens the SQL Create Table dialog box.  This dialog box enables you to create a new table in your SQL database.

Table Type

Use the Table Type drop-down list to specify the type of table you want to create.

Note:  If you borrow the structure of an existing table, but change the Table Type, the structure of the table changes to reflect the new Table Type. For example, if you borrow a Paradox table structure to create an Oracle table, the Paradox field type ALPHA changes automatically to the Oracle field type CHAR.

Alias Name

Use the Alias Name drop-down list to specify the location for the new SQL table.

Table Name

Enter a name for the new table.

Borrow Structure/Borrow Indexes

If you have an existing table with the structure you want to use, you can borrow its structure.  This eliminates the need to enter structure information in the Field Roster.

Choose Borrow Structure to open the File Browser. From the Browser, you can choose the table whose structure you want to borrow.

If you want to borrow the existing table's indexes, check Borrow indexes.

Clear Structure

Choose Clear Structure to clear the structure shown in the Field Roster and begin again, using the same Table Type, Alias Name, and Table Name.

Field Roster

To specify the structure of the new table, complete information in the Field Roster.  Each row describes one field in the table.

For each field, define the Field Name and Type.  (For a menu of available field types, press the spacebar or right-click in the Type column.)  Depending on your table type, you may also need to enter information in the following columns:

Dec	Specifies the number of decimal places to store.

Key	Creates a primary key on the field.  To specify, double-click or press any printable character to display key field indicator.(Paradox only)

No Nulls	Specifies whether the field is non-nullable.  Enter "Y" to make the field non-nullable; "N" to make the field nullable.  (non-Paradox only)


See Also

Creating a new SQL table


Dropping Tables
Dropping a Table

To delete a table from your SQL database:

1.	Complete the SQL Drop Table dialog box, specifying the table to drop.

2.	Choose Drop Table to delete the table. Paradox prompts you to confirm the deletion.

If there are any problems deleting the table, Paradox displays an error message.  Where possible, this includes the SQL server's native error code and message.


See Also

Dropping a table


Drop Table Tool

See also

The Drop Table tool opens the SQL Drop Table dialog box.  This dialog box enables you to delete a table from your SQL server.

Table Name

If you already know the name of the table you want, type it in the Table Name text box.  If the table is not in your working directory use an alias; for example, :ORA_DB:CUSTOMER.

If you already know the name of the table you want, type it in the Table Name text box.  If the table is not in your working directory use an alias; for example, :ORA_DB:CUSTOMER.

If you need to browse to find the name of the table, choose Browse. This opens the Paradox File Browser.

Table Type

Once a table is chosen, its type appears in the Table Type field.

Show Structure

To see the structure of the selected table, choose Show Structure. If the table cannot be deleted, Paradox displays a warning.

Drop Table

To drop the specified table, choose Drop Table.


Add/Copy Table Tool
Adding or Copying a Table

To copy records from one table to another:

1.	Complete the SQL Add/Copy Table dialog box to specify the table to add or copy.

2.	If you want to see the destination table automatically after it is modified or created, check View Modified Table.

3.	If you do not want SQL Tools to copy the table indexes, check Don't copy indexes.

4.	Choose Add to add the records to the destination table.

If there are any problems creating the table, Paradox displays an error message.  Where possible, this includes the SQL server's native error code and message.

Note:  If no indexes are copied during table creation, records are automatically appended to the destination.  This occurs even if you originally specified the Append & Update option.

Field Type Limitations

The destination server may impose some limitations on permissable field types for the destination table.  For example, since Oracle does not allow two LONG fields in the same table, you cannot add a Paradox table with two memo fields to an Oracle SQL table.


Add Copy Table Tool
SQL Add/Copy Table

See also

The Add/Copy Table tool opens the SQL Add/Copy Table dialog box.  This dialog box enables you to add or copy a table to your SQL database.

From Table/To Table

Enter the name of the source table (which contains the records you want to add) in the From text box, and the name of the destination table in the To box.  The source table must be a valid table; the destination table must either be a valid table or a new table

If you already know the name of the table you want, type it in the Table Name text box.  If the table is not in your working directory use an alias; for example, :ORA_DB:CUSTOMER.

If you need to browse to find the name of the table, choose Browse. This opens the Paradox File Browser.

Table Type

The table type is automatically displayed to the right of the table name.  If you choose an invalid file, Paradox sets the table type to "Unknown."  If the destination table does not yet exist, you can specify that it be of a particular table type by selecting the To field and right-clicking.  This opens a drop-down list of available aliases.

If no alias and no type is specified, Paradox sets the table type to "Unknown."

Note:  You can also add records across aliases. Choose the source table in one alias and the destination table in another.  The destination field types shown are native to the alias you chose, and the translation occurs upon adding.

Show Structure

To see the structure of the selected table, choose Show Structure. If the table cannot be displayed, Paradox displays a warning.

If necessary, you can use Show Structure to display the structure of the destination table.  You might need to do this to change field names to avoid a reserved word in the target table. (For example, the word "DATE" is a reserved word in Oracle, and cannot be used for a field name.)

Options

Append	Add records at the end of a non-indexed destination table.

Update	Compare records in both source and destination tables.  Where key values match, let data from the source table over-write data from the destination table.

Append & Update	Update records with matching key values, and append the rest to the end of the destination table.


See Also

Adding or Copying a Table


Manage Index Tool
Manage Indexes Tool

See also

The Manage Indexes tool opens the SQL Manage Indexes dialog box.  This dialog box enables you to create a new index for a table, modify an existing index, or delete an existing index.

Table Name

If you already know the name of the table you want, type it in the Table Name text box.  If the table is not in your working directory use an alias; for example, :ORA_DB:CUSTOMER.

If you need to browse to find the name of the table, choose Browse. This opens the Paradox File Browser.

Table Type

When you choose a table, its type appears in the dialog box.

Indexes

Click Indexes to open the SQL Table Indexes dialog box.  This dialog box shows the structure of the table to be indexed, and lists indexes that already exist on that table.

Buttons on the SQL Table Indexes dialog box are used to define a new index, or modify or delete an existing index.


See also

Defining a new index

Modifying an existing index

Deleting an index


Defining a New Index

To create a new index:

1.	Complete the SQL Manage Indexes dialog box, filling in the Table Name and Table Type fields.

2.	Choose Indexes to open the SQL Table Indexes dialog box.  This dialog box shows the structure of the table to be indexed, and lists indexes that already exist on that table.

3.	Complete the New Index Name text box.

If the table is a Paradox table and you choose only one field for the index, the index is automatically given that field's name.  If you enter a different name, Paradox warns you that the name will be overwritten with the field name.

If you chose more than one field on a Paradox table, or index a non-Paradox table, you must enter an explicit index name.

If the table is an SQL table, the index name must be unique not just for the target table but for the entire volume.  (In other words, there must not be an index with the same name on any other aliases to that SQL server.)

Note:	If you attempt to give the new index a non-unique name, SQL Tools appends a four-digit time stamp to the index, signifying the time the index was created.  This creates an index with the appearance of the name you specified, yet still unique to the volume.

4.	Choose Define New to define the index.  This opens the SQL Table Index dialog box.  Highlight the names of the fields you want indexed and choose >> to move them to the Indexed Fields text box.  Click the Up and Down Charge Order buttons to set the sort position of each field in the index.

5.	Check any appropriate Index options:

Maintained	The index must be incrementally maintained; that is, after changes made to a table are saved, only that portion of the index affected by the change will be updated.  (Paradox only)

Unique	The index must not contain any duplicate key values.

Case-insensitive	The index searches characters without regard for case; for example, it does not differentiate between "a" and "A."

6.	Once you have the desired index design, choose Save Index.  This saves the index definition and returns you to the SQL Table Indexes dialog box.

7.	Choose Index to make the changes permanent.

If there are any problems creating the index (for example, if your current alias is on an SQL server and you have specified a non-unique index name), Paradox displays an error message.  Where possible, this includes the SQL server's native error code and message.

Field Type Limitations

You cannot index on blob or memo field types.

You cannot create or modify array types.


Modifying an Existing Index

To modify an existing index:

1.	Complete the SQL Manage Indexes dialog box, filling in the Table Name and Table Type fields.

2.	Click Indexes to open the SQL Table Indexes dialog box.  This dialog box shows the structure of the table to be indexed, and lists indexes that already exist on that table.

3.	Highlight the name of the index you wish to modify.

4.	To redefine the current index, choose Define New.  This opens the SQL Table Index dialog box.  Use the directional and Change order buttons to modify the fields and sort order of the current index.

5.	Check any appropriate Index options:

Maintained	The index must be incrementally maintained; that is, after changes made to a table are saved, only that portion of the index affected by the change will be updated.  (Paradox only)

Unique	The index must not contain any duplicate key values.

Case-insensitive The index searches characters without regard for case; for example, it does not differentiate between "a" and "A."

6.	Once you have the desired index design, choose Save Index.  This saves the index definition and returns you to the SQL Table Indexes dialog box.

7.	Choose Index to make the changes permanent.

If there are any problems creating the index (for example, if your current alias is on an SQL server and you have specified a non-unique index name), Paradox displays an error message.  Where possible, this includes the SQL server's native error code and message.

Field Type Limitations

You cannot index on blob or memo field types.

You cannot create or modify array types.


Deleting an Existing Index

To delete an existing index:

1.	Complete the SQL Manage Indexes dialog box, filling in the Table Name and Table Type fields.

2.	choose Indexes to open the SQL Table Indexes dialog box.  This dialog box shows the structure of the table to be indexed, and lists indexes that already exist on that table.

3	Highlight the name of the index you wish to delete.

4.	To delete the index, choose Delete Index.


Info Structure Tool

Click the Info Structure tool to open the SQL Info Structure dialog box.  This dialog box enables you to look at the structure of an existing SQL table.

To modify the structure of an existing SQL table, use the Paradox SQL Editor.  (For further information, see the Paradox for Windows online Help.)

Table Name

If you already know the name of the table you want, type it in the Table Name text box.  If the table is not in your working directory use an alias; for example, :ORA_DB:CUSTOMER.

If you need to browse to find the name of the table, choose Browse. This opens the Paradox File Browser.

Table Type

When you choose a table, its type appears in the dialog box.

Show Structure

To show the structure of the table, choose Show Structure.  This opens the SQL Info Structure Browser.

If there are any problems displaying the table structure, Paradox displays an error message.  Where possible, this includes the SQL server's native error code and message.