Using SQL Access
Complete the following tasks to take advantage of SQL access:
1
Manually edit the appropriate XML template to describe the data layout.
2
Copy the data file(s) specified in the XML template(s) to the database folder.
3
Execute the utility to populate the system tables used by the relational interface (use the XML to create normalized data).
4
Optionally, if you are a COBOL applications developer, ensure that you deploy any new system tables created by Schema Executor.
Step 1: Modify the Sample XML Templates
Pervasive PSQL v11 SP3 includes sample XML templates that you use to define the layout of data as required by your COBOL application. See Table 196. In developer’s terms, you describe your metadata in the XML files.
►
To Modify an XML Template
1
Open the XML template in a text editor.
2
Modify the XML as described in the file’s comments.
3
Save the modified template with a path and file name of your choosing.
Step 2: Copy the Data File Specified in the XML Template
Copy the data file(s) specified in the XML file to the data file location of the database before you run Schema Executor. The database is the one to which you need to add the tables.
For example, you want to add a table (specified in the XML as mytable.mkd) to a database "test" that has its data files under c:\data\test. Copy the data file mytable.mkd to c:\data\test before you run Schema Executor.
Step 3: Run the Schema Executor Utility
Pervasive PSQL v11 SP3 includes a command line utility called the Schema Executor, which is also referred to as SchemaExec.
Schema Executor performs the following actions:
•
parses the XML file(s) that you manually edited.
•
populates existing system tables that the relational interface uses to interpret the data as normalized SQL tables (a database created with Pervasive PSQL v11 SP3 contains all of the required system tables to support SQL access)
•
creates additional system tables and populates them if you run the utility against a database created with a version of Pervasive PSQL prior to Pervasive PSQL v11 SP3.
►
To Process an XML File with Schema Executor
See also Example of How to Execute a Sample XML File.
1
Access a command prompt at the operating system.
2
Execute Schema Executor at the command line (see Table 196 for where this executable is installed by default).
Provide the required options, XMLfilename and databasename, and any desired optional options. See Schema Executor Command Format.
If errors occur during the processing of the XML content, review the errors reported in the Schema Executor log file. See Log Messages. Re-execute the utility with corrected XML until no errors result from the processing.
Schema Executor Command Format
cobolschemaexec XMLfilename databasename [-s servername] [-u login_id] [-p password] [-i svr_loginid] [-c svr_password] [-l log_file] [ -h | -? ]
 
