• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 139
  • Last Modified:

problem with reserved word using driver Access with unicode

using the query below I am getting the following error
Error Executing Database Query
on this line: and language= '#request.language#'

I thnk it is due to a reserved word but am not sure how to fix it (really don't want to change the word everywhere).
<cfquery name="CONTAINER#attributes.container#" datasource="#application.DS#" dbtype="ODBC" username="#application.DBuser#" password="#application.DBpass#" cachedwithin="#timespan#">
select *
from contentItems
where container = #attributes.container#
and language= '#request.language#'
and active = 1
order by ord ASC
</cfquery>

Open in new window

0
Shawn
Asked:
Shawn
  • 7
  • 5
1 Solution
 
_agx_Commented:
If it is a reserved word problem, enclose the problematic column/table name in [ ].   I don't know which one it might be, so try them all and remove them one by one to find the one causing the problem.

SELECT *
FROM  [contentItems]
WHERE [container] = #attributes.container#
and [language] = '#request.language#'
and [active] = 1
order by [ord] ASC

If that does not work, what is the data type of #attributes.container# ?  
0
 
_agx_Commented:
> If that does not work, what is the data type of #attributes.container# ?  

Unless it is numeric, it should be enclosed in quotes

       WHERE [container] = '#attributes.container#'

Or better yet, use cfqueryparam:

       WHERE [container] = <cfqueryparam value="#attributes.container#" cfsqltype="cf_sql_varchar">
0
 
ShawnAuthor Commented:
tried that and it does get rid of the error but turns up 0 records. wierd
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
ShawnAuthor Commented:
[container] = #attributes.container# <-- numeric
[language] = '#request.language#' <--text
and [active] = 1
0
 
_agx_Commented:
> tried that and it does get rid of the error but turns up 0 records. wierd

Okay, so it was a reserved word problem.  If it returns 0 records that just means nothing matches the criteria you're using.  So either the criteria is wrong ... or the underlying data is.

0
 
_agx_Commented:
... or maybe you are using the operators.  ie AND instead of OR

Output the values of the variables, and see if they look correct:

<cfoutput>
container = #attributes.container#
 language = '#request.language#'
<cfoutput>

If they do, check your table.  Does it really contain records where _all_ of those conditions match?  ie
container value = #attributes.container# _AND_
language value = #request.language# _AND_
active = 1
0
 
ShawnAuthor Commented:
just got it to work...the active field is a true/false field. For some reason with the standard driver a value of 1 works but with the unicode driver it doesn't. I changed it to True and it works! thanks


while we're at it could you help me with adding cfqueryparam properly. I'm not very good at ityet...just discovered it a while back :-( If you'd prefer me to open another question let me know.

SELECT *
FROM  [contentItems]
WHERE [container] = <cfqueryPARAM value = "#attributes.container#" CFSQLType = "CF_SQL_INTEGER">
and [language] = <cfqueryparam value="#request.language#" cfsqltype="cf_sql_varchar">
and [active] = true <--not sure what goes here
order by [ord] ASC



SELECT *
FROM  [contentItems]
WHERE [container] = #attributes.container#
and [language] = '#request.language#'
and [active] = true
order by [ord] ASC

Open in new window

0
 
_agx_Commented:
No, you just about have it.  The types aren't very documented for access but try "bit" for the true/false column

SELECT *
FROM  [contentItems]
WHERE [container] = <cfqueryPARAM value = "#attributes.container#" CFSQLType = "CF_SQL_INTEGER">
AND [language] = <cfqueryparam value="#request.language#" cfsqltype="cf_sql_varchar">
AND [active] = <cfqueryparam value="1" cfsqltype="cf_sql_bit">
ORDER [ord] ASC

Also,  did you figure out which of the columns is a reserved word?  I am just curious ;-)
0
 
_agx_Commented:
If that doesn't work, try using "true" again:

AND [active] = <cfqueryparam value="true" cfsqltype="cf_sql_bit">
0
 
ShawnAuthor Commented:
both 1 and true work in this case
now I guess I should go through the whole site and add cfqueryparam

thanks again :-)

ps it seems container was the reserved word.
0
 
_agx_Commented:
Ah, okay.  My guess was going to be that or "language". But thanks for satisfying my curiosity :)
0
 
ShawnAuthor Commented:
yeah, I'm surprised language hasn't caused trouble as well. I mostly use Lang now to be safe.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now