#include <stdarg.h>
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>

/* SQL Test. Test the SQLColumns feature of odbc to check that schemas can be
 * used.
 * $URL: https://svn.coolworld.bt.co.uk/svn/sjh/trunk/scripts/sqltest.c $
 * $Author: sjh $
 * $Date: 2010-11-29 10:56:21 +0000 (Mon, 29 Nov 2010) $
 * $Revision: 268 $
 *
 * Copyright British Telecommunications PLC 2009
 *
 */

/* This function courtesy of the unixODBC tutorial 
 * http://www.easysoft.com/developer/languages/c/odbc_tutorial.html
 */
void print_error(char *location,SQLHANDLE handle,SQLSMALLINT type)
{
    SQLINTEGER	 i = 0;
    SQLINTEGER	 native;
    SQLCHAR	 state[ 7 ];
    SQLCHAR	 text[256];
    SQLSMALLINT	 len;
    SQLRETURN	 ret;

    printf("Error encountered in %s\n Diagnostics follow:-\n",location);

    do{
        ret = SQLGetDiagRec(type, handle, ++i, state, &native, text,
                            sizeof(text), &len );
        if (SQL_SUCCEEDED(ret))
            printf("%s:%ld:%ld:%s\n", state, i, native, text);
    }while(ret == SQL_SUCCESS);
}

SQLCHAR *get_column_name(SQLHSTMT stmt,char *buf){

    SQLINTEGER name_col=4;
    SQLLEN ind;

    SQLGetData(stmt,name_col,SQL_C_CHAR,buf,sizeof(buf),&ind);
    return buf;
}

int show_columns(SQLHSTMT stmt,unsigned char *schema,unsigned char *table){

    SQLRETURN ret;
    SQLSMALLINT res;
    char buf[255];
    int i=0;

    /* Do a SQLColumns on the table */
    if(!SQL_SUCCEEDED(ret=SQLColumns(stmt,
				      NULL,0,
				      schema,schema?SQL_NTS:0,
				      table,SQL_NTS,
				      (unsigned char *)"%", SQL_NTS))){
	     print_error("SQLColumns",stmt,SQL_HANDLE_STMT);
	     return ret;
    }

    /* Loop through results to enumerate the columns */
    while((ret=SQLFetch(stmt)) != SQL_NO_DATA && ret!=SQL_ERROR){
	printf("Column %i: %s\n",i++,get_column_name(stmt,buf));
    }

    /* Why were no columns described? */
    if(i==0){
	switch(ret){
	  case SQL_NO_DATA:printf("No data!\n");
	    break;
	  case SQL_ERROR:printf("Error!\n");
	    break;
	  default:
	    printf("Other=%i\n",ret);
	}
    }
    return ret;
}

int describe_table(SQLHDBC con,SQLCHAR *schema,SQLCHAR *table){

    SQLRETURN ret;
    SQLHSTMT stmt;

    printf("\nGetting columns from %s.%s\n",schema,table);
    if(!SQL_SUCCEEDED(ret=SQLAllocHandle(SQL_HANDLE_STMT,con,&stmt))){
	print_error("stmt handle",stmt,SQL_HANDLE_STMT);
	return ret;
    }
    ret=show_columns(stmt,schema,table);
    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
    return ret;
}

SQLCHAR *new_row_select(SQLCHAR *schema,SQLCHAR *table){

    SQLCHAR *select=(SQLCHAR *)malloc(sizeof(SQLCHAR)*255);

    if(schema){
	sprintf(select,"select * from %s.%s",schema,table);
    }else{
	sprintf(select,"select * from %s",table);
    }

    return select;
}

int show_data(SQLHSTMT stmt){

    SQLRETURN ret;
    SQLSMALLINT col;
    int i=0,j;

    /* How many columns are there? */
    if(!SQL_SUCCEEDED(SQLNumResultCols(stmt,&col))){
	print_error("get num columns",stmt,SQL_HANDLE_STMT);
	return 0;
    }

    /* Loop through results to display the data */
    while((ret=SQLFetch(stmt)) != SQL_NO_DATA && ret!=SQL_ERROR){
	printf("Row %i:",i++);
	for(j=1;j<=col;j++){
	    SQLLEN ind;
	    char buf[255];
	    if(SQL_SUCCEEDED(
		   SQLGetData(stmt,j,SQL_C_CHAR,buf,sizeof(buf),&ind))){
		if(ind==SQL_NULL_DATA)
		    printf("%i=NULL;",j);
		else
		    printf("%i=%s;",j,buf);
	    }
	}
	printf("\n");
    }

    return i;
}

int select_table(SQLHDBC con,SQLCHAR *schema,SQLCHAR *table){

    SQLRETURN ret;
    SQLHSTMT stmt;
    SQLCHAR *select;

    printf("\nGetting all rows from %s.%s\n",schema,table);
    if(!SQL_SUCCEEDED(ret=SQLAllocHandle(SQL_HANDLE_STMT,con,&stmt))){
	print_error("stmt handle",stmt,SQL_HANDLE_STMT);
	return ret;
    }

    select=new_row_select(schema,table);
    if(!SQL_SUCCEEDED(ret=SQLExecDirect(stmt,select,SQL_NTS))){
	print_error("stmt select",stmt,SQL_HANDLE_STMT);
	return ret;
    }

    show_data(stmt);

    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
    free(select);
    return ret;
}

int main(int argc, char **argv){

    SQLHENV env;
    SQLHDBC dbc;
    SQLRETURN ret;

    SQLCHAR *dsn="pbx";
    SQLCHAR *schema="test_schema";
    SQLCHAR *public="public";
    SQLCHAR *table="test";

    printf("SQLColumns Connection Test\n");
    
    /* Create connection handles */
    if(!SQL_SUCCEEDED(ret=SQLAllocHandle(
			  SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env))){
	print_error("env handle",dbc,SQL_HANDLE_ENV);
    }
    if(!SQL_SUCCEEDED(ret=SQLSetEnvAttr(
			  env,SQL_ATTR_ODBC_VERSION,(void *)SQL_OV_ODBC2, 0))){
 	print_error("odbc version",dbc,SQL_HANDLE_ENV);
    }
    if(!SQL_SUCCEEDED(ret=SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc))){
	print_error("connection handle",dbc,SQL_HANDLE_DBC);
    }

    printf("Handles Allocated\n");

    /* Connect to the database */
    if(!SQL_SUCCEEDED(ret=SQLConnect(dbc,dsn,SQL_NTS,NULL,0,NULL,0))){
	print_error("connection",dbc,SQL_HANDLE_DBC);
    }

    /* In turn, describe each table and get the contents */
    describe_table(dbc,schema,table);
    select_table(dbc,schema,table);

    describe_table(dbc,public,table);
    select_table(dbc,public,table);

    describe_table(dbc,NULL,table);
    select_table(dbc,NULL,table);


    /* Free handles */
    SQLFreeHandle(SQL_HANDLE_DBC, dbc);
    SQLFreeHandle(SQL_HANDLE_ENV, env);
}