Solved

RemoveChars and update database

Posted on 2006-06-23
13
275 Views
Last Modified: 2013-12-24
I'm updating a form_action.cfm page to update a sql table...  the numbers after RANKING_PL could be 0-400+ (see paste #1 below).  I need help with the routine that will remove RANKING_PL leaving just the number = number (e.g. RANKING_PL65 = 0 would be come 65 = 0).  65 is the unique id for the sql table and the 0 is the value submitted from the form.  This value could be 0-4 only.  The second part which I assume would be just like the first one is YEARUSED65=0... after the manipulation would end up being 65=0.  This value would be 0 or 2001-2007.

<!--- paste ##1 --->
RANKING_PL65 = 0
YEARUSED65 = 0
RANKING_PL62 = 3
YEARUSED62 = 2001
RANKING_PL63 = 4
YEARUSED63 = 2005
RANKING_PL64 = 2
YEARUSED64 = 2000
<!--- end of paste ##1 --->

I'd prefer to hit the database only once.  

I'm not very good with loops but I'm assuming the values could be put into a comma delimited list and assigned a variable name and that variable name woudl be used in teh SQL query as in...

In summary, I need the above list (which is just a snippet of data <!--- paste ##1 --->) converted to an SQL query like this with minimal database hits.  The list could contain any number from 1 - 400 values being submitted.  

UPDATE    IRP_RANKING
SET              RANKING_PL = 3, RANKING_YEAR_USED = 2003
WHERE     (fk_emp_pool_id = 35) AND (fk_skill_pool_id = 65)

fk_emp_pool_id = 35 <--- this is pulled from a session variable
fk_skill_pool_id = 65 <-- was extracted from the RANKING_PL65
RANKING_PL = 0 <-- derived from above RANKING_PL65 = 0
YEARUSED65 = 0 <-- derived from above


0
Comment
Question by:dl8453
  • 7
  • 6
13 Comments
 
LVL 15

Expert Comment

by:DanielSKim
ID: 16973051
is this what you are looking for?

<cfquery name="qUpdate" datasource="datasourceName">
      <cfloop collection="#FORM#" item="e">
            <cfif Left(e, 10) EQ "RANKING_PL">
                  <cfset fk_skill_pool_id = Right(e, Len(e) - 10) />
                  <cfset ranking_pl = FORM[e] />
                  <cfset yearused = FORM['YEARUSED#fk_skill_pool_id#'] />
                  <cfoutput>
                  UPDATE IRP_RANKING
                  SET RANKING_PL = #ranking_pl#, RANKING_YEAR_USED = #yearused#
                  WHERE fk_emp_pool_id = #SESSION.fk_emp_pool_id# AND fk_skill_pool_id = #fk_skill_pool_id#
                  </cfoutput>
            </cfif>
      </cfloop>
</cfquery>

since all the statements are in a cfquery tag, it is actually only one call from cf to the db, although there are multiple statements that are executed on the db.

0
 
LVL 15

Expert Comment

by:DanielSKim
ID: 16973059
note - there is no error, but you don't need the <cfoutput> and </cfoutput> between the UPDATE statements.
0
 
LVL 1

Author Comment

by:dl8453
ID: 16976345
Daniel, you are amazing!  I've never used the cfloop collection tag before.  I'll have to read up on that ...  

This works perfectly for updating if the values already exists in the database.  After looking at the database results more closely, some assessments (rankings) have not been "ranked"; for example if an employee was just added, he/she will not have any rankings for the skills in the system and they will be unable to make any updates.  

What is your recommendation for...  

1)  Updating values that are NOT 0 (the web page default values are 0).  Can I add a select statement (eg Select * from irp_ranking where form.ranking_pl <> 0 and form.yearused <> 0) and update the other form values submitted?  
OR
2)  Updating values that exists and inserting ones that do not exists?  

Option 2 sounds more logical, but not being familiar with the cfloop collection, I'm not even sure if either option is plausible.
0
 
LVL 1

Author Comment

by:dl8453
ID: 16976815
Okay, I've made some head way here... but you I can't have nested <cfquery> statements.  I put in the cfoutput in the code so it would write out the query to the web page for me.  From there, I can copy the output and paste it into SQL and run the query and it does so perfectly.  How do I get around the nested <cfquery> statements?  Below is my code...


