BDE and SQL server

Hi,
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)
db.StartTransaction
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?
thanks
Johny
JohnyAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kretzschmarCommented:
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 ;-)
0
kretzschmarCommented:
btw.
do you call also unprepare?
did you tried different odbc-drivers?
0
JohnyAAuthor Commented:
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?

thanks
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

JohnyAAuthor Commented:
sorry didnt notice all your comments,
I use unprepare at the end, I tried different odbc versions, but what do you different drivers?
0
kretzschmarCommented:
>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;

Description

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;

Description

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 ;-)



0
JohnyAAuthor Commented:
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?

thanks
0
kretzschmarCommented:
>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 ;-)
0
kretzschmarCommented:
any results?
0
JohnyAAuthor Commented:
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.

Thanks
Johny
0
kretzschmarCommented:
>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 ;-)

0
kretzschmarCommented:
0
JohnyAAuthor Commented:
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?
0
kretzschmarCommented:
>is in inthe ODBC use pooling?
yes,
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
http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c&DisplayLang=en

meikl ;-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.