4C External Database Files

4C External Database Files

4C Server version 3.0.4 and higher supports better access to external databases. The current list of supported platforms is: Other platforms will be supported for Oracle, PostgreSQL, and Sybase as long as access is provided to a system with the correct C compiler and Database libraries. I will try to stay current with the latest releases of Sybase, Oracle, and PostgreSQL.

For the most part, earlier versions of the 4C shared libraries should work with later versions of the same database. However, you must make sure that the versions of the 4c shared libraries are compatible with the 4c server version. This means that both Version and Release numbers must be the same. You will get an error when you try to connect to the database if 4C detects an incompatibility in the versions. Other Databases may be supported in the future.

With the exception of the 4codbc libraries which are included on the NT 4csrvr install, the 4C database libraries are available as separate installs. The installs are available under the 4C ExtDB Libs link on the 4C Software Downloads page. They are all tarfiles with a README file describing the install procedure. You should keep the 4CSrvr and 4CExtDB installs in sync. Only use Release 4CExtLib with Release 4CSrvr. Only use Beta 4CExtLib with Beta 4CSrvr.



Bootstrap Files

There are two new bootstrap files that define the external database to 4C. They are:

Bootstrap Programs

There are 2 new bootstrap programs that let you view and change the information in sys.file_type and sys.ext_type. They are:

System PCLs

There are three system PCLs used for managing transactions. You don't have to use them as 4C will make reasonable choices for starting, committing, and rolling back transactions. But, in some situations you may prefer controlling the transactions yourself. The three PCLs are:

Getting Started

Before you can use 4C to access data in an external database, you must have the dbms installed correctly and have at least one database created. You will need to know the database name and a username and password. Once you know this, add a record to the sys.ext_type file using the sys.et.fm program. Don't forget to specify the details. At the very least you will need to enter some type of database identifier, user name, and encoded password. The encoded password can be obtained using the encode4c program.

To specify that a 4C file is a particular external type set sys.fh_access to "External" and then set sys.fh_exttype to the correct application defined external type. If you change the external type of a file, the file will need to be recompiled. If you change the file type of an external type you do not need to recompile the affected files but you may need to exit and reconnect to a 4csrvr.

Object names known to the database may be different than the names known to 4C. For both file names and field names, 4C changes all '.' characters to '_'. Thus a file named resp.party will be named resp_party and a field named rp.code would be named rp_code.

4C also maps dimensioned fields to multiple single dimensioned fields. So a field defined as rp.date[4] would be defined as 4 separate fields in the database. The fields would be named rp_date_0, rp_date_1, rp_date_2, and rp_date_3.

4C fields that are shared are only defined once in the database.

Note: The changes noted above only affect programs outside of 4C. You still need to refer to fields and files by the names known by 4C, but when you access the database from a different program, you need to be aware of these changes.

Converting Files

Before converting files you will need to verify that the data in the file is usable by the database. The program xlvflds will help you do this. Before you run this program you need to understand how 4C will create the file definition to the database. Please note that this is the way 4C will create the database table definition if 4C creates it. If you create the database definition in another way you will still need to have a 4C file definition. 4C will be able to map field types in the database to and from a 4C datatype. Some of the allowable mappings are: When you run xlvflds you may see some of the following errors or warnings.
  1. The precision of a 4C Alpha field is too small for some of the data in the file. You can fix this by doing one of the following.
  2. A 4C Date field is too small.
  3. A 4C Date field is too big based on the '-D' option of xlvflds.
  4. A 4C Time field is too small.
  5. A 4C Time field is too big.
  6. A 4C Float field is too big for its precision and scale. You can fix this by doing one of the following.
  7. A 4C Float will be rounded when converted. This is a warning only and the rounding that will occur may be acceptable. You can fix this by doing one of the following.
  8. Possible garbage in an alpha field. This is a warning only and the data in question may be acceptable.
It is usually preferable to change the SQLType, Precision, and Scale of a field than the display format because changing the default display format will affect all 4C programs that use the default display format.

Once you are sure the data in the 4c file is acceptable, then you can go about converting it to a database file.

There are several methods you can use to convert files and the one you use will depend on your own personal preferences as well as the quantity of the data you need to convert and how often you need to do the conversion. Some possible conversion methods are outlined here. Once converted, you will need to change the file definition to specify External access and the correct external type. Recompile the file definition and any programs that use that file.

Transactions and Record Locking

Another important thing to understand is how 4C will deal with transactions and record locking. All 4C accesses of a database are part of a transaction. These transactions are started either implicitly by 4C or explicitly by the application calling sys.begin().

Implicit transactions are used whenever no application started transactions are in effect. The boundaries of an implicit transaction depend on the setting of CommitType in the external type detail.

