Creating SQL queries

SQL (Structured Query Language)-descended from SEQUEL (or Structured English QUEry Language)-is a language for constructing relational database management systems (RDBMS) on any hardware platform. It is now the standard language for network queries across different hardware and software platforms. For more information about SQL servers and transactions, see "SQL servers and transactions."

A query is a way to retrieve information from a database. Queries are usually in the form of a question. For example, you can find out which customers placed orders in a given month, the total amount billed for all orders, or which orders haven't been paid.

For more information about SQL's role in Paradox, see "SQL-enabled Paradox."

In this section, you'll learn about

  working with the Visual Query Builder
  
  working with tables in the Visual Query Builder
  
  working with query fields in the Visual Query Builder
  
  joining tables by using the Visual Query Builder
  
  working with summary data
  
  grouping and separating selection criteria
  
  displaying nested criteria in the Visual Query Builder
  
  creating and using SQL expressions
  
  creating and saving SQL statements
  
  working with text in the SQL Editor
  
  customizing the Answer table (SQL)
  
  working with aliases
  
  working with live queries (SQL)
  
  combining selection criteria
  
  connecting to SQL databases
  
For more information about SQL queries, see "Reference: Creating SQL queries."

Working with the Visual Query Builder

The Paradox Visual Query Builder lets you create, run and update SQL queries within the query notebook without typing a single line of code. You can also view Visual Query Builder settings as SQL code

When you run a query created using the Visual Query Builder, Paradox displays the results in an Answer table.

For more information about the Visual query notebook, see "The query notebook."

To open the Visual Query Builder

1	Click File  New  SQL file.

2	Click Visual Query Builder.

To create a query by using the Visual Query Builder

1	Click File  New  SQL file.

2	Click Visual Query Builder.

3	Click SQL  Add table.

4	Select a table from the list.

5	Click Add.

6	Click Close.

7	Specify the selection criteria or selection conditions.

Notes

  For more information about selection criteria, see "Working with selection criteria."
  
  For more information about Selection conditions, see "Combining selection criteria."
  
To run a query by using the Visual Query Builder

  In the Visual Query Builder, click SQL  Run SQL.
  
Note

  When you run an SQL query, the SQL server performs all error or syntax checking and executes the statement. The query results display in an Answer table.
  
To view Visual Query Builder settings as SQL code

  Click the Toggle editor button on the property bar.
  
To update a query in the Visual Query Builder

1	Click the Toggle editor button on the property bar.

2	Edit the SQL code.

3	Click SQL  Send to query builder.

Working with tables in the Visual Query Builder

Each table in the VQB displays as a scrolling, resizable, collapsible window that contains a header with the table name and a list of the table's fields.

Using the VQB, you can add tables as well as view the table in collapsed mode. You can remove tables from the VQB and you can rename tables as well as create a secondary index for them.

For more information about the VQB interface, see "SQL builder user interface."

For more information about SQL tables, see "Working with SQL tables."

To add a table to a query by using the Visual Query Builder

1	In the Visual Query Builder, click SQL  Add table.

2	Choose a table from the list.

3	Click Add.

4	Click Close.

Tip

  You can also add a table to a query by choosing a table from the Add table list box.
  
To remove a table from a query using the Visual Query Builder

  In the Visual Query Builder, right-click a table, and click Remove table.
  
Tip

  You can also remove a table from a query by selecting a table and pressing DELETE.
  
To rename a table by using the Visual Query Builder

1	In the Visual Query Builder, right-click a table, and click Edit table alias.

2	Type the new table name in the box.

To view a table in collapsed mode in the Visual Query Builder

  On the title bar of a table, click the Down arrow button.
  
To create an index for a table

1	Click File  Open  Table.

2	Choose the drive and folder where the table is stored.

3	Double-click a table.

4	Click Format  Restructure table.

5	Click the Secondary index tab.

6	In the Define index area, choose a field from the Fields list.

7	Click the right arrow.

8	Click Save.

Note

  When you use an SQL table in Paradox, the table must have a unique index. If the table does not have a unique index and you insert a record, you may not be able to view the record until you close the table and re-open it. For more information about setting an index for an SQL table, see "Naming SQL indexes."
  
Working with query fields in the Visual Query Builder

Each table in the Visual Query Builder table pane lists the names of its fields. You can select one field or all of the fields in a table to display in the Answer table. You can also change the order in which the fields display in the Answer table.

To select a field to query

  In the Visual Query Builder enable the check box beside a field in a table window.
  
Note

  Next to each field and the table name is a check box. If the check box beside a table name is enabled, all fields from that table will be in the query. Otherwise, only fields whose check boxes are enabled are included in the query. If the check boxes for at least one, but not all, fields are enabled, the check box beside the table name displays a gray check mark.
  
To query all fields in a table

  In the Visual Query Builder, enable the check box beside a table name.
  
