/* basic.c - simple test of ODBC This file is in the public domain. */ #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 #define MAX_COLUMNS 40 #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 global henv and exit dbc_die Print diagnostic info from global hdbc and exit stmt_die Print diagnostic info from global hstmt and exit env_warn, dbc_warn, stmt_warn Print diagnostic 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) >= 0 && (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, ncol, data_type, decimal_digits, nullable; #if 0 rc = SQLGetTypeInfo(hstmt, SQL_ALL_TYPES); #else rc = SQLGetTypeInfo(hstmt, SQL_TIMESTAMP); #endif if (RC_ERR(rc)) stmt_die(rc); rc = SQLNumResultCols(hstmt, &ncol); if (RC_ERR(rc)) stmt_die(rc); /* Print the column names and bind columns to result buffers */ for(i = 0; i < ncol; ++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 = SQLFetch(hstmt); while (RC_CONTINUE(rc)) { ++num_rows; for (i = 0; i < ncol; ++i) { printf("%*s ", widths[i], rows[i]); } printf("\n"); rc = SQLFetch(hstmt); } printf("\nrows fetched:%lu\n", num_rows); /* 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); #ifdef VERBOSE printf("connecting (DSN='%s', USER='%s')\n", dsn, user ? user : ""); #endif 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); }