Table Of Contents. KB_SQL ODBC Driver What's New? Understanding ODBC Driver Installation and Setup... 9

(1)

(2)

(3)

KB_SQL ODBC Driver

KB_SQL ODBC Driver

The KB_SQL ODBC Driver allows you to connect your M database to a world of applications. This document describes the ODBC solution in the context of the KB_SQL Server product provided by Knowledge Based Systems, Inc.

For more information about ODBC, please see the Microsoft documentation available at www.microsoft.com/data

For questions about KB_SQL, please see the documentation available at www.knowledgebasedsys.com

To report a problem, or to get technical support, licensed users should have their database administrator contact KBS technical support at [email protected].

(6)

(7)

What's New?

Changes in this Release

The KB_SQL ODBC Driver for KB_SQL Version 4 is a completely new and updated driver designed to work with the latest applications from Microsoft, including Microsoft Office97 and Office2000.

ODBC SDK 3.51

Driver and server compatibility File data source names

Silent configuration options Connection pooling

Safe threads Multi-row fetch

Custom network support

Character set translation ODBC SDK 3.51

The version 4.0 KB_SQL ODBC driver has been updated to be compliant with

Microsoft’s latest SDK for 32-bit ODBC Drivers. Numerous performance and flexibility improvements have been made to the driver.

Connection pooling

Connection pooling enables an application to use a connection from a pool of established connections. If a connection has already been created and placed in a pool, an application can use that connection without performing the entire connection process. Re-using connections from the pool can increase performance since applications can save the overhead involved in making a connection. This is especially significant for middle-tier applications that connect over a network or in a situation where the application repeatedly connects and disconnects (e.g. Internet applications).

(8)

KBSODBCDriver

the feature and set the timeout value via the ODBC administrator. This timeout value determines the period of time which an in

Driver and server compatibility

We will continue to support older versions of the KB_SQL ODBC Driver that work in the ODBC version 2.x mode. This has been accomplished by physically separating the two versions of the Server API code,

functions.

Please note that the older version of the stored procedures and SQL catalogue tables ha been renamed. Version 2.x stored procedures and SQL catalogue tables all end with “A (for example, SQL_SP_COLUMNSA).

Please note that although older drivers can be used with the new server, u

active connection is removed from the pool.

including the stored procedures for catalog

ve ” sing the older drivers will prohibit the use of new server features (for example, the LONG data type). In

ddition, because KBS will not be providing bug fixes for older releases, we strongly urge upgrading to the latest driver.

er. tinue to work, they will not new features. File data source names

can be shared on and can simplify workstation configuration.

Silent configuration options

DBC

For customers that are using, or are interested in using multi-threaded applications such s Microsoft Transaction Server, Internet Information Server, and Java, version 4.0 fully supports safe threads. KB_SQL version 4.0 fully supports safe threads so that

hreaded

ata. a

Special Note About the 16-bit Driver

Please note that no new development has been performed on the 16-bit ODBC driv The 16-bit driver is currently in maintenance mode using Microsoft ODBC SDK 2.10. Although applications using the 16-bit ODBC driver will con

be able to take advantage of any

We now support the dynamic creation of file data source names from the ODBC Administrator and Microsoft Office applications. Previous versions of the product required that file data source names be created manually. File DSNs

network drives

The product now supports DSN-less connections. This feature provides the ability to connect to a remote data source without having to create a data source in the O Administrator, thus simplifying application distribution.

Safe threads

a

applications can use the same handle on more than one thread. In a multi-t

environment, it is possible for each thread to have independent data. However, it is also possible for multiple threads to share data. In this situation, the different threads must cooperate in accessing the d

Multi-row fetch

(9)

We now support SQLE

SQLFetchScroll has replaced SQLExtendedFetch. These calls allow an application to retrieve multiple records at one time thus reducing the

xtendedFetch and SQLFetchScroll. In ODBC 3.0,

number of calls between the application and the driver.

ustom network support

Version 4 separates the client, network, and setup API functions so that each can be

aracter set translation

Prior to this release, ANSI to OEM character translation was performed on the server side by specifying the translation rules in the site global. With version 4.0, character set

translation is performed on the driver side. To implement the character set translation, it will be necessary to specify the appropriate translation DLL for the data source name via the ODBC Administrator.

C

developed independently. Because the components are separated, customers have the option to develop custom network interface modules based on their networking or security conventions.

Contact KBS technical support for more information on how you could implement a custom network module. Ch

(10)

(11)

Understanding ODBC

What is ODBC?

ODBC (Open Database Connectivity) is a standard interface developed by Microsoft for accessing data from different kinds of SQL database management systems. See

www.microsoft.com/data for additional information. What does this mean to the M Community?

• This means that you can use many of the Windows applications already on your desktop to get to your M data.

• With the KB_SQL Server together with the KB_SQL ODBC Driver, you can now use the Microsoft Office programs including Excel, Word, and Access or any ODBC-compliant Windows application to access your M data.

• You have many more choices for data access and reporting of your M application data.

How does it work?

The KB_SQL ODBC Architecture consists of six main components:

• your Windows application programs

• the ODBC Driver Manager

• the KB_SQL ODBC Driver

• networking software (TCP/IP)

• the KB_SQL Server Interface

• the KB_SQL Engine and relational data dictionary

The KB_SQL ODBC Architecture

• Resident on your workstation, the client side, you need your Windows

applications, the ODBC Driver Manager, the KB_SQL ODBC Driver, and the networking software.

• Your Windows applications can be any ODBC-compliant Windows application. They can be commercial off-the-shelf products like Microsoft’s Excel and Visual

(12)

KBSODBCDriver

your M data is limited only by your KB_SQL license, which determines the number of concurrent users.

• Networking software is necessary to communicate between the Driver and Server. The KB_SQL ODBC Driver requires TCP/IP networking software.

• On your server, you need to install KB_SQL Server and you need to have your M globals mapped as SQL tables into a SQL data dictionary. If you have any

previous version of KB_SQL, this has already been done.

• The KB_SQL Server Interface encapsulates the Server API and Network Library M, and exposes the KB_SQL Engine to the client.

Benefits of KB_SQL ODBC

• You are free to select the application that is most appropriate for the task at hand. At any point in time, you can choose to use either the EZQ or SQL Editors that come packaged with KB_SQL, or you can select from many ODBC-compliant Windows applications.

• You can continue to access your M data from a terminal or choose a workstation that has access to a KB_SQL ODBC Driver.

• KB_SQL ODBC simplifies application development. You can write a single application that can be used to extract data from many different databases, permitting maximum interoperability and portability of the application.

• With KB_SQL ODBC, accessing M is like accessing any other RDBMS. Actually, the fact that you may be accessing different types of databases concurrently is transparent to you.

(13)

Driver Installation and Setup

Before you begin

Before installing the KB_SQL ODBC Driver, there are a couple of items to note.

• Is the Server installed and configured?

• Do you know the Server configuration parameters for the Hostname (or IP address), and listening port?

• Can you ping the Server using the Hostname (or IP address)?

