-1

I have a form for a filter that has to generate a query depending on what the user selects and inputs into the form, however i need to save the generated query into the database for the scheduled tasks to call the query, how would i do this?

user3400893
  • 19
  • 2
  • 4
    What scheduled tasks? Why do they require the query to be saved "into the database"? What have you tried? What isn't working? – eggyal Mar 11 '14 at 14:52
  • 3
    *Why do they require the query to be saved "into the database"?* Ditto. Can you elaborate on why do you think that is necessary? – Leigh Mar 11 '14 at 15:23
  • 2
    You almost certainly don't need to save the SQL to the database. Describe more clearly what your real problem is - i.e. what is the specific task you're trying to solve that makes you think you need to do it this way? – Peter Boughton Mar 12 '14 at 13:38

2 Answers2

-2

Why not use the cfsavecontent tag to save your query in a variable or file system using cffile, and then call that file rather than database or write that variable value in database and make a call in scheduled task to fetch that column value of the database and execute it

Regual
  • 377
  • 1
  • 5
  • 20
  • Writing to disk is WAY more 'expensive' than storing in database. This is not a very good idea. – Scott Stroz Mar 12 '14 at 13:12
  • it was just a suggestion, never meant to tell him to do that, its upto him what he wants, and how he wants to do... – Regual Mar 12 '14 at 18:38
  • 1
    I know it was 'only a suggestion', but it was a bad one. And I wanted him to know it was a bad one. – Scott Stroz Mar 12 '14 at 21:04
  • suggestions are suggestions, not a good or bad, what if his requirements is to save in file system, then what will you do – Regual Mar 13 '14 at 04:15
  • idea is an idea, he likes it or not, not my concern, provided options given. I understand some ideas might not be good, but they work, anyways, why debate on this, you right, i am wrong – Regual Mar 13 '14 at 11:54
  • 1
    So, ideas may be bad, but suggestions cannot be? It was a bad idea. It was a bad suggestion. Just because you can do something does not mean that you should do it. – Scott Stroz Mar 13 '14 at 13:23
  • anyways, relax and take a chill – Regual Mar 15 '14 at 17:48
  • I will 'take a chill' when people stop making asinine suggestions. – Scott Stroz Mar 16 '14 at 00:01
-5

You first save the SQL in a variable, then have CFQuery execute what's inside the variable. Then use the same variable to store the SQL executed. 

<cfset mySQL = "SELECT * FROM MY_TABLE">
<cfquery datasource="myDS" name="qrySelect">
    #mySQL#
</cfquery>
<cfquery datasource="myDS" name="qryUpdate">
    UPDATE MY_LOGGING_TABLE
    SET SQL_COL = '#mySQL#'
</cfquery>

Important: Be aware that allowing freedom like this to end users can be dangerous. It opens major security risks, such as SQL Injection, which could destroy your database. You would not want to do this on a public-facing website.

However, there are legitimate reasons to use this code. For example, if you have properly secured the login process, and it's an Intranet site, you could use this for advanced users to create ad-hoc queries while logging what they do.

codemonkey
  • 203
  • 2
  • 12
  • 9
    Do not use dynamic sql unless you know what you are doing and are aware of the risks. Usually it is not necessary, and more importantly, it puts your db at risk for sql injection! – Leigh Mar 11 '14 at 15:33
  • 2
    In addition to what @Leigh said, why `evaulate()`? Why not just #mySQL# ? – Matt Busche Mar 11 '14 at 16:05
  • This was not a question about sql injection or risks. He asked how, and I provided a workable answer. – codemonkey Mar 11 '14 at 18:14
  • Removed the evaluate reference. You're right Matt, I over-complicated it. – codemonkey Mar 11 '14 at 18:21
  • 15
    *This was not a question about sql injection or risks* It is not about the question, but the recommendation. When recommending something that may put someone's database at risk, always tell them so. Do not assume they are aware of the risks, many people are not. Also, they have not fully explained their requirements, so it is entirely possible there are safer ways to achieve the goal. – Leigh Mar 11 '14 at 18:52
  • 1
    @Leigh I added the disclaimer for sql injection. Good? Please vote up ;-) – codemonkey Mar 11 '14 at 20:21