/**************************************************************************

      Project Name : PKG.MAK
      Project Type : QuickWin Application (Microsoft Visual C++)
      File Name    : PKG.C
      
      Purpose      : This sample program shows how to implement Static SQL
                     in the Attachmate DRDA ODBC Driver 
                     The Static SQL is stored in PACKAGES.
                     It can be used with AS/400, DB2 or SQL/DS.

                     The sample is based on a table described bellow :

                           ----------------------------------------
                           !  Library (Owner) : DATASIX           !
                           !  Table           : SALES             !
                           !--------------------------------------!
                           ! Name     Type         Precision Scale!
                           !--------------------------------------!
                           ! REF      SQL_CHAR          5         !
                           ! DES      SQL_CHAR         30         !
                           ! CUST     SQL_NUMERIC       6      0  !
                           ! SAL      SQL_CHAR          5         !
                           ! DATE     SQL_DECIMAL       4      0  !
                           ! QUANT    SQL_DECIMAL       4      0  !
                           ! TURN     SQL_DECIMAL       9      2  !
                           ----------------------------------------

     Documentation: Package.WRI
     Author       : Daniel Dédisse
     Copyright    : (C) Attachmate Corporation 1994

***************************************************************************/                                     

#include <windows.h>       // Windows API
#include <sql.h>           // ODBC API
#include <sqlext.h>        // ODBC extension API
#include <string.h>        // String Manipulation
#include <stdio.h>         // Standard Input/ouput
#include <stdlib.h>

#define PACKAGENAME "TEST_PKG"


HENV hEnv;                 // Global definition of the ODBC Environment
HDBC hDbc;                 // Global definition of the ODBC Connection




/*************************************************
    Procedure : CreatePackage
    
    Create a Package on the table DATASIX.SALES   
    with 2 parameters on fields REF and TURN
*************************************************/
void CreatePackage(void)
{
   HSTMT   hStmt;
   RETCODE Ret;         
   SDWORD  pcbValue1;
   SDWORD  pcbValue2;
   char    SQL[64];
   char    PackageStmt[128];  
   char    Error[256];
   
   printf("\nCreating Package : %s",PACKAGENAME);
   Ret = SQLAllocStmt(hDbc,&hStmt);
   
   
   pcbValue1 = SQL_NULL_DATA;
   pcbValue2 = SQL_NULL_DATA;
   // Describe the first Parameter :
   Ret = SQLBindParameter(hStmt,     // ODBC Statement
                              1,     // Parameter 1
                SQL_PARAM_INPUT,     // Input Parameter
                     SQL_C_CHAR,     // Character Type in input
                       SQL_CHAR,     // Column Type
                              5,     // Length = 5
                              0,     // Scale  = 0
                           NULL,     // Must be Null : not a data
                              0,     // Not Used
                      &pcbValue1     // There is no data (SQL_NULL_DATA)
                               );

   // Describe the Second Parameter :

   Ret = SQLBindParameter(hStmt,     // ODBC Statement
                              2,     // Parameter 2
                SQL_PARAM_INPUT,     // Input Parameter
                     SQL_C_CHAR,     // Character Type in input
                    SQL_DECIMAL,     // Colunm Type
                              9,     // Length = 9
                              2,     // Scale  = 2
                           NULL,     // Must be Null : not a data
                              0,     // Not Used
                      &pcbValue2     // There is no data (SQL_NULL_DATA)
                               );
   

   // Execution of the creation :
   // The syntax is { call AtmBindStmt(PkgName,SQL) }
   // The maximum length of PkgName is 8 characters
   
   strcpy(SQL,"SELECT * FROM DATASIX.SALES WHERE REF=? AND TURN=?");
   sprintf(PackageStmt,"{ call AtmBindStmt(%s,%s) }",PACKAGENAME,SQL);
 
   // Always use the SQLExecDirect Function :
   Ret = SQLExecDirect(hStmt,PackageStmt,SQL_NTS);

   if (Ret==SQL_ERROR)
   {
      SQLError(NULL,NULL,hStmt,NULL,NULL,Error,255,NULL);
      printf("\nError In Bind package : %s",Error);
   }
   else
   {
      printf("\nPackage %s created.",PACKAGENAME);
   }
   
   SQLFreeStmt(hStmt,SQL_DROP);   // Drop the statement
   // That's all !!!
}


