Re: SQL multi member -- JAVA400-L

If use the Toolbox JDBC driver the following may work.  It is from the JDBC
FAQ on the Toolbox web site (http://www.ibm.com/iseries/toolbox)

                    How can I use JDBC to access physical file members
other than the default?

                    Although the OS/400 database supports physical files
with multiple members, SQL (and consequently JDBC)
                    is designed to access only the first member (which is
usually the member with the same name as the
                    file/table). If you are using Toolbox JDBC to access
files and members created outside of SQL, you may need
                    to trick SQL into using members other than the first.

                    You can do this by calling the OVRDBF (Override with
Database File) CL command. Suppose that you have a
                    file MYLIBRARY/MYFILE with two members, MYFILE and
ANOTHERMBR. By default, SQL will access only the
                    MYFILE member. To access ANOTHERMBR, you must first
issue the CL command:

                    OVRDBF FILE(MYFILE) TOFILE(MYLIBRARY/MYFILE)
MBR(ANOTHERMBR) OVRSCOPE(*JOB)

                    Now, any SQL accesses to MYLIBRARY/MYFILE will refer to
the data in member ANOTHERMBR. However, this
                    override only works for the current job. Since the IBM
Toolbox for Java CommandCall class issues its CL
                    commands in a different server job than Toolbox JDBC
works with, using CommandCall to issue the OVRDBF
                    does not work. Instead, you must issue the OVRDBF as a
stored procedure call within the same JDBC
                    connection that you plan to access the data. You can
use QSYS.QCMDEXC to execute CL commands as
                    stored procedures.

                    // Here is the exact OVRDBF CL command string.
                    String clCommand = "OVRDBF FILE(MYFILE) " +
                                       "TOFILE(MYLIBRARY/MYFILE) " +
                                       "MBR(ANOTHERMBR) OVRSCOPE(*JOB)";

                    // The length of the CL command string must be
                    // specified with precision 15, scale 5.
                    String clLength = "0000000000" + clCommand.length();
                    clLength = clLength.substring(clLength.length() - 10) +
                               ".00000";

                    // Create a Statement and issue the override.
                    Statement statement = connection.createStatement();
                    statement.executeUpdate("CALL QSYS.QCMDEXC('" +
                                     clCommand + "', " + clLength + ")");

      With Toolbox JDBC, each connection is a different iSeries server job
so different connections can access
      different members using this technique.

David Wall
AS/400 Toolbox for Java




                      BMIROW@aol.com
                      Sent by:                 To:       
<java400-l@midrange.com>
                      java400-l-admin@m        cc:
                      idrange.com              Subject:  SQL multi member


                      01/10/2002 08:08
                      AM
                      Please respond to
                      java400-l





Does anyone know how to execute a JDBC SQL statement against a multi member
file.

Thanks Barry
_______________________________________________
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l
or email: JAVA400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.