× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I am attepmting to create a SQL CLI program to retreive multiple rows from a 
file.
I get a -2 return code on the prepare statement, and the error retreive code is 
not working.
Below is the code I am using.
Any suggestions would be appreciated.
 
     H DftActGrp(*No) ActGrp('QILE') Option(*SrcStmt)
     D Customer_Data   Ds                  Occurs(30)
     D  Number                        8
     D  Name                         30
      /Include QrpgLeSrc,CSQLCLI_HX
     D pNumber         S                   Like(SqlPointer) Inz(%Addr(Number))
     D pName           S                   Like(SqlPointer) Inz(%Addr(Name  ))
     D cbAuthStr...
     D                 s                   like(SQLSMALLINT)
     D cbDSN...
     D                 s                   like(SQLSMALLINT)
     D cbSqlStr...
     D                 s                   like(SQLINTEGER)
     D cbUID...
     D                 s                   like(SQLSMALLINT)
     D hdbc...
     D                 s                   like(SQLHDBC)
     D henv...
     D                 s                   like(SQLHENV)
     D hstmt...
     D                 s                   like(SQLHSTMT)
     D pcbValue...
     D                 s                   like(SQLINTEGER)
     D rc...
     D                 s                   like(SQLRETURN)
     D strLen...
     D                 s                   like(SQLINTEGER)
     D                                     inz(0)
     D Bytes_Returned  S                   Like(SQLSMALLINT)
     D szAuthStr...
     D                 s             10
     D szSqlState      S                   Like(SQLCHAR)
     D
     D szDSN...
     D                 s             18
     D szSqlStr...
     D                 s           1024
     D szUID...
     D                 s             10
     D Native_Error    S                   Like(SQLINTEGER)
     D szErrorMsg      S            128
     D cbErrorMsg      S                   Like(SQLSMALLINT)
     D                                     Inz(%Len(szErrorMsg))
     D SQL_Select_Stmt...
     D                 C                   'Select Cust#,CName,CAdd1 from -
     D                                     D$Comlib/CustFl where Ardiv = ?'
      * Pointer to Company selected
     D ArDivParm       S              3A
     D pArdivParm      S               *   Inz(%Addr(ArDivParm))
      * Number of Records to fetch
     D Rows_to_Fetch   S                   Like(SQLINTEGER)
      * Rows Returned
     D Rows_Returned   S                   Like(SQLSMALLINT)
     D pRows_Returned  S               *   Inz(%Addr(Rows_Returned))
      * Loop Index
     D Ix              S             10I 0
      /Free
       // Allocate Environment
       Rc = SqlAllocHandle (SQL_HANDLE_ENV: SQL_NULL_HANDLE: henv);
       Exsr Check_Rc;
       // Allocate Connection
       Rc = SQLAllocHandle(SQL_HANDLE_DBC : henv : hdbc);
       Exsr Check_Rc;
       // Connect to system using dummy User/Pwd
       szDSN = 'DYNAX'; // Local Database Name (from WRKRDBDIRE)
       cbDSN = SQL_NTS; // Length of Database Name
       szUID = ''; // User Name (not needed for Local Connect)
       cbUID = SQL_NTS; // Length of user Name
       szAuthStr = ''; // Password (not needed for Local Connect)
       cbAuthStr = SQL_NTS; // Length of Password
       rc = SQLConnect(hdbc      :       // Connection Handle
                       szDSN     :       // Database Name
                       cbDSN     :       // Length
                       szUID     :       // User Id
                       cbUID     :       // Length
                       szAuthStr :       // Password
                       cbAuthStr);       // Length
       Exsr Check_Rc;
       //**********************************************************
       // prepare the statement (do this once)
       //**********************************************************
       rc = SQLAllocHandle (SQL_HANDLE_ENV : SQL_NULL_HANDLE : henv);
       Exsr Check_Rc;
       szSqlStr = SQL_Select_Stmt; // Load SQL Statement to execute
       cbSqlStr = SQL_NTS;  // Length
       // Associate SQL Statement with Handle
       // All SQL commands will now refer to this statement
       rc = SQLPrepare(hstmt    :    // Statement Handle
                       szSqlStr :    // Statement to execute
                       cbSqlStr);    // Length
       Exsr Check_Rc;
       //**********************************************************
       // bind columns
       //**********************************************************
       rc = SQLBindCol(hstmt       :  // Handle
                       1           :  // Column Number
                       SQL_C_CHAR  :  // Data Type
                       pNumber     :  // Pointer to Field
                       %Len(Number):  // Buffer Length
                       pcbValue);     // Bytes available for data
       Exsr Check_Rc;
       rc = SQLBindCol(hstmt       :  // Handle
                       2           :  // Column Number
                       SQL_C_CHAR  :  // Data Type
                       pName       :  // Pointer to Field
                       %Len(Name ) :  // Buffer Length
                       pcbValue);     // Bytes available for data
       Exsr Check_Rc;
       // Setup Selection Parm
       ArDivParm = 'RAN';
       rc = SQLBindParameter(hstmt :              // Handle
                             1     :              // Parm Number
                             SQL_PARAM_INPUT :    // Parm Type
                             SQL_C_CHAR      :    // C Data Type
                             SQL_CHAR        :    // SQL Data Type
                             %Len(ArdivParm) :    // Field Length
                             0               :    // Number of Decimals
                             pArDivParm      :    // Pointer to Parm
                             %Len(ArdivParm) :    // Length of Buffer for Parm
                             strLen);             // Value is Null Terminated 
String
       Exsr Check_Rc;
       rc = SQLExecute(hstmt);
       Exsr Check_Rc;
       //**********************************************************
       // process all rows in result set
       //**********************************************************
       %Occur(Customer_Data)= 1;
       Rows_to_Fetch = %Elem(Customer_Data);
       rc = SQLFetchScroll (hstmt          : // Handle
                       SQL_FETCH_NEXT : // Type of fetch
                       Rows_to_Fetch  ); // Number of rows to fetch
       Exsr Check_Rc;
       // Get number of records returned
       If Rc = SQL_SUCCESS;
         Rc = SQLGetStmtAttr (hstmt               :
                              SQL_ATTR_ROW_NUMBER :
                              pRows_Returned      :
                              %Len(Rows_Returned) :
                              Bytes_Returned);
       Exsr Check_Rc;
       Dsply  Rows_Returned;
       For Ix = 1 to Rows_Returned;
       %Occur(Customer_Data) = Ix;
       Dsply   Customer_Data;
       EndFor;
       EndIf;
       //**********************************************************
       // end of program processing
       //**********************************************************
       rc = SQLFreeStmt(hstmt : SQL_DROP);
       rc = SQLDisconnect(hdbc);
       rc = SQLFreeConnect(hdbc);
       rc = SQLFreeEnv(henv);
       *inlr = *on;
       Return;
       BegSR Check_Rc;
         If Rc <> SQL_Success;
           Rc = SQLGetDiagRec(SQL_HANDLE_STMT : hstmt : 1 : szSqlState :
                 Native_Error : szErrorMsg : cbErrorMsg :  Bytes_Returned);
         EndIf;
       EndSr;
      /end-free


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.