asked on

SQL/Coldfusion query to MS Access for Column's Data Type

How would a write a SQL/Coldfusion query to get the data type and size of a column in a MS Access database?

table name is #url.ViewTable#
column name is #columnName#

<CFQUERY name="q_GetColumnSpecs" datasource="evodb">

?????????

</CFQUERY>

Thanks for the help.

Crazee

When you say "size of a column" do you mean the character length of data stored in each field of a given column ( metadata for the attribute ) or do you mean how many records there are?

As far as I know, you can't get metadata through the SQL query, you should know the characteristics of the tables you are operating on ( one of the reasons is the security )

On the other hand, if you want the number if records returned by the query you use it's RecordCount property...

<CFQUERY name="q_GetColumnSpecs" datasource="evodb">
SELECT #columnName# FROM #url.ViewTable#
</CFQUERY>

<cfOutput>#q_GetColumnSpecs.RecordCount#</cfOutput>

cheers

Max_Davis

ASKER

No, I need the number of columns in a table.

For example if I queried this table it would return 3:

TableA [ Column1, Column2, Column3 ]

So, this isn't possible?

- Max

Max_Davis

ASKER

Sorry.. I mean it would return:

Int, Varchar, Int for DataType
and 2, 225, 6 for Size

- Max

Max_Davis

ASKER

Or just querying one column would return something like:

Type: VARCHAR
Size: 225

- Max

Crazee

As far as I know, it's not possible... Theoreticaly, metadata is stored in a table as well, but I'm not sure that the user can access it. Is there any reason why you can't have a look at the metadata in Access?

Max_Davis

ASKER

I am making an interface to easily create tables in a Access database without downloading the mdb file adding tables and then reuploading (using a hosting service), my application also browses current tables in the database, I want to be able to show the data for the columns.

Here is what I have so far:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<link href="/Evo57Style.css" rel="stylesheet" type="text/css">


<SCRIPT LANGUAGE="JavaScript"></SCRIPT>


<html>
<head>
      <title>Table Creator</title>
</head>

<body>

<CFOUTPUT>

<CFIF IsDefined("Form.DeleteTable")>
<CFQUERY name="q_DeleteTable" datasource="evodb">
DROP Table "#Form.DeleteTable#"
</CFQUERY>
</CFIF>

<CFIF IsDefined("Form.CreateIt")>

<CFTRY>
<CFQUERY name="q_InsertTable" datasource="evodb">
CREATE TABLE #Form.TableName#
(
ID AUTOINCREMENT NOT NULL,
       <CFLOOP from="1" to="#Form.TableFields#" index="i">
#Evaluate("form.Field#i#Name")# #Evaluate("form.Field#i#Type")#,
       </CFLOOP>
PRIMARY KEY(ID)
);
</CFQUERY>

<CFCATCH>

<p class="warning">
       <cfswitch expression = #cfcatch.ErrorCode#>
<cfcase value="37000">No table created: You cannot have spaces in your table or column names.</cfcase>
       <cfcase value="S0001">No table created: You already have a table named #Form.TableName#.</cfcase>
       <cfcase value="S0021">No table created: You had two columns with the same name.</cfcase>
<cfdefaultcase>#cfcatch.message#</cfdefaultcase>
</cfswitch>
      </p>

</CFCATCH>

</CFTRY>

</CFIF>

<CFQUERY name="q_ListTables" datasource="evodb">
SELECT Name, DateCreate
FROM MSysObjects
WHERE Type=1
AND Flags=0
ORDER BY Name;
</CFQUERY>

<CFIF q_ListTables.RecordCount gt 0>
<table cellspacing="0" cellpadding="3" border="0">
<tr><td colspan="5" class="gridtopend"><b>Current Tables</b></td></tr>
<tr><td class="grid">Name</td><td class="grid">Date Created</td><td class="grid">Record Count</td><td class="grid">Column Count</td><td class="gridend">Del</td>
<CFLOOP query="q_ListTables">

<CFQUERY name="q_GetRecordCount" datasource="evodb">
SELECT *
FROM #Name#
</CFQUERY>

<CFSET ColumnCount = 0>
<CFLOOP list="#q_GetRecordCount.columnList#" index="columnName">
<CFSET ColumnCount = ColumnCount + 1>
</CFLOOP>