• If running WindowsNT, do you have Administrator privileges?

Driver Installation

Step 1: Launch the KB_SQL ODBC Driver Installer.

Step 2: Accept the terms of the license agreement.

(14)

KBSODBCDriver

Create a

The installer continues, allowing you to create a Data Source as part of the installation. Step 4: Give the Data Source a name and description.

Default Data Source

Step 5: Specify the Network parameters.

(15)

Step 6: Specify a Code Page Translator

(16)

KBSODBCDriver

(17)

Server and Network Configuration

Server Info

ServerInfo

Prefix for stored quer

Server initial execute erver user initial execute

y routines Maximum number of

Allow connections

stored queries Trace API calls

Edit network configuration S

(18)

KBSODBCDriver

Enter the number of queries that you want held in the stored queries buffer on the server. The value you supply is a matter of storage space versus processor resources. Assigning a large number requires more storage capacity but less processing time. Conversely,

assigning a smaller number requires less space to store queries, but causes more processing by the SQL engine. Here’s why: As the Server API receives each SQL command, it checks the stored queries buffer for the command’s corresponding M routine. If it finds the M routine, it simply runs it. If it doesn’t, it has to use the SQL engine to translate the command into one or more M routines. The M routine and

corresponding SQL command are then stored and assigned a routine name using the base routine prefix. If you specify a large number, say 999, many commands can be processed before having to reuse a stored query for another command. When the maximum number has been reached, KB_SQL reuses the oldest stored query to store the next query’s M routine and corresponding SQL command. You may clear the stored queries buffer of all routine names by invoking the RL^SQL tag.

Allow connections

Answer YES to accept connections to the server. Answer NO if you want to refuse connections. Note that the server will not start if the system lock is set. (Refer to the

Regardless o ound—

erver to be started in background.

server. It is n instructions for starting

Server later in this chapter.

Trace API calls

er YE erver and

is _S

o

faults or host information. The TCP/IP A discussion of this process begins on the next page. UTILITIES\L

If the server i

OCK STATUS option.)

s running and you want to stop allowing connections, first invoke the SVR function. Then set this prompt to NO.

f the setting of this prompt, the server can always be run in foregr STOP^SQL0

useful for testing purposes when you don’t want the s

If the server is not running when you set this value to YES, you still have to start the ot automatically started. To start the server, see the

and stopping the KB_SQL

Answ clients. Th

API

S if you want to track information about the dialog between the s creates a log that you can view by using the SQL Editor to run the SQL_

Edit netw

ERVER_TRACE query, or by using the tag VIEW^SQL0SVR.

rk configuration

Answer YES if you want to edit TCP/IP de Defaults window will appear.

Server initial execute

(19)

Enter the M code that will instantiate any variables that are required by your application during the runtime execution of queries.

d/or establish the username (SQLUNAME) and variables referenced during the connection to the server.

formation you are required to provide consists of the TCP/IP ding on your M implementation type, you might be

n on

Server user initial execute

Enter the M code that will validate an password (SQLUAUTH)

Network Configuration

NetworkInfo

n general, the network in

host and port information. Depen required to provide alternate ports. TCP/IP Defaults

TCP/IP Host Definitio TCP/IP Port Definti

(20)

KBSODBCDriver

Timeout for read commands

Enter the nu

should timeout and chec

Maximum length of M string

Enter the maximum size of an M string value in this implementation (default =

Output buffer size Enter the output buffer siz Input buffer size Enter the in

TCP/IP Host Definition

mber of seconds after which a READ command k error status (default = No timeout)

511).

e in bytes (default = 2048). put buffer size in bytes (default = 2048).

Host name Enter the TCP/IP hostname for the server.

es

the server on which KB_SQL is running.

IP address Enter the internet protocol address for the server. If your system us

dynamic ip address allocation, enter the host name in this field.

Description Enter a brief description of this server. Local host? Enter YES if you are describing

Enter NO if you are documenting another server.

TCP/IP Port Definition

(21)

Port number

The port number is expected to be

Port server?

Answer YES if this is the published port for connec Answer NO if this is an alternate port to be used interna

TroubleShooting

Network Troubleshooting

Troubleshooting network connection However, we've found that most of t configured servers. It is easy to ch

in the range of 5000-65535. ting to this server.

lly.

s can be challenging due to complex configurations. he errors related to KB_SQL have been incorrectly eck the configuration of your server using our built-in testing tool, TEST^SQL0TCP.

(22)

KBSODBCDriver Starting with

Server and Network. The TEST^SQL0TCP utility will attempt conn Server back

For example:

>D TEST^SQL0TCP

KB_SQL Server Connect Test

KB_SQL v3.6, we provide a simple utility to test the configuration of your ections from your to itself, and keep trying connections until it fails.

Server Name or Address: 127.0.0.1// Por

UserName : DBA

0)

e rted.

This attempt to start the server is successful, as it does not return an error in the SQLERR ariable.

Now, try the connect test again.

... Connecting to 127.0.0.1 (6500) .1~6500 ... onnected to #3: |TCP|7~127.0.0.1~6500 ... onnecting to 127.0.0.1 (6500) 127.0.0.1~6500 ... 6500) Connected to #5: |TCP|9~127.0.0.1~6500 ...

Exceeded KB_SQL V4.0 license limit

t Number : 6500// PassWord :

