Database Open/New

You can use this function to open a geological or blast database, or to create a new one.

To run this function: Choose Database > Database > Open/New, or Design > Blast design > Database > Open database, or Design > Blast design > Database > Create blast database, or...

In the Function Chooser, type DHL DATABASE DEFINITION, and press ENTER.
In the Function Chooser, type BLAST OPEN DATABASE, and press ENTER.

Note: If you are already connected to a database the Select database form is not displayed. You must close the database first.

Fields on the Select database form

Field	Description
Database Name	The name of the .ddb file you will open, or which you will create. There are several formats of database that you can use with Surpac, so Surpac uses the .ddb file to connect to the database.

If this is a new database, you are prompted to create a definition for the new database. When you click Apply, the Choose database type form is displayed.

Fields on the Choose database type form

Field	Description
Database Name	The name of the .ddb file. Read only.
Database Type	Type of database engine for the database. Available types are: paradox - Creates database files compatible with the latest version of Borland Paradox, and Paradox for Windows, that GEOVIA supports (which is version 5). paradox5.0 - Creates database files compatible with Paradox 5. odbc - Connect to a database through ODBC. access - the latest version of Microsoft Access that GEOVIA supports (version 2010). access97, access2000, access2007, access2010 - These options allow you to set the database engine to a specific version of Microsoft Access. Notes: If you install a 64-bit version of Microsoft Access and then you use Surpac 6.3, or an earlier version, with an .accdb file, Surpac fails to open the database. To solve this problem, you can either use Surpac 6.4, or save the .accdb file to an .mdb file. technical details The problem occurs because 64-bit Access installs 64-bit drivers for ODBC and OLEDB. Surpac 6.3 includes its own driver for .mdb files but not .accdb files, and so users have had to configure a 32-bit (ODBC) driver. When you install 64-bit Access, the 32-bit ODBC driver for Access on your system is replaced by the 64-bit driver (Microsoft allows only one ‘bitness’ of Office to be installed on a machine, including the Access database drivers). When you then try to use Surpac 6.3 to work with the database, Surpac is unable to find and use the 32-bit ODBC driver that it needs. In previous versions of Surpac, you could also create database definition files of the following types: informix, oracle, ssi, isamsql, isam, and dbase. The preferred method to connect to third-party databases now is to use ODBC.
ODBC connect string	If you are using ODBC to connect to a database, you should enter the ODBC connection string here. The minimum connection string has the following format: `DSN=<data set name>` where <data set name> is the name of the ODBC data source which you have previously set up using the ODBC manager in your operating system. For more details, refer to the ODBC connect string section in the Database Definition File topic.
y x z survey and sample co-ordinates	A database contains some fields for storing sample 'from and to' coordinates and downhole survey coordinates. Stored data allows for faster processing because, after the data is in the database, the computer does not have to repeatedly calculate the coordinates, but the size of your database increases. It is recommended that you use calculated. stored. Fields contain stored data. You can use this option if you already have the coordinates and want to load them into the database. calculated. The program will calculate the values for the relevant x, y, z fields every time the coordinates are required.

When you click Apply, the Choose optional tables for new database form is displayed.

Fields on the Choose optional tables for new database form

Field	Description
Database Name	The name of the .ddb file. Read only.
Database Type	The type of database to which the .ddb file connects. Read only.
Mandatory tables	Lists the mandatory tables that have already been created: collar, survey, and translation. Read only.
Table name	The name of an optional table you wish to create. Up to fifty tables can exist in a single database.
Table type	The following table types are available: interval - for standard 'depth from/depth to' types of data, such as drillhole samples. point - for data that relates to a single point down the hole, such as downhole geophysical information. discrete - discrete sample tables are not directly related to the other tables in the database (that is, the collar, survey, interval and point tables). Discrete tables provide a useful way to store sample data which is typically collected during geochemical soil sampling programs.
Time dependent	Selected. The table stores time-dependent data for which the position of the sample does not change, but repeated samples are taken in the same position over time. Examples of time-dependent data are measurements of groundwater pressure, and contaminants where the observations change with time. Cleared. Only one sample observation can be stored at the same position down the hole. If another reading is taken, you will need to decide whether to overwrite the original reading or store the second value in another field. Sample and geological data are examples of data that is not time-dependent. The table will have a field samp_date for recording the sample date.