Note

  A black check mark beside the table name indicates that all fields are selected. If this check box is disabled, all fields from the query are removed. If the check box beside the table name displays a gray check mark, only a portion of the fields have been selected.
  
To reorder selected fields

1	In the Visual Query Builder, drag a field from a table to the Output name column.

2	Drag the row marker of the field to a new position.

Joining tables by using the Visual Query Builder

If two or more tables in a query contain a common field, you must join the tables on that field to control the size of the query result set. You can do this by using the Visual Query Builder.

You can also separate tables by using the Visual Query Builder.

To join tables using the Visual Query Builder

1	Click the Joins tab of the Visual query builder.

2	Click in the first Field column, and from the list, choose the field you want to match from the first table.

3	Click in the Operator column, and choose an operator from the list.

This operator defines the type of comparison between the two summaries.

4	Click in the second Field column, and from the list choose the field you want to match from the second table.

Note

  A join is indicated in the Visual Query Builder by a single line that connects the two table windows at their table name.
  
To separate tables using the Visual Query Builder

1	In the Visual Query Builder, click the Joins tab.

2	Right-click the row marker of a joined table, and click Delete row.

Working with summary data

You can summarize data to produce the average, sum, and so on, of a group of fields. You can also specify a different output name for a field in the Answer table.

To summarize the data in a field

1	In the Visual Query Builder, enable a box beside a field.

2	Click the Selection tab.

3	In the Field column, right-click the name of the summary field and click Summary.

4	Click in the Summary column, and choose a function from the list box.

Note

  When you produce summary data, the Visual Query Builder automatically groups on all of the non-summary fields to satisfy SQL syntax requirements.
  
To specify an output name for a field

1	In the Visual Query Builder, click the Selection tab.

2	In a table enable the check box beside a field that you want to display in the Answer table.

3	Click the default name of the field in the Output name column.

4	Type a new name for the field.

Grouping and separating selection criteria

You can create a group query to group data from the source tables and produce a single summary row for each row group. A group query can also be separated.

For more information about selection criteria, see "Working with selection criteria."

To create a group query

1	In the Visual Query Builder, click the Criteria tab.

2	Hold down CTRL, and click the row marker of each row you want to group.

3	Right-click one of the selected row markers, and click Group rows.

4	Click SQL  Run SQL.

To separate selection criteria

1	Click View  SQL Editor.

2	Type the opposite to the existing operator in the grouped expression.

3	Click Show VQB.

Notes

  Some operator opposites are as follows.
  
Operator	Opposite

>	<

<>	=

   >=	<=

  For a full list of operators, see "Visual query operators."
  
Displaying nested criteria in the Visual Query Builder

You can create a query that combines selection criteria to pull out very specific data from your tables.

You can display nested criteria as either an individual row or as a single row.

For more information about selection criteria, see "Working with selection criteria."

To display nested criteria as an individual row

  Click the drill-down arrow next to a row containing nested criteria.
  
To display nested criteria as a single row

  Click the drill-out arrow in the top row marker.
  
Creating and using SQL expressions

SQL expressions are used for retrieving specific information from databases. For example, if you are looking only for records whose identification number is greater than 10 or less than 25, you would use an SQL expression. You can create and use different types of SQL expressions such as one with two-summary expression grouping criteria, or you can create HAVING summary expressions.

To create an SQL expression

1	In the Visual Query Builder, click the Group criteria tab.

2	Right-click the grid, and click SQL expression.

3	Type an SQL expression.

An example of an SQL expression is

SUM (Qty * Price) > 1000

To create two-summary expression grouping criteria

1	In the Visual Query Builder, click the Group criteria tab.

2	Right-click the grid, and click Two summary expression.

3	Click in the first Summary column, and from the list choose a summary operator.

4	Click in the first Field or value column and choose a field or value.

5	Choose an operator from the Operator list box.

This operator defines the type of comparison between the two summaries.

6	Click in the second Summary column and choose  a summary operator.

7	Click in the second Field or value column and choose a field or value.

To create a Simple HAVING summary expression

1	In the Visual Query Builder, click the Group criteria tab.

2	Right-click the grid, and click Simple HAVING summary expression.

3	Click the Summary column and choose a summary operator.

This operator defines the type of comparison between the two summaries.

4	Click the first Field or value column and choose a field or value.

5	Choose an operator from the Operator list box.

This operator defines the type of comparison between the two summaries.

6	Click the second Field or value column and choose a field or value.

To use an SQL expression

1	In the Visual Query Builder, click the Criteria tab.

2	Right-click the Criteria page grid, and click SQL expression.

3	Type an SQL expression in the SQL expression column.

An example of an SQL expression would is

(Customer No < 2000) OR (Customer No > 3000)

Note

  String and date constant values must be surrounded by single or double quotes; for example, (Customer first name = "Jane") AND (Customer last name = "Doe") AND (Customer birth date = "11/11/76").
  
