KB_SQL Programmer s Reference Guide

(1)

KB_SQL

Programmer’s

Reference

(2)

(3)

Chapter 1 An Overview

Welcome to KB_SQL Version 3’s programming development tools, KB_ESQL and KB_SQL API, embedded structured query language and application programming interface, respectively. We think you’ll find this development milestone for KB Systems to be arguably the finest accomplishment among many significant new capabilities offered in this release. The culmination of a number of years of intensive research and development, KB_ESQL and KB_SQL API provide the exciting new dimension of programmatic access to information retrieval and analysis.

Many of you are already familiar with the power and flexibility of SQL through earlier releases of KB_SQL. Both the EZQ Report Writer and SQL Editor harness SQL in an interactive mode. Now that same dazzling capability can be built into your own applications.

(6)

KB_SQL Programmer’s Reference Guide 2

SQL in Perspective

Originally developed by IBM Research Laboratories, SQL’s strength lies in its data and device independence. You are relieved of the nagging details of how data is physically accessed and can rely on one standard to access data wherever it resides — mainframes, minicomputers, or personal computers. In addition, because it has been adopted by ANSI as the

de facto standard for relational database access, you are assured of a reasonably long and stable life for your applications.

Easy to Learn

Approximately 30 commands comprise this elegant and concise query language with a core of four basic commands for data manipulation. The four commands (SELECT, UPDATE, DELETE, and INSERT) are used to form statements that closely resemble the structure of a natural English question or query. Once the basics are understood, a developer can adapt to the specifics of any SQL environment.

Piggybacking SQL

For all its strength and versatility, SQL is a special purpose database sublanguage; there are no provisions for declaring variables, testing for certain conditions, or constructing loops. Originally geared toward stand-alone, interactive use, the standard has evolved with an eye towards its programmatic implementation. In this way, existing, standardized

procedural languages may incorporate the special purpose functionality of SQL to deliver database access on an enterprise-wide scale.

(7)

Developing with KB_ESQL/KB_SQL API

Perhaps the most compelling reason for an application programmer to develop with KB_ESQL and KB_SQL API is the need to position your applications, as well as yourself, for the future. Astute developers recognize the rapid rise of client/server architecture as the result of a relentless, industry-wide objective to provide seamless cross-systems

intercommunication. Part and parcel of this movement is the ability to access data no matter what it looks like or where it resides.

Implementing an SQL interface in your application clearly promotes this objective.

To give you some feel for KB Systems’ blueprint for effecting this concept, see the diagram on the next page which illustrates KB Systems’ Client/Server Architecture. When fully implemented, it will provide transparent data access and facilitate intercommunication.

For purposes of this guide, however, we confine our discussion to Version 3’s two new programming tools, KB_ESQL and KB_SQL API, which use the KB_SQL Engine as a server.

(8)

KB Systems’ Client/Server Architecture

Whether you develop with KB_ESQL or KB_SQL API depends largely on the nature of the application. As a general rule, KB_ESQL is well-suited to static applications while KB_SQL API lends itself to ad hoc, dynamic applications. There is much common ground shared by either approach, minimizing exhaustive retraining.

Common Elements

Both approaches share the key concepts of environments, connections, statements, and cursors. In short, the environment is a distinct M $JOB; a connection establishes communication between the application and the database engine (even when both reside on the same system); a statement requests data; and a cursor is the mechanism by which you retrieve data. The following chapters provide more details on these concepts.

(9)

Distinctive Elements

KB_ESQL insulates your static applications from changes in the structure of the database. ESQL supports standard SQL as well as several extensions that are unique to ESQL. A precompiler translates ESQL into one or more M routines. The result is a reusable, executable program that is compiled one time and can be run many times. A typical best-use example for this approach would be the hypothetical development of an account management application for Intuit’s Quicken™ where the procedure is fixed.

KB_SQL API provides maximum flexibility to dynamic applications through the use of function calls from within the application. Unlike KB_ESQL, SQL commands are compiled or prepared at runtime. Here a best-use example would be the hypothetical development of an Microsoft Access® application that uses a query by form approach (an open-ended procedure) to determine what’s out there as the basis for a report.

(10)

Summary

KB_ESQL and KB_SQL API provide programmatic access to database resources. While each is best-suited to a particular type of application, both bring the power and versatility of SQL to your development efforts, positioning your applications for maximum interoperability.

The following two chapters, replete with diagrams and examples,

are designed to provide the information you need to develop simple to complex applications using KB_ESQL and KB_SQL API.

For a quick comparison of common functions of KB_ESQL and KB_SQL API, see Appendix A. KB Systems wishes you every success in your development efforts using KB_ESQL and KB_SQL API.

(11)

Chapter 2 Using the KB_ESQL

When special SQL statements are specified together with a traditional procedural language like M, the technique is generally referred to as embedded SQL (ESQL). The resulting blended source cannot be submitted to its host language compiler, or interpreter in the case of M, without producing an abrupt, if not screeching, halt in the process. A precompiler solves the problem by converting ESQL statements into one or more equivalent M routines. Once ESQL is introduced to the application, the developer realizes two significant advantages: first, the ability to reference data using table and column names, and second, simplified application maintenance.

This chapter describes the basic concepts of KB_ESQL to guide you in your application development. The examples that follow demonstrate the structure and capabilities of ESQL and will repay close attention.

(12)

8 KB_SQL Programmer’s Reference Guide

Choosing a Source Type

The process begins when you create and store the initial source of M code and ESQL statements in one of three available source types: a routine, a global, or a file. All of the source types have the same set of capabilities. Choose the type that is most convenient for your environment. For example, in a stand-alone M system, using the routine or global type avoids having to exit M to view or modify a source. Alternatively, if you are running a layered-windowing environment, using the file type allows you to use any non-M text editor.

Structural Characteristics

Structurally, the source resembles M routines: it is composed of one or more lines, each having two distinct pieces ⎯ a tag name, delimited by a tab character ($ASCII(9)), followed by a command string.

The usual M tag naming rules apply with one exception: the ampersand character (&) is also allowed under certain situations. The command string may contain any valid M commands and an ESQL statement.

An M command string follows a fairly simple structure consisting of one or more command-argument pairs. Each command-argument starts with a command or conditionalized command, followed by a space character, followed by an argument list. Each command-argument within the command string is also separated by a space character.

(13)

M command strings:

[command_argument<space>]...command_argument Examples:

S X=1

S X=1 D ^XXX

An ESQL statement may be defined at any command position within the command string; however, it must be the last command in the line. ESQL takes the format &SQL(statement).

Command string with ESQL:

[command_argument<space>]...ESQL_statement Examples:

&SQL(CONNECT TO M USER DBA AUTHORIZATION SHARK) S X=1 &SQL(FETCH C1 INTO :A,:B)

For readability, we recommend always using the first command position for ESQL statements.

If the statement is too long to fit on one line, additional continuation lines may be used by either placing a single ampersand in the tag position or two ampersands at the beginning of the command string. We illustrate below.

&SQL(CONNECT TO M USER DBA & AUTHORIZATION SHARK)

