[Updated 2/19/2010, added info on localsql.hlp and info on not being able to alter a table and add a primary key.]
[Updated 6/6/2008, added images, cleaned up code and tables, linked message board threads at bottom.]
This chapter focuses on using Paradox as a client/server development tool. It does not talk about connecting; it is assumed you have already connected. If you are having trouble connecting to a particular SQL server, then refer to the Connection Guide for that particular server. This chapter does review what a user can do interactively with Paradox and how to use ObjectPAL with SQL servers.
Who should read this chapter? Anyone interested in getting started with SQL and client/server applications. This chapter also covers using SQL on local tables--Paradox and dBASE tables.
About SQL
Structured Query Language (SQL) was developed to create a standard for accessing database information. The ANSI standard for SQL allows a user to become familiar with the commands needed to query many different types of data. After you learn ANSI SQL, you then can query many different databases.
Is SQL a solid standard? Yes and no. Yes, the core ANSI SQL commands are solid and consistent from vendor to vendor. Every vendor, however, adds capability to its version of SQL. These improvements are expected because ANSI SQL does not go far enough to cover every feature of every high-end DBMS.
The SQL standard is used by many companies for their high-end products. They include Oracle, Sybase, Microsoft SQL, Informix, and Interbase. Paradox also provides the capability to use standard ANSI SQL commands on local Paradox and dBASE tables.
Using SQL on Local Tables
Although SQL by definition is a standard, various flavors are on the market. Paradox, too, has its own flavor of SQL. Local SQL is built on a modified version of the InterBase SQL parser. The parser turns the SQL statements into QBE syntax, and then executes or translates it into BDE function calls.
Note: Paradox is SQL ANSI 92 compliant. This means that you can use any ANSI 92 compliant SQL statement. This is great for learning SQL because you can go out and buy any SQL primer book that is compliant to ANSI 92.
Supported Syntax
Local SQL (sometimes called client-based SQL) is a subset of ANSI 92 SQL enhanced to support Paradox and dBASE (standard) naming conventions for tables and fields (columns in SQL terminology).
Naming Tables
ANSI SQL confines each table or column name to a single word consisting of alphanumeric characters and the underscore. Local SQL, however, is enhanced to support more comprehensive names. Local SQL supports full file and path specifications for table names (including Alias support).
Enclose table names with path or filename extensions in single or double quotation marks. For example,
select * from 'customer.dbf'
select * from ":work:customer.dbf"
Note: If you omit the file extension for a local table name, the table is assumed to be the Table object type specified in the Default Driver setting in the System page of the BDE Administrator.
Column Names
Local SQL also supports Paradox's field names as long as these names are enclosed in single or double quotation marks and prefaced with an SQL table name or table correlation name. For example,
select o."Order No"
from Orders o
Supported SQL Statements
The SQL statements are broken into Data Manipulation Language (DML) and Data Definition Language (DDL). DML statements are used for handling data (selecting, inserting, updating, and deleting), and DDL statements handle the creating, altering, and dropping of tables, and the creating and dropping of indexes.
Category
Supported Keywords
DML Statements
SELECT, INSERT, UPDATE, and DELETE
DML clauses
FROM, WHERE, ORDER BY, GROUP BY, HAVING, and UNION
Aggregate functions
SUM(), AVG(), MIN(), MAX(), COUNT(), and COUNT(*)
String functions
UPPER(), LOWER(), TRIM(), and SUBSTRING()
Operators
+, -, *, /, <, >, =, <>, IS NULL, IS NOTNULL, >=, =<, AND, OR, NOT, ||, and LIKE
DDL statements
CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, DROP INDEX, and CREATE VIEW
Table 16: Supported SQL Keywords
Help! LOCALSQL.HLP
Paradox and the BDE ship with a Win32 Windows help file specific to Local SQL. On my system the help file is located in the following folder:
C:\Program Files\Borland\Common Files\Bde
Do yourself a favor and setup a shortcut to this help file. Here is a screen shot showing the startup page:
SQL Query Properties
Figure 1 shows the Answer tab of the Query Properties dialog box for SQL queries. The following is a short description of each feature:
Live query view: This option enables you to work with the live data generated from an SQL statement (see "Constrained Updates" later in this list for related information).
Answer table: This option enables you to specify if you want a Paradox or dBASE answer table. In addition, it enables you to specify the name of the table.
Figure 1: The Answer tab of the Query Properties dialog box
Figure 2 shows the SQL tab of the Query Properties dialog box for SQL queries. The following is a short description of each feature:
Queries against remote tables: This panel affects only remote SQL data. It enables you to specify whether an SQL statement is run locally or on the remote server. The Run query remotely option is the fastest.
Auxiliary table options: This panel enables you to specify whether auxiliary tables are created. For faster queries, select Fast queries. To retain the ability to undo an SQL statement, select Generate auxiliary tables to create the necessary tables.
Constrained Updates: This option affects live queries only. If it is checked, you can enter values that match the select criteria only.
Figure 2: The SQL tab of the Query Properties dialog box
SQL and the QBE
If you are already familiar with building queries using Paradox's Query By Example (QBE), then an invaluable tool for learning SQL is the Show SQL option available from within the QBE. Figure 3 shows a simple QBE query on the customer table and the SQL Editor after selecting the Show SQL option.
Figure 3: The Show SQL option translates your QBE into SQL
Figure 4 shows a more complicated QBE query joining five tables and the SQL equivalent generated by the database engine.
Figure 4: The Show SQL option works with sophisticated queries
The Visual Query Builder
Available while any SQL editor is showing is the Visual Query Builder (see Figure 5). This brings the available techniques for building queries in Paradox to three. The traditional Query By Example (SQL), the industry standard Structured Query Language (SQL), and the new Visual Query Builder (VQB).
Figure 5: The Visual Query Builder
An SQL Primer
In this section, you learn about ANSI SQL as it applies to local Paradox and dBASE tables in a step by step example format. ANSI SQL is a rich query language. Each command supports many different keywords and parameters. To really learn SQL, you need to get a good ANSI SQL book. You can get started with this section and some of the Paradox and dBASE local tables located in the Example directory. After you learn this core syntax, you can use it with any SQL DBMS including Oracle, Sybase, MS SQL, Informix, and Gupta.
Using select
In this first step into SQL, you learn how to query your local Paradox tables with SQL. Here, you query the Customer and Orders tables using the SQL select command. The syntax for select is quite elaborate. For now, the basic syntax you need is as follows:
select criteria
from tablename
[where whereCriteria]
[order by orderBy]
Step By Step
Change your working directory to the Paradox's Samples directory, and select File | New | SQL File. From the New SQL dialog box, select the Blank Editor option. The SQL Editor opens ready for you to type and execute a single SQL statement.
Referring back to the syntax for select, in place of criteria, you can place the names of columns or an *, which signifies all columns. Type the following SQL statement and save it as SQL-LS1.SQL (see figure 6).
1: select *
2: from Customer
Figure 6: Using the select command
Note: Notice that the .DB extension for the Customer table above is not specified. If you do not specify an extension, Paradox uses the default database type (paradox tables).
Run the SQL query by pressing F8.
Next, specify the names of the columns you want to see separated by commas and ending with a space. For example, type the following, save it as SQL-LS2.SQL, and run the SQL statement. Your screen should then look similar to Figure 7.
1: select Name, Phone
2: from Customer
Figure 7: Specifying columns using select
The count clause enables you to count records. For example, type the following SQL statement into the SQL Editor to find out how many records are in the Customer table:
1: select count(*)
2: from Customer
The where clause enables you to narrow down your search criteria. Using this clause is similar to specifying example elements in Paradox's QBE. Refer to Table 35.1 to see which of the many aggregates and operators are supported by local SQL. Next, use the where clause to specify that you want to see only the records where City is equal to Madeupville. To do this, type the following, save it as SQL-LS3.SQL, and run it. Your screen should look similar to Figure 6.
1: select *
2: from Customer
3: where City = "Madeupville"
You also can use the where clause to link tables similar to how the Data Model does. For example, the following links the Customer and Orders tables. Type, save as SQL-LS4.SQL, and run the following SQL statement. Your screen should look similar to Figure 7. (Note the use of the table name and quotation marks around the fields in line 1. Any field name that contains spaces requires quotation marks.)
3: where Customer."Customer No" = Orders."Customer No"
Commenting SQL Files
You can add a comment to any SQL file using the C-like comment /* and */. For example,
/* This is a comment. */
Using distinct, union, and order by
The distinct keyword prevents duplicate values. The union clause enables you to combine one or more select statements to produce a single result. The order by clause specifies how to order the data. Here is an example of using all three:
select distinct p.Name from �Customer.db' p
union select d.Name from �Customer.dbf' d
order by p.Name
Step By Step
This next example uses a where clause and the and clause. It links the Customer and Orders table on Customer No and displays the five columns (some from each table) where the Balance Due is not 0. Type, save as SQL-LS5.SQL, and run the following SQL statement.
5: where Customer."Customer No" = Orders."Customer No"
6: and Orders."Balance Due" <> 0
This next example introduces the order clause, which enables you to sort the answer table. Type, save as SQL-LS6.SQL, and run the following SQL statement.
This next example demonstrates sorting in a descending order. When you use descending, you must also use distinct. Type, save as SQL-LS8.SQL, and run the following SQL statement.
As previously mentioned, Local SQL supports aliases when referencing table names. This way, you can execute heterogeneous joins; that is, joins of more than one Table object type. For example, you could create an SQL statement that joins Paradox, Oracle, and Informix tables in a single SQL query.
Heterogeneous joins are table links between two or more different types of tables. Because Paradox uses the BDE, you can easily link various heterogeneous tables. For example, the following joins a Paradox table to a dBASE table:
select p.Name, p.'Customer No', d.Customer from
"Customer.db" p,
"Customer.dbf" d
where p.'Name' = d.'Name'
Finally, here are some more select statements you can try. Keep your working directory set to Paradox's Samples directory and type and run any or all of them.
Using insert
The syntax for insert is not nearly as elaborate as the syntax for select, but it is still fairly substantial. Paradox supports only a small subset, however. For now, the basic syntax you will use is as follows:
The next SQL statement uses insert to insert a new record into the Customer table. Type the following, save it as SQL-LI2.SQL, run the SQL statement, and then open the table. Your screen should look similar to Figure 8. Notice that Paradox generates an inserted.db table. You can use these auxiliary tables to undo any changes you need to.
1: insert into Customer
2: (Customer."Customer No", Customer."Name")
3: values (0, �Aurora Cortez')
Figure 8: Using insert on a local table
The next SQL statement uses insert with a nested select statement to add the records from Customer.dbf into Customer.db.
/* Add one table to another. */
insert into "Customer.db"
select * from "customer.dbf"
As briefly mentioned earlier, to increase the speed of the SQL statements, you can turn off the generating of auxiliary tables. From the SQL editor, select SQL | Properties. Select the SQL tab and change the Auxiliary table options (see figure 9). Remember that although the queries run faster, you will not have the benefit of the auxiliary tables. Without the auxiliary tables, it is harder to recover from data problems. This technique works for QBE queries too.
Figure 9: Auxiliary table options
Using update
The syntax for update also is not as elaborate as the syntax for select, but it is still fairly substantial. For now, the basic syntax you will use is as follows:
update tableName
set setcriteria
where wherecriteria
The update command is very powerful, so be cautious when using it. One mistyped word can change all the data in your table.
Step By Step
For your first example, suppose that Sandy Jones and her husband got a divorce and she now wants to use her maiden name. The update command enables you to change the Customer table. Type the following SQL statement, save it as SQL-LU1.SQL, and execute it.
1: update Customer
2: set Name = "Sandy Kowalski"
3: where Name = "Sandy Jones"
The next update statement assumes the entire town of Madeupville was magically moved to Illinois. Type the following SQL statement, save it as SQL-LU2.SQL, and execute it.
1: update Customer
2: set State = "IL"
3: where City = "Madeupville"
This next update statement accomplishes a similar task as step 2, but this SQL statement demonstrates how to specify and update multiple columns. Type the following SQL statement, save it as SQL-LU3.SQL, and execute it.
1: update Customer
2: set City = "SJ", State = "IL"
3: where City = "San Jose" and State = "CA"
Using delete
The syntax for delete is fairly straightforward. The basic syntax you will use is as follows:
delete from tableName
where whereCriteria
Step By Step
Suppose that you want to delete a record or set of records from a database. The delete command enables you to do so. Type the following SQL statement, save it as SQL-LD1.SQL, and execute it.
1: delete from Customer
2: where Name = �Aurora Cortez'
This final delete example adds a comment and the and clause. Suppose that you want to delete all the customers from a table where the customers are in Sacramento, CA. Type the following SQL statement, save it as SQL-LD2.SQL, and execute it.
1: /* SQL-LD2.SQL */
2: delete from Customer
3: where City = "Sacramento" and State = "CA"
Using create table and drop table
The create table SQL commands enable you to create tables. The interesting thing about creating tables with create table is that the create table statement is often portable to other database servers. For example, all the create table SQL statements in this section work on Local Interbase and on Oracle. The syntax for create table is as follows:
Use the following field types: SMALLINT, INT, DECIMAL(x,y), NUMERIC(x,y), FLOAT(x,y), CHAR(n), DATE, BOOLEAN, BLOB(n,s), TIME, TIMESTAMP, MONEY, AUTOINC, and BYTES(n).
Step By Step
Create a table, as follows:
1: create table test
2: (field1 char(20))
Delete it, as follows:
1: drop table test
Create one more tables for use with the following index examples:
1: create table Contacts
2: (Name char(20), Phone char(15), Age SmallInt)
Using create table with the Primary Key Constraint
You can create primary keys with the primary key constraint. The syntax for create table using the option primary key constraint is as follows:
The following is a create table example using the optional primary key constraint:
/* Create emp table with a primary key. */
CREATE TABLE "emp"
(
SSN char(11),
Last_Name char(20),
First_Name char(15),
Salary numeric(10,2),
Dept_No smallint,
PRIMARY KEY (SSN)
)
To delete the primary key, execute the following SQL statement:
1: drop index Contacts.primary
Note: You cannot create a primary key using create index; you must create the primary key at the time you use create table.
Can I add a primary key using Local SQL?
I wish Local SQL supported adding a primary key using standard SQL as in...
ALTER TABLE NoKeyTable ADD PRIMARY KEY (FullName);
...but it doesn't.
Using create index and drop index
Step By Step
To add a secondary index, execute the following SQL statement:
create index secLastFirst
on emp (Last_Name, First_Name)
To delete the newly created secondary index, execute the following SQL statement:
1: drop index emp.secLastFirst
Using Embedded SQL on Local Tables
Just as you can embed query code into your ObjectPAL code and execute it using executeQBE(), you can embed SQL code and execute it using executeSQL(). What is interesting is that embedded SQL statements work on local tables, too. For example, type the following code into the pushButton event of a button:
That's it for the SQL primer. As stated earlier, to learn ANSI SQL really well, you should purchase an ANSI SQL book dedicated to ANSI SQL.
The Client/Server Model
In a client/server model, the database processes are shared between the client and the server. This is called distributed processing. In the case of Paradox, Paradox is the client, and any of the SQL servers that BDE can use can be the server. Oracle, Sybase, Informix, and Interbase are examples of SQL servers. For example, when you connect Paradox to an Oracle server, the database processes are divided between the server and Paradox.
Paradox provides access to SQL servers through SQL Links for Windows. When Paradox communicates with an SQL server, queries--commands--from Paradox need to be in the dialect of the particular server. The link provides this translation and sends the appropriate commands to the server. Because the link is fairly transparent, you do not have to learn SQL.
Back End/Front End
In a client/server model, the SQL server is called the back end. A client application, such as Paradox, is called the front end. When you set up a very large database, you generally have two software-buying considerations: the back end and the front end. Typical back-end servers include Interbase, Oracle, Sybase, Microsoft SQL, and Informix. Typical front-end servers include Paradox, Paradox for DOS, Access, and dBASE for Windows.
Columns and Rows
Sometimes, just getting used to the terminology of a new subject helps you to understand it. In SQL server terms, a column is the same as a Paradox table field's name, and a row is the same as a record.
Overview of Database Application Architectures
Database application architecture has more to do with the software development life cycle than it has to do with Paradox. However, understanding database application architecture is important because it will have a major impact on any large application you develop. Deciding the basic architecture of any application you develop is one of the final steps you undertake when you do your system analysis. You need to decide between a single-user and multi-user application. If you decide that you need a multi-user application, you need to decide between a multi-user network application and a client/server application. Finally, if you decide on a client/server application, you need to decide among a two-tier, three-tier, or multi-tier application.
Single-User Applications
A single-user application contains both the data and application on the same machine. Local tables include Paradox and dBASE and Local Interbase. This type of application is also known as one-tier development because both the application and data reside on the same machine.
Note: Local Interbase is a cross between the local table and the client/server concepts. Although you can develop client/server applications with Local Interbase, one processor processes both the application requests and manages and serves data. The great thing about Local Interbase is that it enables you to develop a client/server application on a single machine and move the data to another processor such as a Windows NT server, NLM server, or UNIX server. Personal Oracle is similar to Local Interbase.
Multi-user Applications
Multi-user one-tier applications are similar to single-user applications. The main difference is that the data is moved to the network. This way, multiple users can access the data. However, this setup is not client/server because the data is still processed by Paradox on a single machine--a single tier.
Client/Server Applications (Fat Client Versus Thin Client)
Client/server applications (two-tier development) split the processing needed to set and retrieve data between two processors, or two tiers. In general, client/server applications are either fat or thin.
A fat client application contains most of the business rules and data integrity on the client (in this case, in Paradox using ObjectPAL). Sometimes program architects like to use this model because they have very strong clients. I have even heard of this design architecture referred to as the muscular client model because a typical client machine today runs at over 100MHz and has more than 16 megabytes of RAM.
A thin client application moves as much of the business rules and data integrity to the server as possible--for example, calculations, required fields, and data link enforcement. This process is accomplished by using data integrity rules such as referential integrity, stored procedures, and triggers. In general, thin client architectures are considered better because they spread more of the processing onto the server, and server machines are generally more powerful than client machines. Most companies today creating client/server applications try to create thin client applications.
Multi-Tier Development
A hot topic in database development today is multi-tiered development. You are already familiar with two-tier or client/server development. Two-tiered development separates the application and data management to two computers (a client and a server). Multi-tiered development takes this to the next logical step by moving more of the processing to another server. It breaks up the application into more than just two logical separations.
Paradox is good at developing the presentation layer, and servers including Interbase, Oracle, and MS SQL are good for developing the database layer. Room is left for the middle layer (some refer to it as the application server layer). You can, for example, place business rules, replication schemes, and data translation rules on the middle layer. You can build the middle layer yourself using languages such as C and C++, or you can buy software designed to aid in the development of middle-ware. Some of the more popular middle-ware applications on the market include EZ-RPC, Entera, RPC Painter, and Tibco.
Interactive SQL
Before you jump into SQL and ObjectPAL, review what Paradox can do interactively with SQL. As a front end for SQL, Paradox rivals anything on the market. Your first experience may well lead you to the same conclusion. When you access SQL tables, you'll notice a few differences from using Paradox tables. The following are some of the obvious features to note:
You can open an SQL in a table window by choosing File | Open | Table. Note that you can directly edit the data in the table.
In previous versions of Paradox, many tasks with SQL servers were accomplished through an add-on utility called SQL Tools. This Paradox form was full of bugs and awkward to use. Creating SQL tables, adding data to an SQL table, restructuring, and so on have now been integrated into the standard Paradox menus. In general, you simply use the option you want and select the SQL alias you want to work with.
You can create a quick form based on an SQL table. Press F9 to go into Edit mode. The default setting for Read-only for all tables in the data is unchecked. Leave this option turned off to edit data. Also worth mentioning is that you need to execute forceRefresh() with table frames to update data altered by other users, but not with fields.
When you query SQL tables with the QBE, note that the SQL button shows you the equivalent QBE query in SQL. If you are familiar with Query by Example (QBE), then you might want to always use it--rather than SQL--to query both local and SQL tables. If you know SQL or have an interest in learning it, however, you can use the SQL Editor built into Paradox.
When you use the SQL File (or query SQL tables with SQL), note that you do not put a semicolon at the end of an SQL statement as you do with other SQL Editors. Also note that you can execute only a single line of SQL at a time.
You can use SQL statements with local tables.
You can base a report on an SQL table or SQL statement. By now, you may have noticed that the link to SQL tables is nearly transparent. Paradox provides an extremely easy-to-use SQL front end.
The button on the vertical scroll bar in a table frame behaves differently than with Paradox tables. While you view remote tables in Paradox, the data is cached. Paradox reads only part of a remote table--unlike a local table. Performance would suffer if the SQL link had to ask the server for the table size and record position while scrolling through the table.
Answer Set
Whenever you access data on an SQL server, you create an answer set. When you open a table in an SQL database in a table window, you can call the data you are currently viewing an answer set. When you execute a specific SQL query--either through a QBE query or an SQL file--you refer to the data returned by the server as an answer set.
Note on Cached Data
Paradox may have occasional delays when accessing data. Paradox is fairly fast at retrieving data if the data is within the set of cached data. If, however, Paradox has to request a new set of records, the SQL link slows down because Paradox has to either generate a fetch or select for the new data. Using indexes--for example, a primary key on Oracle tables--greatly speeds up performance, and this issue is no longer a concern.
ObjectPAL and SQL
With ObjectPAL, you can establish a connection to an SQL server. You can use TCursors and Table variables almost exactly the same way you do with local tables. You can embed SQL code in your ObjectPAL code; this includes any server-specific extensions. For example, you can have ObjectPAL, QBE, SQL, and Oracle's PL/SQL all in one event. ObjectPAL also supports the use of transactions. You can even trap for errors.
Connecting via an Existing Alias
To connect to an existing alias, use the Database open event. The syntax for the Database open event is as follows:
open ( const aliasName String ) Logical
The following is a simple example:
1: ;Button :: pushButton
2: method pushButton(var eventInfo Event)
3: var
4: dbSQL Database
5: endVar
6:
7: dbSQL.open(":Godzilla2:")
8: endMethod
In the preceding example, the user is prompted to enter his or her password. If you know the user's password you can use it in ObjectPAL so that the user doesn't have to enter it. To enter the password for the user, use the following syntax:
open ( [ const aliasName String,] [ const ses Session, ] [ const parms DynArray ] ) Logical
The following is another simple example of using the Database open event passing the alias a username and password:
1: ;Button :: pushButton
2: method pushButton(var eventInfo Event)
3: var
4: dbSQLDatabase
5: dynAlias DynArray[] AnyType
6: endVar
7:
8: dynAlias["USER NAME"] = "guest"
9: dynAlias["PASSWORD"] = "guest"
10: dbSQL.open(":Godzilla2:", dynAlias)
11: endMethod
Connecting via a New Alias
The following example demonstrates connecting to an Oracle server without a preexisting alias:
Disconnecting is actually quite simple. As you do with so many other objects in ObjectPAL, you simply close it. Assuming that the SQL Database variable DbSQL from the preceding example is within scope, the following line of code closes the connection:
1: dbSQL.close()
SQL and TCursor and Table Variables
In the preceding example, you used a TableView variable to open up a TableView of an SQL table. The following code shows that you can use TCursor and Table variables with the same techniques with which you are already familiar. A cursor is a pointer to a row in an SQL answer set. Cursors are implemented in ObjectPAL as a TCursor object. This next example shows you how to copy a table from the server to a local hard drive:
1: ;Button :: pushButton
2: method pushButton(var eventInfo Event)
3: var
4: tc TCursor ;Declare a TCursor variable.
5: endVar
6:
7: tc.open(":Godzilla2:rspitz.customer") ;Open table on server.
8: tc.copy(":work:customer") ;Copy table.
9: tc.close() ;Close table.
10: endMethod
This example shows how to copy a table from your local hard drive to the server:
1: ;Button :: pushButton
2: method pushButton(var eventInfo Event)
3: var
4: tc TCursor
5: endVar
6:
7 errorTrapOnWarnings(Yes)
8: try
9: tc.open(":work:customer.db")
10: tc.copy(":godzilla2:rspitz.test")
11: tc.close()
12: onFail
13: errorShow()
14: endTry
15: endMethod
Note: Although TCursor and Table variables work with SQL data, for the sake of speed, using an SQL query is always better. Therefore, try to do what you want with a query first and then fall back to using a TCursor only when an SQL query is not possible.
Executing an SQL Query in ObjectPAL
The executeSQL() method works just like the Query equivalent executeQBE(). After you define the SQL variable, then you execute it using executeSQL(). The following example shows how to execute an existing SQL file. In ObjectPAL, the technique is similar to executing an existing QBE file.
1: ;Button :: pushButton
2: method pushButton(var eventInfo Event)
3: var
4: sqlEXESQL SQL
5: tvAnswer TableView
6: endVar
7:
8: sqlEXESQL.readFromFile(":WORK:EXECSQL.SQL")
9: sqlEXESQL.executeSQL(db, ":PRIV:ANSWER.DB")
10:
11: tvAnswer.open(":PRIV:ANSWER.DB")
12: endMethod
You assign an SQL variable to an SQL string in any of the following three ways: embedding SQL in ObjectPAL, reading it in from a string, or reading it in from a file. You used readFromFile() previously to read in an SQL file and execute it. The next three examples use embedded SQL, embedded SQL with a tilde variable, and readFromString() to assign an SQL variable an SQL statement and then use executeSQL() to execute it.
Embedded SQL
The capability to place SQL commands within another programming language enables you to extend that language. This capability is called embedding SQL, and it means that you can actually embed SQL commands inside ObjectPAL. The following example shows how to embed SQL statements into your ObjectPAL code. Again, just as you can embed query code, you can embed ObjectPAL code.
1: ;Button :: pushButton
2: method pushButton(var eventInfo Event)
3: var
4: sqlVar SQL
5: tvLike TableView
6: endVar
7:
8: ;Define SQL variable.
9: sqlVar = SQL
10:
11: select * from ksmith.customer
12:
13: endSQL
14:
15: ;Execute SQL variable.
16: executeSQL(db, sqlVar, ":PRIV:__LIKE")
17:
18: ;View answer table.
19: tvLike.open(":PRIV:__LIKE")
20: endMethod
The next SQL query example demonstrates how to use a tilde variable to pass an SQL statement some data. This process is just like passing a tilde variable to an embedded query.
1: ;Button :: pushButton
2: method pushButton(var eventInfo Event)
3: var
4: sqlVar SQL
5: tvLike TableView
6: s String
7: endVar
8:
9: s = "ksmith.customer"
10: s.view("Enter SQL table name")
11:
12: sqlVar = SQL
13:
14: select * from ~s
15:
16: endSQL
17:
18: executeSQL(db, sqlVar, ":PRIV:__LIKE")
19: tvLike.open(":PRIV:__LIKE")
20: endMethod
This final SQL query example demonstrates how to use an SQL string. This process is just like using a query string.
1: ;Button :: pushButton
2: method pushButton(var eventInfo Event)
3: var
4: s String ;Declare a String variable.
5: sTilde String
6: sqlVar SQL ;Declare an SQL variable
7: tv TableView ;Declare a TableView variable.
8: endVar
9:
10: ;Assign String values.
11: sTilde = "Dive"
12:
13: s = "SELECT LAST_NAME, FIRST_NAME, COMPANY, PHONE " +
14: "FROM CONTACTS " +
15: "WHERE COMPANY LIKE �%" + sTilde + "%'"
16:
17: ;Read String values into an SQL variable.
18: sqlVar.readFromString(s)
19:
20: ;Execute SQL statement.
21: sqlVar.executeSQL(db, ":PRIV:ANSWER.DB")
22:
23: ;Open answer table.
24: tv.open(":PRIV:ANSWER.DB")
25: endMethod
Transactions: The Protocol for Communication
Client applications such as Paradox communicate with SQL data base servers with a unit of work called a transaction. Although a transaction is perceived as a single operation, a transaction may consist of a number of operations. For example, a single transaction could update multiple records. If you want to have some control over undeleting changes to your data, then use the following methods and procedures:
beginTransaction() Starts a transaction on a server
commitTransaction() Commits all changes within a transaction
rollbackTransaction() Rolls back all changes within a transaction (undo feature)
Commit and Rollback Overview
The term commit is part of a concept for entering data. The idea is to enter data into a temporary table and commit, or copy, the data to a main table. The term rollback is also part of a concept for entering data. The idea is to enter data into a temporary table with the opportunity to empty the temporary table and to leave the main table untouched. The term two phase commits applies this idea over a wide area network. With two phase commits, you can have data entry from anywhere in the world.
In the following example, the first record 1001 is posted to the table and then undone with the rollback:
1: ;Button :: pushButton
2: method pushButton(var eventInfo Event)
3: var
4: dbSQL Database
5: tc TCursor
6: endVar
7:
8: dbSQL.open(":Server1:")
9: dbSQL.beginTransaction()
10: tc.open(":Server1:guest.customer")
11: tc.insertRecord()
12: tc.(1) = 1001
13: tc.(2) = "Mike Prestwood"
14: tc.postRecord()
15: dbSQL.rollbackTransaction() ;Un-inserts record 1001.
16: dbSQL.begintransaction()
17: tc.(1) = 1002
18: tc.(2) = "Lisa Prestwood"
19: dbSQL.commitTransaction() ;Commits record 1002.
20: tc.close()
21: endMethod
Statement Atomicity
In ObjectPAL, all database commands are committed immediately. For example, a single QBE query, a single ObjectPAL table append, and a single edit operation are examples of commands that are executed and committed to the database immediately.
Views
A view is a virtual table that represents a part of the database or the whole database. The following is a list of various types of views:
A vertical subset of columns from a single relation. This type of view limits the columns displayed.
A horizontal subset of records from a single relation. This type of view limits the records displayed.
A combined vertical and horizontal subset of records from a single relation. This type of view limits the number of records displayed.
A subset of records from many relations. This type of view usually performs a join operation.
You can use a view to ensure security and for convenience; you can allow users to see only columns or values as necessary. Views can also enable users to see the bigger picture; for example, you can simplify a complex join into a manageable package. You create a view using the SQL command create view. The syntax is as follows:
create view [owner] [view name]
[(column name[, column name...])
as SELECT statement
Triggers and Stored Procedures
Triggers are procedures stored in the database that help you enforce both business and data relationship rules. With a trigger, you can keep summary data up to date, take actions based on what data a user enters, and enforce business rules such as no out-of-state checks accepted.
A trigger can occur when you either update, insert, or delete values. A trigger is like a Paradox validity check in which you can put code. In a way, triggers are analogous to events in a form, and stored procedures are analogous to custom methods.
Creating and Using a Sybase Trigger
The following is a simple example of how you might create and use a trigger in Sybase to ensure data integrity. On the SQL side, you create an update trigger on a table's column.
1: create trigger updatetrigger on sqlTableName
2: for update as
3: if update(columnName)
4: begin
5: raiserror 30000 �Cannot change values in this. Changes have been discarded.'
6: rollback transaction
7: end
To raise or see the error in Paradox, use the error event. For example,
1: ;tableframe :: error
2: method error(var eventInfo ErrorEvent)
3: if eventinfo.reason() = ErrorWarning then
4: eventinfo.setReason(ErrorCritical)
5: endIf
6: endMethod
Or you can use errorTrapOnWarnings(Yes). This rudimentary example should help you get started using triggers.
Summary
This chapter introduced you to Paradox as a client/server development tool. You also reviewed what a user can do interactively with Paradox and how to use ObjectPAL with SQL servers. Then you got started using SQL. Now that you are familiar with ANSI SQL and because SQL is common to many database tools, you can begin to query many types of databases with many different types of tools.
Paradox
Print This
Tags:
