FRONTBASE FAQS
FRONTBASE FAQS

Stored Procedures (5)

This FAQ group seeks to explain how to use stored procedures in FrontBase.

How often should I backup my database?

The key is to determine a schedule such that it will be possible to recreate your data at least from the last backup. For example, if you work with a database that is updated daily, which most people do, then you should backup daily to avoid any possible data loss. If you feel the need to backup more often than daily then this process should have some sort of value system applied to it. In other words, how much is my data worth an hour or per half day?

Which backup method should I use?

When you have determined the required frequency of backing up you will need to think about how to do it.There are several options. You can backup live so the database can continue to be updated during the process. Creating backups with the transaction logging option included ensures a backup right up to the problem point/catastrophe. You can also simply copy the database files using your own backup utility and store the files in your chosen location. Or finally, you can export an entire database into flat-files from which the database can later be rebuilt. The implications of adopting each of these methods are outlined in detail below. The question of "How critical is my data" should also be applied to this choice.

  1. Make a live backup with transaction logging enabled
    This method would guarantee integrity of data up to the potential problem point. A backup file of the database and an associated .tl (transaction log) file for each subsequent transaction since the backup was made would enable one to rebuild the database using the 'Restore' -rollforward option via the command line as root. You can determine which .tl files belong to which backup file by issuing the 'ls -l' command in the backup directory for the database and work from the dates/times of the files. Of course this all depends on the nature of the catastrophe, as the directory containing the backup files has to still exist! Please be aware that the WRITE ALL touches all tables in your database so you need to be aware of the current transaction settings (try and use the VERSIONED isolation level when doing WRITE ALL
  2. Copy the <database name>.fb and associated files
    Depending on how often you backup, this method will require that you first stop the database(s) and copy the contents of the Frontbase/Databases directory providing a snap-shot of the database in its current state. Data between backups could be lost.
  3. Export to flat-files
    Basically the database can be rebuilt back to its exported state.

    In the future, however, you could potentially improve this by executing the <database name>.fb.sql file to essentially replay SQL statements logged, if the database was started with the -logSQL invocation option, up to the problem point. A future FrontBase tool will make this possible.

Can I automate the backup process?

Yes. For example, you could use the 'Cron' utility on Linux/Unix and SQL92 for this.

Where do I get a license string from?

You can apply for a license directly from the Buy->License; section of www.frontbase.com. Licenses are sent automatically via e-mail. Alternatively developers can contact license@.frontbase.com for a license.

How do I install a license string?

There are three ways to install the license string:

  1. If you are running FrontBase on a platform that includes FrontBaseManager, you can install it from there. Choose License management from the Tools menu, select Edit license and paste inthe license string and license check that were e-mailed to you.
  2. If you have FBWebManager running on the server, connect to it with your web browser and click License in the left column. Paste the license string and license check that were e-mailed to you.
  3. Although not highly recommended, you can create the license file yourself using a text editor. The file is called LicenseString and resides in the main FrontBase directory. Its format is as follows:

<64 char license string>:<16 char license check>

Note the colon (:) between the license string and license check. The file is just one line - there is no carriage return after the license check.

Ensure that you license FrontBase prior to starting any databases so the string applies to all databases.

Can I use the license string on different machines or platforms?

The license string is generated against the IP address or MAC address (Ethernet) of your server. If you move FrontBase to another machine with a different IP or MAC address, FrontBase will not work. FrontBase will compare the IP address of the host it is running with the license file. If there is a problem/mismatch, it will switch into the free license mode.This entails that a database will become inaccessible after two months and a number of features cannot be enabled. Also, if a license is generated against one OS version of Frontbase, e.g. Win2000, it is not transferable to a different OS such as Mac OS X using the same IP address on the same machine.

Can I install multiple copies of FrontBase on one machine?

You can install multiple copies of FrontBase on one machine and there should not be a problem using the same IP for these.We currently place the installation in /Library by default and install some other files in the Web Server area. You can freely move /Library/FrontBase around if you wish. However, if you should choose this route, you do need to be sure that you always run with the correct FBExec, the one from the same directory as FrontBase.

What happens if I change the IP address on my server?

If you change the IP address on the server you will need to obtain a new license based on the new IP address.

Can I run FrontBase without a license string?

If you do not enter a License string, you can only use the server, for evaluation purposes, for two months. After this period the server will not start and you will need to contact license@frontbase.com if you wish to continue using FrontBase.

Does the license string restrict the number of databases I can have running and is there a limit to the number of users I can have connected?

All FrontBase licenses allow unlimited connections and unlimited users. You can run as many databases as you like from a single installation of FrontBase.

How do I upgrade my license string?

You can obtain an upgrade license string by applying to license@frontbase.com. In order to upgrade the server one would simply re-license in the way described above.

What are the basic features of the different FrontBase licenses?

For details of the different licenses please see the 'Buy->Licenses' section on the FrontBase website at: www.frontbase.com.

What are Stored Procedures?

Stored procedures are basically server-side collections of SQL statements and flow control directives that are verified, compiled, bound and stored by FrontBase. They allow for input parameters, output parameters, declared variables and conditional execution of any commands or statements.

Using stored procedures can enhance both the performance and functionality of FrontBase in some contexts. They can improve the performance of SQL statements (in static and dynamic mode of operation) by eliminating preprocessing overhead. They also reduce network traffic considerably by eliminating the need to send SQL statements from applications to FrontBase servers. In general, a stored procedure is executed in a fraction of the time it takes to process a single embedded SQL command.

When are stored procedures used?

Stored procedures are often used to enforce business rules and data integrity, and to perform system maintenance or administration functions and to extend the FrontBase server's functions.

Are there any disadvantages in using stored procedures?

Stored procedures are a non-standard concept. Different vendor implementations have different standards and they are not usually portable across platforms. It must be considered that using stored procedures and functions will make it more difficult for you to be database server independent in general.

How do I create a Stored Procedure in FrontBase?

When it is necessary to use stored procedures the process within FrontBase is simple.

To create one you could use the FBManager user interface or SQL from the SQL92 command line tool.

To create a stored procedure in FBManager:

Select a schema in the 'Editor' tab pane
Choose the Management>>Create>>Procedure menu item.
The 'Create schema object' window will appear after which point you can name the procedure, fill the body in, specify parameters and edit it.

Using SQL92 one could create and use a very simple stored procedure with the following statements:

create procedure my_proc()
begin
   select * from my_table;
   select * from my_table2;
end;

To run this procedure you would need to execute:

call my_proc();

I'm migrating to FrontBase but how do I convert my stored procedures?

Although it is not possible to actually transfer your stored procedures across directly you will be able to replicate what you already have in FrontBase by creating new stored procedures as described above.

I have been using FrontBase unlicensed for 2 months but now I cannot access my database, what should I do?

In this situation you will receive the error:

Connection error 030. This database can no longer be used without a license.

If the database has been used for more than 60 days with an unlicensed version of FrontBase you will receive this message when you try to connect. You will need to license FrontBase in order to rectify the problem. Another cause of this error could be that the license has expired, check the date in the license with the date on the machine it is used on.

How does it actually work?

The black/white list system works much like most firewalls. The rules are processed sequentially. The first rule that matches in the negative (black listed) blocks access. The first rule that matches in the positive (white listed) grants access. The default rule:

IP: 000.000.000.000, netmask: 000.000.000.000, White list: YES

grants access to all hosts.

As an example, imagine that you have a Class C network, 198.151.161.0 (netmask 255.255.255.0). If you wanted to grant access only to machines within that network, you could add this rule:

IP: 198.151.161.0, Netmask: 255.255.255.0, White list: YES

Once you've added the above, you'd also have to remove the default rule, since it grants access to everyone.

Now let' say that you want to grant access to another host on another network. Let's say that the IP of the machine you want to add is 17.254.0.91. So you add this rule:

IP: 17.254.0.91, Netmask: 255.255.255.255, White list: YES

Notice that the netmask is 255.255.255.255 this time, which matches against the full IP rather than the Class C network address.

Let's use another scenario. Say you want to grant access to everyone except those people in 148.87.*.* (ie., 148.87.0.0, netmask: 255.255.0.0). You would want these two rules:

IP: 148.87.0.0, Netmask: 255.255.0.0, White list: NO
IP: 0.0.0.0, Netmask: 0.0.0.0, White list: YES

Remember that order is important.

Is there any chance of resetting the black/whitelist of a database? I misconfigured my database's black/whitelist and don't even have access to it on that host.

If you make a mistake and lock yourself out, you can run the database with the -localonly option, and reconfigure the black/white list using FBManager. Start FrontBase with the -localonly option as detailed below:

Syntax: -localonly

By specifying this option, a Frontbase database will only accept connections from clients running on the same computer as the database. The default is to accept connections from networked as well as local clients.

MacOS X / Server specific issues:

I cant get the license string into Frontbase. The Licensor says "Cannot connect to FBExec on host myhost: No such host". My computer is called myhost in /etc/hostconfig so I dont understand what's going on.

This is usually a network configuration issue.

Is myhost in NetInfo? If it isnt, then FrontBase won't be able to translate myhost to an IP address. Basically you can just duplicate localhost (that's a folder in /machines) and change the name to myhost and map the ip address by specifying it there.