−OR−

&SQL(CONNECT TO M USER DBA && AUTHORIZATION SHARK)

Note: The use of double ampersands is our extension; it is not part of the M-SQL binding.

Following is a simple example to demonstrate the preceding remarks.

(14)

10 KB_SQL Programmer’s Reference Guide &SQL(update employees set salary = salary * 1.1

& where salary < 18) &SQL(commit) &SQL(disconnect) Q

(15)

Language Elements

ESQL supports a full implementation of Data Manipulation Language (DML), Data Definition Language (DDL), and Data Control Language (DCL). In addition, ESQL supports a variety of statements that are valid only in ESQL. The tables below summarize supported SQL statements; ESQL statements are listed in the table on the next page. For more

information on the use of all the various statements, refer to the KB_SQL Syntax Guide.

SQL Data Manipulation (DML)

SELECT Retrieves columns from tables. INSERT Adds new rows to a table. UPDATE Changes columns in a table. DELETE Removes rows from a table.

SQL Data Definition (DDL)

ALTER Changes a data dictionary object. CREATE Adds a new data dictionary object. DROP Removes data dictionary objects.

SQL Data Control (DCL)

GRANT Gives privileges to user groups. REVOKE Takes privileges away

(16)

12 KB_SQL Programmer’s Reference Guide ESQL Statements

CLOSE Deletes a named result set.

COMMIT Terminates current transaction and applies pending changes to the database.

CONNECT Establishes a link between an application and a database.

DECLARE CURSOR

Creates a positional reference to a result set. DELETE

(positioned)

Deletes the row specified by a cursor.

DISCONNECT Terminates a connection between an application and a database.

FETCH Positions a cursor on a specific result table row and retrieves the result column values from that row.

GET

DIAGNOSTICS

Returns status information on the last SQL statement.

OPEN Builds a named result set and positions the cursor prior to the first row.

ROLLBACK Terminates the current transaction and discards pending changes to the database.

SELECT INTO Selects data values from a particular row in a table producing a single row result set. SET

CONNECTION

Sets the active connection. SET

TRANSACTION

Defines transaction related parameters. UPDATE

(positioned)

(17)

Creating the Source

We turn now to a discussion of the manner in which the ESQL syntactical and semantic elements are combined within the source to access the database. Some fundamental concepts are connections, transactions, and cursors. We examine key details for each as they pertain to programming with M.

To preface our coverage, you should understand that a hierarchical relationship exists between connections, transactions, and cursors. All of these elements are part of the larger logical environment, that is, a distinct M $JOB. Each environment may establish one or more connections. Each connection may perform one or more sequential transactions. Each transaction, in turn, may declare one or more cursors that are unique to the transaction. Keep this hierarchy in mind as you review the following sections.

Connections

With the programmatic approach of ESQL, you must manually establish a connection between the application and the database engine, from client to server. Such a connection is necessary for intercommunication between the two entities, even when both

exist on the same computer. Clients like the SQL Editor and EZQ Editor establish an automatic, albeit transparent, connection to

(18)

14 KB_SQL Programmer’s Reference Guide Connection-Related Statements

CONNECT TO {server_name or DEFAULT} [AS connection_name]

[USER [user_name [SET] AUTHORIZATION] password] SET CONNECTION connection_name

DISCONNECT [ALL or CURRENT or DEFAULT or connection_name]

The CONNECT and DISCONNECT statements are required as a functional pair; you create a connection using the CONNECT statement and terminate the connection using the DISCONNECT statement as shown below. In addition, the CONNECT statement confers an active or inactive status to each connection.

A Functional Pair

CONNECT TO DEFAULT USER SHARK .

. .

DISCONNECT DEFAULT

Notice in the Connection-Related statements illustration that each CONNECT statement identifies a specific connection name that is unique within the environment. Using DEFAULT consistently for all connection_name references can provide greater portability since the local system name may be changed without affecting the ESQL code.

(19)

If the CONNECT statement contains the AS connection_name clause, the connection takes the specified name; otherwise, the connection name defaults to the server name. If a second connection name is then established by a second CONNECT statement, the first connection name automatically becomes inactive.

You may activate or inactivate a connection at any time by using the SET CONNECTION statement. Regardless of the number of established connections, only one connection may be active at a time.

Prior to the CONNECT, and after the DISCONNECT, the connection name and status are undefined. The table below outlines the parameters, values, and meanings of the connection name and status.

Connection Parameters

Parameter Value Meaning

Connection name

server_name Uses the local system name as the connection name.

connection_name Uses the specified name of the AS connection_name clause for the connection name.

Connection status

active All ESQL statements are sent to the active connection by the SQL engine.

inactive A connection is suspended until activated by a SET CONNECTION statement.

(20)

Transactions

Once a connection is established, the next phase of the process centers on the transaction. A transaction is a discrete unit of work composed of one of more statements that reference and/or modify the database.

The atomic execution, all or nothing, of the transaction is a specification of the SQL transaction model. The objective of that model is to maintain a consistent state in the database. Like the transaction, statements are atomic in and of

themselves. This means the programmer can accommodate a failure at the statement level without losing the entire transaction.

Two other provisions of the

SQL transaction model, concurrency control and recovery, are critical in a multi-user transaction environment. Briefly stated, concurrency control provides for multiple access without the loss or corruption of data while recovery ensures that partial transactions never become part of the database.

(21)

Transaction-Related Statements

Three statements, shown below, are integral to an ESQL transaction.

Transaction-Related Statements COMMIT [WORK]

ROLLBACK [WORK] SET TRANSACTION

[{AUTO COMMIT} or {MANUAL COMMIT}]

[ISOLATION LEVEL {READ UNCOMMITTED or READ COMMITTED}] [{READ ONLY} or {READ WRITE}]

Commit/Rollback

For each transaction, all database changes (work) associated with the transaction are either applied (COMMIT) or not applied (ROLLBACK). The COMMIT statement makes any modifications to the database permanent, and deletes all transaction-related structures. Conversely, the ROLLBACK statement discards all database modifications and deletes all transaction-related structures.

Other conditions may also terminate a transaction causing an implicit ROLLBACK including an unrecoverable system error, a data definition statement (CREATE, ALTER or DROP), or a data control statement (GRANT or REVOKE).

(22)

Setting Transaction Attributes

Default transaction parameters are established by the CONNECT statement. To use a different set of parameter values, the SET TRANSACTION statement should immediately follow the CONNECT statement to ensure consistency. Once assigned, transaction parameters should not be changed again until a COMMIT or ROLLBACK statement is executed, completing the current transaction.

Transaction Commit Modes

Each transaction has a commit mode of either AUTO COMMIT or MANUAL COMMIT. The default commit mode for ESQL is MANUAL COMMIT. The commit mode attribute that may be changed by the SET TRANSACTION statement. In the default MANUAL COMMIT mode, a transaction is initiated implicitly by the first SQL statement that causes a database reference or modification. Therefore, every subsequent reference to the database is considered part of that same transaction until the transaction is explicitly terminated by a COMMIT or ROLLBACK statement. An attempt to execute a DISCONNECT statement without completing a database update transaction returns an error (SQLSTATE=25000, invalid transaction state).

