Dynamically building a query w/ variables and CF constructs to be saved...

Posted on 2003-03-27
Medium Priority
Last Modified: 2013-12-24
I just want to let you all know that I am picking back up on a thread of mine from a few weeks ago:   http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_20546693.html 

I thought the original solution would work, but now it is clear that I need to be able to save the SQL string in my DB and load it for different users as they log in. The string is the query to generate their custom-filtered list that is pulled from a different table in the same db.

Please refer to the thread above, and thanks in advance for any suggestions.

Question by:natG
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

crchambers earned 200 total points
ID: 8239390
First, I would make a new table for all the saved queries. The table would look something like this:

queryID Autonum
studentID Number (FK to studenttable used to link student to query)
querySrc Memo

Now define a new cfinsert:

<cfquery datasource="datasource" name="insertquery">
INSERT INTO tableQueries(student_ID,querySrc) VALUES(
'#student_ID#', strSQL)
(I may be missing some quote marks, but this is the general idea)

Every time you run the strSQL, make the insertquery fire as well, by running the insertquery directly after running the dynamic query you mentioned before... Is that what you're looking for?
LVL 35

Expert Comment

ID: 10940919
No comment has been added lately, so it's time to clean up this question.
I will leave the following recommendation in the Cleanup topic area:

Accept crchambers

Please leave any comments here within the next four days.

EE Cleanup Volunteer

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month11 days, 10 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question