This will affect everyone who is on a LAN going through a NAT router which is mainly for users on home networks who may not have manually entered their computer names into NetInfo but simply had ip's like 192.168.1.199, etc....

I'm getting some unexpected results with the time zone values returned from the server. What causes the problem?

The TIMESTAMP WITH TIME ZONE is a problem when doing anything that has to do with the JDBC API since it only supports TIMESTAMP and, for example, EOModeler gets its external data types from the JDBC driver. The JDBC API uses UTC values but always displays them in the client's time zone. Check out JDBC API for full details. Because of the way time zones are handled, WITH TIME ZONE data types are not supported by the JDBC API as it requires the specific time zone stored with the time or timestamp to be maintained on the client.

The data type TIMESTAMP WITH TIME ZONE was available in the pop up in EOModeler a few releases back however it was not supported.

We have made an extension to the FrontBase JDBC driver that can handle WITH TIME ZONE data types and an overall solution to the problem would be do a similar thing in the WO5 adaptor. This is, however, for the WO5 engineering team to decide upon. We have notified the WO5 engineers about the problem and hope for a solution. Meanwhile their suggestions to alternative solutions are:

1) Store your time zone information in a separate column.

2) You may be able to use a custom value type for your attribute so that you can translate the TIMESTAMP WITH TIME ZONE into a string representation that your database would support as a literal value.

