Frontbase Driver For Revolution 2.5 and later

Frontbase, Inc.

Release Notes 1.1

12-June-2007

Installation

For all platforms, be sure that you have FrontBase 3.6.x/4.x or later installed on your FrontBase server. Previous versions are not fully compatible with this driver. The driver is currently only available for MacOSX, Win32 and Linux versions of Revolution. This driver will not function correctly with versions of Revolution prior to 2.5.

The FrontBase driver is “client side” software. In order for it to talk with FrontBase, you’ll need to install a FrontBase server on a computer available on your network.

You can find a FrontBase server for your desired platform in the downloads section of http://www.frontbase.com/. Please refer to the respective database installation documents for instructions.

To install the FrontBase Driver for Revolution 2.8 and later:

MacOSX

Copy the files dbfrontbase.bundle and Database Drivers.txt from MacOSX/Revolution_2.8/Universal into the /Externals/Database Drivers folder of your Revolution installation.

Copy the files dbfrontbase.bundle and Database Drivers.txt from MacOSX/Revolution_2.8/Universal into the /Runtime/Mac OS X/Universal/Externals/Database Drivers folder of your Revolution installation.

Copy the files dbfrontbase.bundle and Database Drivers.txt from MacOSX/Revolution_2.8/PowerPC-32 into the /Runtime/Mac OS X/PowerPC-32/Externals/Database Drivers folder of your Revolution installation.

Copy the files dbfrontbase.bundle and Database Drivers.txt from MacOSX/Revolution_2.8/x86-32 into the /Runtime/Mac OS X/x86-32/Externals/Database Drivers folder of your Revolution installation.

Win32

Copy dbfrontbase.dll and Database Drivers.txt from Win32 into the /Externals/Database Drivers folder of your Revolution installation.

Copy dbfrontbase.dll and Database Drivers.txt from Win32 into the/Runtime/Windows/x86-32/Externals/Database Drivers folder of your Revolution installation.

To install the FrontBase Driver for Revolution 2.7:

MacOSX

Copy the dbfrontbase folder and Database Drivers.txt from MacOSX/Revolution_2.5-2.7 into the /Externals/Database Drivers folder of your Revolution installation.

Copy the dbfrontbase folder and Database Drivers.txt from MacOSX/Revolution_2.5-2.7 into the /Runtime/Mac OS X/PowerPC-32/Externals/Database Drivers folder of your Revolution installation.

Win32

Copy dbfrontbase.dll and Database Drivers.txt from Win32 into the /Externals/Database Drivers folder of your Revolution installation.

Copy dbfrontbase.dll and Database Drivers.txt from Win32 into the/Runtime/Windows/x86-32/Externals/Database Drivers folder of your Revolution installation.

To install the FrontBase Driver for Revolution 2.5 and 2.6:

MacOSX

Copy the dbfrontbase folder from MacOSX/Revolution_2.5-2.7 into the /components/global environment/database_drivers/MacOSX folder of your Revolution installation.

Win32

Copy dbfrontbase.dll from Win32 into the\components\global environment\database_drivers\Win32 folder of your Revolution installation.

Linux

Copy dbfrontbase.so from Linux into the /components/global environment/database_drivers/Linux folder of your Revolution installation.

Licensing

In order to the use the FrontBase driver you have to license your FrontBase database. The driver will not work with the Free-Unlicensed version of FrontBase. A free license can be obtained from the Buy->License section of the FrontBase website at http://www.frontbase.com/.

Connecting to FrontBase

To connect to FrontBase use the revOpenDatabase or revdb_connect function. This takes the form,

revOpenDatabase(databasetype, host, databasename, username, [password][, databasepassword])

where,

databasetype is “frontbase”,

host is the hostname or host ip of the server running the database,

databasename is the name or port number of the database,

username is a valid user of the database,

password is the password of the user specified in username,

databasepassword is the password of the database specified in databasename.

The password and databasepassword can be omitted or passed as empty strings if not required.

Transaction Mode

By default each connection uses an isolation level of REPEATABLE READ, a locking discipline of PESSIMISTIC and an updateability of READ WRITE. An overview of transactions can be found in the FrontBase User’s Guide,

