Simple Select Using ODBC

From Ingres Community Wiki

/*
** File:  seliitab.c
**
** This program assumes you have an ODBC DSN configured to make a connection
** to the database.  The DSN name is provided as a command-line argument:
**     seliitab myDSN
** 
** ...connects you to the database defined by the ODBC DSN name "myDSN".
** 
** The program connects to the target database defined by "myDSN", fetches
** the "table_name" column from the iitables catalog table, and displays
** the result.
**
*/
# ifdef _WIN32
# include <windows.h>
# endif
# include <stdio.h>
# include <sql.h>
# include <sqlext.h>
void check_error( SQLSMALLINT, SQLHANDLE, SQLRETURN, int, char *);
void print_error( SQLSMALLINT, SQLHANDLE, SQLRETURN, int, char *);
#define CHECK_HANDLE( htype, hndl, rc ) if ( rc != SQL_SUCCESS ) {\
check_error (htype,hndl,rc,__LINE__,__FILE__);exit(0); }
int main(int argc, char **argv)
{ 
    RETCODE rc,ret;
    HENV henv;
    HDBC hdbc; 
    HSTMT hstmt = SQL_NULL_HSTMT;
    SQLINTEGER StrLen_or_Ind=SQL_NTS;
    int i;
    SQL_NUMERIC_STRUCT nsStruct;
    SQLCHAR x[41] = "\0"; 
    if (argc < 2 )
    {
        printf("Usage: seliitab dsn\n");
        exit(0);
    }
    printf("allocating the environment\n");
    rc = SQLAllocEnv(&henv);
    CHECK_HANDLE( SQL_HANDLE_ENV, henv, rc); 
    printf("Allocating data source connection info\n");
    rc = SQLAllocConnect(henv, &hdbc);
    CHECK_HANDLE( SQL_HANDLE_ENV, henv, rc); 
    printf("connecting to %s\n", argv[1]);
    rc = SQLConnect(hdbc,(SQLCHAR *)argv[1],SQL_NTS,(SQLCHAR *)0,
	 SQL_NTS, (SQLCHAR *)0,SQL_NTS);
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc);
    printf("allocating for select\n");
    rc = SQLAllocStmt(hdbc,&hstmt);
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc);
    StrLen_or_Ind = SQL_NTS;
    rc = SQLExecDirect(hstmt,"select table_name from iitables",SQL_NTS);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc);
    while(1)
    {
        printf("Fetching\n");
        rc = SQLFetch(hstmt);
        if (rc == SQL_NO_DATA) 
            break;
        else if (rc == SQL_SUCCESS)
        {
            printf("Getting the data\n");
            rc = SQLGetData(hstmt,1,SQL_C_CHAR, x, 40,
                &StrLen_or_Ind);
            CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc);
            printf("x is <%s>\n",x);
        }
        else
            CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc);
    }    
    rc = SQLFreeStmt(hstmt, SQL_DROP);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc);
    printf("disconnecting\n");
    rc = SQLDisconnect(hdbc);
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc);
    rc = SQLFreeConnect(hdbc);
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc);
    rc = SQLFreeEnv(henv);
    CHECK_HANDLE( SQL_HANDLE_ENV, henv, rc);
    return(0);   
}
void check_error( SQLSMALLINT htype, SQLHANDLE hndl,              
    SQLRETURN frc, int line, char * file ) 
{
   CHAR         cli_sqlstate[SQL_SQLSTATE_SIZE + 1];      
   SQLINTEGER   cli_sqlcode;                                        
   switch (frc) 
   {                                                   
   case SQL_SUCCESS:                                                
       break;           
   case SQL_INVALID_HANDLE:                                         
       printf("check_error> SQL_INVALID HANDLE \n");            
       break;                                                         
   case SQL_ERROR:                                                  
       printf("check_error> SQL_ERROR\n");                      
       break;                                                         
   case SQL_SUCCESS_WITH_INFO:                                      
       printf("check_error>  SQL_SUCCESS_WITH_INFO\n");         
       break;                                                         
   case SQL_NO_DATA_FOUND:                                          
       printf("check_error> SQL_NO_DATA_FOUND\n");              
       break;                                                         
   default:                                                         
       printf("check_error> Received rc from api rc=%i\n",frc); 
       break;                                                         
   } /*end switch*/                                                 
   print_error(htype,hndl,frc,line,file);                          
}  /* end check_error */                                                           
/******************************************************************/ 
/* print_error                                                    */ 
/* calls SQLGetDiagRec()displays SQLSTATE and message             */ 
/******************************************************************/
void print_error( SQLSMALLINT htype, SQLHANDLE hndl, SQLRETURN frc,
    int line, char * file) 
{                                                                                             
   SQLCHAR     buffer[SQL_MAX_MESSAGE_LENGTH + 1] ;                 
   SQLCHAR     sqlstate[SQL_SQLSTATE_SIZE + 1] ; 
   SQLINTEGER  sqlcode ;                                              
   SQLSMALLINT length, i ;                                            
   SQLRETURN   prc ;
   printf("return code = %d reported from file: %s, line: %d\n",          
       frc, file, line );
   i = 1 ;                                                            
   while ( SQLGetDiagRec( htype, hndl, i, sqlstate,                                   
       &sqlcode, buffer, SQL_MAX_MESSAGE_LENGTH + 1,                
       &length ) == SQL_SUCCESS ) 
   {                          
       printf( "SQLSTATE: %s\n", sqlstate ) ;                    
       printf( "Native Error Code: %ld\n", sqlcode ) ;           
       printf( "buffer: %s \n", buffer ) ;                       
       i++ ;
   }                                  
}  /* end print_error */