If none of these possibilities are really satisfactory, and if it is possible, we would recommend that you use the ordinary TIMESTAMP data type for the interim.

Do I need the FrontBaseEOAdaptor framework with WO5?

FrontBaseEOAdaptor.framework is not used by WebObjects 5 at all. This framework is only used by the other versions of WebObjects e.g. 4.0 and 4.5.1.

I used to have problems with reserved words such as 'DESCRIPTION' in 4.5 because it is an Obj-C reserved word. What about WO5?

In WO5 EOModeler is still an Obj-C application and therefore the reserved word problem still exists here. However it won't be a problem during java run-time when deploying your WO5 application since it is all Java at this point.

A workaround is simply to rename the fields to, for example, '_DESCRIPTION'.

The WebObjects 5 / FrontBase combination doesn't seem to work on Mac OS X 10.1

Using WebObjects 5 on Mac OS X 10.1 requires that the FrontBase JDBC driver, which is installed with the FrontBase WO5 plugin, be relocated. The following command will provide for this relocation (Note: you should be logged in as root or alternatively prepend the 'sudo' argument to the command to execute as super user):

cp /System/Library/Frameworks/JavaVM.framework/Versions/1.3/Home/lib/ext/frontbasejdbc.jar /Library/Java/Extensions

This anomaly will be fixed in our future plugin releases.

Can we deploy WebObjects 5 / FrontBase applications to Linux?

We do not yet have a WebObjects 5 plugin that ships with our Linux distributions but this is something that is 'in the wings' so watch this space!

We do, however, already have a JDBC driver that requires Java 2 SDK and JDBC 2.0 API. It is in the download section at www.frontbase.com. In addition to this, in a deployment situation, you would be able to use the jar file from the FrontBasePlugIn.framework since at this point it is all java. So, adding the JDBC driver and the FrontBasePlugIn.jar to the java CLASSPATH environment variable will do it.

Which FrontBasePlugIn.jar does one use for linux?

You can use the FrontBasePlugIn.jar from the WebObjects 5 plugin for MacOS X. Just remember that it has to be the one from FrontBasePlugin.framework.

You will see there is also a file called FrontBasePlugin.jar in FrontBasePlugin.EOMplugin. This one, however, is platform specific. The EOMplugin is actually only used by EOModeler.

I thought that EOModeler used a different version of the JDBC API to the Java run time but both EOModeler and the Java run time use the same JDBC driver.

They do. The way it works is that the plugin for EOModeler is built specifically to use only the parts of the 2.x JDBC API that are compatible with the 1.x JDBC API.

I receive an error when attempting to connect to my db via EOModeler with the following URL format:

jdbc:FrontBase://localhost/mydatabase

Error: JDBCAdaptorException: SQLException raised when connecting:
java.sql.SQLException: Error during createSession

In FrontBase a user name is always required in order to create a connection. If no user is created you should use _system. In this case the URL should be:

jdbc:FrontBase://localhost/mydatabase/user=_system

