Copy link to clipboard
Copied
With "myQuery.ColumnList", I get the list of columns in a database query.
Using GetMetaData(), I can then read the data type of each column using typeName.
Is there a way to get additional information, such as the length of varchar data types?
You can use cfdbinfo. I shall now give you a quick proof-of-concept.
You can improve it using the struct or array functions.
<cfdbinfo
type="columns"
table="myTable"
datasource="myDSN"
name="tableColumnData">
<!--- Dump the properties of all the columns of myTable. 'tableColumnData' is a query object. --->
<!---<cfdump var="#tableColumnData#">--->
<!---Get the size of the column named 'myCol'--->
<cfloop index="i" from="1" to="#tableColumnData.recordcount#">
<cfset columnSize = tableColum
...
Copy link to clipboard
Copied
You can use cfdbinfo. I shall now give you a quick proof-of-concept.
You can improve it using the struct or array functions.
<cfdbinfo
type="columns"
table="myTable"
datasource="myDSN"
name="tableColumnData">
<!--- Dump the properties of all the columns of myTable. 'tableColumnData' is a query object. --->
<!---<cfdump var="#tableColumnData#">--->
<!---Get the size of the column named 'myCol'--->
<cfloop index="i" from="1" to="#tableColumnData.recordcount#">
<cfset columnSize = tableColumnData["column_name"][i]=="myCol" ? tableColumnData["column_size"][i] : "Column not found or column-size cannot be determined.">
<cfif tableColumnData["column_name"][i]=="myCol">
<cfbreak>
</cfif>
</cfloop>
Size of column myCol:<cfoutput> #columnSize#</cfoutput>
Copy link to clipboard
Copied
Sometimes it's that simple.
With "cfdbinfo," of course.
Thanks for the tip.
Copy link to clipboard
Copied
My pleasure.
Copy link to clipboard
Copied
Here's an array function you may use instead of the loop:
<!---Get the size of the column named 'myCol'--->
<cfset columnIndex = arrayFindNoCase(tableColumnData["column_name"], "myCol") ?: 0>
<cfset columnSize = columnIndex != 0 ? tableColumnData["column_size"][columnIndex] : "Column not found or column-size cannot be determined.">
Size of column:<cfoutput> #columnSize#</cfoutput>
Find more inspiration, events, and resources on the new Adobe Community
Explore Now