 |
The Local InterBase Server comes with a single user license. It will not allow more than three simultaneous connections and is not appropriate for a production environment. |
by Paul Mahar
The Professional and Client/Server editions of IntraBuilder include native links to high-performance data servers. In this chapter, you learn how easy it is to create applications that bring server data to the Web. If you are using the standard edition of IntraBuilder, you can take the day off or read through the chapter to get an overview of the other editions. The Standard edition restricts data connections to dBASE, Paradox, and ODBC data sources. Here is the final topic list for the week:
| NOTE |
Although the examples used throughout this chapter use InterBase as the database server, the same techniques also apply to any servers connected through a native BDE SQL-Link. The Client/Server edition comes with SQL-Link drivers for Oracle, Informix, InterBase, DB2, Sybase, and Microsoft SQL Server. |
The term client/server was coined for database applications that split data processing between a server, where data resides, and the client, or user, machine. Today, the term also applies to Web applications where the client is the browser and the server is a Web server that serves up HTML pages. To add to the confusion, applications such as IntraBuilder are both clients and servers. IntraBuilder is a server in the Web sense and a client in the database model. The Client/Server in IntraBuilder Client/Server refers to the database model. All editions of IntraBuilder support the browser clients and Web servers.
Although the promises of the client/server architecture were starting to be trumpeted to the PC world in the late 1980s, it took years for client/server applications to become mainstream. One of the difficulties that client/server developers have continually encountered has been the complications and expense of configuring database drivers on client workstations.
Both the expense and configuration management of client workstations are nonexistent in an IntraBuilder application. The requirements for the client are nothing more than a Web browser. Database drivers need only reside on machines running the IntraBuilder Server. By the end of the day you will be ready to leverage the Web as the most affordable platform for client/server applications.
Before you can create a client/server application, you should be familiar with a database server and some front-end development tools. For the past 20 days, you have been familiarizing yourself with the front-end development tools of IntraBuilder. If you already have a database server installed and are comfortable with its administration, you can skip to the next section. In this section, you will learn how to set up and administer the Local InterBase Server.
You can find the setup program for the Local InterBase Server on the IntraBuilder Client/Server or the IntraBuilder Professional CD. The Local InterBase Server is not included with trial or standard versions of IntraBuilder. To work with IntraBuilder, you need only the Program Files and InterBase Windows Tools. I recommend that you also select the InterBase Help, InterBase SQL Tutorial, and the example files when installing. If you want to save disk space, do not install the ODBC driver, the command-line tools, or SDK support. You can select what components to install from the setup dialog, shown in Figure 21.1.
Figure 21.1 : Installing the Local InterBase Server from the IntraBuilder Client/Server CD.
As with other database servers, IntraBuilder can use either ODBC or a SQL-Link to connect to InterBase. The SQL-Link provides superior performance and requires less overhead than the ODBC driver.
When you first install InterBase, the default configuration is
to have InterBase start up when you start Windows. Loading InterBase
at startup is appropriate when using InterBase Server for NT in
a production environment, but consider switching to manual startup
for the Local InterBase Server.
|
The Local InterBase Server comes with a single user license. It will not allow more than three simultaneous connections and is not appropriate for a production environment. |
The InterBase tool set consists of the server and four utility applications. The utilities are the Communication Diagnostic Tool, the Configuration dialog, the Server Manager, and Interactive SQL, which are described in the following sections. You can start each tool from an icon in the InterBase program folder. Most of the tools require a running server to be fully functional. Several of the tools have menu options to launch other tools.
Use this tool to check that you can make a valid connection to a database. The InterBase Server must be running to complete the DB connection test. To validate a new install, start the InterBase Server and test a connection to the employee.gdb example database, as shown in Figure 21.2. The default User Name for employee.gdb is SYSDBA, and the password is masterkey. The password is case sensitive and must be entered in lowercase.
Figure 21.2 : Testing a DB connection to the Employee example database.
The DB connection test is the first test to try when you have trouble accessing an InterBase database. The test uses direct calls to the server. If the DB connection test fails, the problem resides with the InterBase Server, the GDB file, the user, or the password.
If the DB connection passes and you are unable to connect using IntraBuilder, check the BDE configuration. IntraBuilder requires a BDE InterBase driver and alias to connect to an InterBase database.
You can open the InterBase Configuration dialog from a program icon or by selecting Startup Configuration from the server shortcut menu. The options presented depend on the current operating system. Figure 21.3 shows the dialog as it appears in Windows 95.
Figure 21.3 : Configuring the server for manual startup.
If you are running Windows NT, you can set up the server as an NT service and set a priority. The optimum configuration for the InterBase Server for NT is to be a service. If you are using the Local InterBase Server, run the server as an application with a manual startup.
You can start up the server by selecting InterBase Server in the InterBase group. When the server is running, an icon appears in the status area of the task bar. You can open the server property sheet by right-clicking the icon. The property sheet displays the user license count and connection information. Figure 21.4 shows the server property sheet. If you are having trouble making a connection with the Local InterBase Server, check this sheet to see whether the connections have reached the maximum of three.
Figure 21.4 : Checking the number of server connections.
You can use this utility to perform routine maintenance and administration tasks. Use this utility to manage users, generate statistics, and optimize your databases. Before you can do anything with the Server Manager, start up the server and log in as the system administrator. The system administrator account has the user name SYSDBA, and the password defaults to masterkey. After you log in to the server, you can open multiple databases without reentering a user name or password.
InterBase requires a user name and password whenever you open a database through IntraBuilder. You can force a user name and password using the loginString property of a Database object. If you do not set the loginString property, IntraBuilder uses the pass_sql JavaScript form to get the information from the user.
You can create user accounts by selecting Tasks|User Security from the Server Manager. After creating accounts, you can use the owner account of each database to grant access rights. Any user can open a database, but only those with granted rights can read data from tables within the database.
If you are having trouble with a single database, you can verify the integrity of the file from the Server Manager. Figure 21.5 shows the validation dialog while selecting the knowledge base solution GDB file that comes with IntraBuilder Client/Server and IntraBuilder Professional.
Figure 21.5 : Validating the structure of a GDB file.
The Database Sweep optimizes database files in much the same way that the Database::pack() method can optimize Paradox and dBASE tables. When you sweep a database, the server recovers space occupied by changes that were rolled back. You do not need to take a database offline to perform a sweep.
The backup and restore options can help you move a database between
operating systems. The InterBase backup format is transportable
and takes up less space than the standard GDB format.
| TIP |
You can also make routine backups using any standard backup utility such as Cheyenne Backup. All the objects of any one database are contained in a single GDB file. The same GDB file will work with the single-user Local InterBase Server and the multi-user InterBase Server for NT. |
You can open the Interactive SQL utility from the Server Manager. If you select a database in the Server Manager before opening the Interactive SQL utility, it opens with a new connection to the selected database.
The last utility works like a Script Pad for InterBase. You can enter SQL commands in a text area control, and view results appear in another. The Interactive SQL utility also lets you view metadata. You can use Interactive SQL to test SQL statements before embedding them in an IntraBuilder Database::executeSQL() method. Figure 21.6 shows the results of running a simple SELECT command.
Figure 21.6 : Using Interactive SQL to try a SELECT command.
 |