http://www.frontbase.com/documentation/FBUsers_4.pdf.gz

A connection is also placed in autocommit mode (SET COMMIT TRUE) by default. If the connection is placed in manual commit mode (SET COMMIT FALSE) then a successful transaction must be ended by issuing an explicit COMMIT or ROLLBACK or using the revCommitDatabase/revRollBackDatabase commands. Note that a failed transaction is automatically rolled back.

SQL92 Standard

FrontBase adheres strictly to the SQL92 standard. There are a few simple rules that adherence to the standard is likely to impose on developers:

All SQL statements must end with a “;”(semicolon, no quotes). The driver will add this automatically if omitted.

If you use SQL92 reserved words as identifiers (e.g. table or column names), you must surround them in quotes. Since “name” is a reserved word in SQL92, the following statement will generate an error:

CREATE TABLE some_table (name varchar(100));

To make this work, use:

CREATE TABLE some_table (“name” varchar(100));

In Revolution use the quote constant, e.g.

"CREATE TABLE some_table (“ & quote & "name" & quote && "varchar(100));”

Also consider using a table and column naming convention. For example, prepend all column names with “c_” and table names with “t_”:

CREATE TABLE t_some_table (c_namevarchar(100));

Purchase this book:

A Guide to the SQL Standard (Fourth Edition)

by C. J. Date and Hugh Darwen

ISBN:0-201-96426-0

Amazon: http://www.amazon.com/exec/obidos/ASIN/0201964260

While a bit academic, it is the best reference to the SQL 92 Standard (other than the Standard itself).

FrontBase Specific Properties

The driver provides some internal properties to modify and query its behaviour. These can be set and returned as follows.

Setting a Property

To set the value of a property pass its name and new value in the form,

<property_name> value

to a revExecuteSQL or revQueryDatabase command.

For example,

revExecuteSQL gConnectionId,"<BatchSize> 1000"

if result() is not a number then

// error

end if

Will set the cursor batch size to 1000.

Getting a Property

The current value of a property is returned by passing its name in the form,

<property_name>

to a revQueryDatabase command. This will generate a 1-column cursor result.

For example,

put revQueryDatabase(gConnectionId, "<BatchSize>") into gCursorId

if gCursorId is not a number then

// error

else

put revDatabaseColumnNumbered(gCursorId, 1) into lBatchSize

end if

Will query the current setting of the cursor batch size.

Properties

BatchSize

Type : Integer

Default : 500. Must be greater than 0.

This property allows an application to change the number of rows fetched from the server in a single round-trip when a cursor is generated using revQueryDatabase. For example, if there are 5000 rows in a table, using the default it will take 10 round-trips to return all the rows. Setting this to 5000 will return all rows in a single trip, which will increase performance. Note, that by default ALL rows are returned and cached when a cursor is generated. In order to cache only a batch of rows an application must set the CacheAll property to false.

CacheAll

Type : Boolean

Default : true.

This property allows an application to prevent all results from being cached on the client side. By default this is true, meaning that all results are fetched from the server when a cursor is generated. Setting this to false will cache only a maximum of a BatchSize set of rows. This allows an application to save memory when large result sets are generated. This means that when moving past the end of the cursor’s current batch, using revMoveToNextRecord, the next batch is fetched from the server. Note, that an application can only scroll through the current batch of results.

InEncoding

Type : String

Default: ‘MacRoman’ (MacOSX), ‘Latin1’ (Win32 and Linux).

This property allows an application to set the encoding of strings passed into the driver. Since FrontBase stores strings in UTF8 the driver will perform any necessary conversion from the encoding passed from Revolution to that required by FrontBase. The driver currently recognises the following encodings. Use the OutEncoding property to convert strings returned from FrontBase.

UTF8

MacRoman

MacCyrillic

ASCII

ISOLatin1

ISOLatin2

ISOLatin4

ISOLatinCyrillic

ISOLatin5

ISOLatin6

ISOLatin7

ISOLatin8

ISOLatin9

OutEncoding

Type : String

Default: ‘MacRoman’ (MacOSX), ‘Latin1’ (Win32 and Linux).

