Delay SP Prepare/Execute??
in reference to an original q...
https://www.experts-exchange.com/questions/21869846/cfstoredproc-fails-yet-SP-runs-in-QA.html
I believe i've tracked the issue down to a problem JDBC has with dynamic sql. if the driver runs into invalid sql as the stmt is being prepared (which it will) it throws an error.
My Q... (not even sure how to word this)
is there a way to delay/defer the sp to avoid the driver error. In my searching I've seen DB2 has a deferPrepare stmt which seems (?) to address this issue. Is there anything similar to this in T-sql?
any other ideas?
https://www.experts-exchange.com/questions/21869846/cfstoredproc-fails-yet-SP-runs-in-QA.html
I believe i've tracked the issue down to a problem JDBC has with dynamic sql. if the driver runs into invalid sql as the stmt is being prepared (which it will) it throws an error.
My Q... (not even sure how to word this)
is there a way to delay/defer the sp to avoid the driver error. In my searching I've seen DB2 has a deferPrepare stmt which seems (?) to address this issue. Is there anything similar to this in T-sql?
any other ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if the driver runs into invalid sql as the stmt is being prepared (which it will) it throws an error.
Is there a way to catch the error? You may have to catch it at the jdbc level.
Is there a way to catch the error? You may have to catch it at the jdbc level.
Sorry - the explanation of DeferPrepare is from http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2tools.perepref.doc/r0177out.htm
ASKER
thx but that isn't exactly what I'm looking for... all that does is delay the error
what i need(?) is a way to create the entire sql stmt like
"the prepare request to the server is chained to the execute request and is sent in the same packet on the first request. This setting results in increased performance when executing prepared statements." (that's the DB2 explanation of deferPrepare)
my interest isn't in the "performance benefit" but the fact that done this way, the driver doesn't choke while the sql is being prepared before the actual execute.
hmmm... could I perhaps pass the sql string creation to a udf and then run the sql on the returned string??...might be an idea.
what i need(?) is a way to create the entire sql stmt like
"the prepare request to the server is chained to the execute request and is sent in the same packet on the first request. This setting results in increased performance when executing prepared statements." (that's the DB2 explanation of deferPrepare)
my interest isn't in the "performance benefit" but the fact that done this way, the driver doesn't choke while the sql is being prepared before the actual execute.
hmmm... could I perhaps pass the sql string creation to a udf and then run the sql on the returned string??...might be an idea.
ASKER
"thx but that isn't exactly what I'm looking for... all that does is delay the error " was for WAITFOR ...
ptjcb - that's what i was afraid of... any thoughts on whether the udf idea has a chance?
ptjcb - that's what i was afraid of... any thoughts on whether the udf idea has a chance?
A udf has its own limitations and you would run into scope issues that would frustrate more than help.
> s there a way to delay/defer the sp to avoid the driver error.
WAITFOR TIME '22:00'