Retrieve DB Names from My SQL

By Mike Bonnell on Sep 20th, 2011 10:00 pm

I found an old thread that had never been answered here - http://www.daniweb.com/software-development/vbnet/threads/330981

Basically, the guy was asking how to retrieve database names and/or table names from a MySql database with vb.net. Here's my answer to that. Yes, I still use vb6 because vb.net is not much better and I can do anything I need to in vb6. This function is from a production program I wrote a while back.

I looked all over Google to see if I could find a better way to do it but the examples I found were 'college level' and not real world code, so I wrote my own. This function returns a recordset just like any other recordset. Once you get the DB Names you can do whatever you want.

I haven't defined everything here .. the code calls external functions that open or close a connection and the ADODB and ADO recordset are defined elsewhere. But if you work with Visual Basic you should already know how to set up an ADO Connection and recordset. If not, ask your professor. He'll send you to Microsoft's website so you can look it up. *grin*

To see tablenames set the sql to - sql = "show tables in " & dbname & ";"
where dbname is the name of the database to enumerate.

Want to see the field names too?
set sql to - sql = "show columns from " & tbl & " in " & dbname & ";"
where tbl is the tablename and dbname is the database name

If you use this function in a production program, please give me credit for originating it.
And, if this helps you get past a problem, great. If not, here it is anyway.
:)
Mike

Option Explicit

Public dbname() As String                                    ' Array is used elsewhere 


Function GetDBNames()
' anything not defined here is defined elsewhere as a global


Dim x As Integer
Dim sql As String

        Call opnDB(0)                                        ' External function to Open a MySql connection
        sql = "show databases;"                              ' set sql             
        rs.Open sql, conn, adOpenStatic, adLockReadOnly      ' open the recordset. rs is a global. conn is a global.

        x = 1
        frmSetup.List1.Clear                                 ' this is a listbox on a form somewhere

        Do Until rs.EOF = True                               ' loop until done
            ReDim Preserve dbname(x)                         ' set a new item in the array
            dbname(x) = rs.Fields(0)                         ' the first (and only) field returned is the db name  
            If rs.Fields(0) <> "information_schema" Then     ' just get the user database names
                frmSetup.List1.AddItem (rs.Fields(0))        ' add the db name to the list
            end if
            rs.MoveNext                                      ' see if there are any more databases
            x = x + 1
        Loop

        Call closdb                                          ' External function to close the recordset and the connection
        dbfg = 0                                             ' global so I know where I came from. Will have various
                                                             ' values depending on what I'm trying to do.
        
End Function

This article has been dead for over three months

You