5 Replies Latest reply on Mar 24, 2010 10:26 AM by kenji776

    Cleaning Very Dynamic Queries

    kenji776

      Hey all,

      I am working on a function that will take a set of data, and log it into a database. The data comes from another page, and is received in the form scope. Problem is, I never know which data will be coming, and it often sends data I don't care to log. So what I want to do is just use a CFUPDATE call to update the database, since that will take everything in the form scope and use it to update the database. Of course to do that, I need to remove keys from the form struct that don't have matching columns in the database.

       

      I have a small function that "prototypes" the database (finds all columns, stores them in a list) and then another one that loops through all the keys in the form, and if it cannot find a match in list of column names, it removes them. Problem is, my cleaning function behaves oddly, leaving in keys that should have been removed, or when it removes a key, all the rest of the data seems to get shifted around, like up one position. I am starting to think my issues might due to some kind of variable scoping problem, because I have to pass this structure named form around like a million times (it comes in named form, it gets passed to my cleaning function which calls it form, it has to get passed to the CFupdate call named as form). Anyway, If anyone has had to do something like this, or can see any obvious problems in my code, please feel free to chime in, as I am kinda at a loss. Thanks.

       

           <cffunction name="protoTypeDB" access="public"> 
                <cfargument name="table" type="string" required="yes" hint="What is the name of the table we are validating against?">
                <cfargument name="dsn" type="string" required="no" default="webserver" hint="What DB">
                <cfset var returnstruct = structnew()>
                <cftry>
                     <cfquery name="recordQuery" datasource="#arguments.dsn#" maxrows="1" cachedWithin="#CreateTimeSpan(1,0,0,0)#">
                          SELECT * FROM #arguments.table#
                     </cfquery> 
                     
                     <cfset returnstruct.success = true>
                     <cfset returnstruct.data = recordQuery.columnlist>
                     
                     <cfcatch type="Any">
                          <cfset returnstruct.success = false>
                          <cfset returnstruct.error = cfcatch>
                     </cfcatch> 
                </cftry>
                
                <cfreturn returnstruct>
            </cffunction>
      
               <cffunction name="StripNonDatabasefields" access="public" hint="Remove and fields from a scope that do not exist in the database">
                <cfargument name="form" type="struct" required="yes" hint="A structure with fields to test existence for in the database">
                <cfargument name="validationTable" required="yes" hint="What is the name of the table we need to validate against">
                <cfargument name="dsn" required="no" hint="what DB" default="webserver">
                <cfset var returnstruct = structnew()>
                     <cftry>
                     
                          <!--- Create an array to hold all the names of fields that don't exist in our database that were passed in the form --->
                          <cfset NonRecordablefields = arraynew(1)>
                          
                          <!--- Create a counter --->
                          <cfset counter = 0>
                          
                          <cfset DataBaseFieldNames = protoTypeDB(arguments.validationtable,arguments.dsn).data>
                          
                          
                          <!--- Loop over all the fields in the form structure --->
                          <cfloop collection="#arguments.form#" item="Fieldname">
                               <cftry>     
                                    <!--- If a field exists in the form structure that does not exist in the list of columns returned by the query --->
                                    <cfif ListFind(DataBaseFieldNames, fieldname) EQ 0 or len(arguments.form[fieldname]) EQ 0>
                                         <cfset counter = counter +1>
                                         <!--- Record the name of this form field in the nonrecordablefields array --->
                                         <cfset NonRecordableFields[counter] = fieldname & " Is Empty or is not a valid database field and was not recorded. Value Was: #form[fieldname]#">
                                         
                                         <!--- Strip the field from the form structure so it is not passed to the insert/update statments below --->
                                         <cfset formIdDelete = StructDelete(arguments.form, fieldname)>
                                         <cfelse>
                                              <!--- Sometimes, for reasons unknown, Angel.com will send the same value twice, seperated by "," since we
                                                     only want the first instance of the string, we can use listfirst to get it --->
                                              <cfset arguments.form[fieldname] = listfirst(arguments.form[fieldname],",")>
                                              <cfset arguments.form[fieldname] = trim(arguments.form[fieldname])>
                                    </cfif>
                                    <cfcatch type="any">
                                         <!--- Some field in the database does not exist in the structure i guess. No biggie. --->
                                    </cfcatch>
                               </cftry>
                          </cfloop>
                          
                          <cfset returnstruct.removedFields = NonRecordablefields>
                          <cfset returnstruct.form = arguments.form>
                          <cfset returnstruct.dbFieldNames = DataBaseFieldNames>
                          
                          <cfcatch type="any">
                               <cfset returnstruct.error = cfcatch>
                               <cfset returnstruct.arguments = arguments>
                               <cfset returnstruct.form = arguments.form>
                               
                          </cfcatch>
                     </cftry>
                     <cfreturn returnstruct> 
                                    
           </cffunction>
      
        • 1. Re: Cleaning Very Dynamic Queries
          Adam Cameron. Level 5

          Why don't you just use <cfquery> instead of trying to trick <cfupdate> into working?

           

          A couple of observations from your code:

          * your query in prototypeDB will force the DB to all rows from your table.  The maxrows attribute just row-limits at the CF end of things (after the DB has fetched all the rows), not the DB side of things.

          * Why don't you use <cfdbinfo> to get the table's columns?  If you're using CF8 or higher, anyhow.

          * You should be VARing all your function-local variables in all your functions.

           

          --

          Adam

          • 2. Re: Cleaning Very Dynamic Queries
            Dan Bracuk Level 5

            Regarding:

            * your query in prototypeDB will force the DB to all rows from your table.  The maxrows attribute just row-limits at the CF end of things (after the DB has fetched all the rows), not the DB side of things.

            * Why don't you use <cfdbinfo> to get the table's columns?  If you're using CF8 or higher, anyhow.

             

            If you are version 7 or below, an alternative approach is to add "where 1 = 2" to your query.  Then you've already row limited your query to 0 rows and you can still get your column list.

            • 3. Re: Cleaning Very Dynamic Queries
              kenji776 Level 1

              Hey guys, thanks for your input. I took some of your advice, and refactored my code quite a bit. One issue is though that sometimes the wrong length is applied to a field, so the left() applies the wrong length. Not sure why that is. Other than that, this new one seems to be working pretty well thus far. Also totally removed the need for the prototype DB.

               

                   <cffunction name="StripNonDatabasefields" access="public" hint="Remove and fields from a scope that do not exist in the database">
                        <cfargument name="form" type="struct" required="yes" hint="A structure with fields to test existence for in the database">
                        <cfargument name="dsn" required="yes" hint="what DB" default="webserver">
                        <cfargument name="table" required="yes" hint="What is the name of the table we need to validate against">
                        <cfset var returnstruct = structnew()>
                        <cfset var cleanForm = structnew()>
                        <cfset var counter = 0>
                        <cfset var FieldResults = arraynew(1)>
                        <cfset cleanForm = arguments.form>
                             <cftry>
                             
                                  <!--- Create an array to hold all the names of fields that don't exist in our database that were passed in the form --->
                                  <cfset FieldResults = arraynew(1)>
                                  
                                  <!--- If we don't have info stored for this table in the server scope, then make it --->
                                  
                                  <cfif not isquery( "server.tableData[arguments.dsn][arguments.table]" )>
                                       <cfdbinfo datasource="#arguments.dsn#" table="#arguments.table#" type="columns" name="DBResults">
                                       <cfset server.tableData[arguments.dsn][arguments.table] = dbResults> 
                                  </cfif>
                                  
                                  <!--- Okay, need to loop over all form fieldnames, find any that don't have a matching column in the DB and remove them --->
                                  <cfset ColumnNames = valuelist(DBResults.column_name,",")>
                   
                                  <cfloop collection="#cleanForm#" item="Fieldname">
                                       <cfset counter = counter +1>
                                       <cfif ListFindnoCase(ColumnNames, fieldname) EQ 0 or len(cleanForm[fieldname]) EQ 0>
                                                 <!--- Record the name of this form field in the nonrecordablefields array --->
                                                 <cfset FieldResults[counter] = fieldname & " Is Empty or is not a valid database field and was not recorded. Value Was: #form[fieldname]#">
                                                 
                                                 <!--- Strip the field from the form structure so it is not passed to the insert/update statments below --->
                                                 <cfset formIdDelete = StructDelete(cleanForm, fieldname)>
                                       
                                            <cfelse>
                                                 <cfset FieldResults[counter] = fieldname & " Is Valid and contains " &cleanForm[fieldname]>
                                                 <!--- Sometimes, for reasons unknown, Angel.com will send the same value twice, seperated by "," since we
                                                        only want the first instance of the string, we can use listfirst to get it --->
                                                 <cfset cleanForm[fieldname] = listfirst(cleanForm[fieldname],",")>
                                                 <cfset cleanForm[fieldname] = trim(cleanForm[fieldname])>     
                                                 
                                                 <!--- Now, if it is a numeric type of data, lets do our best to make it numeric --->
                                                 <cfset ColumnDataType = server.tableData[arguments.dsn][arguments.table]['TYPE_NAME'][counter]>
                                                 
                                                 <cfif ColumnDataType EQ "int" or ColumnDataType EQ "bigint">
                                                      <cfset cleanForm[fieldname] = ReReplace(cleanForm[fieldname], "[^0-9]", "", "ALL")>
                                                 </cfif>          
              
                                                 <!---Make sure we arn't trying to insert data that is larger than the column --->
                                                 <cfset ColumnSize = server.tableData[arguments.dsn][arguments.table]['COLUMN_SIZE'][counter]>
                                                 <cfset cleanForm[Fieldname] = left(cleanForm[Fieldname], ColumnSize)>                                        
                                       </cfif>                                   
                                  </cfloop>
                                  
                                  <cfset returnstruct.FieldResults = FieldResults>
                                  <cfset returnstruct.cleanedForm = cleanForm>
                                  
                                  <cfcatch type="any">
                                       <cfset returnstruct.error = cfcatch>
                                       <cfset returnstruct.arguments = arguments>
                                       <cfset returnstruct.cleanedForm = cleanForm>
                                       
                                  </cfcatch>
                             </cftry>
                             <cfreturn returnstruct> 
                                            
                   </cffunction>
              
              • 4. Re: Cleaning Very Dynamic Queries
                kenji776 Level 1

                Actually, nevermind, the problems run deeper than just the left() being off. Also the data type comparisons are wrong as well. Seems as though the way I am referencing the query column is incorrect. CF queries start start from 1 right? My counter starts at one, and thats how I reference the table cells I need to pull data from. WTF.

                 

                Check this out

                http://portal.fpitesters.com/test.cfm

                 

                This page calls my function. Refresh it a few times. The fieldresults data shifts around randomly. Whats up with that?

                • 5. Re: Cleaning Very Dynamic Queries
                  kenji776 Level 1

                  Okay, I think i have a working version. Many of my problems where stemming from the fact that you can't sort a struct. So i took the struct keys, put them into a list, sorted then, then loop over that. Seems to work. Here is the new version, if anyone wants to use it.

                   

                       <cffunction name="StripNonDatabasefields" access="public" hint="Remove and fields from a scope that do not exist in the database">
                            <cfargument name="form" type="struct" required="yes" hint="A structure with fields to test existence for in the database">
                            <cfargument name="dsn" required="yes" hint="what DB" default="webserver">
                            <cfargument name="table" required="yes" hint="What is the name of the table we need to validate against">
                            <cfset var returnstruct = structnew()>
                            <cfset var cleanForm = structnew()>
                            <cfset var counter = 0>
                            <cfset var FieldResults = arraynew(1)>
                            <cfset var keyList = StructKeyList(form)>  
                            <cfset keyList = ListSort(keyList, "TEXT")>
                            
                            
                            <cfset cleanForm = arguments.form>
                                 <cftry>
                                 
                                      <!--- Create an array to hold all the names of fields that don't exist in our database that were passed in the form --->
                                      <cfset FieldResults = arraynew(1)>
                                      
                                      <!--- If we don't have info stored for this table in the server scope, then make it --->
                                      
                                      <cfif not isquery( "server.tableData[arguments.dsn][arguments.table]" )>
                                           <cfdbinfo datasource="#arguments.dsn#" table="#arguments.table#" type="columns" name="DBResults">
                                           <cfset server.tableData[arguments.dsn][arguments.table] = dbResults> 
                                      </cfif>
                                      
                                      <!--- Okay, need to loop over all form fieldnames, find any that don't have a matching column in the DB and remove them --->
                                      <cfset ColumnNames = valuelist(DBResults.column_name,",")>
                       
                                      <cfloop list="#keyList#" index="Fieldname">
                                           <cfset counter = counter +1>
                                           <cfif ListFindnoCase(ColumnNames, fieldname) EQ 0 or len(cleanForm[fieldname]) EQ 0>
                                                     <!--- Record the name of this form field in the nonrecordablefields array --->
                                                     <cfset FieldResults[counter] = fieldname & " Is Empty or is not a valid database field and was not recorded. Value Was: #form[fieldname]#">
                                                     
                                                     <!--- Strip the field from the form structure so it is not passed to the insert/update statments below --->
                                                     <cfset formIdDelete = StructDelete(cleanForm, fieldname)>
                                           
                                                <cfelse>
                                                     <cfset FieldResults[counter] = fieldname & " Is Valid and contains " &cleanForm[fieldname]>
                                                     <!--- Sometimes, for reasons unknown, Angel.com will send the same value twice, seperated by "," since we
                                                            only want the first instance of the string, we can use listfirst to get it --->
                                                     <cfset cleanForm[fieldname] = listfirst(cleanForm[fieldname],",")>
                                                     <cfset cleanForm[fieldname] = trim(cleanForm[fieldname])>     
                                                     
                                                     <!--- Now, if it is a numeric type of data, lets do our best to make it numeric --->
                                                     <cfset ColumnDataType = server.tableData[arguments.dsn][arguments.table]['TYPE_NAME'][counter]>
                                                     
                                                     
                                                     <cfif ColumnDataType eq "int" or ColumnDataType eq "bigint">
                                                          <cfset cleanForm[fieldname] = ReReplace(cleanForm[fieldname], "[^0-9]", "", "ALL")>
                                                          
                                                          <cfif ColumnDataType eq "int" and cleanForm[fieldname] GT 2147483647>
                                                               <cfset cleanForm[fieldname] = 0>
                                                          </cfif>
                  
                                                          <cfif ColumnDataType eq "bigint" and cleanForm[fieldname] GT 9223372036854775807>
                                                               <cfset cleanForm[fieldname] = 0>
                                                          </cfif>
                                                                                                  
                                                          
                                                          <cfif len(cleanForm[fieldname]) LT 1>
                                                               <cfset cleanForm[fieldname] = 0>
                                                          </cfif>
                                                     </cfif>
                                                     
                                                     <!---Make sure we arn't trying to insert data that is larger than the column --->
                                                     <cfset ColumnSize = server.tableData[arguments.dsn][arguments.table]['COLUMN_SIZE'][counter]>
                                                     <cfset cleanForm[Fieldname] = left(cleanForm[Fieldname], ColumnSize)>                         
                                           </cfif>                                   
                                      </cfloop>
                                      
                                      <cfset returnstruct.FieldResults = FieldResults>
                                      <cfset returnstruct.cleanedForm = cleanForm>
                                      
                                      <cfcatch type="any">
                                           <cfset returnstruct.error = cfcatch>
                                           <cfset returnstruct.arguments = arguments>
                                           <cfset returnstruct.cleanedForm = cleanForm>
                                           <cfset returnstruct.FieldResults = FieldResults>
                                      </cfcatch>
                                 </cftry>
                                 <cfreturn returnstruct> 
                                                
                       </cffunction>