13.2.1 Database Connection
DEFINE_DB_SERVER can be used in isolation from the other BIFs to just override the connection parameters and database type or it can be used with the full set of related BIFs in this sequence:
DEFINE_DB_SERVER, CONNECT_SERVER, CONNECT_FILE.
The full set of BIFS is only needed when the OTHER File is in a database with a different DSN to the one with which it was loaded.
To connect to all the default databases on startup, that is all the databases defined in the OAMs, it is necessary to execute DEFINE_DB_SERVER (with database type specified) and CONNECT_SERVER for each database using the DSN that is in each OAM. CONNECT_FILE is not required if the DSN is the same as in the OAM. That is, an OTHER File is implicitly connected to the database from which it was loaded.
If your environment has a development, test and production version strategy, the simplest way of managing the differing locations of Other Files as the application passes through the various stages, is to use the same ODBC DSN but alter the definition of it to point to a different physical database. Thus, the default database embedded in the OAM will access a different physical database.
It may seem a simple thing to switch databases at will, but its not if any OAMs are shared. That is, if you are using the same file in multiple databases. When switching a database and there are shared OAMs you must close every file that has been used, including Code-File Lookups, triggers, etc. Its easy to miss a file. If you don't do this the original database will be accessed. That is, the database is set when the File is opened. After that, all IO will go to the original database. When using a single form, a CLOSE is all that is required to close all the files that have been used. When multiple objects are used, its far more complex. Its essential that all Components and Functions exit back to the initial Component/Function, ensuring that any HEAVYUSAGE objects call CLOSE before returning. In fact using LIGHTUSAGE Functions and Dynamic Components everywhere may be the best solution. The following is an example of the code that would need to be executed in the initial component if that component accesses any files:
Subroutine Name(SwitchSRV) Parms(#SwitchSRV)
Define Field(#LastSRV) Reffld(#SERVER1)
Close
If ('#Switchsrv *NE #Lastsrv')
If ('#LastSRV *NE *BLANK')
Use Builtin(Disconnect_file) With_Args(* #LastSRV)
Endif
Use Builtin(connect_file) With_Args(* #SwitchSRV)
Endif
#LastSRV := #SwitchSRV
Endroutine
Note that if you have multiple databases connected with files under commitment control, a COMMIT or ROLLBACK will commit or rollback all transactions on all databases.