Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Unanswered: DB2/as400 Range Select!??

    i want to be able to select part of records...


    lets say that i have 200 records....

    and i want to select 50 records
    then the next 50 records and
    then the next 50 records and so on...

    i tried a fetch first 50 row only...
    but i only get the first 50 records...
    i don't know how to get the next 50 records... and so on...

    thank you!

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    If you are connecting via JDBC then you can set your fetch size after you create the statement:
    For example, to set fetch size to 52:
    Statement getWeeks = Connection.createStatement();
    fetchSize = getWeeks.getFetchSize();
    getWeeks.setFetchSize(52);


    If you are using a stored procedure or embedded SQL, you can use a host structure and select multiple rows on your fetch:

    Fetch Next
    From YourCursor
    For :NbrRows Rows << Where NbrRows is the number of rows you want
    Into :HostStructure


    Below is a link with further info , if you so desire.... HTH

    http://publib.boulder.ibm.com/iserie...f/rzahfms1.htm
    multiple-row-fetch

    FOR k ROWS Evaluates host-variable or integer to an integral value k. If a host-variable is specified, it must be a numeric host variable with zero scale and it must not include an indicator variable. k must be in the range of 1 to 32767. The cursor is positioned on the row specified by the orientation keyword (for example, NEXT), and that row is fetched. Then the next k-1 rows are fetched (moving forward in the table), until the end of the cursor is reached. After the fetch operation, the cursor is positioned on the last row fetched. For example, FETCH PRIOR FROM C1 FOR 3 ROWS causes the previous row, the current row, and the next row to be returned, in that order. The cursor is positioned on the next row. FETCH RELATIVE -1 FROM C1 FOR 3 ROWS returns the same result. FETCH FIRST FROM C1 FOR :x ROWS returns the first x rows, and leaves the cursor positioned on row number x.
    When a multiple-row-fetch is successfully executed, three variables are set in the SQLCA:
    • SQLERRD(3) shows the number of rows retrieved.
    • SQLERRD(4) contains the length of the row retrieved.
    • SQLERRD(5) contains +100 if the last row was fetched. 58
    INTO host-structure-array host-structure-array identifies an array of host structures defined in accordance with the rules for declaring host structures. The first structure in the array corresponds to the first row, the second structure in the array corresponds to the second row, and so on. In addition, the first value in the row corresponds to the first item in the structure, the second value in the row corresponds to the second item in the structure, and so on. The number of rows to be fetched must be less than or equal to the dimension of the host structure array.

  3. #3
    Join Date
    Mar 2006
    Posts
    3
    Hi :-)

    i was so releaved to finally seeing a question similar to my own. However, I didn't make much of the answer, as I'm fairly new to cursors. I need some sample code: I am programming an aspx webform using C# in Visual Studio2005. Can it be done without using stored procedures?

  4. #4
    Join Date
    Mar 2006
    Posts
    3
    Hi :-)

    i was so releaved to finally seeing a question similar to my own. However, I didn't make much of the answer, as I'm fairly new to cursors. I need some sample code: I am programming an aspx webform using C# in Visual Studio2005. Can it be done without using stored procedures?

  5. #5
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Sure you can.... What type of database are you connecting to? And how are you connecting?(i.e JDBC, ODBC, etc ). Show us what your current SQL looks like...

  6. #6
    Join Date
    Mar 2006
    Posts
    3
    Ok, this is what I have; I use OleDb to fill a DataSet (The Sql is a bit simple; i may need to join two tables)

    About the database, i know this: db2, OS400, as/400, v5r1.


    CODE:

    String strConn = "Provider=IBMDA400; Data Source=XXX.XXX.XXX.XXX; User Id=XXX; Password=XXX";

    OleDbConnection objConn = new OleDbConnection(strConn);

    String strSQL = "SELECT a, b, t, c FROM table WHERE a='2' AND b in('A','B') ORDER BY c DESC";

    OleDbDataAdapter adapter = new OleDbDataAdapter();

    adapter.SelectCommand = new OleDbCommand(strSQL, objConn);

    DataSet dataset = new DataSet();

    adapter.Fill(dataset);

  7. #7
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    I'm not familar with .ADO but you should be able to set the size(i.e. number of rows to return).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •