Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


BDE and SQL server

Posted on 2003-11-17
Medium Priority
Last Modified: 2013-11-23
I have application written in Delphi and connects to SQL server (2000), via BDE and ODBC.
When using TQuery or TStoredProcedure, prepare method, I think a new session to DB is opened cause I get exception about not being in transaction or deadlock because of the transaction.
the scenario as following:
qry <- create TQuery with session 1 (s1) and DB 1 (d1)
Update table
sql, change SQL, and call prepare
 (*) SELECT that table => dead lock

* in debug I found that in this stage if I check db.InTransaction I will get false.

The workaround was not to use prepare and create the params manually, but that is annoying and much code

Please advise, any ideas?
Question by:JohnyA
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
LVL 27

Expert Comment

ID: 9765276
you do not need to call prepare explicit (the bde does this implicit)
also preprare doesn't have any effect of a transaction,
its only needed to tell the server how much memory must be allocated for the parameters (-> binding)

maybe you should show a bit more code

meikl ;-)
LVL 27

Expert Comment

ID: 9765291
do you call also unprepare?
did you tried different odbc-drivers?

Author Comment

ID: 9765317
Thanks for responding quickly,
Prepare is needed in order not to create params manually, i.e. using :prmID in the SQL string will create automaticly the params otherwise exception will be raise.
what code do you exactly need?

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 9765332
sorry didnt notice all your comments,
I use unprepare at the end, I tried different odbc versions, but what do you different drivers?
LVL 27

Expert Comment

ID: 9765463
>Prepare is needed in order not to create params manually,
>i.e. using :prmID in the SQL string will create automaticly
>the params otherwise exception will be raise.

here you are on a wrong track:

about prepare (from delphi help)

---------------- paste begin

Sends a query to the Borland Database Engine (BDE) and the server for optimization prior to execution.

procedure Prepare;


Call Prepare to have the BDE and a remote database server allocate resources for the query and to perform additional optimizations. Calling Prepare before executing a query improves application performance.

Delphi automatically prepares a query if it is executed without first being prepared. After execution, Delphi unprepares the query. When a query will be executed a number of times, an application should always explicitly prepare the query to avoid multiple and unnecessary prepares and unprepares.

Preparing a query consumes some database resources, so it is good practice for an application to unprepare a query once it is done using it. The UnPrepare method unprepares a query.

Note:      When you change the text of a query at runtime, the query is automatically closed and unprepared.

------------ paste end

you see, its only for memory-allocation

usual the params are automatically added if the property paramCheck is set to true

from delphi help:

------------ paste begin

Specifies whether the parameter list for a query is regenerated if the SQL property changes at runtime.

property ParamCheck: Boolean;


Set ParamCheck to specify whether or not the Params property is cleared and regenerated if an application modifies the query&#8217;s SQL property at runtime. By default ParamCheck is True, meaning that the Params property is automatically regenerated at runtime. When ParamCheck is True, the proper number of parameters is guaranteed to be generated for the current SQL statement.

This property is useful for data definition language (DDL) statements that contain parameters as part of the DDL statement and that are not parameters for the TQuery. For example, the DDL statement to create a stored procedure may contain parameter statements that are part of the stored procedure. Set ParamCheck to False to prevent these parameters from being mistaken for parameters of the TQuery executing the DDL statement.

An application that does not use parameterized queries may choose to set ParamCheck to False, but otherwise ParamCheck should be True.

----------------- paste end

per default paramCheck is set to true

>create automaticly the params otherwise exception will be raise.

you may not get an exception if paramcheck is set to true
and you access the parameters without using prepare
(usual i use it often without preparing it)

>what code do you exactly need?
>but what do you different drivers?

i guess the problem is on another side
- ide self may lock the table (if some dataset is active)
- the odbc may not work properly (or is configured for shared connections)
- something in your code may cause this

thats why i asked

meikl ;-)


Author Comment

ID: 9765523
maybe you are right, I tried it on TStoreProcedure and didnt work ....
why Iam on this side, cause when I stopped using prepare and create params manually (in case of StoredProc) it worked, other wise deadlock.
If you are sure its not the reason please help finding out why and solve the problem.
how do I check if its configured for shared connections or not? lets say it is, this will cause a prob?

LVL 27

Expert Comment

ID: 9765893
>If you are sure its not the reason please help finding out why and solve the problem.
i am sure that this may not the problem and yes,
i will try to solve your problem

well lets say all is proper,
what can then cause a deadlock

- any triggers on the table, which are not ready (timing problem -> adding parameters by hand costs time)?
- no commited data from another session (ide/app/abnormal abended app)
- shared-connection-pool (driver)

if you can say, no one of this points is it,
then we must look into the source

- using more than one session-object (default and another-one -> check the session-properties of the datasets)?
- runtime created datasets, which are not properly freed (check, if they are already destroyed)
- maybe more

well, today i'm finishing, see you tomorrow

meikl ;-)
LVL 27

Expert Comment

ID: 9776836
any results?

Author Comment

ID: 9776845
Hi meikl,
I didnt have time for further tests, but for questions:
1. No triggers
2. Only one session is opened
3. it is runtime created datasets or to be more specific TQuery and TStoredProcedure and I prety sure it was freed correctly
4. How do you explain the fact that creating the params manually and not using prepare solved the prob?

I will to paste here the code as soon I will extract it from my system or write a small application that demonstrate this issue.

LVL 27

Expert Comment

ID: 9776901
>4. How do you explain the fact that creating the params manually
>and not using prepare solved the prob?

it could be a timing problem
- the driver may not ready with its post, but gave the control back to the app
- creating the params manually costs some milliseconds time, which may enough, to let finish the drivers job

but well thats only a guess

looking forward for your sample

meikl ;-)

LVL 27

Expert Comment

ID: 9776932

Author Comment

ID: 9776986
you said once "shared-connection-pool", how do I check that? is in inthe ODBC use pooling?
you said another drivers, what do you mean? I know there is only 1 driver, no?
LVL 27

Accepted Solution

kretzschmar earned 500 total points
ID: 9777078
>is in inthe ODBC use pooling?
just doubleclick on your driver,
and select the radio on "Don't pool connection . . ."

>you said another drivers, what do you mean?
there are multiple revisions arround,
depending on the installed mdac, installed service-packs
and/or the installed mssql-client-software

maybe a download of the latest mdac resolves the problem,
if you don't have the latest mdac installed

to get the latest mdac (ver 2.8) go

meikl ;-)

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

609 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