void classTables::LoadTables()
{
CursorScoper s(listView()) ;
SQLRETURN nReturn ;
SQLCHAR szTableName[MAX_COLUMN_WIDTH];
SQLCHAR szTableType[MAX_COLUMN_WIDTH];
SQLCHAR szTableRemarks[MAX_COLUMN_WIDTH];
SQLLEN nIndicatorName;
SQLLEN nIndicatorType;
SQLLEN nIndicatorRemarks;
classTable *pTable = NULL;
// CREATE A STATEMENT
StatementScoper stmt( hDbc ) ; if ( !stmt() ) return ;
// EXECUTE OUR SQL/CALL
if (!SQL_SUCCEEDED(nReturn=SQLTables( stmt(), 0, 0, (SQLCHAR*)qsLibrary.ascii(), SQL_NTS, 0, 0, 0, 0 ) ) )
return my_msgBox( "classTables", "SQLTables", nReturn, NULL, NULL, stmt() ) ;
SQLBindCol( stmt(), SQLTables_TABLE_NAME, SQL_C_CHAR, szTableName, sizeof(szTableName), &nIndicatorName );
SQLBindCol( stmt(), SQLTables_TABLE_TYPE, SQL_C_CHAR, szTableType, sizeof(szTableType), &nIndicatorType );
SQLBindCol( stmt(), SQLTables_REMARKS, SQL_C_CHAR, szTableRemarks, sizeof(szTableRemarks), &nIndicatorRemarks );
// GET RESULTS
while ( SQL_SUCCEEDED(SQLFetch( stmt() ) ) )
{
if ( nIndicatorName == SQL_NULL_DATA )
listTables.append( pTable = new classTable( this, pTable, pCanvas, hDbc, "Unknown" ) );
else
listTables.append( pTable = new classTable( this, pTable, pCanvas, hDbc, QString((char*)szTableName).stripWhiteSpace(), QString((char*)szTableType).stripWhiteSpace(), QString((char*)szTableRemarks).stripWhiteSpace(), qsLibrary ) );
}
}
void ring_vm_odbc_tables ( void *pPointer )
{
ring_odbc *pODBC ;
SQLRETURN ret ;
if ( RING_API_PARACOUNT != 1 ) {
RING_API_ERROR(RING_API_MISS1PARA);
return ;
}
if ( RING_API_ISPOINTER(1) ) {
pODBC = (ring_odbc *) RING_API_GETCPOINTER(1,RING_VM_POINTER_ODBC) ;
if ( pODBC == NULL ) {
return ;
}
if ( ! (pODBC->nFlag & RING_ODBC_FLAG_STMT) ) {
SQLAllocHandle(SQL_HANDLE_STMT, pODBC->dbc , &pODBC->stmt);
pODBC->nFlag = pODBC->nFlag | RING_ODBC_FLAG_STMT ;
}
ret = SQLTables(pODBC->stmt, NULL, 0, NULL, 0, NULL, 0, (SQLCHAR *) "TABLE", SQL_NTS);
if ( SQL_SUCCEEDED(ret) ) {
RING_API_RETNUMBER(1);
} else {
RING_API_RETNUMBER(0);
}
} else {
RING_API_ERROR(RING_API_BADPARATYPE);
}
}
/************************************************************************
* name: SQLTablesW
* arguments:
* returns/side-effects:
* description:
* NOTE:
************************************************************************/
ODBC_INTERFACE RETCODE SQL_API
SQLTablesW (SQLHSTMT hstmt,
SQLWCHAR *catalog, SQLSMALLINT catalog_len,
SQLWCHAR *schema, SQLSMALLINT schema_len,
SQLWCHAR *table, SQLSMALLINT table_len,
SQLWCHAR *type, SQLSMALLINT type_len)
{
RETCODE ret = ODBC_ERROR;
SQLCHAR *cb_catalog = NULL, *cb_schema = NULL, *cb_table = NULL, *cb_type=NULL;
int cb_catalog_len = 0, cb_schema_len = 0, cb_table_len = 0, cb_type_len = 0;
OutputDebugString ("SQLTablesW called.\n");
wide_char_to_bytes (catalog, catalog_len, &cb_catalog, &cb_catalog_len, NULL);
wide_char_to_bytes (schema, schema_len, &cb_schema, &cb_schema_len, NULL);
wide_char_to_bytes (table, table_len, &cb_table, &cb_table_len, NULL);
wide_char_to_bytes (type, type_len, &cb_type, &cb_type_len, NULL);
ret = SQLTables(hstmt,
cb_catalog, cb_catalog_len,
cb_schema, cb_schema_len,
cb_table, cb_table_len,
cb_type, cb_type_len);
UT_FREE(cb_catalog);
UT_FREE(cb_schema);
UT_FREE(cb_table);
UT_FREE(cb_type);
return ret;
}
string ODBCHandler::get_tables()
{
SQLHSTMT stmt;
SQLRETURN ret;
SQLSMALLINT nr_columns;
stringstream tbl_list;
string ODBC_error;
bool list_start = false;
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
SQLTables(stmt, NULL, 0, NULL, 0, NULL, 0, (unsigned char*)"TABLE",
SQL_NTS);
SQLNumResultCols(stmt, &nr_columns);
while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) {
SQLUSMALLINT i;
tbl_list<<"{";
for (i = 1; i <= nr_columns; i++) {
SQLLEN indicator;
char buf[512];
ret = SQLGetData(stmt, i, SQL_C_CHAR,
buf, sizeof(buf), &indicator);
if (SQL_SUCCEEDED(ret)) {
if (indicator != SQL_NULL_DATA) {//strcpy(buf, "NULL");
if (list_start)
tbl_list<<",";
else
list_start = true;
tbl_list<<buf;
}
}
}
tbl_list<<"}";
}
return tbl_list.str();
}
int main(int argc, char **argv)
{
int rc;
HSTMT hstmt = SQL_NULL_HSTMT;
char sql[100000];
char *sqlend;
int i;
test_connect();
rc = SQLAllocStmt(conn, &hstmt);
if (!SQL_SUCCEEDED(rc))
{
print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
exit(1);
}
rc = SQLTables(hstmt, "", SQL_NTS,
"public", SQL_NTS,
"%", SQL_NTS,
"TABLE", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLTables failed", hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Clean up */
test_disconnect();
}
/*-----------------------------------------------------------------------------*/
void ODBCTables()
{
struct Cursor *cur = (struct Cursor *)ptoc_int(2);
RETCODE rc;
if (cur->Status == 2) { /* reusing opened cursor*/
rc = SQLFreeStmt(cur->hstmt,SQL_CLOSE);
if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO)) {
ctop_int(3, PrintErrorMsg(cur));
SetCursorClose(cur);
return;
}
}
if (((rc=SQLTables(cur->hstmt,
NULL, 0,
NULL, 0,
NULL, 0,
NULL, 0)) == SQL_SUCCESS) ||
(rc == SQL_SUCCESS_WITH_INFO)) {
ctop_int(3,0);
} else {
ctop_int(3,PrintErrorMsg(cur));
SetCursorClose(cur);
}
return;
}
/* We need also a separate database-table-info command, like the
database-field-info command, which you can use to get more info
about a specific table. */
static gsql_result *
gsql_db_list_tables (Database *db,
char *table_qualifier,
char *table_owner,
char *table_name,
char *table_type)
{
gsql_result *gr = (gsql_result *) NULL;
HSTMT hstmt;
RETCODE status;
/* Issue an SQLTables request, and then create a result set. */
gsql_clear_error_message ();
if (table_qualifier == (char *) NULL)
table_qualifier = "";
if (table_owner == (char *) NULL)
table_owner = "";
if (table_name == (char *) NULL)
table_name = "";
if (table_type == (char *) NULL)
table_type = "";
if (SQLAllocStmt (db->hdbc, &hstmt) != SQL_SUCCESS)
{
gsql_save_error_message (db, "SQLAllocStmt");
return ((gsql_result *)NULL);
}
status = SQLTables (hstmt,
(unsigned char *)table_qualifier, SQL_NTS,
(unsigned char *)table_owner, SQL_NTS,
(unsigned char *)table_name, SQL_NTS,
(unsigned char *)table_type, SQL_NTS);
if (status != SQL_SUCCESS)
{
gsql_save_error_message (db, "SQLTables");
return ((gsql_result *)NULL);
}
/* gsql_store_result() will copy the HSTMT to a result struct,
and it will eventually be freed when someone calls gsql_free_result. */
db->hstmt = hstmt;
gr = gsql_store_result (db);
return (gr);
}
bool
showTables(void)
{
char tabname[40];
char tabtype[40];
char tabowner[40];
SQLLEN tabnameOut, tabtypeOut, tabownerOut;
char *buf;
int buflen, cx;
int truevalue = SQL_TRUE;
/*
SQLSetConnectAttr(hdbc, SQL_ATTR_METADATA_ID,
&truevalue, SQL_IS_INTEGER);
*/
newStatement();
stmt_text = "get tables";
debugStatement();
rc = SQLTables(hstmt,
NULL, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS);
if(rc)
goto abort;
SQLBindCol(hstmt, 2, SQL_CHAR, (SQLPOINTER) tabowner, sizeof (tabowner),
&tabownerOut);
SQLBindCol(hstmt, 3, SQL_CHAR, (SQLPOINTER) tabname, sizeof (tabname),
&tabnameOut);
SQLBindCol(hstmt, 4, SQL_CHAR, (SQLPOINTER) tabtype, sizeof (tabtype),
&tabtypeOut);
buf = initString(&buflen);
while(SQLFetch(hstmt) == SQL_SUCCESS) {
char tabline[140];
sprintf(tabline, "%s.%s|%s\n", tabowner, tabname, tabtype);
stringAndString(&buf, &buflen, tabline);
}
cx = sideBuffer(0, buf, buflen, 0, false);
nzFree(buf);
i_printf(MSG_ShowTables, cx);
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
return true;
abort:
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
return false;
} /* showTables */
void getCatalogs()
{
printf("getCatalogs\n");
SQLHSTMT stmt;
SQLSMALLINT columns; /* number of columns in result-set */
SQLCHAR buf[5][64];
SQLINTEGER indicator[ 5 ];
int i;
connect_to_database();
/* Allocate a statement handle */
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
/* Retrieve a list of tables */
SQLTables(stmt, (SQLCHAR*)SQL_ALL_CATALOGS, SQL_NTS, (SQLCHAR*)"", SQL_NTS, (SQLCHAR*)"", SQL_NTS, (SQLCHAR*)"", SQL_NTS );
/* How many columns are there */
SQLNumResultCols(stmt, &columns);
/* Loop through the rows in the result-set binding to */
/* local variables */
for (i = 0; i < columns; i++) {
SQLBindCol( stmt, i + 1, SQL_C_CHAR,
buf[ i ], sizeof( buf[ i ] ), &indicator[ i ] );
}
/* Fetch the data */
while (SQL_SUCCEEDED(SQLFetch(stmt))) {
/* display the results that will now be in the bound area's */
for ( i = 0; i < columns; i ++ ) {
if (indicator[ i ] == SQL_NULL_DATA) {
printf(" Column %u : NULL\n", i);
}
else {
if (strlen((char*)(buf[i])) > 0) {
printf(" Column %u : %s\n", i, buf[i]);
} else {
printf(" Column %u : Empty string\n", i);
}
}
}
}
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
disconnect_from_database();
}
void list_tables()
{
SQLHSTMT stmt;
/* Allocate a statement handle */
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
/* Retrieve a list of tables */
SQLTables(stmt, NULL, 0, NULL, 0, NULL, 0, "TABLE", SQL_NTS);
printf("Tables:\n");
/* print the results */
print_results(stmt);
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
printf("\n");
}
// --------------------------------------------------------------------------------
// ODBCTableList
// Crea una lista delle tabelle di un dbase
// --------------------------------------------------------------------------------
SINT ODBCTableList(CHAR *lpLibrary,CHAR *lpName,CHAR *lpType)
{
ARMaker(WS_OPEN,NULL);
if (!EhOdbc.hStmt) goto FINE;
// Carica il driver
//win_open(EHWP_SCREENCENTER,50,232,59,-1,3,ON,"Lettura delle Tabelle");
mouse_graph(0,0,"CLEX");
ODBCCloseCursor();
if (lpLibrary)
{
if (!*lpLibrary) lpLibrary=NULL;
}
// Chiedo l'elenco delle tabelle
// EhOdbc.sqlLastError=SQLTables(EhOdbc.hStmt, lpLibrary, SQL_NTS, NULL, 0, "%", SQL_NTS, NULL, 0);
EhOdbc.sqlLastError=SQLTables(EhOdbc.hStmt,
NULL, 0, // Catalogo
//"",SQL_NTS,
lpLibrary, lpLibrary?SQL_NTS:0, //Schema
lpName, (lpName?SQL_NTS:0), // Nome della tabella
lpType, lpType?SQL_NTS:0); // Tipo della tabella
if (EhOdbc.sqlLastError!=SQL_SUCCESS)
{if (!EhOdbc.fNoErrorView) ODBCError("ODBCTableList()::SQLTables()");
goto FINE;
}
ODBCDoDictionary(); // Creo il dizionario del risultato
while (!ODBCNext())
{
//dispx("[%s] %s < ",ODBCFldPtr("TABLE_TYPE"),ODBCFldPtr("TABLE_NAME")); mouse_input();
if (!strcmp(ODBCFldPtr("TABLE_TYPE"),lpType))
//!strcmp(ODBCFldPtr("TABLE_TYPE"),"SYSTEM TABLE"))
{
ARMaker(WS_ADD,ODBCFldPtr("TABLE_NAME"));
}
}
ODBCCloseCursor();
FINE:
//win_close();
MouseCursorDefault();
return ARMaker(WS_CLOSE,"ODBCSource");
}
void ODBCHandler::showTables(const char *dsn)
{
SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;
SQLRETURN ret; /* ODBC API return status */
SQLSMALLINT columns; /* number of columns in result-set */
int row = 0;
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
/* We want ODBC 3 support */
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3_80, 0);
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
/* Connect to the DSN mydsn */
/* You will need to change mydsn to one you have created and tested */
SQLDriverConnect(dbc, NULL, (SQLCHAR*) dsn, SQL_NTS,
NULL, 0, NULL, SQL_DRIVER_COMPLETE);
/* Allocate a statement handle */
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
/* Retrieve a list of tables */
SQLTables(stmt, NULL, 0, NULL, 0, NULL, 0, (SQLCHAR*)"VIEW", SQL_NTS);
/* How many columns are there */
SQLNumResultCols(stmt, &columns);
/* Loop through the rows in the result-set */
while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) {
SQLUSMALLINT i;
printf("Row %d\n", row++);
/* Loop through the columns */
for (i = 1; i <= columns; i++) {
SQLLEN indicator;
SQLCHAR buf[512];
/* retrieve column data as a string */
ret = SQLGetData(stmt, i, SQL_C_CHAR,
buf, sizeof(buf), &indicator);
if (SQL_SUCCEEDED(ret)) {
/* Handle null columns */
if (indicator == SQL_NULL_DATA) strcpy((char*)buf, "NULL");
printf(" Column %u : %s\n", i, buf);
}
}
}
}
SQLRETURN SQLTablesA( SQLHSTMT statement_handle,
SQLCHAR *catalog_name,
SQLSMALLINT name_length1,
SQLCHAR *schema_name,
SQLSMALLINT name_length2,
SQLCHAR *table_name,
SQLSMALLINT name_length3,
SQLCHAR *table_type,
SQLSMALLINT name_length4 )
{
return SQLTables( statement_handle,
catalog_name,
name_length1,
schema_name,
name_length2,
table_name,
name_length3,
table_type,
name_length4 );
}
static int display_tables( SQLHDBC hDbc )
{
SQLHSTMT hStmt;
SQLRETURN ret;
if ( version3 )
{
if ( SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt ) != SQL_SUCCESS )
{
if ( bVerbose ) DumpODBCLog( hEnv, hDbc, 0 );
fprintf( stderr, "[ISQL]ERROR: Could not SQLAllocHandle( SQL_HANDLE_STMT )\n" );
return 0;
}
}
else
{
if ( SQLAllocStmt( hDbc, &hStmt ) != SQL_SUCCESS )
{
if ( bVerbose ) DumpODBCLog( hEnv, hDbc, 0 );
fprintf( stderr, "[ISQL]ERROR: Could not SQLAllocStmt\n" );
return 0;
}
}
ret = SQLTables( hStmt, NULL, 0, NULL, 0, NULL, 0, NULL, 0 );
if ( ret == SQL_ERROR )
{
if ( bVerbose ) DumpODBCLog( hEnv, hDbc, hStmt );
fprintf( stderr, "[ISQL]ERROR: Could not SQLTables\n" );
}
else
{
display_result_set( hDbc, hStmt );
}
SQLFreeStmt( hStmt, SQL_DROP );
return 1;
}
int FetchTest(SQLHANDLE env, SQLHANDLE dbc, SQLHANDLE stmt)
{
int ret;
char f1[50]= "praba";
char f2[50]= "praba";
char f3[50]= "praba";
char f4[50]= "praba";
char f5[50]= "praba";
ret = SQLTables(stmt, (SQLCHAR*)"csql", SQL_NTS, (SQLCHAR*)"csql", SQL_NTS, (SQLCHAR*)"", SQL_NTS, (SQLCHAR*)"Table", SQL_NTS );
checkrc(ret,__LINE__);
short totalFields=0;
ret = SQLNumResultCols (stmt, &totalFields);
checkrc(ret,__LINE__);
printf( "No of columns in resultset = %d\n",totalFields);
ret = SQLBindCol(stmt,1,SQL_C_CHAR,f1,sizeof(f1),NULL);
checkrc(ret,__LINE__);
ret = SQLBindCol(stmt,2,SQL_C_CHAR,f2,sizeof(f2),NULL);
checkrc(ret,__LINE__);
ret = SQLBindCol(stmt,3,SQL_C_CHAR,f3,sizeof(f3),NULL);
checkrc(ret,__LINE__);
ret = SQLBindCol(stmt,4,SQL_C_CHAR,f4,sizeof(f4),NULL);
checkrc(ret,__LINE__);
ret = SQLBindCol(stmt,5,SQL_C_CHAR,f5,sizeof(f5),NULL);
checkrc(ret,__LINE__);
while(SQL_SUCCEEDED(ret = SQLFetch(stmt))) {
printf("\tF1=%s \t F2=%s \t F3=%s \t F4= %s \t F5=%s \n ",f1,f2,f3,f4,f5);
}
ret = SQLCloseCursor(stmt);
checkrc(ret,__LINE__);
ret = SQLTransact(env,dbc,SQL_COMMIT);
checkrc(ret,__LINE__);
return 0;
}
void TOdbcDb::GetTbNmV(TStrV& TbNmV) const {
#define ODBC_STR_LEN 254+1
SQLCHAR szCatalog[ODBC_STR_LEN], szSchema[ODBC_STR_LEN];
SQLCHAR szTableName[ODBC_STR_LEN], szTableTypeName[ODBC_STR_LEN];
SQLCHAR szRemarks[ODBC_STR_LEN];
SQLHSTMT StmtHnd;
SQLAllocHandle(SQL_HANDLE_STMT, DbcHnd, &StmtHnd);
/* Declare buffers for bytes available to return */
SQLLEN cbCatalog, cbSchema, cbTableName, cbTableTypeName, cbRemarks;
SQLRETURN RetCd=SQLTables(StmtHnd,
NULL, 0, /* All catalogs */
NULL, 0, /* All schemas */
NULL, 0, /* All tables */
NULL, 0); /* All table-typescolumns */
TbNmV.Clr();
if (RetCd == SQL_SUCCESS || RetCd == SQL_SUCCESS_WITH_INFO){
/* Bind columns in result set to buffers */
SQLBindCol(StmtHnd, 1, SQL_C_CHAR, szCatalog, ODBC_STR_LEN, &cbCatalog);
SQLBindCol(StmtHnd, 2, SQL_C_CHAR, szSchema, ODBC_STR_LEN, &cbSchema);
SQLBindCol(StmtHnd, 3, SQL_C_CHAR, szTableName, ODBC_STR_LEN, &cbTableName);
SQLBindCol(StmtHnd, 4, SQL_C_CHAR, szTableTypeName, ODBC_STR_LEN, &cbTableTypeName);
SQLBindCol(StmtHnd, 5, SQL_C_CHAR, szRemarks, ODBC_STR_LEN, &cbRemarks);
//
forever{
RetCd=SQLFetch(StmtHnd);
if (RetCd==SQL_SUCCESS || RetCd==SQL_SUCCESS_WITH_INFO){
TbNmV.Add((char*)szTableName);
//printf("[%s]", szTableName);
} else {
break;
}
}
}
int main(int argc, char *argv[])
{
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLRETURN ret;
SQLCHAR dbms_name[256], dbms_ver[256];
SQLSMALLINT columns;
int table_cnt;
// container *V = vector_container( custom_container(sizeof(table_info), NULL) );
container *V = vector_container( pair_container( string_container(), string_container() ) );
ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (!SQL_SUCCEEDED(ret))
{
display_error(SQL_HANDLE_ENV, henv);
exit(-1);
}
ret = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
if (!SQL_SUCCEEDED(ret))
{
display_error(SQL_HANDLE_ENV, henv);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
exit(-1);
}
ret = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if (!SQL_SUCCEEDED(ret))
{
display_error(SQL_HANDLE_ENV, henv);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
exit(-1);
}
// FreeTDS krasjer på SQLDriverConnect! Derfor bruker vi SQLConnect istedet.
ret = SQLConnect(hdbc, (SQLCHAR*) "adventure", SQL_NTS, (SQLCHAR*) "ax", SQL_NTS, (SQLCHAR*) "Ju13brz;", SQL_NTS);
if (ret==SQL_SUCCESS_WITH_INFO)
display_error(SQL_HANDLE_DBC, hdbc);
if (!SQL_SUCCEEDED(ret))
{
display_error(SQL_HANDLE_DBC, hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
exit(-1);
}
fprintf(stderr, "ODBC Crawler: Connected to '%s'!\n", "adventure");
SQLGetInfo(hdbc, SQL_DBMS_NAME, (SQLPOINTER)dbms_name, sizeof(dbms_name), NULL);
SQLGetInfo(hdbc, SQL_DBMS_VER, (SQLPOINTER)dbms_ver, sizeof(dbms_ver), NULL);
fprintf(stderr, "DBMS Name: %s\n", dbms_name);
fprintf(stderr, "DBMS Version: %s\n", dbms_ver);
STMT_CATCH( SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt) );
// Å spesifisere "TABLE" direkte fungerer ikke :\ (FreeTDS?)
// Derfor er vi nødt til å sortere ut tabellene etterpå.
STMT_CATCH( SQLTables(hstmt, NULL, 0, NULL, 0, NULL, 0, NULL, 0) );
STMT_CATCH( SQLNumResultCols(hstmt, &columns) );
while (SQL_SUCCEEDED(ret = SQLFetch(hstmt)))
{
char buf[3][256];
SQLINTEGER indicator;
char *catalog, *schema, *table_name;
ret = SQLGetData(hstmt, 4, SQL_C_CHAR, buf[0], sizeof(buf[0]), &indicator);
if (SQL_SUCCEEDED(ret) && !strcmp(buf[0], "TABLE"))
{
ret = SQLGetData(hstmt, 1, SQL_C_CHAR, buf[0], sizeof(buf), &indicator);
if (SQL_SUCCEEDED(ret) && indicator!=SQL_NULL_DATA)
catalog = buf[0];
else
catalog = NULL;
ret = SQLGetData(hstmt, 2, SQL_C_CHAR, buf[1], sizeof(buf), &indicator);
if (SQL_SUCCEEDED(ret) && indicator!=SQL_NULL_DATA)
schema = buf[1];
else
schema = NULL;
ret = SQLGetData(hstmt, 3, SQL_C_CHAR, buf[2], sizeof(buf), &indicator);
if (SQL_SUCCEEDED(ret) && indicator!=SQL_NULL_DATA)
table_name = buf[2];
else
table_name = NULL;
vector_pushback(V, schema, table_name);
}
}
for (table_cnt=0; table_cnt<vector_size(V); table_cnt++)
{
char query[1024];
char *schema = (char*)pair(vector_get(V,table_cnt)).first.ptr;
char *table_name = (char*)pair(vector_get(V,table_cnt)).second.ptr;
printf("%s.%s:\n", schema, table_name);
STMT_CATCH( SQLFreeHandle(SQL_HANDLE_STMT, hstmt) );
STMT_CATCH( SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt) );
// STMT_CATCH( SQLColumns(hstmt, NULL, 0, schema, SQL_NTS, table_name, SQL_NTS, NULL, 0) );
// STMT_CATCH( SQLExecDirect(hstmt, "select FirstName, LastName from Person.Contact;", SQL_NTS) );
snprintf(query, 1023, "SELECT * FROM %s.%s;", schema, table_name);
// printf("%s\n", query);
STMT_CATCH( SQLExecDirect(hstmt, (SQLCHAR*)query, SQL_NTS) );
STMT_CATCH( SQLNumResultCols(hstmt, &columns) );
while (SQL_SUCCEEDED(ret = SQLFetch(hstmt)))
{
char buf[512];
SQLUSMALLINT i;
SQLINTEGER indicator;
for (i=1; i<=columns; i++)
{
ret = SQLGetData(hstmt, i, SQL_C_CHAR, buf, sizeof(buf), &indicator);
if (SQL_SUCCEEDED(ret))
{
if (indicator==SQL_NULL_DATA) strcpy(buf, "NULL");
}
printf("| %s ", buf);
}
printf("|\n");
}
}
destroy(V);
quit:
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
/****************************
* ExecuteHelp - create a statement, execute the SQL, and get rid of the statement
* - show results as per request; bHTMLTable has precedence over other options
***************************/
static int
ExecuteHelp( SQLHDBC hDbc, char *szSQL, char cDelimiter, int bColumnNames, int bHTMLTable )
{
SQLHSTMT hStmt;
SQLLEN nRows = 0;
SQLRETURN nReturn;
SQLCHAR *szSepLine;
char *args[10];
int n_args;
if (!(szSepLine = calloc(1, 32001)))
{
fprintf(stderr, "[ISQL]ERROR: Failed to allocate line");
return 0;
}
/****************************
* EXECUTE SQL
***************************/
if ( version3 )
{
if ( SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt ) != SQL_SUCCESS )
{
if ( bVerbose ) DumpODBCLog( hEnv, hDbc, 0 );
fprintf( stderr, "[ISQL]ERROR: Could not SQLAllocHandle( SQL_HANDLE_STMT )\n" );
free(szSepLine);
return 0;
}
}
else
{
if ( SQLAllocStmt( hDbc, &hStmt ) != SQL_SUCCESS )
{
if ( bVerbose ) DumpODBCLog( hEnv, hDbc, 0 );
fprintf( stderr, "[ISQL]ERROR: Could not SQLAllocStmt\n" );
free(szSepLine);
return 0;
}
}
n_args = get_args(szSQL, &args[0], sizeof(args) / sizeof(args[0]));
if (n_args == 2 )
{
if (strcmp(args[1], "help") == 0)
{
output_help();
free(szSepLine);
return 0;
}
/* COLUMNS */
nReturn = SQLColumns( hStmt, NULL, 0, NULL, 0, (SQLCHAR*)args[1], SQL_NTS, NULL, 0 );
if ( (nReturn != SQL_SUCCESS) && (nReturn != SQL_SUCCESS_WITH_INFO) )
{
if ( bVerbose ) DumpODBCLog( hEnv, hDbc, hStmt );
fprintf( stderr, "[ISQL]ERROR: Could not SQLColumns\n" );
SQLFreeStmt( hStmt, SQL_DROP );
free(szSepLine);
return 0;
}
}
else
{
SQLCHAR *catalog = NULL;
SQLCHAR *schema = NULL;
SQLCHAR *table = NULL;
SQLCHAR *type = NULL;
if (n_args > 2)
{
catalog = (SQLCHAR*)args[1];
schema = (SQLCHAR*)args[2];
table = (SQLCHAR*)args[3];
type = (SQLCHAR*)args[4];
}
/* TABLES */
nReturn = SQLTables( hStmt, catalog, SQL_NTS, schema, SQL_NTS,
table, SQL_NTS, type, SQL_NTS );
if ( (nReturn != SQL_SUCCESS) && (nReturn != SQL_SUCCESS_WITH_INFO) )
{
if ( bVerbose ) DumpODBCLog( hEnv, hDbc, hStmt );
fprintf( stderr, "[ISQL]ERROR: Could not SQLTables\n" );
SQLFreeStmt( hStmt, SQL_DROP );
free(szSepLine);
free_args(args, sizeof(args) / sizeof(args[0]));
return 0;
}
}
/****************************
* WRITE HEADER
***************************/
if ( bHTMLTable )
WriteHeaderHTMLTable( hStmt );
else if ( cDelimiter == 0 )
WriteHeaderNormal( hStmt, szSepLine );
else if ( cDelimiter && bColumnNames )
WriteHeaderDelimited( hStmt, cDelimiter );
/****************************
* WRITE BODY
***************************/
if ( bHTMLTable )
WriteBodyHTMLTable( hStmt );
else if ( cDelimiter == 0 )
nRows = WriteBodyNormal( hStmt );
else
WriteBodyDelimited( hStmt, cDelimiter );
/****************************
* WRITE FOOTER
***************************/
if ( bHTMLTable )
WriteFooterHTMLTable( hStmt );
else if ( cDelimiter == 0 )
WriteFooterNormal( hStmt, szSepLine, nRows );
/****************************
* CLEANUP
***************************/
SQLFreeStmt( hStmt, SQL_DROP );
free(szSepLine);
free_args(args, sizeof(args) / sizeof(args[0]));
return 1;
}
/* list tables that meet specified criteria */
int TbListTables(SQLHANDLE hdbc)
{
SQLRETURN cliRC = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt; /* statement handle */
struct
{
SQLINTEGER ind;
SQLCHAR val[129];
}
tbQualifier, tbSchema, tbName, tbType;
struct
{
SQLINTEGER ind;
SQLCHAR val[255];
}
tbRemarks;
/* criteria to look for */
SQLCHAR tbSchemaPattern[] = "%"; /* all the schemas */
SQLCHAR tbNamePattern[] = "ST%"; /* all the tables starting with ST */
printf("\n-----------------------------------------------------------");
printf("\nUSE THE CLI FUNCTIONS\n");
printf(" SQLSetConnectAttr\n");
printf(" SQLAllocHandle\n");
printf(" SQLTables\n");
printf(" SQLBindCol\n");
printf(" SQLFetch\n");
printf(" SQLFreeHandle\n");
printf("TO SHOW HOW TO LIST TABLES THAT MEET SPECIFIED CRITERIA:\n");
/* set AUTOCOMMIT on */
cliRC = SQLSetConnectAttr(hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_ON,
SQL_NTS);
DBC_HANDLE_CHECK(hdbc, cliRC);
/* allocate a statement handle */
cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
DBC_HANDLE_CHECK(hdbc, cliRC);
/* call SQLTables */
printf("\n Call SQLTables for:\n");
printf(" schemaPattern = %s\n", tbSchemaPattern);
printf(" namePattern = %s\n", tbNamePattern);
/* get table information */
cliRC = SQLTables(hstmt,
NULL,
0,
tbSchemaPattern,
SQL_NTS,
tbNamePattern,
SQL_NTS,
NULL,
0);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* bind column 1 to variable */
cliRC = SQLBindCol(hstmt,
1,
SQL_C_CHAR,
tbQualifier.val,
129,
&tbQualifier.ind);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* bind column 2 to variable */
cliRC = SQLBindCol(hstmt,
2,
SQL_C_CHAR,
tbSchema.val,
129,
&tbSchema.ind);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* bind column 3 to variable */
cliRC = SQLBindCol(hstmt,
3,
SQL_C_CHAR,
tbName.val,
129,
&tbName.ind);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* bind column 4 to variable */
cliRC = SQLBindCol(hstmt,
4,
SQL_C_CHAR,
tbType.val,
129,
&tbType.ind);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* bind column 5 to variable */
cliRC = SQLBindCol(hstmt,
5,
SQL_C_CHAR,
tbRemarks.val,
255,
&tbRemarks.ind);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* fetch each row and display */
printf("\n Fetch each row and display.\n");
printf(" TABLE SCHEMA TABLE_NAME TABLE_TYPE\n");
printf(" -------------- -------------- ----------\n");
/* fetch next row */
cliRC = SQLFetch(hstmt);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
if (cliRC == SQL_NO_DATA_FOUND)
{
printf("\n Data not found.\n");
}
while (cliRC != SQL_NO_DATA_FOUND)
{
printf(" %-14s %-14s %-11s\n",
tbSchema.val, tbName.val, tbType.val);
if (tbRemarks.ind > 0)
{
printf(" (Remarks) %s\n", tbRemarks.val);
}
/* fetch next row */
cliRC = SQLFetch(hstmt);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
}
/* free the statement handle */
cliRC = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
return rc;
} /* TbListTables */
PassFail TestMXSQLTables (TestInfo *pTestInfo)
{
TEST_DECLARE;
TCHAR Heading[MAX_HEADING_SIZE];
RETCODE returncode;
SQLHANDLE henv;
SQLHANDLE hdbc;
SQLHANDLE hstmt, hstmt1;
TCHAR TableQualifier[NAME_LEN],TableOwner[NAME_LEN],Remark[REM_LEN],*TableStr;
TCHAR oTableQualifier[NAME_LEN];
TCHAR oTableOwner[NAME_LEN];
TCHAR oTableName[NAME_LEN];
TCHAR oTableType[NAME_LEN];
TCHAR oRemark[REM_LEN];
SQLLEN oTableQualifierlen,oTableOwnerlen,oTableNamelen,oTableTypelen,oRemarklen;
int cols, iatt;
SWORD numOfCols = 0;
SWORD pcbDesc;
SQLLEN pfDesc;
TCHAR cn[TabType_LEN];
SWORD cl;
SWORD st;
SQLULEN cp;
SWORD cs, cnull;
TCHAR rgbDesc[RGB_MAX_LEN];
TCHAR *CharOutput[12];
SQLLEN stringlength;
struct
{
TCHAR *TableName;
TCHAR *TableType;
TCHAR *TableTypeIn;
} Table[] = {
{_T("--") ,_T("TABLE") ,_T("")},
{_T("--") ,_T("TABLE") ,_T("TABLE")},
{_T("--") ,_T("TABLE") ,_T("TABLE,VIEW,SYSTEM TABLE")},
{_T("--") ,_T("TABLE") ,_T("TABLE,SYSTEM TABLE,VIEW")},
{_T("--") ,_T("TABLE") ,_T("TABLE,VIEW,SYSTEM TABLE,SYNONYM,MV,MVG")},
{_T("--") ,_T("VIEW"),_T("VIEW")},
{_T("--") ,_T("VIEW"),_T("TABLE,VIEW,SYSTEM TABLE")},
{_T("--") ,_T("MATERIALIZED VIEW"),_T("")},
{_T("--") ,_T("MATERIALIZED VIEW"),_T("MV")},
{_T("--") ,_T("SYNONYM"),_T("")},
{_T("--") ,_T("SYNONYM"),_T("SYNONYM")},
{_T("endloop"),}
};
struct
{
TCHAR *TabQua;
TCHAR *TabOwner;
TCHAR *TabName;
TCHAR *TabType;
int changedSchema;
} TableWC[] =
{
{_T(SQL_ALL_CATALOGS),_T("") ,_T("--") ,_T("") ,-1}, //0
{_T("") ,_T(SQL_ALL_SCHEMAS),_T("--") ,_T("") ,-1}, //1
{_T("") ,_T("") ,_T("--") ,_T(SQL_ALL_TABLE_TYPES) ,-1}, //2
{pTestInfo->Catalog ,pTestInfo->Schema ,_T("--") ,_T("TABLE") ,-1}, //3
{pTestInfo->Catalog ,_T(SQL_ALL_SCHEMAS),_T("--") ,_T(SQL_ALL_TABLE_TYPES) ,-1}, //4
{pTestInfo->Catalog ,_T(SQL_ALL_SCHEMAS),_T("--") ,_T(SQL_ALL_TABLE_TYPES) ,-1}, //5
{pTestInfo->Catalog ,_T(SQL_ALL_SCHEMAS),_T("--") ,_T("TABLE") ,-1}, //6
{pTestInfo->Catalog ,_T(SQL_ALL_SCHEMAS),_T("--") ,_T(SQL_ALL_TABLE_TYPES) ,-1}, //7
{pTestInfo->Catalog ,_T(SQL_ALL_SCHEMAS),_T("--") ,_T(SQL_ALL_TABLE_TYPES) ,-1}, //8
{pTestInfo->Catalog ,_T(SQL_ALL_SCHEMAS),_T("--") ,_T("TABLE") ,-1}, //9
{pTestInfo->Catalog ,_T("--") ,_T("--") ,_T("TABLE") ,1}, //10
{pTestInfo->Catalog ,_T("--") ,_T("--") ,_T("TABLE") ,1}, //11
{pTestInfo->Catalog ,pTestInfo->Schema ,_T("--") ,_T("TABLE") ,-1}, //12
{pTestInfo->Catalog ,pTestInfo->Schema ,_T("--") ,_T("TABLE") ,-1}, //13
{pTestInfo->Catalog ,pTestInfo->Schema ,_T("--") ,_T(SQL_ALL_TABLE_TYPES) ,-1}, //14
{pTestInfo->Catalog ,pTestInfo->Schema ,_T("--") ,_T("TABLE") ,-1}, //15
{pTestInfo->Catalog ,pTestInfo->Schema ,_T("--") ,_T("TABLE") ,-1}, //16
{pTestInfo->Catalog ,pTestInfo->Schema ,_T("--") ,_T("TABLE") ,-1}, //17
{pTestInfo->Catalog ,pTestInfo->Schema ,_T("--") ,_T("TABLE") ,-1}, //18
{pTestInfo->Catalog ,_T(SQL_ALL_SCHEMAS),_T("--") ,_T(SQL_ALL_TABLE_TYPES) ,-1}, //19
{pTestInfo->Catalog ,_T(SQL_ALL_SCHEMAS),_T("--") ,_T("TABLE") ,-1}, //20
{pTestInfo->Catalog ,_T(SQL_ALL_SCHEMAS),_T("--") ,_T("TABLE") ,-1}, //21
{pTestInfo->Catalog ,_T("--") ,_T("--") ,_T("TABLE") ,1}, //22
{pTestInfo->Catalog ,_T(SQL_ALL_SCHEMAS),_T("--") ,_T("TABLE") ,-1}, //23
{pTestInfo->Catalog ,_T("--") ,_T("--") ,_T("TABLE") ,1}, //24
{pTestInfo->Catalog ,pTestInfo->Schema ,_T("--") ,_T("TABLE") ,-1}, //25
{pTestInfo->Catalog ,_T("--") ,_T("--") ,_T("TABLE") ,1}, //26
{pTestInfo->Catalog ,pTestInfo->Schema ,_T("--") ,_T("VIEW") ,-1}, //27
{pTestInfo->Catalog ,pTestInfo->Schema ,_T("--") ,_T("TABLE") ,-1}, //28
{pTestInfo->Catalog ,pTestInfo->Schema ,_T("--") ,_T(SQL_ALL_TABLE_TYPES) ,-1}, //29
{pTestInfo->Catalog ,pTestInfo->Schema ,_T("--") ,_T("TABLE") ,-1}, //30
{pTestInfo->Catalog ,pTestInfo->Schema ,_T("--") ,_T("VIEW") ,-1}, //31
{pTestInfo->Catalog ,pTestInfo->Schema ,_T("--") ,_T("TABLE") ,-1}, //32
{pTestInfo->Catalog ,pTestInfo->Schema ,_T("--") ,_T("TABLE") ,-1}, //33
{pTestInfo->Catalog ,pTestInfo->Schema ,_T("--") ,_T("TABLE") ,-1}, //34
{_T("NULL") ,pTestInfo->Schema ,_T("--") ,_T("TABLE") ,-1}, //35
{pTestInfo->Catalog ,pTestInfo->Schema ,_T("--") ,_T("TABLE") ,-1}, //36
{pTestInfo->Catalog ,pTestInfo->Schema ,_T("--") ,_T("TABLE") ,-1}, //37
{_T("NULL") ,_T("--") ,_T("--") ,_T("TABLE") ,0}, //38
{_T("NULL") ,pTestInfo->Schema ,_T("--") ,_T("TABLE") ,-1}, //39
{_T("NULL") ,_T("NULL") ,_T("--") ,_T("TABLE") ,-1}, //40
{pTestInfo->Catalog ,_T("--") ,_T("--") ,_T(SQL_ALL_TABLE_TYPES) ,0}, //41
{pTestInfo->Catalog ,_T("--") ,_T("--") ,_T(SQL_ALL_TABLE_TYPES) ,0}, //42
{pTestInfo->Catalog ,_T("--") ,_T("--") ,_T(SQL_ALL_TABLE_TYPES) ,2}, //43
{_T("endloop"),}
};
struct
{
TCHAR *TabQua;
SWORD TabQuaLen;
TCHAR *TabOwner;
SWORD TabOwnerLen;
TCHAR *TabName;
SWORD TabNameLen;
TCHAR *TabType;
SWORD TabTypeLen;
} TableWC2[] = { // wild cards from here
{pTestInfo->Catalog, (SWORD)-1, pTestInfo->Schema,(SWORD)-1, _T("--"),(SWORD)-1, _T(""), (SWORD)-1},
//{pTestInfo->Catalog, (SWORD)4, pTestInfo->Schema,(SWORD)2, "OBJECTS",(SWORD)2, _T(""), (SWORD)2},
//{pTestInfo->Catalog, (SWORD)3, pTestInfo->Schema,(SWORD)7, "OBJECTS",(SWORD)3, _T(""), (SWORD)0},
{_T("endloop"),}
};
//attributes for columns added for negative testing
UWORD DescrType[] =
{
SQL_COLUMN_AUTO_INCREMENT,SQL_COLUMN_CASE_SENSITIVE,SQL_COLUMN_COUNT,
SQL_COLUMN_DISPLAY_SIZE,SQL_COLUMN_LENGTH,SQL_COLUMN_MONEY,
SQL_COLUMN_NULLABLE,SQL_COLUMN_PRECISION,SQL_COLUMN_SCALE,
SQL_COLUMN_SEARCHABLE,SQL_COLUMN_TYPE,SQL_COLUMN_UNSIGNED,
SQL_COLUMN_UPDATABLE,SQL_COLUMN_NAME,SQL_COLUMN_TYPE_NAME,
SQL_COLUMN_OWNER_NAME,SQL_COLUMN_QUALIFIER_NAME,SQL_COLUMN_TABLE_NAME,
SQL_COLUMN_LABEL
};
struct {
TCHAR cat[STR_LEN];
TCHAR sch[STR_LEN];
TCHAR tab[STR_LEN];
TCHAR typ[STR_LEN];
} displayBuf;
int i = 0, k = 0, NullValue = 0;
TCHAR tmpSchema[129];
TCHAR schemaList[3][256];
int myInd=1;
int len = _tcslen(pTestInfo->Schema);
TCHAR *CrtCol[] = { _T("--"), _T("--") };
TCHAR *DrpTab[] = { _T("--"), _T("--"), _T("--")};
TCHAR *CrtTab[] = { _T("--"), _T("--"), _T("--")};
//===========================================================================================================
var_list_t *var_list;
var_list = load_api_vars(_T("SQLTables"), charset_file);
if (var_list == NULL) return FAILED;
Table[0].TableName = var_mapping(_T("SQLTables_Table_TableName_0"), var_list);
Table[1].TableName = var_mapping(_T("SQLTables_Table_TableName_1"), var_list);
Table[2].TableName = var_mapping(_T("SQLTables_Table_TableName_2"), var_list);
Table[3].TableName = var_mapping(_T("SQLTables_Table_TableName_3"), var_list);
Table[4].TableName = var_mapping(_T("SQLTables_Table_TableName_4"), var_list);
Table[5].TableName = var_mapping(_T("SQLTables_Table_TableName_5"), var_list);
Table[6].TableName = var_mapping(_T("SQLTables_Table_TableName_6"), var_list);
Table[7].TableName = var_mapping(_T("SQLTables_Table_TableName_7"), var_list);
Table[8].TableName = var_mapping(_T("SQLTables_Table_TableName_8"), var_list);
Table[9].TableName = var_mapping(_T("SQLTables_Table_TableName_9"), var_list);
Table[10].TableName = var_mapping(_T("SQLTables_Table_TableName_10"), var_list);
TableWC[10].TabOwner = var_mapping(_T("SQLTables_TableWC_TabOwner_10"), var_list);
TableWC[11].TabOwner = var_mapping(_T("SQLTables_TableWC_TabOwner_11"), var_list);
TableWC[22].TabOwner = var_mapping(_T("SQLTables_TableWC_TabOwner_22"), var_list);
TableWC[24].TabOwner = var_mapping(_T("SQLTables_TableWC_TabOwner_24"), var_list);
TableWC[26].TabOwner = var_mapping(_T("SQLTables_TableWC_TabOwner_26"), var_list);
TableWC[38].TabOwner = var_mapping(_T("SQLTables_TableWC_TabOwner_38"), var_list);
TableWC[41].TabOwner = var_mapping(_T("SQLTables_TableWC_TabOwner_10"), var_list);
TableWC[42].TabOwner = var_mapping(_T("SQLTables_TableWC_TabOwner_41"), var_list);
TableWC[43].TabOwner = var_mapping(_T("SQLTables_TableWC_TabOwner_43"), var_list);
TableWC[0].TabName = var_mapping(_T("SQLTables_TableWC_TabName_0"), var_list);
TableWC[1].TabName = var_mapping(_T("SQLTables_TableWC_TabName_1"), var_list);
TableWC[2].TabName = var_mapping(_T("SQLTables_TableWC_TabName_2"), var_list);
TableWC[3].TabName = var_mapping(_T("SQLTables_TableWC_TabName_3"), var_list);
TableWC[4].TabName = var_mapping(_T("SQLTables_TableWC_TabName_4"), var_list);
TableWC[5].TabName = var_mapping(_T("SQLTables_TableWC_TabName_5"), var_list);
TableWC[6].TabName = var_mapping(_T("SQLTables_TableWC_TabName_6"), var_list);
TableWC[7].TabName = var_mapping(_T("SQLTables_TableWC_TabName_7"), var_list);
TableWC[8].TabName = var_mapping(_T("SQLTables_TableWC_TabName_8"), var_list);
TableWC[9].TabName = var_mapping(_T("SQLTables_TableWC_TabName_9"), var_list);
TableWC[10].TabName = var_mapping(_T("SQLTables_TableWC_TabName_10"), var_list);
TableWC[11].TabName = var_mapping(_T("SQLTables_TableWC_TabName_11"), var_list);
TableWC[12].TabName = var_mapping(_T("SQLTables_TableWC_TabName_12"), var_list);
TableWC[13].TabName = var_mapping(_T("SQLTables_TableWC_TabName_13"), var_list);
TableWC[14].TabName = var_mapping(_T("SQLTables_TableWC_TabName_14"), var_list);
TableWC[15].TabName = var_mapping(_T("SQLTables_TableWC_TabName_15"), var_list);
TableWC[16].TabName = var_mapping(_T("SQLTables_TableWC_TabName_16"), var_list);
TableWC[17].TabName = var_mapping(_T("SQLTables_TableWC_TabName_17"), var_list);
TableWC[18].TabName = var_mapping(_T("SQLTables_TableWC_TabName_18"), var_list);
TableWC[19].TabName = var_mapping(_T("SQLTables_TableWC_TabName_19"), var_list);
TableWC[20].TabName = var_mapping(_T("SQLTables_TableWC_TabName_20"), var_list);
TableWC[21].TabName = var_mapping(_T("SQLTables_TableWC_TabName_21"), var_list);
TableWC[22].TabName = var_mapping(_T("SQLTables_TableWC_TabName_22"), var_list);
TableWC[23].TabName = var_mapping(_T("SQLTables_TableWC_TabName_23"), var_list);
TableWC[24].TabName = var_mapping(_T("SQLTables_TableWC_TabName_24"), var_list);
TableWC[25].TabName = var_mapping(_T("SQLTables_TableWC_TabName_25"), var_list);
TableWC[26].TabName = var_mapping(_T("SQLTables_TableWC_TabName_26"), var_list);
TableWC[27].TabName = var_mapping(_T("SQLTables_TableWC_TabName_27"), var_list);
TableWC[28].TabName = var_mapping(_T("SQLTables_TableWC_TabName_28"), var_list);
TableWC[29].TabName = var_mapping(_T("SQLTables_TableWC_TabName_29"), var_list);
TableWC[30].TabName = var_mapping(_T("SQLTables_TableWC_TabName_30"), var_list);
TableWC[31].TabName = var_mapping(_T("SQLTables_TableWC_TabName_31"), var_list);
TableWC[32].TabName = var_mapping(_T("SQLTables_TableWC_TabName_32"), var_list);
TableWC[33].TabName = var_mapping(_T("SQLTables_TableWC_TabName_33"), var_list);
TableWC[34].TabName = var_mapping(_T("SQLTables_TableWC_TabName_34"), var_list);
TableWC[35].TabName = var_mapping(_T("SQLTables_TableWC_TabName_35"), var_list);
TableWC[36].TabName = var_mapping(_T("SQLTables_TableWC_TabName_36"), var_list);
TableWC[37].TabName = var_mapping(_T("SQLTables_TableWC_TabName_37"), var_list);
TableWC[38].TabName = var_mapping(_T("SQLTables_TableWC_TabName_38"), var_list);
TableWC[39].TabName = var_mapping(_T("SQLTables_TableWC_TabName_39"), var_list);
TableWC[40].TabName = var_mapping(_T("SQLTables_TableWC_TabName_40"), var_list);
TableWC[41].TabName = var_mapping(_T("SQLTables_TableWC_TabName_41"), var_list);
TableWC[42].TabName = var_mapping(_T("SQLTables_TableWC_TabName_42"), var_list);
TableWC[43].TabName = var_mapping(_T("SQLTables_TableWC_TabName_43"), var_list);
TableWC2[0].TabName = var_mapping(_T("SQLTables_TableWC2_TabName_0"), var_list);
CrtCol[0] = var_mapping(_T("SQLTables_CrtCol_0"), var_list);
CrtCol[1] = var_mapping(_T("SQLTables_CrtCol_1"), var_list);
DrpTab[0] = var_mapping(_T("SQLTables_DrpTab_0"), var_list);
DrpTab[1] = var_mapping(_T("SQLTables_DrpTab_1"), var_list);
DrpTab[2] = var_mapping(_T("SQLTables_DrpTab_2"), var_list);
CrtTab[0] = var_mapping(_T("SQLTables_CrtTab_0"), var_list);
CrtTab[1] = var_mapping(_T("SQLTables_CrtTab_1"), var_list);
CrtTab[2] = var_mapping(_T("SQLTables_CrtTab_2"), var_list);
//===========================================================================================================
memset(schemaList[0],'\0', 256*sizeof(TCHAR));
memset(schemaList[1],'\0', 256*sizeof(TCHAR));
memset(schemaList[2],'\0', 256*sizeof(TCHAR));
myInd = len - 2;
if (len>5) myInd=4;
/*
#ifndef UNICODE
if (_tcscmp("English", _tsetlocale(LC_ALL, _T(""))) != 0) {
_mbsncpy(schemaList[0],pTestInfo->Schema,myInd);
_mbscat(schemaList[0], "%");
_mbsncpy(schemaList[1],pTestInfo->Schema,myInd+1);
_mbscat(schemaList[1], "%");
}
else {
#endif
*/
_tcsncpy(schemaList[0],pTestInfo->Schema,myInd);
_tcscat(schemaList[0], _T("%"));
_tcsncpy(schemaList[1],pTestInfo->Schema,myInd+1);
_tcscat(schemaList[1], _T("%"));
/*
#ifndef UNICODE
}
#endif
*/
len = 0; myInd = 0;
do {
if (pTestInfo->Schema[myInd] == '_')
schemaList[2][len++] = '\\';
schemaList[2][len++] = pTestInfo->Schema[myInd];
} while (pTestInfo->Schema[myInd++] != '\0');
LogMsg(NONE,_T("%s %s %s\n"), schemaList[0], schemaList[1], schemaList[2]);
if (isCharSet) {
_stprintf(tmpSchema, _T("\"SQLTABLES_%s\""), pTestInfo->Schema);
}
else {
_stprintf(tmpSchema, _T("SQLTABLES_%s"), pTestInfo->Schema);
}
_tcsupr(tmpSchema);
//======================================================================================================
LogMsg(LINEBEFORE+SHORTTIMESTAMP,_T("Begin testing API => MX SPECIFIC SQLTables.\n"));
TEST_INIT;
TESTCASE_BEGIN("Setup for SQLTables tests\n");
// for ODBC 3.0
if(!FullConnectWithOptions(pTestInfo, CONNECT_ODBC_VERSION_3))
{
LogMsg(NONE,_T("Unable to connect as ODBC3.0 application.\n"));
TEST_FAILED;
TEST_RETURN;
}
henv = pTestInfo->henv;
hdbc = pTestInfo->hdbc;
hstmt = (SQLHANDLE)pTestInfo->hstmt;
returncode = SQLAllocStmt((SQLHANDLE)hdbc, &hstmt);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLAllocStmt"))
{
TEST_FAILED;
LogAllErrors(henv,hdbc,hstmt);
}
else
{
LogMsg(NONE,_T("Allocate a stmt handle successfully.\n"));
}
TESTCASE_END;
_tcscpy(pTestInfo->Catalog, _T("TRAFODION"));
if (returncode == SQL_SUCCESS)
{
_stprintf(Heading,_T("drop schema %s.%s cascade"),pTestInfo->Catalog,tmpSchema);
returncode = SQLExecDirect(hstmt,(SQLTCHAR*)Heading, SQL_NTS);
_stprintf(Heading,_T("create schema %s.%s"),pTestInfo->Catalog,tmpSchema);
returncode = SQLExecDirect(hstmt,(SQLTCHAR*)Heading, SQL_NTS);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLExecDirect"))
{
LogAllErrors(henv,hdbc,hstmt);
TEST_FAILED;
TEST_RETURN;
}
_stprintf(Heading,_T("set schema %s.%s"),pTestInfo->Catalog,tmpSchema);
returncode = SQLExecDirect(hstmt,(SQLTCHAR*)Heading, SQL_NTS);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLExecDirect"))
{
LogAllErrors(henv,hdbc,hstmt);
TEST_FAILED;
TEST_RETURN;
}
_tcscpy(TableQualifier,pTestInfo->Catalog);
_tcscpy(TableOwner, tmpSchema);
_tcscpy(Remark,_T(""));
TableStr = (TCHAR *)malloc(MAX_NOS_SIZE);
i = 0;
while (_tcsicmp(Table[i].TableName,_T("endloop")) != 0) // cleanup
{
_tcscpy(TableStr,_T(""));
_tcscat(TableStr,_T("drop "));
_tcscat(TableStr,Table[i].TableType);
_tcscat(TableStr,_T(" "));
_tcscat(TableStr,Table[i].TableName);
_tcscat(TableStr,_T(" cascade"));
LogMsg(NONE,_T("Cleanup: %s\n"),TableStr);
returncode = SQLExecDirect(hstmt,(SQLTCHAR*) TableStr,SQL_NTS);
i++;
}
i = 0;
while (_tcsicmp(Table[i].TableName,_T("endloop")) != 0)
{
_tcscpy(TableStr,_T(""));
_tcscat(TableStr,_T("create "));
_tcscat(TableStr,Table[i].TableType);
_tcscat(TableStr,_T(" "));
_tcscat(TableStr,Table[i].TableName);
if (_tcsicmp(Table[i].TableType,_T("VIEW")) == 0) {
_tcscat(TableStr,CrtCol[1]);
_tcscat(TableStr, Table[0].TableName);
}
else if (_tcsicmp(Table[i].TableType,_T("MATERIALIZED VIEW")) == 0) {
_tcscat(TableStr, _T(" REFRESH ON REQUEST INITIALIZE ON REFRESH "));
_tcscat(TableStr,CrtCol[1]);
_tcscat(TableStr, Table[0].TableName);
}
else if (_tcsicmp(Table[i].TableType,_T("SYNONYM")) == 0) {
_tcscat(TableStr, _T(" FOR "));
_tcscat(TableStr, Table[0].TableName);
}
else
{
_tcscat(TableStr,CrtCol[0]);
}
_stprintf(Heading,_T("SQLTables: Test %d: using the following table create command=> \n"),i);
_tcscat(Heading,TableStr);
_tcscat(Heading,_T("\n"));
TESTCASE_BEGINW(Heading);
returncode = SQLExecDirect(hstmt,(SQLTCHAR*)TableStr,SQL_NTS);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLExecDirect"))
{
LogAllErrors(henv,hdbc,hstmt);
TEST_FAILED;
}
else
{
LogMsg(NONE,_T("Created table successfully.\n"));
}
if(isCharSet) {
returncode = SQLSetStmtAttr(hstmt,SQL_ATTR_METADATA_ID,(SQLPOINTER)SQL_TRUE,0);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLSetStmtAttr(SQL_ATTR_METADATA_ID"))
{
TEST_FAILED;
LogAllErrors(henv,hdbc,hstmt);
}
}
if (returncode == SQL_SUCCESS)
{
if (_tcslen(TableQualifier) > 0)
returncode = SQLTables(hstmt,(SQLTCHAR*)TableQualifier,(SWORD)_tcslen(TableQualifier),(SQLTCHAR*)TableOwner,(SWORD)_tcslen(TableOwner),(SQLTCHAR*)Table[i].TableName,(SWORD)_tcslen(Table[i].TableName),(SQLTCHAR*)Table[i].TableTypeIn,(SWORD)_tcslen(Table[i].TableTypeIn));
else
returncode = SQLTables(hstmt,NULL,(SWORD)_tcslen(TableQualifier),(SQLTCHAR*)TableOwner,(SWORD)_tcslen(TableOwner),(SQLTCHAR*)Table[i].TableName,(SWORD)_tcslen(Table[i].TableName),(SQLTCHAR*)Table[i].TableTypeIn,(SWORD)_tcslen(Table[i].TableTypeIn));
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLTables"))
{
TEST_FAILED;
LogAllErrors(henv,hdbc,hstmt);
}
else
{
LogMsg(NONE,_T("Call to SQLTables() executed successfully.\n"));
_tcscpy(oTableQualifier,_T(""));
_tcscpy(oTableOwner,_T(""));
_tcscpy(oTableName,_T(""));
_tcscpy(oTableType,_T(""));
_tcscpy(oRemark,_T(""));
returncode=SQLBindCol(hstmt,1,SQL_C_TCHAR,oTableQualifier,NAME_LEN,&oTableQualifierlen);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLBindCol")){
LogAllErrors(henv,hdbc,hstmt);
TEST_FAILED;
}
returncode=SQLBindCol(hstmt,2,SQL_C_TCHAR,oTableOwner,NAME_LEN,&oTableOwnerlen);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLBindCol")){
LogAllErrors(henv,hdbc,hstmt);
TEST_FAILED;
}
returncode=SQLBindCol(hstmt,3,SQL_C_TCHAR,oTableName,NAME_LEN,&oTableNamelen);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLBindCol")){
LogAllErrors(henv,hdbc,hstmt);
TEST_FAILED;
}
returncode=SQLBindCol(hstmt,4,SQL_C_TCHAR,oTableType,NAME_LEN,&oTableTypelen);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLBindCol")){
LogAllErrors(henv,hdbc,hstmt);
TEST_FAILED;
}
returncode=SQLBindCol(hstmt,5,SQL_C_TCHAR,oRemark,NAME_LEN,&oRemarklen);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLBindCol")){
LogAllErrors(henv,hdbc,hstmt);
TEST_FAILED;
}
k = 0;
while (returncode == SQL_SUCCESS)
{
returncode = SQLFetch(hstmt);
if((returncode != SQL_NO_DATA_FOUND)&&(!CHECKRC(SQL_SUCCESS,returncode,"SQLFetch")))
{
LogAllErrors(henv,hdbc,hstmt);
TEST_FAILED;
}
else if (returncode == SQL_SUCCESS)
{
if (_tcslen(TableQualifier) == 0)
{
//_tcscpy(TableQualifier, _T("TRAFODION"));
_tcscpy(TableQualifier, _T("TRAFODION"));
}
if ((_tcsicmp(TableQualifier,oTableQualifier) == 0)
&& (cwcscmp(TableOwner,oTableOwner,TRUE) == 0)
&& (cwcscmp(Table[i].TableName,oTableName,TRUE) == 0)
&& (_tcsnicmp(Table[i].TableType,oTableType,_tcslen(Table[i].TableType)) == 0)
&& (_tcsicmp(Remark,oRemark) == 0))
{
LogMsg(NONE,_T("TableQualifier expect: '%s' and actual: '%s' are matched\n"),(SQLTCHAR*)TableQualifier,oTableQualifier);
LogMsg(NONE,_T("TableOwner expect: '%s' and actual: '%s' are matched\n"),(SQLTCHAR*)TableOwner,oTableOwner);
LogMsg(NONE,_T("TableName expect: '%s' and actual: '%s' are matched\n"),(SQLTCHAR*)Table[i].TableName,oTableName);
LogMsg(NONE,_T("TableType expect: '%s' and actual: '%s' are matched\n"),Table[i].TableType,oTableType);
LogMsg(NONE,_T("Remark expect: '%s' and actual: '%s' are matched\n"),Remark,oRemark);
}
else
{
TEST_FAILED;
if (_tcsicmp(TableQualifier,oTableQualifier) != 0)
LogMsg(ERRMSG,_T("TableQualifier expect: '%s' and actual: '%s' are not matched\n"),(SQLTCHAR*)TableQualifier,oTableQualifier);
if (cwcscmp(TableOwner,oTableOwner,TRUE) != 0)
LogMsg(ERRMSG,_T("TableOwner expect: '%s' and actual: '%s' are not matched\n"),(SQLTCHAR*)TableOwner,oTableOwner);
if (cwcscmp(Table[i].TableName,oTableName,TRUE) != 0)
LogMsg(ERRMSG,_T("TableName expect: '%s' and actual: '%s' are not matched\n"),(SQLTCHAR*)Table[i].TableName,oTableName);
if (_tcsnicmp(Table[i].TableType,oTableType,10) != 0)
LogMsg(ERRMSG,_T("TableType expect: '%s' and actual: '%s' are not matched\n"),Table[i].TableType,oTableType);
if (_tcsicmp(Remark,oRemark) != 0)
LogMsg(ERRMSG,_T("Remark expect: '%s' and actual: '%s' are not matched\n"),Remark,oRemark);
}
k++;
}
}//End while loop
if(k != 1)
{
TEST_FAILED;
LogMsg(ERRMSG,_T("Only one row should be fetched, the actual number of rows fetched: %d\n"), k);
} else {
LogMsg(NONE, _T("Number of rows fetched: %d\n"), k);
}
}
SQLFreeStmt(hstmt,SQL_UNBIND);
SQLFreeStmt(hstmt,SQL_CLOSE);
}
TESTCASE_END;
i++;
}
// i = 0;
// while (_tcsicmp(Table[i].TableName,"endloop") != 0) // cleanup
// {
// _tcscpy(TableStr,_T(""));
// _tcscat(TableStr,"drop ");
// _tcscat(TableStr,Table[i].TableType);
// _tcscat(TableStr," ");
// _tcscat(TableStr,Table[i].TableName);
// SQLExecDirect(hstmt,(SQLTCHAR*) TableStr,SQL_NTS);
// i++;
// }
_stprintf(Heading,_T("drop schema %s.%s cascade"),pTestInfo->Catalog,tmpSchema);
returncode = SQLExecDirect(hstmt,(SQLTCHAR*)Heading, SQL_NTS);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLExecDirect"))
{
LogAllErrors(henv,hdbc,hstmt);
TEST_FAILED;
TEST_RETURN;
}
if (isCharSet) {
_stprintf(Heading,_T("set schema %s.\"%s\""),pTestInfo->Catalog,pTestInfo->Schema);
}
else {
_stprintf(Heading,_T("set schema %s.%s"),pTestInfo->Catalog,pTestInfo->Schema);
}
returncode = SQLExecDirect(hstmt,(SQLTCHAR*)Heading, SQL_NTS);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLExecDirect"))
{
LogAllErrors(henv,hdbc,hstmt);
TEST_FAILED;
TEST_RETURN;
}
//======================================================================================================
_stprintf(Heading,_T("Setting up Table & view to test SQLTables for wildcard options => \n"));
_tcscat(Heading,_T("\n"));
TESTCASE_BEGINW(Heading);
SQLExecDirect(hstmt,(SQLTCHAR*)DrpTab[0],SQL_NTS);
SQLExecDirect(hstmt,(SQLTCHAR*)DrpTab[1],SQL_NTS);
SQLExecDirect(hstmt,(SQLTCHAR*)DrpTab[2],SQL_NTS);
LogMsg(NONE,_T("%s\n"), CrtTab[0]);
returncode = SQLExecDirect(hstmt,(SQLTCHAR*)CrtTab[0],SQL_NTS);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLExecDirect"))
{
LogAllErrors(henv,hdbc,hstmt);
TEST_FAILED;
}
else
{
returncode = SQLExecDirect(hstmt,(SQLTCHAR*)CrtTab[1],SQL_NTS);
LogMsg(NONE,_T("%s\n"), CrtTab[1]);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLExecDirect"))
{
LogAllErrors(henv,hdbc,hstmt);
TEST_FAILED;
}
else
{
LogMsg(NONE,_T("setting up tables & views executed successfully.\n"));
TESTCASE_END;
}
}
returncode = SQLExecDirect(hstmt,(SQLTCHAR*)CrtTab[2],SQL_NTS);
LogMsg(NONE,_T("%s\n"), CrtTab[2]);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLExecDirect"))
{
LogAllErrors(henv,hdbc,hstmt);
TEST_FAILED;
}
i = 0;
while (_tcsicmp(TableWC[i].TabQua,_T("endloop")) != 0)
{
if(TableWC[i].changedSchema == -1) {
printSymbol(TableWC[i].TabQua,displayBuf.cat);
printSymbol(TableWC[i].TabOwner,displayBuf.sch);
printSymbol(TableWC[i].TabName,displayBuf.tab);
printSymbol(TableWC[i].TabType,displayBuf.typ);
_stprintf(Heading,_T("Test Positive Functionality of SQLTables for following wildcard options => \n TableQualifier: %s\n TableOwner: %s\n TableName: %s\n TableType: %s\n"),
displayBuf.cat, displayBuf.sch, displayBuf.tab, displayBuf.typ);
}
else {
printSymbol(TableWC[i].TabQua,displayBuf.cat);
printSymbol(schemaList[TableWC[i].changedSchema],displayBuf.sch);
printSymbol(TableWC[i].TabName,displayBuf.tab);
printSymbol(TableWC[i].TabType,displayBuf.typ);
_stprintf(Heading,_T("Test Positive Functionality of SQLTables for following wildcard options => \n TableQualifier: %s\n TableOwner: %s\n TableName: %s\n TableType: %s\n"),
displayBuf.cat, displayBuf.sch, displayBuf.tab, displayBuf.typ);
}
TESTCASE_BEGINW(Heading);
NullValue = 0;
returncode = SQLSetStmtAttr(hstmt,SQL_ATTR_METADATA_ID,(SQLPOINTER)SQL_FALSE,0);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLSetStmtAttr(SQL_ATTR_METADATA_ID"))
{
TEST_FAILED;
LogAllErrors(henv,hdbc,hstmt);
}
if (_tcsicmp(TableWC[i].TabType,_T("NULL")) == 0)
{
TableWC[i].TabType = NULL;
NullValue = 1;
returncode = SQLTables(hstmt,NULL,0,NULL,0,NULL,0,NULL,0);
}
else if (_tcsicmp(TableWC[i].TabName,_T("NULL")) == 0)
{
TableWC[i].TabName = NULL;
NullValue = 1;
returncode = SQLTables(hstmt,NULL,0,NULL,0,NULL,0,(SQLTCHAR*)TableWC[i].TabType,(SWORD)_tcslen(TableWC[i].TabType));
}
else if (_tcsicmp(TableWC[i].TabOwner,_T("NULL")) == 0)
{
TableWC[i].TabOwner = NULL;
NullValue = 1;
returncode = SQLTables(hstmt,NULL,0,NULL,0,(SQLTCHAR*)removeQuotes(TableWC[i].TabName,displayBuf.tab),(SWORD)_tcslen(displayBuf.tab),(SQLTCHAR*)TableWC[i].TabType,(SWORD)_tcslen(TableWC[i].TabType));
}
else if (_tcsicmp(TableWC[i].TabQua,_T("NULL")) == 0)
{
TableWC[i].TabQua = NULL;
NullValue = 1;
if (TableWC[i].changedSchema == -1)
returncode = SQLTables(hstmt,NULL,0,(SQLTCHAR*)TableWC[i].TabOwner,(SWORD)_tcslen(TableWC[i].TabOwner),(SQLTCHAR*)removeQuotes(TableWC[i].TabName,displayBuf.tab),(SWORD)_tcslen(displayBuf.tab),(SQLTCHAR*)TableWC[i].TabType,(SWORD)_tcslen(TableWC[i].TabType));
else
returncode = SQLTables(hstmt,NULL,0,(SQLTCHAR*)schemaList[TableWC[i].changedSchema],(SWORD)_tcslen(schemaList[TableWC[i].changedSchema]),(SQLTCHAR*)removeQuotes(TableWC[i].TabName,displayBuf.tab),(SWORD)_tcslen(displayBuf.tab),(SQLTCHAR*)TableWC[i].TabType,(SWORD)_tcslen(TableWC[i].TabType));
}
else
{
if (TableWC[i].changedSchema == -1)
returncode = SQLTables(hstmt,(SQLTCHAR*)TableWC[i].TabQua,(SWORD)_tcslen(TableWC[i].TabQua),(SQLTCHAR*)TableWC[i].TabOwner,(SWORD)_tcslen(TableWC[i].TabOwner),(SQLTCHAR*)removeQuotes(TableWC[i].TabName,displayBuf.tab),(SWORD)_tcslen(displayBuf.tab),(SQLTCHAR*)TableWC[i].TabType,(SWORD)_tcslen(TableWC[i].TabType));
else
returncode = SQLTables(hstmt,(SQLTCHAR*)TableWC[i].TabQua,(SWORD)_tcslen(TableWC[i].TabQua),(SQLTCHAR*)schemaList[TableWC[i].changedSchema],(SWORD)_tcslen(schemaList[TableWC[i].changedSchema]),(SQLTCHAR*)removeQuotes(TableWC[i].TabName,displayBuf.tab),(SWORD)_tcslen(displayBuf.tab),(SQLTCHAR*)TableWC[i].TabType,(SWORD)_tcslen(TableWC[i].TabType));
}
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLTables"))
{
TEST_FAILED;
LogAllErrors(henv,hdbc,hstmt);
}
else
{
LogMsg(NONE,_T("Call to SQLTables() executed successfully.\n"));
_tcscpy(oTableQualifier,_T(""));
_tcscpy(oTableOwner,_T(""));
_tcscpy(oTableName,_T(""));
_tcscpy(oTableType,_T(""));
_tcscpy(oRemark,_T(""));
returncode=SQLBindCol(hstmt,1,SQL_C_TCHAR,oTableQualifier,NAME_LEN,&oTableQualifierlen);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLBindCol"))
{
LogAllErrors(henv,hdbc,hstmt);
TEST_FAILED;
}
returncode=SQLBindCol(hstmt,2,SQL_C_TCHAR,oTableOwner,NAME_LEN,&oTableOwnerlen);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLBindCol"))
{
LogAllErrors(henv,hdbc,hstmt);
TEST_FAILED;
}
returncode=SQLBindCol(hstmt,3,SQL_C_TCHAR,oTableName,NAME_LEN,&oTableNamelen);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLBindCol"))
{
LogAllErrors(henv,hdbc,hstmt);
TEST_FAILED;
}
returncode=SQLBindCol(hstmt,4,SQL_C_TCHAR,oTableType,NAME_LEN,&oTableTypelen);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLBindCol"))
{
LogAllErrors(henv,hdbc,hstmt);
TEST_FAILED;
}
returncode=SQLBindCol(hstmt,5,SQL_C_TCHAR,oRemark,NAME_LEN,&oRemarklen);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLBindCol"))
{
LogAllErrors(henv,hdbc,hstmt);
TEST_FAILED;
}
k = 0;
while (returncode == SQL_SUCCESS)
{
returncode = SQLFetch(hstmt);
if((returncode!=SQL_NO_DATA_FOUND) && (!CHECKRC(SQL_SUCCESS,returncode,"SQLFetch")))
{
LogAllErrors(henv,hdbc,hstmt);
TEST_FAILED;
}
if (returncode == SQL_SUCCESS)
k++;
}
if(k == 0)
{
TEST_FAILED;
LogMsg(ERRMSG,_T("No Data Found => Atleast one row should be fetched\n"));
} else {
LogMsg(NONE, _T("Number of rows fetched: %d\n"), k);
}
}
SQLFreeStmt(hstmt,SQL_CLOSE);
i++;
TESTCASE_END;
}
free(TableStr);
}
//=========================================================================================
TESTCASE_BEGIN("SQLTables: Negative test with NULL handle.\n");
hstmt1 = (SQLHANDLE)NULL;
i = 0;
returncode = SQLTables(hstmt1,(SQLTCHAR*)TableWC[i].TabQua,(SWORD)_tcslen(TableWC[i].TabQua),(SQLTCHAR*)TableWC[i].TabOwner,(SWORD)_tcslen(TableWC[i].TabOwner),(SQLTCHAR*)TableWC[i].TabName,(SWORD)_tcslen(TableWC[i].TabName),(SQLTCHAR*)TableWC[i].TabType,(SWORD)_tcslen(TableWC[i].TabType));
if(!CHECKRC(SQL_INVALID_HANDLE,returncode,"SQLTables"))
{
TEST_FAILED;
LogAllErrors(henv,hdbc,hstmt);
}
TESTCASE_END;
//=========================================================================================
TESTCASE_BEGIN("SQLTables: Negative test with invalid arg lengths.\n");
i = 0;
while (_tcsicmp(TableWC2[i].TabQua,_T("endloop")) != 0)
{
returncode = SQLTables(hstmt,(SQLTCHAR*)TableWC2[i].TabQua,TableWC2[i].TabQuaLen,(SQLTCHAR*)TableWC2[i].TabOwner,TableWC2[i].TabOwnerLen,(SQLTCHAR*)TableWC2[i].TabName,TableWC2[i].TabNameLen,(SQLTCHAR*)TableWC2[i].TabType,TableWC2[i].TabTypeLen);
if(!CHECKRC(SQL_ERROR,returncode,"SQLTables"))
{
TEST_FAILED;
LogAllErrors(henv,hdbc,hstmt);
}
i++;
}
TESTCASE_END;
//=========================================================================================
TESTCASE_BEGIN("Testing SQLColAttribute, SQLDescribeCol, SQLBindCol and SQLFetch functions for catalog names.\n");
if(isCharSet) {
returncode = SQLSetStmtAttr(hstmt,SQL_ATTR_METADATA_ID,(SQLPOINTER)SQL_FALSE,0);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLSetStmtAttr(SQL_ATTR_METADATA_ID"))
{
TEST_FAILED;
LogAllErrors(henv,hdbc,hstmt);
}
}
for(i = 0; i < 5; i++)
{
returncode = SQLTables(hstmt,(SQLTCHAR*)TableWC[i].TabQua,(SWORD)_tcslen(TableWC[i].TabQua),(SQLTCHAR*)TableWC[i].TabOwner,(SWORD)_tcslen(TableWC[i].TabOwner),(SQLTCHAR*)TableWC[i].TabName,(SWORD)_tcslen(TableWC[i].TabName),(SQLTCHAR*)TableWC[i].TabType,(SWORD)_tcslen(TableWC[i].TabType));
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLColumns"))
{
TEST_FAILED;
LogAllErrors(henv,hdbc,hstmt);
}
returncode = SQLNumResultCols(hstmt, &numOfCols);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLNumResultsCol"))
{
TEST_FAILED;
LogMsg(ERRMSG,_T("Test failed while executing call for SQLNUMRESULTSCOL"));
LogAllErrors(henv,hdbc,hstmt);
}
for(cols = 0; cols < numOfCols; cols++)
{
returncode = SQLDescribeCol(hstmt,(SWORD)(cols+1),(SQLTCHAR*)cn,TabType_LEN,&cl,&st,&cp,&cs,&cnull);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLDescribeCol"))
{
TEST_FAILED;
LogMsg(ERRMSG,_T("Test failed while executing call for SQLDESCRIBECOL of column"));
LogAllErrors(henv,hdbc,hstmt);
}
CharOutput[cols] = (TCHAR *)malloc(STR_LEN);
for (iatt = 0; iatt <= TOTALATTRIBS; iatt++)
{
_tcscpy(rgbDesc,_T(""));
pcbDesc = 0;
pfDesc = 0;
returncode = SQLColAttributes(hstmt,(SWORD)(cols+1),DescrType[iatt],(SQLTCHAR*)rgbDesc,STR_LEN,&pcbDesc,&pfDesc);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLColAttribute"))
{
TEST_FAILED;
LogAllErrors(henv,hdbc,hstmt);
}
}
returncode = SQLBindCol(hstmt,(SWORD)(cols+1),SQL_C_TCHAR,(SQLTCHAR*)CharOutput[cols],STR_LEN,&stringlength);
if(!CHECKRC(SQL_SUCCESS,returncode,"SQLBindCol"))
{
TEST_FAILED;
LogMsg(ERRMSG,_T("Test failed while executing call for SQLBindCols of column : %d.\n"),cols);
LogAllErrors(henv,hdbc,hstmt);
}
}
k = 0;
while (returncode == SQL_SUCCESS)
{
returncode = SQLFetch(hstmt);
if((returncode!=SQL_NO_DATA_FOUND) && (!CHECKRC(SQL_SUCCESS,returncode,"SQLFetch")))
{
LogAllErrors(henv,hdbc,hstmt);
TEST_FAILED;
}
if (returncode == SQL_SUCCESS)
k++;
}
if(k == 0)
{
_stprintf(Heading,_T("\n TableQualifier: %s\n TableOwner: %s\n TableName: %s\n TableType: %s\n"),
printSymbol(TableWC[i].TabQua,displayBuf.cat),
printSymbol(TableWC[i].TabOwner,displayBuf.sch),
printSymbol(TableWC[i].TabName,displayBuf.tab),
printSymbol(TableWC[i].TabType,displayBuf.typ));
LogMsg(NONE,Heading);
TEST_FAILED;
LogMsg(ERRMSG,_T("No Data Found => Atleast one row should be fetched\n"));
} else {
LogMsg(NONE, _T("Number of rows fetched: %d\n"), k);
}
for(cols = 0; cols < numOfCols; cols++)
{
free(CharOutput[cols]);
}
TESTCASE_END;
}
// Clean up
SQLExecDirect(hstmt,(SQLTCHAR*)DrpTab[0],SQL_NTS);
SQLExecDirect(hstmt,(SQLTCHAR*)DrpTab[1],SQL_NTS);
SQLExecDirect(hstmt,(SQLTCHAR*)DrpTab[2],SQL_NTS);
//=========================================================================================
FullDisconnect(pTestInfo);
LogMsg(SHORTTIMESTAMP+LINEAFTER,_T("End testing API => SQLTables.\n"));
free_list(var_list);
TEST_RETURN;
}
void getCatalogs()
{
printf("getCatalogs\n");
SQLHENV env;
SQLHDBC dbc;
SQLRETURN ret; /* ODBC API return status */
SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
/* We want ODBC 3 support */
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
ret = SQLDriverConnect(dbc, NULL, inString, SQL_NTS, outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_COMPLETE);
// ret = SQLConnect(dbc, "DSNtestPostgres", SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS);
if (SQL_SUCCEEDED(ret)) { printf("connected. Returned connection string was:\n\t%s\n", outstr);
if (ret == SQL_SUCCESS_WITH_INFO) {
printf("Driver reported the following diagnostics\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
}
} else {
printf("Failed to connect\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}
/*-------------------------------------------------*/
SQLHSTMT stmt;
SQLSMALLINT columns; /* number of columns in result-set */
SQLCHAR buf[5][64];
SQLINTEGER indicator[ 5 ];
int i;
/* Allocate a statement handle */
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
/* Retrieve a list of tables */
SQLTables(stmt, (SQLCHAR*)SQL_ALL_CATALOGS, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS );
/* How many columns are there */
SQLNumResultCols(stmt, &columns);
/* Loop through the rows in the result-set binding to */
/* local variables */
for (i = 0; i < columns; i++) {
SQLBindCol( stmt, i + 1, SQL_C_CHAR, buf[i], sizeof(buf[i]), (SQLLEN*)&indicator[i]);
}
/* Fetch the data */
while (SQL_SUCCEEDED(SQLFetch(stmt))) {
/* display the results that will now be in the bound area's */
for ( i = 0; i < columns; i ++ ) {
if (indicator[ i ] == SQL_NULL_DATA) {
printf(" Column %u : NULL\n", i);
}
else {
if (strlen((char*)(buf[i])) > 0) {
printf(" Column %u : %s\n", i, buf[i]);
} else {
printf(" Column %u : Empty string\n", i);
}
}
}
printf("\n");
}
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
/*-------------------------------------------------*/
/* free up allocated handles */
SQLDisconnect(dbc);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}
int db__driver_drop_table(dbString * name)
{
char cmd[200];
cursor *c;
SQLRETURN ret;
char msg[OD_MSG];
char *emsg = NULL;
SQLINTEGER err;
SQLCHAR ttype[50], *tname;
SQLINTEGER nrow = 0;
/* allocate cursor */
c = alloc_cursor();
if (c == NULL)
return DB_FAILED;
tname = db_get_string(name);
ret = SQLTables(c->stmt, NULL, 0, NULL, 0, tname, sizeof(tname), NULL, 0);
if ((ret != SQL_SUCCESS) && (ret != SQL_SUCCESS_WITH_INFO)) {
report_error("SQLTables()");
return DB_FAILED;
}
/* Get number of rows */
ret = SQLRowCount(c->stmt, &nrow);
if ((ret != SQL_SUCCESS) && (ret != SQL_SUCCESS_WITH_INFO)) {
report_error("SQLRowCount()");
return DB_FAILED;
}
if (nrow == 0) {
G_asprintf(&emsg, "Table %s doesn't exist\n", tname);
report_error(emsg);
G_free(emsg);
return DB_FAILED;
}
ret = SQLFetchScroll(c->stmt, SQL_FETCH_NEXT, 0);
ret = SQLGetData(c->stmt, 4, SQL_C_CHAR, ttype, sizeof(ttype), NULL);
if (strcmp(ttype, "TABLE") == 0) {
sprintf(cmd, "DROP TABLE %s", tname);
}
else if (strcmp(ttype, "VIEW") == 0) {
sprintf(cmd, "DROP VIEW %s", tname);
}
else {
G_asprintf(&emsg, "Table %s isn't 'TABLE' or 'VIEW' but %s\n", tname,
ttype);
report_error(emsg);
G_free(emsg);
return DB_FAILED;
}
SQLCloseCursor(c->stmt);
ret = SQLExecDirect(c->stmt, cmd, SQL_NTS);
if ((ret != SQL_SUCCESS) && (ret != SQL_SUCCESS_WITH_INFO)) {
SQLGetDiagRec(SQL_HANDLE_STMT, c->stmt, 1, NULL, &err, msg,
sizeof(msg), NULL);
G_asprintf(&emsg, "SQLExecDirect():\n%s\n%s (%d)\n", cmd, msg,
(int)err);
report_error(emsg);
G_free(emsg);
return DB_FAILED;
}
free_cursor(c);
return DB_OK;
}
void get_foreign_keys()
{
printf("get_foreign_keys\n");
SQLHENV env;
SQLHDBC dbc;
SQLRETURN ret; /* ODBC API return status */
SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
/* We want ODBC 3 support */
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
ret = SQLDriverConnect(dbc, NULL, inString, SQL_NTS, outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_COMPLETE);
if (SQL_SUCCEEDED(ret)) { printf("connected. Returned connection string was:\n\t%s\n", outstr);
if (ret == SQL_SUCCESS_WITH_INFO) {
printf("Driver reported the following diagnostics\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
}
} else {
printf("Failed to connect\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}
/*-------------------------------------------------*/
SQLHSTMT stmt;
SQLSMALLINT columns; /* number of columns in result-set */
SQLCHAR bufChar[10][64];
SQLSMALLINT bufSmallInt[4];
SQLINTEGER indicator[5];
ret = SQLAllocStmt(dbc, &stmt);
if (ret != SQL_SUCCESS) {
extract_error("SQLAllocStmt", dbc, SQL_HANDLE_DBC); return;
}
SQLTables(stmt, NULL, SQL_NTS, (SQLCHAR*)"installdb3", SQL_NTS, (SQLCHAR*)"", SQL_NTS, (SQLCHAR*)"TABLE", SQL_NTS );
/* How many columns are there */
SQLNumResultCols(stmt, &columns);
/* Loop through the rows in the result-set binding to */
/* local variables */
int i;
for (i = 0; i < columns; i++) {
SQLBindCol( stmt, i + 1, SQL_C_CHAR, bufChar[i], sizeof( bufChar[i] ), &indicator[i] );
}
/* Fetch the data */
while (SQL_SUCCEEDED(SQLFetch(stmt))) {
/* display the results that will now be in the bound area's */
for ( i = 0; i < columns; i ++ ) {
if (indicator[ i ] == SQL_NULL_DATA) {
// printf(" Column %u : NULL\n", i);
}
else {
if (strlen((char*)(bufChar[i])) > 0) {
printf(" Column %u : %s\n", i, bufChar[i]);
}
else {
// printf(" Column %u : Empty string\n", i);
}
}
}
printf("\n");
}
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
/*----------------------------------*/
SQLCHAR errormsg[SQL_MAX_MESSAGE_LENGTH];
ret = SQLAllocStmt(dbc, &stmt);
if (ret != SQL_SUCCESS) {
extract_error("SQLAllocStmt", dbc, SQL_HANDLE_DBC); return;
}
ret = SQLForeignKeys(stmt,
NULL, 0, /* Primary catalog */
NULL, 0, /* Primary schema */
NULL, 0, /* Primary table */
(SQLCHAR*)"instaldb123", SQL_NTS, /* Foreign catalog */
(SQLCHAR*)"instaldb3", SQL_NTS, /* Foreign schema */
(SQLCHAR*)"eventtypes", SQL_NTS /* Foreign table */
);
if (ret != SQL_SUCCESS) {
printf("error: %s\n", dbErrorMsg(env, dbc, stmt, &errormsg[0], SQL_MAX_MESSAGE_LENGTH));
printf("SQLForeignKeys - ret: %d\n", ret);
extract_error("SQLForeignKeys", stmt, SQL_HANDLE_STMT);
return;
}
SQLNumResultCols(stmt, &columns);
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
/*-------------------------------------------------*/
/* free up allocated handles */
SQLDisconnect(dbc);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}
BOOL
TSetupDlg::FillDBCombos (void)
{
TWaitCursor wc;
LPTSTR szValue;
TCHAR szText[256];
TODBCConn conn;
//SQLLEN datalen;
SQLLEN datalen;
int rc;
int iIndex;
if (!m_USEUID.Checked ())
return TRUE;
if (!ODBCConnect (conn))
return FALSE;
/* Get current text */
szValue = m_DEFAULTDB.Text ();
m_DEFAULTDB.Clear ();
/* Add Items */
rc = SQLTables (conn.m_hStmt,
(_SQLCHAR *) _T(SQL_ALL_CATALOGS), SQL_NTS,
(_SQLCHAR *) _T(""), SQL_NTS,
(_SQLCHAR *) _T(""), SQL_NTS,
(_SQLCHAR *) _T(""), SQL_NTS);
if (rc == SQL_SUCCESS)
{
while ((rc = SQLFetch (conn.m_hStmt)) == SQL_SUCCESS)
{
rc = SQLGetData (conn.m_hStmt, 1, _SQL_C_CHAR,
szText, sizeof (szText), &datalen);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
break;
if (datalen > 0 && datalen < sizeof (szText))
{
szText[datalen / sizeof (TCHAR)] = 0;
m_DEFAULTDB.AddString (szText);
}
}
}
if (rc == SQL_ERROR)
conn.ShowStmtError (m_hInstance, m_hWnd);
/* Set old value */
m_DEFAULTDB.Text (szValue);
iIndex = m_DEFAULTDB.FindExact (szValue);
if (iIndex != CB_ERR)
m_DEFAULTDB.CurSel (iIndex);
/* Same trick, now for character set combo */
/* Get current text */
szValue = m_DEFAULTCS.Text ();
m_DEFAULTCS.Clear ();
/* Add Items */
rc = SQLExecDirect (conn.m_hStmt,
(_SQLCHAR *) _T ("SELECT CS_NAME FROM DB.DBA.SYS_CHARSETS"), SQL_NTS);
if (rc == SQL_SUCCESS)
{
while ((rc = SQLFetch (conn.m_hStmt)) == SQL_SUCCESS)
{
rc = SQLGetData (conn.m_hStmt, 1, _SQL_C_CHAR,
szText, sizeof (szText), &datalen);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
break;
if (datalen > 0 && datalen < sizeof (szText))
{
szText[datalen / sizeof (TCHAR)] = 0;
m_DEFAULTCS.AddString (szText);
}
}
}
if (rc == SQL_ERROR)
conn.ShowStmtError (m_hInstance, m_hWnd);
/* Set old value */
m_DEFAULTCS.Text (szValue);
iIndex = m_DEFAULTCS.FindExact (szValue);
if (iIndex != CB_ERR)
m_DEFAULTCS.CurSel (iIndex);
return TRUE;
}
ZK tv(K x,S s){D d=d1(xj);U(d)R rs(SQLTables(d,(S)0,0,(S)0,0,(S)0,0,s,S0),d,3);}
/*
* Test program to run on the connected database
*/
int
ODBC_Test ()
{
SQLTCHAR request[4096];
SQLTCHAR fetchBuffer[1024];
char buf[4096];
size_t displayWidths[MAXCOLS];
size_t displayWidth;
short numCols;
short colNum;
SQLTCHAR colName[50];
SQLSMALLINT colType;
SQLULEN colPrecision;
SQLLEN colIndicator;
SQLSMALLINT colScale;
SQLSMALLINT colNullable;
unsigned long totalRows;
unsigned long totalSets;
int i;
SQLRETURN sts;
while (1)
{
/*
* Ask the user for a dynamic SQL statement
*/
printf ("\nSQL>");
if (fgets (buf, sizeof (buf), stdin) == NULL)
break;
#ifndef UNICODE
strcpy ((char *) request, (char *) buf);
#else
strcpy_A2W (request, buf);
#endif
request[TXTLEN (request) - 1] = TEXTC ('\0');
if (request[0] == TEXTC ('\0'))
continue;
/*
* If the user just types tables, give him a list
*/
if (!TXTCMP (request, TEXT ("tables")))
{
if (SQLTables (hstmt, NULL, 0, NULL, 0, NULL, 0,
NULL, 0) != SQL_SUCCESS)
{
ODBC_Errors ("SQLTables(tables)");
continue;
}
}
/*
* If the user just types qualifiers, give him a list
*/
else if (!TXTCMP (request, TEXT ("qualifiers")))
{
if (SQLTables (hstmt, TEXT ("%"), SQL_NTS, TEXT (""), 0,
TEXT (""), 0, TEXT (""), 0) != SQL_SUCCESS)
{
ODBC_Errors ("SQLTables(qualifiers)");
continue;
}
}
/*
* If the user just types owners, give him a list
*/
else if (!TXTCMP (request, TEXT ("owners")))
{
if (SQLTables (hstmt, TEXT (""), 0, TEXT ("%"), SQL_NTS,
TEXT (""), 0, TEXT (""), 0) != SQL_SUCCESS)
{
ODBC_Errors ("SQLTables(owners)");
continue;
}
}
/*
* If the user just types "types", give him a list
*/
else if (!TXTCMP (request, TEXT ("types")))
{
if (SQLTables (hstmt, TEXT (""), 0, TEXT (""), 0,
TEXT (""), 0, TEXT ("%"), SQL_NTS) != SQL_SUCCESS)
{
ODBC_Errors ("SQLTables(types)");
continue;
}
}
/*
* If the user just types "datatypes", give him a list
*/
else if (!TXTCMP (request, TEXT ("datatypes")))
{
if (SQLGetTypeInfo (hstmt, 0) != SQL_SUCCESS)
{
ODBC_Errors ("SQLGetTypeInfo");
continue;
}
}
else if (!TXTCMP (request, TEXT ("reconnect")))
{
if (ODBC_Reconnect())
return -1;
continue;
}
#if defined (unix)
else if (!TXTCMP (request, TEXT ("environment")))
{
extern char **environ;
int i;
for (i = 0; environ[i]; i++)
fprintf (stderr, "%03d: [%s]\n", i, environ[i]);
continue;
}
#endif
else if (!TXTCMP (request, TEXT ("quit"))
|| !TXTCMP (request, TEXT ("exit")))
break; /* If you want to quit, just say so */
else
{
/*
* Prepare & Execute the statement
*/
if (SQLPrepare (hstmt, (SQLTCHAR *) request,
SQL_NTS) != SQL_SUCCESS)
{
ODBC_Errors ("SQLPrepare");
continue;
}
if ((sts = SQLExecute (hstmt)) != SQL_SUCCESS)
{
ODBC_Errors ("SQLExec");
if (sts != SQL_SUCCESS_WITH_INFO)
continue;
}
}
/*
* Loop through all the result sets
*/
totalSets = 1;
do
{
/*
* Get the number of result columns for this cursor.
* If it is 0, then the statement was probably a select
*/
if (SQLNumResultCols (hstmt, &numCols) != SQL_SUCCESS)
{
ODBC_Errors ("SQLNumResultCols");
goto endCursor;
}
if (numCols == 0)
{
SQLLEN nrows = 0;
SQLRowCount (hstmt, &nrows);
printf ("Statement executed. %ld rows affected.\n",
nrows > 0 ? (long) nrows : 0L);
goto endCursor;
}
if (numCols > MAXCOLS)
{
numCols = MAXCOLS;
fprintf (stderr,
"NOTE: Resultset truncated to %d columns.\n", MAXCOLS);
}
/*
* Get the names for the columns
*/
putchar ('\n');
for (colNum = 1; colNum <= numCols; colNum++)
{
/*
* Get the name and other type information
*/
if (SQLDescribeCol (hstmt, colNum,
(SQLTCHAR *) colName, NUMTCHAR (colName), NULL,
&colType, &colPrecision, &colScale,
&colNullable) != SQL_SUCCESS)
{
ODBC_Errors ("SQLDescribeCol");
goto endCursor;
}
/*
* Calculate the display width for the column
*/
switch (colType)
{
case SQL_VARCHAR:
case SQL_CHAR:
case SQL_WVARCHAR:
case SQL_WCHAR:
case SQL_GUID:
displayWidth = colPrecision;
break;
case SQL_BINARY:
displayWidth = colPrecision * 2;
break;
case SQL_LONGVARCHAR:
case SQL_WLONGVARCHAR:
case SQL_LONGVARBINARY:
displayWidth = 30; /* show only first 30 */
break;
case SQL_BIT:
displayWidth = 1;
break;
case SQL_TINYINT:
case SQL_SMALLINT:
case SQL_INTEGER:
case SQL_BIGINT:
displayWidth = colPrecision + 1; /* sign */
break;
case SQL_DOUBLE:
case SQL_DECIMAL:
case SQL_NUMERIC:
case SQL_FLOAT:
case SQL_REAL:
displayWidth = colPrecision + 2; /* sign, comma */
break;
#ifdef SQL_TYPE_DATE
case SQL_TYPE_DATE:
#endif
case SQL_DATE:
displayWidth = 10;
break;
#ifdef SQL_TYPE_TIME
case SQL_TYPE_TIME:
#endif
case SQL_TIME:
displayWidth = 8;
break;
#ifdef SQL_TYPE_TIMESTAMP
case SQL_TYPE_TIMESTAMP:
#endif
case SQL_TIMESTAMP:
displayWidth = 19;
if (colScale > 0)
displayWidth = displayWidth + colScale + 1;
break;
default:
displayWidths[colNum - 1] = 0; /* skip other data types */
continue;
}
if (displayWidth < TXTLEN (colName))
displayWidth = TXTLEN (colName);
if (displayWidth > NUMTCHAR (fetchBuffer) - 1)
displayWidth = NUMTCHAR (fetchBuffer) - 1;
displayWidths[colNum - 1] = displayWidth;
/*
* Print header field
*/
#ifdef UNICODE
printf ("%-*.*S", displayWidth, displayWidth, colName);
#else
printf ("%-*.*s", displayWidth, displayWidth, colName);
#endif
if (colNum < numCols)
putchar ('|');
}
putchar ('\n');
/*
* Print second line
*/
for (colNum = 1; colNum <= numCols; colNum++)
{
for (i = 0; i < displayWidths[colNum - 1]; i++)
putchar ('-');
if (colNum < numCols)
putchar ('+');
}
putchar ('\n');
/*
* Print all the fields
*/
totalRows = 0;
while (1)
{
#if (ODBCVER < 0x0300)
int sts = SQLFetch (hstmt);
#else
int sts = SQLFetchScroll (hstmt, SQL_FETCH_NEXT, 1);
#endif
if (sts == SQL_NO_DATA_FOUND)
break;
if (sts != SQL_SUCCESS)
{
ODBC_Errors ("Fetch");
break;
}
for (colNum = 1; colNum <= numCols; colNum++)
{
/*
* Fetch this column as character
*/
#ifdef UNICODE
sts = SQLGetData (hstmt, colNum, SQL_C_WCHAR, fetchBuffer,
NUMTCHAR (fetchBuffer), &colIndicator);
#else
sts = SQLGetData (hstmt, colNum, SQL_C_CHAR, fetchBuffer,
NUMTCHAR (fetchBuffer), &colIndicator);
#endif
if (sts != SQL_SUCCESS_WITH_INFO && sts != SQL_SUCCESS)
{
ODBC_Errors ("SQLGetData");
goto endCursor;
}
/*
* Show NULL fields as ****
*/
if (colIndicator == SQL_NULL_DATA)
{
for (i = 0; i < displayWidths[colNum - 1]; i++)
fetchBuffer[i] = TEXTC ('*');
fetchBuffer[i] = TEXTC ('\0');
}
#ifdef UNICODE
printf ("%-*.*S", displayWidths[colNum - 1],
displayWidths[colNum - 1], fetchBuffer);
#else
printf ("%-*.*s", displayWidths[colNum - 1],
displayWidths[colNum - 1], fetchBuffer);
#endif
if (colNum < numCols)
putchar ('|');
}
putchar ('\n');
totalRows++;
}
printf ("\n result set %lu returned %lu rows.\n\n",
totalSets, totalRows);
totalSets++;
}
while ((sts = SQLMoreResults (hstmt)) == SQL_SUCCESS);
if (sts == SQL_ERROR)
ODBC_Errors ("SQLMoreResults");
endCursor:
#if (ODBCVER < 0x0300)
SQLFreeStmt (hstmt, SQL_CLOSE);
#else
SQLCloseCursor (hstmt);
#endif
}
return 0;
}
static int odbc_dispatch17(void)
{
unsigned long retval;
PWord rval; int rtype;
PWord arg1; int type1;
PWord arg2; int type2;
PWord arg3; int type3;
PWord arg4; int type4;
PWord arg5; int type5;
PWord arg6; int type6;
PWord arg7; int type7;
PWord arg8; int type8;
PWord arg9; int type9;
PWord arg10; int type10;
PWord arg11; int type11;
PI_getan(&arg1,&type1,1);
if (type1 != PI_INT)
if (!CI_get_integer((unsigned long *)&arg1,type1))
PI_FAIL;
PI_getan(&arg2,&type2,2);
if (type2 != PI_INT)
if (!CI_get_integer((unsigned long *)&arg2,type2))
PI_FAIL;
PI_getan(&arg3,&type3,3);
if (type3 == PI_SYM)
arg3 = (unsigned long) PI_getsymname(0,arg3,0);
else if (!CI_get_integer((unsigned long *)&arg3,type3))
PI_FAIL;
PI_getan(&arg4,&type4,4);
if (type4 != PI_INT)
if (!CI_get_integer((unsigned long *)&arg4,type4))
PI_FAIL;
PI_getan(&arg5,&type5,5);
if (type5 == PI_SYM)
arg5 = (unsigned long) PI_getsymname(0,arg5,0);
else if (!CI_get_integer((unsigned long *)&arg5,type5))
PI_FAIL;
PI_getan(&arg6,&type6,6);
if (type6 != PI_INT)
if (!CI_get_integer((unsigned long *)&arg6,type6))
PI_FAIL;
PI_getan(&arg7,&type7,7);
if (type7 == PI_SYM)
arg7 = (unsigned long) PI_getsymname(0,arg7,0);
else if (!CI_get_integer((unsigned long *)&arg7,type7))
PI_FAIL;
PI_getan(&arg8,&type8,8);
if (type8 != PI_INT)
if (!CI_get_integer((unsigned long *)&arg8,type8))
PI_FAIL;
PI_getan(&arg9,&type9,9);
if (type9 == PI_SYM)
arg9 = (unsigned long) PI_getsymname(0,arg9,0);
else if (!CI_get_integer((unsigned long *)&arg9,type9))
PI_FAIL;
PI_getan(&arg10,&type10,10);
if (type10 != PI_INT)
if (!CI_get_integer((unsigned long *)&arg10,type10))
PI_FAIL;
PI_getan(&arg11,&type11,11);
switch(arg1)
{
case 0:
retval = (unsigned long) SQLProcedureColumns(((SQLHSTMT ) arg2),((SQLCHAR * ) arg3),((SQLSMALLINT ) arg4),((SQLCHAR * ) arg5),((SQLSMALLINT ) arg6),((SQLCHAR * ) arg7),((SQLSMALLINT ) arg8),((SQLCHAR * ) arg9),((SQLSMALLINT ) arg10));
break;
case 1:
retval = (unsigned long) SQLColumnPrivileges(((SQLHSTMT ) arg2),((SQLCHAR * ) arg3),((SQLSMALLINT ) arg4),((SQLCHAR * ) arg5),((SQLSMALLINT ) arg6),((SQLCHAR * ) arg7),((SQLSMALLINT ) arg8),((SQLCHAR * ) arg9),((SQLSMALLINT ) arg10));
break;
case 2:
retval = (unsigned long) SQLTables(((SQLHSTMT ) arg2),((SQLCHAR * ) arg3),((SQLSMALLINT ) arg4),((SQLCHAR * ) arg5),((SQLSMALLINT ) arg6),((SQLCHAR * ) arg7),((SQLSMALLINT ) arg8),((SQLCHAR * ) arg9),((SQLSMALLINT ) arg10));
break;
case 3:
retval = (unsigned long) SQLColumns(((SQLHSTMT ) arg2),((SQLCHAR * ) arg3),((SQLSMALLINT ) arg4),((SQLCHAR * ) arg5),((SQLSMALLINT ) arg6),((SQLCHAR * ) arg7),((SQLSMALLINT ) arg8),((SQLCHAR * ) arg9),((SQLSMALLINT ) arg10));
break;
default:
PI_FAIL;
}
PI_makedouble(&rval,&rtype,(double) retval);
if (PI_unify(arg11,type11,rval,rtype))
PI_SUCCEED;
PI_FAIL;
}
SQLRETURN simpleSqlTables(SQLHSTMT stmt) {
return SQLTables(stmt, NULL, 0, NULL, 0, (SQLCHAR *)"%", 1, (SQLCHAR *)"TABLE", 5);
}
int
main(int argc, char **argv)
{
int rc;
HSTMT hstmt = SQL_NULL_HSTMT;
/* Cases where output is limited to relevant information only */
SQLSMALLINT sql_tab_privileges_ids[6] = {1, 2, 3, 4, 6, 7};
SQLSMALLINT sql_column_ids[6] = {1, 2, 3, 4, 5, 6};
test_connect();
rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
if (!SQL_SUCCEEDED(rc))
{
print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
exit(1);
}
/* Check for SQLGetTypeInfo */
printf("Check for SQLTypeInfo\n");
rc = SQLGetTypeInfo(hstmt, SQL_VARCHAR);
CHECK_STMT_RESULT(rc, "SQLGetTypeInfo failed", hstmt);
print_result_meta(hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Check for SQLTables */
printf("Check for SQLTables\n");
rc = SQLTables(hstmt, NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "%", SQL_NTS,
(SQLCHAR *) "TABLE", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLTables failed", hstmt);
print_result_meta(hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Check for SQLColumns */
printf("Check for SQLColumns\n");
rc = SQLColumns(hstmt,
NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "%", SQL_NTS,
NULL, 0);
CHECK_STMT_RESULT(rc, "SQLColumns failed", hstmt);
print_result_meta(hstmt);
/*
* Print only the 6 first columns, we do not want for example
* to get the OID in output, and this information looks to be
* enough.
*/
print_result_series(hstmt, sql_column_ids, 6);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Check for SQLColumnPrivileges */
//printf("Check for SQLColumnPrivileges\n");
//rc = SQLColumnPrivileges(hstmt,
// NULL, 0,
// (SQLCHAR *) "public", SQL_NTS,
// (SQLCHAR *) "testtab1", SQL_NTS,
// (SQLCHAR *) "id", SQL_NTS);
//CHECK_STMT_RESULT(rc, "SQLColumnPrivileges failed", hstmt);
//print_result_meta(hstmt);
//print_result(hstmt);
//rc = SQLFreeStmt(hstmt, SQL_CLOSE);
//CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Check for SQLSpecialColumns */
printf("Check for SQLSpecialColumns\n");
rc = SQLSpecialColumns(hstmt, SQL_ROWVER,
NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "testtab1", SQL_NTS,
SQL_SCOPE_SESSION,
SQL_NO_NULLS);
CHECK_STMT_RESULT(rc, "SQLSpecialColumns failed", hstmt);
print_result_meta(hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/*
* Check for SQLStatistics. It is important to note that this function
* returns statistics like the number of pages used and the number of
* index scans.
*/
printf("Check for SQLStatistics\n");
rc = SQLStatistics(hstmt,
NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "testtab1", SQL_NTS,
0, 0);
CHECK_STMT_RESULT(rc, "SQLStatistics failed", hstmt);
print_result_meta(hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Check for SQLPrimaryKeys */
printf("Check for SQLPrimaryKeys\n");
rc = SQLPrimaryKeys(hstmt,
NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "testtab1", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLPrimaryKeys failed", hstmt);
print_result_meta(hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Check for SQLForeignKeys */
printf("Check for SQLForeignKeys\n");
rc = SQLForeignKeys(hstmt,
NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "testtab1", SQL_NTS,
NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "testtab_fk", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLForeignKeys failed", hstmt);
print_result_meta(hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Check for SQLProcedures */
printf("Check for SQLProcedures\n");
rc = SQLProcedures(hstmt,
NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "simple_add", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLProcedures failed", hstmt);
print_result_meta(hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Check for SQLProcedureColumns */
printf("Check for SQLProcedureColumns\n");
rc = SQLProcedureColumns(hstmt,
NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "simple_add", SQL_NTS,
NULL, 0);
CHECK_STMT_RESULT(rc, "SQLProcedureColumns failed", hstmt);
print_result_meta(hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Check for SQLTablePrivileges */
printf("Check for SQLTablePrivileges\n");
rc = SQLTablePrivileges(hstmt,
NULL, 0,
(SQLCHAR *) "public", 0,
(SQLCHAR *) "testtab1", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLTablePrivileges failed", hstmt);
print_result_meta(hstmt);
print_result_series(hstmt, sql_tab_privileges_ids, 6);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/*
* Extra tests.
* Older versions of the driver had a bug in handling table-types lists
* longer than 32 entries. Check for that.
*/
rc = SQLTables(hstmt, "", SQL_NTS,
"public", SQL_NTS,
"testtab%", SQL_NTS,
"1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5, TABLES", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLTables failed", hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Clean up */
test_disconnect();
return 0;
}