<!--- <cfquery name="qUpdate" datasource="datasource"> --->
<cfoutput>
     <cfloop collection="#FORM#" item="e">
          <cfif Left(e, 10) EQ "RANKING_PL">
               <cfset fk_skill_pool_id = Right(e, Len(e) - 10) />
               <cfset ranking_pl = FORM[e] />
               <cfset yearused = FORM['YEARUSED#fk_skill_pool_id#'] />
                  <cfquery name="qGetEmpRank" datasource="#datasource#">
                  select *
                  from IRP_Ranking
                  where fk_emp_pool_id = #form.EMP_POOL_ID# AND fk_skill_pool_id = #fk_skill_pool_id#
                  </cfquery>
                  <cfif qGetEmpRank.recordcount gte 1>
                     UPDATE IRP_RANKING
               SET RANKING_PL = #ranking_pl#, RANKING_YEAR_USED = #yearused#, rank_status = 1
               WHERE fk_emp_pool_id = #form.EMP_POOL_ID# AND fk_skill_pool_id = #fk_skill_pool_id#
                  <cfelse>
                  INSERT INTO IRP_RANKING
                  (RANKING_PL, RANKING_YEAR_USED, fk_emp_pool_id, fk_skill_pool_id, rank_status)
                  VALUES     (#ranking_pl#, #yearused#, #form.EMP_POOL_ID#, #fk_skill_pool_id#, 1)
                  </cfif>
          </cfif>
     </cfloop>
</cfoutput>
<!--- </cfquery> --->
0
 
LVL 1

Author Comment

by:dl8453
ID: 16977262
Daniel, I believe I've got it.  I surrounded my <cfif></cfif> with the query code like this eliminating the nested <cfquery>.  Do you see any flaws with this design?  

<cfloop collection="#FORM#" item="e">
          <cfif Left(e, 10) EQ "RANKING_PL">
               <cfset fk_skill_pool_id = Right(e, Len(e) - 10) />
               <cfset ranking_pl = FORM[e] />
               <cfset yearused = FORM['YEARUSED#fk_skill_pool_id#'] />
                  <cfquery name="qGetEmpRank" datasource="#datasource#">
                  select *
                  from IRP_Ranking
                  where fk_emp_pool_id = #getEmpID.EMP_POOL_ID# AND fk_skill_pool_id = #fk_skill_pool_id#
                  </cfquery>
                  <cfif qGetEmpRank.recordcount gte 1>
                        <cfquery name="uEmp" datasource="#datasource#">
                           UPDATE IRP_RANKING
                           SET RANKING_PL = #ranking_pl#, RANKING_YEAR_USED = #yearused#, rank_status = 1
                           WHERE fk_emp_pool_id = #getEmpID.EMP_POOL_ID# AND fk_skill_pool_id = #fk_skill_pool_id#
                        </cfquery>
                  </cfif>                        
                  <cfif qGetEmpRank.recordcount lt 1 and ranking_pl gte 1 and yearused gt 0>
                        <cfquery name="dEmp" datasource="#datasource#">
                              INSERT INTO IRP_RANKING
                              (RANKING_PL, RANKING_YEAR_USED, fk_emp_pool_id, fk_skill_pool_id, rank_status)
                              VALUES     (#ranking_pl#, #yearused#, #getEmpID.EMP_POOL_ID#, #fk_skill_pool_id#, 1)
                        </cfquery>
                  </cfif>
          </cfif>
     </cfloop>
0
 
LVL 15

Expert Comment

by:DanielSKim
ID: 16977274
what database are you using?
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:dl8453
ID: 16977366
sql and it is behind a corporate firewall.
0
 
LVL 1

Author Comment

by:dl8453
ID: 16977369
Microsoft SQL 2K behind a corporate firewall is what I was trying to say.
0
 
LVL 15

Accepted Solution

by:
DanielSKim earned 250 total points
ID: 16979408
the following solution makes use of sql server's xml capabilities, uses the minimum number of database executions necessary, and wraps the entire database call with coldfusion's transaction tags to handle any database errors.

it loops through the form variables to create an xml string that is sent to sql server to create a parsed xml document that is used to insert the results into a table variable. records in the table variable that exists in IRP_RANKING are updated, while records that don't are inserted into IRP_RANKING.

the entire database transaction is wrapped with cf's transaction tags, that allow the database call to be an all or nothing affair, meaning either the entire database transaction is committed, or the entire thing is rolled back.

you can take a look at SQL Server's Books On Line and CF's documentation for more detailed info, but let me know if anything is not clear.


<cfset xmlRanking = '' />
<cfloop collection="#FORM#" item="e">
      <cfif Left(e, 10) EQ "RANKING_PL">
            <cfset fk_skill_pool_id = Right(e, Len(e) - 10) />
            <cfset ranking_pl = FORM[e] />
            <cfset yearused = FORM['YEARUSED#fk_skill_pool_id#'] />
            <cfset xmlRanking = '#xmlRanking#<ranking fk_skill_pool_id="#fk_skill_pool_id#" ranking_pl="#ranking_pl#" yearused="#yearused#" />' />
      </cfif>
</cfloop>
<cfset xmlRanking = "<rankings>#xmlRanking#</rankings>" />


<!--- uncomment the following cfdump to see the xml that is created while testing --->
<!--- <cfdump var="#xmlRanking#" /> --->


<cftransaction>
      <cfset transactionAction = "commit" />
      <cftry>
            <cfquery name="qUpdate" datasource="#datasourceName#">
            
            DECLARE @xmlHandleId INT
            DECLARE @xmlRanking VARCHAR(8000)
            DECLARE @t_xml TABLE (fk_skill_pool_id int, ranking_pl int, yearused int)
            
            SET @xmlRanking = '#xmlRanking#'
            
            EXEC sp_xml_preparedocument @xmlHandleId OUTPUT, @xmlRanking
            
            INSERT INTO @t_xml (fk_skill_pool_id, ranking_pl, yearused)
            SELECT fk_skill_pool_id, ranking_pl, yearused
            FROM OPENXML(@xmlHandleId, '/rankings/ranking')
            WITH (fk_skill_pool_id int, ranking_pl int, yearused int)
            
            EXEC sp_xml_removedocument @xmlHandleId
            
            <!--- updates the existing rows --->
            UPDATE t2
            SET t2.RANKING_PL = t1.ranking_pl, t2.RANKING_YEAR_USED = t1.yearused
            FROM
                  @t_xml t1 INNER JOIN IRP_RANKING t2
                        ON t1.fk_skill_pool_id = t2.fk_skill_pool_id AND t2.fk_emp_pool_id = #SESSION.fk_emp_pool_id#

            <!--- inserts any new rows --->
            INSERT INTO IRP_RANKING (RANKING_PL, RANKING_YEAR_USED, fk_emp_pool_id, fk_skill_pool_id, rank_status)
            SELECT t1.ranking_pl, t1.yearused, #SESSION.fk_emp_pool_id# fk_emp_pool_id, t1.fk_skill_pool_id, 1
            FROM
                  @t_xml t1 LEFT JOIN
                        (
                        SELECT *
                        FROM IRP_RANKING
                        WHERE fk_emp_pool_id = #SESSION.fk_emp_pool_id#
                        )
                        t2
                        ON t1.fk_skill_pool_id = t2.fk_skill_pool_id
            WHERE
                  t2.fk_skill_pool_id IS NULL
            
            </cfquery>
            <cfcatch type="database">
                  <cfset transactionAction = "rollback" />
                  <!--- uncomment the below cfdump to view errors --->
                  <!--- <cfdump var="#CFCATCH#" /> --->
            </cfcatch>
      </cftry>
      <cftransaction action="#transactionAction#" />
</cftransaction>

<cfif transactionAction EQ 'commit'>
      <!--- code if db transaction successful --->
<cfelseif transactionAction EQ 'rollback'>
      <!--- code if db transaction fails --->
</cfif>
0
 
LVL 1

Author Comment

by:dl8453
ID: 16980301
I'm speachless.  I'll give it a shot and let you know.  I am entering uncharted territory with cftransaction.  One quick question... the last cfif statements... is this where I would put "Selections have been saved/updated" or "An error has occurred, please go back and try again." type of message?  

One thing that worries me about your code is that I might not be able to edit it later if a change is needed.  :)  Maybe after inspecting it closer, it will make more sense.  :)  Give me a few days to look this over and I'll let you know.  :)