When the commit mode is set to AUTO COMMIT, each statement that completes is immediately committed, effectively starting and ending with statement execution. If the statement encounters an error, a rollback occurs automatically, thus ending the transaction.

(23)

Isolation Level

Another optional element of the SET TRANSACTION syntax is the isolation level of the transaction. The isolation level determines the degree to which separate simultaneous transactions are insulated from affecting each other, thus controlling their interaction. Version 3 provides two isolation levels: READ UNCOMMITTED or READ COMMITTED. The default is READ UNCOMMITTED. Your DBA may change this level using the Default transaction isolation level prompt of the CONFIGURATION/SITE EDIT/ANSI INFO option.

READ COMMITTED permits reads and modifications of committed rows only. It ensures your transaction does not read or modify a row another concurrent transaction has changed but not yet committed.

The READ UNCOMMITTED isolation level allows reads of pending transactions that have not been committed. However, it ensures your transaction does not modify a row that another concurrent transaction has changed but not yet committed.

The isolation level may be changed by a SET TRANSACTION statement.

Note: ANSI SQL2 also supports REPEATABLE READ and SERIALIZABLE levels, but these are not

implemented in Version 3.

Internally, the isolation level is implemented through means of automatic READ or WRITE locks that are transparent to the user. Their purpose is to ensure the consistency of the data for the duration of the transaction, through the final COMMIT or ROLLBACK. For more information on the subject of READ and WRITE locks, refer to the KB_SQL Data

(24)

Access Mode

The last option you may choose to set for the transaction is the access mode. Each transaction may be set to either READ ONLY or READ WRITE. The default is READ WRITE. Of course, the programmer compiling the embedded program (EP) must have the appropriate table privileges to successfully compile any INSERT, UPDATE, or DELETE statements. The access mode may be changed by using the SET TRANSACTION statement with the READ ONLY clause. When the mode is READ ONLY, all database updates are rejected.

(25)

Transaction Parameters

Commit Mode MANUAL COMMIT All transactions must be explicitly terminated by either a COMMIT or ROLLBACK statement.

AUTO COMMIT Each successful SQL statement is immediately committed; each statement that encounters an error is immediately rolled back.

Isolation Level READ COMMITTED The transaction returns a concurrency error if it attempts an action that would cause a READ or WRITE lock on a table row that has already been updated by another concurrent

transaction. (An update transaction causes a WRITE lock which is exclusive.)

READ UNCOMMITTED The transaction allows reads on rows that are WRITE locked by another concurrent transaction.

Access Mode READ WRITE The transaction may read and update table rows.

READ ONLY The transaction may read table rows, but cannot update table rows.

(26)

Cursors

While the set-at-a-time processing of SQL is ideal for certain operations, procedural programming languages like M are designed for row-at-a-time processing. To integrate SQL with M it is necessary to use cursors to provide access to a particular row.† Cursors are positioned references to a result set of a SELECT statement. Cursors allow the programmer to step through a result set one row at a time.

Cursor-Related Statements

Below is the ESQL statement sequence used to retrieve data for a multiple-row result set. You may refer to this figure as we highlight syntactical elements in the following section.

Cursor-Related Statements

DECLARE cursor_name[INSENSITIVE] [SCROLL] CURSOR FOR[SET ROWSET integer]

SELECT statement[ORDER BY order_item_commalist] [{FOR READ ONLY} or {FOR UPDATE}]

OPEN cursor_name

FETCH [{NEXT or PRIOR or FIRST or LAST or ABSOLUTE integer_value or RELATIVE integer_value}[FROM]]

cursor_name INTO variable [,variable]...

CLOSE cursor_name

(27)

Defining a Cursor

Cursors are defined by the DECLARE CURSOR statement. For example:

DECLARE C1 CURSOR FOR SELECT NAME, SALARY FROM EMPLOYEES

Here we define a cursor named C1. You use C1 to step through the result rows of the query SELECT NAME, SALARY FROM EMPLOYEES — one row at a time. After the DECLARE statement, the cursor_name is defined, and the cursor state is inactive or CLOSED.

Building the Result Set

The result set is theoretically built when the OPEN cursor statement is executed. However, in practice the result is often built as needed, providing blocks of the result set at a time, even as the query continues to process. The net effect is a faster response time and greater concurrence. If the SELECT statement contains references to M variables, the values of these variables are bound at the time when the OPEN statement is executed. A typical OPEN statement appear as:

OPEN C1

Retrieving Data

After the OPEN statement is executed, the cursor state is activated or OPEN, and it is positioned before the first row in the result set. The FETCH statement is the mechanism by which the cursor is advanced, one row at a time, in the following manner:

FETCH C1 INTO :X1, :X2

The FETCH statement moves the column values from the result row to the INTO clause variables X1 and X2. Building on our example, the first row NAME value moves to variable X1, and the first row SALARY value moves to X2. Note that variables referenced in an ESQL statement are known as host variables and are preceded by a colon to distinguish them for the precompiler.

Checking for Errors

Each successive FETCH statement advances the cursor by one row, retrieving data into the variables. Two obvious potential glitches could derail this process: first, the cursor may be unable to successfully retrieve the data, and second, at some point we can assume the cursor reaches the last row of the result set. As good programming practice would suggest, you should implement a mechanism to monitor cursor activities. Two variables designed especially for the error-checking process are SQLCODE and SQLSTATE. A value of 0 for SQLCODE indicates successful execution, negative values indicate error conditions, and a value of 100 indicates no more rows in the result set. Refer to the section in this chapter titled SQLCODE & SQLSTATE for more information.

Closing the Cursor

The cursor and the result set may be explicitly deleted by the close statement, or implicitly deleted by a COMMIT or ROLLBACK statement when the transaction is in MANUAL COMMIT mode. The following statement explicitly deletes

C1 and its query result set:

CLOSE C1

(28)

(29)

The table below reflects the parameters, values, and meanings related to cursor states, access modes, and cursor types.

Cursor Parameters

Cursor State CLOSED The cursor_name is defined. A result set does not exist.

OPEN A result set exists for the cursor_name.

Access Mode FOR READ ONLY The cursor CANNOT BE USED IN CONJUNCTION WITH The WHERE CURRENT OF cursor_name clause for positioned DELETE and UPDATE statements.

FOR UPDATE If the cursor’s SELECT statement is updatable, the cursor may be used for positioned DELETE and UPDATE statements.

Fetch Type NEXT The cursor is not scrollable. The only allowable fetch type is NEXT.

SCROLL [ROWSET] The cursor is scrollable, allowing all fetch types (FIRST, LAST, NEXT, PRIOR,

RELATIVE and ABSOLUTE). The ROWSET value may be used to tune response time. INSENSITIVE [SCROLL] The cursor has an implicit access mode of FOR

(30)

SQLCODE & SQLSTATE Variables

