/******************************************************************************
**
** Source File Name = columns.sqc
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 2000 
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
** PURPOSE: This sample program demonstrates the use of a CURSOR.
**          The CURSOR is processed using dynamic SQL.  The program
**          will output the result set from this query: 
**
** strcpy (st2, "SELECT tabname, colname, typename, length, scale FROM ");
** strcat (st2, "syscat.columns WHERE tabschema='");
** strcat (st2, schema);
** strcat (st2, "'");
**          
**
** Other Function(s) used:
**     check_error :   Checks for SQLCODE error, and prints out any
**     [in UTIL.C]     related information available.
**
**
** EXTERNAL DEPENDENCIES :
**       - Ensure existence of database for precompile purposes.
**       - Precompile with the SQL precompiler (PREP in DB2)
**       - Bind to a database (BIND in DB2)
**       - Compile and link with the IBM Cset++ compiler (AIX and OS/2)
**         or the Microsoft Visual C++ compiler (Windows) 
**         or the compiler supported on your platform.
**
** For more information about these samples see the README file.
**
** For more information on programming in C, see the:
**   -  "Programming in C and C++" section of the Application Development Guide
** For more information on Building C Applications, see the:
**   -  "Building C Applications" section of the Application Building Guide.
**
** For more information on the SQL language see the SQL Reference.
**
*******************************************************************************/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <ctype.h>
#include <sqlenv.h>
#include "utilemb.h"


EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION;
  char      table_name[18] = "";
  char      st2[255] = "";
  char      parm_var[19]   = "";
  char      column[18]     = "";
  char      typename[18]="";
  char      userid[9];
  char      passwd[19];
  sqlint32  length;
  short     scale;
EXEC SQL END DECLARE SECTION;

int main(int argc, char *argv[])
{
   char schema[8];
   char tableName[18];
   short index;


   printf ("Input the user name (SCHEMA):\n");
   gets (schema);

   printf ("Connecting to the SAMPLE database\n");

   if (argc == 1) {
      EXEC SQL CONNECT TO sample;
   }
   else if (argc == 3) {
      strcpy (userid, argv[1]);
      strcpy (passwd, argv[2]);
      EXEC SQL CONNECT TO sample USER :userid USING :passwd; 
   }
   else {
      printf ("\nUSAGE: columns [userid passwd]\n\n");
      return 1;
   } /* endif */
   
   /* convert schema to upper case */
   for (index=0; index < strlen(schema); index++) {
      if (islower(schema[index]) != 0) {
         schema[index] = toupper(schema[index]);
      }
   }
   printf ("schema :%s\n",schema);

   strcpy (st2, "SELECT tabname, colname, typename, length, scale FROM ");
   strcat (st2, "syscat.columns WHERE tabschema='");
   strcat (st2, schema);
   strcat (st2, "'");

   EXEC SQL PREPARE s2 FROM :st2;
   EMB_SQL_CHECK("PREPARE STATEMENT");

   EXEC SQL DECLARE c2 CURSOR FOR s2;

   EXEC SQL OPEN c2;
   EMB_SQL_CHECK("OPEN CURSOR");
   while (SQLCODE == 0) {
      EXEC SQL FETCH c2 INTO :table_name, :column, :typename, :length, :scale;
      if (SQLCODE != 0) break;
      if (strcmp(table_name, tableName) != 0) {
         strcpy (tableName, table_name);
         printf ("\ntable :%s\n",tableName);
      }

      printf ("   %-18.18s - %-18.18s - %d", column, typename, length);
      if (scale != 0) {
         printf (",%d\n",scale);
      } else {
         printf ("\n");
      }
   }

   EXEC SQL CLOSE c2;  /* :rk.6:erk. */
   EMB_SQL_CHECK("CLOSE CURSOR");

   EXEC SQL CONNECT RESET;
   EMB_SQL_CHECK("CONNECT RESET");
   return 0;
ext:
   EXEC SQL ROLLBACK;
   EMB_SQL_CHECK("ROLLBACK");
   return 1;
}