Board index » delphi » Autoincrement fields starts to increment itself when a Local SQL query is run

Autoincrement fields starts to increment itself when a Local SQL query is run

On Thu, 23 Sep 1999 00:23:28 +0200, "Stefan Bodingh"

Quote
<stefan.bodi...@swipnet.se> wrote:
>Found a problem where Autoincrement fields starts to increment itself when a
>Local SQL query is run...

[...]

I will look at this as time allows. It is not obvious what the problem is
and requires more extensive testing.

==========================================================================
Steve Koterski                  "Computers are useless. They can only give
Technical Publications          you answers."
Borland                                       -- Pablo Picasso (1881-1973)
http://www.borland.com/techpubs/delphi

 

Re:Autoincrement fields starts to increment itself when a Local SQL query is run


Found a problem where Autoincrement fields starts to increment itself when a
Local SQL query is run. This error is new for the 5.10 BDE, it did not exist
in 5.01. How to reproduce:
Tablename BDE_BUG2 (Paradox table)

Fieldnames and type:
   +           A35                   S             N
Counter   Name          Status       Invoice
 4324       test                   1
 4325       test                   1
 4326       test                   1
 4330       Helpension      2
 4342       test4341          5           -29,00

Then you run this query:

 UPDATE "BDE_BUG2.db"
SET Status = 5
WHERE Invoice = -29

And you find that the Autoincrement field will increment each time you run
the query.

/Stefan

Re:Autoincrement fields starts to increment itself when a Local SQL query is run


tried it and it behaves
as expected - autoinc field
DOES NOT change (BDE 5.10)
--
Roman
(please remove 'stopspam' in header when replying)
mail: i...@rksolution.cz
URL: www.rksolution.cz

Re:Autoincrement fields starts to increment itself when a Local SQL query is run


Sorry, I forgot to mention that the WHERE field in the query need to have a
secondary index (invoice field) .

/Stefan

Roman Krejci <Kre...@stopspam.mbox.cesnet.cz> skrev i
diskussionsgruppsmeddelandet:7sd50p$9...@forums.borland.com...

Quote
> tried it and it behaves
> as expected - autoinc field
> DOES NOT change (BDE 5.10)
> --
> Roman
> (please remove 'stopspam' in header when replying)
> mail: i...@rksolution.cz
> URL: www.rksolution.cz

Re:Autoincrement fields starts to increment itself when a Local SQL query is run


On Thu, 23 Sep 1999 14:09:21 +0200, "Roman Krejci"

Quote
<Kre...@stopspam.mbox.cesnet.cz> wrote:
>tried it and it behaves
>as expected - autoinc field
>DOES NOT change (BDE 5.10)

There was additional information posted by Karim Amin in another thread on
the same subject. That post seemed to intimate that the problem only
manifested itself when there was a secondary index (or indexes) on
non-autoinc fields and those non-autoinc fields are set in an UPDATE
statement. I have not yet had a chance to set up a scenario and test this.

==========================================================================
Steve Koterski                  "Computers are useless. They can only give
Technical Publications          you answers."
Borland                                       -- Pablo Picasso (1881-1973)
http://www.borland.com/techpubs/delphi

Re:Autoincrement fields starts to increment itself when a Local SQL query is run


I added secondary index (maintained, case sensitive) on
Invoice field, then on Status field, then on both,
and the result is still the same - Autoinc does not
change in update statement (I assume that the
primary index is the autoinc itself)
--
Roman
(please remove 'stopspam' in header when replying)

Stefan Bodingh p1e ve zprv <7sdsqp$g3...@forums.borland.com>.

Quote
>Sorry, I forgot to mention that the WHERE field in the query need to have a
>secondary index (invoice field) .

>/Stefan

Re:Autoincrement fields starts to increment itself when a Local SQL query is run


I played around a bit with pdox table and found this:

I cannot confirm Stefan's effect but
BDE 5.10 DOES (under my conditions)
renumber autoincrement field in UPDATE
statement IF (and only if) autoinc field
occurs in WHERE clause

e.g.
UPDATE "BDE_BUG2.db"
SET Status = 5
WHERE Counter > 2

(Counter type=+)