Connecting to 127.0.0.1 (650

Error in connect: 0579: TCP/IP Timeout in Open

As the error message implies, the attempt to connect has timed out. The most likely caus is that the server is not sta

>D START^SQL0SVR >W $D(SQLERR) 0

v

>D TEST^SQL0TCP

KB_SQL Server Connect Test Server Name or Address: 127.0.0.1// Port Number : 6500// UserName : DBA PassWord : Connecting to 127.0.0.1 (6500) Connected to #1: |TCP|5~127.0.0.1~6500 Connected to #2: |TCP|6~127.0.0 Connecting to 127.0.0.1 (6500) C C Connected to #4: |TCP|8~ Connecting to 127.0.0.1 ( Connecting to 127.0.0.1 (6500) Connected to #6: |TCP|10~127.0.0.1~6500 ... Connecting to 127.0.0.1 (6500) Connected to #7: |TCP|11~127.0.0.1~6500 ... Connecting to 127.0.0.1 (6500) Connected to #8: |TCP|12~127.0.0.1~6500 ... Connecting to 127.0.0.1 (6500) Error in connect:

[S1000] (139): [Knowledge Based Systems, Inc.][Server API][V4.0]SQLConnect:

(23)

Disconnecting #8: |TCP|12~127.0.0.1~6500 ... Disconnecting #7: |TCP|11~127.0.0.1~6500 ... Disconnecting #6: |TCP|10~127.0.0.1~6500 ... |9~127.0.0.1~6500 ... Disconnecting #4: |TCP|8~127.0.0.1~6500 ... isconnecting #3: |TCP|7~127.0.0.1~6500 ... Disconnecting #2: |TCP|6~127.0.0.1~6500 ... is not started. We TART^SQL to your system startup logic.

ost common problem is for those M types that require alternate ports (ISM, not enough are configured. We always recommend configuring at

u have connections on your license. Configuring more doesn't

MS Cluster issues

rocess to “listen” for ns.

a single definition of the local host. ccess the database.

ode other than the one identified in the local host setting problem is to disable the auto startup feature using Site Edit. ay is to add code to the Server StartUp Execute to test whether the current node matches the local host definition. If it doesn't, then the Server should not be allowed to start. Simply return an error in SQLERR.

Starting the Server

There are several methods available for starting the KB_SQL Server. You may start it Disconnecting #5: |TCP

D

Disconnecting #1: |TCP|5~127.0.0.1~6500 ...

Notice that it gets 8 connections before it encounters an error. It shows that this attempt to connect is rejected because it would exceed the current limit of your license. This is the error that you want to get in this context. It ensures that everything is working. If you get other errors, it is helpful to pass on the information to our technical support.

Currently, the most common problem with connecting is that the Server recommend adding S

The second m

DSM, and others), that least as many ports as yo hurt either. V

On VMS clusters, it gets very tricky when starting an M p connectio

The M database has One or more nodes can a Starting the “listener” from a n can have confusing consequences. One way of preventing this

(24)

KBSODBCDriver

1. Output to Screen

Command:

>D ^SQL0SVR

Description: This command is for general configuration, analysis and debugging. Sample Output:

Waiting for client connection ...

KBSGetInfoShort("1010","23") = (0,"2") KBSGetInfoShort("1010","24") = (0,"2") SQLAllocStmt("1010") = (0,"2007")

SQLPrepare("2007","select * from employees") = (0,"1") SQLNumResultCols("2007") = (0,"4") SQLDescribeCol("2007","1") = (0,"EMP_SSN","12","11","0","0") SQLDescribeCol("2007","2") = (0,"NAME","12","15","0","0") SQLDescribeCol("2007","3") = (0,"SALARY","2","5","2","1") SQLDescribeCol("2007","4") = (0,"MANAGER","12","11","0","1") SQLExecute("2007") = (0) KBSRowsetFrame("2007","1","20480") = (100,"48","102-44-3545","JOHNSON","9","416-82-6316",... SQLFreeStmt("2007","0") = (0) SQLFreeStmt("2007","0") = (0) SQLDisconnect("1010") = (0) 2. Output to File Command: >D FILE^SQL0SVR("[FILE NAME]") Example: >D FILE^SQL0SVR("c:\temp\svrtrace.log")

Description: This command is used to capture the output from a client/KB_SQL Server

interaction for analysis, debugging, or submittal to KBS Technical Support.

3. Output to Printer

Command:

>OPEN %DEV USE %DEV DO ^SQL0SVR CLOSE %DEV

Example:

>O 3 U 3 D ^SQL0SVR C 3

Description: This command is used to capture output from a client/KB_SQL Server

interaction for analysis, debugging, or submittal to KBSs Technical Support.

4. Without Error Trapping

Command:

DO NOTRAP^SQL0SVR

Description: This option turns off KB_SQL error trapping. When a hard M error is

(25)

encountered, the server will stop and the error will be displayed in th your M implementation

e native format for

Note: The foreground server options are intended only for analysis or debugging

only a single connection, it is not appropriate for a multi-user

und

you have answered Yes to Allow Connections? in Site Edit/Server Info and your

ystem or other times without the requirement to log into KB_SQL. Server fails to start, the variable SQLERR may be trapped and be etermine the reason for failure.

p

pin

ay maintena

D STOP

This com uch as Shutdown, etc.

ns , however, prevent any new connections from being

to ensure that everyone is logged off before stopping the KB_SQL Server. You may nt connection status by choosing Connections on the System Status menu. purposes. As it allows

environment. Backgro

If

KB_SQL Server is properly configured, you may start the server simply by logging into KB_SQL as a DBA or System Manager. You may also type:

>D START^SQL0SVR

at the M prompt. This command line option allows you to start KB_SQL Server during the startup of your M s

me reason, the If, for so

used to d

Stop ing the Server

Stop

You m

g the Server

need to stop the KB_SQL Server for various reasons (shutting down M, system nce, etc.) You may stop the KB_SQL Server with the following command:

^SQL0SVR

mand may be called from procedures s

Note: It is important to understand that this command does not affect current connectio

to the KB_SQL Server. It does

established. As some applications require multiple database connections for operation, it is best

(26)

(27)

Simulating ODBC with QUICK^SQL

Using QUICK^SQL

The Quick SQL Editor (QUICK^SQL) is a KB_SQL query interface that lets you use nd ted from an ODBC application. Because of this you can use this editor to simulate queries from ODBC applications. The Quick SQL Editor is also a valuable tool for testing stored procedures and parameterized queries.

>D QUICK^SQL Password:

Enter SQL Command GO Execute command STOP Exit

RESET Clear command

CALL procedure() Call stored procedure @file Read commands from file

-->SELECT NAME, PROJECT FROM PROJECTS -->GO

NAME PROJECT GREEN PHARMACY ROBERTS LABORATORY ROBERTS MEDICAL RECORDS GREEN ACCOUNTING

(4 rows)

Windows Notepad to enter your SQL statements, save the statements as a text file, a execute the file from the QUICK utility. Queries executed from this interface take the same path as a query crea

(28)

(29)

Managing ODBC Data Sources

Data Sources

though Data source Description User DSN

he current user. User System

DSN

em, or

rces that can be shared between all users that have the nd so have access to the database. These data sources need not be dedicated to a user or local to a computer. File data sources do not

urce name, as user or system data sources do; they are identified by a file name with a DSN extension.

ser and system data sources are collectively known as machine data sources because they are local to a computer.

on information to be used

red among several users. When a File DSN is used, the

Data Source Types

There are two types of data sources: machine data sources and file data sources. Al both contain similar information about the source of the data, they differ in the way this information is stored. Because of these differences, they are used in somewhat different manners.

These are local to a computer, and can only be used by t

data sources are registered in the HKEY_CURRENT_USER registry subtree. These are local to a computer, rather than dedicated to a user. The syst

any user having privileges, can use a data source set up with a system data source. System DSNs are registered in the HKEY_LOCAL_MACHINE registry subtree.

File DSN

These are file-based sou same drivers installed, a have a data so

U

Each of these data sources has a tab in the ODBC Administrator that is dedicated to that type.

File DSN

File data sources are stored in a file and allow connecti repeatedly by a single user or sha

(30)

KBSODBCDriver

A shareable File DSN can be placed on a network and used simultaneously by mu applications.

A file DSN can also be unshareable. An unshareable .dsn file resides on a single machine and points to a machine data source. Unshareable File data sources exist mainly to allo the easy conversion of machine data sources to File DSN so that an application can be ltiple

w designed to work solely with File DSNs. When the Driver Manager is sent the

information in an unshareable File DSN, it connects as necessary to the machine data source that the .dsn file points to.

Example

Here is an example of a File DSN for a KB_SQL connection. Note that the file is typically stored in the default path provided by Microsoft, but the file can be stored anywhere including on a networked drive.

C:\Program Files\Common Files\ODBC\Data Sources\KBSQL.DSN [ODBC]

DRIVER=KB_SQL ODBC 32-bit Driver UID=DBA SERVER=KB_SQL MaximumString=220 CommunicationBuffer=20 Network=C:\WINNT\System32\kbnet32.dll Setup=C:\WINNT\System32\kbset32.dll TCP_PORT=6500 HOST=192.9.90.36

Tracing ODBC Calls

Tracing API Calls

One of the best ways to understand what is going on when an application uses an ODBC connection to a remote data source is to trace the conversations. There are several conversations going on. The client-side-trace refers to the tracing of the conversation between the application and the ODBC Driver Manager. The server-side-trace refers to the tracing of the conversation between the KB_SQL ODBC Driver and the KB_SQL Server.

In general, the server-side trace is the most useful to KBS support and is also the easiest to interpret.

The client-side trace is a secondary source of information. It can be challenging to interpret -- let us help you.

Tracing_on_the_Client

Tracing on the Client

The ODBC Driver Manager has a trace facility that allows the sequence of function calls made by an ODBC application to be recorded and transcribed into a log file. Tracing is 26

(31)

performed by a trace DLL that captures calls betw Manager, and the Driver Manager and the driver.

een the application and the Driver This method of tracing replaces the tracing performed by the ODBC 2.x Driver Manager and the tracing performed in ODBC

Enabling tracing

nversational exchange between the 2.x by ODBC Spy.

Certain situations call for examination of the co

application and the driver manager. This can be accomplished using the tracing option provided by Microsoft.

Sample trace output

The trace file can be large and its contents are not intuitive. However, these details are invaluable to KBS technical support representatives when trying to diagnose application

(32)

KBSODBCDriver behaviors.

Tracing_on_the_Server

Tracing on the Server

In developing your own custom Windows applications for use with your KB_SQL database, you may find it necessary from time to time to view the interaction between

ion and the KB_SQL Server. All communication between ODBC-compliant

Viewing the API Trace Interpreting the API Trace

Enabling the API Trace Feature

1. Sign on to KB_SQL as DBA or System Administrator. 2. Choose SYSTEM STATUS.

your applicat

applications and KB_SQL occurs in sequences of function calls. These function calls may be recorded and reported on for both informative and debugging purposes. The following sections will describe the enabling of the API Trace feature and the viewing of the output of an API Trace session.

Enabling the API Trace Feature

(33)

3. Choose SERVER INFO. 4. Choose TRACE OPTIONS. 5. Select ENABLE TRACING.

Caution: The API Trace feature is intended for informative and debugging purposes only.

afte a

Vie n

5. RACE.

It is not intended to be run in a production environment. If it is necessary to use the trace, we recommend turning it on right before you use it and turning it off immediately

rw rds.

wi g the API Trace

1. Sign on to KB_SQL.

2. Choose SYSTEM STATUS. 3. Choose SERVER INFO. 4. Choose TRACE OPTIONS.

(34)

KBSODBCDriver

Interpreting the API Trace

Upon running the SQL_API_SERVER_TRACE query, you will see a screen describing the connection information for the most recent KB_SQL Server connection. Following this information is the sequence of function calls from the client application to the KB_SQL Server, ordered from earliest to last.

There are a number of sources you may use in helping you to determ :

ine the functionality

s he nds this book to

Con l of established

connections. If a connection has already been created and placed in a pool, an application without performing the entire connection process. Re-using behind each of these function calls

• The online query SQL_API_LIST. This query lists all ODBC API functions supported by KB_SQL with a brief description of each.

• See ServerAPI functions for an overview of the KB_SQL ODBC Architecture a well as a detailed description of all currently supported function calls.

• The Microsoft ODBC 2.0 Programmer’s Reference and SDK Guide. This is t BC specification. KBS recomme

definitive work on the OD

anyone involved in developing and deploying ODBC-compliant applications.

Connection Pooling

Connection pooling

nection pooling enables an application to use a connection from a poo can use that connection

(35)

connections from the pool can increase performance since applications can save the overhead involved in making a connection. This is especially significant for middle-tier applications that connect over a network or in a situation where the application repeatedly con

Enabling Connection Pooling

nects and disconnects (e.g. Internet applications).

Connection pooling can be used by an ODBC application if the application calls

SQLSetEnvAttr with the SQL_ATTR_CONNECTION_POOLING attribute. In order to KB_SQL’s ODBC driver, it will be necessary to enable e which an inactive connection is removed from the pool. enable connection pooling with

the feature and set the timeout value via the ODBC administrator. This timeout value determines the period of tim

(36)

(37)

Windows Applications

A

sing Windows Applica s ju ws

pplications with any oth

See the documentation for the specific Windows application for more information about orking with SQL Data Sources.

icrosoft Access

g from Microsoft Access to your M system is easy using the KB_SQL ODBC

river. The conn empte hin Access,

pically during t process nd you will be e

ave password

assword with the e

database and reconnect to the server without having to reenter your username and

assword informa d by correctly

the M or

rmation.

hould I import e?

icrosoft Access ices ource:

• Import the ew M

• Leave the data in the M databas n its current format without importing it -- this is

f you know you nly

Microsoft Access generally works faste d to, you can

odify the impor et your

icrosoft Access y consi

ecurity issues related to removing a copy from the server. These issues should be

r a or inf

Overview of Windows pplications

U tions with KB_SQL i st as it is for using Windo

a er SQL Data Source.

w

M

Connecting

Connectin

D ection will be att d based on a decision made wit

ty he file linking . You are able to use any of the connection types a

S

xpected to log in with your KB_SQL username and password. l have the op

During the connection p p

rocess, you wil Access database info

tion of saving your username and rmation. This would allow you to reopen th p tion. Note also that this capability can be disable

configuring more info

sysConf table on the server. Contact KB Systems technical support f

S or link a tabl

M provides two cho for using data from an external s

M data into a n icrosoft Access table in the current database. e and use it i

called linking.

I will use your data o in Microsoft Access, you should import it. r with its own tables, and if you nee

m M

ted table to me . One of the ke

needs just like any other table created in derations here is the volume of data and the s

(38)

KBSODBCDriver

Microsoft Access uses the JET (Joint Exchang manage database

teractions. The JET E owerful qu y features, including

e intelligence to perf queries

ueries run through JE record se :

ost part, you will probably want snapshots as these are ret eporting purposes. If you are doing updates, dynasets

e rows on the remote d

es

cess has its own set of data types. These types are mapped onto the ODBC

confusion. The documentation guide to

data appears different in an Access database than it does when data on the server.

icrosoft Query

onnecting from Microsoft Query to your M s

Driver. As of Microsoft Office97, MSQuery w .

his limitation is removed with Microsoft Off

any of the connection types and you will be ex KB_SQL sername and passwor

GetExternalData

The option to use MSQuery from Excel and W e

MSQuery component is not installed by defau your system administrator.

eturn Data to Excel

ing you have the ed for ge

to return the data to Excel. The rules for saving the data, query, and password information

re all controlled by th n

ocumentation if you h o

icrosoft Query has its own set of data types.

types which are also mapped to the data types supported on the data source. This can lead

he docu icrosof

nding why the data appears differen it

does when you look at the same data on the se

e Technology) Engine to

in ngine is a p ery processor with man

th q

orm distributed T are stored in

across multiple data sources. The results of ts. These record sets can be of two types Dynasets or Snapshots. F

designed purely for will allow you to updat

Data typ

Microsoft Ac

or the m rieval and r

ata source.

types which are themse This can lead to

understanding why the you look at the same

lves mapped on to the data types supported on the data source. for Microsoft Access is your best

M

Connecting

C ystem is easy using the KB_SQL ODBC

as biased towards the use of file DSNs

T ice97 Service Pack #2. You are able to use

pected to log in with your

u d.

ord is dependent on your installation. Th lt. If it is not available to you, check with

R

Assum

option install tting external data, the obvious next step is a

d

e Microsoft applicatio ave questions about h

s. Please check with the application w data is returned.

Data types

M These types are mapped onto the ODBC

to confusion. T to understa

mentation for M t Query, Excel, or Word is your best guide t in a Microsoft Office application than rver.

(39)

Programming Considerations

Architecture

A

T

ccessing the KB_SQL Engine from Windows

he KB_SQL Client API can be accessed as a dynamic link library (DLL) from ndows programming paradigm, the DLL allows

, connection to the Server

Exp ct

e with Windows programming, DLLs, Windows applications, and

Stat tion

The KB_SQL API supports two methods of statement execution: the direct method

(usi e/execute method (using the

SQL r ute functions). When an SQL statement is prepared it is sent to the ideal method for statements that are

e:

ation does not need information about the result set before executing the statement

Windows via C or Visual Basic. A Wi

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

API.

e ations:

• Experienc gateways

ement Execu

ng the SQLExecDirect function) and the prepar P epare and SQLExec

the server, parsed, and optimized. This is

executed repeatedly, each time with different parameter values.

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

• 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: ation will execute the statement only once.

• The applic

(40)

KBSODBCDriver

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

ter Markers

Parameter markers in a prepared SQL statement are placeholders for parameter values that e

Using p t lets you execute the statement repeatedly

wit i

applica change.

To use parameter markers, the application must first call the SQLBindParameter function ples of using parameter markers are found

PI supports the use of stored procedures as a collection of dure ing repetitive tasks because they are compiled the first time en executed again, subsequent run time is much shorter than for the nt set of stand-alone statements.

eatedly change

at are submitted eries for server.

here nnn represents the

y into a new

y stored procedure permanent so that it will always be available red query buffer. This type of procedure is desirable for L statements. Only the calling parameters and result set must stay ting

ry that returns a result set in the ODBC format.

ET CLIENT=ODBC See also Parameter_Markers

Parame

ar supplied by an application. Parameter markers are delimited by question marks. arameter markers in an SQL statemen

h d fferent values. This is a more efficient alternative to coding literal values in an tion, and changing the application each time the parameters

to bind a storage area to a parameter marker. Then the application must place the parameter’s value in the storage location. Exam

in the Stored Procedures section of this chapter. See also Parameters

Stored Procedures

The KB_SQL Server A

precompiled SQL statements residing on the server. An application can call a proce in place of an SQL statement. Stored procedures help to increase performance and consistency when perform

they are executed. Wh equivale

Stored procedures simplify application development because you can rep 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 th

from client applications. These procedures are available for use by all server qu as long as the procedure is in the buffer of stored queries on the

The query text is stored under a name like @SERVER_nnn, w

number of the query relative to the number of stored queries allowed.

IMPORTANT: DBA users can copy the definition of a server quer query, but server queries should not be modified in any way. Permanent Stored Procedures

KB_SQL can make an

regardless of the size of the sto

use within custom applications. It allows the application to be developed independently of the server-side SQ

constant. Otherwise, the stored procedure can be modified as needed without affec the client application.

Steps to create a permanent stored procedure

1. Create a que S

(41)

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

icate that the procedure is permanent.

S

es the query take on the characteristics of a stored procedure until you remove QLProcedures function.

RAMETERS='name,city,state'

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

Thi c edureColumns

fun o nd

STO E

Ru in

Replace the SQL statements in your application with the ODBC escape clause syntax for rocedure. re-name[(parameter[,parameter...])]} le ary, Comments mpDept = ? mps(?)}

