Accessing a physical file member using JDBC

[J.Pluta]

From what I understand about JDBC, you can indeed create a VIEW on the AS/400 over a specific member of a file. You should then be able to query that VIEW using a standard SELECT statement. I haven't tried it myself, that's purely hearsay. On the other hand, I know for a fact that you can access a specific member of a file using the JT400 (or better yet the JTOpen) record access classes. You specify the name of the memebr using IFS syntax: /QSYS.LIB/MYLIB.LIB/MYFILE.FILE/MYMBR.MBR The above line specifies member MYMBR of file MYLIB/MYFILE. Hope this helps. Joe

[prospec@iol.ie]

Accessing a physical file member using JDBC

Hi Joe, Thanks for the help; I really appreciate it! Another idea occurred to me last night that I'll try (but I've no idea if it will work) is to execute an override with *Job scope within the Java application using the jt400 command execution class, and then try to get the result set. It occurred to me after I'd posted my original question that a Java app executed with QSH probably runs in a new BCI job, in which case an override executed in my calling CL would have no effect on the BCI job, but one executed within the Java app should hold for the job. If it works, I'll post a reply as another solution. Best regards, Liam.

[Guest.Visitor]

Accessing a physical file member using JDBC

Liam, I got this off the jt400 jdbc faq page: 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 creating an SQL Alias. Suppose that you have a physical file MYLIB/SALES with twelve members (one member for each month of the year). To access JANUARY, you must first create an alias with the following SQL command: CREATE ALIAS MYLIB/SALES_JANUARY FOR MYLIB/SALES(JANUARY) Now, you can access the individual member JANUARY by using a select command such as SELECT * FROM SALES_JANUARY. However, if you rename a member of MYLIB/SALES, the alias will no longer point to it. You can either create a new alias for the member you renamed or create a new member with the old member name. // Here is the exact CREATE ALIAS command string. String aliasCommand = "CREATE ALIAS MYLIB/SALES_JANUARY " + "FOR MYLIB/SALES(JANUARY) "; // Create a Statement and issue the CREATE ALIAS SQL command. Statement statement = connection.createStatement(); statement.executeUpdate(aliasCommand);

[prospec@iol.ie]

Accessing a physical file member using JDBC

Hi Is it possible to access a specific member of a physical file using JDBC and SQL? I'm running a simple Java application from a CL program using QSH Cmd('java .....'). I've tried placing an override to the member in the CL program with override scope of *Job, but the Java class still reads the file's default member. If it can't be done using JDBC, is it possible to do it using the jt400 record level access classes and methods? Thanks, Liam.

[prospec@iol.ie]

Accessing a physical file member using JDBC

Hi Alex, I like it! Simple, elegant and efficient! Thanks very much to you, Joe and Don for your excellent help. Best regards, Liam.