Creating and saving SQL statements

SQL statements are the instructions you use to communicate with databases on SQL servers. You can create SQL statements by typing them directly in the SQL Editor. After creating an SQL statement, you can save it.

If you are not prompted to save your changes when you close the SQL Editor, you must change your developer preferences.

For more information about using the SQL Editor, see "Using the SQL Editor."

For information about navigating the SQL Editor by using your keyboard, see "Navigating the SQL Editor by using the keyboard."

To create an SQL statement

1	Click File  New  SQL file.

2	Click Blank editor.

3	Type the SQL code in the SQL Editor.

To save an SQL statement

  In the SQL Editor, click File  Save as.
  
To enable the prompt-to-save option

1	In the SQL Editor, click Tools  Settings  Developer preferences.

2	Click the Display tab.

3	In the Options area, enable the Prompt to save check box.

Working with text in the SQL Editor

If you need to edit text within the SQL Editor, you can choose a word, line, or block of text You can also find and replace text.

For more information about working with text in the SQL Editor, see "Searching for text in the SQL Editor."

For information about how to move around the SQL Editor with your keyboard, see "Navigating the SQL Editor by using the keyboard."

For more information about using the SQL Editor, see "Using the SQL Editor."

To access the SQL Editor

  Click View  SQL Editor.
  
To select text

To select	Do the following

A word	Double-click the word

A line of text	Click the beginning of the line and drag the cursor through the line.

A block of text	Click the start of the text. Hold down SHIFT, and click the end of the text.

Tip

  You can also select a block of text by clicking and dragging the mouse over the text.
  
To find and replace text in an SQL Editor window

1	In the SQL editor, click Edit  Search  Replace.

2	Type text in the Search for box.

3	Type text in the Replace with box.

4	Enable one or more of the following check boxes:

  Case sensitive-searches for the text exactly as you typed it, including capitalization
  
  Backwards-searches backward from the cursor
  
  Whole words only-ignores partial matches
  
  Advanced pattern match-uses Paradox's extended wildcards
  
5	Click Find.

Tip

  You can also click Edit  Search  find, to find text in the SQL Editor window.
  
Customizing the Answer table (SQL)

A Paradox query that retrieves data or performs calculations produces an Answer table, a temporary table that Paradox stores in your private directory and replaces each time you perform a query. Paradox deletes the Answer table when quit Paradox. If you want to save the Answer table, you must rename it before you run another query.

If a query uses the INSERT, DELETE, or CHANGETO reserved words, Paradox does not display an Answer table. Instead, it changes the data in one of the tables represented in the query and creates an Inserted, Deleted, or Changed table. For more information about the Inserted, Deleted, or Changed tables, see "Using reserved words to perform queries."

You can change the displayed names of fields in the Answer table.

The structure of the Answer table is in the same order as they are checked (chronologically) in the Visual Query Builder. You can change the order of fields in the Answer table.

By default, Paradox sorts records in the Answer table in ascending order based on the values in the fields you enable, from left to right. That is, it sorts on the left-most field, then the next field, and so on. You can change the sort order of the values in a given field from ascending sort order to descending. In addition, you can change the Answer table field sort order. For example, you can set Paradox to sort under the second or third field in the Answer table without affecting the order in which the fields display.

For more information about how an ascending sort order applies to the different Paradox field types, see "Ascending sort order."

To rename an Answer table

1	Click Format  Rename table.

2	Type a new name in the To box.

Tip

  You can type the full path in the To box if you want to save the table to a different folder (other than your private folder).
  
To modify the Answer table properties

1	Click SQL  Properties.

2	Click the Answer tab.

3	Enable one or more of the following options:

  Live query view-produces a live query view instead of an Answer table
  
  dBASE-saves the Answer table in dBASE format
  
  Table name-type a new name for the Answer table in this box
  
  Browse-identifies a new folder in which to save the Answer table
  
Notes

  When you enable the Live query view option, a query generates a table that can be edited, instead of a simple Answer table. Any data you change in the live query view table is updated in the source table(s).
  
  For more information about live query views, see "Working with live queries (SQL)"
  
To change a field name in the Answer table

1	Click the Selection tab in the Visual Query Builder.

2	Select a field in a table.

3	Type the field name in the Output name column.

To change the order of fields in the Answer table

1	Click the Selection tab of the Visual Query Builder.

2	Click the row marker next to a name in the Output name column.

3	Drag the row to a new position.

To change the sort order for Answer table fields

1	Click the Sorting tab of the Visual Query Builder.

2	Choose a field from the Output fields list.

3	Click Add.

4	Click the field name in the Sorted by list.

5	Click one of the following:

  a..z
  
  z..a
  
By default, the fields are sorted in ascending order.

Note

  The order of the fields as they display in the Answer table is dictated by the SQL SELECT statement. To change this order, see "To change the order of fields in the Answer table."
  