I can't thank you enough...
0
 
LVL 15

Expert Comment

by:DanielSKim
ID: 16981409
a little background on some of the code ...

the cftransaction tag allows you to indicate a block of database statements that should all be done, or not be done (or rolled back, if some of the statements have already been executed) if there is an error. the cfquery tag is enclosed within a cftry tag, meaning, if something goes wrong, go to the code in the cfcatch block. you'll notice that I set the variable "transactionAction" to "commit", and only if there is an error and it goes to the cfcatch block, will it be set to "rollback". The <cftransaction> tag is funny in that it used for the entire block without any attributes, but it also used within a block of <cftransaction> tags to indicate a database action:

<cftransaction>
   <!--- database transaction --->
   <cftransaction action="commitOrRollback" />
</cftransaction>

"commit" in the inner <cftransaction> tag indicates that all the database statements executed should be left as is; "rollback" indicates that any database statements that were executed should be reversed so that the data in the database is left as it was prior to the transaction being executed. in essence, it allows your database statements be an all or nothing affair.

the section after the <cftransaction> block is indeed where you can determine if the database statements were executed succesfully, and if not, to indicate that there has been a database error of some kind.

in regards to the actual database statements, it may be helpful for you to have the database return a recordset at different points in the code to see what is going on. please let me know what questions you have about any part of the code. this really is the most efficient way to insert and update the form values into the database, and allows you to handle any errors that may occur. if you haven't already, i'd definitely turn the cf debugging options on in your development of this page, as there is some info that is generated that helps clarify exactly what database transactions are going on.


