PDA

View Full Version : embedded SQL question



Dan Walsh
27-Dec-2005, 02:42 PM
In the embedded SQL example there is a sequence along the lines of;

SQLOpen hDbc to hStmt
// create a query string
SQLExecuteDirect hStmt sQuery
// do something with the result
SQLClose hStmt

My question is, can I reuse hStmt for subsequent queries or should I
always close and start with a fresh handle?

TIA,

Dan

Bob Worsley
28-Dec-2005, 08:27 AM
Isn't hStmt simply a variable? If so then what difference if you reused it?
Bob

"Dan Walsh" <danwalsh46@earthlink.net> wrote in message
news:sFh7q2xCGHA.5164@dacmail.dataaccess.com...
> In the embedded SQL example there is a sequence along the lines of;
>
> SQLOpen hDbc to hStmt
> // create a query string
> SQLExecuteDirect hStmt sQuery
> // do something with the result
> SQLClose hStmt
>
> My question is, can I reuse hStmt for subsequent queries or should I
> always close and start with a fresh handle?
>
> TIA,
>
> Dan

Dan Walsh
28-Dec-2005, 09:11 AM
Bob Worsley wrote:
> Isn't hStmt simply a variable? If so then what difference if you reused it?
You would think so, but I seem to get enough surprises and just thought
I'd ask.

Ben Weijers
28-Dec-2005, 09:24 AM
You should not reuse statements for subsequent quesries.

Regards,

Ben Weijers
Data Access Worldwide

Dan Walsh
28-Dec-2005, 03:51 PM
Ben Weijers wrote:
> You should not reuse statements for subsequent quesries.
Thanks Ben.

DaveR
27-May-2009, 02:41 PM
Ben Weijers wrote:
> You should not reuse statements for subsequent quesries.
Thanks Ben.


three and a half years later...............:D

why? it seems to work sometimes.
It certainly 'seems' to work when I follow a select with an update of the same record....

(but never mind, I'll stop it at once)

Bob Worsley
27-May-2009, 02:56 PM
Too bad this wasn't your own original question, you would have established a record for taking the longest to answer it!

Peter Miska
21-Jun-2009, 05:12 AM
Dan,

Mr personal preference is to turn embedded SQL into Stored Procedures, even if they return a result set. Why?
1. SP's produce an execution plan that can give you clues about the cost of running your SQL.
2. The query optimizer can evaluate your SP's and create indexes for you if they'd make a difference. I've seen one SP (dealing with a large GL) that originally took almost a minute to run get reduced to seconds.
3. SP's and UDF's are things SQL Server knows about mainains performance statistics. Looking at object execution statistics tells you which SP's and UDFs are eating up CPU and IO time.
4. In a sense embedded SQL can be a time bomb. SQL server knows nothing about queries written in an app, can offer no information about how they run, and can't help you if you embedded SQL causes blocking transactions.

Martin Moleman
22-Jun-2009, 02:06 AM
hStmt is not just a variable. It is a handle (pointer) to an internal structure that maintains all kind of information about the statement. When re-using the statement incorrect information from this structure may be used, leading to unpredictable results.
So, always close the statement. That will destroy the internal structure.

chuckatkinson
22-Jun-2009, 08:13 AM
Well put Peter...

I would also add that ESQL can be a pain to maintain. SP are the write-once and forget it until you need to change it. And then you have one place to make any changes.

Also any table changes on the server can cause a lot of problems with ESQL.

Personally we experienced a nightmare with MySQL running Production on a Windows Server and development/testing on Linux due to case sensitivity in tablenames and columns on the Linux platform. SP and UDF do not have this problem, or at the very LEAST you will know about it immediately.

We are currently trying to remove all ESQL and migrate to SP's. But the mertech driver currently is making this difficult as it does not support result sets from SPs nor passed parameters on MySQL