Altering database table through Coldfusion and SQL
It's my first time developing an application where the client needs to add and delete table-fields. It's a database-table that stores a few hundred fields, with different types of values, integers, floats and money.
I never needed this until today, so I though I just share it with you. It's really simple, but I like to use my blog as a personal documentation as well (lazy me...)
ALTER TABLE tblMyTable
ADD #form.FieldName# #form.Type#
</cfquery>
I check that form.fieldname contains only letters, numbers and underscores (and that it can't start with a number). form.type is a simple select where you can choose for an INT, FLOAT and MONEY.
ALTER TABLE tblMyTable
DROP COLUMN #form.FieldName#
</cfquery>
The odd thing is that documentation says you need "ADD COLUMN" for the first query, but mine runs only with "ADD" and not the COLUMN constraint. The DROP statement on the other hand NEEDS the COLUMN constraint... go figure.
extensively for code that will need to move from Dev to Staging to Production servers. At that point, I no longer have to worry about making necessary database changes "just before" my code gets
pushed up to the next server environment. I can write code that tests for an existing field and creates it if necessary *at the time the code change expects that new field*, which is beautiful and
very slick. For those types of apps, I tend to run such queries under a different DSN which allows CREATE, ALTER, and DROP while the rest of the app runs queries under a barebones DSN which
limits activities to SELECT, UPDATE, INSERT, and DELETE.
-J-