se used to receive the department

• The server will require that the number of parameters in the reference must match r in the procedure definition.

applications. S 2. Ind

SET STORED_PROCEDURE=YE This mak

the setting. This query will appear in the result set for the S 3. Give names to input parameters (optional). SET PA

s a ts as documentation, and is accessible in the result of the SQLProc cti n. Note that setting PARAMETERS implicitly sets the CLIENT type a

R D_PROCEDURE indicators. nn g a stored procedure calling a stored p Syntax {call procedu Examp Replace:

Select Name, Position, Sal From Employees

Where E

With:

{call DeptE

The ‘?’ represents a parameter marker, in this ca identifier.

Rules

• Users must have SELECT privileges on the stored procedure query.

• The stored procedure query must be compiled in order to show in SQLProcedures, SQLProcedureColumns, or to be executed. the numbe

(42)

KBSODBCDriver

• If the parameters or the result set changes, you would have to respecify your the logic on the server side by editing and recompiling the query.

Tes g

You ma ogram will

let u

Example

?,?)}

te SQL option in MSQuery to invoke a stored procedure. Note that this m for collecting input parameters, so you must supply

KB Q

Belo descrip semant

Task

Connecting to a data SQLAllocEnv Obtains an environment handle. An environment handle can be used for one or more connections.

