CFQUERYPARAM使用說明

CFQUERYPARAM使用說明

 

1.文字                         
     <cfset indcat = "電子">
     <cfquery name="select" DATASOURCE="database">
          ...
          where indcat = <CFQUERYPARAM value = "#indcat#" CFSQLType = "CF_SQL_VARCHAR">
     </cfquery>
 2.數字                        
     <cfset grade=99>
     <cfquery name="select" DATASOURCE="database">
          ...
          where id = <CFQUERYPARAM value = "#id#" CFSQLType = "CF_SQL_DECIMAL">
     </cfquery>

3.浮點小數                     
     <cfset grade=99.81>
     <cfquery name="select" DATASOURCE="database">
          ...
          where grade = <CFQUERYPARAM value = "#grade#" CFSQLType = "CF_SQL_DECIMAL" SCALE="2">
     </cfquery>

4.日期時分(yyyy/mm/dd hh:mm:ss)
     <cfset startDate = '2008/1/15 09:00:00'>
     <cfset endDate = '2008/1/15 18:00:00'>
     <cfquery name="select" DATASOURCE="database">
          ...
          where startDate between <CFQUERYPARAM value = "#startDate#" CFSQLType = "CF_SQL_TIMESTAMP"> and <CFQUERYPARAM value =         "#endDate#" CFSQLType = "CF_SQL_TIMESTAMP">                    
     </cfquery>

5.日期(yyyy/mm/dd)            
     <cfset startDate = '2008/1/15'>
     <cfquery name="select" DATASOURCE="database">
          ...
          where startDate = <CFQUERYPARAM value = "#startDate#" CFSQLType = "CF_SQL_DATE">
     </cfquery>

6.時分(hh:mm:ss)               
     <cfset startTime = '08:00'>
     <cfquery name="select" DATASOURCE="database">
          ...
          where startTime = <CFQUERYPARAM value = "#startTime#" CFSQLType = "CF_SQL_TIME">
     </cfquery>

7.List                        
     <cfset idList = "1256781,5235671,1564866">
     <cfquery name="select" DATASOURCE="database">
          ...
          where id in (<CFQUERYPARAM value="#idList#" CFSQLType="CF_SQL_DECIMAL" list="yes">)          
     </cfquery>

8.Like             
     <cfset jobStr = '%#job#%'>
     <cfquery name="select" DATASOURCE="database">
          ...
          where job like <CFQUERYPARAM value="jobStr" CFSQLType="CF_SQL_VARCHAR">
     </cfquery>

 

9.insert、update、delete
     <cfquery name="insert" DATASOURCE="database">
          insert into jobon
          (
           jobnum,
           job,
           inputdate
          )
          values
          (
           <CFQUERYPARAM value="#jobnum#" cfsqltype="CF_SQL_DECIMAL">,
           <CFQUERYPARAM value="#job#" cfsqltype="CF_SQL_VARCHAR">
           <CFQUERYPARAM value="#inputdate#" cfsqltype="CF_SQL_TIMESTAMP">
          )
     </cfquery>

 

     <cfquery name="update" DATASOURCE="database">
          update jobon
          set job = <CFQUERYPARAM value="#job#" cfsqltype="CF_SQL_VARCHAR">        
          where jobnum = <CFQUERYPARAM value="#jobnum#" cfsqltype="CF_SQL_DECIMAL">
          and inputdate  startDate between <CFQUERYPARAM value = "#startDate#" CFSQLType = "CF_SQL_TIMESTAMP"> and <CFQUERYPARAM value

            = "#endDate#" CFSQLType = "CF_SQL_TIMESTAMP">                    
     </cfquery>

 

     <cfquery name="delete" DATASOURCE="database">
          delete from jobon       
          where inputdate  startDate between <CFQUERYPARAM value = "#startDate#" CFSQLType = "CF_SQL_TIMESTAMP"> and <CFQUERYPARAM

            value = "#endDate#" CFSQLType = "CF_SQL_TIMESTAMP">                    
     </cfquery>