24/7 Support - 800.608.6482

Miva Merchant Community Forums

  1. #1
    Join Date
    Sep 2006
    Posts
    2,578

    Default IF EXISTS Column...

    I found a couple of forum posts/arguments on this topic, but no solutions from what I can see. I have tried a number of variations to the following, with no luck. It compiles without issue, but shoots MySQL version errors with every attempt:

    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = ‘table_name’ AND COLUMN_NAME = ‘FIELDNAME’) BEGIN select fieldname from table_name
    END

    Has anyone ever had any luck making something like this work? (not looking to retain compatibility with MivaSQL)

    Thanks,

    Dan
    Last edited by Dan - Glendale Designs; 01-28-13 at 11:39 AM.
    Dan - Glendale Designs
    http://www.glendaledesigns.com
    sales@glendaledesigns.com
    623-322-6066

  2. #2
    Join Date
    Mar 2006
    Location
    Kirkland, WA USA
    Posts
    203

    Default Re: IF EXISTS Column...

    AFAIK you can't do this in MySQL without using a stored procedure.

  3. #3
    Join Date
    Sep 2006
    Posts
    2,578

    Default Re: IF EXISTS Column...

    Understand. So this opens the question up to any sort of alternative method using MvQUERY and/or MvOPENVIEW?
    Dan - Glendale Designs
    http://www.glendaledesigns.com
    sales@glendaledesigns.com
    623-322-6066

  4. #4
    Join Date
    Mar 2006
    Posts
    1,902

    Default Re: IF EXISTS Column...

    I think that the IF EXISTS clause is only legal in DROP statements.

    It's not clear from the example what you're trying to do. If you want to find out whether a table exists, you can use MvOPENVIEW on it, and see if you get an error. Or you can use an MvOPENVIEW with a SHOW TABLES query, and read the list of results to see if the table you want is already in the database. If you want to find out whether a particular column exists in a specific table, you can use SHOW COLUMNS.

    HTH --
    Kent Multer
    Magic Metal Productions
    http://TheMagicM.com
    * Web developer/designer
    * E-commerce and Miva
    * Author, The Official Miva Web Scripting Book -- available on-line:
    http://www.amazon.com/exec/obidos/IS...icmetalproducA

  5. #5
    Join Date
    Sep 2006
    Posts
    2,578

    Default Re: IF EXISTS Column...

    Want to check for existance of column and perform an action based on that columns existance. (in this specific case it is for drop the column) Will try SHOW COLUMNS and see what I can come up with.
    Last edited by Dan - Glendale Designs; 01-29-13 at 09:10 AM.
    Dan - Glendale Designs
    http://www.glendaledesigns.com
    sales@glendaledesigns.com
    623-322-6066

  6. #6
    Join Date
    Mar 2006
    Posts
    1,902

    Default Re: IF EXISTS Column...

    If you just want to drop a column, I think you can use DROP COLUMN, and maybe DROP COLUMN IF EXISTS. The MySQL 5.1 reference manual seems to be short on details about this.
    Kent Multer
    Magic Metal Productions
    http://TheMagicM.com
    * Web developer/designer
    * E-commerce and Miva
    * Author, The Official Miva Web Scripting Book -- available on-line:
    http://www.amazon.com/exec/obidos/IS...icmetalproducA

  7. #7
    Join Date
    Sep 2006
    Posts
    2,578

    Default Re: IF EXISTS Column...

    Yeah, that's kind of what I have found as well, ie. limited documentation on any sort of DROP COLUMN IF EXISTS. My fear would be MySQL versions prior to 5.1, which from what I can find did not support any sort of DROP COLUMN IF EXISTS, just DROP TABLE.

    I am working on a complete module rewrite, and I would love to clean house on the usual update functions using some sort of DROP COLUMN IF EXISTS method. The other option in the back of my head would be to fire drop table actions, however supress error triggered when the column has already been removed at some point.
    Dan - Glendale Designs
    http://www.glendaledesigns.com
    sales@glendaledesigns.com
    623-322-6066

  8. #8
    Join Date
    Mar 2006
    Location
    Kirkland, WA USA
    Posts
    203

    Default Re: IF EXISTS Column...

    Rather than try and do this at the query level, I use a function. Since I would never do this 1000 times at runtime, it works for me:

    <MvFUNCTION NAME="FieldExists" PARAMETERS="module VAR, table, field" STANDARDOUTPUTLEVEL="" ERROROUTPUTLEVEL="">

    <MvCOMMENT> get first record or just empty record </MvCOMMENT>
    <MIVA MvOPENVIEW_Error="nonfatal, nodisplay">
    <MvOPENVIEW NAME="Merchant" VIEW="Fields"
    QUERY="{ 'SELECT * FROM ' $ g.Store_Table_Prefix $ l.table $ ' LIMIT 0,1' }">

    <MvCOMMENT> get field names and count </MvCOMMENT>
    <MvREVEALSTRUCTURE NAME="Merchant" VIEW="Fields" VARIABLE="l.fieldinfo">
    <MvASSIGN NAME="l.fieldmax" VALUE="{ miva_array_max(l.fieldinfo) }">
    <MvASSIGN NAME="l.fieldindex" VALUE= 1>

    <MvCOMMENT> field loop </MvCOMMENT>
    <MvWHILE EXPR="{ l.fieldindex LE l.fieldmax }">
    <MvCOMMENT> if a field name matches, then return 1 </MvCOMMENT>
    <MvIF EXPR="{ l.fieldinfo[ l.fieldindex ]:field_name EQ l.field }">
    <MvCLOSEVIEW NAME="Merchant" VIEW="Fields">
    <MvFUNCTIONRETURN VALUE= 1>
    </MvIF>
    <MvCOMMENT> increment fields </MvCOMMENT>
    <MvASSIGN NAME="l.fieldindex" VALUE= "{ l.fieldindex +1 }">
    </MvWHILE>

    <MvCLOSEVIEW NAME="Merchant" VIEW="Fields">

    <MvCOMMENT> we got this far without a match, return 0 </MvCOMMENT>
    <MvFUNCTIONRETURN VALUE= 0>
    </MvFUNCTION>

Posting Rules

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