Metadata refers to information that InterBase keeps in system tables. This includes index definitions, stored procedures, triggers, and generators. Data that you enter into standard tables is not metadata. |
The metadata options provide the complete structure of a database. You can create a quick reference by saving the metadata for a database to a text file. This can come in handy when you need the name of an index or stored procedure for a JavaScript method.
The View option works better for getting metadata from complex
database structures. The View option helps you pinpoint specific
objects. Extract options are more effective for simple structures
such as the knowledge base GDB.
|
The InterBase utilities do not share connections. If you open two databases with the Server Manager and load the Interactive SQL, you will create three active connections. When using the Local InterBase Server with more than one database or utility, you can quickly run out of connections. |
In this section, you will learn how to replace the shopping cart application's dBASE tables with an InterBase database. The shopping cart is a Web bookstore that allows shoppers to search for and purchase books through a browser. The application consists of eight JavaScript forms-one custom form class, one report, and six tables. Although the modifications are simple, you will need to make changes to the custom form class, the report, and all but one of the JavaScript forms.
Before you modify any of the JavaScript files, you need to get all the data into an InterBase database. InterBase, BDE, and IntraBuilder provide all the tools you need to replicate the data on the server.
The goal is to have an InterBase database that contains the same
six tables that are currently in dBASE format. Let's call the
new database STORE and name the InterBase file store.gdb.
There is a three-step process for migrating the tables. First,
you must create the store.gdb file using the InterBase
Interactive SQL utility. After creating the database file, you
can define a BDE alias for the gdb. The last step is
to pump the data from the dBASE tables to the InterBase database.
| NOTE |
If you have C++ Builder or Visual dBASE Client/Server, you can also use the Data Pump Expert to copy dBASE tables into an existing InterBase database. The Data Pump Expert is not provided with IntraBuilder because this functionality is built directly into the UpdateSet class. |
You can use the InterBase Interactive SQL utility to create the store.gdb file. When you create a new database, InterBase creates a 200KB file containing system tables for storing metadata. IntraBuilder applications do not require direct references to the physical location of a GDB file. In this exercise, you place the file in the store folder along with the other JavaScript forms. Here are the steps for creating the store database:
|
It is a little too easy to permanently erase a database by selecting File|Drop Database from the InterBase Interactive SQL menu. |
Without a BDE alias, the database is invisible to IntraBuilder. To create an alias for an InterBase database, you must have the InterBase SQL-Link installed. If INTRBASE does not appear as a BDE driver, you can install the SQL-Link from either the IntraBuilder Professional or the IntraBuilder Client/Server setup program. The setup program's Custom selection allows you to install a single SQL-Link without reinstalling any other components. The BDE InterBase driver is not an option of the Local InterBase Server setup program.
If you have the InterBase SQL-Link installed, the following steps can be used to create an alias for the Store database. The Local InterBase Server does not need to be running to complete this process.
| TIP |
If you are using any 16-bit BDE applications, you can optimize BDE by not checking the Configure Win3.1 menu option. When this option is checked, the BDE utility saves driver information to the CFG file instead of the Windows Registry. Alias definitions are always saved to a CFG file. |
IntraBuilder contains an UpdateSet class for moving data between different table types. After establishing a link to the Store database, you copy the dBASE tables to InterBase through the UpdateSet::copy() method. Although this method will copy table structures and data, it will not re-create index definitions on the server.
dBASE tables follow an index scheme that is incompatible with most servers. When working with dBASE tables, the index is tied to the table and not to a database. dBASE index names need to be unique only to the table. The most common index name for a dBASE index is the name of the field. For instance, the index for the ISBN field in the Cart table is named ISBN. The same index name is also found on the Title table.
InterBase requires every index to have a name that is unique to the database. To create unique index names for InterBase, you can include the table name as part of the index name. Using this convention, CARTISBN is the name of the Cart table's ISBN index, and TITLEISBN is the Title table's ISBN index.
You can use the TableDef class and the Database::createIndex() method to gather index definitions from the dBASE tables and create a new index for the InterBase database. To automate the process, you can create a script that does the job for you.
Before committing any thoughts to JavaScript, make sure you can get a connection from IntraBuilder to the Store database. Here are the steps:
After making a connection, you see either a single (Untitled) icon or many tables that begin with RDB$. InterBase uses the RDB$ prefix for system tables. Avoid making changes directly to any system table from the IntraBuilder Explorer. You can control the display of system tables from the application property sheet of the Desktop Properties dialog.
Create a new script, as shown in Listing 21.1. Save the script as pumpup.js. Run the script to upload the six dBASE tables into the Store database. You might need to modify the #DEFINE statement to refer to the location of the dBASE tables on your system.
Listing 21.1. The Pumpup script.
1: #DEFINE TABLEFOLDER " C:\\PROGRAM FILES\\BORLAND\\INTRABUILDER\\STORE\\"
2:
3: var db = new Database() ;
4: db.databaseName = "STORE" ;
5: db.loginString = "sysdba/masterkey" ;
6: db.active = true ;
7:
8: CopyUp(db, "CART") ;
9: CopyUp(db, "CUSTOMER") ;
10: CopyUp(db, "INVOICE") ;
11: CopyUp(db, "LINEITEM") ;
12: CopyUp(db, "TITLE") ;
13: CopyUp(db, "PRODUCT") ;
14: return ;
15:
16: function CopyUp( db, cTable )
17: {
18: var up = new UpdateSet() ;
19: up.source = TABLEFOLDER + cTable + ".DBF" ;
20: up.destination = ":STORE:" + cTable ;
21: up.copy() ;
22
23: var tSource = new TableDef() ;
24: tSource.database = _sys.databases[0] ;
25: tSource.tableName = up.source ;
26: for (var i = 0 ; i < tSource.indexes.length ; i++ )
27: {
28: var idx = new Index() ;
29: idx.fields = tSource.indexes[i].indexName ;
30: idx.indexName = cTable + idx.fields ;
31: db.createIndex( cTable, idx ) ;
32: }
33: }
The Pumpup script starts on lines 3 through 7 by establishing a connection to the Store database. The db variable contains a reference to the Store database. The reference is passed to the
CopyUp() function along with the name of the table to copy. The parameters for CopyUp() are as follows:
CopyUp( <object reference>: database to copy to>, <expC: table to copy> )
| NOTE |
You might need to modify the first line of the Pumpup script before running it on your system. Verify that the TABLEFOLDER string points to the location of the dBASE tables for the shopping cart application. |
Lines 8 through 13 call CopyUp() for the shopping cart application's six dBASE tables. The script ends with the return statement on line 14 after all the tables have been copied to the Store database. The return statement is optional. Without a return, the script ends after executing the last statement outside a function or class block. In this case, the return is the last statement outside a block.
The CopyUp() function has two sections. Lines 18 through 21 copy the table to the server. Lines 23 through 31 comprise the second section. The purpose of the second section is to create index definitions on the server based on existing dBASE index tags.
Unlike other database classes, UpdateSet is not tied to any single database reference. The source and destination properties can point to different databases. UpdateSet selects the appropriate database by examining the string assigned to the source and destination properties. If a string does not begin with an alias name delimited by colons, the table is assumed to be a native BDE dBASE or Paradox table. Line 19 specifies a local dBASE table without supplying an alias.
The ":STORE:" prefix on line 20 requires that a connection be available to the Store database. When the destination property is set, the UpdateSet object establishes a link to the database object created on lines 3 through 6 without making an explicit reference property. An error would occur if the database was not already active.
Lines 23 through 25 create a TableDef object that contains table structure and index definitions for the dBASE table. Line 24 sets the database property to the default system database. This prevents errors that could occur when the current database does not support native dBASE and Paradox tables. Setting the tableName property activates the object and causes the rest of the structure to fill in with values related to the current table.
The for loop, on lines 26 through 32, executes once for each index tag defined on the dBASE table. The idx object defines an equivalent SQL index. Line 30 concatenates the table name to the dBASE index name to ensure an index name that is unique to the Store database.
After running the Pumpup script, open the Title table from the IntraBuilder Explorer to view the InterBase version. Figure 21.11 shows one of the rows for the Title table. The character, memo, and image fields display just as they did in the dBASE tables. The date and numeric fields appear to have different values than the dBASE counterparts. The Pubdate field includes time information. Although no information was lost, you will need to explicitly suppress the time portion of the value when using it in a form or report.
Figure 21.11 : Running the InterBase version of the Title table.
The changes to the Price field look more disturbing. The Price
field shows values rounded to the nearest whole number. Whereas
the price was 29.95 in the dBASE table, it displays as
30 in the InterBase table. Although it looks bad in the
Table run window, the true value is still in the table.
| TIP |
To edit decimal values in an InterBase table, use the Form Expert to quickly generate a form and set the template property for Text controls linked to numeric values. Changing the template property for the Price field to 999.99 lets you see and edit the values as they are stored in the table. |
With the database in place, you're ready to begin the task of updating the custom form class, the JavaScript forms, and the report that comprise the source code of the shopping cart application. To get the application up and running with the Store database, you need to modify only properties of the database objects. The underlying logic for each form remains the same as it was for dBASE tables.
Before you make any changes to the application, you need a working
copy of the source files as they were at Day 14. Copy the following
files into your working folder. The exercises assume that your
working folder is C:\Program Files\Borland\IntraBuilder\Store.
Table 21.1 lists the files you will need to complete the InterBase
version of the shopping cart application.
| File | Name | Purpose |
| cart.jfm | Cart Form | Shows contents of the shopping cart |
| checkout.fjm | Checkout Form | Data entry form for customer |
| detail.jfm | Detail Form | Shows information from Title table |
| help.jfm | Help Form | Describes toolbar buttons |
| index.htm | Store Index | Home page for the bookstore |
| keyword.jfm | Keyword Form | Provides complex search of Title table |
| logo.gif | Store Logo | Image map for Store Index |
| message.jfm | Message Form | Streams an HTML alert |
| quick.jfm | Quick Form | Provides simple search of Title table |
| receipt.jrp | Receipt Report | Displays when an order is committed |
| results.jfm | Results Form | Lists titles resulting from a search |
| store.gdb | Store Database | InterBase database containing Cart, Customer, Invoice, Lineitem, Product, and Title tables |
| store.h | Store Header | Precompiler file for including forms |
| toolbar.jcf | Toolbar | Custom form class used by all forms |
| vcr.gif | VCR Buttons | Image map for the Detail form |
Remove all dBASE tables from your working folder. This causes all the forms but the Message form to generate runtime errors as the forms try to open tables that do not exist. The Message form does not use any tables. When you complete the modifications, the runtime errors will disappear. If you leave the dBASE tables in the same folder, it becomes more difficult to determine what forms have been converted to work with InterBase.
Seven of the forms need a Database object to work with the Store database. You could add the Database object directly to each of the forms, but there is an easier way. All of the forms in the shopping cart application derive from the Toolbar custom form class. If you add the Database object to the Toolbar custom form class, all the other forms automatically inherit the new object.
You can quickly add a Database object through the Custom Form Class Designer. The other modifications you will make to the application will be through source code. Here are the steps for adding a Database object to the Toolbar custom form class:
When you add Data Access objects from the Component Palette, IntraBuilder assigns generic names like query1, query2, and database1. You can provide more intuitive names by replacing the references in the script file. If you right-click on a custom form class and select Edit as Script, you can use the Script Editor to locate and replace references. Open toolbar.jcf in the Script Editor and locate the following code:
with (this.database1 = new Database()){
left = 70;
top = 0;
databaseName = "STORE";
loginString = "SYSDBA/masterkey";
active = true;
}
Replace the database1 reference with dbstore, like this:
with (this.dbstore = new Database()){
left = 70;
top = 0;
databaseName = "STORE";
loginString = "SYSDBA/masterkey";
active = true;
}
Those are all the modifications that are required to give all the forms access to the Store database. The next task will be to modify the query definitions in each form.
You can use IntraBuilder's debugging tools to locate, fix, and optimize queries. To locate queries that need fixing, simply run the shopping cart application and watch for runtime errors. When a table cannot be found, IntraBuilder displays an alert, as shown in Figure 21.13. You can select Fix to open the form in the Script Editor near the line where the trouble is.
Figure 21.13 : Using runtime errors to locate queries that need to be modified.
To run the shopping cart application, you must first start with the Help form. Run the Help form and locate the first problem query. If you click on Fix, the Script Editor opens near the following code:
with (this.cart1 = new Query()){
left = 72;
top = 0;
sql = 'SELECT * FROM "cart.DBF"';
active = true;
}
with (this.cart1.rowset) {
indexName = "CARTUSER";
}
The following problems exist with this and all the queries in the shopping cart application:
The following shows the fixed sql, database, and indexName properties. These changes allow the form to work, but not as well as it could work.
with (this.cart1 = new Query()){
left = 72;
top = 0;
sql = 'SELECT * FROM CART';
database = parent.dbstore;
active = true;
}
with (this.cart1.rowset) {
indexName = "CARTCARTUSER";
}
Whenever you issue a SELECT against a server, the SQL-Link driver uses an ORDER BY clause to control the row sequence. IntraBuilder executes the SELECT as soon as the active property of the query is set to true. If you do not specify an ORDER BY, the SQL-Link driver adds one. Setting the indexName property forces IntraBuilder to reissue the SELECT command if the indexName calls for different order. You can avoid the overhead of multiple SELECT commands by substituting an ORDER BY clause and removing any explicit settings of the indexName property.
Setting the requestLive property to false can also help optimize queries that use SQL-Link drivers. If a rowset is live, you can update values in the rowset. By default, IntraBuilder tries to make every query result in a live rowset. When requestLive is set to false, IntraBuilder creates a read-only rowset. Queries that perform joins and aggregate operators can result in a read-only rowset, regardless of the requestLive property.
The Help form does not require a live rowset. To fully optimize the cart1 query for the Help form, modify the properties for the cart1 query and rowset as shown here:
with (this.cart1 = new Query()){
left = 72;
top = 0;
sql = 'SELECT * FROM CART ORDER BY CARTUSER';
database = parent.dbstore;
requestLive = false;
active = true;
}
with (this.cart1.rowset) {
}
The IntraBuilder Desktop Properties dialog contains two options that you can use to gather detailed information on what the SQL-Link driver is doing. The SQL Trace option causes all SQL-Link operations to echo to the results pane of the Script Pad. This lets you see the differences between the SELECT commands in your scripts and the SELECT commands that run on the server. The output of SQL Trace is often too voluminous to work with in the Script Pad. The Output Log option captures all output to the Script Pad in a text file. By combining the SQL Trace and Output Log options, you can create a text file that gives detailed information on how your application is working with the server.
The script shown in Listing 21.2 demonstrates the optimization that happens when you specify an ORDER BY clause rather than an indexName. Before running the script, select Properties|Desktop Properties to set the SQL Trace and Output Log options. The SQL Trace option is on the Application page. Figure 21.14 shows the Output Log set to sqltrace.txt.
Figure 21.14 : Enabling an Output Log.
|
The SQL Trace and Output Log options degrade system performance. Disable both options in a production environment. |
Listing 21.2. The SQLTrace script.
1: //
2: // To track and compare SQL operations,
3: // check SQL Trace and set an Output log
4: // file in the Desktop Properties dialog.
5: //
6:
7: clear ;
8: var db = new Database()
9: db.databaseName = "STORE" ;
10: db.loginString = "SYSDBA/masterkey" ;
11: db.active = true ;
12:
13: // Prep database
14: var q = new Query() ;
15: q.database = db ;
16: q.sql = "SELECT * FROM CART" ;
17: q.active = true ;
18:
19: TraceSelect(db, "SELECT * FROM CART") ;
20: TraceIndex( db, "SELECT * FROM CART") ;
21: TraceSelect(db, "SELECT * FROM CART ORDER BY ISBN") ;
22:
23: function TraceSelect(db, cSelect)
24: {
25: ShowStart(cSelect) ;
26: var q = new Query() ;
27: q.database = db ;
28: q.sql = cSelect ;
29: q.active = true ;
30: ShowEnd(cSelect) ;
31: }
32:
33: function TraceIndex(db, cSelect)
34: {
35: ShowStart(cSelect + " (with indexName setting)") ;
36: var q = new Query() ;
37: q.database = db ;
38: q.sql = cSelect ;
39: q.active = true ;
40: q.rowset.indexName = "CARTISBN" ;
41: ShowEnd(cSelect) ;
42: }
43:
44: function ShowStart(cSelect)
45: {
46: _sys.scriptOut.writeln() ;
47: _sys.scriptOut.writeln("// Start Trace of " + cSelect);
48: _sys.scriptOut.writeln() ;
49: }
50:
51: function ShowEnd(cSelect)
52: {
53: _sys.scriptOut.writeln() ;
54: _sys.scriptOut.writeln("// End Trace of " + cSelect);
55: _sys.scriptOut.writeln() ;
56: }
The SQLTrace script works with the SQL Trace and Output Log options to create a text file that shows the differences between three similar query operations. Two sections of code prepare IntraBuilder
for the comparison. Lines 8 through 11 define a database object that is used throughout the script. Lines 14 through 17 activate a query without adding any extra logging information.
The first query that activates for a database triggers dozens of extra lines of SQL Trace output. For this reason, the first active query is not comparable to subsequent query operations. Lines 19 through 21 call functions to create the queries that perform the following operations:
Lines 23 through 31 define the TraceSelect() function for tracing queries that do not set the indexName. The db and cSelect parameters refer to the active database and SELECT command used to create the query.
The TraceIndex() function is almost identical to the TraceSelect() function. The only differences occur on lines 35 and 40. An extra string indicating that an indexName is being used is added to the ShowStart() call. The ShowStart() function inserts custom output into the output log.
After running the SQLTrace script, you will not be able to view the log file until you disable the Output Log option from the Desktop Properties dialog. If you open the sqltrace.txt file in a text editor, you can see the differences between the three queries. The first portion of the file contains all the SQL operations that occur when the initial query becomes active:
SQL Prepare: INTRBASE - select RDB$OWNER_NAME, RDB$RELATION_NAME,...
SQL Vendor: INTRBASE - isc_dsql_allocate_statement
SQL Vendor: INTRBASE - isc_start_transaction
SQL Vendor: INTRBASE - isc_dsql_prepare
SQL Execute: INTRBASE - select RDB$OWNER_NAME, RDB$RELATION_NAME,...
SQL Vendor: INTRBASE - isc_dsql_execute
SQL Stmt: INTRBASE - Fetch
SQL Vendor: INTRBASE - isc_dsql_fetch
SQL Stmt: INTRBASE - Fetch
SQL Vendor: INTRBASE - isc_dsql_fetch
SQL Stmt: INTRBASE - EOF
SQL Transact: INTRBASE - XACT Commit
SQL Vendor: INTRBASE - isc_commit_transaction
SQL Stmt: INTRBASE - Close
SQL Vendor: INTRBASE - isc_dsql_free_statement
SQL Prepare: INTRBASE - select R.RDB$FIELD_NAME, F.RDB$FIELD_TYPE,...
SQL Vendor: INTRBASE - isc_dsql_allocate_statement
SQL Vendor: INTRBASE - isc_start_transaction
SQL Vendor: INTRBASE - isc_dsql_prepare
SQL Execute: INTRBASE - select R.RDB$FIELD_NAME, F.RDB$FIELD_TYPE,...
SQL Vendor: INTRBASE - isc_dsql_execute
SQL Stmt: INTRBASE - Fetch
SQL Vendor: INTRBASE - isc_dsql_fetch
SQL Stmt: INTRBASE - Fetch
SQL Vendor: INTRBASE - isc_dsql_fetch
SQL Stmt: INTRBASE - Fetch
SQL Vendor: INTRBASE - isc_dsql_fetch
SQL Stmt: INTRBASE - Fetch
SQL Vendor: INTRBASE - isc_dsql_fetch
SQL Stmt: INTRBASE - Fetch
SQL Vendor: INTRBASE - isc_dsql_fetch
SQL Stmt: INTRBASE - EOF
SQL Transact: INTRBASE - XACT Commit
SQL Vendor: INTRBASE - isc_commit_transaction
SQL Stmt: INTRBASE - Close
SQL Vendor: INTRBASE - isc_dsql_free_statement
SQL Prepare: INTRBASE - select I.RDB$INDEX_NAME, I.RDB$UNIQUE_FLAG,...
SQL Vendor: INTRBASE - isc_dsql_allocate_statement
SQL Vendor: INTRBASE - isc_start_transaction
SQL Vendor: INTRBASE - isc_dsql_prepare
SQL Execute: INTRBASE - select I.RDB$INDEX_NAME, I.RDB$UNIQUE_FLAG,...
SQL Vendor: INTRBASE - isc_dsql_execute
SQL Stmt: INTRBASE - Fetch
SQL Vendor: INTRBASE - isc_dsql_fetch
SQL Stmt: INTRBASE - Fetch
SQL Vendor: INTRBASE - isc_dsql_fetch
SQL Stmt: INTRBASE - Fetch
SQL Vendor: INTRBASE - isc_dsql_fetch
SQL Stmt: INTRBASE - Fetch
SQL Vendor: INTRBASE - isc_dsql_fetch
SQL Stmt: INTRBASE - EOF
SQL Transact: INTRBASE - XACT Commit
SQL Vendor: INTRBASE - isc_commit_transaction
SQL Stmt: INTRBASE - Close
SQL Vendor: INTRBASE - isc_dsql_free_statement
SQL Prepare: INTRBASE - select R.RDB$FIELD_NAME, F.RDB$VALIDATION_BLR,...
SQL Vendor: INTRBASE - isc_dsql_allocate_statement
SQL Vendor: INTRBASE - isc_start_transaction
SQL Vendor: INTRBASE - isc_dsql_prepare
SQL Execute: INTRBASE - select R.RDB$FIELD_NAME, F.RDB$VALIDATION_BLR,...
SQL Vendor: INTRBASE - isc_dsql_execute
SQL Stmt: INTRBASE - Fetch
SQL Vendor: INTRBASE - isc_dsql_fetch
SQL Stmt: INTRBASE - Fetch
SQL Vendor: INTRBASE - isc_dsql_fetch
SQL Stmt: INTRBASE - Fetch
SQL Vendor: INTRBASE - isc_dsql_fetch
SQL Stmt: INTRBASE - Fetch
SQL Vendor: INTRBASE - isc_dsql_fetch
SQL Stmt: INTRBASE - Fetch
SQL Vendor: INTRBASE - isc_dsql_fetch
SQL Stmt: INTRBASE - EOF
SQL Transact: INTRBASE - XACT Commit
SQL Vendor: INTRBASE - isc_commit_transaction
SQL Stmt: INTRBASE - Close
SQL Vendor: INTRBASE - isc_dsql_free_statement
SQL Prepare: INTRBASE - SELECT CARTUSER ,CARTDATE ,ISBN ,QTY FROM CART
ORDER BY CARTUSER ASC
SQL Vendor: INTRBASE - isc_dsql_allocate_statement
SQL Vendor: INTRBASE - isc_start_transaction
SQL Vendor: INTRBASE - isc_dsql_prepare
SQL Execute: INTRBASE - SELECT CARTUSER ,CARTDATE ,ISBN ,QTY FROM CART
ORDER BY CARTUSER ASC
SQL Vendor: INTRBASE - isc_dsql_execute
SQL Stmt: INTRBASE - Fetch
SQL Vendor: INTRBASE - isc_dsql_fetch
SQL Stmt: INTRBASE - EOF
SQL Transact: INTRBASE - XACT Commit
SQL Vendor: INTRBASE - isc_commit_transaction
SQL Stmt: INTRBASE - Close
SQL Vendor: INTRBASE - isc_dsql_free_statement
The first query of the comparison appears after the first Start Trace comment line. Notice that the asterisk of the original SELECT has been replaced by an explicit field list. The resulting SELECT also has an ORDER BY CARTUSER clause that was not present on the original query. The simple SELECT query results in 13 SQL operations:
// Start Trace of SELECT * FROM CART
SQL Prepare: INTRBASE - SELECT CARTUSER ,CARTDATE ,ISBN ,QTY FROM CART
ORDER BY CARTUSER ASC
SQL Vendor: INTRBASE - isc_dsql_allocate_statement
SQL Vendor: INTRBASE - isc_start_transaction
SQL Vendor: INTRBASE - isc_dsql_prepare
SQL Execute: INTRBASE - SELECT CARTUSER ,CARTDATE ,ISBN ,QTY FROM CART
ORDER BY CARTUSER ASC
SQL Vendor: INTRBASE - isc_dsql_execute
SQL Stmt: INTRBASE - Fetch
SQL Vendor: INTRBASE - isc_dsql_fetch
SQL Stmt: INTRBASE - EOF
SQL Transact: INTRBASE - XACT Commit
SQL Vendor: INTRBASE - isc_commit_transaction
SQL Stmt: INTRBASE - Close
SQL Vendor: INTRBASE - isc_dsql_free_statement
// End Trace of SELECT * FROM CART
The next query results in a much more extensive set of SQL operations. It begins by creating a query that is identical to the one of the simple SELECT. A new SELECT is later executed with order set to match the index. By specifying the order with indexName, what were 13 SQL operations become 26 SQL operations. The first SELECT is discarded when the indexName is set:
// Start Trace of SELECT * FROM CART (with indexName setting)
SQL Prepare: INTRBASE - SELECT CARTUSER ,CARTDATE ,ISBN ,QTY FROM CART
ORDER BY CARTUSER ASC
SQL Vendor: INTRBASE - isc_dsql_allocate_statement
SQL Vendor: INTRBASE - isc_start_transaction
SQL Vendor: INTRBASE - isc_dsql_prepare
SQL Execute: INTRBASE - SELECT CARTUSER ,CARTDATE ,ISBN ,QTY FROM CART
ORDER BY CARTUSER ASC
SQL Vendor: INTRBASE - isc_dsql_execute
SQL Stmt: INTRBASE - Fetch
SQL Vendor: INTRBASE - isc_dsql_fetch
SQL Stmt: INTRBASE - EOF
SQL Transact: INTRBASE - XACT Commit
SQL Vendor: INTRBASE - isc_commit_transaction
SQL Stmt: INTRBASE - Close
SQL Vendor: INTRBASE - isc_dsql_free_statement
SQL Prepare: INTRBASE - SELECT CARTUSER ,CARTDATE ,ISBN ,QTY FROM CART
ORDER BY ISBN ASC
SQL Vendor: INTRBASE - isc_dsql_allocate_statement
SQL Vendor: INTRBASE - isc_start_transaction
SQL Vendor: INTRBASE - isc_dsql_prepare
SQL Execute: INTRBASE - SELECT CARTUSER ,CARTDATE ,ISBN ,QTY FROM CART
ORDER BY ISBN ASC
SQL Vendor: INTRBASE - isc_dsql_execute
SQL Stmt: INTRBASE - Fetch
SQL Vendor: INTRBASE - isc_dsql_fetch
SQL Stmt: INTRBASE - EOF
SQL Transact: INTRBASE - XACT Commit
SQL Vendor: INTRBASE - isc_commit_transaction
SQL Stmt: INTRBASE - Close
SQL Vendor: INTRBASE - isc_dsql_free_statement
// End Trace of SELECT * FROM CART
The last query operation shows an exact match between the original SELECT command and the one that executes on the server. In this case, the ORDER BY and asterisk field specification do not change. The result is 19 lines with a single execution of SELECT:
// Start Trace of SELECT * FROM CART ORDER BY ISBN SQL Prepare: INTRBASE - SELECT * FROM CART ORDER BY ISBN SQL Vendor: INTRBASE - isc_dsql_allocate_statement SQL Vendor: INTRBASE - isc_start_transaction SQL Vendor: INTRBASE - isc_dsql_prepare SQL Vendor: INTRBASE - isc_dsql_sql_info SQL Vendor: INTRBASE - isc_vax_integer SQL Transact: INTRBASE - XACT (UNKNOWN) SQL Vendor: INTRBASE - isc_commit_retaining SQL Execute: INTRBASE - SELECT * FROM CART ORDER BY ISBN SQL Vendor: INTRBASE - isc_dsql_execute SQL Stmt: INTRBASE - Fetch SQL Vendor: INTRBASE - isc_dsql_fetch SQL Stmt: INTRBASE - EOF SQL Stmt: INTRBASE - Reset SQL Vendor: INTRBASE - isc_dsql_free_statement SQL Transact: INTRBASE - XACT Commit SQL Vendor: INTRBASE - isc_commit_transaction SQL Stmt: INTRBASE - Close SQL Vendor: INTRBASE - isc_dsql_free_statement // End Trace of SELECT * FROM CART ORDER BY ISBN SQL Connect: INTRBASE - Disconnect GENERAL SQL Vendor: INTRBASE - isc_detach_database
SQL Trace statements vary by SQL-Link driver. Whenever you change database servers, use SQL Trace to check for differences in performance-critical operations.
When you have made the Help form operational, you can update the query definitions for the following JavaScript forms: Cart, Checkout, Detail, Keyword, Quick, and Results, which are covered in the following sections. Use the Script Editor to edit each JavaScript form as a Script. Change each form as shown.
The Cart form requires more than just a change to the table name and the database property. The Cart form relates the Cart and Title tables on the ISBN field. For dBASE tables, the relation is set through the indexName, masterRowset, and masterFields properties. To optimize the query for SQL, all three properties must be unspecified. Each line in bold needs to be revised or removed.
Here are the original Cart form queries for dBASE tables:
with (this.cart1 = new Query()){
left = 70;
top = 1;
sql = 'SELECT * FROM "cart.DBF"';
active = true;
}
with (this.cart1.rowset) {
}
with (this.title1 = new Query()){
left = 70;
top = 2;
sql = 'SELECT * FROM "title.DBF"';
active = true;
}
with (this.title1.rowset) {
indexName = "ISBN";
masterRowset = parent.parent.cart1.rowset;
masterFields = "ISBN";
}
The key to creating efficient SQL relations is the masterSource property. You can use the masterSource property to create an SQL parameter that relates one query to another. The WHERE clause of the SELECT command works with parameters to ensure that the rows of one query relate to rows in another. In the case of the Cart form, the WHERE clause of the Title query needs to restrict the rowset to rows where the ISBN matches the current ISBN in the Cart query.
The Cart form allows updates to the Cart and requires a live rowset for the cart1 query. The Title table is never directly modified by the shopping cart application. You can always optimize Title queries by setting requestLive to false.
Here are the revised Cart form queries for SQL tables:
with (this.cart1 = new Query()){
left = 70;
top = 1;
database = parent.dbstore;
sql = 'SELECT * FROM CART';
active = true;
}
with (this.cart1.rowset) {
}
with (this.title1 = new Query()){
left = 70;
top = 2;
requestLive = false;
database = parent.dbstore;
masterSource = parent.cart1.rowset;
sql = 'SELECT * FROM TITLE WHERE ISBN = :ISBN ORDER BY ISBN';
active = true;
}
with (this.title1.rowset) {
}
The Checkout form has more queries than the other forms you modified already, but there are no new changes. The Customer, Invoice, and Lineitem queries only need to have the database property and revised table names added. There are no index or relation issues with the first three queries.
Here are the original Checkout form queries for dBASE tables:
with (this.customer1 = new Query()){
left = 70;
top = 1;
sql = 'SELECT * FROM "customer.DBF"';
active = true;
}
with (this.customer1.rowset) {
}
with (this.invoice1 = new Query()){
left = 70;
top = 2;
sql = 'SELECT * FROM "invoice.DBF"';
active = true;
}
with (this.invoice1.rowset) {
}
with (this.lineitem1 = new Query()){
left = 70;
top = 3;
sql = 'SELECT * FROM "lineitem.DBF"';
active = true;
}
with (this.lineitem1.rowset) {
}
with (this.cart1 = new Query()){
left = 70;
top = 4;
sql = 'SELECT * FROM "cart.DBF"';
active = true;
}
with (this.cart1.rowset) {
}
with (this.title1 = new Query()){
left = 70;
top = 5;
sql = 'SELECT * FROM "title.DBF"';
active = true;
}
with (this.title1.rowset) {
indexName = "ISBN";
masterRowset = parent.parent.cart1.rowset;
masterFields = "ISBN";
}
The relation between the cart1 query and the title1 query is identical to the one in the Cart form. You can copy and paste the same modifications into the Checkout form. As with the Cart form, the Checkout form updates all tables except the Title table.
Here are the revised Checkout form queries for SQL tables:
with (this.customer1 = new Query()){
left = 70;
top = 1;
database = parent.dbstore;
sql = 'SELECT * FROM CUSTOMER';
active = true;
}
with (this.customer1.rowset) {
}
with (this.invoice1 = new Query()){
left = 70;
top = 2;
database = parent.dbstore;
sql = 'SELECT * FROM INVOICE';
active = true;
}
with (this.invoice1.rowset) {
}
with (this.lineitem1 = new Query()){
left = 70;
top = 3;
database = parent.dbstore;
sql = 'SELECT * FROM LINEITEM';
active = true;
}
with (this.lineitem1.rowset) {
}
with (this.cart1 = new Query()){
left = 70;
top = 4;
database = parent.dbstore;
sql = 'SELECT * FROM CART';
active = true;
}
with (this.cart1.rowset) {
}
with (this.title1 = new Query()){
left = 70;
top = 5;
requestLive = false;
database = parent.dbstore;
masterSource = parent.cart1.rowset;
sql = 'SELECT * FROM TITLE WHERE ISBN = :ISBN ORDER BY ISBN';
active = true;
}
with (this.title1.rowset) {
}
In the Detail form, the Cart query is not related to the Title query. The Cart query is used only when a shopper adds the current title to the shopping cart. A live rowset must be available for the Cart query.
Here are the original Detail Form queries for dBASE tables:
with (this.cart1 = new Query()){
left = 70;
top = 1;
sql = 'SELECT * FROM "cart.DBF"';
active = true;
}
with (this.cart1.rowset) {
}
with (this.title1 = new Query()){
left = 70;
top = 2;
sql = 'SELECT * FROM "title.dbf"';
active = true;
}
with (this.title1.rowset) {
autoEdit = false;
}
You can remove the autoEdit property from the title1.rowset block. The property has no effect on the Detail form in either the dBASE or SQL version. The autoEdit property comes into play only when the form contains a control with a direct datalink to a field. No such controls exist on the completed version of the Detail form.
Here are the revised Detail form queries for SQL tables:
with (this.cart1 = new Query()){
left = 70;
top = 1;
database = parent.dbstore;
sql = 'SELECT * FROM CART';
active = true;
}
with (this.cart1.rowset) {
}
with (this.title1 = new Query()){
left = 70;
top = 2;
requestLive = false;
database = parent.dbstore;
sql = 'SELECT * FROM TITLE';
active = true;
}
with (this.title1.rowset) {
}
The changes to the Keyword form are the simplest yet. The form contains only one query. There are no indexes or relations to worry about.
Here is the original Keyword form query for dBASE tables:
with (this.title1 = new Query()){
left = 70;
top = 1;
sql = 'SELECT * FROM "title.DBF"';
active = true;
}
with (this.title1.rowset) {
}
Like all other instances of a Title query, you can set requestLive to false. You could also delete the with block for the rowset. If you remove the empty with block, the form runs the same. However, if you open and resave the file through the Form Designer, IntraBuilder reinserts the empty with block.
Here is the revised Keyword form query for SQL tables:
with (this.title1 = new Query()){
left = 70;
top = 1;
requestLive = false;
database = parent.dbstore;
sql = 'SELECT * FROM TITLE';
active = true;
}
The only required changes for the Quick form are adding the database assignment and revising the table name. Because the form does not update any tables, you can also set requestLive to false for the form's two queries.
Here are the original Quick Form queries for dBASE tables:
with (this.product1 = new Query()){
left = 70;
top = 4;
sql = 'SELECT * FROM "product.dbf"';
active = true;
}
with (this.product1.rowset) {
}
with (this.title1 = new Query()){
left = 70;
top = 5;
sql = 'SELECT * FROM "title.dbf"';
active = true;
}
with (this.title1.rowset) {
}
Here are the revised Quick Form queries for SQL tables:
with (this.product1 = new Query()){
left = 70;
top = 4;
requestLive = false;
database = parent.dbstore;
sql = 'SELECT * FROM PRODUCT';
active = true;
}
with (this.product1.rowset) {
}
with (this.title1 = new Query()){
left = 70;
top = 5;
requestLive = false;
database = parent.dbstore;
sql = 'SELECT * FROM TITLE';
active = true;
}
with (this.title1.rowset) {
}
The last standard query block to change is a simple Title query for the Results form. This query is identical to the Title query of the Keyword form.
Here is the original Results Form query for dBASE tables:
with (this.title1 = new Query()){
left = 70;
top = 1;
sql = 'SELECT * FROM "title.dbf"';
active = true;
}
with (this.title1.rowset) {
}
Here is the revised Results Form query for SQL tables:
with (this.title1 = new Query()){
left = 70;
top = 1;
requestLive = false;
database = parent.dbstore;
sql = 'SELECT * FROM TITLE';
active = true;
}
with (this.title1.rowset) {
}
After changing the query definitions in each of the five forms, you can run the shopping cart application with the InterBase database. There are still three problems that will keep the application from running as it did with dBASE tables. See whether you can locate the forms that do not respond correctly. One form has a cosmetic problem and a logic error. Another form has a button that can lead to a runtime error. The next two sections will show you how to correct these problems.
If a shopper searches the Titles and opens the Detail form, excess time information displays along with the publication date. If a shopper clicks on the Add to Cart button, the book goes into the cart with a null quantity. To better equip the Detail form for working with SQL tables, the date display and the add operations need modification. Figure 21.15 shows the Detail form before removing the time from the publication date display.
Figure 21.15 : The Detail form with extra time information.
To fix the Detail form, edit the detail.jfm file as a script and adjust the UpdateValues() and buttonAdd_onServerClick() methods as shown in Listings 21.3 and 21.4. The lines that require modification are shown in bold.
Listing 21.3. The revised UpdateValues() method.
1: function UpdateValues(thisForm)
2: {
3: var fTitle = thisForm.title1.rowset.fields ;
4: thisForm.valueTitle.text = fTitle["TITLE"].value ;
5: thisForm.valueAuthor.text = fTitle["AUTHOR"].value ;
6: thisForm.valueISBN.text = fTitle["ISBN"].value ;
7: thisForm.valueProduct.text = fTitle["PRODUCT"].value ;
8: thisForm.valuePublisher.text = fTitle["PUBLISHER"].value ;
9: thisForm.valuePubdate.text = fTitle["PUBDATE"].value.getMonth() +
10: "/" + fTitle["PUBDATE"].value.getYear() ;
11: thisForm.valuePrice.text = fTitle["PRICE"].value ;
12: thisForm.valueNotes.text = fTitle["NOTES"].value ;
13: thisForm.htmlPosition.text =
14: thisForm.currentRow + " of " + thisForm.titleCount ;
15: thisForm.imageCover.visible = (! (fTitle["COVER"].value == "")) ;
16: }
The UpdateValues() method fires when the Detail form loads and whenever the row navigation occurs. The method synchronizes what the user sees with the values from the current row. Lines 9 and
10 replace the following statement:
thisForm.valuePubdate.text = fTitle["PUBDATE"].value
The original version uses whatever value is in the field as the display value. For the dBASE table, the field value contained only a date. InterBase can store date and time information in a single field. The time is not relevant to a publication date. The only essential information for publication date is the month and year. Because the value property of a date field is a date object, you can use methods from the Date class to extract the month and year. Line 9 extracts the month, and line 10 appends the year in the format MM/YY.
The second problem with the Detail form does not manifest itself until the Cart form opens. When a shopper adds a book to his shopping cart, the Cart form shows the Qty set to zero. The actual Qty in the table is a null. A Qty of one should appear in the table and in the browser. Figure 21.16 shows how the total and grand total are blank when the Qty is null. You can add one line to the buttonAdd_onServerClick() method to fix the problem. Listing 21.4 shows the new line in bold.
Figure 21.16 : The Cart form with a null value in the Qty field.
Listing 21.4. The revised buttonAdd_onServerClick() method.
1: function buttonAdd_onServerClick()
2: {
3: var rCart = this.form.cart1.rowset,
4: rTitle = this.form.title1.rowset ;
5: rCart.applyLocate(
6: "ISBN = " + "'" + rTitle.fields["ISBN"].value + "'"
7: + "AND CARTUSER = " + "'" + this.form.user + "'" ) ;
8: if ( rCart.endOfSet )
9: {
10: rCart.beginAppend() ;
11: rCart.fields["ISBN"].value = rTitle.fields["ISBN"].value ;
12: rCart.fields["CARTUSER"].value = this.form.user ;
13: rCart.fields["CARTDATE"].value = new Date() ;
14: rCart.fields["QTY"].value = 0 ;
15: }
16: rCart.fields["QTY"].value++ ;
17: rCart.save() ;
18: rCart.active = false ;
19: nextForm = new cartForm() ;
20: nextForm.user = this.form.user ;
21: nextForm.open() ;
22: this.form.release() ;
23: }
When you append a row to a dBASE table, all numeric fields default to zero. The same operation on most other table types results in null values in the numeric fields. Any computation involving a null
value results in a null value such that 1 + null = null. Line 16 has no effect if the value is a null. To allow the initial value to increment, the Qty field must be set to a non-null value. Line 14 replaces the null with a zero, making
the function work the same for both InterBase and dBASE tables.
The Checkout form contains two queries that are defined within methods rather than in the constructor. Both queries become active when a shopper clicks on the Buy button and the data has been validated. The queries duplicate existing queries for the Custom and Invoice tables. The secondary queries help calculate the next available primary key value.
Determining the next available key value is a native feature of many table types, including Paradox and InterBase. The dBASE table format does not have any such functionality and requires the calculation to happen at the application script level. Paradox and Access tables have auto-increment fields that have self-generating unique key values. InterBase provides stored procedures, triggers, and generators that allow developers to define server-side functions that track key field values.
To get the shopping cart application up and running with InterBase as quickly as possible, you can keep the dBASE logic intact. The last topic of the day explains how to replace some of the existing logic with database server routines. To make the dBASE logic work for InterBase, edit the Checkout form as a script and update the SaveCustomer() and SaveInvoice() methods as shown in Listings 21.5 and 21.6.
Listing 21.5. The revised SaveCustomer() method.
1: function SaveCustomer( thisForm )
2: {
3: var customer2 = new Query() ;
4: customer2.requestLive = false ;
5: customer2.database = thisForm.dbstore ;
6: customer2.sql = "SELECT * FROM CUSTOMER ORDER BY CUSTOMER" ;
7: customer2.active = true ;
8:
9: var customer1Row = thisForm.customer1.rowset,
10: customer2Row = customer2.rowset ;
11: if ( thisForm.radioVisa.value )
12: {
13: customer1Row.fields["CARD"].value = "VISA" ;
14: }
15: else if( thisForm.radioMC.value )
16: {
17: customer1Row.fields["CARD"].value = "MC" ;
18: }
19: else
20: {
21: customer1Row.fields["CARD"].value = "AMEX" ;
22: }
23: // customer2Row.indexName = "CUSTOMER" ;
24: customer2Row.last() ;
25: customer1Row.fields["CUSTOMER"].value = customer2Row.endOfSet ?
26: 1 : customer2Row.fields["CUSTOMER"].value + 1 ;
27: customer1Row.save() ;
28: customer2.active = false ;
29: }
The SaveCustomer() method saves a new row in the Customer Table. When the Checkout form loads, the Rowset::beginAppend() method creates a row buffer that is discarded unless the
shopper clicks on the Buy button and all the entries are validated. All but two of the fields in the customer table have direct datalinks to controls on the Checkout form. The two that do not are the Card field and the Customer field. Lines 11 through 22
determine the value for the Card field. The rest of the logic calculates the appropriate Customer value. Lines 3 through 7 replace the following statement:
var customer2 = new Query("select * from customer") ;
The shortcut approach that was used with the original form does not work when using a table that is not contained in the default database. Another change from the original code appears on line 23. This line is commented out in the InterBase version.
Listing 21.6. The revised SaveInvoice() method.
1: function SaveInvoice( thisForm )
2: {
3: var invoice2 = new Query() ;
4: invoice2.requestLive = true ;
5: invoice2.database = thisForm.dbstore ;
6: invoice2.sql = "SELECT * FROM INVOICE ORDER BY INVOICE" ;
7: invoice2.active = true ;
8:
9: var customerRow = thisForm.customer1.rowset
10: invoice1Row = thisForm.invoice1.rowset,
11: invoice2Row = invoice2.rowset ;
12: invoice1Row.beginAppend() ;
13: invoice1Row.fields["CUSTOMER"].value =
14: customerRow.fields["CUSTOMER"].value ;
15: invoice1Row.fields["ORDERDATE"].value = (new Date()) ;
16: // invoice2Row.indexName = "INVOICE" ;
17: invoice2Row.last() ;
18: invoice1Row.fields["INVOICE"].value = invoice2Row.endOfSet ?
19: 1 : invoice2Row.fields["INVOICE"].value + 1 ;
20: invoice1Row.save() ;
21: invoice2.active = false ;
22: }
The SaveInvoice() method contains much of the same logic contained in SaveCustomer(). The primary differences in this method are that the fields do not have datalinks and the rowset
was not already in append mode. Line 12 places the invoice1 query into append mode, whereas the customer1 query was already in append mode in the previous method. The changes for InterBase are essentially the same as the change made to
SaveCustomer().
| NOTE |
The files for Day 21 contain two versions of the Checkout form. The check1.jfm file contains the methods shown in Listings 21.5 and 21.6. You copy the check1.jfm file over checkout.jfm to run the application without any stored procedures. The checkout.jfm file, contained in the Day 21 file set, relies on the stored procedures presented in the section "Application Partitioning with Stored Procedures." |
The Receipt report needs many of the same modifications that you have already made to the forms. To make the report run with InterBase, each query needs the database setting and revised table names. Unlike the JavaScript forms, the Receipt Report does not inherit a dbstore database from the Toolbar custom form class. You need to add a definition for the dbstore database before the first query definition. Edit the receipt.jrp file as script and modify the query definitions to match Listing 21.7. All modifications are in bold.
Listing 21.7. The revised queries for the Receipt report.
1: with (this.dbstore = new Database()){
2: left = 0;
3: top = 0;
4: databaseName = "STORE";
5: loginString = "SYSDBA/masterkey";
6: active = true;
7: }
8:
9: with (this.invoice1 = new Query()){
10: left = 1000;
11: top = 0;
12: requestLive = false;
13: database = parent.dbstore;
14: sql = 'SELECT * FROM INVOICE';
15: active = true;
16: }
17:
18: with (this.invoice1.rowset) {
19: }
20:
21: with (this.lineitem1 = new Query()){
22: left = 2000;
23: top = 0;
24: requestLive = false;
25: database = parent.dbstore;
26: masterSource = parent.invoice1.rowset;
27: sql = 'SELECT * FROM LINEITEM WHERE INVOICE = :INVOICE ' +
28: ' ORDER BY INVOICE';
29: active = true;
30: }
31:
32: with (this.lineitem1.rowset) {
33: }
34:
35: with (this.customer1 = new Query()){
36: left = 3000;
37: top = 0;
38: requestLive = false;
39: database = parent.dbstore;
40: masterSource = parent.invoice1.rowset;
41: sql = 'SELECT * FROM CUSTOMER WHERE CUSTOMER = :CUSTOMER ' +
42: ' ORDER BY CUSTOMER';
43: active = true;
44: }
45:
46: with (this.customer1.rowset) {
47: }
48:
49: with (this.title1 = new Query()){
50: left = 4000;
51: top = 0;
52: requestLive = false;
53: database = parent.dbstore;
54: masterSource = parent.lineitem1.rowset ;
55: sql = 'SELECT * FROM TITLE WHERE ISBN = :ISBN ORDER BY ISBN';
56: active = true;
57: }
58:
59: with (this.title1.rowset) {
60: }
The Receipt report contains four related queries for the Invoice, Lineitem, Customer, and Title tables. The Lineitem table controls the detail band. The page header displays fields from the Invoice and
Customer tables. Only fields from the Lineitem and Title tables appear in the detail band.
The query code in Listing 21.7 is part of the constructor code for the report. In the receipt.jrp file, the database and query definitions come before any report object definitions. Lines 1 through 7 show the database definition that is entirely new to the InterBase version of the report. All the query and rowset objects also exist in the dBASE version.
The Invoice query contains three modifications on lines 12 through 14. Because reports are almost always read-only operations, you can set requestLive to false for all the queries in the report. You can see the setting repeated for the other queries on lines 24, 38, and 52.
The indexName, masterRowset, and masterFields properties were removed from the Lineitem, Customer, and Title rowset definitions. The masterSource properties set on lines 26, 40, and 54 act as SQL equivalents to the dBASE masterRowset property settings. The ORDER BY clauses found on lines 28, 42, and 55 work in place of the indexName values. The masterFields property settings are replaced in lines 27, 41, and 55 by WHERE clauses.
After incorporating all the changes in Listing 21.7, you run the Receipt report with the Store database. When you do, one more problem appears that is reminiscent of something you recently dealt with on the Detail form. The order date displays with a time value, as shown in Figure 21.17.
Figure 21.17 : The Receipt report with time display.
The time value wraps in the Report Designer and can lead to other problems when run through a browser. You can remove the time portion of the order date with the following steps:
|
If you do not reload the report in the Designer, IntraBuilder might encounter an error when you reference the new method in a self-evaluating code block. |
That is the last modification for the Receipt report. You can run the report to view the receipt without time information. The shopping cart application now functions just as it did with dBASE tables. You can stop here or continue to learn how to further optimize the application using stored procedures.
You can use stored procedures to harness the native processing
power of a database server. IntraBuilder supports stored procedures
with Oracle, Sybase, Informix, DB2, InterBase, and Microsoft SQL
Server. Each server has proprietary syntax for developing stored
procedures, but the JavaScript calling system is consistent across
vendor platforms.
|
Application partitioning occurs when processing is spread across several systems. By nature, every IntraBuilder application does some partitioning between the Intra-Builder Server and client browsers. Adding client-side JavaScript increases the level of partitioning by offloading some of the processes to client browsers. Another way to partition an application is to move logic from IntraBuilder to a database server. The goal of partitioning is to move processes to the systems most adept at the task. For example, database servers are often better equipped at generating unique key values than IntraBuilder or a browser. |
In this section, you will learn how to create stored procedures in the Store database from an IntraBuilder script. After testing the stored procedures, you can simplify and optimize the Checkout form by adding calls to the stored procedures.
To see where stored procedures can be beneficial, let's review
the key values of each table in the Store database. Table 21.2
lists the keys that uniquely identify each row for the six tables
in the database. An examination of the key values reveals that
two tables contain self-referencing keys. The Customer and Invoice
tables contain unique key values that have no meaning outside
the table. Other tables use values from existing entities such
as a session ID, product name, or ISBN.
| Table | Key | Origin of Value |
| Cart | CARTUSER | Netinfo.sessionID |
| Customer | CUSTOMER | One greater than last Customer value |
| Invoice | INVOICE | One greater than last Invoice value |
| Lineitem | INVOICE + ISBN | Invoice from Invoice table and ISBN from Title table |
| Product | PRODUCT | Unique product names |
| Title | ISBN | Provided by publisher |
The Customer and Invoice keys are well suited to the InterBase generator feature. An InterBase generator is a value maintained by the server that you can increment and access through stored procedures. Generators are a remarkably efficient way to track and create key values. Listing 21.8 contains a Generate class to help create, use, and remove InterBase generators.
Listing 21.8. The genclass.js auxiliary class file.
1: //
2: // class Generator
3: //
4: // Provides methods for working with InterBase generators.
5: //
6: // Syntax:
7: // <oRef> = new Generator(<oRef: database>,<expC, generator name>)
8: //
9: // Methods:
10: // make(<expN> start value>)
11: //
12: // Creates a generator and a stored procedure in <oRef: database>.
13: // The generator is named <expC: name>. The stored procedure is
14: // named NEXT<expC: name>. The stored procedure increments the
15: // generator and sets GNEXT to the new value.
16: //
17: // call()
18: //
19: // Calls a generator named NEXT+<expC: name> and returns the value
20: // form GNEXT.
21: //
22: // drop()
23: //
24: // Removes a generator named <expC: name> and a stored procedure
25: // named NEXT<expC: name>.
26: //
27: class Generator(db,cName)
28: {
29: this.db = db ;
30: this.name = cName.toUpperCase() ;
31:
32: function make(nInit)
33: {
34: var CRLF = (new StringEx().chr(13))+ (new StringEx().chr(10)),
35: q = new Query() ;
36: q.database = this.db ;
37: q.sql = "SELECT * FROM RDB$GENERATORS "
38: + "WHERE RDB$GENERATOR_NAME = '" + this.name + "'" ;
39: q.active = true ;
40: if ( q.rowset.count() == 0 )
41: {
42: this.db.executeSQL("CREATE GENERATOR " + this.name) ;
43: this.db.executeSQL("SET GENERATOR " + this.name + " TO " +
44: Math.int(nInit) );
45: this.db.executeSQL("CREATE PROCEDURE NEXT" + this.name + CRLF+
46: "RETURNS (GNEXT INTEGER)" + CRLF +
47: "AS" + CRLF +
48: "BEGIN" + CRLF +
49: " GNEXT = GEN_ID(" + this.name + ",1);" + CRLF +
50: "END" ) ;
51: }
52: }
53:
54: function call()
55: {
56: var s = new StoredProc() ;
57: s.database = this.db ;
58: s.procedureName = "NEXT" + this.name ;
59: s.active = true ;
60: return ( s.params["GNEXT"].value ) ;
61: }
62:
63: function drop()
64: {
65: this.db.executeSQL("DROP PROCEDURE NEXT" + this.name) ;
66: this.db.executeSQL("DELETE FROM RDB$GENERATORS "
67: + "WHERE RDB$GENERATOR_NAME = '" + this.name + "'") ;
68: }
69: }
The Generate class makes it easy to work with InterBase generators. This class consists of two properties and three methods. The properties are values that you must pass to the constructor.
The db property is an object reference to an InterBase database. The name property is the name of the generator. Line 30 converts the name to uppercase, making the name case-insensitive.
The Generate::make() method is the most complex of the three. This method takes one parameter for the initial generator value. Lines 35 through 40 check to see whether the generator already exists. InterBase stores generators in the RDB$GENERATORS system table. This table contains the names and values of all generators in the database. If the generator already exists, the method ends without making any modifications.
If the query has a count of zero, the generator is undefined and the method executes three SQL commands. The following JavaScript statements use the Generate class to create a generator called MYGEN with an initial value of 5:
_sys.scripts.load("genclass.js") ;
var db = new Database() ;
db.databaseName = "STORE" ;
db.loginString = "SYSDBA/masterkey" ;
db.active = true ;
var gen = new Generator(db,"MYGEN");
gen.make(5);
When the Generator.make() method is called in the MYGEN example, the following SQL statements are passed to the InterBase server. The CREATE GENERATOR command adds a row to the RDB$GENERATORS table and sets the value to zero, and the SET command changes the value to 5. The CREATE PROCEDURE command defines a new stored procedure on the server:
CREATE GENERATOR MYGEN SET GENERATOR MYGEN TO 5 CREATE PROCEDURE NEXTMYGEN RETURNS (GNEXT INTEGER) AS BEGIN GNEXT = GEN_ID(MYGEN,1); END
| TIP |
InterBase lets you define much more complex stored procedures than are shown in this chapter. See the InterBase Help file for a complete list of statements and functions that you can use in a stored procedure. |
The Generate::call() method is one you can call anytime you need to increment and retrieve a value from a generator. This method calls the stored procedure defined in the Generate::make() method. Line 56 creates an instance of the StoredProc class. The stored procedure executes when the active property is set to true on line 59.
The StoredProc object makes it easy to pass and receive parameters from a stored procedure. For the generator, there is only one parameter, and it is passed back to IntraBuilder on line 60. IntraBuilder retrieves values from a stored procedure as associate arrays. The GNEXT parameter contains the current generator value. The following statements increment the MYGEN generator and return the new value. This calls a stored procedure with the name NEXTMYGEN:
_sys.scripts.load("genclass.js") ;
var db = new Database() ;
db.databaseName = "STORE" ;
db.loginString = "SYSDBA/masterkey" ;
db.active = true ;
var gen = new Generator(db,"MYGEN");
_sys.scriptOut.writeln(gen.call());
The last method removes a generator and associated stored procedure from the database. InterBase does not support a DROP GENERATOR command. To remove a Generator, lines 66 and 67 delete the row defining the generator from the RDB$GENERATORS system table. The following lines remove the MYGEN generator and NEXTMYGEN stored procedure from the Store database:
_sys.scripts.load("genclass.js") ;
var db = new Database() ;
db.databaseName = "STORE" ;
db.loginString = "SYSDBA/masterkey" ;
db.active = true ;
var gen = new Generator(db,"MYGEN");
gen.delete();
The following SQL commands execute when calling Generate::delete() for the MYGEN generator:
DROP PROCEDURE NEXTMYGEN DELETE FROM RDB$GENERATORS WHERE RDB$GENERATOR_NAME = 'MYGEN'
Using the Generate class, you can create stored procedures and generators for the Customer and Invoice tables. The addproc.js script in Listing 21.9 uses Generate::make() to set up the generators with initial values that correspond to the highest key value in each table.
Listing 21.9. The addproc.js script.
1: _sys.scripts.load("genclass.js") ;
2: var db = new Database() ;
3: db.databaseName = "STORE" ;
4: db.loginString = "SYSDBA/masterkey" ;
5: db.active = true ;
6:
7: KeyIt(db, "CUSTOMER") ;
8: KeyIt(db, "INVOICE") ;
9:
10: function KeyIt(db, cKey)
11: {
12: var q = new Query() ;
13: q.database = db ;
14: q.sql = "SELECT * FROM " + cKey + " ORDER BY " + cKey ;
15: q.active = true ;
16: q.rowset.last() ;
17: var gen = new Generator(db, cKey) ;
18: gen.make(q.rowset.fields[0].value) ;
19: }
Run the addproc script to create the generators and stored procedures for the Customer and Invoice tables. When you run the script, the first line opens the genclass.js file to get
access to the Generate class. Lines 2 through 5 establish a connection to the Store database. The function calls on lines 7 and 8 pass a reference to the Store database and a key field name to the KeyIt() function.
Although the KeyIt() function appears somewhat generic, it makes some broad assumptions about the table and key fields. Line 14 executes correctly only if the table contains a field with the same name as the table. Line 18 also relies on the same key field as being the first field in the table structure. Fortunately, the assumptions are correct for the Customer and Invoice tables.
After running the addproc script, you can use the InterBase Interactive SQL tool to verify that the generators and methods were created correctly. Here are the steps for checking metadata with the Interactive SQL tool:
You can also use the View|Metadata Information option to get a list of generators and the definition of a single stored procedure. If you select Procedures and enter NEXTINVOICE, the ISQL Output area displays the commands and parameters that make up the procedure. Figure 21.20 shows the ISQL Output area.
Figure 21.20 : Viewing the definition of the NEXTINVOICE stored procedure.
It is now time to update the Checkout form to take advantage of the stored procedures. Only the SaveCustomer() and SaveInvoice() methods need modification. Edit the Checkout form as a script file and make the changes shown in Listings 21.10 and 21.11. These changes eliminate the redundant queries and ensure valid keys across any number of IntraBuilder Agents.
Listing 21.10. The SaveCustomer() method with a stored procedure call.
1: function SaveCustomer( thisForm )
2: {
3: _sys.scripts.load("genclass.js") ;
4: var gen = new Generator(thisForm.dbstore, "CUSTOMER"),
5: customer1Row = thisForm.customer1.rowset ;
6: if ( thisForm.radioVisa.value )
7: {
8: customer1Row.fields["CARD"].value = "VISA" ;
9: }
10: else if( thisForm.radioMC.value )
11: {
12: customer1Row.fields["CARD"].value = "MC" ;
13: }
14: else
15: {
16: customer1Row.fields["CARD"].value = "AMEX" ;
17: }
18: customer1Row.fields["CUSTOMER"].value = gen.call() ;
19: customer1Row.save() ;
20: }
The new version of the SaveCustomer() method is about one third shorter than the one that relied on the dBASE key generation technique. See Listing 21.5 for a comparison. All references to
customer2Row are gone. Only lines 3 and 4 are new. Line 3 loads the script containing the Generate class. IntraBuilder ignores the statement if the script is already in memory. Line 4 creates an instance of the Generator class
using the Store database and the Customer table.
The real action happens on line 18. The gen.call() expression executes the NEXTCUSTOMER stored procedure on the InterBase Server. The return value is a valid unique key.
Listing 21.11. The SaveInvoice() method with a stored procedure call.
1: function SaveInvoice( thisForm )
2: {
3: _sys.scripts.load("genclass.js") ;
4: var gen = new Generator(thisForm.dbstore, "INVOICE"),
5: customerRow = thisForm.customer1.rowset,
6: invoice1Row = thisForm.invoice1.rowset ;
7: invoice1Row.beginAppend() ;
8: invoice1Row.fields["CUSTOMER"].value =
9: customerRow.fields["CUSTOMER"].value ;
10: invoice1Row.fields["ORDERDATE"].value = (new Date()) ;
11: invoice1Row.fields["INVOICE"].value = gen.call() ;
12: invoice1Row.save() ;
13: }
The new SaveInvoice() method is about half the size of the one in Listing 21.6. All references to invoice2Row are gone. The new lines are 3 and 4. As the form is currently written,
line 3 will always be ignored because SaveCustomer() always executes before SaveInvoice() and SaveCustomer() also loads the genclass script. Line 11 contains the stored procedure call.
Moving the key generation process to the database server can improve the performance and reliability of the Checkout form. The difference becomes more noticeable as the table grows larger and more users access the system simultaneously. The improvement might not be detectable with only one client or when working with small tables such as those provided with the book.
Today you learned how to leverage IntraBuilder applications by adding connections to database servers such as Oracle, Informix, InterBase, DB2, Sybase, and Microsoft SQL Server. The exercises shown throughout the chapter used InterBase to demonstrate effective access methods that apply equally well to any database server that you connect to through a SQL-Link driver.
The day began with a quick overview of the Local InterBase Server. This small footprint server is ideal for single-station development, and it comes with the Client/Server version of IntraBuilder, Delphi, and C++ Builder. You can use the Local InterBase Server to prototype a database for later deployment with Oracle, Sybase, Microsoft SQL Server, or InterBase Server for NT.
Today's main task was updating the custom form class, a report, and JavaScript forms to replace the dBASE table connections with SQL database connections. The changes for each form included adding a database object and updating the query object. To optimize the queries for SQL, you removed indexName, masterRowset, and masterField settings from the rowset definitions. In their place, you added ORDER BY and WHERE clauses to the SQL commands and masterSource properties to related queries.
By the end of today's lesson, you were partitioning the shopping cart application by moving process out of a JavaScript form and onto the database server. To create unique key values on the server, you defined generators and stored procedures that you could call from IntraBuilder.
| Q: | The final version of the Checkout form calls a stored procedure to generate a unique key value when appending rows. Wouldn't it be easier to create a trigger on the server that automatically fired when a row was added? |
| A: | Client applications are not notified of changes made by a trigger. If an IntraBuilder method appends a row to a table, values altered by any related triggers are not posted back to the IntraBuilder application. This can result in a mismatch between the IntraBuilder row buffer and the current database image. So, although it is possible to generate unique keys through a trigger, it is not recommended. |
| Q: | I need to create an audit trail of all transactions. What is the best approach when using a database server? |
| A: | Triggers offer the most efficient way to track changes to a table. When creating an audit trail, the audit table modified by the trigger is distinct from the table causing the trigger. Row buffers are not in danger of becoming corrupt when a trigger does not modify the table causing the trigger. |
| Q: | I have IntraBuilder Professional and Delphi Client/Server. Is it okay to use the InterBase version bundled with Delphi as a server for IntraBuilder? |
| A: | Yes. The InterBase Servers are not tied to any specific client product. The version bundled with Delphi also allows more connections than the one that comes with IntraBuilder. |
| Q: | I have completed the dBASE and InterBase versions of the shopping cart application, and I'm having problems running both at the same time. It seems like IntraBuilder keeps running the wrong version. What can I do to run both applications on the same agent? |
| A: | IntraBuilder agents cache class definitions and script files. If you try to load more than one class or script that has the same name, the agent recognizes only the first version. If you want to run the dBASE version and the InterBase versions of the shopping cart applications, you need to make the form and class names unique across both versions. If you prefix the names in the InterBase version with the letter I, the applications will run simultaneously. |
The Workshop section provides questions and exercises to help you get a better feel for the material you learned today. Try to answer the questions and at least think about the exercises before moving on to the answers. You'll find the answers to the questions in Appendix A, "Answers to Quiz Questions."
