/* Test ODBC application The following lines are used to construct the test database: drop database if exists cfmctest; create database cfmctest; use cfmctest; drop table if exists mt3595; create table mt3595 ( field1 char(4) not null, field2 char(50), primary key(field1) ); insert into mt3595 values ('0001','aaaaaaaaaaaaaaaaaaa'), ('0002', 'bbbbbbbbbbbbbbbbbbbbbb'), ('0003', 'ccccccccccccccccccccc'), ('0004', 'dddddddddddddddddddd'), ('0005', 'eeeeeeeeeeeeeeeeeeeeee'), ('0006', 'fffffffffffffffffffffff'), ('0007', 'ggggggggggggggggggggg'), ('0008', 'hhhhhhhhhhhhhhhhhhhhhhhh'), ('0009', 'iiiiiiiiiiiiiiiiiiiii'), ('0010', 'jjjjjjjjjjjjjjjjjjjjjjjj'); This command is used to compile this application: cc -lodbc -o tst odbctest.c When run you will notice that after the delete and also after the update instead of getting the next record that one is skipped. 3jul07 - Put together an application to test ODBC functions (jcd) */ #define USING_ODBC_VERSION SQL_OV_ODBC3 #define NUM_COLS_ALLOW 10 #define MAX_LEN_OF_DATA 1000 #define PRECISION_DEFAULT 15 #define SCALE_DEFAULT 2 #include #include #include #include /* Modern SQL headers seem to use SQLLEN and SQLULEN. Older ones not */ #ifndef SQLLEN #define SQLLEN SQLINTEGER #endif /* SQLLEN */ #ifndef SQLULEN #define SQLULEN SQLUINTEGER #endif /* SQLULEN */ #define RET_TYPE_NOTDO 0 #define RET_TYPE_CHAR 1 #define RET_TYPE_DOUBLE 2 #define RET_TYPE_NUM 3 #define RET_TYPE_TIMESTAMP 4 #define RET_TYPE_TEXT 5 #define RET_TYPE_DATEONLY 6 #define RET_TYPE_TIMEONLY 7 typedef struct { int dbsize; /* number of bytes in field */ int dbtype; /* type of data in database */ char display_dbtype[30]; /* type of data */ char name[SQL_MAX_COLUMN_NAME_LEN + 1]; /* name of column */ int name_len; /* length of name */ int dsize; /* display width */ int precision; /* number of digit */ int scale; /* how many are decimal */ int case_sensitive; /* case matters in data */ int nullok; /* okay to have NO data */ int updateable; /* is okay to update data */ SQLLEN ret_wid; /* fetch info for data */ TIMESTAMP_STRUCT ret_timestamp; /* timestamp data */ DATE_STRUCT ret_date; /* date data */ TIME_STRUCT ret_timeonly; /* time data */ int fetch_type; /* type data retrieved */ SQLINTEGER fetch_len; /* max data retrieved */ int data_is_null; /* is column empty */ int truncate_text; /* is text maybe truncated */ int did_truncate; /* truncated the text */ int our_type; /* our data type */ int use_precision; /* our width of number */ int use_scale; /* number of decimals we use */ char odbc_data[MAX_LEN_OF_DATA+1]; /* where store data */ } ODBC_RDBMS_ITEM; static int odbc_my_error_info(SQLSMALLINT, SQLHANDLE, SQLCHAR*); static int odbc_show_error_info(SQLSMALLINT, SQLHANDLE); /*----------------------------------------------------------------------- -----------------------------------------------------------------------*/ static int odbc_show_error_info (SQLSMALLINT type, SQLHANDLE handle) { SQLRETURN rc; SQLINTEGER recs_avail, NativeError; SQLSMALLINT i, sr; SQLCHAR SqlState[10], MessageText[SQL_MAX_MESSAGE_LENGTH + 1]; rc = SQLGetDiagField(type, handle, 0, SQL_DIAG_NUMBER, &recs_avail, sizeof(SQLINTEGER), &sr); if (rc != SQL_SUCCESS) { printf("odbc_show_error_info() got error %d finding count\n", rc); return (1); } printf("There are %d error records available\n", recs_avail); for (i = 1; i <= recs_avail; i++) { rc = SQLGetDiagRec(type, handle, i, SqlState, &NativeError, MessageText, sizeof(MessageText), &sr); if (rc != SQL_SUCCESS) { printf("odbc_show_error_info() got error %d getting %d\n", rc, i); return (1); } SqlState[5] = 0; MessageText[sr] = 0; printf("Err: %d (%s)=\"%s\"\n", NativeError, SqlState, MessageText); } if ((recs_avail == 1) && (NativeError == 106)) return (1); else return (0); } /* odbc_show_error_info */ /*----------------------------------------------------------------------- -----------------------------------------------------------------------*/ static int odbc_my_error_info (SQLSMALLINT type, SQLHANDLE handle, SQLCHAR *TestState) { SQLRETURN rc; SQLINTEGER recs_avail, NativeError; SQLSMALLINT i, sr; SQLCHAR SqlState[10], MessageText[SQL_MAX_MESSAGE_LENGTH + 1]; rc = SQLGetDiagField(type, handle, 0, SQL_DIAG_NUMBER, &recs_avail, sizeof(SQLINTEGER), &sr); if (rc != SQL_SUCCESS) { printf("odbc_my_error_info() got error %d finding count\n", rc); return (1); } for (i = 1; i <= recs_avail; i++) { rc = SQLGetDiagRec(type, handle, i, SqlState, &NativeError, MessageText, sizeof(MessageText), &sr); if (rc != SQL_SUCCESS) { printf("odbc_my_error_info() got error %d getting %d\n", rc, i); return (1); } SqlState[5] = 0; MessageText[sr] = 0; } if (recs_avail != 1) return (1); if (strcmp((char*)SqlState, (char*)TestState)) return (1); else return (0); } /* odbc_my_error_info */ /*----------------------------------------------------------------------- -----------------------------------------------------------------------*/ int main() { SQLUSMALLINT i; int j, max_items, rtn = 1, loc_type, bad, max_width = 0; char *bp, **ptrs, *op, DisplaySQLType[30]; ODBC_RDBMS_ITEM items[NUM_COLS_ALLOW]; SQLRETURN rc; SQLSMALLINT NumParams; SQLCHAR buffer[255]; SQLSMALLINT InfoSize; char *up, *cp; int pos; SQLSMALLINT fetch_spot; /* Data from SQLDescribeCol */ SQLCHAR ColumnName[255]; SQLSMALLINT ColumnNameSize; SQLSMALLINT ColumnSQLDataType; SQLULEN ColumnSize; SQLSMALLINT ColumnDecimals; SQLSMALLINT ColumnNullable; /* Data from SQLColAttribute */ SQLINTEGER NumColumns; SQLINTEGER ColumnCaseSensitive; SQLINTEGER ColumnDisplaySize; SQLINTEGER ColumnUpdateable; SQLUINTEGER NumRowsFetched; /* Info about fetched data */ SQLUSMALLINT RowStatusArray; /* Info about fetched data */ SQLHDBC DBCHandle; /* connection information */ SQLHENV EnvHandle; /* envirnment information */ SQLHSTMT StmtHandle; /* information about a cursor */ SQLUINTEGER ArraySize = 1; /* how many 'rows' to fetch */ /* Allocate a Environment Handle */ rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &EnvHandle); if (rc != SQL_SUCCESS) { printf("Trying to allocate environment failed %d\n", rc); exit (1); } /* Set an environment attribute */ rc = SQLSetEnvAttr(EnvHandle, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)USING_ODBC_VERSION, SQL_IS_UINTEGER); if (rc != SQL_SUCCESS) { printf("Trying to set env attr failed %d\n", rc); odbc_show_error_info(SQL_HANDLE_ENV, EnvHandle); rc = SQLFreeHandle(SQL_HANDLE_ENV, EnvHandle); exit (1); } /* Allocate a Connection Handle */ rc = SQLAllocHandle(SQL_HANDLE_DBC, EnvHandle, &DBCHandle); if (rc != SQL_SUCCESS) { printf("Getting Connection Handle failed %d\n", rc); odbc_show_error_info(SQL_HANDLE_ENV, EnvHandle); rc = SQLFreeHandle(SQL_HANDLE_ENV, EnvHandle); exit (1); } /* Connect to the specified database */ rc = SQLConnect(DBCHandle, (SQLCHAR*)"cfmctest", SQL_NTS, (SQLCHAR*)NULL, SQL_NTS, (SQLCHAR*)NULL, SQL_NTS); if (rc == SQL_SUCCESS_WITH_INFO) { printf("SQLConnect gets warning\n"); odbc_show_error_info(SQL_HANDLE_DBC, DBCHandle); rc = SQL_SUCCESS; } if (rc != SQL_SUCCESS) { printf("Connection failed %d\n", rc); odbc_show_error_info(SQL_HANDLE_DBC, DBCHandle); rc = SQLFreeHandle(SQL_HANDLE_DBC, DBCHandle); rc = SQLFreeHandle(SQL_HANDLE_ENV, EnvHandle); exit (1); } /* Allocate a statement handle*/ rc = SQLAllocHandle(SQL_HANDLE_STMT, DBCHandle, &StmtHandle); if (rc != SQL_SUCCESS) { printf("Getting Statement Handle failed %d\n", rc); odbc_show_error_info(SQL_HANDLE_DBC, DBCHandle); rc = SQLDisconnect(DBCHandle); if (rc != SQL_SUCCESS) { printf("Disconnect failed %d\n", rc); odbc_show_error_info(SQL_HANDLE_DBC, DBCHandle); } rc = SQLFreeHandle(SQL_HANDLE_DBC, DBCHandle); rc = SQLFreeHandle(SQL_HANDLE_ENV, EnvHandle); exit (1); } /* Set Statement Attributes */ if (rc == SQL_SUCCESS) { pos = 1; rc = SQLSetStmtAttr(StmtHandle, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_DYNAMIC, 0); if (rc == SQL_SUCCESS_WITH_INFO) { printf("Setting Cursor Type gets warning\n"); odbc_show_error_info(SQL_HANDLE_STMT, StmtHandle); rc = SQL_SUCCESS; } } if (rc == SQL_SUCCESS) { pos = 2; rc = SQLSetStmtAttr(StmtHandle, SQL_ATTR_CONCURRENCY, (SQLPOINTER)SQL_CONCUR_VALUES, 0); if (rc == SQL_SUCCESS_WITH_INFO) { printf("Setting concurrentcy gets warning\n"); odbc_show_error_info(SQL_HANDLE_STMT, StmtHandle); rc = SQL_SUCCESS; } } if (rc == SQL_SUCCESS) { pos = 3; rc = SQLSetStmtAttr(StmtHandle, SQL_ATTR_ROW_BIND_TYPE, SQL_BIND_BY_COLUMN, 0); if (rc == SQL_SUCCESS_WITH_INFO) { printf("Setting RowBindType gets warning\n"); odbc_show_error_info(SQL_HANDLE_STMT, StmtHandle); rc = SQL_SUCCESS; } } if (rc == SQL_SUCCESS) { pos = 4; rc = SQLSetStmtAttr(StmtHandle, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)ArraySize, 0); if (rc == SQL_SUCCESS_WITH_INFO) { printf("Setting RowArraySize gets warning\n"); odbc_show_error_info(SQL_HANDLE_STMT, StmtHandle); rc = SQL_SUCCESS; } } if (rc == SQL_SUCCESS) { pos = 5; rc = SQLSetStmtAttr(StmtHandle, SQL_ATTR_ROW_STATUS_PTR, &RowStatusArray, 0); if (rc == SQL_SUCCESS_WITH_INFO) { printf("Setting RowStatusPTR gets warning\n"); odbc_show_error_info(SQL_HANDLE_STMT, StmtHandle); rc = SQL_SUCCESS; } } if (rc == SQL_SUCCESS) { pos = 6; rc = SQLSetStmtAttr(StmtHandle, SQL_ATTR_ROWS_FETCHED_PTR, &NumRowsFetched, 0); if (rc == SQL_SUCCESS_WITH_INFO) { printf("Setting RowsFetchedPTR gets warning\n"); odbc_show_error_info(SQL_HANDLE_STMT, StmtHandle); rc = SQL_SUCCESS; } } if (rc != SQL_SUCCESS) { printf("SQLSetStmtAttr(%d) failed %d\n", pos, rc); odbc_show_error_info(SQL_HANDLE_STMT, StmtHandle); close_down: rc = SQLCloseCursor(StmtHandle); rc = SQLFreeHandle(SQL_HANDLE_STMT, StmtHandle); rc = SQLDisconnect(DBCHandle); if (rc != SQL_SUCCESS) { printf("Disconnect failed %d\n", rc); odbc_show_error_info(SQL_HANDLE_DBC, DBCHandle); } rc = SQLFreeHandle(SQL_HANDLE_DBC, DBCHandle); rc = SQLFreeHandle(SQL_HANDLE_ENV, EnvHandle); exit (1); } rc = SQLPrepare(StmtHandle, (SQLCHAR*)"select * from mt3595 where field1<'0030'", SQL_NTS); if (rc != SQL_SUCCESS) { printf("SQLPrepare() failed %d\n", rc); odbc_show_error_info(SQL_HANDLE_STMT, StmtHandle); exit (1); } rc = SQLNumParams(StmtHandle, &NumParams); if (rc != SQL_SUCCESS) { printf("SQLNumParams() failed %d\n", rc); odbc_show_error_info(SQL_HANDLE_STMT, StmtHandle); goto close_down; } if (NumParams) { printf("We do not support parameters in SQL, but %d found\n", NumParams); goto close_down; } rc = SQLColAttribute(StmtHandle, 1, SQL_DESC_COUNT, NULL, 0, NULL, (SQLPOINTER)&NumColumns); if (rc != SQL_SUCCESS) { printf("SQLColAttribute(NumColumns) failed %d\n", rc); odbc_show_error_info(SQL_HANDLE_STMT, StmtHandle); goto close_down; } if (NumColumns > NUM_COLS_ALLOW) { printf("We only support %d columns but %d found\n", NUM_COLS_ALLOW, NumColumns); goto close_down; } if (! NumColumns) { printf("This does not seem to be a select statement\n"); goto close_down; } for (i = 1, j = 0; i <= NumColumns; i++, j++) { rc = SQLDescribeCol(StmtHandle, i, ColumnName, sizeof(ColumnName), &ColumnNameSize, &ColumnSQLDataType, &ColumnSize, &ColumnDecimals, &ColumnNullable); if (rc != SQL_SUCCESS) { printf("SQLDescribeCol(%d) failed %d\n", i, rc); odbc_show_error_info(SQL_HANDLE_STMT, StmtHandle); goto close_down; } rc = SQLColAttribute(StmtHandle, i, SQL_DESC_CASE_SENSITIVE, NULL, 0, NULL, (SQLPOINTER)&ColumnCaseSensitive); if (rc != SQL_SUCCESS) { printf("SQLColAttribute(CaseSensitive, %d) failed %d\n", i, rc); odbc_show_error_info(SQL_HANDLE_STMT, StmtHandle); goto close_down; } rc = SQLColAttribute(StmtHandle, i, SQL_DESC_DISPLAY_SIZE, NULL, 0, NULL, (SQLPOINTER)&ColumnDisplaySize); if (rc != SQL_SUCCESS) { printf("SQLColAttribute(DisplaySize, %d) failed %d\n", i, rc); odbc_show_error_info(SQL_HANDLE_STMT, StmtHandle); goto close_down; } rc = SQLColAttribute(StmtHandle, i, SQL_DESC_UPDATABLE, NULL, 0, NULL, (SQLPOINTER)&ColumnUpdateable); if (rc != SQL_SUCCESS) { printf("SQLColAttribute(Updateable, %d) failed %d\n", i, rc); odbc_show_error_info(SQL_HANDLE_STMT, StmtHandle); goto close_down; } switch (ColumnSQLDataType) { case SQL_CHAR: { /* 1 */ loc_type = RET_TYPE_CHAR; strcpy(DisplaySQLType, "Char"); break; } case SQL_VARCHAR: { /* 12 */ loc_type = RET_TYPE_CHAR; strcpy(DisplaySQLType, "VarChar"); break; } case SQL_LONGVARCHAR: { /* -1 */ loc_type = RET_TYPE_CHAR; strcpy(DisplaySQLType, "LongVarChar"); break; } case SQL_DECIMAL: { /* 3 */ loc_type = RET_TYPE_NUM; strcpy(DisplaySQLType, "Decimal"); break; } case SQL_NUMERIC: { /* 2 */ loc_type = RET_TYPE_NUM; strcpy(DisplaySQLType, "Numeric"); break; } case SQL_SMALLINT: { /* 5 */ loc_type = RET_TYPE_NOTDO; strcpy(DisplaySQLType, "SmallInt"); break; } case SQL_INTEGER: { /* 4 */ loc_type = RET_TYPE_NUM; strcpy(DisplaySQLType, "Integer"); break; } case SQL_REAL: { /* 7 */ loc_type = RET_TYPE_DOUBLE; strcpy(DisplaySQLType, "Real"); break; } case SQL_FLOAT: { /* 6 */ loc_type = RET_TYPE_DOUBLE; strcpy(DisplaySQLType, "Float"); break; } case SQL_DOUBLE: { /* 8 */ loc_type = RET_TYPE_DOUBLE; strcpy(DisplaySQLType, "Double"); break; } case SQL_BIT: { /* -7 */ loc_type = RET_TYPE_NOTDO; strcpy(DisplaySQLType, "Bit"); break; } case SQL_TINYINT: { /* -6 */ loc_type = RET_TYPE_NOTDO; strcpy(DisplaySQLType, "TinyInt"); break; } case SQL_BIGINT: { /* -5 */ loc_type = RET_TYPE_NOTDO; strcpy(DisplaySQLType, "BigInt"); break; } case SQL_BINARY: { /* -2 */ loc_type = RET_TYPE_NOTDO; strcpy(DisplaySQLType, "Binary"); break; } case SQL_VARBINARY: { /* -3 */ loc_type = RET_TYPE_NOTDO; strcpy(DisplaySQLType, "VarBinary"); break; } case SQL_LONGVARBINARY: { /* -4 */ loc_type = RET_TYPE_NOTDO; strcpy(DisplaySQLType, "LongVarBinary"); break; } case SQL_TYPE_DATE: { /* 91 */ loc_type = RET_TYPE_DATEONLY; strcpy(DisplaySQLType, "DateTime"); break; } case SQL_TYPE_TIME: { /* 92 */ loc_type = RET_TYPE_TIMEONLY; strcpy(DisplaySQLType, "Time"); break; } case SQL_TYPE_TIMESTAMP: { /* 93 */ loc_type = RET_TYPE_TIMESTAMP; strcpy(DisplaySQLType, "TimeStampType"); break; } case SQL_UNKNOWN_TYPE: { /* 0 */ loc_type = RET_TYPE_NOTDO; strcpy(DisplaySQLType, "Unknown"); break; } default: { loc_type = RET_TYPE_NOTDO; strcpy(DisplaySQLType, "NotDefined"); break; } } items[j].dbsize = items[j].precision = ColumnSize; items[j].dbtype = ColumnSQLDataType; strcpy(items[j].name, (char*)ColumnName); items[j].name_len = ColumnNameSize; items[j].scale = ColumnDecimals; /* ColumnDisplaySize does not include 0 */ items[j].dsize = ColumnDisplaySize + 1; items[j].nullok = ColumnNullable; items[j].case_sensitive = ColumnCaseSensitive; if (ColumnUpdateable != SQL_ATTR_READONLY) { items[j].updateable = 1; } else { printf("For %s column %d is not updateable!\n", ColumnName, j + 1); items[j].updateable = 0; } strcpy(items[j].display_dbtype, DisplaySQLType); items[j].our_type = loc_type; } bad = 0; for (i = 0; i < NumColumns; i++) { switch (items[i].our_type) { case RET_TYPE_CHAR: { items[i].fetch_type = SQL_C_CHAR; items[i].fetch_len = items[i].use_precision = items[i].dbsize; if (items[i].fetch_len > MAX_LEN_OF_DATA) { items[i].fetch_len = MAX_LEN_OF_DATA; } items[i].fetch_len++; if (items[i].use_precision > MAX_LEN_OF_DATA) { items[i].use_precision = MAX_LEN_OF_DATA; items[i].truncate_text = 1; } break; } case RET_TYPE_DATEONLY: { if (items[i].dbsize != SQL_DATE_LEN) { printf("Date in column %s not %d bytes is %d\n", items[i].name, SQL_DATE_LEN, items[i].dbsize); bad++; } items[i].fetch_type = SQL_C_TYPE_DATE; items[i].fetch_len = 15; /* yyyymmddhhmmss */ items[i].use_precision = 14; break; } case RET_TYPE_TIMEONLY: { int want_len; want_len = SQL_TIME_LEN; if (items[i].scale) { want_len += items[i].scale + 1; } if (items[i].dbsize != want_len) { printf("Time in column %s not %d bytes is %d\n", items[i].name, want_len, items[i].dbsize); bad++; } items[i].fetch_type = SQL_C_TYPE_TIME; items[i].fetch_len = 15; /* yyyymmddhhmmss */ items[i].use_precision = 14; break; } case RET_TYPE_TIMESTAMP: { int want_len; want_len = SQL_TIMESTAMP_LEN; if (items[i].scale) { want_len += items[i].scale + 1; } if (items[i].dbsize != want_len) { printf("TimeStamp in column %s not %d bytes is %d\n", items[i].name, want_len, items[i].dbsize); bad++; } items[i].fetch_type = SQL_C_TYPE_TIMESTAMP; items[i].fetch_len = 15; /* yyyymmddhhmmss */ items[i].use_precision = 14; break; } case RET_TYPE_DOUBLE: { items[i].use_precision = PRECISION_DEFAULT; items[i].use_scale = SCALE_DEFAULT; items[i].fetch_type = SQL_C_CHAR; items[i].fetch_len = 50; if (items[i].use_precision >= 50) { printf("Column %s has too many digits\n", items[i].name); bad++; } if (items[i].use_scale >= items[i].use_precision) { printf("Column %s has scale too big\n", items[i].name); bad++; } break; } case RET_TYPE_NUM: { if (items[i].scale <= 0) { /* rounded somewhere before or at whole number */ items[i].use_scale = 0; items[i].use_precision = items[i].precision; } else { /* number has some decimal digits */ items[i].use_precision = items[i].precision; items[i].use_precision++; /* room for . */ items[i].use_scale = items[i].scale; } items[i].use_precision++; /* room for sign */ items[i].fetch_type = SQL_C_CHAR; items[i].fetch_len = 50; if (items[i].use_precision >= 50) { printf("Column %s has too many digits\n", items[i].name); bad++; } if (items[i].use_scale >= items[i].use_precision) { printf("Column %s has scale too big\n", items[i].name); bad++; } break; } default: { printf("Column %s is not useable\n", items[i].name); bad++; } } switch (items[i].our_type) { case RET_TYPE_DATEONLY: { rc = SQLBindCol(StmtHandle, (SQLUSMALLINT)(i + 1), items[i].fetch_type, (SQLPOINTER)&items[i].ret_date, sizeof(DATE_STRUCT), &(items[i].ret_wid)); break; } case RET_TYPE_TIMEONLY: { rc = SQLBindCol(StmtHandle, (SQLUSMALLINT)(i + 1), items[i].fetch_type, (SQLPOINTER)&items[i].ret_timeonly, sizeof(TIME_STRUCT), &(items[i].ret_wid)); break; } case RET_TYPE_TIMESTAMP: { rc = SQLBindCol(StmtHandle, (SQLUSMALLINT)(i + 1), items[i].fetch_type, (SQLPOINTER)&items[i].ret_timestamp, sizeof(TIMESTAMP_STRUCT), &(items[i].ret_wid)); break; } case RET_TYPE_NOTDO: { /* can't bind */ rc = SQL_SUCCESS; break; } default: { rc = SQLBindCol(StmtHandle, (SQLUSMALLINT)(i + 1), items[i].fetch_type, items[i].odbc_data, items[i].fetch_len, &(items[i].ret_wid)); break; } } if (rc != SQL_SUCCESS) { printf("SQLBindCol(%d) failed %d\n", i + 1, rc); odbc_show_error_info(SQL_HANDLE_STMT, StmtHandle); goto close_down; } if (items[i].fetch_len > max_width) max_width = items[i].fetch_len; } if (bad) { printf("There are %d columns we can not deal with\n", bad); goto close_down; } rc = SQLExecute(StmtHandle); if (rc != SQL_SUCCESS) { printf("SQLExecute failed %d\n", rc); odbc_show_error_info(SQL_HANDLE_STMT, StmtHandle); goto close_down; } fetch_spot = SQL_FETCH_FIRST; while (1) { /* Now read the data */ rc = SQLFetchScroll(StmtHandle, fetch_spot, 0); fetch_spot = SQL_FETCH_NEXT; if (rc == SQL_NO_DATA) { /* end of file */ rtn = 0; goto close_down; } else if (rc == SQL_SUCCESS_WITH_INFO) { /* We may have truncated string data */ if (! odbc_my_error_info( SQL_HANDLE_STMT, StmtHandle, (SQLCHAR*)"01004")) { goto say_my_odbc_error; } printf("When fetching data at least one field was truncated\n"); } else if (rc != SQL_SUCCESS) { say_my_odbc_error: printf("SQLFetch() failed %d\n", rc); odbc_show_error_info(SQL_HANDLE_STMT, StmtHandle); goto close_down; } /* We have to do something to display what we got */ for (i = 0; i < NumColumns; i++) { printf("Item %s=", items[i].name); items[i].data_is_null = (items[i].ret_wid == SQL_NULL_DATA); if (items[i].data_is_null) { printf("NULL\n"); } else { switch (items[i].our_type) { case RET_TYPE_DOUBLE: case RET_TYPE_NUM: { items[i].odbc_data[items[i].use_precision] = 0; printf("'%s'\n", items[i].odbc_data); break; } case RET_TYPE_TEXT: case RET_TYPE_CHAR: { items[i].did_truncate = 0; printf("%d bytes=", strlen(items[i].odbc_data)); if (items[i].truncate_text) { int truncate_spot; if (items[i].ret_wid > items[i].use_precision) { truncate_spot = items[i].use_precision - 3; memcpy(&items[i].odbc_data[truncate_spot], "...", 4); } } printf("'%s'\n", items[i].odbc_data); break; } case RET_TYPE_DATEONLY: { printf("y-%04d m-%02d d-%02d'\n", items[i].ret_date.year, items[i].ret_date.month, items[i].ret_date.day); break; } case RET_TYPE_TIMEONLY: { printf("h-%4d m-%2d s-%2d\n", items[i].ret_timeonly.hour, items[i].ret_timeonly.minute, items[i].ret_timeonly.second); break; } case RET_TYPE_TIMESTAMP: { printf("y-%4d m-%2d d-%2d h-%2d m-%2d s-%2d\n", items[i].ret_timestamp.year, items[i].ret_timestamp.month, items[i].ret_timestamp.day, items[i].ret_timestamp.hour, items[i].ret_timestamp.minute, items[i].ret_timestamp.second); break; } default: { printf("?????%d %d?????\n", items[i].fetch_type, items[i].name); goto close_down; } } } } if (strcmp(items[0].odbc_data, "0003") == 0) { /* This will delete a record */ rc = SQLSetPos(StmtHandle, 1, SQL_DELETE, SQL_LOCK_NO_CHANGE); if (rc != SQL_SUCCESS) { printf("SQLSetPos(delete) failed %d\n", rc); odbc_show_error_info(SQL_HANDLE_STMT, StmtHandle); goto close_down; } rc = SQLEndTran(SQL_HANDLE_DBC, DBCHandle, SQL_COMMIT); if (rc != SQL_SUCCESS) { printf("SQLEndTran(delete) failed %d\n", rc); odbc_show_error_info(SQL_HANDLE_DBC, DBCHandle); goto close_down; } printf("!!!Deleted!!!\n\n"); } else if (strcmp(items[0].odbc_data, "0007") == 0) { items[0].ret_wid = SQL_NTS; items[1].ret_wid = SQL_COLUMN_IGNORE; memmove(items[0].odbc_data, "0050", 5); /* This will update a record */ rc = SQLSetPos(StmtHandle, 1, SQL_UPDATE, SQL_LOCK_NO_CHANGE); if (rc != SQL_SUCCESS) { printf("SQLSetPos(update) failed %d\n", rc); odbc_show_error_info(SQL_HANDLE_STMT, StmtHandle); goto close_down; } rc = SQLEndTran(SQL_HANDLE_DBC, DBCHandle, SQL_COMMIT); if (rc != SQL_SUCCESS) { printf("SQLEndTran(update) failed %d\n", rc); odbc_show_error_info(SQL_HANDLE_DBC, DBCHandle); goto close_down; } printf("!!!Updated!!!\n\n"); } else printf("!!!Unchanged!!!\n\n"); } }