To change the order of the sorting fields

1	In the Visual Query Builder, click the Sorting tab.

2	Click a field in the Sorted by list.

3	Click one of the following arrows:

  up-moves the field up the sorting list
  
  down-moves the filed down the sorting list
  
Working with aliases

Before running an SQL statement, you must specify the alias that the statement will run against. You to choose any of the aliases that exist. You can also create a Query By Example query on an SQL table.

To specify an alias in the SQL Editor

1	Click SQL  Select alias.

2	Choose an alias from the Alias list box.

Notes

  If you do not specify an alias, the alias :WORK: is used by default.
  
  For more information about using the SQL Editor, see "Using the SQL Editor."
  
Tip

  You can also specify an alias by choosing the alias from the Select alias list box on the property bar.
  
To create a QBE query on an SQL table

1	Click File  New  Query.

2	Choose the alias to the SQL server from the Alias list box.

3	Double-click a table.

4	Create the QBE query.

Notes

  If the SQL database does not support an equivalent SQL statement for a QBE query, a message confirms that the query is processing in the QBE environment.
  
  For information about creating QBE queries, see "Creating, opening, running, and saving a Query By Example query."
  
Working with live queries (SQL)

When you create a Paradox query that generates an answer, the Answer table generated by the query does not maintain a relationship with the original table you queried. To create a relationship with the table queried, you can create and run a live query view. Paradox generates an answer set that is a limited, direct view into the table you queried. The view is limited by the selection conditions you specify in the query. SQL queries on three tables or fewer can return live query views.

Before running a live query, see "Restrictions on live queries."

You can edit data in a live query view as if it were in any other table you work with in Paradox. When you edit the data in a live query view, you are really editing the table you queried and using this limited, direct view to see only the data you want from that table.

You can delete records from the live query view.

You can save a live query in the same way as you save a query that generates an Answer table. The live query view is a temporary view of the table you queried. You can save the table generated by the live query.

You can add a live query view to an existing query.

Constraining the live query view ensures that all future queries contain a live query view.

To create a live query view (SQL)

1	Click File  New  SQL file.

2	Click Visual Query Builder.

3	Click SQL  Properties.

4	Click the Answer tab.

5	Enable the Live query view option.

Notes

  Each time you run the query, Paradox generates a new live query view of the table's data.
  
  When Paradox creates a live query view, live field indicators display next to each live field.
  
To run a live query (SQL)

  Click SQL  Run SQL.
  
Note

  Each time you run the query, Paradox generates a new live query view of the table's data.
  
To edit the live query view (SQL)

1	Press F9.

The live query view is brought into Edit mode.

2	Edit the table data.

Note

  All changes to data in the live query view are updated immediately in the source table.
  
To delete a record from a live query view (SQL)

1	Click the row you want to delete.

2	Click Record  Delete.

Notes

  Deletions from Paradox tables cannot be undone.
  
  Deleting a record from a live query view also deletes the record from the source table.
  
To save a live query (SQL)

1	Click File  Save.

2	Type a name in the File name box.

Note

  The live query view is converted to a standard Answer table and saved in your private directory. Edits you make after saving the live query view are no longer updated in the source table.
  
To save the table generated by the live query (SQL)

1	Click SQL  Run SQL.

2	Click File  Save.

To add a live query view to an existing query (SQL)

1	Click File  Open  SQL file.

2	Double-click an SQL file.

3	Click SQL  Properties.

4	Click the Answer tab.

5	Enable the Live query view option.

To constrain the live query view (SQL)

1	Click Tools  Settings  Preferences.

2	Click the Query tab.

3	In the SQL answer constraints area, enable the Constrained updates check box.

To constrain one live query view at a time (SQL)

1	Click SQL  Properties.

2	Click the SQL tab.

3	In the Constraints area, enable the Constrained updates check box.

Combining selection criteria

Paradox allows you to include two or more types of selection criteria in a query. By combining selection criteria, you can fine-tune a query to return only specific types of data in the query result set. The full list of logical operators can be displayed or hidden, from which you can choose an option with which to combine selection conditions. After creating a selection condition, you can remove it. You can also prevent duplicate rows in the answer set.

To hide or display logical operators

1	Click the Criteria tab of the Visual Query Builder.

2	Right-click the grid, and click Row info in the submenu that displays.

A check mark next to the Row info option indicates that the operators are displayed.

To choose a combining option

1	Click the Criteria tab of the Visual Query Builder.

2	From the list box at the top of the Criteria page, choose one of the following keywords:

  ALL-Specifies that all selection criteria in the grid must be true for the combined criteria to be true. When the Row info command is enabled, an AND will display next to each row after the first row.
  
  ANY-Specifies that at least one of the selection criteria in the grid must be true for the combined criteria to be true. When the Row info command is enabled, an OR will display next to each row after the first row.
  
  NONE-Specifies that all of the selection criteria in the grid must be false for the combined criteria to be true. When the Row info command is enabled, a NOT will display next to the first row, and an AND will display next to each row after the first row.
  
  NOT ALL-Specifies that at least one of the selection criteria in the grid must be false for the combined criteria to be true. When the Row info command is enabled, a NOT will display next to the first row and an OR will appear next to each row after the first row.
  
