/* basic.c - simple test of ODBC 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 #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 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) { /* * This code snagged directly from MSDN ODBC API Reference */ #define TAB_LEN SQL_MAX_TABLE_NAME_LEN + 1 #define COL_LEN SQL_MAX_COLUMN_NAME_LEN + 1 UCHAR szTable[TAB_LEN]; /* Table to display */ UCHAR szPkTable[TAB_LEN]; /* Primary key table name */ UCHAR szFkTable[TAB_LEN]; /* Foreign key table name */ UCHAR szPkCol[COL_LEN]; /* Primary key column */ UCHAR szFkCol[COL_LEN]; /* Foreign key column */ SQLINTEGER cbPkTable, cbPkCol, cbFkTable, cbFkCol, cbKeySeq; SQLSMALLINT iKeySeq; SQLRETURN rc; /* Bind the columns that describe the primary and foreign keys. */ /* Ignore the table schema, name, and catalog for this example. */ rc = SQLBindCol(hstmt, 3, SQL_C_CHAR, szPkTable, TAB_LEN, &cbPkTable); if (RC_ERR(rc)) stmt_die(rc); rc = SQLBindCol(hstmt, 4, SQL_C_CHAR, szPkCol, COL_LEN, &cbPkCol); if (RC_ERR(rc)) stmt_die(rc); rc = SQLBindCol(hstmt, 5, SQL_C_SSHORT, &iKeySeq, TAB_LEN, &cbKeySeq); if (RC_ERR(rc)) stmt_die(rc); rc = SQLBindCol(hstmt, 7, SQL_C_CHAR, szFkTable, TAB_LEN, &cbFkTable); if (RC_ERR(rc)) stmt_die(rc); rc = SQLBindCol(hstmt, 8, SQL_C_CHAR, szFkCol, COL_LEN, &cbFkCol); if (RC_ERR(rc)) stmt_die(rc); strcpy(szTable, "b00"); /* Get the names of the columns in the primary key. */ rc = SQLPrimaryKeys(hstmt, NULL, 0, /* Catalog name */ NULL, 0, /* Schema name */ szTable, SQL_NTS); /* Table name */ if (RC_ERR(rc)) stmt_die(rc); while ((rc == SQL_SUCCESS) || (rc == SQL_SUCCESS_WITH_INFO)) { /* Fetch and display the result set. This will be a list of the */ /* columns in the primary key of szTable. */ rc = SQLFetch(hstmt); if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO) fprintf(stdout, "Table: %s Column: %s Key Seq: %hd \n", szPkTable, szPkCol, iKeySeq); } if (rc != SQL_NO_DATA) stmt_die(rc); /* Close the cursor (the hstmt is still allocated). */ SQLFreeStmt(hstmt, SQL_CLOSE); /* Get all the foreign keys that refer to szTable's primary key.*/ rc = SQLForeignKeys(hstmt, NULL, 0, /* Primary catalog */ NULL, 0, /* Primary schema */ szTable, SQL_NTS, /* Primary table */ NULL, 0, /* Foreign catalog */ NULL, 0, /* Foreign schema */ NULL, 0); /* Foreign table */ while ((rc == SQL_SUCCESS) || (rc == SQL_SUCCESS_WITH_INFO)) { /* Fetch and display the result set. This will be all of the */ /* foreign keys in other tables that refer to szTable's */ /* primary key. */ rc = SQLFetch(hstmt); if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO) fprintf(stdout, "%-s ( %-s ) <-- %-s ( %-s )\n", szPkTable, szPkCol, szFkTable, szFkCol); } if (rc != SQL_NO_DATA) stmt_die(rc); /* Close the cursor (the hstmt is still allocated). */ SQLFreeStmt(hstmt, SQL_CLOSE); /* Get all the foreign keys in szTable. */ rc = SQLForeignKeys(hstmt, NULL, 0, /* Primary catalog */ NULL, 0, /* Primary schema */ NULL, 0, /* Primary table */ NULL, 0, /* Foreign catalog */ NULL, 0, /* Foreign schema */ szTable, SQL_NTS); /* Foreign table */ while ((rc == SQL_SUCCESS) || (rc == SQL_SUCCESS_WITH_INFO)) { /* Fetch and display the result set. This will be all of the */ /* primary keys in other tables that are referred to by foreign */ /* keys in szTable. */ rc = SQLFetch(hstmt); if (rc == SQL_SUCCESS_WITH_INFO) fprintf(stdout, "%-s ( %-s )--> %-s ( %-s )\n", szFkTable, szFkCol, szPkTable, szPkCol); } if (rc != SQL_NO_DATA) 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 < 0) 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); }