Databases

Using multiple databases

dbf format databases

baserunner uses standard dbf format databases. Because there are several formats for memo fields associated with different xBase software, there are different versions of baserunner available: FoxPro format and dBase formats. The FoxPro format is more efficient in most cases, so, if you have no particular preference, that is probably the best choice. There are actually 2 memo field formats for dBase. The baserunner executable for dBase memo fields supports the most common form. If you have trouble with dBase memo fields using that version, contact baserunner and we'll get you a copy with the alternate dBase support (this is mostly a problem with databases created with Visual dBase). Even if your data does not use memo fields, the session database, used by baserunner, does. baserunner does not use index files.

Most DBMS software, including MS Access (along with many spreadsheets and word processors) can produce dbf format databases, either as a native format or through an export routine. There are a number of shareware applications available that can create and manipulate dbf format databases as well. You may also create a database using basemaker, a cgi application available online at baserunner.com.

baserunner supports these field types:

In addition to these standard field types, baserunner will recognize these later FoxPro field types: Float, Integer, Double, Currency (all treated identically to Numeric), and DateTime. This support is only available using the FoxPro format version of baserunner.

baserunner has no particular restriction on the size of a database it can use. Technically, you could use a database up to about 1GB in size; although in practical terms the size of the database is limited by performance considerations. The larger the database, the more memory and processor time baserunner will require. On a busy server, these issues are very important. The best rule of thumb is that the larger your database, the simpler your application should be. With larger databases, complex queries, and using baserunner features such as lists and totals will exact a price in performance. If you have a database of, say,  tens-of-thousands of records, you would be much better off breaking it up into several smaller databases.

Each template in your application will be associated with one database. You may use as many databases within an application as you wish. While baserunner does not support actual relations between databases, such relations may be simulated by a baserunner application.

The database baserunner will use for a given template is determined by the value of the _dbf variable. This variable will normally be included in the application configuration file, in which case it is treated as the default. If a _dbf variable is included in the configuration file, it may be overridden by sending a _dbf variable through the command line or through a form. In this case, baserunner will revert to the default database on the next call that does not include a _dbf variable. If no _dbf variable is included in the configuration file, each time you send a _dbf variable to baserunner (through the command line or a form) the database specified will remain active until the next time you send a _dbf variable.

Using multiple databases

If you use several databases within an application, there are a number of special considerations you need to take into account. The user's session record maintains the current query statement and record number, and moving from one database to another without assuring the proper record positioning and a valid query for the database can lead to some strange results. Some of this may be unavoidable if the user chooses to use her browser's back button in the middle of an application (although that can be just as true in an application that uses only one database). In most cases, calls that change the database should explicitly set the position and the query statement.

To simplify these potential issues in the case of calling detail records from a second database, you can make use of the _tempfil variable for the second database. The _tempfil variable is discarded after it is used, returning the query statement to whatever it was before. Also, when you use the _tempfil variable, any changes to the current record pointer stored in the user's session will be discarded. Of course, because of the the fact that the filter and the position are not saved, using the _tempfil variable is not advisable if you want to allow the user to navigate the second database--in other words, you should only display a list or a unique list in the template for the second database. Navigation actions (next, back, etc.) won't work.

Examples:

If you have broken up a large database into multiple smaller files to get better performance, you can use the _dbf variable as part of a query form. This would make the multiple databases transparent to the user. In this case, say, the database has been broken up into 3 smaller databases based on regions. The selected database would be queried for the value the user submitted for the field "field_name.":

In an application such as a shopping cart, a data entry form would be redirected to a different database that would hold the user's orders. This snippet starts from a single record display from a default catalog database. The user's session name is used as a code in the order database.

When an entry form is targeted to a different database, remember that when the _entrypass template is parsed, it will use the entry database (positioned at the new record). To get back to the catalog database you would need to add a link from the target. In this case you could reposition the record pointer using the _recordnumber variable passed through the entry form.

In this case, a link for the user to complete the order from the catalog template would be something like:

In an application where you wish to simulate a relational database model, the simplest method would be to use the _tempfil variable and set the target to a new window (or to a separate frame). In the case here, two databases would each contain a field named code. The second database would contain multiple detail records for each code.

The template target.htm would contain a list of all the records matching code:

Details for {alltrim(code)} <table> <tr>    <th>Text</th>    <th>Date</th>    <th>Number</th> </tr> <!--list1--> <tr>    <td>{trim(chr_field)}</td>    <td>{dtoc(date_field)}</td>    <td>{str(num_field, 10, 2)}</td> </tr> <!--endlist--> </table>

Another use of multiple databases is to create a password system to get into a particular database. If you have a database that contains the user names and passwords for all the people who should be granted access to a given database, the application could begin (this query template would be contained in _defhtml) with a query on the user database. If the user name and password are not found by the query, the template in _positionfail would be returned; otherwise a the target template, containing a link the the protected database would be returned. This snippet passes a _positionfail variable from the form which overrides the default value in the application configuration file. This value is a string rather than a template. This way, the default _positionfail template can still be used when a user query fails within the protected database. In actual use, you would probably want to add a validation script for this form.

The template target.htm is called is called if the user name and password are found. This template would then contain a link to the protected database:

<a href="/cgi-bin/baserun.exe?_ses={session}&_tar=protected.htm&_dbf=protected.dbf"> You passed; Proceed</a>

Even if a user bookmarked the protected target, that link would only be valid until the session expired. The internal password function in baserunner could not accomplish this particular application. It functions only on information contained in a particular record in the current database.