Note

  To enable the Row info command, see "To hide or display logical operators."
  
To remove a selection condition

1	Click the Criteria tab of the Visual Query Builder.

2	Right-click a row, and click Delete row.

To prevent duplicate rows in the answer set

1	Click the Selection tab of the Visual Query Builder.

2	Enable the Remove duplicates check box.

The DISTINCT keyword is added to the SQL statement.

Connecting to SQL databases

If you want to connect to an SQL database without first performing a database action, you can connect manually to the database. You can also disconnect manually from the database.

While connected to an SQL database, you will be constructing queries. As you construct these queries, the SQL code is being recorded in the background in the SQL Editor. You can view the SQL translation of a QBE query.

There are guidelines that you must follow before you connect to an SQL database. To view these guidelines, see "Rules for connecting to an SQL database."

For more information about using the SQL Editor, see "Using the SQL Editor."

To connect manually to an SQL database

1	Click Tools  Alias manager.

2	Choose an alias from the Database alias list box.

Paradox displays the alias and its connection parameters in the upper-right corner of the Alias manager dialog box.

3	Choose a driver from the Driver type list box.

4	Type your password in the Password box.

5	Click Connect.

Note

  If the connection to the server is successful, Paradox continues with the operation you requested. The connection remains until you quit Paradox or manually disconnect.
  
To disconnect manually from an SQL database

1	Click Tools  Alias manager.

2	Choose an alias from the Database alias box.

3	Click Disconnect.

Notes

  If you connect to an SQL database and then move to another project, that alias is still available until you manually disconnect from the database.
  
  If you have used the same name for aliases in two different projects, you may want to disconnect from the SQL database before you work in another project to avoid confusion.
  
Tip

  You can disconnect automatically from a database by quitting Paradox.
  
To view the SQL translation of a QBE query

  Click View  Show SQL.
  
Paradox opens the SQL Editor and displays the SQL statement for your query.

Notes

  If the SQL database does not support an equivalent SQL statement for a QBE query, a message confirms that the query is processing in the QBE environment.
  
  For information about how to move around the SQL Editor by using your keyboard, see "Navigating the SQL Editor by using the keyboard."
  
Reference: Creating SQL queries

The following topic contains reference information pertaining to Structured Query Language queries.

The query notebook

The Query notebook is a tabbed notebook of query parameter grids that allow you to specify different options for the query. Each page in the notebook has options that affect the query in a particular way.

Criteria page

The Criteria page allows you to specify selection criteria that the query will use to exclude certain rows of data from the query results.  The grid contains the selection criteria by which the query will exclude rows of data. Adding selection criteria to this page adds a WHERE clause to the query. The criteria can be a simple expression, an SQL expression, or an EXISTS clause. The list box specifies whether ALL, ANY, NONE, or NOT ALL of the criteria apply.

Selection page

The Selection page allows you to create summary data. It also allows you to specify a customized output name for a field or for summary data in the query results.

Grouping page

The Grouping page lets you create a grouped query. A grouped query groups the data from the source tables and produces a single summary row for each row group.

Group Criteria page

The Group Criteria page allows you to specify group selection criteria used in the HAVING clause that Visual Query Builder adds to the query. A HAVING clause selects and rejects row groups. The group criteria can be a simple expression, an SQL expression, or a two-summary expression.

Sorting page

The Sorting page lets you specify a sort order for the Answer table generated by the query.

Joins Page

The Joins page lets you create multitable SQL queries (joins).

Include unmatched records

This check box let you specify full, left, and right outer joins. If only the first check box is enabled, a left outer join is added to the query. If only the second check box is enabled, a right outer join is added to the query. If both check boxes are enabled, a full outer join is added to the query. If neither box is enabled (the default), an inner join is added to the query.

Join list box

This box displays above the grid and allows you to specify a particular join. When you choose a join in this box, both tables in the join appear. When a join is selected, the grid contains the field information for that particular join.

Joins grid

The Joins grid contains three columns: Field, Operator, and Field. To include a field as a choice in the Fields column list boxes, choose the field in the table. The Operator column allows you to specify a comparison operator for the join. You may choose from =, <, >, <=, >=, and <>.

Visual query operators

Operator	Description

>, <, >=, <=, <>	Standard Boolean operators for a comparison test

LIKE 	Adds a LIKE clause to the query. Tests whether the data matches the specified pattern.

NOT LIKE	Adds a NOT LIKE clause to the query. Tests whether the data value does not match the specified pattern.

