How to reference column names, if using select *

[tushars2001]

Hi,

How do i reference column names for outputting data, when using select *, and not aware of column names (and number of columns) in advance.

Even if i could get column names in other vars. I'm new to CF so question may be silly.

getting column names:-

<cfquery datasource="RTW_ORA" name="cn">
SELECT COLUMN_NAME
FROM ALL_COL_COMMENTS
WHERE TABLE_NAME = '#meas#'
</cfquery>

getting data:-

<cfquery datasource="RTW_ORA" name="cd">
SELECT *
FROM #meas#
</cfquery>

How to output all the data?

Any help would be highly appriciated!

Thanks,

Tushar Saxena

[Adam Cameron.]

If you're new to CF, then the best first place to start is to read the docs.  Start with this lot:

http://livedocs.adobe.com/coldfusion/8/htmldocs/queryDB_1.html#1127430

http://livedocs.adobe.com/coldfusion/8/htmldocs/Tags_p-q_17.html#1102316

http://livedocs.adobe.com/coldfusion/8/htmldocs/Tags_m-o_16.html#1101659

--

Adam

[Guest]

In general, using "select * " isn't always the exact query you need, but it's a starting point. Try to only pull in the columns you know you're going to need.

One of the most useful tags in cf is <cfdump>. Right above where you want to start your organized output, put <cfdump var="#cn#"> to show the entire dataset, including the column names. You have all the information you need to start formatting your output.

[tclaremont]

My understanding, and someone please correct me if I am wrong, is that using SELECT * actually results in two queries being made to the database. The first query is invisible, and is used to ask the database table what fields exist. The second query retrieves the actual content of those fields.

By specifying the names of the fields you need (rather than the wildcard) it negates the need for the first query. It also keeps the environment cleaner by returning only the fields that are needed.

This is one of those cases where CF lets you "get away" with the easy method, but most experienced developers avoid it.

But, is the case where you do not know what fields you are staring with, I would recommend doing a SELECT * query, and use the maxrows attribute of 1. This will reduce the size of the data set returned. You only need one record, and if the database has 4 million it is going to make for a heck of a table.

Then, just use a CFDUMP statement and indicate the name of the query. That will display the fields and their values on the screen. Again, using a maxrows attribute of `1 will make life easier.

[Dan_Bracuk]

Regarding "

But, is the case where you do not know what fields you are staring with, I would recommend doing a SELECT * query, and use the maxrows attribute of 1. This will reduce the size of the data set returned. You only need one record, and if the database has 4 million it is going to make for a heck of a table."

I don't think so.  My understanding is that the db sends all records to Cold Fusion but Cold Fusion ignores all but the first.  Sending all that data across the network is not a good idea.

[Adam Cameron.]

Dan's mostly correct.  The MAXROWS attribute only throttles the process at JDBC level, so the DB still fetches the entire recordset, and will start to send it back to the JDBC driver.  After MAXROWS worth of records are fetched, the drivers tells the DB it's got enough, and the DB should stop sending.  However one is still putting a lot of unnecessary load on the DB here.

One should always row-limit one's queries at DB level, not JDBC level (so SELECT TOP n, LIMIT n, WHERE ROWNUM <= n, etc).

All DBs (well: all proper DBs) provide a mechanism for fetching this information, be it SP_HELP on SQL Server, or the USER_TAB_COLUMNS on Oracle, and similar solutions on other platforms. Indeed CF itself provides <cfdbinfo> (probably the best solution to this, as it's portable).  One can use a select * on the table to effect this, but it's not a very elegant way of doing it.

All this said, how often is one not going to know the columns one wants to fetch?  The only situations I can think of is if one is writing a generic DB client, or perhaps some sort of data export routine.  I would say that for almost all situations, one knows which columns one wants to fetch.  And so one should fetch them by name.

--

Adam

[Dan_Bracuk]

Regarding:

"All this said, how often is one not going to know the columns one wants to fetch?  The only situations I can think of is if one is writing a generic DB client, or perhaps some sort of data export routine.  I would say that for almost all situations, one knows which columns one wants to fetch.  And so one should fetch them by name."

When one forgets, or didn't memorize it in the first place.  Select * isn't necessarily in the final source code, but it's a quick reference.  I use

select *

from sometable

where 1 = 2

fairly frequently to get myself started.

[BKBK · Accepted solution]

How do i reference column names for outputting data, when using select *, and not aware of column names (and number of columns) in advance.

Even if i could get column names in other vars. I'm new to CF so question may be silly. getting column names:-

<cfquery datasource="RTW_ORA" name="cn">
SELECT COLUMN_NAME
FROM ALL_COL_COMMENTS
WHERE TABLE_NAME = '#meas#'
</cfquery>

getting data:-

<cfquery datasource="RTW_ORA" name="cd">
SELECT *
FROM #meas#
</cfquery>

How to output all the data?

Your question is not silly. You could use the concept of query-of-a-query, and cfquery's attributes name and result and their properties.

<!--- Start with the general query --->

<cfquery datasource="RTW_ORA" name="cd" result="resQ">
SELECT *
FROM #meas#
</cfquery>

<!--- For the list of column names, either cd.columnList or resQ.columnList will do. Each is a built-in comma-delimited list of the columns in the query cd. --->

<!--- if you need the list of columns --->
<cfset column_names = q.columnlist>
<cfset no_of_columns = listLen(q.columnlist)>
<p>
<cfoutput >
column names: #column_names#<br>
number of columns: #no_of_columns#<br>
</cfoutput>
</p>

<!--- Now comes the query of a query. It may include, for example, a where-clause, order-by, etc. --->

<cfquery dbType="query" name="QoQ" result="resQoQ">
SELECT #column_names#
FROM cd
</cfquery>

<!--- the SQL of the QoQ --->
<p>
Query of a query SQL: <cfoutput >#resQoQ.sql#<br></cfoutput>
</p>

<!--- Output the column names and corresponding values, per row --->

<!--- for the query --->

<p>

Query:<br>
<cfoutput query="cd">
    <cfloop list="#column_names#" index="column">
    <!--- currentrow is built-in property of query--->
    #column# : #cd[column][currentrow]#
    </cfloop><br><!--- end of row --->
</cfoutput>

</p>

<!--- for the query of a query --->

<p>

QoQ:<br>
<cfoutput query="QoQ">
    <cfloop list="#QoQ.columnList#" index="column">
    #column# : #QoQ[column][currentrow]#
    </cfloop><br>
</cfoutput>

</p>

[tushars2001]

This is awesome! Thanks a  million! and sorry for replying late as i was OOO.

[BKBK]

This is awesome! Thanks a  million! and sorry for replying late as i was OOO.

It's OK.