Board index » delphi » Repost, strange bug: An SQL insert that fails through Ibx but not Ibconsole

Repost, strange bug: An SQL insert that fails through Ibx but not Ibconsole

I have made no progress on this strange bug.
Since my original post (below) I have changed
from using TIbQuery to a TIbSQL component.
Also I made sure that I was not creating any
superfluous indices on the primary keys after a
hint from Arno Brinkman.

The error still occurs. To summarise: I have a
particular SQL insert that fails through
Ibx but not through IbConsole, this does not
occur under Win98SE.

original post...
--
I am using Win2K, D6 & Ib6.
When I execute a particular SQL Insert via an
TIbQuery component I get a "Violation on primary key"
error.  However if the same command is executed in IbConsole
on the same database there is no error.  Also I did not
have this error when running the app in Win98SE.

All the foreign references on the insert are valid and
I use a sub SELECT to find the appropriate generated Id
where the reference is not known explicitely.

Here is the table definition (I have changed the names
here but the structure is the same...)

CREATE TABLE table1
(
  fld1   INTEGER NOT NULL,
  fld2    TIMESTAMP NOT NULL,
  fld3    SMALLINT NOT NULL,
  fld4     TIMESTAMP,
  fld5     SMALLINT,
  fld6    SMALLINT NOT NULL,
  fld7    INTEGER,
  fld8   CHAR(1) NOT NULL,
  fld9   CHAR(1) NOT NULL,
  fld10   SMALLINT NOT NULL,
  fld11 SMALLINT NOT NULL,
  PRIMARY KEY (fld1, fld2, fld3),
  FOREIGN KEY (fld1) REFERENCES table2 (fld1),
  FOREIGN KEY (fld10) REFERENCES table3 (fld1)
);
CREATE INDEX table1_idx ON table1 (fld1, fld2, fld3);
CREATE INDEX table1_idx2 ON table2 (fld8);

The other two tables involved have fld1 as a primary key,
on table 3 this is generated.

The insert command is

INSERT INTO table1
(fld1, fld2, fld3, fld4, fld5, fld6,  fld8, fld9, fld10,fld11)
VALUES (1053, '3/19/2001', 302,'3/19/2001', 374, 8871, 'L', 'N',
(SELECT fld1 FROM table3 WHERE fld2='SYSDBA'), 0);

The primary key in the error is numbered 175, how can I check
which constraint this is?
Any other ideas as to what is going on with this error would be
appreciated.
--
Thanks,
Daniel.

 

Re:Repost, strange bug: An SQL insert that fails through Ibx but not Ibconsole


Quote
Daniel wrote:

> The primary key in the error is numbered 175, how can I check
> which constraint this is?

You can use IBConsole to check what columns are part of that constraint.  Bring
up the object browser for the table and you will have 3 buttons on the
properties page - one for unique constraints, one for check constraints and one
for foreign constraints (plus indexes and columns but they are not what you are
looking for).  It will be in one of those three lists.

Quote
> Any other ideas as to what is going on with this error would be
> appreciated.

Just an FYI, IBConsole uses nothing but IBX so if it is working in IBC, there is
probably a logic error in your code somewhere if it works in IBC.  You can use
the SQL Monitor to verify what field values are being passed.

Quote
> --
> Thanks,
> Daniel.

--
Jeff Overcash (TeamB)
      (Please do not email me directly unless  asked. Thank You)
This sad little lizard told me that he was a brontosaurus on his mother's
side.  I did not laugh; people who boast of ancestry often have little else
to sustain them.  Humoring them costs nothing and ads to happiness in
a world in which happiness is in short supply.   (RAH)

Re:Repost, strange bug: An SQL insert that fails through Ibx but not Ibconsole


Quote
> Daniel wrote:

> > The primary key in the error is numbered 175, how can I check
> > which constraint this is?

> You can use IBConsole to check what columns are part
> of that constraint.
> Bring
> up the object browser for the table and you will have 3
> buttons on the
> properties page - one for unique constraints, one for check
> constraints and one
> for foreign constraints (plus indexes and columns but they
> are not what you are
> looking for).  It will be in one of those three lists.

OK, I did this and the key that is supposedly preventing the
insert is on another table than the one I am inserting to.
This reference is the primary key field on a table and it is
an integer with no generator.

Quote
> Just an FYI, IBConsole uses nothing but IBX so if it is working in IBC,
there is
> probably a logic error in your code somewhere if it works in IBC.  You can
use
> the SQL Monitor to verify what field values are being passed.

The point is, that having detected the error and reset everything
(I usually drop the database as the insert is a small
part of one large operation) I then re-run and get the IDE to
stop just before the error.  Next I copy what is about to
be passed via the IBX component into the IBC window
and the query is accepted.

I guess this is how most developers get SQL bugs out of their code.
I have probably done this lots of times to find various typos and
other errors in my applications SQL as I am developing it and I have
never seen it  where the command runs in IBC but not in IBX.
The only exception to this rule that I know of, is when the command
is part of a number of commands in a transaction and will not work
on its own.  For this reason I put this problem insert in its own
transaction to elimate this possiblity.

I should also note that the problem insert is the first on the table and
there are hundreds of other SQL commands run successfully through
exactly the same compiled Delphi code before this one is executed.

What I find most wierd is why there was no error when I ran under
Win98SE.

Do you want me to mail you the table definitions (metadata) and the
problematic insert?

Do you have any other ideas?

--
Thanks,
Daniel.

"Jeff Overcash (TeamB)" <jeffoverc...@mindspring.com> wrote in message
news:3C69ABEC.712BBB32@mindspring.com...

- Show quoted text -

Quote

> Daniel wrote:

> > The primary key in the error is numbered 175, how can I check
> > which constraint this is?

> You can use IBConsole to check what columns are part of that constraint.
Bring
> up the object browser for the table and you will have 3 buttons on the
> properties page - one for unique constraints, one for check constraints
and one
> for foreign constraints (plus indexes and columns but they are not what
you are
> looking for).  It will be in one of those three lists.

> > Any other ideas as to what is going on with this error would be
> > appreciated.

> Just an FYI, IBConsole uses nothing but IBX so if it is working in IBC,
there is
> probably a logic error in your code somewhere if it works in IBC.  You can
use
> the SQL Monitor to verify what field values are being passed.

> > --
> > Thanks,
> > Daniel.

> --
> Jeff Overcash (TeamB)
>       (Please do not email me directly unless  asked. Thank You)
> This sad little lizard told me that he was a brontosaurus on his mother's
> side.  I did not laugh; people who boast of ancestry often have little
else
> to sustain them.  Humoring them costs nothing and ads to happiness in
> a world in which happiness is in short supply.   (RAH)

Other Threads