SQLAllocConnect Obtains a connection handle. A connection handle can be used for one or more statements.

SQLConnect Connects to a specific driver by data source name, user ID, and password. nect* Connects to a specific driver by

connection string or invokes driver display connection dialog boxes. LGetInfo Returns information about a specific

driver and data source.

SQLGetTypeInfo Returns information about supported application. Otherwise, changes can be made to

tin stored procedures

y test stored procedures by using the sample routine SQLJ13. This pr yo enter the ODBC syntax for calling the procedure.

>{call xyzProc( >go

Use the Execu

interface does not have a mechanis the values as string

literals.

Example

{call xyzProc('abc','123')}

ODBC API Calls

Functions

_S L API Function Summary

w are the KB_SQL API functions, grouped by type of task, including a brief tion of the purpose of each function. For more information about the syntax and ics of each function, consult the alphabetical reference section of this chapter.

Function Name Purpose

source

SQLDriverCon

Obtaining information ver and data

SQ about a dri

source

(43)

data types.

pported driver functions. a connection option.

SQLGetConnectOption Returns the value of a connection tion.

option.

Statement Inserts partial SQL statements. Allocates a statement handle.

SQLPrepare Prepares an SQL statement for later execution. arameter Assigns storage for a parameter in an SQL

e cursor name associated with a t handle.

sor name. ing

requests

SQLDescribeParam Returns the description of a parameter marker associated with a prepared SQL statement. ExecDirect Executes a statement.

ommand text as translated by Returns the number of parameters in an SQL statement.

ata* Used in conjunction with SQLPutData to supply parameter data at execution time.

a Send part or all of a data value for a parameter. ed by SQLGetFunctions Returns su

Setting and retrieving ptions

SQLSetConnectOption Sets driver o

option.

SQLSetStmtOption Sets a statement op

SQLGetStmtOption Returns the value of a statement

Preparing SQL KBS requests SQLAllocStmt SQLBindP statement. SQLGetCursorName Returns th statemen SQLSetCursorName Specifies a cur Submitt

SQLExecute Executes a prepared statement. SQL

SQLNativeSql Returns the SQL c the server. SQLNumParams

SQLParamD SQLPutDat

(44)

KBSODBCDriver

specifies the data type. SQLFetch Returns a result row.

LGetData eturns a specific column value from a statement cursor.

and information about (continued)

ror eturns additional error or status information.

about the data

ColumnPrivilege eturns a list of columns and associated privileges for the specified table.

mns Returns a list of column names in specified tables.

Keys Returns information about the foreign keys for a table.

Returns the list of column name(s) that comprise the primary key for a table. es Returns the list of procedure names stored

in a specific data source.

olum Returns information about the optimal set of columns that uniquely identifies a row in a specified table, or the columns that are automatically updated when any value in the row is updated by a transaction. LStatistics Returns statistics about a single table and

the list of indexes associated with the table.

SQLTablePrivileges Returns a list of tables and the privileges SQLTables Returns the list of table names stored in a

source.

SQLFreeStmt Ends statement processing and closes any associated cursors, discarding pending results, and optionally, frees all resources associated with the statement handle. Cancels an SQL statement.

ransact Commits or rolls back a transaction. Disconnect Closes the connection.

SQLFreeEnv Releases the environment handle.

SQ R

Retrieving Results SQLEr results

R

Obtaining information SQL source’s system tables (catalog functions) s R SQLColu SQLForeign SQLPrimaryKeys SQLProcedur SQLSpecialC ns SQ

associated with each table. specific data Terminating a statement SQLCancel SQLT Terminating a n SQL connectio

SQLFreeConnect Releases the connection handle.

(45)

For f programming languages, see e

M Programming

verview_M_Programming

e

KB_ SQL API function in alphabetic order. Each programming language function.

some or all of the following items:

ions

L provides SQL access to M data. You can choose to leverage this power for repo tin

func o com in control

A Word about Variable Names.

shown in the following functions are used for illustrative purposes only. QL” for use as the first three letters of our variable iable name conventions suitable to your operation. in ormation on using the ODBC API functions from other

th Microsoft ODBC Software Development Kit and Programmer's Reference.

O

KB_SQL API Function Referenc

ribe each The following pages desc

function is defined as an M de Descriptions may inclu

• Arguments • Returns yntax • S • Examples es • Not • Opt • Result set • Related functions Add SQ KB_SQ

L access to your M routines.

r g, data browsing, updating, or any combination. With the KB_SQL API ti ns, you can encapsulate your SQL references in your M programs. This b ation allows you to leverage your M programming skills with a well-defined,

led interface to the data using SQL. Variable names

KB_SQL reserves the letters “S ggest you use var names. We su

(46)

KBSODBCDriver

S S I SQ C Notes

• This function can be used to insert partial SQL commands to the SQL server. Its support SQLPrepare.

ce or punctuation character. A special subroutine, ^SQLK4, may be invoked to automate this process

s a long line (375 characters), thus exceeding the default of 255 bytes M imposes on strings.

COL3, COL4, COL5, COL6,

OL10, COL11, COL12, COL13, COL14, COL18, COL19, COL20, COL21, COL22, , COL26, COL27, COL28, COL29, COL30, COL33, COL34, COL35, COL36, COL37, COL38,

TABLE WHERE COL1 = 100 AND (COL2 = 200) BY COL3

pts to split first on a space boundary, followed by one of several characters.

SQLK4 0) 7

, COL9, COL10, COL11, COL12, COL13, COL14, L20, COL21, L22, COL23, COL24, COL25, COL26, COL27, COL28, X(5) COL29, COL30, COL31, COL32, COL33, COL34, COL35, X(6

X(7) C

Now, y tement for each entry in X(n).

Wh u

this invocation occurs in the last segment of the SQL command text.

is very long. Below you see a sequence of typical statements

ements.

KS^SQLOS(SQLHSTMT,X(I)) I SQLCODE'=0 G Error P^SQLOS(SQLHSTMT,X(N))

rm your own split logic, make sure you adhere to all of the rules LKI subroutine.

QLCODE=$$KS^SQLOS(SQLHSTMT, SQLTEXT) L ODE'=0 D Error G:SQLCODE<0 Discon

purpose is to

• Splits should occur on a spa SPLIT

For example, suppose X contain limitation

X SELECT COL1, COL2, COL7, COL8, COL9, C COL15, COL16, COL17, COL23, COL24, COL25 COL31, COL32,

COL39, COL40 FROM ORDER

You can invoke the SPLIT^SQLK4 subroutine to decompose the line into smaller segments. It attem

punctuation

>D SPLIT^ X(

X(1) SELECT COL1, COL2, COL3, COL4, COL5, COL6, COL7, X(2) COL8

X(3) COL15, COL16, COL17, COL18, COL19, CO X(4) CO

) COL36, COL37, COL38, COL39, COL40 FROM TABLE WHERE OL1 = 100 AND (COL2 = 200) ORDER BY COL3

ou simply call KBSSta

en sing this function, you must still invoke the SQLPrepare function. Typically, For example, assume X

from the subroutine’s point of entry through the call to SQLPrepare.

>D SPLIT^SQLK4 >S N=X(0)

Note: X(0) contains the count of el

>F I=1:1:N-1 DO > . SQLCODE=$$ >S SQLCODE=$$

Note: If you perfo

observed by the SPLIT^SQ

SQLAllocConnect

(47)

Allocates a connection handle.

Arguments

Environment handle [SQLHENV]

Returns

le [SQLHDBC]

SQLCODE=$$AC^SQLOS(SQLHENV,.SQLHDBC) xample

S SQLCODE=$$AC^SQLOS(SQLHENV,.SQLHDBC)

is function requires a valid environment handle (SQLHENV). andle (SQLHDBC) is returned by the function. ou need a connection handle to store information about the connection.

tatement handles. ndle. ents None Ret n Ret

Environment handle [SQLHENV]

SQLCODE=$$AE^SQLOS(.SQLHENV) xample

S SQLCODE=$$AE^SQLOS(.SQLHENV)

(SQLHENV) is returned by the function.

u need an environment handle to store information about the environment. contain multiple connection handles.

Return code [SQLCODE] Connection hand Syntax E I SQLCODE'=0 D Error Q Notes • Th • A valid connection h • Y

• A connection can contain multiple s

SQLAllocEnv

Allocates an environment ha

Argum ur s

urn code [SQLCODE]

Syntax E

I SQLCODE'=0 D Error Q Notes

• A valid environment handle

• Yo

• An environment can

(48)

KBSODBCDriver

S SQLCODE=$$AS^SQLOS(SQLHDBC,.SQLHSTMT) I SQLCODE'=0 D Error G:SQLCODE<0 Discon

• andle (SQLHSTMT) is returned by the function.

• You need a statement handle to prepare and execute an SQL command.

pression to a host variable. Statement handle [SQLHSTMT] Col n Col n e [NAME] Returns ODE] $$BC^SQLOS(SQLHSTMT,SQLCOL,NAME) (SQLHSTMT,2,"CNAME") DE'=0 D Error G:SQLCODE<0 Discon

ation. SQLFetch function places data into the host variable.

y the name by the actual column number. Consider

ch fetch operation returns the local array XYZ populated with all columns from the result set. For example, SELECT

NAME, SEX, DOB FROM PATIENT yields: XYZ(1) = name

ameter

the data type for a parameter. andle [SQLHSTMT]

s

Return code [SQLCODE]

Syntax Notes

• This function requires a valid connection handle (SQLHDBC). A valid statement h SQLBindCol Binds a select ex Arguments um sequence [SQLCOL] um nam Return code [SQLC Syntax SQLCODE= Example S SQLCODE=$$BC^SQLOS I SQLCO Notes

• This function cannot occur prior to statement prepar

• The

• If a column sequence is 0, all columns in the array are bound b parameter and sequenced

$$BC^SQLOS(SQLHSTMT, 0, "XYZ"). Ea XYZ(2) = sex XYZ(3) = dob SQLBindPar Declares Arguments Statement h

Parameter sequence [SEQ] ODBC data type [TYPE]

Return

(49)

SQ O Examp S SQL I SQLC Notes

• This function is equivalent to the SQLSetParam function from ODBC V1.0.

• The data type is the numeric value of the ODBC data type.

• Run the on-line query SQL_API_DATATYPE to generate a list of supported data

statement. t handle [SQLHSTMT] [SQLCODE] Syntax SQ Example r G:SQLCODE<0 Discon tes

• This function is primarily useful when asynchronous processing is enabled.

SQLColAttributes

Returns information on a particular feature of a column.

Arguments

Column sequence [SQLCOL] Des ip Returns DE] Syntax SQ O LC DE=$$BP^SQLOS(SQLHSTMT,SEQ,TYPE) le CODE=$$BP^SQLOS(SQLHSTMT,1,1) ODE'=0 D Error G:SQLCODE<0 Discon

types. SQLCancel Cancels a Arguments Statemen Returns Return code LCODE=$$CS^SQLOS(SQLHSTMT) S SQLCODE=$$CS^SQLOS(SQLHSTMT) I SQLCODE'=0 D Erro No Statement handle [SQLHSTMT] cr tor type [SQLDESC] Return code [SQLCO

Descriptor value [VALUE]

(50)

KBSODBCDriver SQL_COLUMN_NAME 1. 2. SQL_COLUMN_PRECISION 4. SQL_C SQL_C SQ C SQL_C SQL_C SQL_C SQL_COLUMN_TYPE_NAME 14 SQL C SQL C SQLColumnPrivileges

Privileges returns a list of columns and associated privileges for the

HS L] DE] =$$CM^SQ LUMN) SQL_COLUMN_TYPE SQL_COLUMN_LENGTH 3. OLUMN_SCALE 5. OLUMN_DISPLAY_SIZE 6. SQL_COLUMN_UNSIGNED 8 SQL_COLUMN_MONEY 9 L_ OLUMN_UPDATABLE 10 OLUMN_AUTO_INCREMENT 11 OLUMN_CASE_SENSITIVE 12 OLUMN_SEARCHABLE 13 SQL_COLUMN_TABLE_NAME 15 SQL_COLUMN_OWNER_NAME 16 _ OLUMN_QUALIFIER_NAME 17 _ OLUMN_COLUMN_LABEL 18 SQLColumn specified table.

