Solved

Execute multiple dynamic SQL from CF

Posted on 2003-11-20
11
264 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
  • 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 150 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 100 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 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