IN	Adds an IN clause to the query. Tests whether the data matches at least one value in the list of values. To create this list of values, type fields or values separated by commas in the second Field or Value column.

NOT IN	Adds a NOT IN clause to the query. Tests whether the data does not match any value in the list of values. To create this list of values, type fields or values separated by commas into the second Field or Value column.

BETWEEN	Adds a BETWEEN clause to the query. A BETWEEN clause tests whether the field or value falls within a specified range of values. For example, you can use this to return a list of salespeople whose sales are between $50,000 and $200,000.

NOT BETWEEN	Adds a NOT BETWEEN clause to the query. A NOT BETWEEN clause tests whether the field or value is outside a specified range of values.

IS NULL	Adds an IS NULL clause to the query. Tests whether the field or value contains a NULL value.

IS NOT NULL	Adds an IS NOT NULL clause to the query. Tests whether the field or value does not contain a NULL value.

SQL-enabled Paradox

Since Paradox supports the use of SQL operations against tables, the SQL Editor is visible in the toolbar even if an SQL driver is not installed. When an SQL driver is installed, Paradox is SQL-enabled, and the desktop changes in the ways described below.

New icons

Whenever you access the SQL server, you see the SQL hourglass.

Working directories

Since you cannot store objects such as documents, queries, reports, and forms on SQL servers, you cannot set an SQL server as the location for your working directory.

Refreshing data displays

When you are using local tables, all users see their view of the data refreshed as soon as one user makes a change to a shared database. However, when you are working on an SQL server, this does not occur.

Using Table windows

When Paradox is SQL-enabled, table windows change in the following general ways:

  Table windows of SQL data do not display record numbers; the scroll box is always in the center of the vertical scroll bar.
  
  Because of the differences in how indexing functions in an SQL environment, there are minor differences in the Filter tables dialog box:
  
  If an SQL table has any indexes, the table is always sorted based on the specified index (you can choose which one). This allows fast and reliable updates.
  
  Filter expressions on SQL tables can include arithmetic operators.
  
  When you query an SQL table using Query By Example (QBE), Paradox stores the SQL table properties in a file with the file name extension .tvs. This helps distinguish them from table window property files for Paradox tables (.tv) and dBASE tables (.tvf).
  
  TVS files for SQL tables are not automatically deleted when you delete the SQL table. Also, if you change your private directory the table will no longer be displayed with the properties you set.
  
  If you try to view an SQL table when someone else is editing data, you may have to wait until the other user is finished editing.
  
Changes in QBE

The characteristic behavior of SQL update queries means that updates to SQL data are performed either completely or not at all. When you use QBE to perform updates on SQL data, Paradox does not generate any of the following auxiliary tables:

  changed.db
  
  inserted.db
  
  deleted.db
  
  errorchg.db
  
  errorins.db
  
  errordel.db
  
Querying SQL data

Paradox provides several different ways to perform operations against SQL data such as:

Pass-through SQL

Programmers familiar with SQL can use the SQL Editor to enter, execute, or save an SQL statement on a remote SQL server. The remote SQL server performs all error or syntax checking. You can save the SQL statement (the SQL Editor automatically saves the file with the extension .sql), and you can later load, modify, or execute it.

Using QBE to query SQL data

QBE provides you with a graphical format that helps you show the kind of information you want in the Answer table. When you use QBE to query a table in an SQL database, Paradox attempts to translate the query to an equivalent SQL statement and pass it to the SQL server. If successful, the server processes the query, and passes the answer set to you through the SQL driver. The SQL Editor lets you view the equivalent SQL statement for the query at any time during query construction or after it is processed.

Borland SQL Links also supports queries that join SQL tables with local tables or with SQL tables from different SQL databases (heterogeneous queries). Heterogeneous queries are always processed according to QBE rules. For more information about heterogeneous queries, see "Heterogeneous queries."

Viewing the SQL translation of a QBE query

When you use QBE to query an SQL table, Borland SQL Links attempts to translate the query to an equivalent SQL statement and pass it to the SQL server. The server processes the query, and passes the answer set to you through Borland SQL Links. Paradox lets you view the equivalent SQL statement for the query at any time during query construction or after it is processed.

Depending on the type of query you create, the SQL translation will be one of the following types of statements:

Query result	Equivalent SQL statement

Display specific data	SELECT

Add new data	INSERT

Change data	UPDATE

Remove data	DELETE

Working with SQL tables

With SQL tables, you can define the structure (fields and types), specify required fields, and define indexes for SQL tables. Other features of Paradox tables, such as validity checks and referential integrity, are not supported on SQL tables.

When you use an SQL table in Paradox, the table must have a unique index. If it does not have a unique index and you insert a record, you may not be able to view the record until you close the table and reopen it. To add a unique index, you must restructure the table and create a secondary index. You can use Paradox to create and modify indexes on SQL tables.

Restructuring SQL tables

