/* fetchscroll.c - test SQLFetchScroll This file is in the public domain. */ #include #include #include #include #include #include /* You might want to change these. You can also call the program as: progname [DSN [USER [PASSWORD]]] to override these values. For example, call it as: progname myodbc3 -- use the myodbc3 dsn progname test abc -- use the test dsn, with user abc progname test abc 0U8I2 -- ditto, with password */ static char *dsn = "test"; static char *user = NULL; /* Use DSN default */ static char *pass = NULL; /* Use DSN default */ /* Quick hack to provide buffers for query results - adjust as needed */ #define MAX_NAME_LEN 64 /* for column names */ #define MAX_COLUMNS 16 #define MAX_ROW_DATA_LEN 1024 /* Max length for ODBC diagnostic messages */ #ifdef SQL_MAX_MESSAGE_LENGTH # define MAX_MESSAGE_LENGTH SQL_MAX_MESSAGE_LENGTH #else # define MAX_MESSAGE_LENGTH 1024 #endif /* Thes are initialized in db_connect() */ static SQLHENV henv; static SQLHDBC hdbc; static SQLHSTMT hstmt; /* Convenience macros for error checking: env_die Print diagnostic info from an HENV and exit dbc_die Print diagnostic info from an HDBC and exit stmt_die Print diagnostic info from an HSTMT and exit env_warn, dbc_warn, stmt_warn print the info, but do not exit */ #define env_die(rc) \ _error((rc), SQL_HANDLE_ENV, (henv), 1, __FILE__, __LINE__) #define env_warn(rc) \ _error((rc), SQL_HANDLE_ENV, (henv), 0, __FILE__, __LINE__) #define dbc_die(rc) \ _error((rc), SQL_HANDLE_DBC, (hdbc), 1, __FILE__, __LINE__) #define dbc_warn(rc) \ _error((rc), SQL_HANDLE_DBC, (hdbc), 0, __FILE__, __LINE__) #define stmt_die(rc) \ _error((rc), SQL_HANDLE_STMT, (hstmt), 1, __FILE__, __LINE__) #define stmt_warn(rc) \ _error((rc), SQL_HANDLE_STMT, (hstmt), 0, __FILE__, __LINE__) static void _error(SQLRETURN rc, SQLSMALLINT htype, SQLHANDLE handle, int is_fatal, const char *file, const unsigned int line); #if 0 /* Warning: RC_ERR evaluates its argument twice. So don't use it with an expression, or the expression will be evaluated twice! Here's an example of what *not* to do: if (RC_ERR(SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT))) -- WRONG! fprintf(stderr, "Oops, I just ran that COMMIT twice!\n"); */ #define RC_ERR(rc) ((rc) != SQL_SUCCESS && (rc) != SQL_SUCCESS_WITH_INFO) #else #define RC_ERR(rc) ((rc) < 0) #endif #define RC_CONTINUE(rc) (!RC_ERR((rc)) && (rc) != SQL_NO_DATA) /* Some basic tests */ void db_tests(void) { SQLRETURN rc; SQLUINTEGER num_rows = 0; SQLCHAR column_name[MAX_NAME_LEN]; int widths[MAX_COLUMNS]; SQLCHAR rows[MAX_COLUMNS][MAX_ROW_DATA_LEN]; SQLSMALLINT i, ncols, data_type, decimal_digits, nullable; /* Create the table (dropping it first, if it exists) */ rc = SQLExecDirect(hstmt, "DROP TABLE if exists vendor", SQL_NTS); if (RC_ERR(rc)) stmt_die(rc); rc = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT); if (RC_ERR(rc)) dbc_die(rc); rc = SQLExecDirect(hstmt, "CREATE TABLE vendor (" "recnum int not null primary key auto_increment," "id int not null," "name char(30)," "u_name char(30)," "address char(30)," "city char(14)," "state char(2)," "zip char(10)," "phone_number char(20)," "fax_number char(20)," "key (u_name)" ")", SQL_NTS); if (RC_ERR(rc)) stmt_die(rc); rc = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT); if (RC_ERR(rc)) dbc_die(rc); /* Insert some data */ rc = SQLExecDirect(hstmt, "INSERT INTO vendor VALUES (1, 1, 'Books-R-Us', 'BOOKS-R-US', '456 N.W. 156th Ave.', 'Miami', 'FL', '44444', '(305) 222-7532', '')", SQL_NTS); if (RC_ERR(rc)) stmt_die(rc); rc = SQLExecDirect(hstmt, "INSERT INTO vendor VALUES (2, 2, 'Book World', 'BOOK WORLD', '6501 S.E. 25th Ave.', 'Coral Gables', 'FL', '44427', '(305) 445-9723', '')", SQL_NTS); if (RC_ERR(rc)) stmt_die(rc); rc = SQLExecDirect(hstmt, "INSERT INTO vendor VALUES (3, 3, 'The Book Emporium', 'THE BOOK EMPORIUM', '145 N.E. 56th AVE.', 'Coral Springs', 'FL', '67823', '(407) 223-5678', '')", SQL_NTS); if (RC_ERR(rc)) stmt_die(rc); rc = SQLExecDirect(hstmt, "INSERT INTO vendor VALUES (4, 4, 'Technical Book Supply', 'TECHNICAL BOOK SUPPLY', '14 E. Main Street', 'San Diego', 'CA', '91941', '(800) TECHBOOK', '')", SQL_NTS); if (RC_ERR(rc)) stmt_die(rc); rc = SQLExecDirect(hstmt, "INSERT INTO vendor VALUES (5, 5, 'Construction Resource', 'CONSTRUCTION RESOURCE', 'P.O. Box 1792', 'Denver', 'CO', '92345', '(800) 545-7236', '')", SQL_NTS); if (RC_ERR(rc)) stmt_die(rc); rc = SQLExecDirect(hstmt, "INSERT INTO vendor VALUES (6, 6, 'Ace Publications', 'ACE PUBLICATIONS', '4514 Lombard St - Suite 123', 'San Fransisco', 'CA', '92014', '(415) 456-8765 x.888', '')", SQL_NTS); if (RC_ERR(rc)) stmt_die(rc); rc = SQLExecDirect(hstmt, "INSERT INTO vendor VALUES (7, 7, 'Software Publishing', 'SOFTWARE PUBLISHING', 'P.O. Box 123', 'Bostn', 'MA', '91234', '(800) 567-9875', '')", SQL_NTS); if (RC_ERR(rc)) stmt_die(rc); rc = SQLExecDirect(hstmt, "INSERT INTO vendor VALUES (8, 8, 'Paperback Merchant', 'PAPERBACK MERCHANT', '2734 Center Blvd.', 'Fort Worth', 'TX', '87632', '(404) 879-9999', '')", SQL_NTS); if (RC_ERR(rc)) stmt_die(rc); rc = SQLExecDirect(hstmt, "INSERT INTO vendor VALUES (9, 9, 'Business Work Books', 'BUSINESS WORK BOOKS', '9182A A. Street', 'San Diego', 'CA', '91920', '(619) 555-1231', '')", SQL_NTS); if (RC_ERR(rc)) stmt_die(rc); rc = SQLExecDirect(hstmt, "INSERT INTO vendor VALUES (10, 10, 'Olde Book Merchant', 'OLDE BOOK MERCHANT', '1371 Market Street', 'San Fransisco', 'CA', '92314', '(415) 555-9823', '')", SQL_NTS); if (RC_ERR(rc)) stmt_die(rc); rc = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT); if (RC_ERR(rc)) dbc_die(rc); /* Select some rows */ rc = SQLFreeStmt(hstmt, SQL_CLOSE); if (RC_ERR(rc)) stmt_die(rc); rc = SQLSetStmtAttr (hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_DYNAMIC, SQL_IS_UINTEGER); if (RC_ERR(rc)) stmt_die(rc); rc = SQLSetStmtAttr (hstmt, SQL_ATTR_CURSOR_SCROLLABLE, (SQLPOINTER)SQL_SCROLLABLE, SQL_IS_UINTEGER); if (RC_ERR(rc)) stmt_die(rc); rc = SQLPrepare (hstmt, "select * from vendor where id > 3 order by id", SQL_NTS); if (RC_ERR(rc)) stmt_die(rc); rc = SQLExecute (hstmt); if (RC_ERR(rc)) stmt_die(rc); rc = SQLNumResultCols(hstmt, &ncols); if (RC_ERR(rc)) stmt_die(rc); /* Print the column names and bind columns to result buffers */ for(i = 0; i < ncols; ++i) { rc = SQLDescribeCol(hstmt, i + 1, column_name, MAX_NAME_LEN + 1, NULL, &data_type, (SQLUINTEGER*)&widths[i], &decimal_digits, &nullable); if (RC_ERR(rc)) stmt_die(rc); printf("%*s ", widths[i], column_name); rc = SQLBindCol(hstmt, i + 1, SQL_C_CHAR, rows[i], MAX_ROW_DATA_LEN + 1, NULL); if (RC_ERR(rc)) stmt_die(rc); } printf("\n--\n"); /* Fetch and print each row */ rc = SQLFetchScroll (hstmt, SQL_FETCH_NEXT, 0); while (RC_CONTINUE(rc)) { ++num_rows; for (i = 0; i < ncols; ++i) printf("%*s ", widths[i], rows[i]); printf("\n"); rc = SQLFetchScroll (hstmt, SQL_FETCH_NEXT, 0); } printf("\nrows fetched:%lu\n", num_rows); if (RC_ERR(rc)) dbc_die(rc); /* Free the statement row bind resources */ rc = SQLFreeStmt(hstmt, SQL_UNBIND); if (RC_ERR(rc)) stmt_die(rc); /* Free the statement cursor */ rc = SQLFreeStmt(hstmt, SQL_CLOSE); if (RC_ERR(rc)) stmt_die(rc); } void db_connect(void); void db_disconnect(void); int main(int argc, char **argv) { if (--argc > 0) dsn = *++argv; if (--argc > 0) user = *++argv; if (--argc > 0) pass = *++argv; db_connect(); db_tests(); db_disconnect(); exit(EXIT_SUCCESS); } /* _error() should be called via the convenience macros. If there is any diagnostic info, print it. If is_fatal is true, exit if rc == SQL_ERROR. */ static void _error(SQLRETURN rc, SQLSMALLINT htype, SQLHANDLE handle, int is_fatal, const char *file, const unsigned int line) { SQLCHAR sql_state[6], message[MAX_MESSAGE_LENGTH + 1]; SQLINTEGER native_error; SQLSMALLINT dummy; SQLRETURN new_rc; switch (rc) { case SQL_SUCCESS: fprintf(stderr, "\nSuccess\n"); break; case SQL_ERROR: case SQL_SUCCESS_WITH_INFO: case SQL_NO_DATA: default: fprintf(stderr, "\nError at line %s:%u: ", file, line); new_rc = SQLGetDiagRec(htype, handle, 1, sql_state, &native_error, message, SQL_MAX_MESSAGE_LENGTH, &dummy); if(new_rc == SQL_SUCCESS || new_rc == SQL_SUCCESS_WITH_INFO) fprintf(stderr, "[%s:%ld] %s\n", sql_state, native_error, message); else fprintf(stderr, "Can't get error message (SQLGetDiagRec " "returned rc[%d]; original rc[%d])\n", new_rc, rc); break; } if (is_fatal && rc == SQL_ERROR) exit(EXIT_FAILURE); } /* db_connect() initializes the global henv, hdbc and hstmt variables. It uses the global dsn, user, and pass variables. */ void db_connect(void) { SQLRETURN rc; rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); if (RC_ERR(rc)) env_die(rc); rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0); if (RC_ERR(rc)) env_die(rc); printf("connecting (DSN='%s', USER='%s')\n", dsn, user ? user : ""); rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); if (RC_ERR(rc)) env_die(rc); rc = SQLConnect(hdbc, dsn, SQL_NTS, user, SQL_NTS, pass, SQL_NTS); if (RC_ERR(rc)) dbc_die(rc); rc = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_ON, 0); if (RC_ERR(rc)) dbc_die(rc); rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); if (RC_ERR(rc)) dbc_die(rc); } /* db_disconnect() releases all the resources acquired by db_connect() */ void db_disconnect(void) { SQLRETURN rc; rc = SQLFreeStmt(hstmt, SQL_DROP); if (RC_ERR(rc)) dbc_die(rc); rc = SQLDisconnect(hdbc); if (RC_ERR(rc)) dbc_die(rc); rc = SQLFreeConnect(hdbc); if (RC_ERR(rc)) dbc_die(rc); rc = SQLFreeEnv(henv); if (RC_ERR(rc)) env_die(rc); }