Re: SQL CLI Help

Thanks Tommy,
I reread the manaul and found out that that value in ignored on a  Fetch Next.
I added a SQLSetStmtAttr to set the number of rows and am getting a argument 
value not valid error when executing it.
The code is:
// Set number of rows to return                      
  cbRows_to_Fetch = %Len(Rows_to_Fetch);             
  Rc = SQLSetStmtAttr (hstmt                   :     
                       SQL_ATTR_ROW_ARRAY_SIZE :     
                       pRows_to_Fetch          :     
                       cbRows_to_Fetch);             

Any ideas?
 
Thanks,
 
Jeff Young
 
----- Original Message ----
From: Holden Tommy <Tommy.Holden@xxxxxxxxxxxxxxxxx>
To: RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
Sent: Wednesday, July 19, 2006 9:32:49 AM
Subject: RE: SQL CLI Help


<snip>
the offset parameter can be the number rows to fetch.
          SQLINTEGER   FetchOffset   input   Number of the row to fetch.
The interpretation of this argument depends on the value of the
FetchOrientation argument
</snip>

To my little pea-brain it appears that the offset can be the ROW NUMBER
to fetch not number of rows....


Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Young
Sent: Tuesday, July 18, 2006 7:11 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: SQL CLI Help


Thanks Scott,
  I expect you are correct regarding the failing statement.
  I must have just copied the code from a prior line and not changed it.
  Regarding the SQLFetchScroll, according to the manual, the offset
parameter can be the number rows to fetch.
          SQLINTEGER   FetchOffset   input   Number of the row to fetch.
The interpretation of this argument depends on the value of the
FetchOrientation argument. 
   
   
  I assume that the SQLGetDiagRec is failing for the same reason as the
other statement?
  If so, what should I use to get the reason for an error?
   
  I am first experimenting with the SQL CLI and am not sure how to do
all the things I want.
   
  
Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx> wrote:
  
Hi Jeff,

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

Your program creates the environment and connection handles and connects

successfully.

The next thing it does is create another environment handle? Why would 
you do that? I think you probably meant to create a statement handle 
here, but that's not what you did! You then call SQLPrepare() and pass 
hstmt, 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 be 
because you never allocate hstmt (unless you change your code to match
my 
suggestion)

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 an 
offset, and NOT the number of rows to fetch!

This offset would be used when the 2nd parameter is SQL_FETCH_RELATIVE
to 
tell it where to move the cursor relative to the current position, which

SQLFetchScroll does before it fetches a record.

I don't think SQLFetchScroll is capable of fetching multiple records, is

it? I thought you had to use SQLExtendedFetch() for that...

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

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