Getting Informix DB schema throough ODBC

[adrianjohnson]

Hello,

I'm trying to retrieve a list of tables on an Informix DB in VB.NET. I've got an odbc connection which works, but when I run this code I get a "System error":

Dim da As New Odbc.OdbcDataAdapter("SELECT * FROM Information_Schema.Tables where Table_Type = 'BASE TABLE'", conEf)
Dim dt As New DataTable
da.Fill(dt)
Dim dr As DataRow

I am then trying to populate a list box with the names of the tables.

The system connects ok to the data source (I have error checking in place etc.), but I get the error message when I get to the bit shown above.

Where am I going wrong?

Thanks,

Adrian Johnson



Adrian Johnson
Assystance - i.t. solutions

http://www.assystance.com

 

[mkohl]

I'm glad you posted this, I've been wanting to know how do do the same thing your trying to do. And I was unfamiliar with "information_schema.tables".

I couldn't really tell what was wrong with your code, because you did not post it all. I did a quick example using for a sql database, which I hope will help you.

Dim qy As String
Dim cs As String
Dim conn As SqlConnection
Dim da As SqlDataAdapter
Dim ds As DataSet
Dim row As DataRow

'query statement
qy = "select * from INFORMATION_SCHEMA.tables where TABLE_TYPE = 'BASE TABLE'"

'connection string
cs = "Initial Catalog=wheelone; Data Source =datasource;"
cs = cs + "User ID=ID; password =password"

Try
conn = New SqlConnection(cs)
conn.Open()

da = New SqlDataAdapter(qy, conn)
ds = New DataSet
da.Fill(ds)
conn.close()

'get information from 3rd column in he dataset
For Each row In ds.Tables(0).Rows
ListBox1.Items.Add(row(2)) 'populate listbox control
Next
Catch ex As Exception
MsgBox(ex.ToString)
End Try


I tested this and it works for a sql database, if you have any more problems, let me know, and I will connect to a odbc database and post it.