Using MySQL 4.1 and the built in scripting is it possible to write a script
that checks to see if a table exists and then branches one way and branches
another if it doesn't?

I've been trying all sorts of combinations but nothing works for me. I was
thinking of running a select * from the table that I am interested in and if
it returned a null then that would mean it would not exist but I do not know
how to write this in SQL script.

Is there anyone out there that can help me or at least point me in the right
direction? TIA.

---
Sam

Sam Smith wrote:

> Using MySQL 4.1 and the built in scripting is it possible to write a script
> that checks to see if a table exists and then branches one way and branches
> another if it doesn't?
>
> I've been trying all sorts of combinations but nothing works for me. I was
> thinking of running a select * from the table that I am interested in and if
> it returned a null then that would mean it would not exist but I do not know
> how to write this in SQL script.
>
> Is there anyone out there that can help me or at least point me in the right
> direction? TIA.
>
> ---
> Sam
>
>

you would not receive a null from such a query, you would receive an ERROR.

try

select table_name from information_schema.tables
where table_schema ='someschemaname'
and table_name ='yourtablename';

now, if 0 rows returned, it does not exist else it does.


--
Michael Austin
Database Consultant
Domain Registration and Linux/Windows Web Hosting Reseller
http://www.spacelots.com

Sorry - I wasn't asking how to write a select statement only how to get IF
working.

I think if I use it in this context it should work:

IF EXISTS (SELECT 1 FROM tbl) THEN
-- actions if tbl not empty
ELSE
-- actions if tbl empty
END IF;

---
Sam

I am trying this:

IF EXISTS (SELECT propnum FROM tblBooked) THEN
DROP TABLE IF EXISTS 'imagine','tbl1'
ELSE
DROP TABLE IF EXISTS 'imagine','tbl2'
END IF;

To see if a table (tblBooked) exists and if it does carry out one branch -
and if not then carry out the other.

Ideally I would like to write something linke:

IF EXISTS 'imagine','tblBooked' THEN
DROP TABLE IF EXISTS 'imagine','tbl1'
ELSE
DROP TABLE IF EXISTS 'imagine','tbl2'
END IF;

But any kludge that MySQL 4.1 likes will do.

It always throws an error. The DROP TABLE line works on its own. I am
finding the MySQL online manual to be unhelpful in solving my problem. Can
anyone see what I am doing wrong and advise? TIA.

---
Sam

> Ideally I would like to write something linke:
>
> IF EXISTS 'imagine','tblBooked' THEN
> DROP TABLE IF EXISTS 'imagine','tbl1'
> ELSE
> DROP TABLE IF EXISTS 'imagine','tbl2'
> END IF;
>
> But any kludge that MySQL 4.1 likes will do.

But version 4.1 does not like that much. In version 5, you can do this,
but only within a stored procedure.

Best regards

--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

"Willem Bogaerts" <EMAIL REMOVED> wrote in
message news:45b5c0cd$0$329$EMAIL REMOVED...

> But version 4.1 does not like that much. In version 5, you can do this,
> but only within a stored procedure.
>

Which is my problem. The web site that I have to work with is on 4.1 and not
likely to change for some time.

---
Sam

Is it possible to catch an error in 4.1 scripting? I have found that whan I
run a script and a line of the script cannot find a table instead of halting
the script continues to run.

Is there anyway to get MySQL to halt the running of a script if an error
occurs in one line?

It's a little like the whole system is running with the VB 'on error resume
next' running all of the time.

---
Sam