oem_string option [database]

Stores user-specified information in the header page of the database file.

Allowed values

String (up to 128 bytes)

Default

Empty string

Scope

Can only be set for the PUBLIC group. Takes effect immediately. DBA authority required.

Remarks

You can store information in the header page of the database file and later extract the information by reading the file directly from your application. This page is stored in the system dbspace file header. If you specify a value for the OEM string that is longer than 128 bytes, an error is returned.

You may find it useful to store such information as schema versions, the application name, the application version, and so on. Alternatively, without starting the database, an application could use the OEM string to determine whether the database file is associated with the application, or design your application to use the information to validate that the database file is intended for your application, by storing a string that the application reads for validation purposes before using the database file. You could also extract metadata to display to users.

To set the oem_string in the system dbspace file header, execute the following statement:

SET OPTION PUBLIC.oem_string=user-specified-string;

The user-specified-string value is stored both in the ISYSOPTIONS system table and the system dbspace file header. You must define the string in the required character set before you specify it in a SET OPTION statement because no translation is done on the string when it is supplied in the SET OPTION statement. You can use the CSCONVERT function to convert the string to the required character set.

You can query the value of the oem_string in the following ways:

Using the oem_string connection property:

SELECT CONNECTION_PROPERTY( 'oem_string' );

Using the SYSOPTION system view:

SELECT setting FROM SYSOPTION WHERE "option" = 'oem_string';

To query the oem_string option from an application

Open the database system dbspace file.
Read the first page of the file into a buffer.
Search the buffer for the two byte prefix and suffix sequences before and after the OEM string.

The prefix and suffix strings are defined in sqldef.h as DB_OEM_STRING_PREFIX and DB_OEM_STRING_SUFFIX, respectively. All the bytes between these two strings define the OEM string that is defined in the database.

SQL Anywhere includes two sample programs in the oem_string directory:

dboem.cpp is a C program that illustrates how to extract the OEM string and print it to the database server messages window.
dboem.pl illustrates how to extract the OEM string and print it to the stdout within a PERL script.

Caution

Applications cannot write directly to the OEM string in the database because it corrupts the database header page.

On Windows, applications cannot read the file directly when a server has the database file loaded. The database server has an exclusive lock on the file. However, on any supported Unix platform, applications that have read permissions can read the file directly at any time. However, changes to the OEM string may not show up in the file immediately. Issuing a checkpoint causes the database server to flush page 0 to disk, and reflect the current OEM string value.

Should the database server fail between changing the OEM string and the next checkpoint, the file header may not reflect the new OEM string value; the new OEM string value will be set correctly after the database goes through recovery.

Example

The following example encrypts the OEM string that contains information about the database file and stores it in the database header file:

BEGIN 
  DECLARE @v VARCHAR(100); 
  SET @v = BASE64_ENCODE( ENCRYPT( 'database version 10', 'abc' ) ); 
  EXECUTE IMMEDIATE 'SET OPTION PUBLIC.oem_string = ''' || @v || ''''; 
END;

You can retrieve the value of the OEM string using the following command:

SELECT DECRYPT(
   BASE64_DECODE( 
      CONNECTION_PROPERTY( 'oem_string' ) ),'abc' )

Send feedback about this page via email or DocCommentXchange