void ring_vm_odbc_columns ( void *pPointer )
{
ring_odbc *pODBC ;
SQLRETURN ret ;
if ( RING_API_PARACOUNT != 2 ) {
RING_API_ERROR(RING_API_MISS2PARA);
return ;
}
if ( RING_API_ISPOINTER(1) && RING_API_ISSTRING(2) ) {
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 = SQLColumns(pODBC->stmt, NULL, 0, NULL, 0, (SQLCHAR *) RING_API_GETSTRING(2), SQL_NTS, NULL, 0);
if ( SQL_SUCCEEDED(ret) ) {
RING_API_RETNUMBER(1);
} else {
RING_API_RETNUMBER(0);
}
} else {
RING_API_ERROR(RING_API_BADPARATYPE);
}
}
/************************************************************************
* name: SQLColumnsW
* arguments:
* returns/side-effects:
* description:
* NOTE:
************************************************************************/
ODBC_INTERFACE RETCODE SQL_API
SQLColumnsW (SQLHSTMT hstmt,
SQLWCHAR *catalog, SQLSMALLINT catalog_len,
SQLWCHAR *schema, SQLSMALLINT schema_len,
SQLWCHAR *table, SQLSMALLINT table_len,
SQLWCHAR *column, SQLSMALLINT column_len)
{
RETCODE ret = ODBC_ERROR;
char *cb_catalog = NULL, *cb_schema = NULL, *cb_table = NULL, *cb_column = NULL;
int cb_catalog_len = 0, cb_schema_len = 0, cb_table_len = 0, cb_column_len = 0;
ODBC_STATEMENT* stmt_handle = (ODBC_STATEMENT *) hstmt;
OutputDebugString ("SQLColumnsW called.\n");
wide_char_to_bytes (catalog, catalog_len, &cb_catalog, &cb_catalog_len, stmt_handle->conn->charset);
wide_char_to_bytes (schema, schema_len, &cb_schema, &cb_schema_len, stmt_handle->conn->charset);
wide_char_to_bytes (table, table_len, &cb_table, &cb_table_len, stmt_handle->conn->charset);
wide_char_to_bytes (column, column_len, &cb_column, &cb_column_len, stmt_handle->conn->charset);
ret = SQLColumns (hstmt,
cb_catalog, cb_catalog_len,
cb_schema, cb_schema_len,
cb_table, cb_table_len,
cb_column, cb_column_len);
UT_FREE (cb_catalog);
UT_FREE (cb_schema);
UT_FREE (cb_table);
UT_FREE (cb_column);
return ret;
}
/*-----------------------------------------------------------------------------*/
void ODBCColumns()
{
struct Cursor *cur = (struct Cursor *)ptoc_int(2);
char tmpstr[255];
char *str1, *str2, *str3;
RETCODE rc;
strcpy(tmpstr,ptoc_string(3));
str1 = strtok(tmpstr,".");
str2 = str3 = NULL;
if (str1) str2 = strtok(NULL,".");
if (str2) str3 = strtok(NULL,".");
if (!str3 && !str2) {str3 = str1; str1 = NULL;}
else if (!str3) {str3 = str2; str2 = NULL;}
/* printf("str1 %s, str2 %s, str3 %s\n",str1,str2,str3);*/
if (((rc=SQLColumns(cur->hstmt,
str1, SQL_NTS,
str2, SQL_NTS,
str3, SQL_NTS,
NULL,0)) == SQL_SUCCESS) ||
(rc == SQL_SUCCESS_WITH_INFO)) {
ctop_int(4,0);
} else {
ctop_int(4,PrintErrorMsg(cur));
SetCursorClose(cur);
}
return;
}
static int display_columns( SQLHDBC hDbc, char *sql )
{
SQLHSTMT hStmt;
SQLRETURN ret;
char *args[10];
int n_args;
SQLCHAR *table;
int len;
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;
}
}
n_args = get_args(sql, &args[0], sizeof(args) / sizeof(args[0]));
if ( n_args == 0 )
{
table = NULL;
len = 0;
}
else
{
table = (SQLCHAR*)args[ 0 ];
len = SQL_NTS;
}
ret = SQLColumns( hStmt, NULL, 0, NULL, 0, table, len, 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 );
free_args(args, sizeof(args) / sizeof(args[0]));
return 1;
}
K eval(K x,K y,K z){K*k;S*b,s;SQLULEN w;SQLLEN*nb;SQLINTEGER*wb;H*tb,u,t,j=0,p,m;F f;C c[128];I n=xj<0;D d=d1(n?-xj:xj);U(d)x=y;Q(z->t!=-KJ||xt!=-KS&&xt!=KC,"type")
if(z->j)SQLSetStmtAttr(d,SQL_ATTR_QUERY_TIMEOUT,(SQLPOINTER)(SQLULEN)z->j,0);
if(xt==-KS)Q1(SQLColumns(d,(S)0,0,(S)0,0,xs,S0,(S)0,0))else{I e;K q=kpn(xG,xn);ja(&q,"\0");e=SQLExecDirect(d,q->G0,xn);r0(q);Q1(e)}
SQLNumResultCols(d,&j);P(!j,(d0(d),knk(0)))
b=malloc(j*SZ),tb=malloc(j*2),wb=malloc(j*SZ),nb=malloc(j*SZ),x=ktn(KS,j),y=ktn(0,j);// sqlserver: no bind past nonbind
DO(j,Q1(SQLDescribeCol(d,(H)(i+1),c,128,&u,&t,&w,&p,&m))xS[i]=sn(c,u);
if(t>90)t-=82;
Q(t<-11||t>12,xS[i])wb[i]=ut[tb[i]=t=t>0?t:12-t]==KS&&w?w+1:wt[t];if(ut[t]==KS&&(n||!wb[i]||wb[i]>9))tb[i]=13)
DO(j,kK(y)[i]=ktn(ut[t=tb[i]],0);if(w=wb[i])Q1(SQLBindCol(d,(H)(i+1),ct[t],b[i]=malloc(w),w,nb+i)))
for(;SQL_SUCCEEDED(SQLFetch(d));)DO(j,k=kK(y)+i;u=ut[t=tb[i]];s=b[i];n=SQL_NULL_DATA==(int)nb[i];
if(!u)jk(k,n?ktn(ct[t]?KC:KG,0):wb[i]?kp(s):gb(d,(H)(i+1),t));
else ja(k,n?nu(u):u==KH&&wb[i]==1?(t=(H)*s,(S)&t):u==KS?(s=dtb(s,nb[i]),(S)&s):u<KD?s:u==KZ?(f=ds(s)+(vs(s+6)+*(I*)(s+12)/1e9)/8.64e4,(S)&f):(w=u==KD?ds(s):vs(s),(S)&w)))
if(!SQLMoreResults(d))O("more\n");DO(j,if(wb[i])free(b[i]))R free(b),free(tb),free(wb),free(nb),d0(d),xT(xD(x,y));}
void classTable::LoadColumns()
{
SQLHSTMT hstmt;
SQLRETURN nReturn = -1;
SQLCHAR szTableName[101] = "";
SQLCHAR szColumnName[101] = "";
SQLCHAR szColumnType[101] = "";
SQLCHAR szDescription[301] = "";
QString qsError;
classColumn *pColumn = NULL;
// CREATE A STATEMENT
nReturn = SQLAllocStmt( hDbc, &hstmt );
if ( nReturn != SQL_SUCCESS )
{
QMessageBox::warning( pCanvas, "Data Manager", "Failed to SQLAllocStmt" );
return;
}
// EXECUTE OUR SQL/CALL
strcpy( (char *)szTableName, qsName.data() );
if ( SQL_SUCCESS != (nReturn=SQLColumns( hstmt, 0, 0, 0, 0, szTableName, SQL_NTS, 0, 0 )) )
{
QMessageBox::warning( pCanvas, "Data Manager", "Failed to SQLColumns" );
return;
}
// GET RESULTS
nReturn = SQLFetch( hstmt );
while ( nReturn == SQL_SUCCESS || nReturn == SQL_SUCCESS_WITH_INFO )
{
szDescription[0] = '\0';
nReturn = SQLGetData( hstmt, SQLColumns_COLUMN_NAME, SQL_C_CHAR, &szColumnName[0], sizeof(szColumnName), 0 );
if ( nReturn != SQL_SUCCESS )
strcpy( (char *)szColumnName, "Unknown" );
nReturn = SQLGetData( hstmt, SQLColumns_TYPE_NAME, SQL_C_CHAR, &szColumnType[0], sizeof(szColumnType), 0 );
listColumns.append( pColumn = new classColumn( this, pColumn, pCanvas, hDbc, (char *)szColumnName, (char*)szColumnType, (char*)szDescription ) );
nReturn = SQLFetch( hstmt );
}
// FREE STATEMENT
nReturn = SQLFreeStmt( hstmt, SQL_DROP );
if ( nReturn != SQL_SUCCESS )
QMessageBox::warning( pCanvas, "Data Manager", "Failed to SQLFreeStmt" );
}
SQLRETURN SQL_API
SQLColumnsA(SQLHSTMT StatementHandle,
SQLCHAR *CatalogName,
SQLSMALLINT NameLength1,
SQLCHAR *SchemaName,
SQLSMALLINT NameLength2,
SQLCHAR *TableName,
SQLSMALLINT NameLength3,
SQLCHAR *ColumnName,
SQLSMALLINT NameLength4)
{
return SQLColumns(StatementHandle,
CatalogName, NameLength1,
SchemaName, NameLength2,
TableName, NameLength3,
ColumnName, NameLength4);
}
main() {
SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;
SQLRETURN ret; /* ODBC API return status */
SQLSMALLINT columns; /* number of columns in result-set */
SQLCHAR table[ 64 ];
SQLCHAR column[ 64 ];
SQLINTEGER type;
SQLLEN indicator[ 3 ];
int i;
/* 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);
/* Connect to the DSN mydsn */
/* You will need to change mydsn to one you have created and tested */
SQLDriverConnect(dbc, NULL, "DSN=PostgreSQL-rupen-aws;uid=poc;pwd=****;", SQL_NTS,
NULL, 0, NULL, SQL_DRIVER_COMPLETE);
/* Allocate a statement handle */
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
/* Retrieve a list of columns */
SQLColumns(stmt, NULL, 0, NULL, 0, "t1", SQL_NTS, NULL, 0);
ret = SQLBindCol( stmt, 3, SQL_C_CHAR,
table, sizeof( table ), &indicator[ 0 ] );
ret = SQLBindCol( stmt, 4, SQL_C_CHAR,
column, sizeof( column ), &indicator[ 1 ] );
ret = SQLBindCol( stmt, 5, SQL_C_LONG, &type, 0, &indicator[ 2 ] );
/* Fetch the data */
while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) {
printf( "%s.%s type %d\n", table, column, type );
}
}
SQLRETURN SQLColumnsA( SQLHSTMT statement_handle,
SQLCHAR *catalog_name,
SQLSMALLINT name_length1,
SQLCHAR *schema_name,
SQLSMALLINT name_length2,
SQLCHAR *table_name,
SQLSMALLINT name_length3,
SQLCHAR *column_name,
SQLSMALLINT name_length4 )
{
return SQLColumns( statement_handle,
catalog_name,
name_length1,
schema_name,
name_length2,
table_name,
name_length3,
column_name,
name_length4 );
}
void classTable::LoadColumns()
{
CursorScoper s(listView()) ;
SQLRETURN nReturn ;
SQLCHAR szColumnName[MAX_COLUMN_WIDTH];
SQLCHAR szColumnType[MAX_COLUMN_WIDTH];
SQLCHAR szColumnSize[MAX_COLUMN_WIDTH];
SQLCHAR szRemarks [MAX_COLUMN_WIDTH];
classColumn *pColumn = NULL;
QString qsDesc ;
// CREATE A STATEMENT
StatementScoper stmt( hDbc ) ; if ( !stmt() ) return ;
// EXECUTE OUR SQL/CALL
if (!SQL_SUCCEEDED(nReturn=SQLColumns( stmt(), 0, 0, (SQLCHAR*)qsLibrary.ascii(), SQL_NTS, (SQLCHAR*)qsTable.ascii(), SQL_NTS, 0, 0 ) ) )
return my_msgBox( "classTable", "SQLColumns", nReturn, NULL, NULL, stmt() ) ;
// GET RESULTS
while (SQL_SUCCEEDED(SQLFetch( stmt() ) ) )
{
if (!SQL_SUCCEEDED(SQLGetData( stmt(), SQLColumns_COLUMN_NAME, SQL_C_CHAR, &szColumnName[0], sizeof(szColumnName), 0 ) ) )
strcpy( (char *)szColumnName, "Unknown" );
if (!SQL_SUCCEEDED(SQLGetData( stmt(), SQLColumns_TYPE_NAME, SQL_C_CHAR, &szColumnType[0], sizeof(szColumnType), 0 ) ) )
strcpy( (char *)szColumnType, "Unknown" );
if (!SQL_SUCCEEDED(SQLGetData( stmt(), SQLColumns_COLUMN_SIZE, SQL_C_CHAR, &szColumnSize[0], sizeof(szColumnSize), 0 ) ) )
strcpy( (char *)szColumnSize, "Unknown" );
if (!SQL_SUCCEEDED(SQLGetData( stmt(), SQLColumns_REMARKS, SQL_C_CHAR, &szRemarks[0], sizeof(szRemarks), 0 ) ) )
strcpy( (char *)szRemarks, "Unknown" );
qsDesc.sprintf("Len=%s Desc=%s", QString((char*)szColumnSize).stripWhiteSpace().ascii(), QString((char*)szRemarks).stripWhiteSpace().ascii() ) ;
listColumns.append( pColumn = new classColumn( this, pColumn, pCanvas, hDbc, QString((char*)szColumnName).stripWhiteSpace(), QString((char*)szColumnType).stripWhiteSpace(), QString(qsDesc).stripWhiteSpace() ) ) ;
}
}
/* This needs to build the array of gsql_fields.
The resulting array of fields is cached in the database struct,
if, and only if, the define CACHED_GSQL_LIST_FIELDS is set.
In that case, the caller should *not* attempt to free the gsql_result
object which is returned from this function, since it needs to persist
in the database table info cache until the database is closed. */
static gsql_result *
gsql_list_fields (Database *db, char *tablename)
{
register int i;
gsql_result *gr = (gsql_result *)NULL;
char *p1, *p2, *p3;
char *owner, *basenam, *qualifier;
#define STR_LEN 128+1
#define REM_LEN 254+1
HSTMT hstmt;
UCHAR szQualifier[STR_LEN], szOwner[STR_LEN];
UCHAR szTableName[STR_LEN], szColName[STR_LEN];
UCHAR szTypeName[STR_LEN], szRemarks[REM_LEN];
SDWORD Precision, Length;
SWORD DataType, Scale, Radix, Nullable;
SDWORD cbQualifier, cbOwner, cbTableName, cbColName;
SDWORD cbTypeName, cbRemarks, cbDataType, cbPrecision;
SDWORD cbLength, cbScale, cbRadix, cbNullable;
RETCODE retcode;
#if defined (CACHED_GSQL_LIST_FIELDS)
gr = database_lookup_cached_table_info (db, tablename);
if (gr != (gsql_result *) NULL) return (gr);
#endif /* CACHED_GSQL_LIST_FIELDS */
/* Pick out the table parts. The full name could be:
[[[qualifier].owner].basename]
foo.bix.address
p1 = foo, p2 = bix, p3 = address
bix.address
p1 = bix, p2 = address, p3 = NULL;
address
p1 = address, p2, p3 = nULL; */
p1 = strdup (tablename);
p2 = gettoken (p1, '.');
p3 = gettoken (p2, '.');
if ((p2 == (char *)NULL) && (p3 == (char *)NULL))
{
/* basename only */
qualifier = NULL; owner = NULL; basenam = p1;
}
else if (p3 == (char *)NULL)
{
qualifier = NULL; owner = p1; basenam = p2;
}
else
{
qualifier = p1; owner = p2; basenam = p3;
}
gr = make_gsql_result ();
if (SQLAllocStmt (db->hdbc, &hstmt) != SQL_SUCCESS)
{
free (p1);
return ((gsql_result *)NULL);
}
retcode = SQLColumns
(hstmt,
(unsigned char *)qualifier,
(qualifier != NULL) ? SQL_NTS : 0, /* All qualifiers */
(unsigned char *)owner, (owner != NULL) ? SQL_NTS : 0, /* All owners */
(unsigned char *)basenam, SQL_NTS, /* Table name */
(unsigned char *)NULL, 0); /* All columns */
if (retcode == SQL_SUCCESS)
{
int arraysize = 10;
/* We get back one descriptive row for each column in the table. */
gr->fields = (gsql_field **) xmalloc
((arraysize + 1) * sizeof (gsql_field *));
SQLBindCol (hstmt, 1, SQL_C_CHAR, szQualifier, STR_LEN, &cbQualifier);
SQLBindCol (hstmt, 2, SQL_C_CHAR, szOwner, STR_LEN, &cbOwner);
SQLBindCol (hstmt, 3, SQL_C_CHAR, szTableName, STR_LEN, &cbTableName);
SQLBindCol (hstmt, 4, SQL_C_CHAR, szColName, STR_LEN, &cbColName);
SQLBindCol (hstmt, 5, SQL_C_SHORT, &DataType, 0, &cbDataType);
SQLBindCol (hstmt, 6, SQL_C_CHAR, szTypeName, STR_LEN, &cbTypeName);
SQLBindCol (hstmt, 7, SQL_C_LONG, &Precision, 0, &cbPrecision);
SQLBindCol (hstmt, 8, SQL_C_LONG, &Length, 0, &cbLength);
SQLBindCol (hstmt, 9, SQL_C_SHORT, &Scale, 0, &cbScale);
SQLBindCol (hstmt, 10, SQL_C_SHORT, &Radix, 0, &cbRadix);
SQLBindCol (hstmt, 11, SQL_C_SHORT, &Nullable, 0, &cbNullable);
SQLBindCol (hstmt, 12, SQL_C_CHAR, szRemarks, REM_LEN, &cbRemarks);
for (i = 0; ; i++)
{
retcode = SQLFetch (hstmt);
if (retcode == SQL_SUCCESS)
{
/* create fields... */
gsql_field *gfield = (gsql_field *)xmalloc (sizeof (gsql_field));
gfield->name = strdup ((char *)szColName);
gfield->type = DataType;
gfield->length = Length;
gfield->nullable = Nullable;
gfield->table = (char *) NULL;
gfield->qualifier = strdup ((char *)szQualifier);
gfield->owner = strdup ((char *)szOwner);
gfield->type_name = strdup ((char *)szTypeName);
gfield->precision = Precision;
gfield->scale = Scale;
gfield->radix = Radix;
if (i + 2 > arraysize)
gr->fields = (gsql_field **)xrealloc
(gr->fields, ((arraysize += 5) * sizeof (gsql_field *)));
gr->fields[i] = gfield;
}
else
break;
}
gr->fields[i] = (gsql_field *) NULL;
gr->numfields = i;
gr->tablename = strdup (tablename);
}
if (hstmt)
SQLFreeStmt (hstmt, SQL_DROP);
#if defined (CACHED_GSQL_LIST_FIELDS)
database_add_cached_table_info (db, gr);
#endif
free (p1);
return (gr);
}
int
main(int argc, char **argv)
{
SQLHANDLE env;
SQLHANDLE dbc;
SQLHANDLE stmt, stmt2;
char *dsn = "MonetDB";
char *user = "******";
char *pass = "******";
SQLRETURN ret;
int i;
SQLSMALLINT f1;
char f2[30];
SQLDOUBLE f3;
SQL_DATE_STRUCT f4;
SQL_TIME_STRUCT f5;
if (argc > 1)
dsn = argv[1];
if (argc > 2)
user = argv[2];
if (argc > 3)
pass = argv[3];
if (argc > 4 || *dsn == '-') {
fprintf(stderr, "Usage: %s [datasource [user [password]]]\n", argv[0]);
exit(1);
}
if (SQLAllocHandle(SQL_HANDLE_ENV, NULL, &env) != SQL_SUCCESS) {
fprintf(stderr, "Cannot allocate ODBC environment handle\n");
exit(1);
}
ret = SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) (size_t) SQL_OV_ODBC3, 0);
check(ret, SQL_HANDLE_ENV, env, "SQLSetEnvAttr");
ret = SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
check(ret, SQL_HANDLE_ENV, env, "SQLAllocHandle 1");
ret = SQLConnect(dbc, (SQLCHAR *) dsn, SQL_NTS, (SQLCHAR *) user, SQL_NTS, (SQLCHAR *) pass, SQL_NTS);
check(ret, SQL_HANDLE_DBC, dbc, "SQLConnect");
ret = SQLSetConnectAttr(dbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) (size_t) SQL_AUTOCOMMIT_OFF, 0);
check(ret, SQL_HANDLE_DBC, dbc, "SQLSetConnectAttr");
/* create a test table to be filled with values */
ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
check(ret, SQL_HANDLE_DBC, dbc, "SQLAllocHandle 2");
ret = SQLExecDirect(stmt, (SQLCHAR *)
"CREATE TABLE odbcsampletest (\n"
" i INT DEFAULT '0' NOT NULL,\n"
" s VARCHAR(200),\n"
" f FLOAT,\n"
" d DATE,\n"
" t TIME\n"
")", SQL_NTS);
check(ret, SQL_HANDLE_STMT, stmt, "SQLExecDirect 1");
/* figure out the type of the columns and bind parameters */
{
SQLSMALLINT coltype;
SQLSMALLINT colno;
ret = SQLColumns(stmt, NULL, 0, NULL, 0, (SQLCHAR *) "odbcsampletest", SQL_NTS, NULL, 0);
check(ret, SQL_HANDLE_STMT, stmt, "SQLColumns");
ret = SQLBindCol(stmt, 5, SQL_C_SSHORT, &coltype, 0, NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 1");
ret = SQLBindCol(stmt, 17, SQL_C_SSHORT, &colno, 0, NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 2");
for (;;) {
ret = SQLFetch(stmt);
if (ret == SQL_NO_DATA)
break;
check(ret, SQL_HANDLE_STMT, stmt, "SQLFetch");
switch (colno) {
case 1:
ret = SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_SSHORT, coltype, 0, 0, &f1, sizeof(f1), NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindParameter 1");
break;
case 2:
ret = SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, coltype, 0, 0, &f2, sizeof(f2), NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindParameter 2");
break;
case 3:
ret = SQLBindParameter(stmt, 3, SQL_PARAM_INPUT, SQL_C_DOUBLE, coltype, 0, 0, &f3, sizeof(f3), NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindParameter 3");
break;
case 4:
ret = SQLBindParameter(stmt, 4, SQL_PARAM_INPUT, SQL_C_TYPE_DATE, coltype, 0, 0, &f4, sizeof(f4), NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindParameter 4");
break;
case 5:
ret = SQLBindParameter(stmt, 5, SQL_PARAM_INPUT, SQL_C_TYPE_TIME, coltype, 0, 0, &f5, sizeof(f5), NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindParameter 5");
break;
}
}
ret = SQLFreeStmt(stmt, SQL_UNBIND);
check(ret, SQL_HANDLE_STMT, stmt, "SQLFreeStmt");
ret = SQLCloseCursor(stmt);
check(ret, SQL_HANDLE_STMT, stmt, "SQLCloseCursor");
}
/* prepare for filling the test table */
/* we use a single statement with parameters whose values vary */
ret = SQLPrepare(stmt, (SQLCHAR *)
"INSERT INTO odbcsampletest VALUES (?, ?, ?, ?, ?)", SQL_NTS);
check(ret, SQL_HANDLE_STMT, stmt, "SQLPrepare 1");
/* do the actual filling of the test table */
f4.year = 2003;
f4.month = 1;
f4.day = 1;
f5.hour = 0;
f5.minute = 0;
f5.second = 0;
for (i = 0; i < 2000; i++) {
f1 = i;
/* \342\200\230 is the UTF-8 encoding of U+2018 Left Single Quotation Mark;
\342\200\231 is the UTF-8 encoding of U+2019 Right Single Quotation Mark */
snprintf(f2, sizeof(f2), "value \342\200\230%d\342\200\231", i);
f3 = i * 1.5;
f4.day++;
if ((f4.day == 29 && f4.month == 2) || (f4.day == 31 && (f4.month == 4 || f4.month == 6 || f4.month == 9 || f4.month == 11)) || f4.day == 32) {
f4.day = 1;
f4.month++;
if (f4.month == 13) {
f4.month = 1;
f4.year++;
}
}
f5.second++;
if (f5.second == 60) {
f5.second = 0;
f5.minute++;
if (f5.minute == 60) {
f5.minute = 0;
f5.hour++;
if (f5.hour == 25)
f5.hour = 0;
}
}
ret = SQLExecute(stmt);
check(ret, SQL_HANDLE_STMT, stmt, "SQLExecute 1");
}
ret = SQLEndTran(SQL_HANDLE_DBC, dbc, SQL_COMMIT);
check(ret, SQL_HANDLE_DBC, dbc, "SQLEndTran");
ret = SQLSetConnectAttr(dbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) (size_t) SQL_AUTOCOMMIT_ON, 0);
check(ret, SQL_HANDLE_DBC, dbc, "SQLSetConnectAttr");
/* Now we are going to read back the values from the test table.
We create two statment handles, one of which will be used
to read the even table entries and the other for the odd
table entries. */
check(ret, SQL_HANDLE_DBC, dbc, "SQLAllocHandle 3");
/* first the handle for the even entries */
/* bind the columns before preparing the statement */
ret = SQLBindCol(stmt, 1, SQL_C_SSHORT, &f1, sizeof(f1), NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 1");
ret = SQLBindCol(stmt, 2, SQL_C_CHAR, &f2, sizeof(f2), NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 2");
ret = SQLBindCol(stmt, 3, SQL_C_DOUBLE, &f3, sizeof(f3), NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 3");
ret = SQLBindCol(stmt, 4, SQL_C_TYPE_DATE, &f4, sizeof(f4), NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 4");
ret = SQLBindCol(stmt, 5, SQL_C_TYPE_TIME, &f5, sizeof(f5), NULL);
check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 5");
ret = SQLPrepare(stmt, (SQLCHAR *) "SELECT * FROM odbcsampletest WHERE 2*(i/2) = i", SQL_NTS);
check(ret, SQL_HANDLE_STMT, stmt, "SQLPrepare 2");
ret = SQLExecute(stmt);
check(ret, SQL_HANDLE_STMT, stmt, "SQLExecute 2");
/* now the handle for the odd entries */
ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt2);
check(ret, SQL_HANDLE_DBC, dbc, "SQLAllocHandle 3");
ret = SQLPrepare(stmt2, (SQLCHAR *) "SELECT * FROM odbcsampletest WHERE 2*(i/2) <> i", SQL_NTS);
check(ret, SQL_HANDLE_STMT, stmt2, "SQLPrepare 3");
/* bind the columns after preparing the statement */
ret = SQLBindCol(stmt2, 1, SQL_C_SSHORT, &f1, sizeof(f1), NULL);
check(ret, SQL_HANDLE_STMT, stmt2, "SQLBindCol 6");
ret = SQLBindCol(stmt2, 2, SQL_C_CHAR, &f2, sizeof(f2), NULL);
check(ret, SQL_HANDLE_STMT, stmt2, "SQLBindCol 7");
ret = SQLBindCol(stmt2, 3, SQL_C_DOUBLE, &f3, sizeof(f3), NULL);
check(ret, SQL_HANDLE_STMT, stmt2, "SQLBindCol 8");
ret = SQLBindCol(stmt2, 4, SQL_C_TYPE_DATE, &f4, sizeof(f4), NULL);
check(ret, SQL_HANDLE_STMT, stmt2, "SQLBindCol 9");
ret = SQLBindCol(stmt2, 5, SQL_C_TYPE_TIME, &f5, sizeof(f5), NULL);
check(ret, SQL_HANDLE_STMT, stmt2, "SQLBindCol 10");
ret = SQLExecute(stmt2);
check(ret, SQL_HANDLE_STMT, stmt2, "SQLExecute 3");
for (;;) {
/* Alternate fetching an even and an odd entry. The
end result should be that we get all entries in the
correct order. */
ret = SQLFetchScroll(stmt, SQL_FETCH_NEXT, 0);
if (ret == SQL_NO_DATA)
break;
check(ret, SQL_HANDLE_STMT, stmt, "SQLFetch 1");
printf("%d %s %g %04d:%02d:%02d %02d-%02d-%02d\n", f1, f2, f3, f4.year, f4.month, f4.day, f5.hour, f5.minute, f5.second);
ret = SQLFetch(stmt2);
if (ret == SQL_NO_DATA)
break;
check(ret, SQL_HANDLE_STMT, stmt2, "SQLFetch 2");
printf("%d %s %g %04d:%02d:%02d %02d-%02d-%02d\n", f1, f2, f3, f4.year, f4.month, f4.day, f5.hour, f5.minute, f5.second);
}
ret = SQLCloseCursor(stmt);
check(ret, SQL_HANDLE_STMT, stmt, "SQLCloseCursor");
/* cleanup and disconnect */
ret = SQLFreeHandle(SQL_HANDLE_STMT, stmt2);
check(ret, SQL_HANDLE_STMT, stmt2, "SQLFreeHandle 1");
/* drop the test table */
ret = SQLExecDirect(stmt, (SQLCHAR *) "DROP TABLE odbcsampletest", SQL_NTS);
check(ret, SQL_HANDLE_STMT, stmt, "SQLExecDirect 3");
ret = SQLFreeHandle(SQL_HANDLE_STMT, stmt);
check(ret, SQL_HANDLE_STMT, stmt, "SQLFreeHandle 2");
ret = SQLDisconnect(dbc);
check(ret, SQL_HANDLE_DBC, dbc, "SQLDisconnect");
ret = SQLFreeHandle(SQL_HANDLE_DBC, dbc);
check(ret, SQL_HANDLE_DBC, dbc, "SQLFreeHandle 3");
ret = SQLFreeHandle(SQL_HANDLE_ENV, env);
check(ret, SQL_HANDLE_STMT, stmt, "SQLFreeHandle 4");
return 0;
}
/****************************
* 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 columns that meet specified criteria */
int TbListColumns(SQLHANDLE hdbc)
{
SQLRETURN cliRC = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt; /* statement handle */
struct
{
SQLINTEGER ind;
SQLCHAR val[129];
}
colName, colType;
struct
{
SQLINTEGER ind;
SQLCHAR val[255];
}
colRemarks;
struct
{
SQLINTEGER ind;
SQLINTEGER val;
}
colLength;
struct
{
SQLINTEGER ind;
SQLSMALLINT val;
}
colScale, colNullable;
/* criteria to look for */
SQLCHAR tbSchemaPattern[] = "%";
SQLCHAR tbNamePattern[] = "STAFF";
SQLCHAR colNamePattern[] = "%";
printf("\n-----------------------------------------------------------");
printf("\nUSE THE CLI FUNCTIONS\n");
printf(" SQLSetConnectAttr\n");
printf(" SQLAllocHandle\n");
printf(" SQLColumns\n");
printf(" SQLBindCol\n");
printf(" SQLFetch\n");
printf(" SQLFreeHandle\n");
printf("TO SHOW HOW TO LIST COLUMNS 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 SQLColumns */
printf("\n Call SQLColumns for:\n");
printf(" tbSchemaPattern = %s\n", tbSchemaPattern);
printf(" tbNamePattern = %s\n", tbNamePattern);
printf(" colNamePattern = %s\n", colNamePattern);
/* get column information for a table */
cliRC = SQLColumns(hstmt,
NULL,
0,
tbSchemaPattern,
SQL_NTS,
tbNamePattern,
SQL_NTS,
colNamePattern,
SQL_NTS);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* bind column 4 to variable */
cliRC = SQLBindCol(hstmt, 4, SQL_C_CHAR, colName.val, 129, &colName.ind);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* bind column 6 to variable */
cliRC = SQLBindCol(hstmt, 6, SQL_C_CHAR, colType.val, 129, &colType.ind);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* bind column 7 to variable */
cliRC = SQLBindCol(hstmt,
7,
SQL_C_LONG,
(SQLPOINTER)&colLength.val,
sizeof(colLength.val),
&colLength.ind);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* bind column 9 to variable */
cliRC = SQLBindCol(hstmt,
9,
SQL_C_SHORT,
(SQLPOINTER)&colScale.val,
sizeof(colScale.val),
&colScale.ind);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* bind column 11 to variable */
cliRC = SQLBindCol(hstmt,
11,
SQL_C_SHORT,
(SQLPOINTER)&colNullable.val,
sizeof(colNullable.val),
&colNullable.ind);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* bind column 12 to variable */
cliRC = SQLBindCol(hstmt,
12,
SQL_C_CHAR,
colRemarks.val,
255,
&colRemarks.ind);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* fetch each row and display */
printf("\n Fetch each row and display.\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(" %-10.10s", colName.val);
if (colNullable.val == SQL_NULLABLE)
{
printf(", NULLABLE");
}
else
{
printf(", NOT NULLABLE");
}
printf(", %s", colType.val);
if (colLength.ind != SQL_NULL_DATA)
{
printf(" (%ld", colLength.val);
}
else
{
printf("(");
}
if (colScale.ind != SQL_NULL_DATA)
{
printf(", %d)\n", colScale.val);
}
else
{
printf(")\n");
}
/* 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;
} /* TbListColumns */
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;
}
int print_columns(char *param_TableName)
{
char *TableType = NULL;
char schema[256] = { '\0', '\0', '\0' };
char *schema_ptr = NULL;
char *catalog_ptr = NULL;
char TableName[129];
char ColumnName[129];
SQLSMALLINT ColumnType;
char TableSchema[256];
char TableCatalog[256];
SQLSMALLINT schema_len, catalog_len, tname_len;
SQLINTEGER tname_size, schema_size, catalog_size, cname_size,
ctype_size;
SQLHANDLE StmtHandle = NULL;
SQLRETURN rc;
int ret_len;
int numret = 0;
int i;
int l, RemoteFilter;
if (!param_TableName) {
return -1;
}
/* Allocate An SQL Statement Handle */
rc = SQLAllocHandle(SQL_HANDLE_STMT, ConHandle, &StmtHandle);
if (rc != SQL_SUCCESS) {
printf("SQLAllocHandle call failed in print_columns on %s\n",
param_TableName);
LogODBCError(rc, SQL_HANDLE_DBC, ConHandle);
return -1;
}
catalog_ptr = NULL;
strcpy(schema, Get_UserName());
schema_len = strlen(schema);
for (i = 0; i < schema_len; i++) {
schema[i] = (char)toupper(schema[i]);
}
schema_ptr = schema;
tname_len = (SQLSMALLINT) strlen(param_TableName);
RemoteFilter = 1;
schema_len = (SQLSMALLINT) (schema_ptr ? strlen(schema_ptr) : 0);
catalog_len = (SQLSMALLINT) (catalog_ptr ? strlen(catalog_ptr) : 0);
rc = SQLColumns(StmtHandle,
(RemoteFilter ? catalog_ptr : NULL), catalog_len,
(RemoteFilter ? schema_ptr : NULL), schema_len,
param_TableName, tname_len, NULL, 0);
if (rc != SQL_SUCCESS) {
printf("SQLColumns call failed in print_columns on %s.%s.%s\n",
(catalog_ptr ? catalog_ptr : "%"),
(schema_ptr ? schema_ptr : "%"), param_TableName);
LogODBCError(rc, SQL_HANDLE_STMT, StmtHandle);
return -5;
}
rc = SQLBindCol(StmtHandle, 5, SQL_SMALLINT, (SQLPOINTER)
& ColumnType, sizeof(ColumnType), &ctype_size);
if (rc != SQL_SUCCESS) {
printf
("SQLBindCol (column type) call failed in print_columns on %s.%s.%s\n",
(catalog_ptr ? catalog_ptr : "%"),
(schema_ptr ? schema_ptr : "%"), param_TableName);
LogODBCError(rc, SQL_HANDLE_STMT, StmtHandle);
return -6;
}
rc = SQLBindCol(StmtHandle, 4, SQL_C_CHAR, (SQLPOINTER)
& ColumnName, sizeof(ColumnName), &cname_size);
if (rc != SQL_SUCCESS) {
printf
("SQLBindCol (column name) call failed in print_columns on %s.%s.%s\n",
(catalog_ptr ? catalog_ptr : "%"),
(schema_ptr ? schema_ptr : "%"), param_TableName);
LogODBCError(rc, SQL_HANDLE_STMT, StmtHandle);
return -7;
}
rc = SQLBindCol(StmtHandle, 3, SQL_C_CHAR, (SQLPOINTER)
& TableName, sizeof(TableName), &tname_size);
if (rc != SQL_SUCCESS) {
printf
("SQLBindCol (table name) call failed in print_columns on %s.%s.%s\n",
(catalog_ptr ? catalog_ptr : "%"),
(schema_ptr ? schema_ptr : "%"), param_TableName);
LogODBCError(rc, SQL_HANDLE_STMT, StmtHandle);
return -8;
}
rc = SQLBindCol(StmtHandle, 2, SQL_C_CHAR, (SQLPOINTER)
& TableSchema, sizeof(TableSchema), &schema_size);
if (rc != SQL_SUCCESS) {
printf
("SQLBindCol (schema) call failed in print_columns on %s.%s.%s\n",
(catalog_ptr ? catalog_ptr : "%"),
(schema_ptr ? schema_ptr : "%"), param_TableName);
LogODBCError(rc, SQL_HANDLE_STMT, StmtHandle);
return -9;
}
rc = SQLBindCol(StmtHandle, 1, SQL_C_CHAR, (SQLPOINTER)
& TableCatalog, sizeof(TableCatalog), &catalog_size);
if (rc != SQL_SUCCESS) {
printf
("SQLBindCol (catalog) call failed in print_columns on %s.%s.%s\n",
(catalog_ptr ? catalog_ptr : "%"),
(schema_ptr ? schema_ptr : "%"), param_TableName);
LogODBCError(rc, SQL_HANDLE_STMT, StmtHandle);
return -10;
}
/* loop over the result set */
ret_len = 0;
rc = SQL_SUCCESS;
l = 0;
while (rc == SQL_SUCCESS) {
ColumnName[0] = '\0';
TableName[0] = '\0';
TableSchema[0] = '\0';
TableCatalog[0] = '\0';
l++;
rc = SQLFetch(StmtHandle);
if (rc != SQL_SUCCESS && rc != SQL_NO_DATA) {
printf
("SQLFetch call number %d failed (%d) in print_columns on %s.%s.%s\n",
l, rc, (catalog_ptr ? catalog_ptr : "%"),
(schema_ptr ? schema_ptr : "%"), param_TableName);
LogODBCError(rc, SQL_HANDLE_STMT, StmtHandle);
return -11;
}
/* only permit exact matches! */
if (rc != SQL_NO_DATA) {
if ((strcmp(param_TableName, TableName) == 0) &&
((catalog_ptr == NULL)
|| (strcmp(catalog_ptr, TableCatalog) == 0))
&& ((schema_ptr == NULL)
|| (strcmp(schema_ptr, TableSchema) == 0))) {
numret++;
VERBOSE
("SQLColumns returned a match %s.%s.%s. Column name is %s\n",
TableCatalog, TableSchema, TableName,
ColumnName);
} else {
/*printf("SQLColumns returned a non-match %s.%s.%s\n",TableCatalog,TableSchema,TableName); */
}
}
}
VERBOSE("print_columns found %d matching columns:\n", numret);
SQLFreeHandle(SQL_HANDLE_STMT, StmtHandle);
if (rc == SQL_NO_DATA)
return SQL_SUCCESS;
return -1;
}
int FetchTest(SQLHANDLE env, SQLHANDLE dbc, SQLHANDLE stmt)
{
int ret;
SQLCHAR szSchema[STR_LEN];
SQLCHAR szCatalog[STR_LEN];
SQLCHAR szColumnName[STR_LEN];
SQLCHAR szTableName[STR_LEN];
SQLCHAR szTypeName[STR_LEN];
SQLCHAR szRemarks[REM_LEN];
SQLCHAR szColumnDefault[STR_LEN];
SQLCHAR szIsNullable[STR_LEN];
SQLINTEGER ColumnSize;
SQLINTEGER BufferLength;
SQLINTEGER CharOctetLength;
SQLINTEGER OrdinalPosition;
SQLSMALLINT DataType;
SQLSMALLINT DecimalDigits;
SQLSMALLINT NumPrecRadix;
SQLSMALLINT Nullable;
SQLSMALLINT SQLDataType;
SQLSMALLINT DatetimeSubtypeCode;
ret = SQLColumns(stmt, (SQLCHAR*)"", SQL_NTS, (SQLCHAR*)"", SQL_NTS, (SQLCHAR*)"T1", SQL_NTS, NULL, 0);
checkrc(ret,__LINE__);
short totalFields = 0 ;
ret = SQLNumResultCols (stmt, &totalFields);
checkrc(ret,__LINE__);
printf( "No of columns in resultset = %d\n",totalFields);
SQLBindCol(stmt, 1, SQL_C_CHAR, szCatalog, STR_LEN,NULL);
SQLBindCol(stmt, 2, SQL_C_CHAR, szSchema, STR_LEN, NULL);
SQLBindCol(stmt, 3, SQL_C_CHAR, szTableName, STR_LEN,NULL);
SQLBindCol(stmt, 4, SQL_C_CHAR, szColumnName, STR_LEN, NULL);
SQLBindCol(stmt, 5, SQL_C_SSHORT, &DataType, 0, NULL);
SQLBindCol(stmt, 6, SQL_C_CHAR, szTypeName, STR_LEN, NULL);
SQLBindCol(stmt, 7, SQL_C_SLONG, &ColumnSize, 0, NULL);
SQLBindCol(stmt, 8, SQL_C_SLONG, &BufferLength, 0, NULL);
SQLBindCol(stmt, 9, SQL_C_SSHORT, &DecimalDigits, 0, NULL);
SQLBindCol(stmt, 10, SQL_C_SSHORT, &NumPrecRadix, 0, NULL);
SQLBindCol(stmt, 11, SQL_C_SSHORT, &Nullable, 0, NULL);
SQLBindCol(stmt, 12, SQL_C_CHAR, szRemarks, REM_LEN, NULL);
SQLBindCol(stmt, 13, SQL_C_CHAR, szColumnDefault, STR_LEN, NULL);
SQLBindCol(stmt, 14, SQL_C_SSHORT, &SQLDataType, 0, NULL);
SQLBindCol(stmt, 15, SQL_C_SSHORT, &DatetimeSubtypeCode, 0,NULL);
SQLBindCol(stmt, 16, SQL_C_SLONG, &CharOctetLength, 0, NULL);
SQLBindCol(stmt, 17, SQL_C_SLONG, &OrdinalPosition, 0, NULL);
SQLBindCol(stmt, 18, SQL_C_CHAR, szIsNullable, STR_LEN, NULL);
while(SQL_SUCCEEDED(ret = SQLFetch(stmt))) {
printf("szCatalog =%s \t szSchema=%s \t szTableName=%s \t szColumnName= %s \t DataType = %d ColumnSize = %d Nullable =%d szRemarks = %s, OrdinalPosition =%d szIsNullable =%s\n ",szCatalog,szSchema,szTableName,szColumnName,DataType,ColumnSize,Nullable,szRemarks,OrdinalPosition,szIsNullable);
}
ret = SQLCloseCursor(stmt);
checkrc(ret,__LINE__);
ret = SQLTransact(env,dbc,SQL_COMMIT);
checkrc(ret,__LINE__);
return 0;
}
SQLRETURN simpleSqlColumns(SQLHSTMT stmt, SQLCHAR *tablename,
SQLSMALLINT tnlen) {
return SQLColumns(stmt, NULL, 0, NULL, 0, tablename, tnlen, (SQLCHAR *)"%", 1);
}
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;
}
