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
Solved

Execute multiple dynamic SQL from CF

Posted on 2003-11-20
11
265 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

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…
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

791 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