This property allows an application to set the encoding of strings passed from the driver to Revolution. Since FrontBase stores strings in UTF8 the driver will perform any necessary conversions to the encoding required by the application. Use the InEncoding property to set the encoding of strings passed into the driver from Revolution. See the InEncoding property for a list of supported encodings.

InsertIndex

Type : Integer.

Default : -1.

This read only property provides the INDEX of the last row successfully inserted. Any other SQL statement sets this to –1.

TimeZoneName

Type : String

Default : Server’s timezone, e.g.‘US/Pacific’.

By default a FrontBase database connection uses the time zone of the server to apply a zone offset to TIMESTAMP and TIME values. An application can change the applied zone by setting this property to a valid time zone name, which the server recognizes, e.g. ‘US/Pacific’. If the server fails to recognize the zone name it will be reset to the current server time zone for the connection. If an application changes the zone name by explicitly sending a ‘SET TIME ZONE’ statement to the server and the value of TimeZoneName is not the same then it will be automatically updated when a new cursor is generated via revQueryDatabase. See Time Zones.

Time Zones

FrontBase has several datetime types, which incorporate the use of a time zone. These are,

· TIME WITH TIMEZONE

with format [+/-]HH:MM:SS[.ss]+/-hh:mm, e.g. 18:46:00+01:00

· TIMESTAMP WITHTIME ZONE

with format YYYY-MM-DD HH:MM:SS[.ss]+/-hh:mm, e.g. 2002-07-13 18:46:00+01:00

· TIME

with format [+/-]HH:MM:SS[.ss], e.g. 18:46:00

· TIMESTAMP

with format YYYY-MM-DD HH:MM:SS[.ss], e.g. 2002-07-13 18:46:00

A time zone represents the relative offset of the time from GMT and is used to calculate the GMT (or UTC) when stored. FrontBase needs to calculate the GMT value of a stored time in order to represent that time in another zone. The ZONEd types provide an explicit offset as part of their values and FrontBase stores this offset and uses it to calculate the GMT. The GMT of TIME and TIMESTAMP types is calculated by using the time zone of the database connection which stored the values. By default a FrontBase connection uses the time zone of the server to calculate the GMT value for a TIME or TIMESTAMP. A FrontBase database connection can set it’s own time zone by using the SET TIME ZONE statement, e.g.

SET TIME ZONEINTERVAL '-08:00' HOUR TO MINUTE;

or

SET TIME ZONE‘US/Pacific’;

will set the timezone to Pacific Standard Time. However, the FrontBase driver provides a TimeZoneName property which should be used to set the timezone. This is described in the Properties section.

When retrieving TIME and TIMESTAMP values from FrontBase the driver converts the value based on the current time zone. TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE columns are returned unchanged since they explicitly state the zone their values are based on.

Passing data to and from FrontBase

It is up to an application to ensure that values passed to FrontBase match the destination type. See the FrontBase Users guide,

http://www.frontbase.com/documentation/FBUsers_4.pdf.gz,

for a description of the supported column types.

CHAR/NATIONAL/VARCHAR

To pass a character value directly as part of a SQL statement it must be enclosed in single quotes. To embed a single quote into a string, which is passed directly, use two single quotes.

The driver will automatically quote the string if you pass this as a character parameter. To do this use a variable placeholder and add a suffix of ‘c’ or ‘C’ to this placeholder. For example, the following will insert the value of the variable my_char_var into the my_char column.

revExecuteSQL gConnectionId, "insert into t0 (my_char) values (:1c) ", "my_char_var"

A character value returned from the server will not be quoted. Values passed to FrontBase, which are longer than the defined length of the column, will generate a truncation error.

FrontBase uses UTF8 to encode all of its string data and the driver supports a number of character set conversions. Therefore, make sure that the InEncoding and OutEncoding properties of the driver are set to match the encoding you are using (See the Properties section for details).

TINYINT/SMALLINT/INT/LONGINT/DECIMAL/NUMERIC/FLOAT/REAL/DOUBLE

Values passed must be in the correct range and have the correct precision and scale where applicable. For example, a TINYINT ranges from –127 to 127 since this is a signed 8-bit integer. Any value out of range will cause a data exception error. There may be some rounding of values returned with fractional parts as these are passed as 8 byte double values on retrieval.