Each executed SQL statement returns basic status information in the variables SQLCODE and SQLSTATE. Although these two variables seem to return redundant information, we believe the best approach is to use both. SQLCODE should always be implemented for basic status checking. (The exception is the DECLARE CURSOR statement which does not set values for the SQLCODE and SQLSTATE variables.) In the event of an error, you may then check SQLSTATE for specific conditions that could require special handling.

SQLCODE returns one of three values {-1, 0, 100}; SQLSTATE may return a value called an exception from a set of more than 100 possibilities. Each SQLSTATE exception is five characters long, composed of a two-character class code and three-character subclass code. The subclass code is often 000.

While each statement returns only one value for SQLCODE and SQLSTATE, it is possible that additional SQLSTATE exceptions may be recorded. Two statements may be used to provide information on the status of statement execution, shown below. The first GET DIAGNOSTICS statement allows the programmer to determine the number of exceptions recorded; the second statement retrieves individual values on each exception.

Diagnostic Statements

GET DIAGNOSTICS

variable={NUMBER or COMMAND_FUNCTION or ROW_COUNT} [,variable={NUMBER or COMMAND_FUNCTION or ROW_COUNT}]... GET DIAGNOSTICS EXCEPTION integer

[,variable={RETURNED_SQLSTATE or MESSAGE_TEXT}]... variable={RETURNED_SQLSTATE or MESSAGE_TEXT}

Your application should check the SQLCODE or SQLSTATE variables after each SQL statement to determine if the statement was successful.

(31)

The table below (which continues on the following page) lists the most common SQLCODE and SQLSTATE values for various SQL statements. (All possible SQLSTATE values are contained in the DATA_DICTIONARY table

SQL_API_SQLSTATE.)

Statement Type

# Rows

Processed SQLCODE SQLSTATE Meaning

SELECT

INSERT...SELECT FETCH

0 100 02000 Data not found.

n>0 0 00000 Success.

SELECT...INTO 0 100 02000 Data not found.

1 0 00000 Success.

n>1 -1 21000 Cardinality

violation.

Attempting to use more than one row. positioned DELETE or UPDATE 0 -1 24000 Invalid cursor state. 1 0 00000 Success.

DISCONNECT 25000 Invalid transaction

state.

DECLARE CURSOR not

applicable

not applicable not applicable not applicable all other statements not

applicable

(32)

Statement Type

# Rows

Processed SQLCODE SQLSTATE Meaning

any error on any statement not applicable

-1 -variety- See the

SQL_API_SQLSTA TE table for the specific SQLSTATE value. 40001 Serialization failure. Attempting to access a row in use by other transactions.

(33)

Compiling the Source

Several utility routines are available to you for compiling the source. They are: SQL0S, EPI^SQL, and EP^SQL. Let’s take a closer look at each of these interfaces.

SQL0S

Prior to execution, the ESQL source may be compiled into one or more M routines using the SQL0S utility routine.

Note: If you intend to modify rows in a table using the INSERT, UPDATE, or DELETE verbs, you must

first write or compile a table filer for the table that you want to modify. Refer to the KB_SQL Data

Dictionary Guide for more information on table filers.

The following input variables are required to successfully compile the source.

Input Variables

Variable Meaning

SQLSRC Source name (routine name, global prefix, or file) SQLRTN Object code routine name

SQLTYPE Source type (routine, global, or file) SQLUSER User password (not encrypted)

On the next page are examples of the statement structure and sequence for compiling three different types of source files: routine, global, and file.

(34)

To compile the routine-type source DESQL into the M routines XESQL*, the following sequence may be executed:

Compiling a Routine

S SQLSRC="DESQL",SQLRTN="XESQL",SQLUSER="SHARK",SQLTYPE="ROUTINE" D ^SQL0S

I SQLCODE<0 F I=1:1:SQLDIAG W !,SQLDIAG(I)

To compile the global-type source ^EP("XXX",sequence)=text into the M routines XXX*, execute the following sequence:

Compiling a Global

S SQLSRC="^EP(""XXX"")",SQLRTN="XXX",SQLUSER="SHARK",SQLTYPE="GLOBAL D ^SQL0S

I SQLCODE<0 F I=1:1:SQLDIAG W !,SQLDIAG(I)

And finally, to compile the file-type source \TEMP\RTN.EP into the M routines Z*, you would execute the following sequence:

Compiling a File

S SQLSRC="\TEMP\RTN.EP",SQLRTN="Z",SQLUSER="SHARK",SQLTYPE="FILE" D ^SQL0S

(35)

Obtaining Compile Status

The compiler returns status information in the variable SQLDIAG which takes the following general format.

SQLDIAG=number_of_messages SQLDIAG(number)=message_text

The table below shows the possible compiler output variables with their values and meanings.

Output Variables

Variable Value Meaning

SQLCODE 0 The compile was successful. -1 The compile encountered an error. SQLDIAG integer The number of compile messages. SQLDIAG(integer) text A compile message.

(36)

EPI^SQL

This utility routine provides a higher level interface than the SQL0S routine described on the previous pages. Using this approach to compile your source offers the advantages of resource tracking as well as compatibility with the EP ^SQL interactive option which follows. The EPI^SQL routine saves both the project and program definition. In addition, the routine tracks changes to resources for automatic recompilation.

To implement this routine, you must first define the variable array SQLX as described below.

SQLX("FUNCTION")=operation(COMPILE, DELETE, or SAVE) SQLX("PROJECT")=project name

SQLX("PROJECT_DESCRIPTION")=project description SQLX("PROJECT_ROUTINE")=base routine name

SQLX("PROGRAM")=program name

SQLX("PROGRAM_DESCRIPTION")=program description SQLX("TYPE")=source type (FILE, GLOBAL, or ROUTINE)

SQLX("SOURCE")=source location (the format depends on the TYPE) SQLX("OBJECT")=M routine name for compiled ESQL

The FUNCTION, PROJECT, and PROGRAM entries are always required. Remaining entries may be defined for COMPILE and SAVE operations.

Compile status may be obtained as described previously in “Obtaining Compile Status”; SQLCODE should be checked after returning from EPI^SQL. The SQLDIAG variable may be defined after a COMPILE operation. After the SQLX variable array is defined, the entry point to the routine is:

(37)

EP^SQL

This menu-driven routine provides facilities to compile a new source and recompile and manage existing embedded programs, (EPs). The high-level cataloging capability, illustrated below, helps you establish an organizational framework for your embedded programs.

EP^SQL cataloging structure

EPs are grouped by projects. Each project has a name, description, and base routine. EPs, in turn, are identified by name, description, source, source location, and optional M routine (object code).

(38)

Starting EP^SQL

The entry point to this routine is:

D EP^SQL

The EMBEDDED PROGRAM OPTIONS window appears, with a choice of three different options. For purposes of discussion, we assume this is the initial use of EP^SQL. Therefore, as suggested by the cataloging structure illustration, you typically establish a project definition first through the second option, PROJECT EDIT. In subsequent sessions, you are more likely to make frequent use of the first option, EMBEDDED PROGRAM EDIT. For this reason, it is