Check that you have not mistyped the username or passwords. To make sure the WO Application knows the plugin just execute the following:

setenv CLASSPATH /Library/Frameworks/FrontBasePlugIn.framework/Resources/Java/FrontBasePlugIn.Jar

This should get things moving in the right direction.

I can connect ok but I receive the following error when I attempt to run my application:

com.webobjects.jdbcadaptor.JDBCAdaptorException:
SQLException raised when connecting : java.sql.SQLException: No suitable driver

It could be that the application is not 'aware'of the plugin. Your actual problem might be that you have not added all necessary frameworks to your project. Look under External Frameworks. You need to have the JavaJDBCAdaptor.framework and the FrontBasePlugIn.framework added to your project. The latter is installed at /Library/Frameworks/.

You have to actually add the FrontBasePlugIn Framework to your AppServer target. Just adding to your target (name=projectname) is not enough.

If it is a pre-built application the FrontBase plugin is not linked directly into the application. Therefore the plugin and JDBC driver must be in the CLASSPATH environment variable. Try to execute this line before running the application.

(MacOSX v. 10.1)

setenv
CLASSPATH /Library/Java/Extensions/frontbasejdbc.jar:/Library/Frameworks/FrontBasePlugIn.framework/Resources/Java/FrontBasePlugIn.jar

(MacOSX v. 10.0.x)

setenv
CLASSPATH /Library/Java/Home/lib/ext/frontbasejdbc.jar:/Library/Frameworks/FrontBasePlugIn.framework/Resources/Java/FrontBasePlugIn.jar

The reason for this is that the plugin is not built into the JavaJDBCAdaptor.framework so it is necessary to specify the location of it.

When I try to connect (either through saveChanges() or a fetch) to my new FB database, I get an error. However, I can connect fine in EOModeler - all I put was "jdbc:FrontBase://localhost/TestDatabase/user=_SYSTEM" in the URL field in the connection panel.

It could be that the connection dictionary of your EOModel is corrupted.

Try a "Switch Adaptor" in EOModeler. This will restore the data type information in the connection dictionary. If this doesn't rectify the issue then it might be worth a mail to support@frontbase.com.

I'm porting my WO4.5 Application to WO5 but now when I try to insert a new EnterpriseObject with an EOF generated Global Unique Id as the primary key (BYTE(12)) I get an exception. I use NSData for the ID column with a SQL data type of either BYTE(12) or BIT(96)...

Enterprise object primary key generation:

The "12-byte" method of automatically generating primary keys for EOs is gone. It is replaced by a 24-byte version.

Solution - you now have to use BYTE(24).

Can I use the WebObjects 5 / FrontBase combination on Windows 2000/NT?

Yes. FrontBase 3.2 now ships with an installer for the Windows version of our plugin for WebObjects 5.

When I run my database I get the following error:
License problem detected: No license for actual platform
What does this mean?

The license is for a platform that you arent currently running on.

When I try to use the 'write all output' command I get a 'License error 404. Option - Export/Import - is not licensed' error. What does this mean?

This error indicates that FrontBase has not been licensed for export. In order to export you will either need a commercial license, such as an E-Business, or a free Developer license. You will also need something similar for the subsequent import.

Can I export a database with WRITE ALL OUTPUT and then import it on another platform?

Yes. When you perform a WRITE ALL OUTPUT you will be exporting into a flat file that can be subsequently imported on any supported platform

Can I export only the 'Meta data' i.e. none of the actual data?

Yes. You have a choice of either including a YES or NO argument as follows:

WRITE ALL OUTPUT ('/library/temp', 'YES')
WRITE ALL OUTPUT ('/library/temp', 'NO')

When you specify 'NO', none of the actual data will be exported.

Can I export a FrontBase 2.x database and import into FrontBase 3.x using WRITE ALL OUTPUT?

Yes. This is the method for upgrading your databases to FrontBase 3.x. Note: 3.x databases are not backward compatible.

When I try the 'Execute File' button in FrontBaseManager and point it to my schema.sql file, I get an error that says the data file cannot be opened.

In the schema.sql file there is an INSERT statement for the (for example) 2_22 file. The path name in that INSERT statement will need to be adjusted.


Creating and editing stored procedures is also outlined in the FrontBase Users Guide available from our homepage www.frontbase.com.

Some good information pertaining to Persistent Stored Modules (as they are referred to in SQL92) can be found in a highly recommended book by C J Date and Hugh Darwen - 'A guide to the SQL Standard' pp. 453 - 493. If you have any other questions please mail us at support@frontbase.com.