Re: SQL CLI Help -- RPG400-L


Hi Jeff,

You said you're getting a -2 return code from SQLPrepare(). -2 is thesame as SQL_INVALID_HANDLE (the latter being a named constant with a valueof -2).

Your program creates the environment and connection handles and connectssuccessfully.

The next thing it does is create another environment handle? Why wouldyou do that? I think you probably meant to create a statement handlehere, but that's not what you did! You then call SQLPrepare() and passhstmt, but I don't see where you ever created the hstmt handle?


Here's the code I'm referring to:

      //**********************************************************
      // prepare the statement (do this once)
      //**********************************************************
      rc = SQLAllocHandle (SQL_HANDLE_ENV : SQL_NULL_HANDLE : henv);
      Exsr Check_Rc;



I would assume that what you really wanted to do the following, correct?

       rc = SQLAllocHandle (SQL_HANDLE_STMT : henv : hstmt);
       Exsr Check_Rc;


Then you do the following:

      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;

Here you're getting an SQL_INVALID_HANDLE error, which is likely to bebecause you never allocate hstmt (unless you change your code to match mysuggestion)


Another thing that struck me as odd was the following:

      %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

Unless I'm really confused, the 3rd parameter to SQLFetchScroll is anoffset, and NOT the number of rows to fetch!

This offset would be used when the 2nd parameter is SQL_FETCH_RELATIVE totell it where to move the cursor relative to the current position, whichSQLFetchScroll does before it fetches a record.

I don't think SQLFetchScroll is capable of fetching multiple records, isit? I thought you had to use SQLExtendedFetch() for that...

However, I've never been able to get SQLExtendedFetch() to work, I alwaysget SQLSTATE=HY010 when I try to use SQLExtendedFetch(). Since I cannever get it to work properly, I didn't respond to your first question :)

If SQLFetchScroll() does allow multiple records to be returned, I'd expectthat you'd have to set the rowset size with SQLSetStmtAttr(). Have youtried that?