Professional Web Applications Themes

Is this possible? - MySQL

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 ...

  1. #1

    Default Is this possible?

    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 Guest

  2. #2

    Default Re: Is this possible?

    Sam Smith wrote:
     

    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
    Michael Guest

  3. #3

    Default Re: Is this possible?

    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


    Sam Guest

  4. #4

    Default Re: Is this possible?

    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


    Sam Guest

  5. #5

    Default Re: Is this possible?

    > Ideally I would like to write something linke: 

    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 Guest

  6. #6

    Default Re: Is this possible?

    "Willem Bogaerts" <maardanzonderditstuk.nl> wrote in
    message news:45b5c0cd$0$329$xs4all.nl...
     

    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


    Sam Guest

  7. #7

    Default Re: Is this possible?

    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


    Sam Guest

  8. #8

    Default Is this possible?

    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.

    Thank you for your help.

    ---
    Sam


    Sam Guest

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139