asked on

IBM DB2 List Table Columns using a SQL SELECT STatement and ADO in a VB Script

I have extracted data into Microsoft Access from a DB2-based Finance Application.
Now, I want to be able to see the names of fields in a few tables.
I have the odbc connection set up and my script is handling whatever sql I pass it.
Using ADO in a vb script, is there a way to do the following:

sql = SELECT * FROM [table name]

rs.open sql, Connectionstring

rs.movefirst
if not rs.eof then
for each c in rs.columns

msgbox c.name & " " & c.value

next

end if
rs.close
rs = nothing

Member_2_2484401

In DB2 for i (a.k.a. AS/400), the following query would work beautifully for you:

select TABLE_NAME, 
       COLUMN_NAME,
       DATA_TYPE,
       LENGTH
  from qsys2.syscolumns 
 where table_schema = 'ET@DTALIB'
 order by TABLE_NAME, 
       ORDINAL_POSITION

Open in new window

HTH,
DaveSlash

Member_2_2484401

On what platform does your DB2 run?

Keith McElroy

ASKER

Platform: OS Release V7R1M0
Power T20
Model E4C

I tested and got past some initial errs, cool!. Now I get no errs, but no 0 records returned.

The following is a valid sql for a table then how I am implementing your solution.
Feel like I am close.

S_GM055AP = "SELECT GMODSC FROM HTEDTA.GM055AP"

Attempt 1:
S_TABLESCHEMA = "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, LENGTH FROM qsys2.syscolumns WHERE table_schema = 'HTEDTA.GM055AP' ORDER BY TABLE_NAME, ORDINAL_POSITION"

Attempt 2:
S_TABLESCHEMA = "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, LENGTH FROM qsys2.syscolumns WHERE table_schema = 'GM055AP' ORDER BY TABLE_NAME, ORDINAL_POSITION"

ASKER CERTIFIED SOLUTION

Member_2_2484401

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Keith McElroy

ASKER

Wow, great guidance. All works perfect. Thank you!!!