Announcement

Collapse
No announcement yet.

IF EXISTS Column...

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    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, 11:39 AM.

    #2
    Re: IF EXISTS Column...

    AFAIK you can't do this in MySQL without using a stored procedure.
    Gordon Currie
    Phosphor Media - "Your Success is our Business"

    Improve Your Customer Service | Get MORE Customers | Edit Any Document Easily | Free Modules | Follow Us on Facebook
    phosphormedia.com

    Comment


      #3
      Re: IF EXISTS Column...

      Understand. So this opens the question up to any sort of alternative method using MvQUERY and/or MvOPENVIEW?

      Comment


        #4
        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

        Comment


          #5
          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, 09:10 AM.

          Comment


            #6
            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

            Comment


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

              Comment


                #8
                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>
                Gordon Currie
                Phosphor Media - "Your Success is our Business"

                Improve Your Customer Service | Get MORE Customers | Edit Any Document Easily | Free Modules | Follow Us on Facebook
                phosphormedia.com

                Comment

                Working...
                X