highlighted as the default.

Project Edit Option

Select the PROJECT EDIT option from the Select Embedded Program Options window and provide the required information. Note that during this procedure you may make modifications and deletions. However, keep in mind the following points as you make any changes.

Add

Project definitions must be added before you enter associated EPs. Once a project definition has been added, you may link EPs to that project. You may make project-related additions and insertions at your discretion without implication.

(39)

Edit

Edits to project definitions do not impact associated programs.

Delete

Deleting a project causes associated EPs to be deleted from the organizational framework. Source and object files remain intact.

EMBEDDED PROGRAM EDIT Option

Once the project definition is created, EPs may be linked to a given project. In addition, you may make modifications and deletions to EPs according to the following guidelines.

Add

You may make additions to program definitions without implication.

Edit

You may edit program-related information freely. Although changes may be committed, they do not take effect until the program is actually compiled.

Delete

You may delete program-related information or delete programs themselves. This last action only removes a program from the organizational framework and does not delete source or object file.

Now, select the EMBEDDED PROGRAM EDIT option from the Select Embedded Program Options window. Supply the required values to the series of program-related windows to establish an EP definition.

Because we are following a sequence, the Search for Project window appears again as we have seen it when we defined the initial project group name. In this cycle, the project name is derived from the previous PROJECT EDIT option. In subsequent sessions, you will likely use this option as the starting point, in which case you provide an existing project name manually or you may use [list].

RECOMPILE PROGRAMS Option

The third and final option from the Select Embedded Program Options window is a RECOMPILE PROGRAMS option that brings up the Recompile Embedded Programs window. This option allows you to recompile one or many previously compiled EPs from one or more projects. You can select all EPs or specific EPs either manually or using wildcards. Taking the second approach allows you to narrow the scope to EPs that reference data dictionary objects that have changed since the original compile. Objects subject to change include tables, view, functions, and domains.

(40)

At any point in the EP^SQL menu-driven process, you may encounter various error messages if you attempt an illegal action or enter erroneous information. These messages are built into the process to provide general feedback to you regarding the operational status of each procedure you attempt to execute.

(41)

ESQL Examples

The following examples illustrate the use of ESQL within M code. These examples were created in a non-M text editor (Microsoft Windows Notepad™) and as a file source-type. Using M routines or globals for the source is equivalent. The relative placement and position of SQL statements depends on both parsing consideration at compile time, and program flow at run time. For example, during the compile time parse, DECLARE statements must precede any references by OPEN, FETCH, or CLOSE statements.

During runtime, the program flow must execute a CONNECT statement to establish a connection prior to executing any other SQL statement. The DECLARE statement may precede a CONNECT without consequence because it is non-executable. The DISCONNECT must be the last statement executed.

Example 1

This example demonstrates the basic structure of an ESQL source. To work with two query result sets, notice that we establish two unique cursors through two separate DECLARE statements. Remember that each transaction may declare one or more cursors that are unique to the transaction. For readability and support purposes, we strongly recommend that all DECLARE statements be at, or near the top of, the embedded source. Returning to the example, we then establish a connection with the CONNECT statement.

(42)

Two separate loops, tag L1 and tag L2, comprise the body of the file. Prior to starting each loop, an OPEN statement initializes each cursor. Each loop contains a FETCH statement to retrieve the next result row into the specified M variables. The SQLCODE variable returns one of the following values: -1=failure, 0=success, or 100=no data found. After each loop completes, a CLOSE statement executes to clear the result set and a DISCONNECT statement terminates the connection.

; example1.ep ;

&SQL(DECLARE C1 CURSOR FOR SELECT PROJ_NO, PROJECT && FROM PROJECTS)

&SQL(DECLARE C2 CURSOR FOR SELECT TASK_NO, TASK, STATUS && FROM TASKS WHERE PROJ_NO=:PNO)

;

&SQL(CONNECT TO DEFAULT USER DBA AUTHORIZATION SHARK) I SQLCODE<0 Q ; O1 &SQL(OPEN C1) I SQLCODE<0 G 99 L1 &SQL(FETCH C1 INTO :X1, :X2) I SQLCODE=0 W !,X1,?20,X2 G L1 &SQL(CLOSE C1) ;

R !,"Select project #: ",PNO I 'PNO G 99 ; &SQL(OPEN C2) L2 &SQL(FETCH C2 INTO :X1, :X2, :X3) I SQLCODE=0 W !,X1,?20,X2,?30,X3 G L2 &SQL(CLOSE C2) W !! G O1 ; 99 &SQL(DISCONNECT DEFAULT) Q

(43)

Note: Example 1 uses the standard ampersand in the first tab piece as a line continuation indicator.

However, if the source type is a routine, using this syntax may cause an error, effectively preventing

saving the source under certain M implementations. If you encounter this problem, use a different

source type or the nonstandard double ampersand in the first command position continuation indicator.

Example 2

This example builds on the previous example, adding INSERT, UPDATE, and DELETE statements to change the database as well as COMMIT and ROLLBACK statements. In manual commit mode, this source includes explicit COMMIT statements to commit all previously executed statement or ROLLBACK statements to rollback any pending changes. If a statement fails to execute successfully, any database changes performed by the statement prior to the failure will be rolled back and the SQLCODE variable will be set to -1.

Subroutines perform the insert, update, and delete functions. While these statements are positioned after the

DISCONNECT statement, the run time program flow executes the subroutines prior to the DISCONNECT statement. The insert and delete sections are relatively straightforward. The insert performs an INSERT...VALUES to add a new row. The delete subroutine utilizes a searched DELETE using the row’s primary key in the WHERE clause to delete the specific row. The update section performs a singleton SELECT...INTO† to load the old values and a searched UPDATE to save the new values.

†_{The singleton SELECT...INTO is the key characteristic of the less-common single-row query. Notice the structural difference from the}

(44)

40 KB_SQL Programmer’s Reference Guide ; example2.ep

;

&SQL(CONNECT TO DEFAULT USER SHARK) I SQLCODE<0 Q

;

&SQL(declare C1 cursor for select EMP_SSN, NAME && from SQL_TEST.EMPLOYEES)

; 0 &SQL(open C1) 1 &SQL(fetch C1 into :X1, :X2) I SQLCODE=0 W !,X1,?20,X2 G 1 &SQL(CLOSE C1) ;

2 R !,"Option (I, U#, D#): ",OPT I OPT="I" G I I OPT?1"U"1.E G U I OPT?1"D"1.E G D I OPT'="" W "???" G 2 R !,"Commit? ",A I $F("Yy",A)>1 G CT &SQL(ROLLBACK WORK) G DC CT &SQL(COMMIT WORK) DC &SQL(DISCONNECT) Q ; I R !,"SSN: ",SSN R !,"Name: ",NAME R !,"Salary: ",SAL R !,"Manager: ",MGR

&SQL(INSERT INTO EMPLOYEES

&& (EMP_SSN, NAME, SALARY, MANAGER) && VALUES (:SSN, :NAME, :SAL, :MGR)) I SQLCODE<0 W !,SQLERR

