RE: Detect if table exists from within MySQL?

2005-10-06 Thread Ryan Stille 
> Maybe you could use
> 
> SHOW TABLES LIKE 'your_table';

That's a great idea, I just tried it in several ways, like:

IF EXISTS (SHOW TABLES LIKE 'cfgbiz') THEN
   SELECT siacnotifyto FROM cfgbiz
ELSE
   SELECT '' as siacnotifyto
END IF;

-and- 

select IF((SHOW TABLES LIKE 'cfgbiz'),notifyto,'') FROM cfgbiz;

But it looks like the SHOW TABLES statement just doesn't return like a
regular SELECT statement does, because the above works if I use it like
this:

select IF(1,notifyto,'') FROM cfgbiz;

-Ryan

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Detect if table exists from within MySQL?

2005-10-06 Thread Jeff Smelser 
On Thursday 06 October 2005 10:57 am, Ryan Stille wrote:
> I am converting some code from MSSQL to MySQL.  In one place I need to
> have a conditional query depending on if a table exists or not.  There
> are different versions of this application and the table only exists in
> some of them.  Here is how it was done in MSSQL:
>
> IF OBJECT_ID('cfgbiz') IS NOT NULL
>   SELECT notifyto FROM cfgbiz
> ELSE
>   SELECT '' as notifyto
>
> Is there something similar in MySQL? I am running version 4.1.x.

5.X has information_schema.. 

However...

4.1, you can show tables like '%tab%' and you will get a row back if exists.. 
WARNING, myisam will be quick, innodb will be really slow, specially if the 
tables are big.. 

Jeff


pgphkfOW9sFUY.pgp
Description: PGP signature

Re: Detect if table exists from within MySQL?

2005-10-06 Thread Keith Ivey 

Ryan Stille wrote:


If I have to, I could resort to doing another query in my application
(SHOW TABLES) and seeing if my table was returned in that list.  But I
was hoping for a more elegant way to do it, within the single query.


Maybe you could use

   SHOW TABLES LIKE 'your_table';

--
Keith Ivey <[EMAIL PROTECTED]>
Smokefree DC
http://www.smokefreedc.org
Washington, DC

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Detect if table exists from within MySQL?

2005-10-06 Thread Ryan Stille 
> If 'SHOW COLUMNS FROM tablename' returns error 1146 (42S02), the
> table doesn't exist. 

This causes my application (ColdFusion) to throw an exception.

If I have to, I could resort to doing another query in my application
(SHOW TABLES) and seeing if my table was returned in that list.  But I
was hoping for a more elegant way to do it, within the single query.

-Ryan


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Detect if table exists from within MySQL?

2005-10-06 Thread Peter Brawley 

Ryan,

>I am converting some code from MSSQL to MySQL. In one place I need to
>have a conditional query depending on if a table exists or not. There
>are different versions of this application and the table only exists in
>some of them. Here is how it was done in MSSQL:

If 'SHOW COLUMNS FROM tablename' returns error 1146 (42S02), the table 
doesn't exist.


PB


Ryan Stille wrote:


I am converting some code from MSSQL to MySQL.  In one place I need to
have a conditional query depending on if a table exists or not.  There
are different versions of this application and the table only exists in
some of them.  Here is how it was done in MSSQL:

IF OBJECT_ID('cfgbiz') IS NOT NULL
 SELECT notifyto FROM cfgbiz
ELSE
 SELECT '' as notifyto

Is there something similar in MySQL? I am running version 4.1.x.

Thanks,
-Ryan


 




--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.11/121 - Release Date: 10/6/2005


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Detect if table exists from within MySQL?

2005-10-06 Thread Ryan Stille 
I am converting some code from MSSQL to MySQL.  In one place I need to
have a conditional query depending on if a table exists or not.  There
are different versions of this application and the table only exists in
some of them.  Here is how it was done in MSSQL:

IF OBJECT_ID('cfgbiz') IS NOT NULL
  SELECT notifyto FROM cfgbiz
ELSE
  SELECT '' as notifyto

Is there something similar in MySQL? I am running version 4.1.x.

Thanks,
-Ryan


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]