CODEWITHSUNDEEP
sqlcoldfusion
Lawrence Barsanti
Lawrence Barsanti

Reputation: 33342

Is it possible to rebind sql paramters using the result from a cfquery?

When I run the following code:

<cfquery name="someQuery" result="queryResult" datasource="wetakepictures">
  SELECT id 
  FROM events
  WHERE category_id = <cfqueryparam value="1" cfsqltype="cf_sql_integer">
     OR title like <cfqueryparam value="%test%" cfsqltype="cf_sql_varchar">
</cfquery>

<cfoutput>
  #queryResult.sql# <br />
  #ArrayToList(queryResult.sqlparameters)#
</cfoutput>

It outputs:

SELECT id FROM events WHERE category_id = ? OR title like ?
1,%test%

I need the actual string "SELECT id FROM events WHERE category_id = 1 OR title like '%test%'".

Is there a way to rebind the parameters to the sql?

---- edit ----

The reason for doing this is to eliminate duplicate SQL when paginating results. I would like to do something like this: 

<cftransaction>
  <cfquery name='getCount' result='queryResult'>
     SELECT count(*)
     ... conditions that are guarded by <cfif> ...
  </cfquery>

  <cfquery name='getLimitedRecords'>
     #replace(queryResult.sql, 'count(*)', 'id')#
     LIMIT ... based on pagination ...
  </cfquery>
</cftransaction>

Note: I've looked at this question and decided to use two queries with MySQL.

Upvotes: 4

Views: 177

Answers (1)

Antony
Antony

Reputation: 3781

Two approaches, depending on what you are trying to do:

Ben Nadel:Merging ColdFusion SQL Debugging And Query Params With Javascript - useful if all you want is to copy and paste

A better debugging template - useful if you want to reverse engineer the code to reconstruct your query in code and do some logging, etc.

Upvotes: 1

Related Questions