<tr>
<td class="grid"><a href="tcreator.cfm?ViewTable=#Name#">#Name#</a></td>
<td class="grid">#DateFormat(DateCreate, "mmmm dd, yyyy")# - #TimeFormat(DateCreate, "hh:mm tt")#</td>
      <td class="grid">#q_GetRecordCount.RecordCount#</td>
      <td class="grid">#ColumnCount#</td>
<form action="tcreator.cfm" method="post" name="DeleteTable"><td class="gridend"><input type="hidden" name="DeleteTable" value="#Name#"><input onClick="return doubleCheck()" type="image" src="/images/close.gif" alt="Delete Table"></td></form>
</tr>
</CFLOOP>
</table>

<CFIF IsDefined("url.ViewTable")>
<CFQUERY name="q_GetColumnNames" datasource="evodb">
SELECT *
FROM #url.ViewTable#
</CFQUERY>
<br>
<table cellspacing="0" cellpadding="3" border="0">
<tr><td colspan="4" class="gridtopend"><b>Columns in #url.ViewTable#</b></td></tr>
<tr><td class="gridend">Name</td>
</tr>
<CFLOOP list="#q_GetColumnNames.columnList#" index="columnName">
<tr>
<td class="gridend">#columnName#</td>
</tr>
</CFLOOP>
</table>

</CFIF>

</CFIF>

<CFFORM action="tcreator.cfm" method="post">

<table cellspacing="0" cellpadding="3" border="0">
<tr><td colspan="2" class="gridtopend"><b>Create Table</b></td></tr>
<tr>
<td class="grid">Table Name:</td><td class="gridend"><input type="text" value="<CFIF IsDefined("Form.TableName") AND (NOT IsDefined("Form.CreateIt"))>#Form.TableName#</CFIF>" name="TableName"<CFIF IsDefined("Form.TableName") AND (NOT IsDefined("Form.CreateIt"))> READONLY</CFIF> size="30"></td>
</tr>
<tr>
<td class="grid">Field Size:</td><td class="gridend"><input type="text" value="<CFIF IsDefined("Form.TableFields") AND (NOT IsDefined("Form.CreateIt"))>#Form.TableFields#</CFIF>" name="TableFields"<CFIF IsDefined("Form.TableFields") AND (NOT IsDefined("Form.CreateIt"))> READONLY</CFIF> size="3"></td>
</td>
</table>

<CFIF IsDefined("Form.TableFields") and IsDefined("Form.TableName") and #Form.TableFields# lt 101 and #Form.TableFields# gt 0 and #Form.TableName# neq "" and #Form.TableFields# neq "" AND (NOT IsDefined("Form.CreateIt"))>

<br>
<table cellspacing="0" cellpadding="3" border="0">
<tr><td colspan="3" class="gridtopend"><b>Create Columns</b></td></tr>
<tr>
<td class="grid">##</td>
<td class="grid">Name</td>
<td class="gridend">DataType</td>
<CFLOOP from="1" to="#Form.TableFields#" index="i">
<tr>
<td class="grid">#i#</td><td class="grid"><input type="text" value="" name="Field#i#Name" size="30"></td>
<td class="gridend"><select name="Field#i#Type"><option value="int">Number</option><option value="varchar">Text</option><option value="datetime">Date/Time</option></select></td>
</tr>
</CFLOOP>
</table>
<br>
<input type="hidden" value="yes" name="CreateIt">
<input type="submit" value="Create Table">

<CFELSE>
<br>
<input type="submit" value="Create Columns">
<CFIF IsDefined("Form.TableFields") and IsNumeric("#Form.TableFields#") and #Form.TableFields# gte 101><br><br><span class="warning">Woah there! You can't create more than 100 fields.</span>
      <CFELSEIF IsDefined("Form.TableFields") and (NOT IsNumeric("#Form.TableFields#"))><br><br><span class="warning">Woah there! "Field Size" has to be a number.</span>
      </CFIF>
</CFIF>

</CFFORM>

</CFOUTPUT>

</body>
</html>

Crazee

Hmm...I know you can do it using JDBC ( ResultSetMetaData object ) but I never seen it done using strictly CF - perhaps someone else will know...

ASKER CERTIFIED SOLUTION

jyokum

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Max_Davis

ASKER

Beautiful jyokum! Thank you. Don't you love when you can do things that others claim can't be done?

- Max

Crazee

If you don't mind jyokum, I might use it in a future - I'll promise to give you points then...?

jyokum

Max_Davis.. yes, i do love it.

Crazee... go for it, i'm here to help