CODEWITHSUNDEEP
mysqlsqlcoldfusion
Keith Stewart
Keith Stewart

Reputation: 43

Retrieve MySQL database column names (with table name) using Coldfusion

hopefully somebody will help me as this has had me scratching my head.

As most would already know of 2 easy ways of retrieving mysql query column names using coldfusion:

1. <cfset arrColumns = ListToArray(thequery.columnList) />
2. <cfset arrColumns = getMetaData(thequery) />

both of which would successfully create an array of column names (1 in alphanumeric order, 2 in order retrieved from the database).

For example:

<cfquery name="thequery" datasource="thedatasource">
    SELECT a.title, b.name 
    FROM tablea a
    JOIN tableb b ON b.a_id = a.id
</cfquery>
<cfset arrColumns = ListToArray(thequery.columnList) />

The above would successfully output an array with values:

arrColumns[1]: "name" 
arrColumns[2]: "title"

What I need it to do is to output:

arrColumns[1]: "tableb.name" 
arrColumns[2]: "tablea.title"

...basically, to include the database table name along with the column name. This has had me scratching my head for some time, and any help would be greatly appreciated. I have already searched Google and stackoverflow and found no answers.

Thank you

Upvotes: 4

Views: 1041

Answers (2)

Dan Bracuk
Dan Bracuk

Reputation: 20794

I suggest this approach. Put your query into a function that accepts a mandatory argument regarding the inclusion of table names. If you have to provide them, then include them in the column alias, surrounded by double quotes. Something like this:

select table1.field1 "table1.field1"
, table1.field2 "table1.field2"
etc

Upvotes: 0

Adam Cameron
Adam Cameron

Reputation: 29870

First things first, JDBC doesn't reliably expose the table info you need, and the relevant method (getTableName()) in ColdFusion's implementation simply raises an exception. So the short version is that you cannot do what you want, how you want to.

That said, I think you should approach things differently anyhow. The idea of having one data structure which contains both data and schema information seems... wrong to me.

Upvotes: 1

Related Questions