/************************************************************************
* name: SQLStatisticsW
* arguments:
* returns/side-effects:
* description:
* NOTE:
************************************************************************/
ODBC_INTERFACE RETCODE SQL_API
SQLStatisticsW (SQLHSTMT hstmt,
SQLWCHAR *catalog, SQLSMALLINT catalog_len,
SQLWCHAR *schema, SQLSMALLINT schema_len,
SQLWCHAR *table, SQLSMALLINT table_len,
SQLUSMALLINT unique, SQLUSMALLINT accuracy)
{
RETCODE ret = ODBC_ERROR;
SQLCHAR *cb_catalog = NULL, *cb_schema = NULL, *cb_table = NULL;
int cb_catalog_len = 0, cb_schema_len = 0, cb_table_len = 0;
OutputDebugString ("SQLStatisticsW 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);
ret = SQLStatistics(hstmt,
cb_catalog, cb_catalog_len,
cb_schema, cb_schema_len,
cb_table, cb_table_len,
unique, accuracy);
UT_FREE(cb_catalog);
UT_FREE(cb_schema);
UT_FREE(cb_table);
return ret;
}
int FetchTest(SQLHANDLE env, SQLHANDLE dbc, SQLHANDLE stmt,char *tablename)
{
int ret;
char f2[50]= "praba";
SWORD f1;
char f4[50]= "praba";
SWORD f3;
int f5;
char f6[50]= "praba";
ret = SQLStatistics(stmt, NULL, 0, NULL, SQL_NTS,
(SQLCHAR*) tablename, SQL_NTS, SQL_INDEX_ALL, SQL_QUICK);
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,4,SQL_C_SHORT,(void*)&f1,0,NULL);
checkrc(ret,__LINE__);
ret = SQLBindCol(stmt,6,SQL_C_CHAR,f2,sizeof(f2),NULL);
checkrc(ret,__LINE__);
ret = SQLBindCol(stmt,7,SQL_C_SHORT,(void *)&f3,0,NULL);
checkrc(ret,__LINE__);
ret = SQLBindCol(stmt,9,SQL_C_CHAR,f4,sizeof(f4),NULL);
checkrc(ret,__LINE__);
while(SQL_SUCCEEDED(ret = SQLFetch(stmt))){
printf(" Unique =%hd \tIndexName = %s \t Type=%hd ColumnName = %s \n ",f1,f2,f3,f4);
}
ret = SQLCloseCursor(stmt);
checkrc(ret,__LINE__);
ret = SQLTransact(env,dbc,SQL_COMMIT);
checkrc(ret,__LINE__);
return 0;
}
/* get the number of rows and pages that meet specified criteria */
int TbListRowsAndPages(SQLHANDLE hdbc)
{
SQLRETURN cliRC = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt; /* statement handle */
struct
{
SQLINTEGER ind;
SQLINTEGER val;
}
tbCardinality, tbPages;
struct
{
SQLINTEGER ind;
SQLSMALLINT val;
}
infoType;
/* criteria to look for */
SQLCHAR tbSchema[] = "SYSCAT";
SQLCHAR tbName[] = "TABLES";
printf("\n-----------------------------------------------------------");
printf("\nUSE THE CLI FUNCTIONS\n");
printf(" SQLSetConnectAttr\n");
printf(" SQLAllocHandle\n");
printf(" SQLStatistics\n");
printf(" SQLBindCol\n");
printf(" SQLFetch\n");
printf(" SQLFreeHandle\n");
printf("TO SHOW HOW TO GET THE NUMBER OF ROWS AND PAGES\n");
printf("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 SQLStatistics */
printf("\n Call SQLStatistics for:\n");
printf(" tbSchema = %s\n", tbSchema);
printf(" tbName = %s\n", tbName);
/* get index and statistics information for a base table */
cliRC = SQLStatistics(hstmt,
NULL,
0,
tbSchema,
SQL_NTS,
tbName,
SQL_NTS,
SQL_INDEX_UNIQUE,
SQL_QUICK);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* bind column 7 to variable */
cliRC = SQLBindCol(hstmt,
7,
SQL_C_SHORT,
(SQLPOINTER)&infoType.val,
sizeof(infoType.val),
&infoType.ind);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* bind column 11 to variable */
cliRC = SQLBindCol(hstmt,
11,
SQL_C_LONG,
(SQLPOINTER)&tbCardinality.val,
sizeof(tbCardinality.val),
&tbCardinality.ind);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* bind column 12 to variable */
cliRC = SQLBindCol(hstmt,
12,
SQL_C_LONG,
(SQLPOINTER)&tbPages.val,
sizeof(tbPages.val),
&tbPages.ind);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* fetch each row and display */
printf("\n Display the number of rows and pages.\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)
{
if (infoType.val == SQL_TABLE_STAT)
{
if (tbCardinality.ind == SQL_NULL_DATA)
{
printf(" Number of rows = (Unavailable)\n");
}
else
{
printf(" Number of rows = %u\n", tbCardinality.val);
}
if (tbPages.ind == SQL_NULL_DATA)
{
printf(" Number of pages used to store the table =");
printf("(Unavailable)\n");
}
else
{
printf(" Number of pages used to store the table = %u\n",
tbCardinality.val);
}
break;
}
/* 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;
} /* TbListRowsAndPages */
static int odbc_dispatch16(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_INT)
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) SQLStatistics(((SQLHSTMT ) arg2),((SQLCHAR * ) arg3),((SQLSMALLINT ) arg4),((SQLCHAR * ) arg5),((SQLSMALLINT ) arg6),((SQLCHAR * ) arg7),((SQLSMALLINT ) arg8),((SQLUSMALLINT ) arg9),((SQLUSMALLINT ) arg10));
break;
default:
PI_FAIL;
}
PI_makedouble(&rval,&rtype,(double) retval);
if (PI_unify(arg11,type11,rval,rtype))
PI_SUCCEED;
PI_FAIL;
}
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;
}