From 0c1ec674474a317c8f90a114db4111f48cc76223 Mon Sep 17 00:00:00 2001 From: "Thomas G. Lockhart" Date: Thu, 17 Jun 1999 14:19:17 +0000 Subject: [PATCH] Add CLI required header and examples from SQL3/SQL98 August 1994 draft standard. Use the ecpg support libraries to write the CLI interface? Date and Darwen claim that CLI is a more modern and flexible approach... --- src/interfaces/cli/example1.c | 171 ++++++++++++++ src/interfaces/cli/example2.c | 260 ++++++++++++++++++++ src/interfaces/cli/sqlcli.h | 535 ++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 966 insertions(+) create mode 100644 src/interfaces/cli/example1.c create mode 100644 src/interfaces/cli/example2.c create mode 100644 src/interfaces/cli/sqlcli.h diff --git a/src/interfaces/cli/example1.c b/src/interfaces/cli/example1.c new file mode 100644 index 0000000000..95dbeabd64 --- /dev/null +++ b/src/interfaces/cli/example1.c @@ -0,0 +1,171 @@ +/* -*- C -*- */ +/* The first example illustrates creating a table, adding some data + * to it, and selecting the inserted data. The second example shows + * interactive ad hoc query processing. + * + * Actual applications include more complete error checking following + * calls to SQL/CLI routines. That material is omitted from this + * Appendix for the sake of clarity. + * + * This file is adapted for PostgreSQL + * from the CLI Annex in the SQL98 August 1994 draft standard. + * Thomas G. Lockhart 1999-06-16 + */ + +/* + * B.1 Create table, insert, select + * + * This example function creates a table, inserts data into the table, + * and selects the inserted data. + * + * This example illustrates the execution of SQL statement text + * both using the Prepare() and Execute() method and using the + * ExecDirect() method. The example also illustrates both the case + * where the application uses the automatically-generated descriptors + * and the case where the application allocates a descriptor of its + * own and associates this descriptor with the SQL statement. + * + * Code comments include the equivalent statements in embedded SQL + * to show how embedded SQL operations correspond to SQL/CLI function + * calls. + */ + +#include +#include + +#ifndef NULL +#define NULL 0 +#endif + +int print_err(SQLSMALLINT handletype, SQLINTEGER handle); + +int example1(SQLCHAR *server, SQLCHAR *uid, SQLCHAR *authen) +{ + SQLHENV henv; + SQLHDBC hdbc; + SQLHDESC hdesc; + SQLHDESC hdesc1; + SQLHDESC hdesc2; + SQLHSTMT hstmt; + SQLINTEGER id; + SQLSMALLINT idind; + SQLCHAR name[51]; + SQLINTEGER namelen; + SQLSMALLINT nameind; + + /* EXEC SQL CONNECT TO :server USER :uid; */ + + /* allocate an environment handle */ + SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); + /* allocate a connection handle */ + SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); + + /* connect to database */ + if (SQLConnect(hdbc, server, SQL_NTS, uid, SQL_NTS, + authen, SQL_NTS) + != SQL_SUCCESS) + return(print_err(SQL_HANDLE_DBC, hdbc)); + + /* allocate a statement handle */ + SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); + + /* EXEC SQL CREATE TABLE NAMEID (ID integer, NAME varchar(50)); */ + { + SQLCHAR create[] ="CREATE TABLE NAMEID (ID integer," + " NAME varchar(50))"; + + /* execute the CREATE TABLE statement */ + if (SQLExecDirect(hstmt, create, SQL_NTS) != SQL_SUCCESS) + return(print_err(SQL_HANDLE_STMT, hstmt)); + } + + /* EXEC SQL COMMIT WORK; */ + /* commit CREATE TABLE */ + SQLEndTran(SQL_HANDLE_ENV, henv, SQL_COMMIT); + /* EXEC SQL INSERT INTO NAMEID VALUES ( :id, :name ); */ + { + SQLCHAR insert[]= "INSERT INTO NAMEID VALUES (?, ?)"; + + /* show the use of SQLPrepare/SQLExecute method */ + /* prepare the INSERT */ + if (SQLPrepare(hstmt, insert, SQL_NTS) != SQL_SUCCESS) + return(print_err(SQL_HANDLE_STMT, hstmt)); + /* application parameter descriptor */ + SQLGetStmtAttr(hstmt, SQL_ATTR_APP_PARAM_ + DESC, &hdesc1, 0L, + (SQLINTEGER *)NULL); + SQLSetDescRec(hdesc1, 1, SQL_INTEGER, 0, 0L, 0, 0, + (SQLPOINTER)&id, (SQLINTEGER *)NULL, (SQLSMALLINT *)NULL); + SQLSetDescRec(hdesc1, 2, SQL_CHAR, 0, 0L, 0, 0, + (SQLPOINTER)name, (SQLINTEGER *)NULL, + (SQLSMALLINT *)NULL); + /* implementation parameter descriptor */ + SQLGetStmtAttr(hstmt, SQL_ATTR_IMP_PARAM_ + DESC, &hdesc2, 0L, + (SQLINTEGER *)NULL); + SQLSetDescRec(hdesc2, 1, SQL_INTEGER, 0, 0L, 0, 0, + (SQLPOINTER)NULL, (SQLINTEGER *)NULL, + (SQLSMALLINT *)NULL); + SQLSetDescRec(hdesc2, 2, SQL_VARCHAR, 0, 50L, 0, 0, + (SQLPOINTER)NULL, (SQLINTEGER *)NULL, + (SQLSMALLINT *)NULL); + + /* assign parameter values and execute the INSERT */ + id=500; + (void)strcpy(name, "Babbage"); + if (SQLExecute(hstmt) != SQL_SUCCESS) + return(print_err(SQL_HANDLE_STMT, hstmt)); + } + /* EXEC SQL COMMIT WORK; */ + SQLEndTran(SQL_HANDLE_ENV, henv, SQL_COMMIT); + /* commit inserts */ + + /* EXEC SQL DECLARE c1 CURSOR FOR SELECT ID, NAME FROM NAMEID; */ + /* EXEC SQL OPEN c1; */ + /* The application doesn't specify "declare c1 cursor for" */ + { + SQLCHAR select[]= "select ID, NAME from NAMEID"; + if (SQLExecDirect(hstmt, select, SQL_NTS) != SQL_SUCCESS) + return(print_err(SQL_HANDLE_STMT, hstmt)); + } + + /* EXEC SQL FETCH c1 INTO :id, :name; */ + /* this time, explicitly allocate an application row descriptor */ + SQLAllocHandle(SQL_HANDLE_DESC, hdbc, &hdesc); + SQLSetDescRec(hdesc, 1, SQL_INTEGER, 0, 0L, 0, 0, + (SQLPOINTER)&id, (SQLINTEGER *)NULL, (SQLSMALLINT *)&idind); + + SQLSetDescRec(hdesc, 2, SQL_ + CHAR, 0, (SQLINTEGER)sizeof(name), + 0, 0, (SQLPOINTER)&name, (SQLINTEGER *)&namelen, + (SQLSMALLINT *)&nameind); + /* associate descriptor with statement handle */ + SQLSetStmtAttr(hstmt, SQL_ATTR_APP_ROW_DESC, &hdesc, 0); + /* execute the fetch */ + SQLFetch(hstmt); + + /* EXEC SQL COMMIT WORK; */ + /* commit the transaction */ + SQLEndTran(SQL_HANDLE_ENV, henv, SQL_COMMIT); + + /* EXEC SQL CLOSE c1; */ + SQLClose(hstmt); + /* free the statement handle */ + SQLFreeHandle(SQL_HANDLE_STMT, hstmt); + + /* EXEC SQL DISCONNECT; */ + /* disconnect from the database */ + SQLDisconnect(hdbc); + /* free descriptor handle */ + SQLFreeHandle(SQL_HANDLE_DESC, hdesc); + /* free descriptor handle */ + SQLFreeHandle(SQL_HANDLE_DESC, hdesc1); + /* free descriptor handle */ + SQLFreeHandle(SQL_HANDLE_DESC, hdesc2); + /* free connection handle */ + SQLFreeHandle(SQL_HANDLE_DBC, hdbc); + /* free environment handle */ + SQLFreeHandle(SQL_HANDLE_ENV, henv); + + return(0); +} diff --git a/src/interfaces/cli/example2.c b/src/interfaces/cli/example2.c new file mode 100644 index 0000000000..e882274221 --- /dev/null +++ b/src/interfaces/cli/example2.c @@ -0,0 +1,260 @@ +/* -*- C -*- */ +/* The first example illustrates creating a table, adding some data + * to it, and selecting the inserted data. The second example shows + * interactive ad hoc query processing. + * + * Actual applications include more complete error checking following + * calls to SQL/CLI routines. That material is omitted from this + * Appendix for the sake of clarity. + * + * This file is adapted for PostgreSQL + * from the CLI Annex in the SQL98 August 1994 draft standard. + * Thomas G. Lockhart 1999-06-16 + */ + +/* + * B.2 Interactive Query + * + * This sample function uses the concise CLI functions to + * interactively execute a SQL statement supplied as an argument. + * In the case where the user types a SELECT statement, the function + * fetches and displays all rows of the result set. + * + * This example illustrates the use of GetDiagField() to identify + * the type of SQL statement executed and, for SQL statements where + * the row count is defined on all implementations, the use of + * GetDiagField() to obtain the row count. + */ + +/* + * Sample program - uses concise CLI functions to execute + * interactively an ad hoc statement. + */ +#include +#include +#include + +#define MAXCOLS 100 + +#define max(a,b) (a>b?a:b) + +int print_err(SQLSMALLINT handletype, SQLINTEGER handle); +int build_indicator_message(SQLCHAR *errmsg, + SQLPOINTER *data, + SQLINTEGER collen, + SQLINTEGER *outlen, + SQLSMALLINT colnum); + +SQLINTEGER display_length(SQLSMALLINT coltype, + SQLINTEGER collen, + SQLCHAR *colname); + +example2(SQLCHAR *server, SQLCHAR *uid, SQLCHAR *authen, SQLCHAR *sqlstr) +{ + int i; + SQLHENV henv; + SQLHDBC hdbc; + SQLHSTMT hstmt; + SQLCHAR errmsg[256]; + SQLCHAR colname[32]; + SQLSMALLINT coltype; + SQLSMALLINT colnamelen; + SQLSMALLINT nullable; + SQLINTEGER collen[MAXCOLS]; + SQLSMALLINT scale; + SQLINTEGER outlen[MAXCOLS]; + SQLCHAR *data[MAXCOLS]; + SQLSMALLINT nresultcols; + SQLINTEGER rowcount; + SQLINTEGER stmttype; + SQLRETURN rc; + + /* allocate an environment handle */ + SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); + + /* allocate a connection handle */ + SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); + + /* connect to database */ + if (SQLConnect(hdbc, server, SQL_NTS, uid, SQL_NTS, authen, SQL_NTS) + != SQL_SUCCESS ) + return(print_err(SQL_HANDLE_DBC, hdbc)); + + /* allocate a statement handle */ + SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); + + /* execute the SQL statement */ + if (SQLExecDirect(hstmt, sqlstr, SQL_NTS) != SQL_SUCCESS) + return(print_err(SQL_HANDLE_STMT, hstmt)); + + /* see what kind of statement it was */ + SQLGetDiagField(SQL_HANDLE_STMT, hstmt, 0, + SQL_DIAG_DYNAMIC_FUNCTION_CODE, + (SQLPOINTER)&stmttype, 0, (SQLSMALLINT *)NULL); + + switch (stmttype) { + /* SELECT statement */ + case SQL_SELECT_CURSOR: + /* determine number of result columns */ + SQLNumResultCols(hstmt, &nresultcols); + /* display column names */ + for (i=0; i= collen[i]) + build_indicator_message(errmsg, + (SQLPOINTER *)&data[i], collen[i], + &outlen[i], i); + (void)printf("%*.*s ", outlen[i], outlen[i], + data[i]); + } /* for all columns in this row */ + /* print any truncation messages */ + (void)printf("\n%s", errmsg); + } + } /* while rows to fetch */ + SQLClose(hstmt); + break; + + /* searched DELETE, INSERT or searched UPDATE statement */ + case SQL_DELETE_WHERE: + case SQL_INSERT: + case SQL_UPDATE_WHERE: + /* check rowcount */ + SQLGetDiagField(SQL_HANDLE_STMT, hstmt, 0, + SQL_DIAG_ROW_COUNT, (SQLPOINTER)&rowcount, 0, + (SQLSMALLINT *)NULL); + if (SQLEndTran(SQL_HANDLE_ENV, henv, SQL_COMMIT) + == SQL_SUCCESS) { + (void) printf("Operation successful\n"); + } + else { + (void) printf("Operation failed\n"); + } + (void)printf("%ld rows affected\n", rowcount); + break; + + /* other statements */ + case SQL_ALTER_TABLE: + case SQL_CREATE_TABLE: + case SQL_CREATE_VIEW: + case SQL_DROP_TABLE: + case SQL_DROP_VIEW: + case SQL_DYNAMIC_DELETE_CURSOR: + case SQL_DYNAMIC_UPDATE_CURSOR: + case SQL_GRANT: + case SQL_REVOKE: + if (SQLEndTran(SQL_HANDLE_ENV, henv, SQL_COMMIT) + == SQL_SUCCESS) { + (void) printf("Operation successful\n"); + } + else { + (void) printf("Operation failed\n"); + } + break; + + /* implementation-defined statement */ + default: + (void)printf("Statement type=%ld\n", stmttype); + break; + } + + /* free data buffers */ + for (i=0; i