[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Execute multiple dynamic SQL from CF

Posted on 2003-11-20
11
Medium Priority
?
271 Views
Last Modified: 2013-12-24
Hi lads, I have a script that works in ASP, and want to convert it to CF (due to certain issues)

basically the ASP script does the follows: -

1)Selects a number of strings (which contain SQL SELECT statements) from a database.

2)Creates a temporary Table.

3)Uses the recordset of SQL selected in step 1. to select data into the temp table.



0
Comment
Question by:MoOTottle
[X]
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
  • 4
  • 4
  • 3
11 Comments
 
LVL 6

Expert Comment

by:PE_CF_DEV
ID: 9789454
<cfquery name="name" datasource="your datasource">
SET NOCOUNT OFF
your SQL you are using
SET NOCOUNT ON
</cfquery>

Is one option  however by the sounds of it you would be better off creating a stored procedure in SQL and then executing it with coldfusion using <cfstoredproc>.

<cfquery> Info:
http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b19.htm

<cfstoredproc> info:
http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-pc9.htm#wp2607555
0
 
LVL 9

Accepted Solution

by:
shooksm earned 450 total points
ID: 9789562
I would agree with using a stored procedure but here is how you might do it if a stored procedure is not an option.

<cfquery datasource="#strDatasource#" name="qrySelectStatements">
      SELECT SelectField
            FROM TableWithSelectStatements
</cfquery>
<cfquery datasource="#strDatasource#" name="qryTempTable">
      SELECT *
            FROM TempTable
</cfquery>
<cfset intUniqueQueryID = 1>
<cfloop query="qrySelectStatements">
      <cfquery dbtype="query" name="qryTemp#intUniqueQueryID#">
            #qrySelectStatements.SelectField#
                  FROM qryTempTable
      </cfquery>
      <cfset intUniqueQueryID = IncrementValue(intUniqueQueryID)>
</cfloop>
0
 

Author Comment

by:MoOTottle
ID: 9789618
<cfquery name="get_usergroups" datasource="#dsn#" dbtype="ODBC" username="#DSN_UN#" password="#DSN_PW#">
        #strSQL#
</cfquery>

<cfset iCounter = 0>

<cfSET tmpTableName="tmp_"&skeywordFilter&dateformat(now(),'yyyymmdd')&TimeFormat(now(), "hhmmss")>

<cfset tmpTableCounter=1>
<cfset tmpQueryName="sub_usergroups"&tmpTableCounter>
<cfquery name="create_temptable" datasource="#dsn#" dbtype="ODBC" username="#DSN_UN#" password="#DSN_PW#">

            someSQL
</cfquery>

<cfoutput>
      
<cfloop query="get_usergroups">

      <cfquery name="#Evaluate("tmpQueryName")#" datasource="#dsn#" dbtype="ODBC" username="#DSN_UN#" password="#DSN_PW#">
            #group_sql#
      </cfquery>


      <cfloop query="#Evaluate("tmpQueryName")#">

            <cfquery datasource="#dsn#" dbtype="ODBC" username="#DSN_UN#" password="#DSN_PW#">

            INSERT INTO #tmpTableName# (fields)
            VALUES;

            </cfquery>
      </cfloop>
                        
                        

                        
<cfset tmpTableCounter=tmpTableCounter+1>
<cfset tmpQueryName="sub_usergroups"&tmpTableCounter>
</cfloop>
</cfoutput>
0
Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

 

Author Comment

by:MoOTottle
ID: 9789625
I got there in the end myself thanks lads.

but shooksm, your way looks much cleaner, is that suitable for large quantities of records?
0
 
LVL 9

Expert Comment

by:shooksm
ID: 9789651
Not sure.  That was just something I threw together quickly.  For moving data around, I much prefer more powerful tools likes DTS or BCP and leave Cold Fusion to just displaying result sets and not data manipulation.
0
 
LVL 6

Assisted Solution

by:PE_CF_DEV
PE_CF_DEV earned 300 total points
ID: 9789694
Thats a lot of connections to the database...That gets to be a lot of overhead

you might try something like doing a join with the get_usergroups  and the following query:
     <cfquery name="#Evaluate("tmpQueryName")#" datasource="#dsn#" dbtype="ODBC" username="#DSN_UN#" password="#DSN_PW#">
          #group_sql#
     </cfquery>

Then using <cfoutput group="columnname"> you can group them together for each user.
so it would be
<cfoutput group="columnname">
 anything you want to do for each user
<cfoutput>
anything you want to do for stuff that pertains to te temquery
</cfoutput>
</cfoutput>

Connecting to a databse is expensive in terms of system resources and it makes you more vulnerable to network problems. Try to limit it as much as possible :)
0
 
LVL 6

Expert Comment

by:PE_CF_DEV
ID: 9789719
If all you want to do is move stuff from one table to another try:

INSERT INTO TABLE (Columns)
Select COLUMNS
from table_to_move_stuff_from
Where and limiters
0
 
LVL 9

Expert Comment

by:shooksm
ID: 9789854
I aggree with PE_CF_DEV as the solution with the INSERT INTO with SELECT will hands down outperform a cold fusion solution using looping.
0
 

Author Comment

by:MoOTottle
ID: 9823335
@PE_CF_DEV, so loop through each of my tempory SELECT statments but instead of using my current method force a "INSERT INTO TABLE (Columns)" before the custom SQL?

Offtopic I am real busy this week guys, so wont get chance to allocate points til the end of the week when i can test the code/suggestions out.
0
 
LVL 6

Expert Comment

by:PE_CF_DEV
ID: 9824718
Well I am not sure if it will work in this senerio but. What I was saying is if the end result is taking information from one or many tables and insterting it into a new or different table the best way of doing that is like this (things in all caps are thing you will want to replace):

Insert into ENDTABLE (COLUMN1, COLUMN2, COLUMN3)
/*now here instead of inserting 1 record like this Values (value1,value2,value3) we use a select statement*/
Select INFO1, INFO2, INFO3
from BASETABLE1 IINER JOIN BASETABLE1 ON BASETABLE1.PRIMARYKEY = BASETABLE2.FORGIENKEY
WHERE ANYTHING YOU NEED TO LIMIT

HTH
0
 

Author Comment

by:MoOTottle
ID: 10331829
oh i see what your saying PE_CF_DEV, but the nature of my records & tabel structure means the SQL is impossible to draft in this instance.

I'll split the points between you both though.

Sorry its been such a long time, i must admit i forgot about this question :(
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

649 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