/*************************************************
    Procedure : RunPackage
    
    Run the package created with CreatePackage
    on the table DATASIX.SALES   
    with 2 parameters on fields REF and TURN
*************************************************/
void RunPackage(void)
{
   HSTMT   hStmt;    
   SDWORD  pcbValue1;
   SDWORD  pcbValue2;
   RETCODE Ret;            
   char    PackageStmt[128];
   char    szParamValue1[5];
   char    szParamValue2[11];
   char    Error[256];
   int     nLine,nRow;                   
   char    szReturnValue[128];
   
   printf("\n\nRunning the Package : %s",PACKAGENAME);
   Ret = SQLAllocStmt(hDbc,&hStmt);

   pcbValue1 = SQL_NTS;
   pcbValue2 = SQL_NTS;
   // Describe the first Parameter :
   strcpy(szParamValue1,"01101");
   Ret = SQLBindParameter(hStmt,     // ODBC Statement
                              1,     // Parameter 1
                SQL_PARAM_INPUT,     // Input Parameter
                     SQL_C_CHAR,     // Character Type in input
                       SQL_CHAR,     // Column Type
                              5,     // Length = 5
                              0,     // Scale  = 0
                  szParamValue1,     // Parameter Value
                              5,     // Not Used
                     &pcbValue1      // Length of Data (SQL_NTS)
                               );

   // Describe the Second Parameter :
   strcpy(szParamValue2,"150.75");
   Ret = SQLBindParameter(hStmt,     // ODBC Statement
                              2,     // Parameter 2
                SQL_PARAM_INPUT,     // Input Parameter
                     SQL_C_CHAR,     // Character Type in input
                    SQL_DECIMAL,     // Character Type
                              9,     // Length = 9
                              2,     // Scale  = 2
                  szParamValue2,     // Must be Null : not a data
                             11,     // Not Used
                     &pcbValue2      // Length of Data (SQL_NTS)
                               );
   
   sprintf(PackageStmt,"{ call AtmExecStmt(%s) }",PACKAGENAME);
   
   // Always use the SQLExecDirect Function :
   Ret = SQLExecDirect(hStmt,PackageStmt,SQL_NTS);

   if (Ret==SQL_ERROR)
   {
      SQLError(NULL,NULL,hStmt,NULL,NULL,Error,255,NULL);
      printf("\nError In Bind package : %s",Error);
      SQLFreeStmt(hStmt,SQL_DROP);   // Drop the statement
      return;
   }

   // Get The Result :
   Ret = SQLFetch(hStmt);
   nLine = 0;
   while (Ret==SQL_SUCCESS)
   {        
      nLine++;
      printf("\nLine %d",nLine);
      
      for (nRow=1;nRow<8;nRow++)    // 7 Rows in the Table
      {                                   
         // Get the Columns' Data 
         SQLGetData(hStmt,nRow,SQL_C_CHAR,szReturnValue,127,&pcbValue1);
         printf(" %s ",szReturnValue);
      }
   
      Ret = SQLFetch(hStmt);  //Next Line 
   }

   SQLFreeStmt(hStmt,SQL_DROP);   // Drop the statement
}


void DisplayPackageList(void)
{
   RETCODE Ret;
   HSTMT   hStmt;
   char    szReturnValue[128];
   char    szError[256];
   SDWORD  pcbValue;
   int     nPkgNum;
   
   printf("\nPackage List : ");
   Ret = SQLAllocStmt(hDbc,&hStmt);

   Ret = SQLProcedures(hStmt,NULL,0,NULL,0,NULL,0);
   if (Ret==SQL_ERROR)
   {
      SQLError(NULL,NULL,hStmt,NULL,NULL,szError,255,NULL);
      printf("\nError in retreiving package List : %s",szError);
      SQLFreeStmt(hStmt,SQL_DROP);   // Drop the statement
      return;
   }
   
   nPkgNum = 0;
   Ret = SQLFetch(hStmt);
   while (Ret==SQL_SUCCESS)
   {
      nPkgNum++;
      printf("\n\nPACKAGE : %d ",nPkgNum);
      printf("\n------------");
      SQLGetData(hStmt,2,SQL_C_CHAR,szReturnValue,127,&pcbValue);
      printf("\nCollection            : %s ",szReturnValue);
      SQLGetData(hStmt,3,SQL_C_CHAR,szReturnValue,127,&pcbValue);
      printf("\nPackage Name          : %s ",szReturnValue);
      SQLGetData(hStmt,4,SQL_C_CHAR,szReturnValue,127,&pcbValue);
      printf("\nNum Input Parameters  : %s ",szReturnValue);
      SQLGetData(hStmt,5,SQL_C_CHAR,szReturnValue,127,&pcbValue);
      printf("\nNum output Parameters : %s ",szReturnValue);

      Ret = SQLFetch(hStmt);
   }   

   SQLFreeStmt(hStmt,SQL_DROP);   // Drop the statement
}




