SQL error -3102 = Invalid subtrans structure

[stefangustafsson]

Hello,

I get a strange error updating through a view if the underlying table contains a blob.

I'm using 7.7.06.09 (WIN32)

To reproduce I can run the following script using loadercli

-

---

error.sql----

---

CREATE TABLE FOO

(

"A" Fixed (10,0) NOT NULL,

"B" Fixed (21,11),

CONSTRAINT SYSPRIMARYKEY PRIMARY KEY("A")

)

//

CREATE TABLE BAR

(

"A" Fixed (10,0) NOT NULL,

"C" Blob,

CONSTRAINT SYSPRIMARYKEY PRIMARY KEY("A")

)

//

ALTER TABLE "BAR" FOREIGN KEY "AA" ("A") REFERENCES "FOO" ("A") ON DELETE CASCADE

//

CREATE VIEW "KILLROY" AS SELECT FOO.A,FOO.B,BAR.C FROM BAR,FOO WHERE FOO.A = BAR.A AND BAR.A = FOO.A WITH CHECK OPTION

//

INSERT INTO "KILLROY" SET A = 1, B = 1

//

INSERT INTO "KILLROY" SET A = 2, B = 2

//

UPDATE "KILLROY" SET B = 10 WHERE A = 1

-

---

end of script----

---

Is there anything I can do to prevent this error from happening?

Best Regards,

Stefan Gustafsson

[Former Member · Accepted solution]

Hallo,

unfortunately I have to tell you bad news:

No, there is no way to prevent this error from occuring if you want to use this updateable joinview.

Unfortunately you've found a real bug. Thank you for this.

As I assume that your 2 tables posted are only short examples and not your real world, you will have some trouble to handle what you want. (If this was your real world I would ask for the reason to separate the LOB-value from the rest of the columns...)

If you did not have two tables in your view which you want to use for your updates, problems did not occur.

If you did the update on the corresponding basetable, it would do, too (and would not do things different to the wanted ones within this small example).

If your view did not include the LOB-column, you could use this more-than-one-table-containing-view for update.

Sorry, but as these updateable joinviews do have no priority for our main customer (our own company) I am not able to promise a bug-fix within a foreseeable timeframe or for one special release-number.

I hope you will find a way to overcome the trouble.

Elke

[stefangustafsson · Accepted solution]

Thank you for the reply.

We have an application using an ODMG OR mapping using a 7.3 instance today. We are now in the process of moving to 7.7 and have found this problem then updating objects that have BLOB attributes.

I will change the object_write code in the ODMG layer to write the different tables one by one instead to avoid this problem. The cost will be an increased number of SQL statements but it's not that bad since it's in the write case and we have far more reads than writes. And we can still read the objects through the view.

Best Regards,

Stefan Gustafsson

Expisoft AB, Sweden

[Former Member]

BTW:

WHERE FOO.A = BAR.A AND BAR.A = FOO.A

is not needed.

WHERE FOO.A = BAR.A

or the other search-condition

WHERE BAR.A = FOO.A

would be enough

But perhaps, this is only a copy-problem from your real world.

Elke