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
Solved

BDE and SQL server

Posted on 2003-11-17
15
1,095 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
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 ;-)
0
 
LVL 27

Expert Comment

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

Author Comment

by:JohnyA
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?

thanks
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:JohnyA
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?
0
 
LVL 27

Expert Comment

by:kretzschmar
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;

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
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?

thanks
0
 
LVL 27

Expert Comment

by:kretzschmar
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 ;-)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9776836
any results?
0
 

Author Comment

by:JohnyA
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.

Thanks
Johny
0
 
LVL 27

Expert Comment

by:kretzschmar
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 ;-)

0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9776932
0
 

Author Comment

by:JohnyA
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?
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 125 total points
ID: 9777078
>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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

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…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

792 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