ColdFusion MX Un-Documentation Query Object

Updated on November 14, 2023
By Pete Freitag

ColdFusion MX Un-Documentation - Query Object coldfusion.sql.QueryTable

NOTE - Because these functions are undocumented, they may not be there in future releases, and Macromedia will not support code that uses these functions. Use at your own risk, for educational purposes only.

Some useful methods

Return Type	Method Signiture	Description
void	queryName.first()	jump to the first row of a query
void	queryName.last()	jump to the last row of a query
boolean	queryName.isFirst()	true if we are positioned at the first row
boolean	queryName.isLast()	true if we are positioned at the first row
boolean	queryName.next()	go to the next row, return true if there is a next row
boolean	queryName.previous()	go to the previous row, return true if there is a previous row
int	queryName.findColumn(String name)	get the internal zero based Column ID by name
void	sort(int columnID, boolean ascending)	sort by a column, use findColumn to get the column id
String	getColumnTypeName(int columnID)	gets the data type of a column, this one didn't work properly when I tested it, it would return NUMERIC for a field that was a varchar. There is a method called guessColumnType that was probably used to determine it, it guessed wrong.

Examples

<cfquery datasource="?" name="query">
SELECT Name FROM sometable
</cfquery>

Jump to beginning or end 
<cfoutput query="query">
Viewing names from <cfset query.first()>#query.Name# to <cfset query.last()> #query.Name#
</cfoutput>