BIT/BIT VARYING/BYTE

To pass these values they must be either specified as a hex string or a bit string.

A hex string takes the form,

H’<hex_digits>’

where hex_digits is a string of hexadecimal digits with each digit occupying 4 bits, i.e. 0-f.

A bit string takes the form,

B’<binary_digits>’

where binary_digits is a string of binary digits with each digit occupying 1 bit, i.e. 0 or 1.

The values must match the bit length of the destination column or a truncation error will occur. On retrieval the column values will be returned as a hex string or bit string depending on their type and size. If the column is a BIT(n) then a hex string will be returned if n is a multiple of 8 bits, otherwise a bit string will be returned. The BIT VARYING and BYTE columns will always return a hex string. If a BIT VARYING column is not a multiple of 8 bits then its binary value is right padded with 0’s to the next byte boundary to form the resulting hex string.

DATE

To pass a DATE column value directly as part of a SQL statement it must take the form,

DATE ‘YYYY-MM-DD’

where YYYY is the year, e.g. 2004

MM is the month, e.g. 03

DD is the day, e.g. 24

The driver will automatically add the DATE specifier and quote the value if it is passed as a date parameter. To do this use a variable placeholder and add a suffix of ‘d’ or ‘D’ to the placeholder. For example, the following will insert the value of the variable my_date_var into the my_date column.

put "2004-03-26" into my_date_var

revExecuteSQL gConnectionId, "insert into t0 (my_date) values (:1d) ", "my_date_var"

A DATE value is returned as YYYY-MM-DD with no quotes or DATE specifier.

TIME

To pass a TIME column value directly as part of a SQL statement it must take the form,

TIME ‘[+/-]HH:MM:SS[.ss]’

where HH is the hour using the 24 hour clock, e.g. 13

MM is the minute, e.g. 42

SS is the seconds, e.g. 33

ss is the optional hundreds of a second, e.g. 54

The driver will automatically add the TIME specifier and quote the value if it is passed as a time parameter. To do this use a variable placeholder and add a suffix of ‘t’ or ‘T’ to the placeholder. For example, the following will insert the value of the variable my_time_var into the my_time column.

put "17:59:00" into my_time_var

revExecuteSQL gConnectionId, "insert into t0 (my_time) values (:1t) ", "my_time_var"

A time value is returned as +/-HH:MM:SS[.ss]with no quotes or TIME specifier. The hundreds are omitted if they are zero. Note that the time is converted to the connection’s timezone (see Time Zones).

TIME WITH TIME ZONE

To pass a TIME WITH TIME ZONE column value directly as part of a SQL statement it must take the form,

TIME ‘[+/-]HH:MM:SS[.ss]+/-hh:mm’

where HH is the hour using the 24 hour clock, e.g. 13

MM is the minute, e.g. 42

SS is the seconds, e.g. 33

ss is the optional hundreds of a second, e.g. 54

hh is the zone hour offset, e.g. 01

mm is the zone minute offset, usually 00

The driver will automatically add the TIME specifier and quote the value if it is passed as a time parameter. To do this use a variable placeholder and add a suffix of ‘t’ or ‘T’ to the placeholder. For example, the following will insert the value of the variable my_timewithzone_var into the my_timewithzone column.

put "17:59:00+01:00" into my_timewithzone_var

revExecuteSQL gConnectionId, "insert into t0 (my_timewithzone) values (:1t) ", "my_timewithzone_var"

A TIME WITH TIME ZONE value is returned as +/-HH:MM:SS[.ss]+/-hh:mm with no quotes or TIME specifier. The hundreds are omitted if they are zero.

TIMESTAMP

To pass a TIMESTAMP column value directly as part of a SQL statement it must take the form,

TIMESTAMP ‘YYYY-MM-DD HH:MM:SS[.ss]’

where YYYY is the year, e.g. 2004

MM is the month, e.g. 03

DD is the day, e.g. 24

HH is the hour using the 24-hour clock, e.g. 13

MM is the minute, e.g. 42

SS is the seconds, e.g. 33

ss is the optional hundreds of a second, e.g. 54

