Product Update Description Copyright 1998 Pervasive Software Inc. All rights reserved worldwide. Reproduction, photocopying, or transmittal of this publication, or portions of this publication, is prohibited without the express prior written consent of the publisher, unless such reproduction, photocopying, or transmittal is part of a Derivative Software Product as defined in the licenses granted in conjunction with the purchase of this publication and associated software. This product includes software developed by Powerdog Industries. 1994 Powerdog Industries. All rights reserved. Pervasive Software Inc. 8834 Capital of Texas Highway Austin, Texas 78759 USA
disclaimer
PERVASIVE SOFTWARE INC. LICENSES THE SOFTWARE AND DOCUMENTATION PRODUCT TO YOU OR YOUR COMPANY SOLELY ON AN "AS IS" BASIS AND SOLELY IN ACCORDANCE WITH THE TERMS AND CONDITIONS OF THE ACCOMPANYING LICENSE AGREEMENT. PERVASIVE SOFTWARE INC. MAKES NO OTHER WARRANTIES WHATSOEVER, EITHER EXPRESS OR IMPLIED, REGARDING THE SOFTWARE OR THE CONTENT OF THE DOCUMENTATION; PERVASIVE SOFTWARE INC. HEREBY EXPRESSLY STATES AND YOU OR YOUR COMPANY ACKNOWLEDGES THAT PERVASIVE SOFTWARE INC. DOES NOT MAKE ANY WARRANTIES, INCLUDING, FOR EXAMPLE, WITH RESPECT TO MERCHANTABILITY, TITLE, OR FITNESS FOR ANY PARTICULAR PURPOSE OR ARISING FROM COURSE OF DEALING OR USAGE OF TRADE, AMONG OTHERS.
trademarks
Btrieve and XQL are registered trademarks of Pervasive Software Inc. Built on Btrieve, Built on Scalable SQL, Client/Server in a Box, DDF Ease InstallScout, MicroKernel Database Engine, MicroKernel Database Architecture, Navigational Client/Server, Pervasive.SQL, Scalable SQL, Smart Components, Smart Component Management, Smart Naming, SmartScout, and Xtrieve PLUS are trademarks of Pervasive Software Inc. Microsoft, MS-DOS, Windows, Windows NT, Win32, Win32s, and Visual Basic are registered trademarks of Microsoft Corporation. Windows 95 is a trademark of Microsoft Corporation. NetWare and Novell are registered trademarks of Novell, Inc. NetWare Loadable Module, NLM, Novell DOS, Transaction Tracking System, and TTS are trademarks of Novell, Inc. All company and product names are the trademarks or registered trademarks of their respective companies.
Product Update Description
100-003198-004
February 1998
Contents About This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Manual Organization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1
Btrieve 7.0 Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 New MicroKernel Features . . . Communications Enhancements Configuration Changes . . Utilities Enhancements . . . . . New API Features. . . . . . . . Installation Toolkit . . . . . . . . File Conversion . . . . . . Compatibility. . . . . . . .
2
8 9
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
11 14 14 18 20 25 25 25
Scalable SQL 4.0 Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 Compatibility Feature Summary. Features . . . . . . . . . . Data Types . . . . . . . . Keywords . . . . . . . . . Scalable SQL Statements . Scalable SQL APIs . . . . Inscribe Compatibility . . .
Product Update Description
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
3
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
28 28 29 30 34 38 43
Contents
3
Scalable SQL Compatibility. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 Compatibility Modes. . . . . . . . . . . . . . . . . . . . . . . . . Login Parameter. . . . . . . . . . . . . . . . . . . . . . . . Naming Conventions . . . . . . . . . . . . . . . . . . . . . . . . Naming Database Elements . . . . . . . . . . . . . . . . . Operator Precedence . . . . . . . . . . . . . . . . . . . . . Specifying String Data. . . . . . . . . . . . . . . . . . . . . Stored Statements and Procedures. . . . . . . . . . . . . . . . . Behavioral Differences of Stored Statements and Procedures Converting Stored Statements to Stored Procedures . . . . . Version 3.01 and 4.0 Behavioral Differences . . . . . . . . . . . . Changes to 3.x Dictionaries . . . . . . . . . . . . . . . . . . Transaction Processing . . . . . . . . . . . . . . . . . . . . Temporary Sort Files . . . . . . . . . . . . . . . . . . . . . Unqualified Column Names . . . . . . . . . . . . . . . . . . Conventions for Constants . . . . . . . . . . . . . . . . . . Constants and Column Masks . . . . . . . . . . . . . . . . CREATE TABLE Syntax . . . . . . . . . . . . . . . . . . . CASE Attribute on Columns and Indexes . . . . . . . . . . . Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4
. . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . .
45 46 47 47 47 48 49 50 52 53 53 54 54 54 55 56 57 57 58
Obsolete Scalable SQL Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 Obsolete Requester . . . . . . . . . . . . . . . . . . . . . . Obsolete Functionality . . . . . . . . . . . . . . . . . . . . Blank Replacement Character . . . . . . . . . . . . . Data File Formats . . . . . . . . . . . . . . . . . . . . Operators . . . . . . . . . . . . . . . . . . . . . . . . Stored Statements . . . . . . . . . . . . . . . . . . . Obsolete Syntax. . . . . . . . . . . . . . . . . . . . . . . . CREATE PROCEDURE Syntax for Stored Statements. EXECUTE Statement . . . . . . . . . . . . . . . . . . SET BLANK Statement . . . . . . . . . . . . . . . . . Obsolete Functionality of INSERT Statement . . . . . .
Product Update Description
4
. . . . . . . . . . .
. . . . . . . . . . .
. . . . . . . . . . .
. . . . . . . . . . .
. . . . . . . . . . .
. . . . . . . . . . .
. . . . . . . . . . .
. . . . . . . . . . .
. . . . . . . . . . .
. . . . . . . . . . .
. . . . . . . . . . .
61 62 62 64 64 65 73 73 74 76 77 Contents
Obsolete API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 XQLSPUtility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 Obsolete Status Codes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
5
Relational Primitives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
Product Update Description
5
Contents
Tables 2-1
Features Supported in 3.01 and 4.0 Compatibility Mode ...........................................28
2-2
Data Types Supported in 3.01 and 4.0 Compatibility Modes .....................................29
2-3
Keywords Supported in Both 3.01 and 4.0 Compatibility Modes ...............................30
2-4
Keywords Supported in 4.0 Compatibility Mode Only ................................................33
2-5
Statements Supported in Both 3.01 and 4.0 Compatibility Modes .............................34
2-6
APIs Supported in Both 3.01 and 4.0 Compatibility Modes .......................................38
3-1
Stored Statement and Stored Procedure Comparison...............................................50
4-1
Execution Statistics Data Buffer .................................................................................84
4-2
Obsolete Status Codes ..............................................................................................89
Product Update Description
6
Tables
About This Manual Pervasive Software would appreciate your comments and suggestions about this manual. Please complete the User Comments form that appears at the end of this manual, and fax or mail it to Pervasive Software, or send e-mail to
[email protected].
Product Update Description
7
About This Manual
Manual Organization The following list briefly describes each chapter and appendix in this manual:
Chapter 1—“Btrieve 7.0 Overview” This chapter introduces the Btrieve 7.0 changes.
Chapter 2—“Scalable SQL 4.0 Overview” This chapter introduces the Scalable SQL 4.0 changes.
Chapter 3—“Scalable SQL Compatibility” This chapter describes specific compatibility issues such as compatibility modes, stored statements, and behavioral differences between Scalable SQL 3.01 and Scalable SQL 4.0.
Chapter 4—“Obsolete Scalable SQL Features” This chapter details features that are no longer supported or that will become obsolete in future releases (such as syntax, APIs, status codes, etc.).
Chapter 5—“Relational Primitives” This chapter provides a reference for each of the relational primitives supported for Scalable SQL 3.01 applications, and provides equivalent SQL functionality for 3.01 to 4.0 migration.
Product Update Description
8
About This Manual
Conventions Unless otherwise noted, command syntax, code, and code examples use the following conventions: Case
Commands and reserved words typically appear in uppercase letters. Unless the manual states otherwise, you can enter these items using uppercase, lowercase, or both. For example, you can type MYPROG, myprog, or MYprog.
[ ]
Square brackets enclose optional information, as in [log_name]. If information is not enclosed in square brackets, it is required.
|
A vertical bar indicates a choice of information to enter, as in [filename | @filename].
< >
Angle brackets enclose multiple choices for a required item, as in /
D=. variable
Words appearing in italics are variables that you must replace with appropriate values, as in filename.
...
An ellipsis following information indicates you can repeat the information more than one time, as in [parameter ...].
::=
The symbol ::= means one item is defined in terms of another. For example, a::=b means the item a is defined in terms of b.
Product Update Description
9
About This Manual
chapter
1
Btrieve 7.0 Overview
You should read this section if you are upgrading from Btrieve 6.x to Pervasive.SQL 7.0. This section discusses the following topics:
“New MicroKernel Features” “Communications Enhancements” “Configuration Changes” “Utilities Enhancements” “New API Features” “Installation Toolkit” “File Conversion” “Compatibility”
Product Update Description
10
Btrieve 7.0 Overview
New MicroKernel Features The version 7.0 MicroKernel provides several new features and performance enhancements. Here is a brief description:
Large Files. Data files that use the 7.x format can expand up to 64 GB. Btrieve applications treat the large file as a single, logical data file, but at the operating system level the large file consists of up to 32 two-gigabyte files. This 2 GB limit allows the large file to be portable across all Btrieve-supported platforms. For more information, read "Large Files" in the Pervasive.SQL User’s Guide.
Developers: The MicroKernel’s large file support feature requires a maximum of 256 records per page. This limit can affect the optimum page size in files that have very small records and very large page sizes. For more information, read "Designing the Database" in the Btrieve Programmer’s Guide.
Transaction Durability. The 7.0 MicroKernel employs a new transaction logging scheme that tracks all operations on all files in a single log. This new scheme allows the MicroKernel to guarantee transaction persistence and atomicity, improve performance, and use a larger system transaction size. Transaction durability is accomplished by uniquely identifying each record, either by a user-defined unique key or by a system-defined log key. Systemdefined log keys are called system data. Consequently, if a file does not have system data or a unique user-defined key, the file is not "transaction durable." To determine if a file is transaction durable, retrieve the file’s statistics. You can do this using the BUTIL -STAT command, using the Maintenance utility’s Create Stat Report command, or calling the Btrieve Stat operation. If you have existing 6.x files that are not transaction durable because they do not have unique userdefined keys, you should add system data. Because the MicroKernel adds system data only to 7.0 files, you must update the file format when you add system data.
Product Update Description
11
Btrieve 7.0 Overview
To convert a 6.15 file to 7.0 format and add system data, see Chapter 10, ”Converting MicroKernel Data Files,” in the Pervasive.SQL User’s Guide. In Btrieve 6.x, the Transaction Durability configuration option was turned off by default. In 7.x the option is turned on by default for two reasons. First, guaranteeing transaction persistence and atomicity is beneficial. Second, the default system transaction size is larger than in Btrieve 6.x, which improves performance at the risk of losing more work in the event of a crash if durability were not turned on. For more information about these topics, read "Transaction Durability" and "System Data" in the Pervasive.SQL User’s Guide.
Enhanced roll forward capability. Rolling forward is the process by which you recover changes made to a data file between the time of the last backup and a system failure. Previously, roll forward functions were performed by a separate Roll Forward utility. In Pervasive.SQL 7.0, the MicroKernel performs roll forward tasks when instructed by the Maintenance utility. This enhances roll forward performance and reliability. Support for more than 64,000 file handles. The 7.0 MicroKernel allows you to assign up to 4 billion file handles. Previously, the MicroKernel limited file handles to 64,000. Pervasive.SQL Event Logging. The MicroKernel log file has been replaced with a new, centralized event log that Pervasive.SQL components share. This log contains informational, warning, and error messages. When receiving some types of status codes now, more detailed information can be found in this log pertaining to that error. Event logs are generated at both the client and the server machines. For more information, read "Event Logging" in the Pervasive.SQL User’s Guide. International Sort Rules. This feature extends the use of alternate collating sequences by providing a set of multi-weight collation tables you can use to sort international string values with an ISO-defined, language-specific collating
Product Update Description
12
Btrieve 7.0 Overview
sequence. The tables provide for collation in English, French, German, Spanish, and Japanese (Shift-JIS character set). For more information, read "International Sort Rule" in the Pervasive.SQL User’s Guide.
Stackable user counts. You can increase the Btrieve user count by supplying a software license key you obtain from Pervasive Software. You can increase the user count incrementally by 10, 20, 50, 100, or to an unlimited number of users. When you reach 200 users, you have an unlimited user count environment. For more information, refer to Chapter 5, “Updating Your User Count License,” in Getting Started With Pervasive.SQL. Smart Components. A new architecture governs how Btrieve’s internal client components communicate with one another. By ensuring that outdated or incompatible components are not used by mistake, this architecture improves installation and run-time reliability and makes application trouble-shooting easier. For more information about troubleshooting your installation, read “Troubleshooting Installation Problems,” in Getting Started With Pervasive.SQL. For more information about developing Btrieve applications in the Smart Components environment, read "Btrieve Component Architecture" in the Btrieve Programmer’s Guide.
Product Update Description
13
Btrieve 7.0 Overview
Communications Enhancements Pervasive.SQL provides these enhancements for client/server communications:
TCP/IP to all servers. You can now choose to use TCP/IP for communications between any Btrieve client Requester (except DOS) and any Btrieve Windows NT or NetWare server engine. OS/2 clients to all servers. You can now use the Btrieve Requester for OS/2 to access files on any Btrieve Windows NT or NetWare server environment. Transparent Novell Directory Service (NDS) Support. Applications on NDScapable client machines now use NDS to locate Btrieve servers and authenticate Btrieve file users. Btrieve client Requesters support NDS naming conventions on the Windows, Windows NT, Windows 95 and OS/2 platforms by recognizing the NDS volume and directory map objects. Btrieve applications written for previous Btrieve Requesters are forward compatible with NDS without change to the binaries. The Btrieve DOS Requester requires applications themselves to provide NDS support through inclusion of the NetWare NDS API. Refer to Getting Started With Pervasive.SQL for more information on NDS support.
For more information about these enhancements, refer to Chapter 4, “Installing and Configuring the Pervasive.SQL Requesters,” in Getting Started With Pervasive.SQL.
Configuration Changes For Windows NT servers, the Setup utility provides a new System Cache option, which allows you to specify whether to use the Windows NT system cache in addition to the MicroKernel’s own Cache Allocation option. In most cases, performance is enhanced by
Product Update Description
14
Btrieve 7.0 Overview
turning on the System Cache option, because less memory paging occurs. For more information about this option, see the Pervasive.SQL User’s Guide. Pervasive.SQL uses an updated Setup utility for configuration. The following options are new in Pervasive.SQL 7.0. See the Pervasive.SQL User’s Guide for descriptions:
Database Names Directory Log Buffer Size Maximum Databases Minimal State Delay System Data Thread Priority Delta Transaction Log Size Wait Lock Timeout Working Directory
The following MicroKernel options are obsolete in Pervasive.SQL 7.0:
Record Locks per Client Create Log File Format Transactions Disk I/O Wait Limit Page Write Group Size
Product Update Description
15
Btrieve 7.0 Overview
The following MicroKernel options have changed from 6.x to 7.0: Option
Change
Open Files
Memory required is now 1,024 bytes per file.
Create Files in Pre-6x Format
Name changed to Create File Version. Range is now 5x, 6x, or 7x. Default is 7x.
Logging of Selected Files
Name changed to Archival Logging of Selected Files.
Handles
Support for more than 64,000 file handles.
Cache Allocation
Default is now 1,024 KB.
Perform Transaction Durability
Name changed to Transaction Durability. Default is now On.
Operation Bundle Limit
Default is now 1,000.
Initiation Time Limit
Default is now 1,000.
Worker Threads
Range is now 1 through 128.
I/O Threads
Range is now 1 through 128.
Allocate Resources at Startup
Default is now On.
Back to Minimal State if Inactive
Default is now On.
Product Update Description
16
Btrieve 7.0 Overview
The following MicroKernel options have not changed from 6.x to 7.0:
Index Balancing Communication Buffer Size Largest Compressed Record Size Extended Operation Buffer Size Sort Buffer Size Active Clients Home Directory Transaction Log Directory Trace Operations Trace File Select Operations Number of Bytes from Data Buffer Number of Bytes from Key Buffer
Product Update Description
17
Btrieve 7.0 Overview
Utilities Enhancements Pervasive.SQL 7.0 offers the following enhancements to utilities:
New Diagnostic Utilities. SmartScout and InstallScout. These utilities help prevent configuration and installation problems, and are installed and run as part of the Pervasive.SQL installation process. All of these utilities can be run later to evaluate configuration and registry settings and to troubleshoot problems. See “Troubleshooting Installation Problems” in Getting Started With Pervasive.SQL for more information. New User Count Utilities. Both the command-line based and the interactive user count utilities allow you to increase your user count license or determine your current user count license. For more information, see “Updating Your User Count License” in Getting Started With Pervasive.SQL. New Interactive Rebuild Utility. This utility complements the existing command-line based utility (BREBUILD) and provides equivalent capabilities for file format conversion. For more information, see Chapter 10, ”Converting MicroKernel Data Files,” in the Pervasive.SQL User’s Guide. Enhanced Maintenance Utility. Both the command-line based (BUTIL) and the interactive utilities provide roll forward capability. (There is no separate utility in Pervasive.SQL for roll forward functions.) In addition, the command-line based utility provides support for large (extended) files and the ability to read a file forward or backwards in SAVE and RECOVER operations. For more information, see the Pervasive.SQL User’s Guide. Enhanced Monitor Utility. This utility now provides monitoring capabilities for remote servers. In addition, the Communication Statistics screen has been split into two screens, one for SPX and one for TCP/IP. For more information, see the Pervasive.SQL User’s Guide. Previous versions of the Monitor utility will not work with the 7.0 MicroKernel.
Product Update Description
18
Btrieve 7.0 Overview
Enhanced Setup Utility. This utility now provides configuration capabilities for remote servers. For more information, see the Pervasive.SQL User’s Guide. Previous versions of the Setup utility will not work with the 7.0 MicroKernel. DDF Ease. With this utility, you can create data dictionary files (DDFs) for your existing Btrieve data files. You can also add relational capability to your Btrieve database. For explicit instruction for using DDF Ease, run its online help system or refer to the Pervasive.SQL User’s Guide.
Product Update Description
19
Btrieve 7.0 Overview
New API Features
New Data Types. The MicroKernel supports two new data types: CURRENCY and TIMESTAMP. The CURRENCY data type represents an 8-byte signed quantity, sorted and stored in Intel binary integer format; therefore, its internal representation is the same as an 8 byte INTEGER data type. The TIMESTAMP data type represents a time and date value. In applications that use Pervasive’s Scalable SQL or ODBC Interface, you can use this data type to stamp a record with the time and date of the last update to the record. For more information, see the Pervasive.SQL User’s Guide. New Stat Extended (65) Operation. The Stat Extended operation provides two subfunctions. One returns the file names and paths of an extended file’s components: the base file and extension files. The other returns information regarding a file’s use of system data, which is important for transaction durability. Changed Operations. The following operations have changed. For more information, see the Btrieve Programmer’s Reference: Operation
Changes
Close (1)
You can close a file while inside a transaction.
Create (14)
You can set new bits in the File Flags to control the addition of system data, which is important for transaction durability. In addition, you can specify an international sort rule (ISR) for keys in the file, just as you would an ACS.
Create Index (31) Drop Index (32)
You can use these operations to create and drop the system-defined log key (also known as system data). Also, the Create Index operation allows you to specify an existing ACS or ISR, define a new ACS or ISR, or use the file’s default ACS or ISR.
Product Update Description
20
Btrieve 7.0 Overview
Operation
Changes
Get Next Extended (36) Get Previous Extended (37) Step Next Extended (38) Step Previous Extended (39)
You can use any existing ACS or ISR defined in the file when you specify the Filter criteria.
Open (0)
The 6.x MicroKernel accepted Accelerated open requests, but interpreted them as Normal open requests. The 7.0 MicroKernel uses the Accelerated open mode to override transaction durability, thus increasing performance.
Version (26)
The 7.0 MicroKernel returns an expanded set of values for the version information in the data buffer, which more specifically identifies the type of requester or engine.
Changed Status Codes on Return. Several status codes have been added to provide more information in certain situations. For example, several operations return a more informative status code instead of Status Code 85 (File is Locked) in situations in which one client is in an exclusive transaction and makes changes to a file that are not visible to other clients. For example, client 1 opens an empty file, begins an exclusive transaction, and inserts a record. Before client 1 ends the transaction, client 2 performs a Get First operation on the same file. Previous versions of Btrieve returned Status Code 85; Btrieve 7.0 returns Status Code 9 (End of File). For more information about the more informative status codes, see Status Codes and Messages. Improved Status Codes. Many new status codes have been added that enhance the quality of the information returned. For more information about these status codes, see Status Codes and Messages.
Product Update Description
21
Btrieve 7.0 Overview
The following MicroKernel status codes are new since Btrieve 6.15: 134
The MicroKernel cannot read the International Sorting Rule.
135
The specified ISR table is corrupt or otherwise invalid.
136
The MicroKernel cannot find the specified Alternate Collating Sequence in the file.
139
The MicroKernel has detected an unacceptable value in the key number.
140
The savepoint ID is invalid.
143
The MicroKernel cannot allow unauthorized access to files in a secure Scalable SQL database.
147
The log segment is missing.
148
A roll forward error occurred.
149
You must use Scalable SQL to make changes to this file.
151
The nesting level is too deep.
160
Invalid parameters passed to MicroKernel.
Product Update Description
22
Btrieve 7.0 Overview
The following communications status codes are new since Btrieve 6.15: 1020
Btrieve Requester Interface communications error.
1021
The MicroKernel failed to initialize.
2000
Internal error.
2009
Cannot load MicroKernel Router component.
2010
Thunk not possible—the Win32 MicroKernel Router could not be found or is incompatible.
2011
Resource DLL not loaded.
Status codes in the new 3000 range are returned by the MicroKernel router, which receives requests from the Btrieve client requesters and routes them to the correct instance of the MicroKernel. Status codes in the new 3100 range are returned by the Pervasive Network Services Layer (that is, the communications requester). Status codes in the new 7000 range are returned by the User Count components. Status codes in the new 8000 range are returned by the Services DLL. The following status codes were active in Btrieve 6.15, but are now obsolete: 38
The MicroKernel encountered a transaction control file I/O error.
77
The application encountered a wait error.
79
A programming error occurred.
Product Update Description
23
Btrieve 7.0 Overview
83
The MicroKernel attempted to update or delete a record that was read outside the transaction.
92
The transaction table is full.
1001
The lock parameter is out of range.
1004
A page size error occurred.
1009
The transaction file name parameter is invalid.
1010
An error occurred during the access of the transaction control file.
Product Update Description
24
Btrieve 7.0 Overview
Installation Toolkit If you are a Pervasive Manufacturing Partner, you can use Pervasive’s Partner Installation Toolkit to bundle all or part of a Pervasive.SQL product installation with your application’s installation procedure. The Installation Toolkit incorporates InstallShield technology and runs on Windows 3.x, Windows NT, and Windows 95 platforms. For more information, refer to the Manufacturing Partner Handbook. For information about becoming a Manufacturing Partner, contact Pervasive Software.
File Conversion Pervasive.SQL 7.0 uses a new file format, which supports the new large files and transaction durability features. If you want to use these features, convert your files to 7.x format. For more information, see Chapter 10, ”Converting MicroKernel Data Files,” in the Pervasive.SQL User’s Guide. Pre-7.0 engines cannot open 7.x files; however, the 7.0 MicroKernel can open pre-7.0 files. When the MicroKernel opens a pre-7.0 file, it does not convert the file to 7.x format. (You can configure the MicroKernel to create pre-7.0 files. This may be useful if you want to use newly created files with earlier engines.)
Compatibility Pervasive.SQL 7.0 is compatible with all Btrieve 6.15 features and functions, including the Btrieve API and data file formats (4.x, 5.x, and 6.x). Applications developed for the Btrieve 6.15 server or workstation engines will run on Btrieve 7.0. Moreover, 6.15 applications running in mixed environments with 6.15 and 7.0 engines on multiple platforms are also fully supported as long as the data files are in 6.x format.
Product Update Description
25
Btrieve 7.0 Overview
Note
Applications developed for Btrieve 7.0 are not guaranteed to run on 6.15 engines, particularly if they make use of Pervasive.SQL 7.0 features. Also, 7.0 utilities are not backward compatible; they must be run with 7.0 engines. More importantly, 6.15 utilities will not run against the 7.0 engine, because they make assumptions about the underlying Btrieve implementation.
Pervasive.SQL’s Btrieve Interface is also compatible with Scalable SQL 3.01, Scalable SQL 4.x, and all versions of the ODBC Interface. If Btrieve and either Scalable SQL or ODBC Interface are installed, the Btrieve 7.0 services should be started before the Scalable SQL service.
Product Update Description
26
Btrieve 7.0 Overview
chapter
2
Scalable SQL 4.0 Overview
Scalable SQL version 4.0 is backwardly compatible with Scalable SQL 3.01. This manual explains the differences between versions 3.01 and 4.0, discusses compatibility issues and support for existing 3.01 applications, recommends techniques and alternatives for obsolete functionality, and documents supported 3.01 features such as stored statements and relational primitives. This manual is intended for Btrieve 6.15 and Scalable SQL 3.01 users and developers only. If you are a new user of Btrieve 7.0 or Scalable SQL 4.0, or need information about core product features, refer to the documentation included with your Pervasive.SQL product. For general information about Pervasive Software, refer to the Pervasive Products and Services manual. Applications log in to Scalable SQL 4.0 in one of two mutually exclusive compatibility modes.
Version 3.01 compatibility mode (for existing applications) Version 4.0 compatibility mode (for new applications)
For more information about these compatibility modes, refer to Chapter 3, “Scalable SQL Compatibility.” This section contains the following topic:
“Compatibility Feature Summary”
Product Update Description
27
Scalable SQL 4.0 Overview
Compatibility Feature Summary The following sections identify the features included with Scalable SQL 4.0, and in which compatibility modes they are supported. This section contains the following topics:
“Features” “Data Types” “Keywords” “Scalable SQL Statements” “Scalable SQL APIs” “Inscribe Compatibility”
Features Table 2-1 lists the features supported in each compatibility mode.
Table 2-1
Features Supported in 3.01 and 4.0 Compatibility Mode
Feature
Supported in 3.01 Mode
Supported in 4.0 Mode
Creating and Executing Stored Procedures
No
Yes
Creating Triggers
No
Yes
Executing Triggers
Yes
Yes
Product Update Description
28
Scalable SQL 4.0 Overview
Table 2-1
Features Supported in 3.01 and 4.0 Compatibility Mode continued
Feature
Supported in 3.01 Mode
Supported in 4.0 Mode
Creating and Executing Stored Statements
Yes
No
Cursor-Based Statements (DECLARE, OPEN, CLOSE, FETCH, Positioned UPDATE, Positioned DELETE)
No
Yes
SQL Variables
No
Yes
Implicit nested transactions (Atomic statement execution)
Yes
Yes
SAVEPOINT (explicit nested transactions)
No
Yes
For more information about the features supported in Scalable SQL 4.0, refer to the Database Design Guide. For information about stored statements supported in 3.01 compatibility mode, refer to Chapter 4, “Obsolete Scalable SQL Features.”
Data Types Table 2-2 lists the data types that are supported in both 3.01 and 4.0 compatibility mode.
Table 2-2
Data Types Supported in 3.01 and 4.0 Compatibility Modes
AUTOINC
BFLOAT
BIT
CHARACTER
CURRENCY*
DATE
DECIMAL
FLOAT
INTEGER
LOGICAL
LSTRING
LVAR
MONEY
NOTE
NUMERIC
NUMERICSA
Product Update Description
29
Scalable SQL 4.0 Overview
Table 2-2 NUMERICSTS
Data Types Supported in 3.01 and 4.0 Compatibility Modes continued TIME
TIMESTAMP*
UNSIGNED*
ZSTRING Data types marked with an asterisk (*) are new with Scalable SQL 4.0. For more information about these data types, refer to the SQL Language Reference. Note
Any new keywords required for supporting the new data types are also supported in 3.01 compatibility mode.
Keywords Table 2-3 lists the keywords that are supported in 3.01 compatibility mode and in 4.0 compatibility mode. For a list of keywords supported only in 4.0 compatibility mode, refer to Table 2-4 For more information about keywords, refer to the SQL Language Reference.
Table 2-3
Keywords Supported in Both 3.01 and 4.0 Compatibility Modes
ACCELERATED
ADD
ALL
ALTER
AND
ANY
AS
ASC
AUTOINC
AVG
BEGIN
BEGINS
BETWEEN
BFLOAT
BINARYNULL
BIT
BLANK
BY
CASCADE
CASE
CHAR
CHARACTER
COMMIT
CONTAINS
Product Update Description
30
Scalable SQL 4.0 Overview
Table 2-3
Keywords Supported in Both 3.01 and 4.0 Compatibility Modes continued
COUNT
CREATE
CREATETAB
CS
CURDATE
CURTIME
DATE
DAY
DCOMPRESS
DEC
DECIMAL
DECIMALNULL
DEFAULT
DELETE
DESC
DICTIONARY
DISTINCT
DROP
END FLOAT
EX
EXCLUSIVE
EXEC*
EXECUTE*
EXISTS
FOR
FOREIGN
FROM
GRANT
GROUP
HAVING
HOUR
IN
INDEX
INSERT
INT
INTEGER
INTO
IS
ISOLATION
KEY
LEFT
LENGTH
LIKE
LOGICAL
LOGIN
LOWER
LSTRING
LTRIM
LVAR
MASK
MAX
MILLISECOND
MIN
MINUTE
MOD
MODIFY
MONEY
MONTH
NORMAL
NOT
NOTE
NULL
NUMERIC
NUMERICSA
NUMERICSTS
ON
OPENMODE
OR
Product Update Description
31
Scalable SQL 4.0 Overview
Table 2-3
Keywords Supported in Both 3.01 and 4.0 Compatibility Modes continued
ORDER
OWNER
OWNERACCESS
PAGESIZE
PREALLOCATE
PRIMARY
PROC
PROCEDURE
PUBLIC
RANGE
READONLY
REFERENCES
REPLACE
RESTRICT
REVOKE
RIGHT
ROLLBACK
RTRIM
SECONDS
SECURITY
SEG
SELECT
SET
SOME
START
STATEMENT
STRINGNULL
SUBSTR
SUBSTRING
SUM
TABLE
THRESHOLD
TIME
TO
TRANSACTION
UNION
UNIQUE
UPDATE
UPPER
USER
USING
VALUES
VERIFY
VIEW
WEEKDAY
WHERE
WITH
WORK
YEAR
ZSTRING
*The EXECUTE (or EXEC) keyword is only supported in Scalable SQL 3.01, or in 3.01 compatibility mode with the Scalable SQL 4.0 engine. EXEC and EXECUTE are recognized as keywords in the Scalable SQL 4.0 engine, but the statements associated with these keywords are not supported in 4.0 mode.
Product Update Description
32
Scalable SQL 4.0 Overview
Table 2-4 lists the keywords that are only supported in 4.0 compatibility mode. These keywords are new with Scalable SQL 4.0. For more information about these keywords, refer to the SQL Language Reference.
Table 2-4
Keywords Supported in 4.0 Compatibility Mode Only
ABSOLUTE
AFTER
ATOMIC
BEFORE
BEGIN
CALL
CHAIN
CLOSE
CONDITION
CONTINUE
CURRENT
CURSOR
DECLARE
DO
EACH
ELSE
ELSEIF
END
EXIT
EXTERNAL
FETCH
FIRST
FOR
HANDLER
IF
INOUT
INCLUDE
LAST
LEAVE
LOOP
NEW
NEXT
NO
NOTFOUND
OF
OLD
OPEN
OUT
PRIOR
READ
REFERENCING
RELATIVE
RELEASE
RESIGNAL
ROW
SAVEPOINT
SCROLL
SIGNAL
SQLEXCEPTION
SQLSTATE
SQLWARNING
THEN
TRIGGER
VALUE
WHEN
WHILE
Product Update Description
33
Scalable SQL 4.0 Overview
Scalable SQL Statements Table 2-5 lists the statements and in which compatibility mode they are supported.
Table 2-5
Statements Supported in Both 3.01 and 4.0 Compatibility Modes
Statement
Supported in 3.01 Mode
Supported in 4.0 Mode
ALTER TABLE
Yes
Yes
BEGIN...END
No
Yes
CALL
No
Yes
CLOSE (cursor)
No
Yes
COMMIT WORK
Yes
Yes
COMMIT WORK AND CHAIN
No
Yes
CREATE DICTIONARY
Yes
Yes
CREATE GROUP
Yes
Yes
CREATE INDEX
Yes
Yes
CREATE PROCEDURE
Yes*
Yes*
CREATE TABLE
Yes
Yes
CREATE TRIGGER
No
Yes
CREATE VIEW
Yes
Yes
DECLARE (variable)
No
Yes
Product Update Description
34
Scalable SQL 4.0 Overview
Table 2-5
Statements Supported in Both 3.01 and 4.0 Compatibility Modes continued
Statement
Supported in 3.01 Mode
Supported in 4.0 Mode
DECLARE CONDITION
No
Yes
DECLARE CURSOR
No
Yes
DECLARE HANDLER
No
Yes
DELETE
Yes
Yes
DELETE (positioned)
No
Yes
DROP DICTIONARY
Yes
Yes
DROP GROUP
Yes
Yes
DROP INDEX
Yes
Yes
DROP PROCEDURE
Yes
Yes
DROP TABLE
Yes
Yes
DROP TRIGGER
No
Yes
DROP VIEW
Yes
Yes
EXECUTE
Yes
No**
FETCH
No
Yes
GRANT (access rights)
Yes
Yes
GRANT CREATETAB
Yes
Yes
Product Update Description
35
Scalable SQL 4.0 Overview
Table 2-5
Statements Supported in Both 3.01 and 4.0 Compatibility Modes continued
Statement
Supported in 3.01 Mode
Supported in 4.0 Mode
GRANT LOGIN
Yes
Yes
IF
No
Yes
INSERT
Yes
Yes
LEAVE
No
Yes
LOOP
No
Yes
OPEN (cursor)
No
Yes
RELEASE SAVEPOINT
No
Yes
RESIGNAL
No
Yes
REVOKE (access rights)
Yes
Yes
REVOKE CREATETAB
Yes
Yes
REVOKE LOGIN
Yes
Yes
ROLLBACK TO SAVEPOINT
No
Yes
ROLLBACK AND CHAIN
No
Yes
ROLLBACK WORK
Yes
Yes
SAVEPOINT
No
Yes
SELECT
Yes
Yes
Product Update Description
36
Scalable SQL 4.0 Overview
Table 2-5
Statements Supported in Both 3.01 and 4.0 Compatibility Modes continued
Statement
Supported in 3.01 Mode
Supported in 4.0 Mode
SET
No
Yes
SET BLANK
Yes
No
SET CHAR
Yes
Yes
SET DATAPATH
Yes
Yes
SET DDPATH
No
No
SET DEFAULT
Yes
Yes
SET (global null value)
Yes
Yes
SET ISOLATION
Yes
Yes
SET MASK
Yes
Yes
SET OPENMODE
Yes
Yes
SET OWNER
Yes
Yes
SET RANGE
Yes
Yes
SET SECURITY
Yes
Yes
SET VALUES
Yes
Yes
SET VIEWPATH
No
No
SIGNAL
No
Yes
Product Update Description
37
Scalable SQL 4.0 Overview
Table 2-5
Statements Supported in Both 3.01 and 4.0 Compatibility Modes continued
Statement
Supported in 3.01 Mode
Supported in 4.0 Mode
START TRANSACTION
Yes
Yes
UPDATE
Yes
Yes
UPDATE (positioned)
No
Yes
WHILE
No
Yes
*Although these statements use the same keywords (CREATE PROCEDURE), they are not functionally identical. Refer to Chapter 4, “Obsolete Scalable SQL Features,” for more information about the CREATE PROCEDURE statement for creating stored statements. Refer to the SQL Language Reference for more information about the CREATE PROCEDURE statement for creating stored procedures. **The EXECUTE (or EXEC) keyword is only supported in Scalable SQL 3.01, or in 3.01 compatibility mode with the Scalable SQL 4.0 engine. EXEC and EXECUTE are recognized as keywords in the Scalable SQL 4.0 engine, but the statements associated with these keywords are not supported in 4.0 mode.
Scalable SQL APIs For Scalable SQL Developers Only: Table 2-6 lists the APIs and in which compatibility mode they are supported.
Table 2-6
APIs Supported in Both 3.01 and 4.0 Compatibility Modes
APIs
Supported in 3.01 Mode
Supported in 4.0 Mode
xAccess
Yes
Yes
xChar
Yes
iType=0 is not valid. All other options and iType values are valid.
Product Update Description
38
Scalable SQL 4.0 Overview
Table 2-6
APIs Supported in Both 3.01 and 4.0 Compatibility Modes continued
APIs
Supported in 3.01 Mode
Supported in 4.0 Mode
xCompute
Yes
No
xConvert
Yes
Yes, including the new convert options for XQLConvert.
xDD
Yes
Yes
xDDAttr
Yes
Yes
xDDCreate
Yes
Yes
xDDDrop
Yes
Yes
xDDField
Yes
Yes
xDDFile
Yes
Yes
xDDIndex
Yes
Yes
xDDModify
Yes
Yes
xDDPath
Yes
Option 2 is valid, but only when you specify the view directory as the same directory as the data dictionary directory; all other options are valid.
xDDView
Yes
Yes
xDescribe
Yes
Yes
xFetch
Yes
Yes
Product Update Description
39
Scalable SQL 4.0 Overview
Table 2-6
APIs Supported in Both 3.01 and 4.0 Compatibility Modes continued
APIs
Supported in 3.01 Mode
Supported in 4.0 Mode
xField
Yes
No
xFree
Yes
No
xGetSessionID
Yes
Yes
xInsert
Yes
Yes
xJoin
Yes
No
xLogin
Yes
Yes
xLogout
Yes
Yes
xMask
Yes
Yes
xMoveFld
Yes
No
xNew
Yes
No
xOrder
Yes
No
xPassword
Yes
Yes
xPutSessionID
Yes
Yes
xRecall
Yes
No
xRemall
Yes
No
xRemove
Yes
Yes
xReset
Yes
Yes
xRestrict
Yes
No
Product Update Description
40
Scalable SQL 4.0 Overview
Table 2-6
APIs Supported in Both 3.01 and 4.0 Compatibility Modes continued
APIs
Supported in 3.01 Mode
Supported in 4.0 Mode
xSecurity
Yes
Yes
xShareSessionID
Yes
Yes
xStatus
Yes
Yes
xStop
Yes
Yes
xStore
Yes
No
xTrans
Yes
Yes
xUpdall
Yes
No
xUpdate
Yes
Yes
xUser
Yes
Yes
xValidate
Yes
Yes
xVersion
Yes
Yes
SQLGetCountDatabaseNames
Yes
Yes
SQLGetCountRemoteDatabaseNames
Yes
Yes
SQLGetDatabaseNames
Yes
Yes
SQLGetRemoteDatabaseNames
Yes
Yes
SQLUnloadDBNames
Yes
Yes
XQLCallback
Yes
Yes
XQLCompile
Yes
Yes
Product Update Description
41
Scalable SQL 4.0 Overview
Table 2-6
APIs Supported in Both 3.01 and 4.0 Compatibility Modes continued
APIs
Supported in 3.01 Mode
Supported in 4.0 Mode
XQLConvert
Yes
Yes
XQLCursor
Yes
Yes
XQLDescribe
Yes
Yes
XQLExec
Yes
Yes
XQLFetch
Yes
Yes
XQLFormat
Yes
Yes
XQLFree
Yes
Yes
XQLLogin
Yes
Yes
XQLLogout
Yes
Yes
XQLMask
Yes
Yes
XQLSPUtility
Yes
No
XQLStatus
Yes
Yes
XQLStop
Yes
Yes
XQLSubst
Yes
Yes
XQLValidate
Yes
Yes
XQLVersion
Yes
Yes
For more information about the APIs supported in Scalable SQL 4.0, refer to the SQL Programmer’s Guide included with your Pervasive.SQL Developer Kit.
Product Update Description
42
Scalable SQL 4.0 Overview
Inscribe Compatibility If you have Inscribe scripts compiled in a previous version of Softbridge Basic Language (SBL) from the original Scalable SQL 4 release, you need to recompile them with the new SBL provided with the components installed via the Programmer Interface.
Product Update Description
43
Scalable SQL 4.0 Overview
chapter
3
Scalable SQL Compatibility
This chapter discusses the following topics regarding compatibility between Scalable SQL 3.01 and Scalable SQL 4.0:
“Compatibility Modes” “Naming Conventions” “Stored Statements and Procedures” “Version 3.01 and 4.0 Behavioral Differences”
Product Update Description
44
Scalable SQL Compatibility
Compatibility Modes To run new applications with Scalable SQL 4.0, you use the 4.0 (ANSI-compatible) compatibility mode. To run existing applications with Scalable SQL 4.0, you use the 3.01 compatibility mode. Although Scalable SQL continues to support non-standard parsing elements, follow these guidelines for any new application development for maximum compatibility. You can compile and execute statements in either mode with identical results if they do not contain any of the following constructs. These elements behave differently in each mode:
Dictionary names containing embedded blanks, arithmetic operators, etc. Complex arithmetic expressions that are not fully parenthesized Procedure syntax, either in 3.01 or 4.0 form Strings with double quotes Constant Values
You can maintain existing non-standard dictionary names in ANSI-compatible mode using double quotes. See “Naming Conventions” for more information.
Product Update Description
45
Scalable SQL Compatibility
Login Parameter Note
This section applies to Scalable SQL developers only.
The XQLLogin function establishes a session between Scalable SQL and your application. When you log in, the compatibility mode for that session is set to the mode specified in the iFeaturesUsed (formerly iReserved) parameter of an xLogin or XQLLogin API call. The iFeaturesUsed parameter denotes the earliest supported engine version. For more information on xLogin, refer to Chapter 5, “Relational Primitives.” For more information on XQLLogin, refer to the SQL Programmer’s Guide. If this parameter is set to 1, the compatibility mode is 4.0, or ANSI-compatible. If this parameter is set to 0, the mode is compatible with Scalable SQL 3.01. In a session, any cursor allocated in that session inherits the session’s compatibility mode. Therefore, a session can only submit statements in one mode. You must use 3.01 compatibility mode for some relational primitives and all stored statements. These are only processed if the session in which you submitted them was in Scalable SQL 3.01 compatibility mode. You receive Status Code 362 if you attempt to submit them in a 4.0 compatibility mode session.
Product Update Description
46
Scalable SQL Compatibility
Naming Conventions Scalable SQL 3.01 uses the following naming conventions. For information on naming conventions in Scalable SQL 4.0, refer to the Database Design Guide.
Naming Database Elements When naming tables and columns, begin names with a letter followed by up to 19 letters, numbers, or the caret, underscore, or tilde characters. The blank replacement character is supported for existing 3.01 or earlier applications only; do not use the blank replacement character in any new development in Scalable SQL 4.0. You receive Status Code 362 if you attempt to set the blank replacement character for a 4.0 compatibility mode session. Refer to Chapter 4, “Obsolete Scalable SQL Features,” for more information on the blank replacement character.
Operator Precedence In Scalable SQL 3.01 and in 3.01 compatibility mode of Scalable SQL 4.0, parentheses define the precedence of operations in an expression. If none of the operations in the expression are enclosed within parentheses, Scalable SQL evaluates the expression from left to right. However, always fully parenthesize arithmetic expressions for maximum compatibility with Scalable SQL 4.0. Use: Avoid:
WHERE ((X + Y) * Z) > 100 WHERE X + Y * Z > 100
Product Update Description
47
Scalable SQL Compatibility
In 4.0 compatibility mode, if none of the operations in the expression are enclosed within parentheses, Scalable SQL evaluates the expression using standard operator precedence rules.
Specifying String Data In Scalable SQL 3.01 and in 3.01 compatibility mode with the Scalable SQL 4.0 engine, single and double quotes are interchangeable for indicating string data. In Scalable SQL 4.0 compatibility mode, data in double quotes is interpreted as a database component name (such as a column, procedure, or table name), or as a parameter name in a stored procedure. For compliance with the ANSI standard, when representing string data, use only single quotes. For example:
WHERE ID = '000123'
Product Update Description
48
Scalable SQL Compatibility
Stored Statements and Procedures Scalable SQL 4.0 supports stored statement syntax (in 3.01 compatibility mode), but enhanced procedure functionality is available only in 4.0 compatibility mode, through a new ANSI-based procedure syntax. Scalable SQL 3.01 allows you to create stored statements, which are statements compiled and saved in the data dictionary for later execution. Stored statements execute as a linear sequence of SQL statements. Scalable SQL 4.0 introduces stored procedures that allow control structures and other useful enhancements such as branching, looping, and variables. Although stored statements are supported in 4.0 for existing applications, you cannot invoke a stored statement from a stored procedure, and you cannot invoke a stored procedure from a stored statement. Stored statements will become obsolete in future releases. You must establish a session in 3.01 compatibility mode in order to create or execute stored statements. Using the 4.0 stored procedures, you can migrate program logic from the application into the database itself. SQL stored procedures are logically self-contained; they always execute to completion as their internal logic dictates. No intervention from the application is required or allowed. For more information on stored procedures, see the Database Design Guide.
Product Update Description
49
Scalable SQL Compatibility
Behavioral Differences of Stored Statements and Procedures Table 3-1 summarizes the differences between stored statements and stored procedures.
Table 3-1
Stored Statement and Stored Procedure Comparison
Stored Statements
Stored Procedures
Parameter list must not include parentheses or commas.
Require parentheses around the parameter list and commas between the arguments.
Include @ prefix for parameter declaration and reference.
Include no prefix for parameter declaration or reference.
CREATE PROCEDURE statement includes the keyword AS between the parameter list and statements.
CREATE PROCEDURE statement includes the semicolon (optional) between the parameter list and body of the procedure.
Invoked using the EXECUTE statement or the procedure name.
Invoked using the CALL statement.
Statements you use to create stored statements and stored procedures both start with the keywords CREATE PROCEDURE, but the syntax differs significantly for each. Stored statements do not require parentheses around the list of parameters, and stored procedures do require them. The @ prefix is required before each parameter in stored statements, and no prefix is required in stored procedures for parameter declaration and reference. Both stored statements and stored procedures must have a unique name. In Scalable SQL 3.01 and in 3.01 compatibility mode with the 4.0 engine, the CREATE PROCEDURE statement includes the keyword AS between the parameter list and the
Product Update Description
50
Scalable SQL Compatibility
statements. In 4.0 compatibility mode, Scalable SQL 4.0 uses the semicolon (;) between the parameter list and the body of the procedure; however, this semicolon is optional. The DROP PROCEDURE statement can delete stored statements and stored procedures in the same way. However, when you create a stored statement, and a stored statement with the same name already exists, the existing statement definition is overwritten with the new definition. When you create a stored statement or a stored procedure, and a stored procedure with the same name already exists, you receive Status Code 366. If you create a stored procedure, and a stored statement with the same name already exists, you receive Status Code 366. In both cases, the existing definition is kept in the dictionary. You invoke stored statements using the EXECUTE statement. You invoke stored procedures using the CALL statement. Following are the differences between these two statements: EXECUTE
CALL
Keyword is optional.
Keyword is required.
Argument list must not include parentheses.
Argument list must include parentheses.
Not allowed in a stored procedure.
Not allowed inside an EXECUTE statement.
For more information about using the CALL statement to invoke a stored procedure, refer to the SQL Language Reference. For more information about the EXECUTE statement, refer to Chapter 4, “Obsolete Scalable SQL Features.”
Product Update Description
51
Scalable SQL Compatibility
Converting Stored Statements to Stored Procedures Scalable SQL 4.0 stores stored procedures in a different format than that used by Scalable SQL 3.01. Scalable SQL 4.0 cannot execute stored statements created with Scalable SQL 3.01. You must recompile your CREATE PROCEDURE statements using Scalable SQL 4.0 by resubmitting the CREATE PROCEDURE statement to the Scalable SQL 4.0 engine. This replaces the existing stored statement definition with a Scalable SQL 4.0 definition. For compatibility purposes, stored statements and stored procedures must have unique names. When an application creates a stored statement, an existing stored statement of the same name is implicitly dropped; when the CREATE PROCEDURE statement for a stored statement is passed to the Scalable SQL 4.0 engine, it replaces a previous declaration of a stored statement (if any already exist) with the same name. If a stored procedure exists with the specified name, the statement fails and you receive an error status code. Scalable SQL 4.0 introduces SQL control statements, which can only be used in the body of a stored procedure. For more information about control statements, see the Database Design Guide.
Product Update Description
52
Scalable SQL Compatibility
Version 3.01 and 4.0 Behavioral Differences This section explains the behavioral differences between Scalable SQL 3.01 and Scalable SQL 4.0. For more information on Scalable SQL 4.0 functionality, refer to the documentation included with your product.
Changes to 3.x Dictionaries When you use Scalable SQL 4.0 to access 3.x dictionaries, Scalable SQL 4.0 makes the following changes to the dictionaries:
Adds a column to the X$File system table, so that the number of bytes in the record for the table definition matches the number of bytes in the data file definition. The new column, Xf$Reserved, is the last column in the table. Adds two indexes to the X$Relate system table, if that system table exists, as follows:
Index Number
Segment Number
Column Name
Duplicates Case Insensitive Segmented
3
0
Xr$Pld
No
Yes
Yes
3
1
Xr$Name
No
Yes
No
4
0
Xr$Fld
No
Yes
Yes
4
1
Xr$Name
No
Yes
No
Product Update Description
53
Scalable SQL Compatibility
Transaction Processing In Scalable SQL 3.01 you can either commit all changes you made during the current SQL transaction or abort those changes. In Scalable SQL 4.0, you can define additional markers, called savepoints, in a SQL transaction. You can then undo the changes you made after any savepoint in the transaction and make additional changes before the final commit or abort of the entire transaction. For more information on savepoints and transaction processing, refer to the Database Design Guide.
Temporary Sort Files The Scalable SQL 4.0 engine behaves differently than the Scalable SQL 3.01 engine when handling temporary sort files, as follows:
Scalable SQL 4.0 may build temporary sort files in situations in which Scalable SQL 3.01 did not, and vice-versa. If Scalable SQL 4.0 builds a temporary sort, it is built at the beginning of the fetch operation, rather than at the end of the XQLCompile operation. This may affect applications that were programmed to handle a pause for sorting at a specific point.
Unqualified Column Names Scalable SQL 3.01 uses the physical order of column names in the dictionary and uses the first column name that is also in one of the tables in the view. If more than one column has the same name, the column that is used is unpredictable.
Product Update Description
54
Scalable SQL Compatibility
Scalable SQL 4.0 (in both 3.01 and 4.0 compatibility modes) uses the order of the tables in the FROM clause and uses the first column that is in one of the tables. Scalable SQL 4.0 operates just as Scalable SQL 3.01 when non-qualified column names are used in the SET VALUES, RANGE, MASK, and CHAR statements.
Conventions for Constants In Scalable SQL 3.01, the following constant is valid:
$-1.00 In Scalable SQL 4.0, this format is invalid. You must specify the minus sign before the dollar sign, as follows:
-$1.00 In Scalable SQL 3.01, the following time constant is valid:
1:10:10 In Scalable SQL 4.0, this format is invalid. You must specify two digits for all components of a constant time value, as follows:
01:10:10
Product Update Description
55
Scalable SQL Compatibility
Constants and Column Masks In 3.01 mode, SET clauses in UPDATE statements and WHERE clauses in UPDATE and SELECT statements have the following rules:
LOGICAL and BIT values must conform to the default mask or be the constant number values 0 for false and 1 for true. All other values must conform to the default mask, except as follows: Number values can be free-form number constants. DATE values can be single-digit month or day values, if the constant is enclosed in single quotes. Also, year values can be four digits. If you specify a two-digit month value, you can specify a single-digit day. TIME values can be single-digit hour, minute, and second values, if the constant is enclosed in single quotes. If you specify a two-digit hour value, you can specify singledigit minute and second values. TIME values can include hundredths of seconds.
VALUES clauses in INSERT statements have the following rules: You must specify column values of non-string data types using either the
column’s defined mask or a default input format. (For more information about default input formats, refer to the SQL Language Reference.) For data types INTEGER, AUTOINC, and UNSIGNED, Scalable SQL rounds the number to the nearest whole number value. For data types DATE and TIME, Scalable SQL implicitly aligns the constant to the column’s default mask or the default input format. For data types CHARACTER, LSTRING, and ZSTRING, column masks have no
effect on the result, even when the mask specifies fewer characters than the column size and the input value is longer than the mask.
Product Update Description
56
Scalable SQL Compatibility
If the VALUES clause contains a substitution variable, then the constant subsequently assigned to the substitution variable has the same requirements as a constant that appears in the VALUES clause.
CREATE TABLE Syntax In Scalable SQL 3.01, the syntax of the CREATE TABLE statement allowed you to separate the USING clause from the WITH REPLACE clause. In Scalable SQL 4.0, if you include the WITH REPLACE clause, it must immediately follow the USING clause. The USING clause can appear anywhere an option can appear (i.e., it does not have to be the first option as currently written in the documentation). For more information and a complete description of the CREATE TABLE statement, refer to the Scalable SQL Reference.
CASE Attribute on Columns and Indexes In Scalable SQL 4.0, you can use the CASE keyword on a column or index segment in a CREATE TABLE statement. If a column is a segment within an index, you must apply the CASE keyword to both the column and the index segment or to neither. You cannot use it for only the column or only the index segment. If the column and index segment do not match in an existing table definition, the index is not used for optimization to satisfy a characteristic of the statement for the column. For example, if the column appears in a predicate or ORDER BY clause, the index is not used to locate the rows that satisfy the predicate or read the rows indicated in the ORDER BY clause. For this reason, apply the CASE keyword to both the column and all index segments that use that column. Product Update Description
57
Scalable SQL Compatibility
Most Scalable SQL 3.01 applications and databases are set up so that an index is used as expected despite the column and segment CASE attributes being unmatched. In Scalable SQL 4.0, these applications may behave differently. To work around this, you can use SQLScope to alter your columns to match the index. For information on the ALTER TABLE statement, refer to the SQL Language Reference. Scalable SQL supports a configurable option to notify you if a table definition in the dictionary does not match the data file definition. Use the Setup Utility to set the Check Table Definition to ON. When you query a table, Scalable SQL will return status 353 if the table definition does not match the data file definition. You can then use SQLScope to review the table definition. Typically, the definitions will be out of sync if the data file was created using a Btrieve application and then later added to a Scalable SQL data dictionary. You can use the Btrieve File Manager to display the attributes of the MicroKernel file and compare it to what is stored in the dictionary.
Views You must convert your VIEW.DDF files for your existing 3.01 applications to Scalable SQL 4.0 before you can query a stored view. To do so, you can use the View Conversion utility provided with Scalable SQL 4.0, or you can re-create your views in 4.0 as you originally created them in 3.01. For more information on the View Conversion utility, refer to the README file.
Views and Compatibility Modes You can store views in one compatibility mode and recall them in another; however, you may experience unexpected results if you are simultaneously accessing views in both compatibility modes.
Product Update Description
58
Scalable SQL Compatibility
In 3.01 compatibility mode, your application may create SQL-level views (using CREATE VIEW statements) or primitive-level views (using xStore or an application that calls xStore). The Scalable SQL engine creates views only for the level at which you created them. Therefore, if you issue a CREATE VIEW statement and attempt to recall the view using an application that uses the relational primitives, you receive an error. If you issue an xStore call and attempt to recall the view using a SQL statement, you also receive an error. Scalable SQL preserves the semantic meaning of the statement for the mode in which it is stored. Applications in different modes may have to use different syntax when recalling such views. You cannot always determine the compatibility mode of the application storing the view based on the CREATE VIEW statement. When you store a view, Scalable SQL uses the current compatibility mode to resolve issues for the statement in the view. For example, following is an example CREATE VIEW statement.
CREATE VIEW v1 (CUST^ID) AS SELECT cust^Id FROM accts^payable If the application recalling this view is in 3.01 compatibility mode, and the blank character is set to '^', this view is stored as though both the heading name and column name are CUST ID. If the application recalling this view is in 4.0 compatibility mode, it may appear to be accessing the view heading CUST ID in a statement such as the following:
SELECT CUST^ID FROM v1
Product Update Description
59
Scalable SQL Compatibility
However, it receives a Status Code 207 because the view column name is CUST ID. The application in 4.0 compatibility mode should use the following form:
SELECT "CUST ID" FROM v1 A view is stored with the operator precedence of the compatibility mode of the application in which it is stored. For example:
CREATE VIEW v1 (c1, c2) AS SELECT c1, c1 + 0.57 * c2 FROM cTable This statement in an application in 3.01 compatibility mode stores this view with the following precedence:
CREATE VIEW v1 (c1, c2) AS SELECT c1, (c1 + 0.57) * c2 FROM cTable An application in 4.0 compatibility mode stores it as follows:
CREATE VIEW v1 (c1, c2) AS SELECT c1, c1 + (0.57 * c2) FROM cTable When storing views, always fully parenthesize all arithmetic calculations and eliminate nonstandard identifiers wherever possible. For more information on views in Scalable SQL 4.0, refer to the Database Design Guide.
Product Update Description
60
Scalable SQL Compatibility
chapter
4
Obsolete Scalable SQL Features
This chapter details the features that are not supported in Scalable SQL 4.0, are only supported in 3.01 compatibility mode with the 4.0 engine, or that will become obsolete in future versions. Refer to your Pervasive.SQL 7.0 documentation for information on supported functionality in Scalable SQL 4.0. This section contains the following topics:
“Obsolete Requester” “Obsolete Functionality” “Obsolete Syntax” “Obsolete API” “Obsolete Status Codes”
Obsolete Requester In Scalable SQL 4.0, the SQLREQ.EXE file is similar to the Scalable SQL 3.01 SQLREQS.EXE file. Scalable SQL 4.0 does not include a SQLREQS.EXE file.
Product Update Description
61
Obsolete Scalable SQL Features
Obsolete Functionality The following functionality is included for support of existing applications prior to Scalable SQL 4.0. Do not use this functionality in new application development with the Scalable SQL 4.0 engine.
Blank Replacement Character The blank replacement character is not supported in 4.0 compatibility mode in Scalable SQL 4.0. If you create database elements in 4.0 compatibility mode using characters that could serve as the blank replacement character in 3.01 compatibility mode, the element names actually contain that character. To include blanks in element names, use one of the following methods:
Create the element in 4.0 compatibility mode placing double quotes around a name that contains blanks. Create the element in 3.01 compatibility mode using the defined blank replacement character.
You must use the correct syntax in the corresponding mode to access those elements. For example, the following statement is from an application in 4.0 compatibility mode:
CREATE TABLE T^1 (C^1 INT (2), C^2 CHAR (10)) An application in 3.01 compatibility mode with the blank replacement character set to a caret (^) is not able to access this table or its columns. If you set the blank character to
Product Update Description
62
Obsolete Features in Scalable SQL
some other value, it can access these elements. For example, the following statement is from an application in 4.0 compatibility mode:
CREATE TABLE “T 1” (“C 1” INT (2), “C 2” CHAR (10)) An application in 3.01 compatibility mode with the blank replacement character set to a caret (^) can access this table and its columns.
Blank Replacement Character in 3.01 Following is a description of the blank replacement character, which is only applicable in 3.01 compatibility mode. If the name of a database component contains a blank, you must replace the blank with another character when you specify the name to Scalable SQL. This allows Scalable SQL to distinguish between the blanks in names and the blanks between elements of restriction clauses. By default, Scalable SQL expects a caret to replace blanks in names. However, you can use the SET BLANK statement to change the blank replacement character to an underscore or a tilde. Since the valid blank replacement characters are the caret, underscore, and tilde, do not use one of these characters when defining a name if you intend to use that character as the blank replacement character. Note
Blank replacement characters are not allowed in names for parameters and substitution variables.
Product Update Description
63
Obsolete Features in Scalable SQL
Data File Formats The Scalable SQL 4.0 utilities support the following data file formats:
SDF (Standard Data Format, or comma delimited) ASC (ASCII format) UNF (Unformatted)
Scalable SQL 4.0 does not support DIF (Data Interchange Format). This format is supported in Scalable SQL 3.01, but is obsolete in Scalable SQL 4.0. For more information on the supported data file formats, refer to the Pervasive.SQL User’s Guide.
Operators The following operators are not supported in 4.0 compatibility mode in Scalable SQL 4.0. Use the equivalent keywords instead. Operator
Description
&&
And
||
Or
?
Begins with
#
Contains
!#
Does not contain
##
Like
!##
Not like
=NULL
Is null
NULL
Is not null
Product Update Description
64
Obsolete Features in Scalable SQL
Refer to the Database Design Guide for more information on these and other operators in Scalable SQL 4.0.
Stored Statements This section explains how to create stored SQL statements in 3.01 compatibility mode. This information is provided for compatibility with existing applications. All new development should take advantage of stored procedures. For details on creating stored procedures, refer to the Database Design Guide.
Creating Stored Statements You can use a CREATE PROCEDURE statement to create a stored statement. For more information about using this statement to create stored statements, see "Obsolete Syntax."
Rules for Creating Stored Statements The following rules apply to creating stored statements:
You must name each stored statement, which must meet the following criteria: Must begin with a letter. Cannot be a reserved word. Cannot exceed 30 characters. Must be unique among all stored statements and stored procedures in the database. If the name is already in use by another stored statement definition, the existing definition is overwritten. If the name is in use by a stored procedure, you receive Status Code 366.
Product Update Description
65
Obsolete Features in Scalable SQL
If you specify a stored statement within another stored statement, precede the nested stored statement with the EXECUTE statement. Do not include the following statements within a stored statement: CREATE DICTIONARY
DROP DICTIONARY
CREATE PROCEDURE
DROP PROCEDURE
CREATE TRIGGER
DROP TRIGGER
CREATE VIEW
DROP VIEW
You cannot use clauses that specify referential constraints within a stored statement. Do not create stored statements that call themselves (i.e., recursive stored statements). You can create such a stored statement, but you will receive Status Code 326 every time you try to run it. Use recursive stored procedures instead of stored statements. You cannot use a stored statement to delete a table from a dictionary if the stored statement also includes an UPDATE, DELETE, INSERT, or SELECT statement acting on that table.
Stored Statement Name Strings You can create and execute stored statements only in 3.01 compatibility mode. Thus, all 3.01 mode elements, such as blank replacement characters, operator precedence, and double-quoted strings, remain in effect.
Product Update Description
66
Obsolete Features in Scalable SQL
Parameters to Stored Statements You can use parameters as placeholders for information that may vary when you invoke a stored statement. Parameters allow you to pass values to the statement when you execute it. Precede each parameter in the statement definition with an at (@) symbol. You can specify parameters in place of string or numeric constants in the following types of clauses:
WHERE HAVING VALUES (in an INSERT statement) SET (in an UPDATE statement)
The following example creates a stored statement that uses a parameter for zip code. You can run this statement several times and specify a different zip code value each time.
CREATE PROCEDURE maillist @zip CHAR(5) AS SELECT First_Name, Last_Name, Address, City, State, Zip FROM Person WHERE Zip = @zip You must declare all parameters in a stored statement in the parameter list of the procedure. You can pass the values for the parameters to the stored statement in one of two ways:
With the EXECUTE statement As defaults specified in the CREATE PROCEDURE statement
Product Update Description
67
Obsolete Features in Scalable SQL
If you specify default values as part of the statement declaration, Scalable SQL uses them whenever you execute the statement without providing specific values for those parameters. For example, the following statement declares parameters @parm1 and @parm2 with default values of VALUE1 and 100, respectively:
CREATE PROC test1 @parm1 CHAR(10) = 'value1', @parm2 INTEGER = 100 AS SQL_Statements If you do not specify default values when you create the statement, you must supply values at execution time; otherwise, Scalable SQL returns Status Code 808. Values supplied at execution time always take precedence over default values. The following example creates stored statement Test2, which updates the Billing table using an ID and Amount Paid value provided at runtime:
CREATE PROC test2 @parm1 MONEY(8,2), @parm2 CHAR(6) AS UPDATE Billing SET Amount_Paid = @parm1 WHERE ID = @parm2 The following example creates stored statement Maillist, which accepts a parameter for the Zip column:
CREATE PROCEDURE Maillist @zipcode CHAR (10) AS SELECT Last_Name, Street, ID FROM Person p, Student s WHERE p.ID = s.ID and ZIP = @zipcode
Product Update Description
68
Obsolete Features in Scalable SQL
Rules for Parameter Names The following rules apply to the names of parameters:
The name can contain only alphanumeric characters, and must begin with a letter. The name also cannot contain blanks or the special characters ^ (caret), _ (underscore), and ~ (tilde). The name cannot exceed 20 characters. The name must not be a reserved keyword. Each parameter for a stored SQL statement must be unique. The name is not case-sensitive.
You define these names by preceding them with an at (@) sign inside a SQL statement string.
Parameters to Nested Stored Statements You can nest stored statements. The statements you specify in the AS clause of the CREATE PROCEDURE statement can be stored statements themselves. These statements are referred to as nested stored statements. Although you can nest statements, you cannot call them recursively because stored statements provide no conditional statements to allow an exit from a recursive loop. Calling statements recursively generates Status Code 326 when the stored statement executes. To pass a parameter to a nested stored statement, you must declare the parameter at the first level and then pass it on at each subsequent stored statement level until it reaches the applicable level. For example, in order for a fourth-level nested stored statement to
Product Update Description
69
Obsolete Features in Scalable SQL
receive and use a parameter value, you must pass the parameter through the outer three levels of stored statements. The following example creates a stored statement that calls two nested stored statements:
CREATE PROC caller @var1, @var2 AS EXEC sub1 @var1 EXEC sub2 @var2 In this statement, caller is the outer statement. The nested stored statements (nested together on the same level) are sub1 and sub2. In the following example, the CREATE PROCEDURE statement specifies a default value while creating sub1 and sub2. When executing the statement caller, however, Scalable SQL uses the set of values specified at runtime; the supplied values 10 and 12 override the defaults.
CREATE PROCEDURE sub1 @var1 = 2 AS ... CREATE PROCEDURE sub2 @var2 = 4 AS ... CREATE PROCEDURE caller @var1, @var2 AS EXEC sub1 @var1 EXEC sub2 @var2 EXEC caller 10, 12 If you execute caller without specifying values, Scalable SQL uses the default values, 2 and 4.
Product Update Description
70
Obsolete Features in Scalable SQL
Compiling Stored Statements If the statement is an EXEC statement, XQLCompile returns an informative code indicating that the stored statement has been successfully recalled and can now be executed. If you pass a SQL variable as an argument to a parameter of a stored statement through an EXEC statement, you must declare the SQL variable as a session variable if you submit the EXEC through an XQLCompile or you must declare it as a procedure owned variable in the same procedure in which you issue the EXEC. If you provide a parameter reference as an argument, it must be a declared parameter of the same procedure in which you issue the EXEC. An EXEC submitted through an XQLCompile cannot contain a parameter reference.
Executing Stored Statements You use an EXECUTE statement to run a previously stored statement. For more information on the EXECUTE statement, refer to "EXECUTE Statement." You can use XQLExec to execute a stored SQL statement. You can also use XQLExec to initiate execution of a stored SQL statement after recalling it with XQLCompile. Scalable SQL executes each statement within the stored statement until it finds either a SELECT statement or no more statements. If Scalable SQL finds a SELECT statement, control returns to your application with a Status Code 0. You must then use XQLFetch to fetch the data. After fetching the data, you can use XQLExec to continue executing the statements in the stored statement. When executing stored SQL statements, XQLExec returns an error status code for the following conditions:
The stored SQL statement is not active when you make the call. A stored statement becomes active when you have successfully recalled it with
Product Update Description
71
Obsolete Features in Scalable SQL
XQLCompile; the stored statement remains active until you compile another SQL statement with the same cursor ID or release the cursor ID.
An error occurs during the execution of the stored statement. Note
You cannot use XQLSubst to pass parameter values into a stored SQL statement. Attempting to do so causes Scalable SQL to return an error. For example, you might receive Status Code 866.
You can include previously stored statements by name in other stored statements, but you cannot call them recursively, and you cannot create a stored statement that calls itself. If you do, Scalable SQL returns Status Code 326 when the stored statement executes. The following stored statement is valid and is not recursive:
CREATE PROCEDURE doall AS EXECUTE maillist EXECUTE phonelist The following stored statement is recursive and therefore invalid:
CREATE PROCEDURE doall AS SELECT First_Name, Last_Name, Address, City, State, Zip FROM Person EXECUTE doall EXECUTE phonelist
Product Update Description
72
Obsolete Features in Scalable SQL
Obsolete Syntax The following statements are included for support of existing applications prior to Scalable SQL 4.0. Do not use these statements in new application development with Scalable SQL 4.0.
CREATE PROCEDURE Syntax for Stored Statements The CREATE PROCEDURE statement allows you to create a new stored statement or replace an existing one. A stored statement consists of one or more statements that are precompiled and saved in the dictionary. To execute stored statements, use the EXECUTE statement described in the section "EXECUTE Statement." The stored statement name must be a valid dictionary name. The maximum length of the name is 30 bytes. Following is the syntax for the CREATE PROCEDURE statement:
CREATE PROCEDURE statement_name [ [ @parameter_name [data_type] [= value ]] [, @parameter_name [data_type] [= value ]]] AS SQL_Statements Note
Scalable SQL creates a procedure upon successful execution of the CREATE PROCEDURE statement. Even if you include the statement in a transaction, you cannot roll back the changes that the statement made.
Product Update Description
73
Obsolete Features in Scalable SQL
EXECUTE Statement The EXECUTE statement allows you to recall a stored statement that has previously been compiled with a CREATE PROCEDURE statement. The EXECUTE (or EXEC) keyword is optional. However, when you create a stored statement that calls a previously defined stored statement, you must specify the EXECUTE (or EXEC) keyword in your CREATE PROCEDURE statement. Following is the syntax for the EXECUTE statement:
[EXECUTE | EXEC] statement_name [ @parm [= value] [ @parm [= value] ...] | value [ value ...]] Note
You cannot use the EXECUTE statement in a trigger or within any stored procedure that directly or indirectly invokes a trigger. The EXECUTE statement is for use with stored statements only. For more information on stored procedures and triggers, refer to the Database Design Guide.
If the statement contains parameters, you can include values for them in the EXECUTE statement. Any values you specify override the defaults specified when you created the statement. If you omit the values when you execute the statement, Scalable SQL uses the default values. (If you do not supply a value for a parameter that does not have a default value, Scalable SQL returns Status Code 808 when you try to execute the stored statement.) You can assign the parameter values using the following format:
[EXEC] statement_name [ @parm [= value] [ @parm [= value] ...] ] Product Update Description
74
Obsolete Features in Scalable SQL
When you use this format, you can place the value assignments in any order. Alternatively, you can simply list the values for the multiple parameters after the statement name:
[EXEC] statement_name [ value [ value ...] ] When you use this format, you must list the values in the order in which the parameters occur in the stored statement.
Example The following statement defines two parameters without default values:
CREATE PROC test3 @testid CHAR(6), @testname CHAR(20) AS SELECT * FROM Person p, Appointments a WHERE p.ID = @testid AND p.Last_Name = @testname Since no default values are specified in this CREATE PROCEDURE statement, you must assign values to the parameters when you execute Test3. The next example shows one way to do this. Because @testID appears before @testname in the statement that created Test3, the value for @testID must appear before the value for @testname in the parameter list:
EXEC test3 'RJ0002' 'Reilly'
Product Update Description
75
Obsolete Features in Scalable SQL
SET BLANK Statement The SET BLANK statement allows you to define the character to replace blanks in table names and column names. Scalable SQL does not store the setting in the dictionary; the blank replacement character you specify is used during the current session only. Following is the syntax of the SET BLANK statement:
SET BLANK = 'character' If you define a character to denote a blank, and existing tables or columns use that character in their names, you must set the blank character to a different value before you can access those tables or columns. Scalable SQL does not allow database element names in SQL statements to contain blanks. Unless you use a SET BLANK statement to specify a blank replacement character, Scalable SQL expects an underscore (_) in place of a blank in a column name or table name. You can specify any of the following characters to replace blanks: _
Underscore (default)
~
Tilde
^
Caret
Note
Do not use one of the valid blank replacement characters when defining a name if you intend to use that character as the blank replacement character.
Product Update Description
76
Obsolete Features in Scalable SQL
Example The following statement defines the tilde ( ~) character as the blank replacement character for the current Scalable SQL session:
SET BLANK = '~' After issuing this statement, use the tilde character to replace blanks in column names and table names:
SELECT First~Name FROM Person
Obsolete Functionality of INSERT Statement The INSERT statement allows you to insert column values into one or more tables. When you issue an INSERT statement, Scalable SQL validates the data you specify and inserts the values into the designated table or tables. The following behaviors of the INSERT statement are not supported in Scalable SQL 4.0:
INSERT INTO T1 VALUES (ABC, DEF) In Scalable SQL 3.01, the two columns in this statement are string columns and the statement inserts the strings ‘ABC’ and ‘DEF’ into the columns. In Scalable SQL 4.0, you receive a Status Code 807 from this statement, indicating you must enclose the strings in quotes.
INSERT INTO T2 VALUES ('ABC', , 'DEF') In Scalable SQL 3.01, there is no value supplied for the second string so the NULL value is inserted. In Scalable SQL 4.0, you receive a Status Code 221 from this statement. Product Update Description
77
Obsolete Features in Scalable SQL
Obsolete API Note
For Scalable SQL Developers Only.
The following API is included for support of existing applications prior to Scalable SQL 4.0. Do not use this API in new application development with the Scalable SQL 4.0 engine. XQLSPUtility
Controls the execution of a stored SQL statement, or obtains information about the stored statement.
XQLSPUtility The XQLSPUtility function allows you to control the execution of a stored SQL statement or obtain information about the stored statement. For example, you can do the following:
Disable or enable the execution of a particular SQL statement within the stored statement. Return the number of SQL statements in the stored statement. Return execution statistics for the SQL statements.
In the following discussion, an active stored statement is a stored statement that you have successfully recalled with XQLCompile; the stored statement remains active until you compile another SQL statement with the same cursor ID or release the cursor ID.
Product Update Description
78
Obsolete Features in Scalable SQL
Parameter Summary The following table summarizes the parameters for XQLSPUtility. Note
XQLSPUtility allows you to perform different operations depending on which option you choose for iOption. The values that you specify for the other parameters may vary between options.
Passed by: Parameter
Description
Value Ref.
iCursorID
Cursor ID for the active stored statement (a stored statement successfully recalled with XQLCompile)
iOption
Value of the option to specify
iStatementCount Count of the SQL statements or the number of a particular statement iStatementExec
Number of SQL statements the last XQLExec call executed
iBufLen
Length in bytes of bDataBuf
bDataBuf
Information about the SQL statements in the active stored statement
Product Update Description
79
Obsolete Features in Scalable SQL
Options The XQLSPUtility function allows you to specify one of the following values for the iOption parameter: Value Description 0
Returns the number of SQL statements in the stored statement.
1
Returns the type and execution status of each SQL statement in the stored statement.
2
Returns execution statistics for all SQL statements (within the stored statement) that the last XQLExec call executed.
3
Returns the execution statistics for a particular SQL statement in the stored statement.
4
Enables or disables the execution of a particular SQL statement in the stored statement.
Return the Number of SQL Statements (Option 0) Option 0 returns in iStatementCount the total number of SQL statements contained in the active stored statement. Parameter
Setting Parameter Values for Option 0
iCursorID
Specify the cursor ID for the active stored SQL statement.
iOption
Specify 0 to return the number of SQL statements.
iStatementCount
Initialize this parameter to 0 to obtain the total number of SQL statements in the active stored statement.
iStatementExec
Initialize this parameter to 0.
iBufLen
Initialize this parameter to 0.
bDataBuf
Pass the address of the data buffer.
Product Update Description
80
Obsolete Features in Scalable SQL
Return the SQL Statement Types and Execution Statuses (Option 1) Option 1 returns in bDataBuf the type and execution status of each SQL statement in the active stored statement. Table 4-1 shows the structure of the data buffer. Option 1 returns the values in the Statement Type and Execution Status columns of bDataBuf. The option does not return any information in the other data buffer columns. Option 1 returns information for all SQL statements in the stored statement, regardless of whether they have been executed. Parameter
Setting Parameter Values for Option 1
iCursorID
Specify the cursor ID for the active stored SQL statement.
iOption
Specify 1 to return the type and execution status of each SQL statement in the stored statement.
iStatementCount
Initialize this parameter to 0.
iStatementExec
Initialize this parameter to 0.
iBufLen
Specify the size of bDataBuf.
bDataBuf
Pass the address of the data buffer. Allocate 44 bytes for each SQL statement in the stored statement. You can use Option 0 to determine the total number of SQL statements in the stored statement.
Product Update Description
81
Obsolete Features in Scalable SQL
Return Execution Statistics for All Statements (Option 2) Option 2 returns in bDataBuf the execution statistics for all SQL statements (within the stored statement) that the last XQLExec call executed. Also, Option 2 returns in iStatementExec the number of SQL statements that the last XQLExec call executed. Table 4-1 shows the structure of the data buffer. Parameter
Setting Parameter Values for Option 2
iCursorID
Specify the cursor ID for the active stored SQL statement.
iOption
Specify 2 to return the execution statistics for all SQL statements that the last XQLExec call executed.
iStatementCount
Initialize this parameter to 0.
iStatementExec
Initialize this parameter to 0 to obtain the number of SQL statements that the last XQLExec call executed.
iBufLen
Specify the size of bDataBuf.
bDataBuf
Pass the address of the data buffer. Allocate 44 bytes for each SQL statement in the stored statement. You can use Option 0 to determine the total number of SQL statements in the stored statement.
Return the Execution Statistics for a Particular SQL Statement (Option 3) Option 3 returns in bDataBuf the execution statistics for a particular SQL statement in the stored statement. If the SQL statement has not been previously executed, Scalable
Product Update Description
82
Obsolete Features in Scalable SQL
SQL does not return any execution statistics. Table 4-1 shows the structure of the data buffer. Parameter
Setting Parameter Values for Option 3
iCursorID
Specify the cursor ID for the active stored SQL statement.
iOption
Specify 3 to return the execution statistics for a particular SQL statement in the stored statement.
iStatementCount
Specify the number of the particular SQL statement. The numbering of the SQL statements in a stored statement begins with 1.
iStatementExec
Initialize this parameter to 0.
iBufLen
Specify the size of bDataBuf.
bDataBuf
Pass the address of the data buffer. Allocate 44 bytes for the execution statistics.
Enable or Disable the Execution of a Particular SQL Statement (Option 4) Option 4 allows you to enable or disable the execution of a particular SQL statement in a stored statement. The value that you specify in the Execution Status column of bDataBuf determines whether the execution is enabled or disabled. (Table 4-1 shows the structure of the data buffer.) A value of 1 enables execution; a value of 0 disables execution. By default, when you first recall a stored statement, the execution status of every SQL statement in the stored statement is enabled. Parameter
Setting Parameter Values for Option 4
iCursorID
Specify the cursor ID for the active stored SQL statement.
iOption
Specify 4 to enable or disable the execution of a particular SQL statement in the stored statement.
iStatementCount
Specify the number of the particular SQL statement. The numbering of the SQL statements in a stored statement begins with 1.
Product Update Description
83
Obsolete Features in Scalable SQL
Parameter
Setting Parameter Values for Option 4
iStatementExec
Initialize this parameter to 0.
iBufLen
Specify the size of bDataBuf.
bDataBuf
Pass the address of a structure of 44 bytes as shown in Table 4-1. To disable the execution of a particular statement, set the Execution Status column of the data buffer to 0. To enable the execution of a statement, set the Execution Status column to 1. Initialize all other columns in the data buffer to 0 to ensure future compatibility.
bDataBuf Parameter Table 4-1 shows the structure of the execution statistics data buffer. The columns are shown in the order in which they appear in the buffer.
Table 4-1
Execution Statistics Data Buffer
Size
Type
Column
2
INTEGER
Statement Type
2
INTEGER
SQL Statement Status
4
INTEGER
Select Count
4
INTEGER
Reject Count
2
INTEGER
Execution Status
30
STRING
Error ID
The following sections describe each column in the execution statistics data buffer.
Product Update Description
84
Obsolete Features in Scalable SQL
Statement Type The Statement Type column identifies the type of SQL statement executed. The following list shows the numbers that appear in the Statement Type column when you execute a SQL statement that begins with one of the following keywords: ALTER
0
REVOKE
7
COMMIT
1
ROLLBACK
8
CREATE
2
SELECT
9
DELETE
3
SET
10
DROP
4
START
11
GRANT
5
UPDATE
12
INSERT
6
SQL Statement Status The SQL Statement Status column contains the current SQL-level function status code associated with the specified SQL statement. This column does not contain a Status Code until you execute the statement.
Select Count The Select Count column is relevant only for SELECT, UPDATE, INSERT, or DELETE statements. This column indicates the number of rows that the specified statement selected for processing.
Product Update Description
85
Obsolete Features in Scalable SQL
Reject Count The Reject Count column is relevant only for SELECT, UPDATE, INSERT, or DELETE statements. This column contains a count of the number of rows that the specified statement rejected for processing.
Execution Status The Execution Status column contains a flag that you can use to enable or disable execution of a particular SQL statement within a stored statement. If the Execution Status column contains a nonzero value, the statement executes. If the value is zero, the statement does not execute.
Error ID When the SQL Statement Status column returns a positive status code, the Error ID column may contain a name up to 30 bytes long. This name identifies the column associated with the status code in the SQL Statement Status column. If no identifier is associated with the specific status code, Scalable SQL fills this column with blanks.
Error Conditions XQLSPUtility might return one of the following status codes: Status Code
Description
821
The specified cursor ID is in an invalid state for this operation, or is not an allocated cursor ID.
822
The data buffer is too small to hold the requested data.
Product Update Description
86
Obsolete Features in Scalable SQL
Call Specifications C/C++ BTI_API XQLSPUtility ( BTI_SINT iCursorID, BTI_SINT iOption, BTI_SIZE BTI_FAR iStatementCount, BTI_SIZE iStatementExec, BTI_SIZE BTI_FAR iBufLen, BTI_CHAR_PTR bDataBuf); Pascal FUNCTION XQLSPUtility( iCursorID : INTEGER; iOption : INTEGER; VAR iStatementCount : INTEGER; VAR iStatementExec : INTEGER; VAR iBufLen : INTEGER; VAR bDataBuf) : INTEGER; Visual BASIC Declare Function XQLSputility Lib “wxqlcall.dll” (ByVal iCursorID%, ByVal iOption%, iStatementCount As Integer, iStatementExec As Integer,iBufLen As Integer, bDataBuf As Any) As Integer Product Update Description
87
Obsolete Features in Scalable SQL
BASIC CALL XQLSPUTILITY (iStatus%, iCursorID%, iOption%,iStatementCount%, iStatementExec%, iBufLen%, bDataOff%, bDataSeg%) MicroFocus COBOL CALL “_XQLSPUTILITY” USING ISTATUS, ICURSORID,IOPTION, ISTATEMENTCOUNT,ISTATEMENTEXEC, IBUFLEN, BDATABUF.
Product Update Description
88
Obsolete Features in Scalable SQL
Obsolete Status Codes The following status codes and messages apply only to 3.x Scalable SQL engines or to Scalable SQL 3.01 functionality only supported in 3.01 compatibility mode in the 4.0 engine. For more information and a complete listing of status codes and messages, refer to the Status Codes and Messages manual. Note
Status codes in the range 2401 are returned from the DataLens Driver, and status codes in the range 2500 are returned when executing embedded SQL statements. Both of these functions have not been supported for some time; for more information on these codes and products, contact your application vendor.
Table 4-2
Obsolete Status Codes
Status Code
Description
-115
Scalable SQL successfully recalled the stored SQL statement.
-116
Scalable SQL successfully executed the stored SQL statement.
16
The application encountered an expansion error.
17
The application encountered a close error.
23
The position block parameter is not 128 bytes long.
31
The file is already extended.
34
The specified extension name is invalid.
38
The MicroKernel encountered a transaction control file I/O error.
40
The file access request exceeds the maximum number of files allowed.
42
A file previously opened in Accelerated mode was not closed.
Product Update Description
89
Obsolete Features in Scalable SQL
Table 4-2
Obsolete Status Codes continued
Status Code
Description
47
The number of files opened exceeds the maximum allowed.
52
An error occurred while writing to the cache.
57
An expanded memory error occurred.
74
The MicroKernel aborted the transaction.
77
The application encountered a wait error.
79
A programming error occurred.
83
The MicroKernel attempted to update or delete a record that was read outside the transaction.
89
A name error occurred.
90
The redirected device table is full.
92
The transaction table is full.
98
The MicroKernel detected an internal transaction error.
130
The MicroKernel ran out of system locks.
218
You have exceeded the maximum number of tables allowed in a join.
219
You can specify a maximum of 24 index segments for a join operation.
227
The expressions must be column names or constants.
230
The restriction clause is incomplete.
236
You cannot update a view that is ordered on an external index.
249
The data types of the columns are incompatible.
263
You cannot sort a view by a variable-length column.
Product Update Description
90
Obsolete Features in Scalable SQL
Table 4-2
Obsolete Status Codes continued
Status Code
Description
285
Scalable SQL cannot open the system tables.
287
No data dictionary is currently active.
289
The specified dictionary name is invalid.
297
You cannot delete the column from the view.
298
A string constant contains mismatched quotes.
300
A conditional computed column must contain a :: operator.
301
No restriction clause has been defined for the view.
302
The specified column is not a computed column.
303
Cannot update current sort index if more than one file is in the view.
307
The computed column you specified for the join does not exist in the view.
315
An application cannot change the dictionary or data path within a transaction.
324
The restriction clause or computed column contains an unmatched parenthesis.
327
At least one index must be defined without the NULL attribute.
332
You must convert the dictionary for use with Scalable SQL 3.x.
335
The specified join option is invalid.
339
You cannot perform an insert, update, or delete operation on a view containing a self join.
345
The computed column is invalid.
347
A column descriptor is missing.
505
Each item in the attribute list must be enclosed in quotation marks.
530
You must supply a dictionary path after the equal sign.
Product Update Description
91
Obsolete Features in Scalable SQL
Table 4-2
Obsolete Status Codes continued
Status Code
Description
534
You must include SELECT in a CREATE VIEW statement.
543
You must enclose a string in single quotation marks.
550
An alias name has already been defined for the table.
558
You cannot use a group aggregate function within an expression.
561
A CREATE PROCEDURE statement must include a name for the stored SQL statement.
563
You specified a default for a parameter that cannot have a default value.
568
A CREATE TABLE statement cannot reference the same table more than once.
809
The specified column is not in the recalled view.
815
The specified join column is invalid.
824
The statement contains an unmatched parenthesis.
829
The syntax for the computed column definition is invalid.
832
The statement cannot be recalled because it is not of the expected size.
843
An OS path must be specified if Add, Modify, or Drop is not entered.
847
The syntax in the WHERE clause is invalid.
848
A statement may contain only one DISTINCT keyword.
849
The buffer is not large enough for Scalable SQL to process the WHERE clause.
851
In an INSERT or UPDATE statement, a subquery cannot access the table being updated.
854
At least one index must be defined for this operation.
857
You cannot use the equal to (=) operator with ALL in a WHERE clause.
Product Update Description
92
Obsolete Features in Scalable SQL
Table 4-2
Obsolete Status Codes continued
Status Code
Description
860
The buffer is not large enough for Scalable SQL to store the view definition.
861
A value must follow the equal sign (=).
863
Scalable SQL cannot recall the stored SQL statement.
865
You cannot update columns that exceed 255 characters by using an UPDATE statement.
866
The XQLSubst function call is invalid.
1001
The lock parameter is out of range.
1004
A page size error occurred.
1009
The transaction file name parameter is invalid.
1010
An error occurred during the access of the transaction control file.
1012
Invalid /n: option.
1016
The MicroKernel is already initialized.
2107
The application has activated the maximum number of views.
2109
You have attempted to attach to too many servers.
2112
The Scalable SQL Requester is not loaded.
Product Update Description
93
Obsolete Features in Scalable SQL
chapter
5
Relational Primitives
Using the Application Programming Interface (API) for the Scalable SQL Developer Kit, your applications can query, update, and manage database files, dictionaries, and system security. The Scalable SQL API set includes the following types of APIs:
SQL-level functions Database names functions Relational primitives
The relational primitives are supported for interactive programs that need to perform other operations between the relational database operations, such as confirming an operation on a particular row. Note
The relational primitives documented in this chapter are included for support of existing applications prior to Scalable SQL 4.0, but will become obsolete in future releases. Use equivalent SQL functionality instead of these primitives. Relational primitives are the lowest level of the Scalable SQL APIs. The relationalprimitive level includes over 30 functions. Your application can call any of them directly. These functions provide a relational link between your database application and the MicroKernel Database Engine. The relational primitives are divided into the following categories:
Data definition functions Data manipulation functions Data control functions
Product Update Description
94
Relational Primitives
The following relational primitives are now documented in the SQL Programmer’s Guide. The remaining primitives are documented in Pervasive’s Web site (www.pervasive.com). If you do not have Web access, contact
[email protected] via e-mail, or send mail to the address on this manual to the attention of the Documentation Group and request another on-line format for the document.
xDD xDDAttr xDDField xDDFile xDDIndex xFetch xInsert xRemove xReset xStatus xUpdate xAccess xChar xGetSessionID xPassword xPutSessionID xUser
Product Update Description
95
Relational Primitives
Index A
C
Active stored statement 78 ANSI-compatible mode 45 APIs new features in Btrieve 7.0 20 obsolete in Scalable SQL 78 XQLSPUtility 78
CALL statement 51 CASE attribute 57 Characters, blank replacement 76 Cofiguration, in Btrieve 7.0 14 Column masks 56 Column names, unqualified 54 Communications, in Btrieve 7.0 14 Compatibility feature summary 28 APIs 38 keywords 30 statements 34 modes ANSI-compatible 45 iFeaturesUsed parameter 46 login parameter 46 version 3.01 compatibility 45 Pervasive.SQL and Btrieve versions 26 Constants 56 Conventions constants 55 naming 47 operator precedence 47 CREATE PROCEDURE statement 73 CREATE TABLE statement 57
B Blank character replacement 76 Blank replacement character about 62 version 3.01 63 Btrieve 7.0 API features 20 compatibility 25 enhancements communications 14 configuration 14 utilities 18 file conversion 25 MicroKernel features 11 overview 10 status codes, new 21 Product Update Description
96
Index
D
K
Data file formats, DIF 64 Data types in Btrieve 7.0 20 in Scalable SQL 29 DDF Ease 19
Keywords 30
L Large files 11 Login parameter 46
E
M
Event logging 12 EXECUTE statement 51, 74
Maintenance utility, enhanced 18 Masks 56 MicroKernel, features in Btrieve 7.0 11 Modes, compatibility ANSI-compatible 45 v3.01 compatibility 45 Monitor utility, enhanced 18
F Features Btrieve 7.0 10 Scalable SQL 28 File conversion 25 formats 64 handles 12
N Naming conventions blank replacement character 76 database elements 47 overview 47 NDS See Novell Directory Service support Novell Directory Service support 14
I iFeaturesUsed parameter 46 INSERT statement 77 Installation toolkit 25 InstallScout 18 International sort rules 12 ISRs See International Sort Rules. Product Update Description
97
Index
O
xLogin 46 Parsing 45 Pervasive.SQL compatibility with Btrieve, Scalable SQL, ODBC 26 event logging 12 utilities 18
Obsolete APIs overview 78 XQLSPUtility 78 features APIs, XQLSPUtility 78 blank replacement character 62 data file formats 64 functionality 62 operators 64 status codes 89 stored statements 65 Syntax 61 syntax 73 status codes 89 syntax CREATE PROCEDURE 73 EXECUTE 74 INSERT 77 SET BLANK 76 Operators 4.0 compatibility mode 64 precedence 47
R Rebuild utility, interactive 18 Recovering changes, and roll forward feature 12 Relational primitives 94 Roll forward, and recovering changes 12
S Scalable SQL compatibility issues 44 obsolete features 61 relational primitives 94 SET BLANK statement 76 Setting compatibility modes 46 Setup utility, enhanced 19 Smart Components 13 SmartScout 18 Sort files 54 SQL statements CREATE PROCEDURE 73 EXECUTE 74 SET BLANK 76
P Parameters iFeaturesUsed 46 login 46 nested stored statement 69 Product Update Description
98
Index
U
SQL-level functions, XQLSPUtility 78 Statements 34 Status Codes obsolete 89 Status codes, new in Btrieve 7.0 21 Stored procedures 49 Stored statements active 78 controlling execution with XQLSPUtility 78 converting 52 creating 65, 73 creating in version 3.01 65 executing 71, 74 names, string parameter format 66 nested statement parameters 69 obtaining information with XQLSPUtility 78 overview 49 parameters 67 String data 48 Syntax EXECUTE statement 74 INSERT statement 77 obsolete 73
User count stackable 13 utility 18 Utilities, enhancements in Pervasive.SQL 18
V Version differences CASE attribute 57 constants 55 overview of Scalable SQL 53 transaction processing 54 unqualified column names 53, 54 Views compatibility modes 58 converting 58
X XQLLogin call 46 XQLSPUtility 78
T TCP/IP 14 Temporary sort files 54 Transaction durability 11 Transaction processing 54
Product Update Description
99
Index
User Comments Pervasive Software would like to hear your comments and suggestions about our manuals. Please write your comments below and send them to us at:
Pervasive Software Inc. Documentation 8834 Capital of Texas Highway Austin, Texas 78759 USA
Product Update Description 100-003198-004 February 1998
Telephone: 1-800-287-4383 Fax: 512-794-1778 Email:
[email protected] Your name and title: Company: Address:
Phone number:
Fax:
You may reproduce these comment pages as needed so that others can send in comments also.
I use this manual as:
Completeness Readability (style) Organization/Format Accuracy Examples Illustrations Usefulness
an overview
a tutorial
a reference
a guide
Excellent
Good
Fair
Poor
Please explain any of your above ratings:
In what ways can this manual be improved?
You may reproduce these comment pages as needed so that others can send in comments also.