EXAMPLE 1
-----------------------------------------------------------------------------------------------

<!--- put the form loop code that creates the xmlRanking string here --->

<!--- dump to see the xml string created --->
<cfdump var="#xmlRanking#" />

<cfquery name="qUpdate" datasource="#datasourceName#">
         
DECLARE @xmlHandleId INT
DECLARE @xmlRanking VARCHAR(8000)
DECLARE @t_xml TABLE (fk_skill_pool_id int, ranking_pl int, yearused int)
         
SET @xmlRanking = '#xmlRanking#'
         
EXEC sp_xml_preparedocument @xmlHandleId OUTPUT, @xmlRanking

SELECT fk_skill_pool_id, ranking_pl, yearused
FROM OPENXML(@xmlHandleId, '/rankings/ranking')
WITH (fk_skill_pool_id int, ranking_pl int, yearused int)
         
EXEC sp_xml_removedocument @xmlHandleId
         
</cfquery>
<!--- to see the recordset from the select openxml statement --->
<cfdump var="#qUpdate#" />


EXAMPLE 2
----------------------------------------------------------------------------------------

<!--- put the form loop code that creates the xmlRanking string here --->

<!--- dump to see the xml string created --->
<cfdump var="#xmlRanking#" />

<cfquery name="qUpdate" datasource="#datasourceName#">

DECLARE @xmlHandleId INT
DECLARE @xmlRanking VARCHAR(8000)
DECLARE @t_xml TABLE (fk_skill_pool_id int, ranking_pl int, yearused int)

SET @xmlRanking = '#xmlRanking#'

EXEC sp_xml_preparedocument @xmlHandleId OUTPUT, @xmlRanking

INSERT INTO @t_xml (fk_skill_pool_id, ranking_pl, yearused)
SELECT fk_skill_pool_id, ranking_pl, yearused
FROM OPENXML(@xmlHandleId, '/rankings/ranking')
WITH (fk_skill_pool_id int, ranking_pl int, yearused int)

EXEC sp_xml_removedocument @xmlHandleId

SELECT t1.*
FROM
      @t_xml t1 INNER JOIN IRP_RANKING t2
            ON t1.fk_skill_pool_id = t2.fk_skill_pool_id AND t2.fk_emp_pool_id = #SESSION.fk_emp_pool_id#

</cfquery>

<!--- to see what records already exist in the database --->
<cfdump var="#qUpdate#" />



EXAMPLE 3
----------------------------------------------------------------------------------------

<!--- put the form loop code that creates the xmlRanking string here --->

<!--- dump to see the xml string created --->
<cfdump var="#xmlRanking#" />

<cfquery name="qUpdate" datasource="#datasourceName#">

DECLARE @xmlHandleId INT
DECLARE @xmlRanking VARCHAR(8000)
DECLARE @t_xml TABLE (fk_skill_pool_id int, ranking_pl int, yearused int)

SET @xmlRanking = '#xmlRanking#'

EXEC sp_xml_preparedocument @xmlHandleId OUTPUT, @xmlRanking

INSERT INTO @t_xml (fk_skill_pool_id, ranking_pl, yearused)
SELECT fk_skill_pool_id, ranking_pl, yearused
FROM OPENXML(@xmlHandleId, '/rankings/ranking')
WITH (fk_skill_pool_id int, ranking_pl int, yearused int)

EXEC sp_xml_removedocument @xmlHandleId

SELECT t1.ranking_pl, t1.yearused, #SESSION.fk_emp_pool_id# fk_emp_pool_id, t1.fk_skill_pool_id, 1
FROM
      @t_xml t1 LEFT JOIN
            (
            SELECT *
            FROM IRP_RANKING
            WHERE fk_emp_pool_id = #SESSION.fk_emp_pool_id#
            )
            t2
            ON t1.fk_skill_pool_id = t2.fk_skill_pool_id
WHERE
      t2.fk_skill_pool_id IS NULL

</cfquery>

<!--- to see the records that are not already in the database --->
<cfdump var="#qUpdate#" />


0
 
LVL 1

Author Comment

by:dl8453
ID: 17001478
My apologies on the delay in getting back to you, I had another issue I had to tend to and now I can focus on this again.  I read through the code and I <em>think</em> I understand it but I could no way come up with that on my own... or at least not yet.  :)  

Thanks for the GREAT information and the explanation and examples.  If you have teammates, ther are lucky to have a talented guy like you.
0
 
LVL 15

Expert Comment

by:DanielSKim
ID: 17001810
thank you for the kind words. let me know if you have any questions regarding the code up there.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now