When you use Paradox to restructure an SQL table , you can add, modify, and drop indexes. You cannot otherwise use Paradox to change the structure of a table on a server, unless you use pass-through SQL.

When you use an SQL table in Paradox, the table must have a unique index (key). If it does not have a unique index and you insert a record, you may not be able to view the record until you close the table and reopen it.

Naming SQL indexes

With most database servers, index names must be unique for all tables in a database (or in some other predefined workspace). Index names must start with a letter, not a number. When you create an index for an SQL table, Paradox prefixes the index name with the table name to ensure that the index name is unique.

When you create an SQL index, Paradox supplies the prefix "<table>_" for the index name. For example, if you create the index "last_name" on the Customer table, Paradox gives the index the name "customer_last_name."

You can include the table name with the index name or without:

  If you type the index name following "<table>_," Paradox prefixes the index name with the table name and an underscore.
  
  If you delete "<table>_," Paradox omits the table name from the index name. If the index name is not unique, an error occurs when Paradox saves the table.
  
This index naming scheme also affects copying and restructuring.

Sybase index names do have to be unique within a database; therefore, Paradox does not prefix Sybase index names with table names.

To prefix the index name with the table name

Paradox prefixes some index names with the table name, as described in "To create an index for a table." These index names are also affected  as follows when you restructure an SQL table:

  If you create an index, Paradox prefixes the index name with the table name unless you delete the string "<table>_" from the index name.
  
  If you modify an index, Paradox does not modify the index name, unless you rename the index as part of your modification.
  
  If you click File  Save as, Paradox renames all index names with the new table name, even if the index names are not prefixed with the current table name. (Otherwise, a duplicate index name would be guaranteed.) For example, suppose the Employee table contains the following indexes:
  
- Employee_Dept_No

- Employee_Emp_No

- Full_Name

- Job

If you restructure the table and save it as My_Dept, Paradox renames the indexes as follows:

- My_Dept_Dept_No

- My_Dept_Emp_No

- My_Dept_Full_Name

- My_Dept_Job

If, during a restructure operation, you add an index and omit the "<table>_" string or modify an index name in any way, Paradox does not prefix the index name with the table name during the Save as operation.

SQL servers and transactions

SQL servers run on local area network (LAN) file-server systems, minicomputers, and mainframes. They handle requests in logical units of work called transactions. Transaction processing protects data against conflicts that may arise when more than one person is working on a table at the same time.

In SQL, all transactions can be explicitly ended with a command to either accept or discard the changes.

Heterogeneous queries

The Visual Query Builder supports heterogeneous queries-queries made against tables in more than one database. A heterogeneous query may join tables on different servers, and even different types of servers. For example, a heterogeneous query may involve a table in an Oracle database, a table in a Sybase database, or a local dBASE table.

When you execute a heterogeneous query, the Borland Database Engine analyzes and processes the query using Local SQL, so extended, server-specific SQL syntax is not supported. Local SQL is a subset of ANSI-standard SQL enhanced to support Paradox and dBASE naming conventions for tables and fields.

SQL builder user interface

The Visual Query Builder toolbar displays as part of the main Paradox toolbar when the Visual Query Builder is open. It contains the following buttons and list boxes for executing commands.

Button	Command

	Remove the selection, and place it on the Clipboard.

	Copy the selection, and place it on the Clipboard.

	Paste the contents of the Clipboard at the cursor position.

	Execute the SQL query.



List box	Description

Alias	(Visual Query Builder window only) Choose an available database from a list of aliases.

Table	(Visual Query Builder only) Choose a table from the database to add to the query.

Ascending sort order

Paradox sorts numbers and other non alphabetic characters according to the sort order you installed. Alphanumeric "10" sorts before "2" although it is numerically larger.

Here is how an ascending sort order applies to the different Paradox field types.

Field type	Examples of sorted values (from low to high)

Number	0-10

Alpha	A, a-Z, z

Date	1/1/97 to 12/31/97

Money	$1.99 to $99.99

Memo	Not sorted

Graphic	Not sorted

Time	00:00:01 to 23:59:59

Logical	False to True

	F to T

	No to Yes

	0 to 1

Restrictions on live queries

Single-table queries or views can be updated provided that

  there are no UNION, INTERSECT, or MINUS operations
  
  there is no DISTINCT keyword in the SELECT statement
  
  everything in the SELECT clause is a simple column reference or a calculated field, and no aggregation is allowed
  
  there is no GROUP BY or HAVING clause
  
  there are no sub queries that reference the table in the FROM clause and no correlated sub queries
  
  any ORDER BY clause can be satisfied with an index
  
Using Local SQL

The Borland Database Engine (BDE) allows access to both local and remote database tables through Local Structured Query Language (SQL). Local SQL, also known as "client-based SQL," is a subset of ANSI-92 SQL that has been enhanced to support Paradox and dBASE (standard) naming conventions for tables and fields (called "columns" in SQL).

