tracker issue : CF-3360511

select a category, or use search below
(searches all categories and all time range)

CFDBINFO doesn't know to escape reserved words

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/ThirdParty

Reporter/Name(from Bugbase): Adam Cameron / Adam Cameron (Adam Cameron)

Created: 11/06/2012

Components: Database

Versions: 10.0

Failure Type:

Found In Build/Fixed In Build: Final /

Priority/Frequency: Minor / Some users will encounter

Locale/System: English / Windows 7 64-bit

Vote Count: 0

Problem Description:
When querying a DB with a table "User" in a Derby DB, <cfdbinfo> errors when retrieving its columns

Steps to Reproduce:
		id int
<cfdbinfo type="Tables" name="tables">
<cfdump var="#tables#">
<cfloop query="tables">
	<cfif table_type EQ "TABLE">
		<cfdbinfo type="Columns" table="#table_name#" name="columns">
		<cfdump var="#columns#" label="#table_name#">

Actual Result:
Error Executing Database Query.
Table User does not exist.

Expected Result:
It to fetch the columns from my User table.

Any Workarounds:
Don't use reserved words for table names.  The thing is this should still be a legit operation, provided CF was savvy enough to escape the values it's passing to the DB.  if it passed '"User"' instead of just 'User', this would work.  It should always escape these values, as it cannot predict what they'll be.  It def should not return "table does not exist", because the table DOES exist.

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3360511

Deployment Phase:	Release Candidate

External Customer Info:
External Company:  
External Customer Name: Adam Cameron.
External Customer Email:  
External Test Config: My Hardware and Environment details:



Looks like a derby bug. Same code works in SQLServer even though 'user' is a reserved word for all databases. A strange and easy workaround is to use 'USER' instead of 'user'
Comment by Himavanth R.
17283 | January 03, 2014 06:18:11 AM GMT
Weird! I thought Derby was case-sensitive, so "USER" would not match "User"? Cheers for feedback though! -- Adam
Comment by External U.
17284 | January 03, 2014 06:37:27 AM GMT
@Adam: It is case-sensitive. You will have to use uppercase while creating the table too but lowercase just doesn't work. Updated my previous note to make it clear.
Comment by Himavanth R.
17285 | January 03, 2014 06:43:12 AM GMT
Aha, I gotcha. Cheers! -- Adam
Comment by External U.
17286 | January 03, 2014 07:01:22 AM GMT