cfquery return datatypes

2010-02-26 Thread Joshua Rowe 

Hello.  Is there a way to return a datatype list from your query along with the 
column list?  Example:

cfquery name=qryTest datasource=test
SELECT * FROM tblTest;
/cfquery

cfoutput
#qryTest.columnlist#br
#qryTest.datatypes#
/cfoutput

Thanks! 

~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331178
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: cfquery return datatypes

2010-02-26 Thread Leigh 

 Hello.  Is there a way to return a datatype list from
 your query along with the column list?  Example:

IIRC, there is no direct method. But you could certainly extract that 
information using getMetaData()

cfdump var=#getMetaData(qryTest)#




~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331179
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

re: cfquery return datatypes

2010-02-26 Thread Jason Fisher 

You can usually get that info from the database's system tables, depending 
on your RDBMS.   (In the following queries, 'typename' will give you the 
datatype.)

MSSQL:

SELECT syscolumns.colid, 
syscolumns.name, 
syscolumns.colorder AS sortorder, 
syscolumns.prec AS collen, 
syscolumns.xtype, 
syscolumns.typestat, 
syscolumns.xusertype, 
syscolumns.isnullable,
systypes.name AS typename, 
(   SELECT COUNT(*)
FROM sysindexkeys
WHERE id = syscolumns.id
AND indid = 1
AND colid = syscolumns.colid
) AS isPrimary
FROM syscolumns LEFT JOIN
systypes ON syscolumns.xtype = systypes.xtype 
AND syscolumns.xusertype = systypes.xusertype
WHERE id = OBJECT_ID(cfqueryparam cfsqltype=CF_SQL_VARCHAR 
value=#arguments.tableName#)
ORDER BY colorder

Oracle:

SELECT table_name colid, 
column_name name, 
column_id sortorder, 
data_length collen, 
nullable isnullable, 
data_type typename, 
(   SELECT COUNT(*)
FROM user_constraints c INNER JOIN
user_cons_columns cc ON c.constraint_name = 
cc.constraint_name
WHERE c.table_name = cfqueryparam 
cfsqltype=CF_SQL_VARCHAR value=#UCase(arguments.tableName)#
AND c.constraint_type = cfqueryparam 
cfsqltype=CF_SQL_VARCHAR value=P
AND cc.column_name = user_tab_columns.column_name
) AS isPrimary
FROM user_tab_columns
WHERE table_name = cfqueryparam cfsqltype=CF_SQL_VARCHAR 
value=#UCase(arguments.tableName)#
ORDER BY column_id





~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331186
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: cfquery return datatypes

2010-02-26 Thread Gerald Guido 

In CF8 there is cfdbinfo This function below will return a query object the
following fields (see below).
http://www.cfquickdocs.com/cf8/?getDoc=cfdbinfo#cfdbinfo

If you don't have CF 8 you will have to query the metadata based on the SQL
dialect for that particular database. You can look at the source code for
Brian Rinaldi's  Illudium cfcgenerator.  Last time I looked he had cfcs for
MySQL, MSSQL, Oracle and Postges.

http://code.google.com/p/cfcgenerator/


cffunction name=GetTableMetaData access=public output=false
returntype=query hint=I return column metadata
  cfargument name=MyDSN required=true type=string  /
cfargument name=MyTable required=true type=string  /

  cfdbinfo
 type=columns
datasource=#arguments.MyDSN#
 name=TableMetaData
table = #Arguments.MyTable#
   

   cfreturn TableMetaData /

/cffunction


COLUMN_NAME
Name of the column.

TYPE_NAME
SQL data type of the column.

IS_NULLABLE
 Whether the column allows nulls.

IS_PRIMARYKEY
 Whether the column is a primary key.

IS_FOREIGNKEY
 Whether the column is a foreign key.

REFERENCED_PRIMARYKEY
 If the column is a foreign key, the name of the table it refers to.

REFERENCED_PRIMARYKEY_TABLE
 If the column is a foreign key, the key name it refers to.

COLUMN_SIZE
 Size of the column

DECIMAL_DIGITS
 Number of digits to the right of the decimal point.

COLUMN_DEFAULT_VALUE
 Default value of column.

CHAR_OCTET_LENGTH
 Maximum length in bytes of a character or integer data type column.

ORDINAL_POSITION
 Ordinal position of the column.

REMARKS
 Remarks of the column.



On Fri, Feb 26, 2010 at 12:13 PM, Joshua Rowe
joshua.r...@varimereweb.comwrote:


 Hello.  Is there a way to return a datatype list from your query along with
 the column list?  Example:

 cfquery name=qryTest datasource=test
SELECT * FROM tblTest;
 /cfquery

 cfoutput
#qryTest.columnlist#br
#qryTest.datatypes#
 /cfoutput

 Thanks!

 

~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331193
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4