|
| 1 | +/* -*- C -*- */ |
| 2 | +/* The first example illustrates creating a table, adding some data |
| 3 | + * to it, and selecting the inserted data. The second example shows |
| 4 | + * interactive ad hoc query processing. |
| 5 | + * |
| 6 | + * Actual applications include more complete error checking following |
| 7 | + * calls to SQL/CLI routines. That material is omitted from this |
| 8 | + * Appendix for the sake of clarity. |
| 9 | + * |
| 10 | + * This file is adapted for PostgreSQL |
| 11 | + * from the CLI Annex in the SQL98 August 1994 draft standard. |
| 12 | + * Thomas G. Lockhart 1999-06-16 |
| 13 | + */ |
| 14 | + |
| 15 | +/* |
| 16 | + * B.1 Create table, insert, select |
| 17 | + * |
| 18 | + * This example function creates a table, inserts data into the table, |
| 19 | + * and selects the inserted data. |
| 20 | + * |
| 21 | + * This example illustrates the execution of SQL statement text |
| 22 | + * both using the Prepare() and Execute() method and using the |
| 23 | + * ExecDirect() method. The example also illustrates both the case |
| 24 | + * where the application uses the automatically-generated descriptors |
| 25 | + * and the case where the application allocates a descriptor of its |
| 26 | + * own and associates this descriptor with the SQL statement. |
| 27 | + * |
| 28 | + * Code comments include the equivalent statements in embedded SQL |
| 29 | + * to show how embedded SQL operations correspond to SQL/CLI function |
| 30 | + * calls. |
| 31 | + */ |
| 32 | + |
| 33 | +#include<sqlcli.h> |
| 34 | +#include<string.h> |
| 35 | + |
| 36 | +#ifndefNULL |
| 37 | +#defineNULL 0 |
| 38 | +#endif |
| 39 | + |
| 40 | +intprint_err(SQLSMALLINThandletype,SQLINTEGERhandle); |
| 41 | + |
| 42 | +intexample1(SQLCHAR*server,SQLCHAR*uid,SQLCHAR*authen) |
| 43 | +{ |
| 44 | +SQLHENVhenv; |
| 45 | +SQLHDBChdbc; |
| 46 | +SQLHDESChdesc; |
| 47 | +SQLHDESChdesc1; |
| 48 | +SQLHDESChdesc2; |
| 49 | +SQLHSTMThstmt; |
| 50 | +SQLINTEGERid; |
| 51 | +SQLSMALLINTidind; |
| 52 | +SQLCHARname[51]; |
| 53 | +SQLINTEGERnamelen; |
| 54 | +SQLSMALLINTnameind; |
| 55 | + |
| 56 | +/* EXEC SQL CONNECT TO :server USER :uid; */ |
| 57 | + |
| 58 | +/* allocate an environment handle */ |
| 59 | +SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv); |
| 60 | +/* allocate a connection handle */ |
| 61 | +SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc); |
| 62 | + |
| 63 | +/* connect to database */ |
| 64 | +if (SQLConnect(hdbc,server,SQL_NTS,uid,SQL_NTS, |
| 65 | +authen,SQL_NTS) |
| 66 | +!=SQL_SUCCESS) |
| 67 | +return(print_err(SQL_HANDLE_DBC,hdbc)); |
| 68 | + |
| 69 | +/* allocate a statement handle */ |
| 70 | +SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt); |
| 71 | + |
| 72 | +/* EXEC SQL CREATE TABLE NAMEID (ID integer, NAME varchar(50)); */ |
| 73 | + { |
| 74 | +SQLCHARcreate[]="CREATE TABLE NAMEID (ID integer," |
| 75 | +" NAME varchar(50))"; |
| 76 | + |
| 77 | +/* execute the CREATE TABLE statement */ |
| 78 | +if (SQLExecDirect(hstmt,create,SQL_NTS)!=SQL_SUCCESS) |
| 79 | +return(print_err(SQL_HANDLE_STMT,hstmt)); |
| 80 | + } |
| 81 | + |
| 82 | +/* EXEC SQL COMMIT WORK; */ |
| 83 | +/* commit CREATE TABLE */ |
| 84 | +SQLEndTran(SQL_HANDLE_ENV,henv,SQL_COMMIT); |
| 85 | +/* EXEC SQL INSERT INTO NAMEID VALUES ( :id, :name ); */ |
| 86 | + { |
| 87 | +SQLCHARinsert[]="INSERT INTO NAMEID VALUES (?, ?)"; |
| 88 | + |
| 89 | +/* show the use of SQLPrepare/SQLExecute method */ |
| 90 | +/* prepare the INSERT */ |
| 91 | +if (SQLPrepare(hstmt,insert,SQL_NTS)!=SQL_SUCCESS) |
| 92 | +return(print_err(SQL_HANDLE_STMT,hstmt)); |
| 93 | +/* application parameter descriptor */ |
| 94 | +SQLGetStmtAttr(hstmt,SQL_ATTR_APP_PARAM_ |
| 95 | +DESC,&hdesc1,0L, |
| 96 | + (SQLINTEGER*)NULL); |
| 97 | +SQLSetDescRec(hdesc1,1,SQL_INTEGER,0,0L,0,0, |
| 98 | + (SQLPOINTER)&id, (SQLINTEGER*)NULL, (SQLSMALLINT*)NULL); |
| 99 | +SQLSetDescRec(hdesc1,2,SQL_CHAR,0,0L,0,0, |
| 100 | + (SQLPOINTER)name, (SQLINTEGER*)NULL, |
| 101 | + (SQLSMALLINT*)NULL); |
| 102 | +/* implementation parameter descriptor */ |
| 103 | +SQLGetStmtAttr(hstmt,SQL_ATTR_IMP_PARAM_ |
| 104 | +DESC,&hdesc2,0L, |
| 105 | + (SQLINTEGER*)NULL); |
| 106 | +SQLSetDescRec(hdesc2,1,SQL_INTEGER,0,0L,0,0, |
| 107 | + (SQLPOINTER)NULL, (SQLINTEGER*)NULL, |
| 108 | + (SQLSMALLINT*)NULL); |
| 109 | +SQLSetDescRec(hdesc2,2,SQL_VARCHAR,0,50L,0,0, |
| 110 | + (SQLPOINTER)NULL, (SQLINTEGER*)NULL, |
| 111 | + (SQLSMALLINT*)NULL); |
| 112 | + |
| 113 | +/* assign parameter values and execute the INSERT */ |
| 114 | +id=500; |
| 115 | + (void)strcpy(name,"Babbage"); |
| 116 | +if (SQLExecute(hstmt)!=SQL_SUCCESS) |
| 117 | +return(print_err(SQL_HANDLE_STMT,hstmt)); |
| 118 | + } |
| 119 | +/* EXEC SQL COMMIT WORK; */ |
| 120 | +SQLEndTran(SQL_HANDLE_ENV,henv,SQL_COMMIT); |
| 121 | +/* commit inserts */ |
| 122 | + |
| 123 | +/* EXEC SQL DECLARE c1 CURSOR FOR SELECT ID, NAME FROM NAMEID; */ |
| 124 | +/* EXEC SQL OPEN c1; */ |
| 125 | +/* The application doesn't specify "declare c1 cursor for" */ |
| 126 | + { |
| 127 | +SQLCHARselect[]="select ID, NAME from NAMEID"; |
| 128 | +if (SQLExecDirect(hstmt,select,SQL_NTS)!=SQL_SUCCESS) |
| 129 | +return(print_err(SQL_HANDLE_STMT,hstmt)); |
| 130 | + } |
| 131 | + |
| 132 | +/* EXEC SQL FETCH c1 INTO :id, :name; */ |
| 133 | +/* this time, explicitly allocate an application row descriptor */ |
| 134 | +SQLAllocHandle(SQL_HANDLE_DESC,hdbc,&hdesc); |
| 135 | +SQLSetDescRec(hdesc,1,SQL_INTEGER,0,0L,0,0, |
| 136 | +(SQLPOINTER)&id, (SQLINTEGER*)NULL, (SQLSMALLINT*)&idind); |
| 137 | + |
| 138 | +SQLSetDescRec(hdesc,2,SQL_ |
| 139 | +CHAR,0, (SQLINTEGER)sizeof(name), |
| 140 | +0,0, (SQLPOINTER)&name, (SQLINTEGER*)&namelen, |
| 141 | +(SQLSMALLINT*)&nameind); |
| 142 | +/* associate descriptor with statement handle */ |
| 143 | +SQLSetStmtAttr(hstmt,SQL_ATTR_APP_ROW_DESC,&hdesc,0); |
| 144 | +/* execute the fetch */ |
| 145 | +SQLFetch(hstmt); |
| 146 | + |
| 147 | +/* EXEC SQL COMMIT WORK; */ |
| 148 | +/* commit the transaction */ |
| 149 | +SQLEndTran(SQL_HANDLE_ENV,henv,SQL_COMMIT); |
| 150 | + |
| 151 | +/* EXEC SQL CLOSE c1; */ |
| 152 | +SQLClose(hstmt); |
| 153 | +/* free the statement handle */ |
| 154 | +SQLFreeHandle(SQL_HANDLE_STMT,hstmt); |
| 155 | + |
| 156 | +/* EXEC SQL DISCONNECT; */ |
| 157 | +/* disconnect from the database */ |
| 158 | +SQLDisconnect(hdbc); |
| 159 | +/* free descriptor handle */ |
| 160 | +SQLFreeHandle(SQL_HANDLE_DESC,hdesc); |
| 161 | +/* free descriptor handle */ |
| 162 | +SQLFreeHandle(SQL_HANDLE_DESC,hdesc1); |
| 163 | +/* free descriptor handle */ |
| 164 | +SQLFreeHandle(SQL_HANDLE_DESC,hdesc2); |
| 165 | +/* free connection handle */ |
| 166 | +SQLFreeHandle(SQL_HANDLE_DBC,hdbc); |
| 167 | +/* free environment handle */ |
| 168 | +SQLFreeHandle(SQL_HANDLE_ENV,henv); |
| 169 | + |
| 170 | +return(0); |
| 171 | +} |
