Identifying the Database
You tell CopiaFacts which database you want to work with by assigning a value to the system variable DB_FILE using the following format:
$set_var DB_FILE "PFX%=database_ref[table|DSN|stored procedure|*DUMMY*]"
The left side of the equal sign is the table or procedure identifier. This is a unique value that will be prefixed to the field names of the table or procedure results to ensure that the field names will be unique within the user variable name space in CopiaFacts. The percent sign is not required and the table/procedure ID may be any number of characters. However, using the percent sign in the ID usually helps reduce search time in the user variable space for variable references. It is recommended that you should assign a prefix string which allows you to identify the database. For example, you might use CUST% to identify a customer database.
The right side of the equal sign contains the database reference. This may take several forms. The most common would be the full path name of the file that contains your database, such as:
$set_var DB_FILE "CUST%=D:\My Files\Customers.dbf"
CopiaFacts would examine this reference and determine that the database reference named an xBase type file and would establish a connection using the dBase option of the Jet provider. If you wanted to connect to a Microsoft Access database, you might use a database reference such as:
$set_var DB_FILE "ORD%=C:\My Files\sales.mdb[Orders]"
In this case you must also specify a table name in square brackets following the file name because Microsoft Access databases can have multiple tables. You would also need to specify a table name for SQL Server.
If you were specifying an ODBC data source, you could specify the name of the system or user DSN as follows:
$set_var DB_FILE "DSN%=MyDSN.DSN"
The .DSN suffix is not necessary, since CopiaFacts will assume that you are specifying an ODBC data source. This data source must be defined in the ODBC data source manager. If you wish to use a file DSN, then you must specify the complete file name, including the .DSN extension as follows:
$set_var DB_FILE "DSN%=C:\My DSN Sources\MyDSN1.DSN"
You may need to name a table in square brackets as well, depending upon how your data source name is defined. You may also use an ODBC driver string as your database reference as follows:
$set_var DB_FILE "PFX%=DRIVER=…"
The format of the string will depend upon your ODBC supplier requirements. Keep in mind that ADO is based upon OLE DB. As such, it will call the OLE DB provider for ODBC if you use ODBC connection strings. This adds another level of abstraction to the database calls and will affect performance. When possible, use the correct OLE DB provider for your database. We recommend using UDL files whenever possible for storing connection information. Not only does this provide you with a flexible means of accessing your database, but it is also much easier to select the proper provider and also allows testing of the connection before you make use of it.
Use the literal value *DUMMY*, or variations of that constant value that begin with the sequence *DUMMY and end with an asterisk, in place of a table or procedure name when you wish to execute SQL queries that do not reference a particular table in the database or may reference multiple tables or use DDL statements. Use of a specific table name restricts data retrieval to the fields defined for that table in the catalog or database schema. Queries that are made using the dummy table reference will return all field values from the resultant record set. The field names from the result set will be used for the variable names returned to CopiaFacts. The use of one of these ambiguous table names also bypasses additional table name and field/column name validation checks.
Notice that there are no provisions for user ID or password. You may include these in your DSN sources if necessary. You may also set these parameters in a Microsoft universal data link (.UDL) file as well. In this case you just name the universal data link file in your reference and the table as well if necessary:
$set_var DB_FILE "PFX%=C:\My Links\My Link1.udl[Table1]"
You must identify your database before proceeding with any of the other database processing. You can determine if your assignment was successful by checking the system variable DB_RESULT. It should be zero if the assignment was successful. Otherwise, it will contain a negative number that indicates what error occurred. Refer to the list of error codes for more information.
If you are using Clarion's Topspeed database, you must have the latest ODBC driver and you should set up a data source that identifies the Topspeed ODBC driver and the folder where your Topspeed files are located. Your database reference should then be assigned as follows:
$set_var DB_FILE "PFX%=C:\Topspeed Files\MyTable.tps[TPSDSN]"
In this case the DSN name is placed in square brackets instead of the table. If you will be executing a stored procedure, you must identify the stored procedure within the square brackets instead of a table name. For example, your assignment statement might look like this:
$set_var DB_FILE "PFX%=C:\TTestSQL\test.udl[SP=sp_MyProcedure]"
You must identify the procedure using the SP= sequence preceding the procedure name. Do not include any parameters with the name.
It is important to remember to store user and password information with your connection information source. If you use the Microsoft Data Link editor to create a data link (.udl) file, then you must remember to check the allow saving of password box. This prevents any connection prompts from appearing on the fax server while doing database operations. The fax server is unable to respond to these messages, which could cause your application to hang.
You may assign as many tables or procedures as you wish using the DB_FILE command. When you use a DB_FILE command, the table or procedure that you assign becomes the active table or procedure for that line. Any subsequent commands are processed against the active table or procedure. You may change the active table or procedure by using the DB_SELECT command as follows:
$set_var DB_SELECT "ACTIVE=PFX%"
where PFX% is the table, query, or procedure prefix you assigned to the table, query, or procedure in a previous DB_FILE assignment statement. All of your assignments and connections are automatically closed when the line is reset. So you do not need to be concerned about closing any active resources specifically.
You should, however, be aware that each assignment represents a possibly unique connection to the database. If your database connections are limited by your licensing, you may exhaust connections during processing. Connections may be shared using connection pooling, which is set in Windows by default. Connections will remain available after being released by a line for about 60 seconds, which is the default. You can change this option in Windows.
Be aware that the DB_FILE command also establishes connection information and field names and types for any specified tables. If you change the structure of a table after making an assignment using the DB_FILE command, the software will not adjust its internal field references. Therefore, you may not be able to read and update fields in the table correctly until you restart CopiaFacts.