Solved

BDE and SQL server

Posted on 2003-11-17
15
1,089 Views
Last Modified: 2013-11-23
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
0
Comment
Question by:JohnyA
  • 8
  • 5
15 Comments
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
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
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
btw.
do you call also unprepare?
did you tried different odbc-drivers?
0
 

Author Comment

by:JohnyA
Comment Utility
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
 

Author Comment

by:JohnyA
Comment Utility
sorry didnt notice all your comments,
I use unprepare at the end, I tried different odbc versions, but what do you different drivers?
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
>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
 

Author Comment

by:JohnyA
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
>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
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
any results?
0
 

Author Comment

by:JohnyA
Comment Utility
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
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
>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
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
0
 

Author Comment

by:JohnyA
Comment Utility
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
 
LVL 27

Accepted Solution

by:
kretzschmar earned 125 total points
Comment Utility
>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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now