Check to see if you are looking at first or last record 
<cfoutput query="query">
<cfif query.isFirst()>(</cfif>#query.Name#<cfif NOT query.isLast()>,<cfelse>)</cfif>
</cfoutput>

Skip or go back 
<cfoutput query="query">
 #query.Name#
 [next]<cfset query.next()>#query.Name#
 [previous]<cfset query.previous()>#query.Name# 
</cfoutput>

Resort the query

<cfoutput query="query">
#query.Name#
</cfoutput>

Get column types

<cfset numColumns = query.getColumnCount()>
<cfoutput>
<cfloop list="#query.ColumnList#" index="column">
name: #column#
type: #query.getColumnTypeName(query.findColumn(column))# 
</cfloop>
</cfoutput>
 Note this one didn't really return the correct datatypes, but it does return datatypes.

UPDATE

myquery.getColumnTypeName(myquery.findColumn('DOCUMENTNAME')) returns the
wrong type but


metadata = myquery.getMetaData();
metadata.getColumnType(myquery.findColumn('DOCUMENTNAME')
myquery.getColumnTypeName(metadata.getColumnType(myquery.findColumn('DOCUMENTNAME')))

gets the right type

-Justin MacCarthy

Thanks Justin!

The coldfusion.sql.QueryTable Class Stub

public class coldfusion.sql.QueryTable
	extends coldfusion.sql.Table
	implements javax.sql.RowSet, coldfusion.wddx.RecordSet
{
	/*** CONSTRUCTORS ***/
	public coldfusion.sql.QueryTable(int)

	public coldfusion.sql.QueryTable(com.allaire.cfx.Query)

	public coldfusion.sql.QueryTable(java.sql.ResultSet)

	public coldfusion.sql.QueryTable(coldfusion.sql.QueryTable)

	public coldfusion.sql.QueryTable(int ,[Ljava.lang.String;)

	public coldfusion.sql.QueryTable(int ,java.lang.String)

	public coldfusion.sql.QueryTable()


	/*** METHODS ***/
	public static java.lang.String getColumnTypeName(int)

	public static final java.lang.Object resolveObject(java.lang.Object, int, int)
		throws java.sql.SQLException

	public native int hashCode()

	public final void wait()
		throws java.lang.InterruptedException

	public final void wait(long, int)
		throws java.lang.InterruptedException

	public final native void wait(long)
		throws java.lang.InterruptedException

	public final native java.lang.Class getClass()

	public boolean equals(java.lang.Object)

	public final native void notify()

	public final native void notifyAll()

	public void sort(int, boolean, boolean)

	public void sort(int, boolean)

	public void sort(coldfusion.sql.imq.OrderByColumnList)

	public void sort(coldfusion.sql.imq.OrderByColumnList, boolean)

	public coldfusion.sql.imq.Row getRow(int)

	public void setColumnNames([Ljava.lang.String;)

	public void addColumnName(java.lang.String)

	public void removeRows(int, int)

	public int guessColumnType(int)
		throws coldfusion.sql.imq.imqException

	public java.lang.String toString()

	public java.lang.Object getField(int, int)

	public void clear()

	public void setField(int, int, java.lang.Object)

	public int getColumnCount()

	public void setPreviousTable(coldfusion.sql.Table)

	public void setNextTable(coldfusion.sql.Table)

	public int getRowCount()

	public int addRows(int)

	public [Ljava.lang.String; getColumnNames()

	public coldfusion.sql.Table nextTable()

	public coldfusion.sql.Table previousTable()

	public coldfusion.sql.Table firstTable()

	public coldfusion.sql.Table lastTable()

	public java.util.List getColumn(java.lang.String)

	public java.util.List getColumn(int)

	public java.lang.String getColumnName(int)

	public [B getBytes(java.lang.String)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public [B getBytes(int)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public void setReadOnly(boolean)

	public boolean next()

	public boolean previous()

	public boolean getBoolean(int)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public boolean getBoolean(java.lang.String)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public int getType()

	public long getLong(java.lang.String)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public long getLong(int)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public void close()

	public java.lang.Object getObject(java.lang.String, java.util.Map)

	public java.lang.Object getObject(int)

	public java.lang.Object getObject(java.lang.String)

	public java.lang.Object getObject(int, java.util.Map)

	public java.sql.Ref getRef(java.lang.String)

	public java.sql.Ref getRef(int)

	public void setTime(int, java.sql.Time, java.util.Calendar)

	public void setTime(int, java.sql.Time)

	public java.sql.Time getTime(java.lang.String)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public java.sql.Time getTime(int)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public java.sql.Time getTime(int, java.util.Calendar)

	public java.sql.Time getTime(java.lang.String, java.util.Calendar)

	public java.sql.Date getDate(java.lang.String, java.util.Calendar)

	public java.sql.Date getDate(java.lang.String)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public java.sql.Date getDate(int, java.util.Calendar)

	public java.sql.Date getDate(int)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public byte getByte(int)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public byte getByte(java.lang.String)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public java.lang.String getString(java.lang.String)

	public java.lang.String getString(int)

	public void updateTime(int, java.sql.Time)

	public void updateTime(java.lang.String, java.sql.Time)

	public void setDate(int, java.sql.Date, java.util.Calendar)

	public void setDate(int, java.sql.Date)

	public int getInt(int)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public int getInt(java.lang.String)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public void setByte(int, byte)

	public short getShort(int)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public short getShort(java.lang.String)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public float getFloat(java.lang.String)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public float getFloat(int)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public double getDouble(java.lang.String)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public double getDouble(int)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public void setBoolean(int, boolean)

	public void setShort(int, short)

	public void setInt(int, int)

	public void setLong(int, long)

	public void setFloat(int, float)

	public void setDouble(int, double)

	public boolean isReadOnly()

	public void execute()

	public boolean first()

	public boolean last()

	public void setCharacterStream(int, java.io.Reader, int)

	public java.io.Reader getCharacterStream(java.lang.String)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public java.io.Reader getCharacterStream(int)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public void setType(int)

	public java.sql.SQLWarning getWarnings()

	public java.lang.String getPassword()

	public void setPassword(java.lang.String)

	public boolean absolute(int)

	public void setUrl(java.lang.String)

	public void beforeFirst()

	public void afterLast()

	public java.sql.ResultSetMetaData getMetaData()

	public void setObject(int, java.lang.Object, int, int)

	public void setObject(int, java.lang.Object)

	public void setObject(int, java.lang.Object, int)

	public void setRef(int, java.sql.Ref)

	public java.lang.String getUsername()

	public void setUsername(java.lang.String)

	public void setMetaData(coldfusion.sql.QueryTableMetaData)

	public java.lang.String getDataSourceName()

	public void setDataSourceName(java.lang.String)

	public void setTransactionIsolation(int)

	public int getMaxFieldSize()

	public void setMaxFieldSize(int)

	public int getMaxRows()

	public void setMaxRows(int)

	public void setEscapeProcessing(boolean)

	public int getQueryTimeout()

	public void setQueryTimeout(int)

	public void clearWarnings()

	public int getUpdateCount()

	public void setFetchDirection(int)

	public int getFetchDirection()

	public void setFetchSize(int)

	public int getFetchSize()

	public void clearParameters()

	public void setArray(int, java.sql.Array)

	public void setAsciiStream(int, java.io.InputStream, int)

	public void setBigDecimal(int, java.math.BigDecimal)

	public void setBinaryStream(int, java.io.InputStream, int)

	public void setBlob(int, java.sql.Blob)

	public void setBytes(int, [B)

	public void setClob(int, java.sql.Clob)

	public void setNull(int, int, java.lang.String)

	public void setNull(int, int)

	public void setString(int, java.lang.String)

	public void setTimestamp(int, java.sql.Timestamp)

	public void setTimestamp(int, java.sql.Timestamp, java.util.Calendar)

	public int getTransactionIsolation()

	public java.util.Map getTypeMap()

	public void setTypeMap(java.util.Map)

	public void populate(coldfusion.sql.QueryTable)
		throws java.sql.SQLException

	public void populate(com.allaire.cfx.Query)
		throws java.sql.SQLException

	public void populate(java.sql.ResultSet, int)
		throws java.sql.SQLException

	public void populate(java.sql.ResultSet)
		throws java.sql.SQLException

	public void setUpdateCount(int)

	public int addColumn(java.lang.String)

	public int addColumn(java.lang.String, java.util.Vector)

	public int findColumn(java.lang.String)

	public java.lang.String getRecordsetType()

	public int getRow()

	public int getRecordCount()

	public int getCurrentRow()

	public [Ljava.lang.String; getColumnList()

	public boolean wasNull()

	public java.math.BigDecimal getBigDecimal(java.lang.String, int)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public java.math.BigDecimal getBigDecimal(java.lang.String)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public java.math.BigDecimal getBigDecimal(int, int)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public java.math.BigDecimal getBigDecimal(int)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public java.sql.Timestamp getTimestamp(int)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public java.sql.Timestamp getTimestamp(java.lang.String, java.util.Calendar)

	public java.sql.Timestamp getTimestamp(int, java.util.Calendar)

	public java.sql.Timestamp getTimestamp(java.lang.String)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public java.io.InputStream getAsciiStream(java.lang.String)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public java.io.InputStream getAsciiStream(int)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public java.io.InputStream getUnicodeStream(int)

	public java.io.InputStream getUnicodeStream(java.lang.String)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public java.io.InputStream getBinaryStream(java.lang.String)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public java.io.InputStream getBinaryStream(int)
		throws coldfusion.sql.QueryTable$TypeConversionException

	public java.lang.String getCursorName()

	public boolean isBeforeFirst()

	public boolean isAfterLast()

	public boolean isFirst()

	public boolean isLast()

	public boolean relative(int)

	public int getConcurrency()

	public boolean rowUpdated()

	public boolean rowInserted()

	public boolean rowDeleted()

	public void updateNull(java.lang.String)

	public void updateNull(int)

	public void updateBoolean(java.lang.String, boolean)

	public void updateBoolean(int, boolean)

	public void updateByte(java.lang.String, byte)

	public void updateByte(int, byte)

	public void updateShort(int, short)

	public void updateShort(java.lang.String, short)

	public void updateInt(int, int)

	public void updateInt(java.lang.String, int)

	public void updateLong(int, long)

	public void updateLong(java.lang.String, long)

	public void updateFloat(java.lang.String, float)

	public void updateFloat(int, float)

	public void updateDouble(int, double)

	public void updateDouble(java.lang.String, double)

	public void updateBigDecimal(int, java.math.BigDecimal)

	public void updateBigDecimal(java.lang.String, java.math.BigDecimal)

	public void updateString(java.lang.String, java.lang.String)

	public void updateString(int, java.lang.String)

	public void updateBytes(int, [B)

	public void updateBytes(java.lang.String, [B)

	public void updateDate(int, java.sql.Date)

	public void updateDate(java.lang.String, java.sql.Date)

	public void updateTimestamp(int, java.sql.Timestamp)

	public void updateTimestamp(java.lang.String, java.sql.Timestamp)

	public void updateAsciiStream(java.lang.String, java.io.InputStream, int)

	public void updateAsciiStream(int, java.io.InputStream, int)

	public void updateBinaryStream(java.lang.String, java.io.InputStream, int)

	public void updateBinaryStream(int, java.io.InputStream, int)

	public void updateCharacterStream(int, java.io.Reader, int)

	public void updateCharacterStream(java.lang.String, java.io.Reader, int)

	public void updateObject(int, java.lang.Object)

	public void updateObject(int, java.lang.Object, int)

	public void updateObject(java.lang.String, java.lang.Object)

	public void updateObject(java.lang.String, java.lang.Object, int)

	public void insertRow()

	public void updateRow()

	public void deleteRow()

	public void refreshRow()

	public void cancelRowUpdates()

	public void moveToInsertRow()

	public void moveToCurrentRow()

	public java.sql.Statement getStatement()

	public java.sql.Blob getBlob(int)

	public java.sql.Blob getBlob(java.lang.String)

	public java.sql.Clob getClob(int)

	public java.sql.Clob getClob(java.lang.String)

	public java.sql.Array getArray(int)

	public java.sql.Array getArray(java.lang.String)

	public void addRowSetListener(javax.sql.RowSetListener)

	public java.lang.String getCommand()

	public boolean getEscapeProcessing()

	public java.lang.String getUrl()

	public void removeRowSetListener(javax.sql.RowSetListener)

	public void setCommand(java.lang.String)

	public void setConcurrency(int)


	/*** FEILDS ***/
	public static final int FETCH_FORWARD
	public static final int FETCH_REVERSE
	public static final int FETCH_UNKNOWN
	public static final int TYPE_FORWARD_ONLY
	public static final int TYPE_SCROLL_INSENSITIVE
	public static final int TYPE_SCROLL_SENSITIVE
	public static final int CONCUR_READ_ONLY
	public static final int CONCUR_UPDATABLE
	public static final int NOT_FOUND

}

cfml jdbc query

ColdFusion MX Un-Documentation Query Object was first published on February 20, 2003.

If you like reading about cfml, jdbc, or query then you might also like:

Discuss / Follow me on Twitter ↯

Tweet Follow @pfreitag