The driver will automatically add the TIMESTAMP specifier and quote the value if it is passed as a timestamp parameter. To do this use a variable placeholder and add a suffix of ‘ts’ or ‘TS’ to the placeholder. For example, the following will insert the value of the variable my_timestamp_var into the my_timestamp column.

put "2004-03-2617:59:00" into my_timestamp_var

revExecuteSQL gConnectionId, "insert into t0 (my_timestamp) values (:1ts) ", "my_timestamp_var"

A TIMESTAMP value is returned as YYYY-MM-DDHH:MM:SS[.ss] with no quotes or TIMESTAMP specifier. The hundreds are omitted if they are zero. Note that the value returned is converted to the session’s timezone (see Time Zones).

TIMESTAMP WITH TIME ZONE

To pass a TIMESTAMP WITH TIME ZONE column value directly as part of a SQL statement it must take the form,

TIMESTAMP ‘YYYY-MM-DDHH:MM:SS[.ss]+/-hh:mm’

where YYYY is the year, e.g. 2004

MM is the month, e.g. 03

DD is the day, e.g. 24

HH is the hour using the 24-hour clock, e.g. 13

MM is the minute, e.g. 42

SS is the seconds, e.g. 33

ss is the optional hundreds of a second, e.g. 54

hh is the zone hour offset, e.g. 01

mm is the zone minute offset, usually 00

The driver will automatically add the TIMESTAMP specifier and quote the value if it is passed as a timestamp parameter. To do this use a variable placeholder and add a suffix of ‘ts’ or ‘TS’ to the placeholder. For example, the following will insert the value of the variable my_timestampwithzone_var into the my_timestampwithzone column.

put "2004-03-2617:59:00+01:00" into my_timestampwithzone_var

revExecuteSQL gConnectionId, "insert into t0 (my_timestamp) values (:1ts) ", "my_timestampwithzone_var"

A TIMESTAMP WITH TIME ZONE value is returned as YYYY-MM-DD HH:MM:SS[.ss] +/-hh:mm with no quotes or TIMESTAMP specifier. The hundreds are omitted if they are zero.

BLOB

To pass a BLOB column value it must be sent as a parameter variable. The variable must be prefixed with *b to indicate it is a binary value. For example, the following will insert the data from my_binary_var into my_blob_column.

revExecuteSQL gConnectionId, "insert into t0(my_blob_column) values (:1)","*bmy_binary_var"

Data returned from a BLOB column must be directly passed into a variable. For example, the following will return the BLOB data from my_blob_column into my_binary_var.

put revQueryDatabaseBLOB(gConnectionId, "select my_blob_column from t0")into gCursorId

if gCursorId is not a number then

//error

else

get revDatabaseColumnNumbered(gCursorId, 1,"my_binary_var")

end if

CLOB

To pass a CLOB column value it must be sent as a CLOB parameter. To do this use a variable placeholder and add a suffix of ‘cb’ or ‘CB’ to the placeholder. For example, the following will insert the data from my_clob_var into my_clob_column.

revExecuteSQL gConnectionId, "insert into t0(my_clob_column) values (:1cb)","my_clob_var"

A string passed as a CLOB value does not need to be quoted and a returned CLOB value will not be quoted.

BOOLEAN

A boolean column value should be sent as a string (unquoted) containing the literal true or false.

Capturing SQL sent to the server

When tracking down a problem, it is often very helpful to watch the SQL statements processed by the server as you do things or run code on the client side.

To do this:

Using FBManager or FBWebManager on the server, restart your database with the “logSQL” option (see “Start Advanced”).

From a terminal window, watch the “.sql” file for the database. This file is located in FrontBase/Databases, and has a name in the form“<database_name>.fb.sql”. For example, on Mac OS X, you can watch the database “TestDB” using the command:

tail -f /Library/FrontBase/Databases/TestDB.fb.sql

The SQL commands will be printed to the terminal as the server processes them.

Reporting Bugs

If you think you’ve found a bug in the FrontBase driver, please do the following:

1. Create a small Revolution application that exposes the problem. Write instructions for replicating the problem. Note versions of Revolution, FrontBase and driver.

2. Capture a SQL log if the problem is with generated SQL.

3. Stuff library, description, and log and send to FrontBase Support.