The performance hit I meant was on the cf-app and then the inability of the
app server (or would it be the db server?) to cache that query.
also cfqueryparam doesn't seem to be working in this case.
here is my code
<cfdump var="#form#">
<cfparam name="form.user_id" default="1">
<cfset variables.dateTimeStamp = createodbcdatetime(now())>
<cftry>
<cfquery name="insertProduct" datasource="#application.dsn#">
insert into case_products
(
case_id
,created_by
,datetimestamp
,product_id
,formulation
<cfif len(trim(form.freq))>,frequency</cfif>
<cfif len(trim(form.dosage_route))>,route</cfif>
<cfif len(trim(form.product_dosage))>,dosage</cfif>
<cfif len(trim(form.product_duration))>,duration</cfif>
<cfif len(trim(form.product_exposure))>,exposure</cfif>
<cfif len(trim(form.product_indication))>,indication_for_use</cfif>
<cfif len(trim(form.product_interaction))>,interaction</cfif>
)
values
(
#form.case_id#
,#form.user_id#
,#variables.dateTimeStamp#
,#form.product_id#
<cfqueryparam value="#form.product_formula#" cfsqltype="cf_sql_char"
null="#YesNoFormat(NOT LEN(TRIM(form.product_formula)))#">
<cfif len(trim(form.freq))>,#form.freq#</cfif>
<cfif len(trim(form.dosage_route))>,#form.dosage_route#</cfif>
<cfif len(trim(form.product_dosage))>,#form.product_dosage#</cfif>
<cfif
len(trim(form.product_duration))>,#form.product_duration#</cfif>
<cfif
len(trim(form.product_exposure))>,#form.product_exposure#</cfif>
<cfif
len(trim(form.product_indication))>,#form.product_indication#</cfif>
<cfif
len(trim(form.product_interaction))>,#form.product_interaction#</cfif>
)
</cfquery>
<cfcatch type = "any">
<!--- the message to display --->
<h3>You've Thrown a Database <b>Error</b></h3>
<cfoutput>
<!--- and the diagnostic message from the ColdFusion server --->
<p>#cfcatch.message#</p>
<p>Caught an exception, type = #CFCATCH.TYPE# </p>
<p>The contents of the tag stack are:</p>
<cfloop index = i from = 1
to = #ArrayLen(CFCATCH.TAGCONTEXT)#>
<cfset sCurrent = #CFCATCH.TAGCONTEXT[i]#>
<br>#i# #sCurrent["ID"]#
(#sCurrent["LINE"]#,#sCurrent["COLUMN"]#)
#sCurrent["TEMPLATE"]#
</cfloop>
</cfoutput>
</cfcatch>
</cftry>
--
The part that is throwing the error is the form.product_formula. I
intentionally sent over empty formfields. cfqueryparam doesn't seem to
insert in a null if the string is empty.I have sent over both "NULL" and
empty string and the cfdump is always empty. If i put the <cfif> around the
sql statements it works though.
On Mon, Jan 11, 2010 at 5:01 PM, Leigh <cfsearch...@yahoo.com> wrote:
>
> > I can get around this by using a <cfif
> > len(trim(form.fieldname)>. Is there
> > a better way to handle this? Isn't there a
> > performance hit for this sort of
> > quasi-dymanic sql statement?
>
> No, because the sql string is all constructed in CF. Then the final string
> is sent (all at once) to your database. So the database is not even aware of
> any conditionals.
>
>
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329553
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