void DisplayInfoOnPackage(LPSTR szPkgName)
{
   RETCODE Ret;
   HSTMT   hStmt;           
   char    szError[256];
   char    szColumnInfo[512];
   char    szReturnValue[128];
   char    szColName[128];
   int     nColType;
   int     nDataType;
   char    szTypeName[128];
   long    lPrecision;
   long    lLength;
   int     nScale;
   int     nNullable;
   SDWORD  pcbValue;
   int     nLine;
   
   printf("\n\nInfo On Package : %s",szPkgName);
   Ret = SQLAllocStmt(hDbc,&hStmt);

   Ret = SQLProcedureColumns(hStmt,NULL,0,NULL,0,szPkgName,SQL_NTS,NULL,0);
   if (Ret==SQL_ERROR)
   {
      SQLError(NULL,NULL,hStmt,NULL,NULL,szError,255,NULL);
      printf("\nError in retreiving info on package : %s",szError);
      SQLFreeStmt(hStmt,SQL_DROP);   // Drop the statement
      return;
   }
   
   nLine = 0;
   Ret = SQLFetch(hStmt);
   while (Ret==SQL_SUCCESS)
   {         
      nLine++;
      if (nLine==1)
      {
         SQLGetData(hStmt,2,SQL_C_CHAR,szReturnValue,127,&pcbValue);
         printf("\nCollection            : %s ",szReturnValue);
         SQLGetData(hStmt,3,SQL_C_CHAR,szReturnValue,127,&pcbValue);
         printf("\nPackage Name          : %s ",szReturnValue);
      }
      SQLGetData(hStmt,4,SQL_C_CHAR,szColName,127,&pcbValue);
      SQLGetData(hStmt,5,SQL_C_SHORT,&nColType,5,&pcbValue);
      SQLGetData(hStmt,6,SQL_C_SHORT,&nDataType,5,&pcbValue);
      SQLGetData(hStmt,7,SQL_C_CHAR,&szTypeName,127,&pcbValue);
      SQLGetData(hStmt,8,SQL_C_LONG,&lPrecision,10,&pcbValue);
      SQLGetData(hStmt,9,SQL_C_LONG,&lLength,10,&pcbValue);
      SQLGetData(hStmt,10,SQL_C_SHORT,&nScale,5,&pcbValue);
      SQLGetData(hStmt,12,SQL_C_SHORT,&nNullable,5,&pcbValue);
      
      sprintf(szColumnInfo,"%s : %s  Type : %s = %d  Prec : %ld Length : %ld Scale : %d  %s",
                        ( (nColType==SQL_PARAM_INPUT) ? "Input " : "Result" ),
                        szColName,szTypeName,nDataType,lPrecision,lLength,nScale,
                        ( (nNullable==SQL_NULLABLE) ? "Nullable " : "No Nulls" ));
      printf("\n%s",szColumnInfo);
      Ret = SQLFetch(hStmt);
   }   

   SQLFreeStmt(hStmt,SQL_DROP);   // Drop the statement
}





// Remember it's a QuickWin !!!
void main(void)
{
   char    Error[256];
   RETCODE Ret;
   
   Ret = SQLAllocEnv(&hEnv);
   Ret = SQLAllocConnect(hEnv,&hDbc);
   Ret = SQLDriverConnect(hDbc,GetActiveWindow(),NULL,0,NULL,0,NULL,SQL_DRIVER_COMPLETE_REQUIRED);
   if (Ret==SQL_ERROR)
   {
      printf("\nError in DriverConnect : ");
      SQLError(NULL,hDbc,NULL,NULL,NULL,Error,255,NULL);
      printf("\n%s",Error);
      SQLDisconnect(hDbc);
      SQLFreeConnect(hDbc);
      SQLFreeEnv(hEnv);
      exit(0);
   }

   
   CreatePackage();
   DisplayPackageList();
   DisplayInfoOnPackage(PACKAGENAME);
   RunPackage();
   
   SQLDisconnect(hDbc);
   SQLFreeConnect(hDbc);
   SQLFreeEnv(hEnv);

   exit(0);
      
}  


