Weird things happen when using .cfc function arguments

Report · Dec 19, 2008

Hey Guys,
I have a function as the following attached code, I can invoke this function and it works well. but when I change the number 32144809 to <cfqueryparam value="#arguments.jpRunNum#" cfsqltype="cf_sql_integer"> and invoke it by
<cfset qGetJPDependency = CreateObject("component","cfdocs.imd.criticalPath.components.CriticalPath").test(run_num)>,it firstly takes a long time waiting without any response, then it shows a message "Request timed out waiting to execute", I don't how to explain this, is there anyone know the reason caused this issue????
Thanks!

Report · Dec 21, 2008

Get the query to work in a .cfm page with cfqueryparam first. Then any problems you subsequently have will be a result of your function call.

Report · Dec 23, 2008

I tried as you suggested, but it still doesn't work....
I don't know why it can't work when using <cfquetyparam>... that's really weird..
Could you please share your skype or MSN id with me? Maybe we can talk on this issue offline..

Report · Dec 23, 2008

quote:

Originally posted by: safin_luo
I tried as you suggested, but it still doesn't work....
I don't know why it can't work when using <cfquetyparam>... that's really weird..
Could you please share your skype or MSN id with me? Maybe we can talk on this issue offline..

I have neither of those to share and prefer public discussions. Somebody else may spot something I missed.

If the problem appears to be the use of cfqueryparam, I suggest that you start by making sure you can use it at all in a very simple query. Something like

select distinct <cfqueryparam cfsqltype="cf_sql_integer" value = "1"> abc
from some_small_table.

If that works, look at your own query. Does the datatype of your field match the cfsqltype of your queryparam?

Report · Dec 23, 2008

What happens if you try to use <cfquryParam value="32144809"
cfsqltype="cf_sql_integer">?

Does the value '3214809' fit inside an SQL integer value, it seems a bit
big to me. You may need to be using cfsqltype="cf_sql_bigint".

Report · Dec 23, 2008

Depending on what database server you're using, and what driver, changing a static value to a <cfqueryparam> can change the statement sent to the database from a static SQL statement to a prepared statement. This can drastically change the query plan, thereby changing execution speed.

Report · Dec 24, 2008

Have you checked the logs to see what (if anything) else is being reported
back about this?

Can you check what's going on on the Oracle - am guessing it's Oracle from
the "CONNECT BY PRIOR" - box @ the time? I presume you can set it to log
all SQL or JDBC activity?

Are you certain (absolutely ~) that arguments.jpRunNum contains 32144809,
for the purposes of the test that's failing?

What if you factor the query out into a CF template and run these tests:

1) as is in your example
2) change 32144809 to use a <cfqueryparam>, but still hard code the value
to 32144809.
3) set a variable to 32144809, and use the variable as the value of the
parameter.

From what you're describing, it seems like your query is getting in a loop,
which with this sort of thing can be down to giving the CONNECT BY PRIOR a
bung value.

--
Adam