G 0

(45)

U S ID=$E(OPT,2,999)

&SQL(SELECT NAME, SALARY, MANAGER && INTO :NAME, :SAL, :MGR

&& FROM EMPLOYEES

&& WHERE EMP_SSN = :ID) I SQLCODE'=0 G 1

W !,"Name: ",NAME R "/",A I A'="" S NAME=A W !,"Salary: ",SAL R "/",A I A'="" S SAL=A W !,"Manager: ",MGR R "/",A I A'="" S MGR=A &SQL(UPDATE EMPLOYEES SET NAME=:NAME,

&& SALARY=:SAL, MANAGER=:MGR && WHERE EMP_SSN = :ID) I SQLCODE<0 W !,SQLERR

G 0

;

D S ID=$E(OPT,2,999)

&SQL(DELETE EMPLOYEES WHERE EMP_SSN=:ID) I SQLCODE<0 W !,SQLERR

G 0

Note: The nonstandard double ampersand is used in the first command position as a line continuation

indicator.

Example 3

This example utilizes two connections to demonstrate several features. First, notice that the DECLARE statement is not linked to a connection. Also, the source establishes two connections, A and B, that each open a distinct copy of one cursor, C1. The SET CONNECTION statement switches context between the two concurrent connections. Two points to remember are that cursor names must be unique with a connection at run time and cursor names must be unique within the source at compile time. The DISCONNECT ALL statement disconnects both connections.

(46)

42 KB_SQL Programmer’s Reference Guide ; example3.ep

;

&SQL(DECLARE C1 CURSOR FOR SELECT PROJECT FROM PROJECTS) ;

&SQL(CONNECT TO DEFAULT AS A USER SHARK) &SQL(OPEN C1)

;

&SQL(CONNECT to DEFAULT AS B USER SHARK) &SQL(OPEN C1) ; &SQL(SET CONNECTION A) &SQL(FETCH C1 INTO :X1) W !,X1 &SQL(SET CONNECTION B) &SQL(FETCH C1 INTO :X1) W !,X1 &SQL(SET CONNECTION A) &SQL(FETCH C1 INTO :X1) W !,X1 &SQL(SET CONNECTION B) &SQL(FETCH C1 INTO :X1) W !,X1 &SQL(DISCONNECT ALL) Q

It is important to remember that using multiple connections has concurrency implications. In this example, both connections perform only READS, so the FETCH statements do not receive any concurrency violations. However, if one of the connections performs an update on a table row, the other connections would be unable to access the updated row.

(47)

Example 4

Here we demonstrate a searched DELETE. If a failure occurs during a searched DELETE, no employee rows are deleted, even if the failure occurs after some of the result rows have been processed.

; example4.ep ;

&SQL(connect to DEFAULT user SHARK) &SQL(delete from employees)

&SQL(commit work) &SQL(disconnect)

Q

Since this example updates the database, it is necessary to execute either a COMMIT or ROLLBACK statement.

Example 5

Unlike Example 4, this example illustrates a positioned DELETE, deleting some rows and displaying any rows it is unable to delete.

; example5.ep ;

&SQL(connect to DEFAULT user SHARK) &SQL(declare C1 cursor for select name & from employees where salary > 15 for update)

;

&SQL(open C1)

1 &SQL(fetch C1 into :X1) I SQLCODE=100 G 2

I SQLCODE<0 W !,"Unable to delete ",X1," due to ",SQLSTATE E &SQL(delete from employees where current of C1)

G 1

2 &SQL(commit) &SQL(disconnect)

(48)

Example 6

This example demonstrates a searched UPDATE.

; example6.ep ;

&SQL(connect to DEFAULT user SHARK)

&SQL(update employees set salary = salary * 1.1 & where salary < 18)

&SQL(commit) &SQL(disconnect) Q

Example 7

Following is a positioned UPDATE example.

; example7.ep ;

&SQL(connect to DEFAULT user SHARK)

&SQL(declare C1 cursor for select name, salary & from employees where salary > 15 for update) &SQL(open C1)

1 &SQL(fetch C1 into :X1, :X2) I SQLCODE'=0 G 2

&SQL(update employees set salary = salary * 1.1 & where current of C1)

G 1

2 &SQL(close C1) &SQL(commit) &SQL(disconnect) Q

(49)

Example 8

Here you see a demonstration of most of the FETCH options for a scrollable cursor. To use these options, the DECLARE statement must specify the SCROLL option.

; example8.ep ;

&SQL(CONNECT TO DEFAULT USER SHARK) &SQL(DECLARE C1 SCROLL CURSOR FOR && SELECT PROJ_NO, PROJECT

&& FROM PROJECTS) ;

O1 &SQL(OPEN C1) W !,"next"

L1 &SQL(FETCH NEXT C1 INTO :X1, :X2) I SQLCODE=0 W !,X1,?20,X2 G L1 R !,Z W !,"prior"

L2 &SQL(FETCH PRIOR C1 INTO :X1, :X2) I SQLCODE=0 W !,X1,?20,X2 G L2 R !,Z W !,"first"

&SQL(FETCH FIRST C1 INTO :X1, :X2) I SQLCODE=0 W !,X1,?20,X2

R !,Z W !,"relative 1"

L4 &SQL(FETCH RELATIVE 1 FROM C1 INTO :X1, :X2) I SQLCODE=0 W !,X1,?20,X2 G L4

R !,Z W !,"last"

&SQL(FETCH LAST C1 INTO :X1, :X2) I SQLCODE=0 W !,X1,?20,X2

R !,Z W !,"relative -1"

L6 &SQL(FETCH RELATIVE -1 FROM C1 INTO :X1, :X2) I SQLCODE=0 W !,X1,?20,X2 G L6

R !,Z W !,"absolute +1"

S I=1

L7 &SQL(FETCH ABSOLUTE :I C1 INTO :X1, :X2) I SQLCODE=0 W !,X1,?20,X2 S I=I+1 G L7 R !,Z W !,"absolute -1"

(50)

46 KB_SQL Programmer’s Reference Guide L8 &SQL(FETCH ABSOLUTE :I C1 INTO :X1, :X2)

I SQLCODE=0 W !,X1,?20,X2 S I=I-1 G L8 ;

&SQL(CLOSE C1)

&SQL(DISCONNECT DEFAULT) Q

(51)

Chapter 3 Using the KB_SQL API

KB Systems’ application programming interface, KB_SQL API, supports the development of applications, custom servers, gateways, and other modules that interface with database systems defined in the KB_SQL data dictionary.

From a programming point of view, an API is an SQL-software interface comprised of functions that provide intercommunication between a data source and an application. This approach is highly suited to ad hoc, interactive processing.

The KB_SQL API has two versions: one for the client, which we refer to as the KB_SQL Client API, and one for the server, the KB_SQL Server API. The function names are identical regardless of whether they are used by the Client API or the Server API. The difference between the two is the relationship that each one has with the KB_SQL Engine. The Client API connects to the Server API over the network. The Server API speaks directly to the KB_SQL Engine. In this chapter, we examine several general implementation scenarios. They show you how the Client and Server APIs come to play depending on how you are accessing the KB_SQL Engine. Then we focus on the information you need to develop M applications using the KB_SQL API.

