|
SQLWindows is an object-oriented development tool for Microsoft Windows that is most easily distinguished from its competition by its unique programming interface. SQLWindows Application Language, or SAL, uses an outline structure that is unlike any
tool of its kind. The application outline represents the hierarchy of constants, variables, and objects within the application by placing them at different levels of indentation within the outline structure. At each level, the outline can be expanded or
collapsed, in order to view details or hide levels of detail when navigating to a particular section.
In addition to a unique language and programming structure, SQLWindows provides several means of communicating with an ODBC data source, both programmatically and through bound controls. This appendix focuses on these aspects of the product, including
QuickObjects and the SAL programming interface to ODBC. In addition, a brief overview of the object-oriented features of SQLWindows is provided.
SQLWindows can only connect using the Q+E drivers supplied with the product. These drivers should be installed with SQLWindows and the SQLBase desktop database engine. You can use Quest, the interactive querying, reporting, and data source configuration
tool included with SQLWindows, to configure a new ODBC data source and ensure that it is accessible by SQLWindows.
After launching Quest, select ODBC from the Utilities menu. The dialog box displayed is the Data Sources dialog box of the ODBC administration program. You can use this dialog box to define a new data source in ODBC.INI, and data sources defined here
will be available to all applications.
Next, add the data source to the Quest desktop by selecting Add from the Database option of the Utilities menu. The Add Database dialog box will present a list of defined ODBC data sources. Click on the name of the data source to be added, and enter a
user ID and password in the appropriate fields. When adding an Oracle data source to the list of Quest databases, the dialog box might appear as in Figure B.1.
Figure B.1. The Quest Add Database dialog box.
Click on OK to add the selected data source to Quest's list. You can use Quest to create and save queries and reports and to view catalog information, such as table definitions. Try viewing a small table to test the connection. If data can be accessed
through Quest, it can be accessed through SQLWindows QuickObjects and SAL SQL functions as well.
The easiest way to communicate with a database through SQLWindows is to use QuickObjects. There are three main categories of QuickObjects: visualizers, commanders, and the data source. Visualizers and commanders are data-aware controls that tie a window
control to values stored in the database. A data source is used to connect to the database and provide data to bound controls. To place a data source on the form, click on the Quest icon on the tool palette, and select cQuickDatabase as the
class. The object should appear on the form as in Figure B.2.
Figure B.2. A cQuickDatabase object that will be used as a data source.
After placing the new data source on the form, click on table or query to choose the means by which it will be populated. If a query is selected, a new query can be defined, or a file containing the pre-written SQL can be identified. Alternatively, a
cQuickTable object can be used as the data source. The primary difference between a cQuickTable and a cQuickDatabase object that is using a table as its data source is the default display format. A cQuickTable defaults to table display type, while a
cQuickDatabase object defaults to the VCR button format, which displays a single record at a time.
SQLWindows refers to bound controls that display data as Visualizers. After defining the result set for the data source, Visualizers can be placed on the form and bound to columns in the data source. The simplest of the Visualizers is the data
field. To create a bound data field, select the data field icon on the tool palette and select cQuickField as the class. When cQuickField is clicked in the class window of the tool palette, a drop-down list box appears in the lower portion of the tool
palette. Select the name of the data source from the drop-down list, and in the list of columns below, select the name of the column to which the data field will be bound. Other controls can be used as Visualizers. Radio buttons or drop-down list boxes can
be bound to description columns of lookup tables, check boxes can be bound to columns containing Boolean values, and list boxes and multi-line edits can be bound to columns to display data.
Commanders, in SQLWindows terminology, refer to pushbutton classes of QuickObjects that perform operations on a data source. To create a new Commander, click on the pushbutton icon on the tool palette, select the data source name from the drop-down
list, and select from the list of Commander types. This creates an object of type cQuickCommander. These include First, Last, Next, Prev, and Retrieve. The first four are used to position the record pointer, and the last is used to fetch the row at the
current record position.
The additional methods New, Apply, Delete, and Discard can be used to edit the record set. As is typically the case with bound controls, it is probably not advisable to use these methods when accessing a remote database through ODBC. The lack of control
over the transaction and SQL that is prepared adds a considerable amount of risk to this method of applying transactions.
The SAL programming language provides functions for communicating with the database that are very similar to the underlying ODBC API. In addition, it provides numerous system variables and abstract data types to make the API easier to use. Connecting to
the database, for example, requires that three system variables be set, followed by a single function call (SqlConnect). The system variables SqlDatabase, SqlUser, and SqlPassword must be set to the name of the ODBC data source, the user ID, and the
password respectively. SqlConnect takes a single argument, the buffer that will receive the new connection handle if the function succeeds. SqlConnect returns TRUE if it succeeds, FALSE if it fails. The example in listing B.1 uses SAL to establish a
database connection by taking values from a login form and an initialization file to connect to the data source.
Listing B.1. This code fragment uses SAL to connect to an ODBC data source.
Contents
Pushbutton:pbOK
Message Actions
On SAM_Click
iLen = SalGetProfileString("ORACLE TEST", "DSN",
"ORACLE", szTmpBuf, "TEST.INI")
Set SqlDatabase = SalStrLeft(szTmpBuf, iLen)
Set SqlUser = dfUser
Set SqlPassword = dfPassword
bSuccess = SqlConnect(hSQLMain)
If (bSuccess = FALSE)
Call SalMessageBox("Unable To Connect.",
"Oracle Test", 0)
Window Variables
Sql Handle: hSQLMain
Boolean: bSuccess
Note that the parameter passed to SqlConnect is of type SqlHandle. This is an abstract data type used in SAL exclusively for database communications.
After connecting to the data source, SQL can be processed in a number of different ways. SAL provides methods for prepared and immediate execution of SELECT statements, as well as INSERT, UPDATE, and DELETE. The implementation of immediate execution in
SAL is actually just a shortcut to using prepared execution. This point will be illustrated with the following examples. SQLWindows provides several alternative methods for retrieving a result set.
The most verbose of these options is to first call SqlPrepare(), followed by SqlExecute() and SqlFetch() to retrieve the first row. The code fragment in listing B.2 illustrates the use of these methods.
Listing B.2. This example retrieves a result set.
Contents
Pushbutton:pbRefresh
Message Actions
On SAM_Click
Set szSQL = "SELECT Last_name, First_name
FROM TEMP INTO :szLast, :szFirst".
Set bSuccess = SqlPrepare(hSQLMain, szSQL)
If (bSuccess = TRUE)
Set bSuccess = SqlExecute(hSQLMain)
If (bSuccess = TRUE)
Set bSuccess = SqlFetchNext(hSQLMain, iRet)
Window Variables
Sql Handle: hSQLMain
Boolean: bSuccess
String: szSQL
Number: iRet
Note that the SELECT statement must provide parameters to receive column values.
If columns are not bound when the statement is prepared, the application will not be able to retrieve the values at run time. The second parameter to SqlFetchNext() is of some interest as well. It is used to receive a code indicating the current state
of the row that was just fetched. This code has four possible values:
- FETCH_Delete—indicates that the row has been deleted since the cursor was built
- FETCH_EOF—indicates that the end of the cursor has been reached
- FETCH_Ok—indicates that the record has not been modified
- FETCH_Update—indicates that the record has been modified since the cursor was created
This value may not be entirely accurate depending on the transaction isolation level. For example, if the transaction isolation level is set to read-only, this value would be either FETCH_Ok, or FETCH_EOF because the snapshot would not be refreshed
after the cursor was created. In most database environments, transaction isolation should be set to read-only, if possible, as this reduces contention and improves performance. Unless an application has a specific need that requires in-place updates or
similar cursor operations, this level of isolation should be acceptable.
A possible alternative to the methods demonstrated in listing B.2 is to combine the SqlPrepare() and SqlExecute() function calls with a single call to SqlPrepareAndExecute(), as illustrated following:
Set bSuccess = SqlPrepareAndExecute(hSQLMain, szSQL)
If (bSuccess = TRUE)
Set bSuccess = SqlFetchNext(hSQLMain, iRet)
The operation can be simplified further with a call to SqlImmediate():
Set bSuccess = SqlImmediate(szSQL)
After making the call to SqlImmediate(), the first row is already bound to application variables. Subsequent calls to SqlFetchNext() will begin with the second row in the result set. Unfortunately, the first time SqlImmediate() is called, it calls
SqlConnect() with a handle that it manages internally. For this reason, it can be somewhat slow the first time it is called. However, each subsequent time it calls only the remaining three functions, SqlPrepare(), SqlExecute(), and SqlFetchNext(). It can
also be used with transaction SQL, in which case, it does not make the call to SqlFetchNext().
Transaction-based SQL can be processed using the same functions, in a very similar manner. Listing B.3 is an example of a transaction that inserts a record based on values in the data fields of a form.
Listing B.3. This code fragment inserts rows based on the values in the data fields of a form and demonstrates transaction control in SQLWindows.
Contents
Pushbutton:pbRefresh
Message Actions
On SAM_Click
Set szSQL1 = "SELECT individual_ids.nextval
INTO :iNewID FROM dual"
Set bSuccess = SqlImmediate(szSQL1)
Set szSQL2 = "INSERT INTO individual VALUES (
:iNewID, :dfLast, :dfFirst, :dfNotes)"
Set szSQL3 = "INSERT INTO phone VALUES (
:iNewID, :dfType, :dfNumber)"
If (bSuccess = TRUE)
Set bSuccess = SqlPrepareAndExecute(hSQLMain,
szSQL2)
If (bSuccess = FALSE)
Call SqlPrepareAndExecute(hSQLMain, 'ROLLBACK')
Else
Set bSuccess = SqlPrepareAndExecute(szSQL3)
If (bSuccess = FALSE)
Call SqlPrepareAndExecute(hSQLMain, 'ROLLBACK')
Else
Call SqlCommit(hSQLMain)
Window Variables
Sql Handle: hSQLMain
Boolean: bSuccess
String: szSQL1
String: szSQL2
String: szSQL3
Number: iRet
In the example, if either insert fails, the entire transaction is rolled back. Note the somewhat odd way in which commits and rollbacks are handled by SAL. SqlCommit() is a function, while no corresponding function exists for a rollback. The rollback
must be accomplished through another call to SqlPrepareAndExecute().
Although SQLWindows has no functions specifically designed to support stored procedures, the ODBC syntax for Oracle can be used to execute a stored procedure using the methods previously described. For example, instead of the separate
SqlPrepareAndExecute() statements in listing B.4, a single statement could be used to execute a stored procedure, as illustrated following:
Set szSQL2 = "{call insert_indiv_phone(dfLast, :dfFirst, :dfNotes
, :dfType, :dfNumber)}"
Set bSuccess = SqlPrepareAndExecute(hSQLMain, szSQL2)
This example assumes that the stored procedure is using the sequence to get the new id, and it is handling commits and rollbacks internally.
The methods that have been described for communicating with the database from SQLWindows are a subset of a larger API. Additional functions include methods for scrolling cursors backwards, or to an absolute row position, naming transactions and cursors,
and determining the number of rows in a result set before they are fetched.
Unfortunately, some of the more useful functions apply only to SQLBase connections. For example, the SqlError() and SqlGetErrorText() functions can be used to retrieve the error number, description, and remedy for the database error that occurred most
recently. However, these values are not received from the driver, but read from a text file that ships with the product, containing only SQLBase error codes, messages, and remedies. Despite a few SQLBase-only features, the methods provided by SAL are
fairly comprehensive, and they will prove adequate for most client-server development projects.
In addition to extensive support for database applications, the object-oriented features of SQLWindows are numerous, although somewhat unorthodox. It supports inheritance, multiple inheritance, and the ability to define classes based on visual and
non-visual objects. One of the more interesting features of the SQLWindows model of inheritance is the concept of class variables. SAL distinguishes these from instance variables, which are treated as normal class members. No matter how many instances of
an object are created, they share the same class variables. In other words, class variables are global to the class. This implementation can be somewhat useful. For example, a database connection class could have a global counter that stores the number of
active connections. Unfortunately, this counter would be somewhat tedious to maintain, because for non-visual classes, constructors and destructors do not exist. The code that creates a new instance of a non-visual object is responsible for calling member
functions used to initialize the instance. By the same token, the code that destroys a non-visual object is responsible for calling any clean-up routines.
In fact, the concept of constructors and destructors is not really a part of visual objects either. Visual objects simply have the ability to respond to the Windows messages WM_CREATE and WM_DESTROY, which are represented by SAL as SAM_Create and
SAM_Destroy. By defining Message Actions to respond to these messages, the rough equivalents of constructors and destructors can be created.
Another feature of SAL that seems somewhat at odds with the object-oriented model is the fact that all data members and member functions are public (except for variables declared within functions). It would also seem that the programmer should be able
to declare variables within Message Actions, which are the closest thing to private member functions within SAL. The fact that variables cannot be defined in event-handlers should be kept in mind when designing SQLWindows applications, so you can attempt
to keep the number of instance variables (or Window Variables) to a reasonable level.
Despite a few shortcomings in the object model employed by SQLWindows, the SAL programming language has many powerful features. Perhaps the most useful of these is the ability to create and respond to user-defined messages. A message can be defined as a
global constant in the User section and sent to forms and controls using the SALSendMessage() function. To respond to a user-defined message, a window or control need only add the message and code to handle the message in its Message Actions section. The
way in which event-handlers are defined in SAL maps into the actual Windows message very well, which allows SQLWindows objects more flexibility in the messages it can respond to.
SAL also has very good support for common Windows APIs, including DDE, OLE, and (through QuickObjects), MAPI. You can also use QuickObjects to interface with LotusNotes and NetWare mail, making it very easy to build messaging capabilities into
SQLWindows applications. The outline editor and a few unorthodox features of SAL take some getting used to, but overall, SQLWindows is a serious client-server development tool. It is particularly well-suited to rapid prototyping, through the use of
QuickObjects.
| 