Note: This function is imp

Arguments

lemented but not yet supported.

Statement handle [SQL TMT] TableQualifier [QUA TableOwner [OWNER] TableName [TABLE] ColumnName [COLUMN] Returns

Return code [SQLCO

Syntax

S SQLCODE LOS(SQLHSTMT,QUAL,OWNER,TABLE,CO

Notes

(51)

• The ColumnName search pattern.

• This function will lt set until the product supports

column-level privileges wi gine.

QLColumns

eturns the list of column ables.

rguments tatement handle [SQLHS r [QUAL] OWNER] ] ] yntax QLCODE=$$CM^SQLOS(SQLHSTMT,QUAL,OWNER,TABLE,COLUMN) HSTMT,"","SQL_TEST","EMPLOYEES","%" :SQLCODE<0 Discon

s for an index table. Use the SQLStatistics ex table.

next page) that can be processed like any

SQ o

Con ec

argument accepts a return an empty resu thin the KB_SQL En S R names in specified t A S TMT] Table qualifie Table owner [

Table name [TABLE Column name [COLUMN

Returns

S S Example S SQLCODE=$$CM^SQLOS(SQL ) I SQLCODE'=0 D Error G Notes

• SQLColumns does not return column function to return the columns of an ind

result set (see the

• This function returns a other query result.

LC nnect

n ts to KB_SQL server.

Arguments

Data source name [SQLDATAS] QLUNAME] User name [S

(52)

KBSODBCDriver

• The data source name parameter is not currently validated by the Server API. If you are using the API f

• or M, set the data source name parameter to "M".

• If you are using the API for C, the data source name parameter takes the value of in the ODBC.INI configuration file.

tadata on a column. LHSTMT] quence [SQLCOL] de [SQLCODE] type [TYPE] NULLABLE] DC^SQLOS(SQLHSTMT,SQLCOL,.NAME,.TYPE,.PREC,.SCALE S COL=3 SQLCODE=$$DCSQLOS(SQLHSTMT,COL,.NAME,.TYPE,.PREC,.SCALE,.NUL I SQLCODE'=0 D Error G:SQLCODE<0 Discon

otes

olumn data dictionary information for lumn in the result set. Note that COL is a number where 1 <= COL

s an easier form of the more complete SQLColAttributes function.

s the description of a parameter marker associated with a SQL statement. e [PARSEQ] CALE] ] SQ N The par SQL_NULLABLE(1) the entry SQLDescribeCol Gets basic me Arguments Statement handle [SQ Column se Returns Return co

Column name [NAME] ODBC data Precision [PRECISION] Scale [SCALE] Nullable [ Syntax SQLCODE=$$ ) ,.NULLABLE Example S LABLE) N

• The example uses the variable COL to get c the third co <= NumResultCols. • This function i SQLDescribeParam SQLDescribeParam return prepared Arguments andle [SQLHSTMT] Statement h Parameter sequenc SqlType [TYPE] Precision [PREC] Scale [S Nullable [NULLABLE L_ O_NULLS(0)

ameter does not allow NULL values. (This is the default value).

(53)

The a

SQ N

The driver cannot determine if the parameter allows NULL values.

YPE,.PREC,.SCALE, ) cts from KB_SQL Server. handle [SQLHDBC] SQLCODE=$$D^SQLOS(SQLHDBC) Exa p DISCO I SQLCODE'=0 D Error Notes

• , this function causes a network disconnect.

s

BC] TMT]

[SQLNC]

p rameter allows NULL values. L_ ULLABLE_UNKNOWN(2) Syntax S SQLCODE=$$DP^SQLOS(SQLHSTMT,PARSEQ,.T .NULLABLE SQLDisconnect Disconne Arguments Connection Returns

Syntax m le

N S SQLCODE=$$D^SQLOS(SQLHDBC)

• All connection statement handles are dropped. In the KB_SQL API for C

SQLError

Returns error information.

Argument

Connection handle [SQLHD Statement handle [SQLHS

Returns

Return code [SQLCODE] Sqlstate [SQLSTATE] Native code

(54)

KBSODBCDriver W !,"SQLCOD '$G(SQLHDBC) S SQLHDBC=0 I '$G(SQLHS 4. S SQLCOD SQ I SQLCODE=100 Q W "SQL I ERS="" G 5 S K65=$O

5. W !,"Error Code= ",ERC,!,"Error Text= ", 6. E= ",SQLCODE,! N SQLCODE,K65,ERS,ERC,ERT I TMT) S SQLHSTMT=0 E=$$ER^SQLOS(SQLHDBC,SQLHSTMT,.ERS,.ERC,.ERT) I LCODE<0 Q STATE= ",ERS (^SQL(65,-1,ERS,0)) W " ",^SQL(65,K65,2) ERT,! G 4 Q SQLExecDirect Pre re Argum Statem SQL co Ret n Ret Syn x SQLCO Example CODE<0 Discon d SQLExecute. pared SQL command. HSTMT] Returns

eturn code [SQLCODE]

Syntax

HSTMT)

QLCODE<0 Discon

pa s and executes an SQL command.

ents

ent handle [SQLHSTMT] mmand [SQLTEXT]

ur s

urn code [SQLCODE]

ta DE=$$ED^SQLOS(SQLHSTMT,SQLTEXT) S SQLCODE=$$ED^SQLOS(SQLHSTMT,"SELECT * FROM SQL_TEST.EMPLOYEES") I SQLCODE'=0 D Error G:SQL Notes

• This function can be used in place of SQLPrepare an

SQLExecute

Executes a previously pre

Arguments Statement handle [SQL R SQLCODE=$$E^SQLOS(SQLHSTMT) Example S SQLCODE=$$E^SQLOS(SQL I SQLCODE'=0 D Error G:S Notes 50

(55)

• If your query includes parameters, the SQLExecute function can return

ed for parameters. his point, you would invoke SQLPutData until SQLCODE is not equal to 99.

to the illustration in the Error Processing section earlier in this chapter.

Advanc Arg Statem Returns DE] $$F^SQLOS(SQLHSTMT) QLCODE=$$F^SQLOS(SQLHSTMT) DE'=0 D Error G:SQLCODE<0 Discon

2. . Notes

•

SQ o

SQLForeignKeys can return:

le that "SQLCODE=99," which indicates that data needs to be provid

At t Refer

SQLFetch

es the cursor position for a statement.

uments

ent handle [SQLHSTMT] Return code [SQLCO

Syntax SQLCODE= Example 1. S S I SQLCO I SQLCODE=100 G 2 . . . G 1

This function automatically populates all bound columns for each row.

LF reignKeys

(56)

KBSODBCDriver FkTableQualifier [FKQUAL] FkTableOwner [FKOWNER] FkT l Syntax S SQL ER,PKTABLE, FK Notes • UALIFIER,

Example: To obtain a list of foreign keys that refer to the PATIENT table.

S S

set

FKTABLE_QUALIFIER, FKTABLE_OWNER, FKTABLE_NAME, and

stored in the PATIENT table.

LHSTMT,””,””,””,””,””,”PATIENT”)

kTableName and FkTableName contain table names, SQLForeignKeys foreign keys in the table specified in FkTableName that refer to the

f the table specified in PkTableName.

T and VISIT

e Description

Primary key table qualifier identifier. Primary key table owner identifier. Primary key table identifier. AME Primary key column identifier.

R Foreign key table qualifier identifier. ER Foreign key table owner identifier. E Foreign key table identifier. _NAME Foreign key column identifier.

Column sequence number in key (starting with 1). E Action to be applied to the foreign key on UPDATE. ab eName [FKTABLE]

CODE=$$FK^SQLOS(SQLHSTMT,PKQUAL,PKOWN QUAL,FKOWNER,FKTABLE)

If PkTableName contains a table name, SQLForeignKeys returns a result set containing the primary key of the specified table and all of the foreign keys that refer to it. The result set is ordered by PKTABLE_Q

PKTABLE_OWNER, PKTABLE_NAME, and KEY_SEQ.

QLCODE=$$FK^SQLOS(SQLHSTMT,””,””,”PATIENT”,””,””,””)

• If FkTableName contains a table name, SQLForeignKeys returns a result containing all of the foreign keys in the specified table and the primary keys (in other tables) to which they refer. The result set is ordered by

KEY_SEQ.

Example: To obtain a list of foreign keys

S SQLCODE=$$FK^SQLOS(SQ

• If both P returns the primary key o

Example: To obtain a list of the one foreign key that joins the PATIEN

tables. S SQLCODE=$$FK^SQLOS(SQLHSTMT,””,””,”PATIENT”,””,””,”VISIT”) Result Set Column Nam PKTABLE_QUALIFIER R PKTABLE_OWNE PKTABLE_NAME PKCOLUMN_N FKTABLE_QUALIFIE FKTABLE_OWN FKTABLE_NAM FKCOLUMN KEY_SEQ UPDATE_RUL 52

(57)

SQL_CASCADE L_RESTRICT L_SET_NULL ULL

Action to be applied to the foreign key on DELETE. SQL_CASCADE

ICT ULL ULL if not applicable

dentifier. Primary key identifier. .

ed statement handles.

DBC)

• This function effectively closes all active statements and drops the connection.

all associated connection and statement handles. SQLHENV] SQ Q S N DELETE_RULE SQL_RESTR SQL_SET_N N

FK_NAME Foreign key i

PK_NAME

Related Functions

Sample routine SQLJ16

SQLFreeConnect

Frees the connection handle and all associat

Arguments QLHDBC] Connection handle [S Returns code [SQLCODE] Return Syntax SQLCODE=$$FC^SQLOS(SQLHDBC) Example S SQLCODE=$$FC^SQLOS(SQLH Error Q I SQLCODE'=0 D Note SQLFreeEnv

Frees the environment handle and

Arguments

ent handle [ Environm