Looking for a ODBC "If Table Exists" Solution.

I am currently attempting to write a "ODBC handling class" and I would like to put an "if table exists return bool" method in the class, any ideas on how I would go about this?
LVL 1
addicktzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

addicktzAuthor Commented:
I attempted this code here

            public bool TableExists(string TableName)
            {
                  bool myReturn;
                  int myInt = 0;
                  try
                  {

                        OdbcCommand myCommand = new OdbcCommand("SELECT * FROM " + TableName + "WHERE EXISTS (SELECT * FROM " + TableName + ");", MyConnection);
                        myInt = myCommand.ExecuteNonQuery();
                  }
                  catch (Exception ex)
                  {
                        APT(ex.ToString());
                  }
                  finally
                  {
                  }

                  if (myInt > 0)
                  {
                        myReturn = true;
                  }
                  else
                  {
                        myReturn = false;
                  }

                  return myReturn;
            }

but am recieving the following error

textBox1System.Data.Odbc.OdbcException: ERROR [23000] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS (SELECT * FROM tblJeffRocks)' at line 1
   at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method)
   at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
   at ADK.ADKODBC.TableExists(String TableName) in g:\adk\source\adkodbc\adkodbc.cs:line 111
False
Kevin HaysIT AnalystCommented:
are you trying to do something like the following example?

DROP TABLE IF EXISTS companies;
CREATE TABLE companies
(
  id                 int(11)         NOT NULL auto_increment,
  description       varchar(75)   NOT NULL default 'na',
  city                 varchar(25)   NOT NULL default 'na',
  state             char(2)         NOT NULL default 'na',
  PRIMARY KEY (id)
)
ENGINE=MyISAM
DEFAULT CHARSET=utf8;

notice I drop the table if it exists then i create it.  

addicktzAuthor Commented:
I dont want to drop any tables, I just want to know if it exists, if it doesnt exist I would want to create it.....
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Kevin HaysIT AnalystCommented:
How about just doing a select count(*) from tablename and if the odbc HasRows then the table has rows.  I've not figured a way out to just do a if exists tablename by a select statement.

I'm not sure if the ODBC has a property called HasRows, but I know if you use the MySql.NET Connector which is better for .NET IMHO then you can use the .HasRows() function.

cheers
nil1stCommented:
I am not much familier with MySql but folliwng link seems to have answer
http://dev.mysql.com/doc/maxdb/en/6d/117c20d14811d2a97400a0c9449261/content.htm


addicktzAuthor Commented:
that does appear to be the solution, but im not sure how to go about using that...I am going to look into MySql.NET as well....
Kevin HaysIT AnalystCommented:
I convinced my other developers to switch from odbc to MySql.Net Connector since you just package the MySql.Data.Client.dll with your .NET programs.  Much easier for what we use than the ODBC connector and I also believe it's more efficient than ODBC since it's native to mysql and .net.

Regards,

Kevin
addicktzAuthor Commented:
I have been looking into the mysql connector, and it doesnt seem anymore difficult than odbc to implement, so i am taking a shot at it, any chance you could ask those developers for a quick if table exists method for me?
Kevin HaysIT AnalystCommented:
This is what I use to use and my other developers use currently before creating tables.

-- Drop companies table if it exists and
-- create the companies table.
DROP TABLE IF EXISTS companies;
CREATE TABLE companies
(
  id                 int(11)         NOT NULL auto_increment,
  description       varchar(75)   NOT NULL default 'na',
  city                 varchar(25)   NOT NULL default 'na',
  state             char(2)         NOT NULL default 'na',
  PRIMARY KEY (id)
)
ENGINE=MyISAM
DEFAULT CHARSET=utf8;

Was you wanting to see if a table exists before you do a select command on it?
Kevin HaysIT AnalystCommented:
LOL, I didn't realize I had posted that same thing earlier in the thread, sorry about that.

Try this link out from mysql website.  It talks about the exists and not exists.
http://dev.mysql.com/doc/mysql/en/exists-and-not-exists-subqueries.html

Kevin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.