(52)

Highlights of this chapter include:

a framework for implementation, including typical usage scenarios;

the KB_SQL API Function Summary Table;

a reference section containing an alphabetic listing of each KB_SQL API function complete with descriptions and illustrations.

Note: When installing KB_SQL, you can choose to load API sample routines. Each routine demonstrates the usage of an

API function.

Because the KB_SQL API is modeled on Microsoft’s Open Database Connectivity application programming interface (ODBC API), we strongly recommend ODBC 2.0 Programmer’s Reference and SDK Guide from Microsoft. This guide provides excellent detailed coverage of the ODBC interface as an emerging industry standard.

Attention MSE programmers: You should also refer to Appendix B which discusses the MSE syntax of the API

(53)

A Framework for Implementation

In order to use the KB_SQL API, you need to be aware of some fundamental network and SQL conformance standards that form the framework for implementing our product.

The authoritative standard adhered to in development of the API is Microsoft’s ODBC interface. The strategy is simple: Using standard SQL, applications access data in database management systems no matter what the data looks like or where it resides. The payoff is maximum interoperability.

Network Standards

The KB_SQL API supports a multiple-tier driver as defined by ODBC. A driver is a dynamic-link library (DLL) that implements API function calls and interacts with a data source. The data source typically resides on a server in another system.

There are many multiple-tier configurations (including a gateway process through which SQL requests may be passed) and any number of these may appear in a single network. However, all are transparent from the application’s point of view. The strength of the ODBC is its interoperability. When you develop an ODBC-compliant application, you need not target a specific data source because you can add drivers to the application later for accessing specific data sources. To deploy the KB_SQL API in a network, you will need TCP/IP access as a requirement for both the client and server processes. For more information on tested configurations, see Appendix A in KB Systems’ KB_SQL ODBC Driver

(54)

Software Standards

The KB_SQL API is based on the framework provided by the SQL Access Group (SAG) and the Open Database Connectivity (ODBC) specification by Microsoft. Specifically, the API conforms to Core and Level 1 ODBC specifications for API performance and meets the Minimum and Core levels defined for SQL grammar.

A Conceptual View of the KB_SQL API

The KB_SQL API is extremely versatile and can accommodate a variety of situations; you might develop in C or M, and you may or may not require network access. Ultimately, you can design your application to access any data source using the API and appropriate drivers.

(55)

Accessing the KB_SQL Engine from M programs

KB_SQL

Server

API

Server

API

shell

(SQLOS)

M

MSE

KB_SQL

Engine

Relational Data Dictionary M Data M Reporting Tools

In this example, M programs call KB_SQL Server API functions to access the KB_SQL Engine. These functions perform the same operations as their ODBC versions. The distinction in the structure of each function is a result of the inherent differences between the C and M languages. The SQLOS program serves as an interface to the Server API. Note that this configuration is host-based as opposed to client/server. Providing application development for this configuration

significantly extends existing capabilities.

Guidelines

M programming experience

(56)

Accessing the KB_SQL Engine from C programs

Non-Windows environment KB_SQL Server API KB_SQL Client API C KB_SQL Engine Relational Data Dictionary M Data TCP/IP M Reporting Tools Network API Network API

In UNIX and other non-Windows environment, the programmer can access the KB_SQL Client API by including an object library with the application. By linking this library with your application, you can access the Server API. For example, the object library can be used to develop a gateway from a non-M database system. This method requires C programming expertise. Note that this method communicates with the Server API over TCP/IP. As a side note, the send-side of the Network API is written in C; the receive send-side is written in M.

Guidelines

C programming experience

(57)

Accessing the KB_SQL Engine from Windows

via Dynamic Link Library (DLL)

KB_SQL Server API KB_SQL Client API C KB_SQL Engine Relational Data Dictionary M Data Visual Basic M Reporting Tools TCP/IP Network API Network API

With this approach, the KB_SQL Client API is accessed as a dynamic link library (DLL) from Windows via C or Visual Basic. A Windows programming paradigm, the DLL allows one copy of a set of functions to be accessed by multiple applications. In Visual Basic, the Server API is accessed directly through the KB_SQL ODBC Driver. The ODBC Driver Manager is not involved. Again, TCP/IP provides your connection to the Server API.

Guidelines

(58)

Accessing the KB_SQL Engine or other data sources from Windows

via ODBC Manager

KB_SQL Server API KB_SQL Client API C KB_SQL Engine Relational Data Dictionary M Data Visual Basic ODBC Manager M Data ODBC

Driver M Reporting Tools

TCP/IP Network

API

Network API

Unlike the previous approach that provides access only to the KB_SQL Engine, this method furnishes access to virtually any data source for which you have ODBC drivers. (As you may have guessed, this scenario represents the ultimate goal of ODBC.) The KB_SQL Engine may also still be accessed via the ODBC Manager from C or Visual Basic.

Guidelines

(59)

Features of the KB_SQL API

Prepared and Direct Execution

The KB_SQL API supports two methods of statement execution: the direct method (using the SQLExecDirect function) and the prepare/execute method (using the SQLPrepare and SQLExecute functions). When an SQL statement is prepared it is sent to the server, parsed, and optimized. This is the ideal method for statements that are executed repeatedly, each time with different parameter values.

An application should prepare a statement for execution if any of the following are true:

The application will repeatedly execute the statement.

The application needs information about the result set before executing the statement.

An application can execute a statement directly if both are true:

The application will execute the statement only once.

(60)

Parameter Markers

Parameter markers in a prepared SQL statement are placeholders for parameter values that are supplied by an application. Parameter markers are delimited by question marks. Using parameter markers in an SQL statement lets you execute the statement repeatedly with different values. This is a more efficient alternative to coding literal values in an application, and changing the application each time the parameters change.

To use parameter markers, the application must first call the SQLBindParameter function to bind a storage area to a parameter marker. Then the application must place the parameter’s value in the storage location. Examples of using parameter markers are found in the Stored Procedures section of this chapter.

ODBC Extensions

Catalog Functions

Catalog functions return information about a data source’s data dictionary, such as the names of the tables and the columns and the privileges associated with them. The information is returned as a result set. The application retrieves the result set by calling SQLBindCol and SQLFetch. Descriptions for the following catalog functions can be found in the KB_SQL API Function Summary section of this chapter.

(61)

SQLTables SQLForeignKeys

SQL TablePrivileges SQLSpecialColumns

SQLColumns SQLStatistics SQLColumnPrivileges SQLProcedures

SQLPrimaryKeys SQLProcedureColumns

Parameter Data Functions

An application can send parameters at statement execution time by calling the following functions:

SQLBindParameter specifies the SQL data type of the parameters and reserves storage location for each parameter. SQLPutData sends data value for the parameter to the driver.

Using data-at-execution parameters is useful for applications handling long data.

SQL Extensions

