Board index » delphi » MS SQL Server/TQuery Insert problem

MS SQL Server/TQuery Insert problem

I use Delphi 3 and MS SQL server.
I obtain a result set with TQuery and link it to a TDBGrid, where I
insert additional records which disappear instantly. The only way to
retrieve them is to close/opn TQuery, which is inefficient and loses my
positionaing.

Is there a way to make my TQuery SENSITIVE to STOREs.

Thanks

 

Re:MS SQL Server/TQuery Insert problem


Hi Hakan,

it seems that this is a known problem with SQL backends.

After reading some older threads regarding this issue
I've been coming to the conclusion that I have only
two ways to solve this problem:

1) Doing an explicit Close/Open/Locate sequence
2) Using cached updates

As I don't want to use cached updates if I don't need them
I decided to write a new Component derived from TQuery.

- Add a new Property, say FLocateField
- Override doBeforePost
- Override doAfterPost
- In doBeforePost save the state of the DataSet and
  value used for a Locate
- In doAfterPost check if the state was dsInsert.
  If so do the Close/Open/Locate.
  In the Locate call use FLocateField as the Column and
  the value saved in doBeforePost.

Just my conclusion after{*word*154} around in the ng's the
last two days. (Nearly full time. <g>)

Maybe someone can correct me, if I'm wrong.
It would also be interesting if anybody is
using another approach.

Have a nice day

Heinz

Quote
Hakan UNLU wrote:

> I use Delphi 3 and MS SQL server.
> I obtain a result set with TQuery and link it to a TDBGrid, where I
> insert additional records which disappear instantly. The only way to
> retrieve them is to close/opn TQuery, which is inefficient and loses my
> positionaing.

> Is there a way to make my TQuery SENSITIVE to STOREs.

> Thanks

Re:MS SQL Server/TQuery Insert problem


On Sat, 03 Jan 1998 14:22:38 +0100, Heinz Schiffmann

Quote
<a...@mail.augustanet.de> wrote:
>Hi Hakan,

>it seems that this is a known problem with SQL backends.

>After reading some older threads regarding this issue
>I've been coming to the conclusion that I have only
>two ways to solve this problem:

>1) Doing an explicit Close/Open/Locate sequence
>2) Using cached updates

>As I don't want to use cached updates if I don't need them
>I decided to write a new Component derived from TQuery.

Heinz,

I understand you don't use cached updates. Can you explain how you
make your TQueries editable and updatable? Till now I am using cached
updates, but I have some problems with them and therefore I am
interested in alternatives. However, as far as I can see, a TQuery is
not even editable if you don't use cached updates and TUpdateSQL
objects.
Can you clarify?

Jan den Ouden

E:mail denOu...@compuserve.com
Wezep - Netherlands

Re:MS SQL Server/TQuery Insert problem


Jan,

When setting the RequestLive property to TRUE, the BDE attempts
to return an updatetable result set. But this is no guarantee that
the requested result set is actually updatetable. You can check
this with the CanModify property afterwards.

However, there are some restrictions for getting a live result
set. You can find them in the Developers Guide(Chapter 11, Page 16).
- No DISTINCT clause
- No aggregate functions
- No GROUP BY or HAVING clauses
...

If your query conflicts with these restrictions you have to
choose Chached Updates, but I think the most common queries
will get a live result set and therefore don't need them.

Hope that helps.

Heinz

Quote
Jan den Ouden wrote:

> Heinz,

> I understand you don't use cached updates. Can you explain how you
> make your TQueries editable and updatable? Till now I am using cached
> updates, but I have some problems with them and therefore I am
> interested in alternatives. However, as far as I can see, a TQuery is
> not even editable if you don't use cached updates and TUpdateSQL
> objects.
> Can you clarify?

> Jan den Ouden

Re:MS SQL Server/TQuery Insert problem


On Sun, 04 Jan 1998 23:40:49 +0100, Heinz Schiffmann

Quote
<a...@mail.augustanet.de> wrote:

>If your query conflicts with these restrictions you have to
>choose Chached Updates, but I think the most common queries
>will get a live result set and therefore don't need them.

Do you really mean that?
Other important conditions for live result sets are:
- Query involves only a single table
- No order by clause

In my case, and I think in most highly normalized databases, these
conditions make live queries in most cases impossible, don't you think
so?

Jan den Ouden

E:mail denOu...@compuserve.com
Wezep - Netherlands

Re:MS SQL Server/TQuery Insert problem


Jan

You're applying the local SQL limitations to live queries.  A query
conducted against a proper C/S backend may, or may not work with the
limitations you present. (It all depends on which C/S database you
use).

Derek Davidson
Author of DK's Audit Components
Get a FREE copy from my web site at :
http://freespace.{*word*269}.net/d.davidson

(Remove the x to EMail me : der...@mksoft.com)

Re:MS SQL Server/TQuery Insert problem


Quote
On Thu, 08 Jan 1998 09:07:07 EST, Derek Davidson <derekx> wrote:
>Jan

>You're applying the local SQL limitations to live queries.  A query
>conducted against a proper C/S backend may, or may not work with the
>limitations you present. (It all depends on which C/S database you
>use).

>Derek Davidson
>Author of DK's Audit Components
>Get a FREE copy from my web site at :
>http://freespace.{*word*269}.net/d.davidson

>(Remove the x to EMail me : der...@mksoft.com)

Thanks very much for pointing that out. The documentation that comes
with D2 doesn't mention anything about differences between local SQL
and server SQL regarding live result sets, so I never really checked
out on live results sets.
I am going to check now how things work with Interbase.
Thanks again.

Jan den Ouden

E:mail denOu...@compuserve.com
Wezep - Netherlands

Other Threads