I have inherited a large legacy coldfusion app. There are hundreds of <cfquery>some sql here #variable#</cfquery> statements that need to be parameterized along the lines of: <cfquery> some sql here <cfqueryparam value="#variable#"/> </cfquery> How can I go about adding parameterization programatically? I have thought about writing so...
I would like to be able to do a query of a query to UNION an unknown number of recordset. However when doing a query-of-query dots or brackets are not allowed in record set names. For example this fails: <cfquery name="allRecs" dbtype="query"> SELECT * FROM recordset[1] UNION SELECT * FROM recordset[2] </cfquery> Using dy...
I'm trying to be a good CF web developer and use <cfqueryparam> around all FORM or URL elements that make it to my SQL queries. In this case, I'm trying to allow a user to control the ORDER BY clause dynamically. <cfquery datasource="MyDSN" name="qIncidents"> SELECT IncidentID, AnimalID, IntakeDate, DxDate, OutcomeDate FROM Incide...
I have the unfortunate task of cleaning up a bunch of old ColdFusion code. Queries are all over the place, I am working on moving them all to common CFCs for easier maintenance. I am running into a problem because cfquery is automatically converting the single quotes to double-single-quotes. How can I override that behavior? More spe...
I want to get a specific row in a ColdFusion Query object without looping over it. I'd like to do something like this: <cfquery name="QueryName" datasource="ds"> SELECT * FROM tablename </cfquery> <cfset x = QueryName[5]> But it's giving me an error saying that the query isn't indexable by "5". I know for a fact that there are m...
I'm writing a subsystem that tables might be renamed from project to project. Instead of asking the user of my subsystem to search & replace before using it, does this work? <cfquery name="local.foo" datasource="#dsn#"> SELECT col1, col2, col3 FROM #tableName# </cfquery> Without <cfqueryparam>, will it become non-cacheable? or an...
I'm using CF8 and SQL2000. I'm storing a bunch of HTML in a Text field in my SQL table. When I do a simple CFQUERY against that data, and CFDUMP it, it's truncated to 64000 characters. I've confirmed that my data is complete in the SQL table, by selecting the tail end of the data using SELECT Substring, and confirmed the length using...
How to print all the result without using Results.columnname in ColdFusion for ex:- I have <cfquery name="getProductId"> select productId from product </cfquery> In Product Table i have 2 columns with product_name and Product_id. How can I print them without using getProductId.product_name getProduc...
If one of the columns in the returned coldfusion query result set has a NULL, how do we check if the value of this column being NULL? Should we just say <cfif queryname.columnname[i] EQ ''> OR <cfif queryname.columnname[i] eq 'NULL'> ? ...
Hey guys well I'm working on this system and it giving me sooo much trouble. I just want to quit at this point. Long story short I made a few changes to how a mySQL table works, I went from storing a comma separated list for a few different fields to storing the same data each in its own field. I then rewrote the code to work with the ne...
I had to change the SQL stored procedure to ORacle stored procddure.I am able to successfully execute my modified stored procedure in oracle. But unable to obtain the query result in CF for the Oracle stored-Procedure.I have used <cfquery>. Any suggestions or tips to for using an Oracle stored proc/CF-8? ...
Is it possible to Execute 2 insert or Update Statements using cfquery? If yes how? if no, what is the best way to execute multiple queries in Coldfusion, by opening only one Connection to DB. I think every time we call cfquery we are opening new connection DB ...
I am wondering if there are java methods/objects that would be alternatives to cfquery, that both allow variable sanitation, and better caching methods. I know that you can use information schema to get data types and char lengths, and then use that to validate data type & length of variables in a query. But with everyone converting to...
Hello, I am trying to fire Update Query using cfquery like below <cfquery name = "UpdateRecord" dataSource = #DATASOURCE# username = #DBUSER# password = #DBPASSWORD# result="updateResult" > update table1 set field1=( select field1 from table2 where field3='Some Val...
At first I was thinking I was running into an issue with cfqueryparam and mysql. However when I change substitute them with static values I get the same error. This is stumping me, I'm too used to Microsoft SQL Server I guess. Any help would be greatly appreciated. Here's the query, this works perfectly in mySql query browser, but f...
When I retrived a Date field in TOAD it displayed like is '1/18/2038 9:14:07 PM', But when I rertrived in Coldfusion using cfquery and displayed using , then I got the date on screen like '2038-01-18 21:14:07.0'. Does anyone have idea why it displayed in different format? Is there anyway we can make it display like TOAD format? I am...
Hi, I am unable to retrieve any data from my cfquery. Same query when i run in sql developer i get the result. Any reason why ? Hi all, thanks for the responses. Sorry, it was my fault. It was a data issue. I was retrieving uncommited data from CF. ...
I have the following ColdFusion 9 code: <cfloop from="1" to="#arrayLen(tagArray)#" index="i"> <cfquery name="qryGetSPFAQs" datasource="#application.datasource#"> EXEC searchFAQ '#tagArray[i]#' </cfquery> </cfloop> The EXEC executes a stored procedure on the database server, which returns rows of data, depending on what...
The typical controls against SQL injection flaws are to use bind variables (cfqueryparam tag), validation of string data and to turn to stored procedures for the actual SQL layer. This is all fine and I agree, however what if the site is a legacy one and it features a lot of dynamic queries. Then, rewriting all the queries is a herculean...
When you use the cachedwithin attribute in a cfquery how does it store the query in memory. Does it store it by only the name you assign to the query? For example, if on my index page I cache a query for an hour and name it getPeople will a query with the same name on a different page (or the same page for that matter) use the cached res...