IF EXISTS Column...

[Ren Hoek]

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

[Gordon - Phosphor Media]

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

[Ren Hoek]

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

[Kent Multer]

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

[Ren Hoek]

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.

[Kent Multer]

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.

[Ren Hoek]

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.

[Gordon - Phosphor Media]

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>