The prerequisite is that autoinc field constitutes
PRIMARY index of the table.
Indexing on other fields has no effect (unlike
in  Stefan's report)
This seems rather serious bug.

--
Roman
(please remove 'stopspam' in header when replying)
mail: i...@rksolution.cz
URL: www.rksolution.cz
Steve Koterski (Borland) p?e ve zprv
<37ec7b2f.12946...@forums.inprise.com>

Quote
>On Thu, 23 Sep 1999 14:09:21 +0200, "Roman Krejci"
><Kre...@stopspam.mbox.cesnet.cz> wrote:

>>tried it and it behaves
>>as expected - autoinc field
>>DOES NOT change (BDE 5.10)

>There was additional information posted by Karim Amin in another thread on
>the same subject. That post seemed to intimate that the problem only
>manifested itself when there was a secondary index (or indexes) on
>non-autoinc fields and those non-autoinc fields are set in an UPDATE
>statement. I have not yet had a chance to set up a scenario and test this.

>==========================================================================
>Steve Koterski                  "Computers are useless. They can only give
>Technical Publications          you answers."
>Borland                                       -- Pablo Picasso (1881-1973)
>http://www.borland.com/techpubs/delphi

Re:Autoincrement fields starts to increment itself when a Local SQL query is run


Roman Krejci p1e ve zprv <7sgksc$95...@forums.borland.com>.

Quote
>I played around a bit with pdox table and found this:

>I cannot confirm Stefan's effect but
>BDE 5.10 DOES (under my conditions)
>renumber autoincrement field in UPDATE
>statement IF (and only if) autoinc field
>occurs in WHERE clause

>e.g.
>UPDATE "BDE_BUG2.db"
>SET Status = 5
>WHERE Counter > 2

It also appears that this bug manifests itself ONLY if
"<" or ">" operators are used in WHERE clause:
as "WHERE Counter=2"
does behave correctly whereas
"WHERE (Counter>=2) and (Counter<=2)"
does renumber the autoinc

Roman

Re:Autoincrement fields starts to increment itself when a Local SQL query is run


I can reproduce the problem by referencing as string field (that is a
secondary index) in the where clause and changing another string field which
is also a secondary index.
The same query behaves correctly in the database desktop but renumbers the
autoinc key when compiled into a project.

sqlstring: UPDATE RouteSch Set AssignedName = "New Value" WHERE Route =
"NORTH"

I have a sample project that you can look at if it is of any help.

Quote
Roman Krejci <Kre...@stopspam.mbox.cesnet.cz> wrote in message

news:7sgmkr$95j17@forums.borland.com...
Quote
> Roman Krejci p1e ve zprv <7sgksc$95...@forums.borland.com>.
> >I played around a bit with pdox table and found this:

> >I cannot confirm Stefan's effect but
> >BDE 5.10 DOES (under my conditions)
> >renumber autoincrement field in UPDATE
> >statement IF (and only if) autoinc field
> >occurs in WHERE clause

> >e.g.
> >UPDATE "BDE_BUG2.db"
> >SET Status = 5
> >WHERE Counter > 2

> It also appears that this bug manifests itself ONLY if
> "<" or ">" operators are used in WHERE clause:
> as "WHERE Counter=2"
> does behave correctly whereas
> "WHERE (Counter>=2) and (Counter<=2)"
> does renumber the autoinc

> Roman

Re:Autoincrement fields starts to increment itself when a Local SQL query is run


Borland has reproduseed the problem and suggest as a workaround, replace
idsql32.dll with the one from bde5.01

/Stefan

John Jacobs <jo...@GenerationSystems.com> skrev i
diskussionsgruppsmeddelandet:7sgq5a$c...@forums.borland.com...

Quote
> I can reproduce the problem by referencing as string field (that is a
> secondary index) in the where clause and changing another string field
which
> is also a secondary index.
> The same query behaves correctly in the database desktop but renumbers the
> autoinc key when compiled into a project.

> sqlstring: UPDATE RouteSch Set AssignedName = "New Value" WHERE Route =
> "NORTH"

> I have a sample project that you can look at if it is of any help.

> Roman Krejci <Kre...@stopspam.mbox.cesnet.cz> wrote in message
> news:7sgmkr$95j17@forums.borland.com...
> > Roman Krejci p1e ve zprv <7sgksc$95...@forums.borland.com>.
> > >I played around a bit with pdox table and found this:

> > >I cannot confirm Stefan's effect but
> > >BDE 5.10 DOES (under my conditions)
> > >renumber autoincrement field in UPDATE
> > >statement IF (and only if) autoinc field
> > >occurs in WHERE clause

> > >e.g.
> > >UPDATE "BDE_BUG2.db"
> > >SET Status = 5
> > >WHERE Counter > 2

> > It also appears that this bug manifests itself ONLY if
> > "<" or ">" operators are used in WHERE clause:
> > as "WHERE Counter=2"
> > does behave correctly whereas
> > "WHERE (Counter>=2) and (Counter<=2)"
> > does renumber the autoinc

> > Roman

Other Threads