declaring a database

+|–database

Define a Sculptor or ODBC database

SYNTAX

+|–database database_id {
      source = “source_name”
      type = DBT_ODBC | DBT_SCULPTOR
      flags = option_flags
      riu_timeout = integer

!file file_id “pathname” [create | read | update]
!ofile file_id “pathname” create | read | update
… other ![o]files

!cursor cursor_id
… other !cursor statements
}

+|–database database_id

The database and its identifier. The identifier is the name by which it will subsequently be referenced in the program.

The database may be declared as +database or –-database.

If it is declared as +database, the program connects to the database at startup.

If it is declared as -–database, the program does not connect to the database until it is opened with the opendb command at run time.

source = “source_name”

In case of a DBT_ODBC database, the source property specifies the name of an ODBC data source. The source name can be specified in one of three ways:

1

source = “[DSN=]Data source name” – if this form is used, the data source must be defined using the ODBC configuration program in the system Control Panel.

2

source = “DRIVER=Driver specific name and arguments” – use this form to specify an ODBC driver and its arguments. Refer to the driver’s documentation for the syntax of the driver string.

3

source = “” – if the source name is left blank, the data source is selected at run time by means of a dialog, which allows the user to choose from the sources which have been defined using the ODBC configuration program in the system Control Panel.

Since Sculptor 6.0.0 It’s now possible to connect to a SQL database in the Sculptor server by adding a “SCULPTOR_SERVER=” clause to the start of the source string in an ODBC database declaration. Example:

+database mydb {
    type = DBT_ODBC
    source = "SCULPTOR_SERVER=srv;DSN=mydsn"
    riu_timeout = 3
}

Example of an alternative form of source string:

source = "SCULPTOR_SERVER=srv;DRIVER=SQL Server;SERVER=SQLS;DATABASE=master"

The SCULPTOR_SERVER clause must be the first clause in the source string. Do not use {} to enclose the Sculptor server name as this is not supported. Connection to the ODBC driver is made on the server and all network traffic is handled by Sculptor, making it unnecessary to install the ODBC driver on the client.

If a database is declared as type DBT_SCULPTOR and has a source string, this is prepended to each database filename before it is opened. Example:

+database mydb1 {
    type = DBT_SCULPTOR
    source = "srv:c:/app/data/"
    !file FILE1 "file1"
    !file FILE2 "subdir/file2"
}

Both file1 and file2 are opened on srv at run-time. It is of course necesary to ensure that a corresponding path to file1.d and subdir/file2.d exists locally for program compilation purposes.

type =

The following types, defined in sculptor.h with prefix DBT_, are valid:

DBT_SCULPTOR

The database uses Sculptor keyed files. An ODBC driver is not required. Only traditional Sculptor commands can be used on the files in this database.

The database can be mirrored to an ODBC database, so that the ODBC database is automatically synchronised to it.

An ODBC driver is available for use with Sculptor keyed files. To use embedded SQL commands on a Sculptor database, install this driver and specify type DBT_ODBC.

DBT_ODBC

Access to the database is via ODBC. A suitable ODBC driver must be installed. Both traditional Sculptor file commands and embedded SQL commands can be used on the files in the database.

flags = option_flags

The valid option flags, defined in sculptor.h with prefix DBFL_, are:

DBFL_OPTIMISTIC_LOCKING

Force optimistic locking on a database where pessimistic locking is the default. See ODBC record locking.

DBFL_IGNORE_ CATALOG

Ignore catalog usage.

DBFL_IGNORE_ SCHEMA

Ignore schema usage.

Sculptor queries the ODBC driver’s catalog and schema properties for the syntax the driver requires for fully qualified field and table names. Some drivers return inaccurate information. Setting one or both of these flags tells Sculptor to ignore the driver’s response and use a common default. A likely reason for setting one or both of these flags would be if an ODBC driver was generating errors relating to field and table names.

DBFL_ACCESS_MULTIUSER

This flag should be set if it is essential to work with Microsoft Access in multi-user mode. We advise that Access is used in single-user mode only, since its ODBC driver is not multi-user safe.

Setting DBFL_ACCESS_MULTIUSER tells Sculptor to perform updates to an Access database in slower but more reliable way. This technique has proved reliable in tests but safe multi-user operation cannot be guaranteed.

This flag must also be set if you are using an Access data dictionary that contains a key field of type AutoNumber.

DBFL_CASE_SENSITIVE

This flag tells Sculptor to put quotes round SQL identifiers. This is necessary if the database supports case sensitivity with quoted identifiers but not with unquoted identifiers. Sculptor asks the ODBC driver for the quote character to use.

If SQL reserved words are used for field names, this flag must be set, and the ODBC data source must be configured to “Use ANSI quoted identifiers” if this is an option.

DBFL_UNSIGNED_KEYS

Make all main index key fields of type i2, i4 or i8 sort positive values first, so that the file is sorted in the same order as a Sculptor keyed file. The field flag “U” is used to apply this to individual fields. See Sort order.

DBFL_MULTICONNECT

Separate connection for each cursor. To open a file in an ODBC driver, Sculptor first opens a connection to the driver. Sculptor queries the driver to see if it supports multiple files on the same connection to decide whether or not it needs to open a new connection for each file. This flag forces Sculptor to open a new connection for each file. One would use this if the driver supported multiple connections, but with limitations that affected Sculptor.

DBFL_DRIVER_COMPLETE

Added in Sculptor version 6.0.9.

When defined uses SQL_DRIVER_COMPLETE on SQLDriverConnect() which generates a dialog to complete a connection if necessary. Otherwise uses SQL_DRIVER_PROMPT if the connection string is null or SQL_DRIVER_NOPROMPT if the connection string is not null.

DBFL_RECREATE_LOGFILE

Recreate the error log file (for example the one specified in the set_mirror_source function)

New in version 6.3.0: See the new features.

DBFL_COMBINE_PACKED_TEXT

Maps Sculptor packed text fields onto a single LONGVARCHAR column. If not set, separate CHAR columns are created for each array element.

DBFL_IGNORE_FILL_FIELDS

Ignores all Sculptor fields whose names begin fill[0-9] and spare[0-9] when creating a SQL table from a Sculptor data dictionary. The name comparison is not case sensitive.

riu_timeout = integer

Timeout value in seconds. If a timeout occurs, it triggers the riu trap.

If no timeout value is set, or if it is set to zero, SQL Server waits indefinitely for a locked record and does not return a record in use status.

The riu_timeout value can be changed at run-time, e.g.:

mydb->riu_timeout = 5

!file file_id “pathname” [create | read | update]

!ofile file_id “pathname” create | read | update

Each file to be accessed or updated in the database is declared by !ofile or !file. These are standard Sculptor declarations for opening keyed files. The !ofile version declares a file which is initially open; !file declares a file which is initially closed.

The name = clause of the openfile command can be used to specify a a SQL database connection string as an alternative to a pathname. In this case, the file declaration can be inside or outside the +|– database structure. See SQL database connection strings.

!cursor cursor_id

Declares a cursor. The sql command is used at run time to execute SQL statements and associate a set of records with the cursor.

RELATED TOPICS

ODBC

Mirroring a Sculptor database to ODBC