Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Generate dynamic query based on changing table names

Posted on 2011-10-04
5
Medium Priority
?
301 Views
Last Modified: 2012-05-12
Experts -

VB.Net\MySQL

I have a group of tables in my db that have this naming convention:

Table123
Table124
Table125
Table126

A new table is added regularly, the next one will be Table127.

I need to query these tables as if they were one table, and currently i simply do this by hardcoding table names:

select * from Table123
union
select * from Table124
union
select * from table 125
union
select * from Table126;

my question is this: i would simply like to be able to get the list of table names (SELECT table_name FROM information_schema.tables WHERE table_name like 'Table1%'), and then iterate through the results to dynamically create the query above.

Best way to do this?

TIA,

crafuse
0
Comment
Question by:crafuse
  • 2
  • 2
5 Comments
 
LVL 5

Assisted Solution

by:Didier Vally
Didier Vally earned 200 total points
ID: 36914904
You can perform the following SQL queries to dynamically scan for table names in mySql :

SELECT DATABASE();
This returns the name of the database, let's say "my_db".

SHOW TABLES;
This returns the tables in the schema.
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 1800 total points
ID: 36915578
Once you have the list of tables from the above query, you can perform a loop. But instead of Union statement, you can create a temp table and, in the loop, insert rows into temp table selecting rows from each table. Are you using MySQL?
0
 

Author Comment

by:crafuse
ID: 36915648

sorry, pretty easy question, i just wasn't really thinking when i asked the question, went with something like the following:

        Dim FileSQL As String
        FileSQL = "SELECT table_name FROM information_schema.tables WHERE table_name like 'Table1%';"

        Dim conReaderH As New MySqlConnection(strMySQLConnectString)
        Dim conStringH As New MySqlCommand(FileSQL, conReaderH)
        Dim drReaderH As MySqlDataReader

        Try
            conReaderH.Open()
            Try
                drReaderH = conStringH.ExecuteReader(CommandBehavior.CloseConnection)
                historySQL = ""

                    Do While drReaderH.Read()
                        If historySQL = "" Then
                            historySQL = "select * from " & drReaderH.Item("table_name").ToString & " where " & historywhereSQL
                        Else
                            historySQL = historySQL & " union select * from " & drReaderH.Item("table_name").ToString & " where " & historywhereSQL
                        End If

                    Loop
               
                historySQL = historySQL & ";"

                drReaderH.Close()
            Catch myerror As MySqlException
                MsgBox("There was an error reading from the database: " & myerror.Message & ". The application will now close.")
                Application.Exit()
            End Try
        Catch myerror As MySqlException
            MessageBox.Show("Error connecting to the database: " & myerror.Message & ". The application will now close.")
            Application.Exit()
        Finally
            If conReaderH.State <> ConnectionState.Closed Then
                conReaderH.Close()
            End If
        End Try
0
 

Author Closing Comment

by:crafuse
ID: 36915654
Thanks.
0
 
LVL 5

Expert Comment

by:Didier Vally
ID: 36923842
You're welcome.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month14 days, 5 hours left to enroll

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question