SQL Call Level Interface APIs let you do embedded SQL without the SQL product
From Support
Hidden Treasure - SQL Call Level Interface APIs let you do embedded SQL without the SQL product If you ever need to do embedded SQL to drastically ease a project, but your shop doesn’t have the SQL/400 product (now called “IBM DB2 Query Manager and SQL Development Kit for AS/400”).
OS/400 now comes with a complete set of APIs that allow you to do embedded SQL without having the SQL/400 product. Even though these APIs have been available for a few years, little has been made of them, in part, because the documentation is written in C and is nearly non-existent. Previously, you needed the SQL/400’s SQL precompilers to compile any program containing embedded SQL statements. However, since these SQL APIs are, in fact, APIs, they are accessed by calling them, just as you would any other API. And, they are based on the ODBC standard so you can take advantage of your knowledge of ODBC when using APIs. SQL has many benefits over DDS
First and foremost, the AS/400 database (called DB2/400) is now a true relational database. As a consequence, SQL — not DDS — is the true database language for the AS/400 database. But even before DB2/400 was introduced, SQL had many advantages over DDS. Here are just a few:
Powerful operators and functions. SQL provides many operators and functions that let you specify complex expressions and record selection criteria. For example: you can multiply two fields to produce a new field containing the product (field1 * field2); you can calculate an average of the values contained in a field (AVG(field)); and you can use parentheses and operators such as LIKE, BETWEEN, and EXIST to create complex search and selection criteria. Better join support. DDS has only limited join-file support that greatly restricts your ability to relate (or join) two or more files. SQL supports just about any join relationship you can think up.
Unions. Unions allow you to logically combine the records of two or more files. To a program, the result appears as one big file. This is different than joining files. Joined files take associated records from two or more files and "connect" them to produce a single record. Unions "append" the records from one file to the records from another file. Unions are similar to DDS’s multiple-format logical files or multiple-member physical files, but unions provide much more flexibility.
How embedded SQL helps you
Embedded SQL is an I/O "machine" that processes the SQL statements in your programs. A program "feeds" an SQL SELECT statement to the SQL engine. The SQL engine processes the statement and creates a cursor through which the program can access the records (a.k.a. rows) resulting from the SQL statement. The SQL engine creates a temporary access path and the cursor is essentially a pointer to the access path which keeps track of the current record, where the next read (a.k.a. fetch) will take place. You can execute other SQL statements — not just SELECT statements — using embedded SQL. However, SELECT statements are by far the most common because they are akin to opening a file.
Static versus dynamic embedded SQL
Static embedded SQL is precompiled. The SQL statements in a program are put through the SQL precompiler when the program is compiled. This produces very fast embedded SQL code because much of the work has already been done by the precompiler. However, it has its limitations. Namely, SQL statements must be very definite, which leaves very few ways of modifying the statement (e.g., with parameters) at run-time.
If you need the ability to modify or alter SQL statements at run-time, you must use dynamic embedded SQL. Dynamic embedded SQL is slower because, in order to gain the flexibility of modifying statements at run-time, the precompiler cannot compile the statements (i.e., since they will be modified at run-time, the SQL statements are not complete enough to precompile). The SQL statements must be parsed, prepared, optimized, and executed, all at run-time. Dynamic embedded SQL uses calls to special SQL routines that accomplish all these tasks. This is important to remember because the SQL APIs we will discuss perform comparably to dynamic embedded SQL.
How to use the SQL Call Level Interface APIs The official name for the SQL APIs is "SQL Call Level Interface (SQL CLI) APIs." The main thing to remember before rushing into these APIs is that they can be called from ordinary programs. The compiler has no way of knowing that the program will be using these APIs to perform embedded SQL and cannot, therefore, add any of the SQL preparation steps to the program, as the SQL precompilers do. This means that you are responsible for manually adding these steps using calls to the various APIs.
This preparation (or initialization) consists of allocating working storage for the SQL environment, connecting to a database, and allocating resources to run an SQL statement. All this must be done manually by the programmer using calls to APIs. There are also clean-up (or termination) APIs that are the reverse of the preparation APIs: free statement resources, disconnect from a database, and free storage for SQL environment.
When you are ready to make the commitment, use the following steps as a guide. And, remember the good news: once you have coded your first SQL CLI program, you can reuse most of it and drastically reduce your future efforts.
- .Call the SQLAllocEnv API. This API allocates working storage and other resources for the SQL environment. An application can only have one SQL environment at a time. The API returns a handle which is used subsequently by the app to identify the environment.
- .Call the SQLAllocConnect API. This API allocates the resources necessary to support the logical connection to a relational database. It returns a handle which is used to identify the connection.
- .Call the SQLConnect API. Connects the application to a relational database. This API must be used, even to connect to the local database.
- .Call the SQLAllocStmt API. This API allocates the resources necessary to run SQL statements. It returns a handle which is used to identify the resources.
- .Call other SQL CLI APIs to do the actual transaction processing. Use other SQL CLI APIs to execute SELECT statements (SQLExecute), fetch records (SQLFetch), update records (SQLExecDirect), etc.
- .Call the SQLFreeStmt API. Frees the statement handle allocated by SQLAllocStmt.
- .Call the SQLDisconnect API. Disconnects the app from the database. No SQL statements can be executed until the apps is again connected to a database.
- .Call the SQLFreeConnect API. Frees the connection handle allocated by SQLAllocConnect.
- .Call the SQLFreeEnv API. Frees the environment allocated by SQLAllocEnv.
Note, the SQL CLI APIs can only execute SQL statements that can be prepared dynamically with embedded SQL (i.e., using dynamic embedded SQL).
Use procudure calls in RPG These APIs are functions; they all return an SQL error code. Moreover, some parameters are passed by reference and others are passed by value. You must, therefore, prototype the APIs and use the CallP op-code in RPG to call them. Alternatively, you can call them as part of an Eval statement (but you still have to prototype them).
As usual, we have taken care of this grunt work for you. Starting below and continuing through page 7 you will find the #SQLAPI include file (/copy member) for ILE RPG. It contains all the procedure prototypes for the SQL CLI APIs and convenient constants you can use when calling the APIs. Also, the beginning of the file contains an informational chart containing the SQL data types you will encounter in the IBM documentation, along with the RPG equivalents. Click here _(File name: SQL Code sqlcli.txt)_ to download this file.
The #SQLCLI RPG include file
* RPG include file for SQL Call-Level Interface APIs.
* The following table is a mapping of SQL data types
* to RPG data types.
*
* SQL Data Type How to declare on a D-spec
* ------------------- --------------------------
* SQLCHAR nnnnA
* SQLINTEGER 10i 0
* SQLSMALLINT 5i 0
* SQLREAL 4f
* SQLDOUBLE 8f
* PTR *
* SQLPOINTER *
* HENV 10i 0
* HDBC 10i 0
* HSTMT 10i 0
* HDESC 10i 0
* SQLHENV 10i 0
* SQLHDBC 10i 0
* SQLHSTMT 10i 0
* SQLHDESC 10i 0
* RETCODE 10i 0
* SQLRETURN 10i 0
* Generally useful constants.
d #SQLFalse c Const(0)
d #SQLTrue c Const(1)
d #NullHandl c Const(0)
d #SQLNTS c Const(-3)
d #SQLState c Const(5)
* Return Code constants
d #SQLOK c Const(0 )
d #OKwInfo c Const(1 )
d #NoDataFnd c Const(100)
d #NeedData c Const(99 )
d #NoData c Const(100)
d #SQLError c Const(-1 )
d #BadHandle c Const(-2 )
* Data type constants
d #SQLChar c Const(1 )
d #SQLNum c Const(2 )
d #SQLZoned c Const(2 )
d #SQLDec c Const(3 )
d #SQLPacked c Const(3 )
d #SQLInt c Const(4 )
d #SQLSmlInt c Const(5 )
d #SQLFloat c Const(6 )
d #SQLReal c Const(7 )
d #SQLDouble c Const(8 )
d #SQLDatTim c Const(9 )
d #SQLVChar c Const(12)
d #SQLGraphc c Const(95)
d #SQLVGraph c Const(96)
d #SQLDate c Const(91)
d #SQLTime c Const(92)
d #SQLTimStm c Const(93)
d #SQLDfault c Const(99)
* Valid environment attributes
d #OutputNTS c Const(10001)
d #SysNaming c Const(10002)
* SQLTransact option values
d #SQLCommit c Const(0)
d #SQLRollbk c Const(1)
* Codes used in FetchScroll and ExtendedFetch
d #FetchNext c Const(1)
d #FtchFirst c Const(2)
d #FetchLast c Const(3)
d #FtchPrior c Const(4)
d #FAbsolute c Const(5)
d #FRelative c Const(6)
* SQLFreeStmt option values
d #SQLClose c Const(0)
d #SQLDrop c Const(1)
d #SQLUnbind c Const(2)
d #SQLResetP c Const(3)
* Valid values for connect attribute (ie, commitment control)
d #Commit c Const(0)
d #CmtNone s 10i 0 Inz(1)
d #CmtChange s 10i 0 Inz(2)
d #CommitCS s 10i 0 Inz(3)
d #CommitALL s 10i 0 Inz(4)
d #CommitRR s 10i 0 Inz(5)
* SQL CLI function prototypes.
d SQLAlcEnv PR 10i 0 ExtProc('SQLAllocEnv')
d EnvHandle 10i 0
d SQLAlcCon PR 10i 0 ExtProc('SQLAllocConnect')
d EnvHandle 10i 0 Value
d ConHandle 10i 0
d SQLConnect PR 10i 0 ExtProc('SQLConnect')
d ConHandle 10i 0 Value
d DBName 9999a Options(*VarSize)
d DBNameLen 10i 0 Value
d UserID 9999a Options(*VarSize: *Omit)
d UserIDLen 10i 0 Value
d PassWord 9999a Options(*VarSize: *Omit)
d PassWordL 10i 0 Value
d SQLAlcStmt PR 10i 0 ExtProc('SQLAllocStmt')
d ConHandle 10i 0 Value
d StmtHandle 10i 0
d SQLExecDir PR 10i 0 ExtProc('SQLExecDirect')
d StmtHandle 10i 0 Value
d SQLString 9999a Options(*VarSize)
d SQLStrLen 10i 0 Value
d SQLPrepare PR 10i 0 ExtProc('SQLPrepare')
d StmtHandle 10i 0 Value
d SQLString 9999a Options(*VarSize)
d SQLStrLen 10i 0 Value
d SQLExecute PR 10i 0 ExtProc('SQLExecute')
d StmtHandle 10i 0 Value
d SQLBindCol PR 10i 0 ExtProc('SQLBindCol')
d StmtHandle 10i 0 Value
d Column# 10i 0 Value
d ColumnType 10i 0 Value
d pColVar * Value
d ColVarLen 10i 0 Value
d pColDtaLen * Value
d SQLExFetch PR 10i 0 ExtProc('SQLExtendedFetch')
d StmtHandle 10i 0 Value
d OrientFlag 5i 0 Value
d Offset 10i 0 Value
d CurrentRow 10i 0
d RowStatus 5i 0
d SQLFetch PR 10i 0 ExtProc('SQLFetch')
d StmtHandle 10i 0 Value
d SQLFetchS PR 10i 0 ExtProc('SQLFetchScroll')
d StmtHandle 10i 0 Value
d OrientFlag 5i 0 Value
d Offset 10i 0 Value
d SQLDescCol PR 10i 0 ExtProc('SQLDescribeCol')
d StmtHandle 10i 0 Value
d Column# 10i 0 Value
d pColName * Value
d ColNameMax 10i 0 Value
d ColNameLen 5i 0
d ColumnType 5i 0
d ColumnLen 10i 0
d ColScale 5i 0
d Nullable 5i 0
d SQLFreeStm PR 10i 0 ExtProc('SQLFreeStmt')
d StmtHandle 10i 0 Value
d OptionFlag 5i 0 Value
d SQLDiscon PR 10i 0 ExtProc('SQLDisconnect')
d ConHandle 10i 0 Value
d SQLFreeEnv PR 10i 0 ExtProc('SQLFreeEnv')
d EnvHandle 10i 0 Value
d SQLFreeCon PR 10i 0 ExtProc('SQLFreeConnect')
d ConHandle 10i 0 Value
d SQLSetCon PR 10i 0 ExtProc('SQLSetConnectOption')
d ConHandle 10i 0 Value
d Attribute 10i 0 Value
d ParmVal 10i 0
d SQLSetEnvA PR 10i 0 ExtProc('SQLSetEnvAttr')
d EnvHandle 10i 0 Value
d Attribute 10i 0 Value
d pParmVal * Value
d ParmValLen 10i 0 Value
The DB2 for OS/400 SQL Call Level Interface manual (SC41-3806) is a "complete" reference; however, knowledge of C is assumed and the information is scant.
Categories: Misc | TBPR | Knowledge Base | Sample Code | How To
