ASP - MySQL -> Show Tables

Drexl Spivey

Hi,

There is a MySQL database here and I would like to list the tables and their column.

I need to do that in asp vbscript.

I have already connected to the db (I have the connection string)

I know there is a SHOW TABLE command but how do I go about displaying the information on my asp page?

Any help much appreciated.

Thanks

Evil Phil

IIRC you need to create a recordset object, populate it and then loop through that to generate the HTML. Dunno if there is data-binding in classic asp but considering it's classic vb it's probably best avoided anyway.

comer_97

Dim objConn, objRS, strSQL
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

objConn.open "your connection string"

txtSQL = "select * from TABLE"

Set objRS = objConn.Execute(txtSQL)

Do While not objRS.EOF
Response.Write(Trim(objRS("FieldName")) & "</br>")
objRS.MoveNext
Loop

objRS.Close
objConn.Close
Set objConn = Nothing
Set objRS = Nothing

Evil Phil

    Dim i As Integer
    i = 0
    
    Do While i < objRS.Fields.Count
    
        Response.Write( objRS.Fields(i).Name & "</br>")
    
    End

Using comer_97's code something like this should give you the column names for a given table. Dunno about getting table names but it's in there somewhere.

I'm quite sure theres a much better way of doing this but it's been a long time since I worked with classic vb.

Drexl Spivey

Thanks


The problem I have is that I don't know the name of the table(s)
And I don't know the name of the columns in the table(s)

So how would I go about displaying the above information?

Only then will I be able to use the name of the tabl in my sql statement and the name of the columns in the recorset fields.

Thanks

comer_97

if you don't know the table name i can't help you.

if you don't know the column name you can use

objRS.Fields(0)
objRS.Fields(1)
etc instead of objRS("FieldName")

Evil Phil

There is probably a way of pulling back the database schema but I'm not familiar with MySQL.

Drexl Spivey

sSQL="show tables"

....

do while not recordset.eof
for each field in recordset.fields
response.write("<br>" & field.value)
next
recordset.movenext
loop

seamus

The SQL is simple enough.

Just
SHOW TABLES FROM $database

Then from that resultset, you'll have one column called "Tables_in_$database"

Then for each row in the resultset, call the SQL,

SHOW COLUMNS FROM $table_name

Which gives you another resulset containing all of the columns in that table.

Obviously $database and $table_name are the names of the database and tables.