How can I use ColdFusion to modify database structure?

[lee704]

I have a project that requires me to import hundreds of tables into PostgreSQL from dBase.  The import is simple enough.  However, I need to rename all the columns to meet my organization,s naming conventions.  This requires me to prepend a data type name to the column name.

For example... I have three columns named NAME, LOCATION and EDOP.  These columns are typed varchar, number and date respectively.  These columns need to be altered so their new column names are vchar_name, num_location and date_edop respectively.

Since there are thousands of columns, performing this task manually is not plausible.  I think I can use CF to perform the task for me, but I am not certain how to do so.

Has anyone performed a similar task and would be willing to share some code example?

Thanks,
Lee

[ilssac · Accepted solution]

IF you know how to preform these translations with SQL then you know how to do it with ColdFusion.

If you only know hot to preform these trnalations with a Databasse management tool, you will first need to learn the SQL that the tool really uses to make these translations.

You Can pass ANY SQL statements through a <cfquery...> block that your database management system understands and the account used to connect to ColdFusion is allowed to use.

P,.S.  That is latter point is important AS good web security states that the normal DSN connected to ColdFusion should never have unnecessary permissions, such as ones to create, modify or drop tables.  So you may very well, (I would hope so at least) need to create a special DSN with the necessary permissions for this task.  And, of course, don't let this DSN be used for other taskes where its permissions are not needed.

[lee704]

Thanks Ian.  I do have the DSN locked down tight.  I also know the syntax to alter the table in SQL.  Where I am confused is how to do the loop in CF to actually perform the change.  For example, I see this as a nested loop situation.

First, I need to loop through the tables in the database to get only those tables I need.  Then I need to loop through each of those tables to get all the column names and their data types.  Then I need to perform the alter command on each column in each table to change the column name.

I just don't know the way to write this in CF.  Thoughts?

[ilssac]

Sounds like you know what to do with the loops.  You may need to figure out where to get the data for the loops.  All databaes have system tables that contain all the data for the other tables.  You would just need to use a search engine to find the specific system tables for your database managment system of choice.

Then you use normal SQL select statements to query these tables for the data need for you loops, such as the column names and types.

[lee704]

OK.  With that said.  Would anyone be willing to share any code snippets on this?

Thanks!