Explicit transactions are those started by a call to sys.begin(). Each explicit transaction must have it's own database connection, so if you nest transactions you will have multiple database connections active. You should try to call sys.commit() or sys.rollback() for each call to sys.begin(). However, if you don't 4C will try to do the right thing. If you neglect to commit a transaction that you started with sys.begin(), 4C will eventually commit the transaction if the program exits with a 0 exitcode, or 4c will rollback the transaction if the program exits with a negative exitcode. The transaction will also be rolled back if 4C reaches a state earlier in the program than when the transaction was started. This is possible if the EFldPCL of an input field starts a transaction and the user cancels before the application calls sys.commit().

Even when T_AUTOCOMMIT is specified with sys.begin(), you still need a matching sys.commit(). In this case, even though each sql statement has already been commited, the sys.commit() serves to mark the end of a set of statements that are each auto commited. Without the sys.commit(), you would probably leak database connections and this is very expensive.

A 4C transaction may involve multiple files spanning multiple databases. By allowing multiple connections to the database 4C allows you to have multiple concurrent transactions. The transactions can be parallel or nested. A nested transaction is a transaction started by a program that already has running transaction or by a program that has an ancestor with a running transaction. Parallel transactions are transactions in separate 4C groups or in linked 4C programs.

A 4C transaction may involve more than 1 database connection. Calling sys.commit() will commit the transaction on each connection, but it is possible that one connection could die and not commit the transaction while the other connections commit it. If you need to depend on a transaction that spans multiple databases to either fully commit or fully rollback then you need to use the database management facilities for this and not the 4C transaction management.

4C transactions have no effect on non database files such as JISAM or CISAM.

The underlying database system may choose to rollback a transaction due to deadlock conditions or some other database specific issue. If a transaction is rolled back any call to access or update data in a file involved in that transaction will fail and sys.errno will be set to ABORT_ERR. If sys.commit() is called, it will also fail and sys.errno will be set to ABORT_ERR. sys.rollback() will succeed on an aborted transaction. Once sys.commit() or sys.rollback() has been called, the application will be able to run another transaction.

If an explicit transaction is aborted, then all pushed programs since the start of the transaction will be killed and will exit with a -3 exitcode. The program that started the transaction will continue from where it left off.

If an implicit transaction is aborted 4C does not even know which program started it, so it cannot exit pushed programs. In this case the current program continues running.

4C as a default will try to lock records so that 2 users trying to modify the same record at the same time will be prevented from doing so. It does this by aquiring update locks from the database when it first accesses the rcd in modify mode. You can tell 4C to not try to aquire locks in this manner and just let the database grant the locks as it sees fit by specifying no for UseRcdLocks in the external type detail definition. You should be careful about using this though since it will be more likely that a sys.upd_file() will fail rather than the sys.read_file(...,F_LOCK)

This may be acceptable if you are doing all of your own transaction management, but if you are letting 4C do it, it will probably not be acceptable.

Another issue that will affect concurrency is the isolation level of your transactions. You should use the lowest isolation level that is acceptable to your application, especially if you are using 4C transactions and 4C rcd locking. The sys.et.fm program allows you to specify a default isolation level for transactions. Use the IsolationLevel field for setting the default isolation level rather than one of the InitStrings. If you don't specifically set an isolation level, then the DBMSs default isolation level will be used. You can overide the default isolation level for individual trannsactions with one of the flags in sys.begin();

See your DBMSs documentation for more info on isolation levels. 
<!--
sql direct

possible problems
        drop table outside of current process when it has it open
        updating the current rcd in DRSel

F_NODROP

stored procedure syntax



sys.sql_execute



-->


OS Specific Issues



Database Specific Issues



Miscellaneous



Miscellaneous





Troubleshooting



On some systems it may be necessary to set the LD_LIBRARY_PATH or SHLIB_PATH to include the directories where the database stores it's run time libraries. It is not necessary to include the HOME_4C/lib directory in this path since 4C knows where to look for it's own runtime libraries.

You can save the SQL statements that 4C sends to the DBMS by specifying an SQLFileName and SQLDebugLevel in the external type detail definition. Use 1 for the SQLDebugLevel. Be careful though, this file will not usually be removed or truncated and will eventually get quite large and slow down your system. Make sure SQLDebugLevel is set to 0 for normal use.

If you need very verbose debugging info you can set the SQLDebugLevel to 9 and use the dbg library instead of the rel lib. Any time you are using the dbg library, lots of stuff will be written to the file specified by DbgFileName. This file also will get large and not be removed or truncated. Make sure you always use the rel lib during normal use. You can switch between the rel and dbg libraries by setting the correct symbolic link.

Back to Top