Table 197
Options for Schema Executor Utility
Option
Meaning
XMLfilename
The file name of the XML schema that defines the layout of the data. Required option. See Step 1: Modify the Sample XML Templates.
databasename
The name of the Pervasive PSQL database accessed by your application. Required option. If the database specified does not exist, the utility prompts for a path and file name.
See also Creating a New Database with Schema Executor.
-s servername
The name or IP address of the server running the Pervasive PSQL database engine. You may use “localhost” as the name if running SchemaExec on the same machine as the database engine. If servername is not specified, the local machine is assumed to be the server.
-u login_id
The user name required to access a secure database. See Pervasive PSQL Security in Advanced Operations Guide for a discussion of the Pervasive PSQL security models.
-p password
The password required to access a secure database. See Pervasive PSQL Security in Advanced Operations Guide for a discussion of the Pervasive PSQL security models.
-i svr_loginid
The login name required to access the operating system on a remote machine. This option is required if SchemaExec is processing an XML file located on a remote server.
-c svr_password
The password required to access the operating system on a remote machine. This option is required if SchemaExec is processing an XML file located on a remote server.
-l log_file
Log file to use for messages produced during processing of the XML file.
If you execute SchemaExec on a machine running the Pervasive PSQL database engine, a default log is created automatically. You do not need to use the -l log_file option. The default log is named cobolschemaexec.log.
If you execute SchemaExec on a client machine (a machine not running the Pervasive PSQL database engine), you can specify a log file for the client machine.
See Log Messages.
-h or -?
Display command usage. Ignore all other options.
*Note: The required options XMLfilename and databasename are positional and must appear in that order.
Example Usage
The following examples illustrate usage of Schema Executor.
For default locations of Pervasive PSQL files, see Where are the Pervasive PSQL files installed? in Getting Started With Pervasive PSQL.
•
Database already exists (with server running on local host):
cobolschemaexec file_path\PSQL\samples\cobolschemaexec\test.xml demodata\
•
Database does not exist (with server running on local host):
cobolschemaexec file_path\PSQL\samples\cobolschemaexec\test.xml mytest
The utility prompts as follows:
CB103 : Could not connect to mytest
Do you want to create database (y/n) ?
If you press "y," the utility prompts for a database path:
Please enter the Database Path:
Provide an existing path or the utility returns an error. Ensure that the database file (for example, a .MKD file) being used in the XML file is available in the path.
•
Database exists on a remote server:
cobolschemaexec file_path\PSQL\samples\cobolschemaexec\test.xml demodata -s TestMachine -i testuser -c testuser
This example assumes that a user "testuser" (with password "testuser") exists on the remote machine (TestMachine) with administrative privileges, and that the database file being used in the XML file is available in the data file directory of the database on the remote machine.
•
Database does not exist on a remote server:
cobolschemaexec file_path\PSQL\samples\cobolschemaexec\test.xml mytest -s RemoteMachine -i testuser -c testuser
This example assumes that a user "testuser" (with password "testuser") exists on the remote machine (TestMachine) with administrative privileges.
The utility prompts as follows:
CB103 : Could not connect to mytest
Do you want to create database (y/n) ?
If you press "y," the utility prompts for a database path:
Please enter the Database Path:
Provide an existing path or the utility returns an error. Ensure that the database file (for example, a .MKD file) being used in the XML file is available in the path.
Creating a New Database with Schema Executor
If the utility option databasename specifies a database that does not exist, Schema Executor prompts you whether to create a new database. If you specify “yes,” the utility prompts for a location of the new database. The location (path and folder name) must already exist for Schema Executor to create the database.
Note that Schema Executor also expects to find the data files for databasename in the default folder. The utility informs you if it finds no data files. Manually copy the data files to the default folder and run Schema Executor again to process the XML.
Log Messages
This section lists the codes that may appear in a log file after processing an XML file with Schema Executor.
The success code is CB100 : Schemaexec completed successfully.
The following table lists the error codes.
Table 198
Possible Error Codes in a CobolSchemaExec Log File 
Error Code
Description
CB001
Unknown error.
CB002
Property name attribute missing
CB003
Both MAINTABLE and VARIANTRECORDTABLES not supported
CB004
Occurs Table Name specified is Invalid
CB005
Occurs Count specified is Invalid
CB006
Occurs Mapping Index specified is Invalid
CB007
TableName - Duplicate TableName
CB008
FieldName - Duplicate FieldName
CB009
IndexName - Duplicate IndexName
CB010
Table name parameter is not specified
CB012
Identifier - contains invalid characters
CB013
Offset has to be a non-negative integer
CB014
Identifier length cannot exceed 20 characters
CB016
Precision has to be greater than zero
CB017
Invalid Precision specified for - FieldName
CB018
Scale cannot be greater Precision for - FieldName
CB019
Log and XML file names must be different
CB022
TableFilter - TableFilter cannot have more than 255 characters
CB023
FieldName is not a field of TableName
CB024
TableFilters should be defined for all REDEFINES Table or for NONE
CB025
Incorrect Parent Element
CB028
Identifier name identifiername should start with an alphabetic character
CB029
Identifier name identifiername cannot be a keyword
CB050
DataFile - doesn't exist at DatabasePath
CB051
OCCURS/REDEFINES Length must be a non-negative integer
CB052
Length of Btrievefilename cannot exceed 64 characters
CB057
No index specified for Parent Table - ParentTableName
CB099
Parser error
CB100
Schemaexec completed successfully
CB101
Invalid value for command line argument argument
CB102
Value for Password cannot be specified without Login
CB103
Could not connect to DatabaseName
CB105
Could not create database DatabaseName in the dictionary path Databasepath
CB106
Could not create the specified DSN
CB108
Could not close the database databasename
CB109
Could not read data from XML file
CB110
Could not drop the database
Step 4: Optionally, Deploy the System Tables
If you are a COBOL applications developer, ensure that you deploy all of the system tables with your application. Such deployment is nothing new and is mentioned only because you may have additional system tables. For example, Schema Executor creates additional system tables and populates them if you run the utility against a database created with a version of Pervasive PSQL prior to Pervasive PSQL v11 SP3. Therefore, you may have a few additional system tables (DDF files) to deploy.