KB_SQL API supports several ODBC SQL extensions:

the representation of date and time literals

the syntax for scalar functions

LIKE predicate escape characters

(62)

These extensions provide essential functionality that is shared by most DBMSs. However, because the syntax required by the DBMSs varies, ODBC uses a syntactical notation called the escape clause. The escape clause consists of a pair of curly braces {} which surround the standard SQL syntax form and a one- or two-character token which specifies the type of clause.

Date, Time, and Timestamp Data

KB_SQL API supports the DATE, TIME, and TIMESTAMP (a combined form of DATE and TIME) data types. The shorthand escape clause syntax for these data types is:

{d 'value'} {t 'value'} {ts 'value'}

Scalar Functions

Scalar (or row) functions operate on a single value. They include functions that compute absolute value or length, format a date or time, or return a substring. The on-line query SQL_FUNCTION_LIST provides a complete list of the KB_SQL API scalar functions and their syntax.

(63)

Wildcards (Search Pattern Escape Characters)

ODBC supports wildcards using the underscore character (_) for single-character replacement and the percent sign (%) for multiple-character replacement.

A word of caution is in order with respect to the use of wildcards, particularly in relation to the various catalog functions where the SQL_IDENTIFIER can include the underscore character. When you want to use a metacharacter as a literal and not as a wildcard, you must precede the metacharacter with the search pattern escape character (the default is the

backward slash).

For example, to obtain all columns for the LAB_PATIENT table where the underscore is a literal in the SQL_IDENTIFIER, you send the table name parameter as:

LAB\_PATIENT

Using the escape character prevents the engine from interpreting the underscore as a wildcard, thus avoiding retrieving columns for tables that could be named LABaTABLE, LABbTABLE, and so forth.

(64)

LIKE Predicate Escape Characters

The support of the LIKE predicate escape clause allows for searches to include metacharacters. In a LIKE predicate, the underscore character (_) is used for single-character replacement and the percent sign (%) is used for multiple-character replacement. The percent and underscore characters can be used as literals in a LIKE predicate by preceding them with an escape character.

The escape clause for ODBC queries looks like:

{escape ‘escape-character’} Example {escape ‘\’}

Example

Items aab aac aad aazz aa_123 aa_245 abc ade

SQL without escape character select code from item_master

where code like ‘aa_%’

Returns

aab aac

aad aazz

aa_123 aa_245

(65)

SQL with escape character

select code from item_master

where code like ‘aa\_%’ Returns

aa_123 aa_245

Notes

The default LIKE escape character is '\'.

The escape character can be specified using the escape clause syntax: {escape '\'}.

The SQL Editor supports the feature, but does not use the {} syntax.

ODBC Example

select code from item_master

where code like ‘aa\_%’ {escape ‘\’}

SQL Editor Example select code

from item_master

(66)

Stored Procedures

The KB_SQL Server API supports the use of stored procedures as a collection of precompiled SQL statements residing on the server. An application can call a procedure in place of an SQL statement. Stored procedures help to increase

performance and consistency when performing repetitive tasks because they are compiled the first time they are executed. When executed again, subsequent run time is much shorter than for the equivalent set of stand-alone statements.

Stored procedures simplify application development because you can repeatedly change them without modifying and compiling the application.

Stored procedures can be created for permanent or temporary use.

Temporary Stored Procedures

KB_SQL creates a stored procedure for every set of SQL statements that are submitted from client applications. These procedures are available for use by all server queries for as long as the procedure is in the buffer of stored queries on the server.

The query text is stored under a name like @SERVER_nnn, where nnn represents the number of the query relative to the number of stored queries allowed.

IMPORTANT: DBA users can copy the definition of a server query into a new query, but server queries should not be modified in any way.

(67)

Permanent Stored Procedures

KB_SQL can make any stored procedure permanent so that it will always be available regardless of the size of the stored query buffer. This type of procedure is desirable for use within custom applications. It allows the application to be developed independently of the server-side SQL statements. Only the calling parameters and result set must stay constant. Otherwise, the stored procedure can be modified as needed without affecting the client application.

Steps to create a permanent stored procedure

1. Create a query that returns a result set in the ODBC format.

SET CLIENT=ODBC

This parameter is automatically inserted into SQL statement buffers sent from client applications. Setting STORED_PROCEDURE implicitly sets the CLIENT type.

2. Indicate that the procedure is permanent.

SET STORED_PROCEDURE=YES

This makes the query take on the characteristics of a stored procedure until you remove the setting. This query will appear in the result set for the SQLProcedures function.

3. Give names to input parameters (optional).

SET PARAMETERS='name,city,state'

You can choose to specify names for parameter markers in any query.

This acts as documentation, and is accessible in the result of the SQLProcedureColumns function. Note that setting PARAMETERS implicitly sets the CLIENT type and STORED_PROCEDURE indicators.

KB_SQL Programmer s Reference Guide

KB_SQL

Programmer’s

Reference

Table of Contents

Chapter 1

An Overview

SQL in Perspective

Easy to Learn

Piggybacking SQL

Developing with KB_ESQL/KB_SQL API

KB Systems’ Client/Server Architecture

Common Elements

Distinctive Elements

Summary

Chapter 2

Using the KB_ESQL

Choosing a Source Type

Structural Characteristics

Note: The use of double ampersands is our extension; it is not part of the M-SQL binding.

Language Elements

Creating the Source

Connections

Transactions

Transaction-Related Statements

Commit/Rollback

Setting Transaction Attributes

Transaction Commit Modes

Isolation Level

Note: ANSI SQL2 also supports REPEATABLE READ and SERIALIZABLE levels, but these are not

implemented in Version 3.

Access Mode

Cursors

Cursor-Related Statements

Defining a Cursor

Building the Result Set

Retrieving Data

Checking for Errors

Closing the Cursor

SQLCODE & SQLSTATE Variables

Compiling the Source

SQL0S

Note: If you intend to modify rows in a table using the INSERT, UPDATE, or DELETE verbs, you must

first write or compile a table filer for the table that you want to modify. Refer to the KB_SQL Data

Dictionary Guide for more information on table filers.

Compiling a Routine

Obtaining Compile Status

EPI^SQL

EP^SQL

Starting EP^SQL

Project Edit Option

EMBEDDED PROGRAM EDIT Option

RECOMPILE PROGRAMS Option

ESQL Examples

Example 1

Note: Example 1 uses the standard ampersand in the first tab piece as a line continuation indicator.

However, if the source type is a routine, using this syntax may cause an error, effectively preventing

saving the source under certain M implementations. If you encounter this problem, use a different

source type or the nonstandard double ampersand in the first command position continuation indicator.

Example 2

Note: The nonstandard double ampersand is used in the first command position as a line continuation

indicator.

Example 3

Example 4

Example 5

Example 6

Example 7

Example 8

Chapter 3

Using the KB_SQL API

A Framework for Implementation

Network Standards

Software Standards

A Conceptual View of the KB_SQL API

Accessing the KB_SQL Engine from M programs

KB_SQL

Server

API

Server

API