Sequel & ADODB

[disord3r]

This may not be the right place for this, but here goes...

In an ASP page, I'm creating an ADODB connection to an AS/400 system using a system DSN that I setup on the web server serving up the ASP pages. The DSN was created using the IBM Client Access driver that was installed with the IBM Client Access Express software (and I additionally installed a service pack for IBM CAE).

I'm able to perform simple queries against the AS/400 database, but there are some things that are stumping me. One thing in particular is the ability to have multiple logical files/tables within other physical files/tables. For a task I'm performing, the AS/400 administrator gave me this query:

"select column_list from ABCDEF(*FIRST @UVWXYZ)"

...but this produces and error saying:

"SQL0104 - Token ( was not valid."

I'm not sure why it doesn't allow the parentheses, but I took them out to see what would happen. This is what happened:

"SQL0104 - Token * was not valid."

I then tried dropping "*FIRST" from the query and got this:

"SQL7003 - File ABCDEF in LMNOP has more than one format".

The AS/400 administrator insists that the query works perfectly fine for him over his host connection, but it's not working for me with ADODB and my DSN. My assumption is that I'm trying to select the logical file @UVWXYZ as ABCDEF, but it's not working out. Any idea how I would resolve the issue?

 

[leveyp]

I suspect your AS400 administrator is trying to wind you up. That sql looks like jibberish to me. I don't believe there is a 'column_list' function available.
Are trying to get a list of all columns in one or more tables, or has you administrator created a special table for you that you are trying to access?

To access all columns in a table (file) or view (logical) in a collection (library) you just need
SELECT * FROM collection.table or
SELECT * FROM collection.view
If you can give me a bit more info on what you're trying to achieve I may be able to help.

 

[disord3r]

What I need to do is "select * ABCDEF", where ABCDEF is a physical file containing logical tables (I think that's the proper terminology). @UVWXYZ is the logical table within the physical file ABCDEF that I actually need to select * from.

I don't doubt that it works fine natively, because it was likely built from the query building program on the AS/400 (a step-by-step wizard, essentially).

 

[leveyp]

Hi disord3r,

I think we may have a terminology issue here.
Physical files do not contain logical tables.
An AS/400 physical file is a table.
An AS/400 logical file is a view - which may include select/omit statements, joins to other physicals and a subset of the columns available in the physical file(s).

Maybe what you are refering to are 'Members' If so then see my previous answer in thread178-851898

Hope that helps