Click Apply to display the Define all fields for all tables form. You use this form to to define the fields for all the tables in the database. For each table, there is a section for defining the mandatory fields, and a section for defining the optional fields.

Fields on the Define all fields for all tables form

Field	Description
Mandatory Fields	The mandatory fields are already partially defined, with meaningful defaults.
Field	Read only.
Type	Read only.
Nulls	Whether or not null values are allowed in the field. For a character field, a blank is equivalent to a null. For a numeric field, a blank is stored as a zero.
Index	The type of index required for the field. All mandatory indexes are created automatically. The only field for which this input is permitted is the samp_id field in an interval or point table. Valid entries are: none - no index is created unique - a unique index is created duplicate - an index which allows storage of duplicate values is created
Length	The length of the field in which the data is to be stored. The length should be large enough to include the decimal point and the decimal places when the field type is real.
No. Dec	The number of decimal places to be stored. This field is available only when the field type is real.
Low Bound	For numeric fields, the minimum value which you can enter.
High Bound	For numeric fields, the maximum value which you can enter.
Optional Fields	You should define the optional fields for each table, including the field name, and field type.
Field	The name of the field to create. Names must start with a letter, and contain only letters, numbers, and underscore characters.
Type	The field type for the data to be stored in the field. Valid entries are: boolean - TRUE or FALSE. character - character data. Hole IDs, sample IDs and geology codes are stored as character data. datetime - date and time in the format `yyyy-mm-dd hh:mm:ss`. Trailing fields are assumed to be zero if they are not entered. 2013-01-12 is equivalent to 2013-01-1200:00:00. duration - a duration in the format `yyyy-mm-dd hh:mm:ss` integer - integer data (numeric data with no decimal places) real - real data (numeric data with decimal places)
Nulls	Whether or not null values are allowed in the field. For a character field, a blank is equivalent to a null. For a numeric field, a blank is stored as a zero.
Length	The length of the field in which the data is to be stored. The length should be large enough to include the decimal point and the decimal places when the field type is real.
No. Dec	The number of decimal places to be stored. This field is available only when the field type is real.
Case	The case of characters you can store in the field. This is only necessary if the field type is character. Valid entries are: mixed - mixed case characters are allowed upper - only upper case characters are allowed lower - only lower case characters are allowed
Low Bound	For numeric fields, the minimum value which you can enter.
High Bound	For numeric fields, the maximum value which you can enter.
Phys, Virt or Exp	physical. The field in which you store data. Physical fields store the data loaded from your sample text files, and any numeric values translated from codes in your sample text file during loading. virtual. A virtual field has no space reserved in the database table for it. Instead a special translation function is defined to convert internally stored values to a preferred value for display purposes. This example shows you how you can use physical and virtual fields. expression. This setting permits you to enter an algebraic expression to derive a suitable value at the time a value is retrieved from the field. The variables used in the algebraic expression must by the names of other columns in the same database table.
Reference field or Expression	If the field is a virtual field, you must enter the name of the reference or physical field from which values are to be extracted before translation by the function associated with this field. If the field is an expression field, you must enter the algebraic expression. Because of the sequence of events, it is not possible to validate the entered algebraic expression while you are defining the database. Note: If you see a message that an algebraic expression is invalid when you create the database, use the Edit database definition function to correct the error.
Valid Entries	You might want to limit the character data, that anyone can enter in the field, to a predefined set of values. You can enter the values here. Each separate value must be separated by a semi-colon for example ";OVB;GRA" If you have a large number of values, it is advisable to enter them in the translation table. If the valid entries are stored in the translation table, enter "@" followed by the translation table field name here to tell the system to look in the translation table when validating input for this field. For example enter @code.

Output

Open. If you opened an existing database, the Message window states that the database is open and connected. An icon displaying the name of the database appears on the Status bar to show that the database is open.

New. A file named <dbname>.ddb is created. The other files that are created depend on the database type. For example, if the database type is access, an .accdb file is also created.