SQL statements

The SQL statements fall two categories: Data Manipulation Language (DML) and Data Definition Language (DDL).

  DML statements are used for selecting, inserting, updating, and deleting table data. Syntax and usage examples are included.
  
  DDL statements are used for creating, altering, and dropping tables, and for creating and dropping indexes. The DDL transforms directly into BDE function calls. Syntax and usage examples are included.
  
Terminology

SQL uses slightly different terminology than Paradox to refer to tables and their components. Below is a list of SQL terms with brief descriptions. The equivalent Paradox terms are in parentheses.

SQL (Paradox)	Description

Table (Table)	A structure of rows (records) and columns (fields) that contains information

Row (Record)	A group of columns (fields) in a table that contain related information about a single record

Column (Field)	A category of information (column) in a table that passes through all rows in the table

Working with selection criteria

Comparison operators and reserved words are used to define the type of information you are looking for selection criteria are used to narrow the scope of the information you retrieve with a query You can combine selection criteria to more accurately define the type of information you are looking for.

Adding selection criteria

Query selection criteria specify which rows of data are included in the query results. You specify selection criteria on the Criteria page of the Visual Query Builder.

Selection criteria types

You can choose one of three selection criteria types for a query:

  SQL expression
  
  simple HAVING summary expression
  
  two-summary expression
  
Using the SQL Editor

The SQL Editor is a full-featured text editor that includes color highlighting and smart tab indent. It also supports BRIEF and Epsilon-style editing.

Use the SQL Editor to directly type, execute, or save an SQL statement. This is sometimes called pass-through SQL. You specify the SQL statement in your server's dialect, and the SQL server performs all error or syntax checking and executes the statement without the help of Paradox.

By default, keywords display in bold, comments in italics.

Keystroke mappings

You can choose from three keystroke mappings in the SQL Editor:

  the default Paradox keymap
  
  BRIEF keymap
  
  Epsilon keymap
  
For a listing of keystrokes that correspond to the keymap you choose in the Developer preferences dialog box, position the cursor on a blank space in the SQL Editor and press F1.

Menus

Using the BRIEF and Epsilon keymaps, you can access the menus by pressing F10 or by pressing ALT, and then pressing the shortcut key for the required menu.

The default keymap allows the same menu access as for BRIEF and Epsilon, but in addition, the menus can be reached by the standard ALT + Key combination; for example, pressing ALT + E the Edit menu.

Standard MDI system keys

Standard MDI system keys are available only for the default keymap. Examples of these keys are:

  CTRL + F6- toggles the MDI window toggle
  
  ALT + F6-toggles the SDI window toggle
  
  CTRL + F4-closes an MDI window
  
Navigating the SQL Editor by using the keyboard

Use the following keyboard shortcuts to navigate in the SQL Editor:

Shortcut	Action

CTRL + LEFT ARROW	Moves the cursor one word to the left

CTRL + RIGHT ARROW	Moves the cursor one word to the right

HOME	Moves the cursor to the beginning of a line

END	Moves the cursor to the end of a line

CTRL + HOME	Moves the cursor to the beginning of the text

CTRL + END	Moves the cursor to the end of the text

PAGE UP	Moves back one screen

PAGE DOWN	Moves forward one screen

BACKSPACE	Deletes the character to the left of the cursor

DELETE	Deletes the character to the right of the cursor

INSERT	Overwrites characters

CTRL + C	Copies the selected text to the Clipboard

CTRL + X	Copies the selected text to the Clipboard and deletes it from the window

CTRL + V	Pastes text from the Clipboard into a method

TAB	Inserts a tab character, and pushes the text to the right

Searching for text in the SQL Editor

You can use the following Edit menu commands to select, locate, and replace text.

Command	Description

Find	Searches for strings of text in code

Find Next	Searches for the next occurrence of the text you specified using the Find command

Replace	Searches for text, and replaces it with the value you specify

Replace next	Replaces the next occurrence of the text specified using the Replace command

Select all	Selects all text in the SQL Editor

Rules for connecting to an SQL database

  Enable SQL database access-Make sure you have a valid user name and password on the SQL server, and at least Read access privileges for the SQL database. See your database administrator for more information.
  
  Install necessary client software-Install any client software libraries required to communicate with the SQL server. Test software is usually included. Before using an SQL alias, make sure this test software can successfully connect to the SQL server 
  
  Install an SQL driver-Install an SQL driver for your SQL server; Paradox supports Borland SQL Links as well as Object Database Connectivity (ODBC).
  
  Configure the SQL driver-When you first install the SQL driver, it uses the default driver settings. Make sure these default settings are right for your server installation before you create any aliases for the SQL database.
  
  Create at least one SQL alias-The SQL database alias includes your user name and password on the target SQL server and is required to access any SQL data. A generic SQL alias is automatically created the first time you modify the default link driver parameters after installation.
  



