4

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.

Community
  • 1
  • 1
Lawrence Barsanti
  • 27,683
  • 10
  • 43
  • 64
  • Why exactly do you need to do this? It sounds like you want to do something that isn't possible, but if you have a goal in mind, we might be able to help you reach that goal without this step... – Adam Tuttle May 03 '10 at 13:40
  • I think I understand why you would want to do this because I thought of this once. Actually is not good approach for pagination since there are already other better solutions. If you always need count(id), than make it cached for couple of minutes depending on how often those records are changed. Second query can also be cached for cases when user goes back and forth. IMHO limit+ofset+if squence of other params should to just fine in all possible scenarios. If not you can put short desc. of what you're trying to achieve. – zarko.susnjar May 03 '10 at 18:22
  • I also don't understand what's cftransaction for. – zarko.susnjar May 03 '10 at 18:25
  • i remembered now why I wanted to do this once. To save in history last search. But couple of seconds later I realized that I need only params, not whole query! Now you MUST tell us what are you trying to do because it's so interesting :) – zarko.susnjar May 03 '10 at 18:35

1 Answers1

1

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.

Antony
  • 3,753
  • 1
  • 23
  • 32
  • Thanks for the links but they wont do the trick. It needs to be done server side so Nadel's approach is out. The better debugging template just uses string replace to rebind the parameters so the rebuilt query is susceptible to sql injection. – Lawrence Barsanti May 03 '10 at 13:31
  • how is it susceptible - if you are doing this server side and persisting the query in the session scope it never goes near a user. anyway, i'm not sure your idea is a great appraoch to pagination and it feels like premature optimisation – Antony May 03 '10 at 21:24