Solved

Delay SP Prepare/Execute??

Posted on 2006-07-05
7
378 Views
Last Modified: 2008-02-01
in reference to an original q...
http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_21869846.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?
0
Comment
Question by:SidFishes
  • 4
  • 2
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17044868
SidFishes,
> s there a way to delay/defer the sp to avoid the driver error.

WAITFOR TIME '22:00'

0
 
LVL 27

Accepted Solution

by:
ptjcb earned 500 total points
ID: 17045240
is there a way to delay/defer the sp to avoid the driver error.

No.

When SQL creates the stored procedure it validates the syntax. When the sql is executed then it creates a query plan and checks if everything is in place (tables are created, etc). TSQL does not have anything like deferPrepare in DB2 (DEFERPREPARE    Indicates whether preparation of dynamic SQL statements was deferred. Possible values are:   YES      Dynamic SQL statement preparation was deferred.  NO   Dynamic SQL statements were prepared immediately. ).
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 17045245
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 27

Expert Comment

by:ptjcb
ID: 17045268
0
 
LVL 36

Author Comment

by:SidFishes
ID: 17045283
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.

 
0
 
LVL 36

Author Comment

by:SidFishes
ID: 17045311
"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?
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 17045449
A udf has its own limitations and you would run into scope issues that would frustrate more than help.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question