[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Execute multiple dynamic SQL from CF

Posted on 2003-11-20
11
Medium Priority
?
273 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 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

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…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

834 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