Hi!

I have run into an annoying (but reproducible) problem. At the end of
this post is some DDLs for setting up a test case. I have tried it with
a DB on a Win XP machine (SuperServer 1.5.3).

I get a kind of corrupted table when I run the SP (below) twice without
committing. If I run the SP once, then committ (or committRetaining)
everything is ok. But if I run the SP twice or more and then committ I
get the error upon fetching the affected record. Also; if I fetch the
record that was affected before committing within the same transaction
the error will appear. In this case a rollback will redeem the problem.
The error (if committed) is of a persistent nature - it will be there
tomorrow too (after a server restart).

The error message that I am receiving is

"Unsuccessful execution caused by system error that does not preclude
successful execution of subsequent statements."

335544382: "BLOB not found."

To reproduce; run the SP twice in the same transaction, then commit.
Then "browse" the record in its entirety. One would have to insert a
record or two (I tested with three) of course.

I also note that if I strip down the SP to have two input parameters
(excluding the SP) everything seems to be OK. But at three the problem
appears. I will work around this in my application by writing an SP that
handles one field at a time, also taking an VARCHAR as an argument to
tell it what field to update.

!!! What I would very much appreciate is if someone has an explanation
to or experience with this. Beauty would be if it is possible to have
confirmed that this will *not* happen as long as I use two or less BLOB
parameters. I browsed through lots of SPs in some DBs and realized that
I don't use BLOBs for input parameters very much but I do use it and I
feel rather dependent on it.

If you don't want to read the DDL, here the gist; the table has an ID
and some BLOB columns. The SP takes an ID and the same number of BLOBs
and updates the table row at the ID with the BLOBs. Simple as that.
There are test for NOT NULL before each update because of application
stuff. I include it here just because.

OK, here goes the interesting stuff;

SET SQL DIALECT 3;

SET NAMES WIN1252;

CREATE TABLE PRJ_MAIN_TEST (
PRJ_ID INTEGER NOT NULL,
PRJ_NARRAT_GOALS_SV BLOB SUB_TYPE 1 SEGMENT SIZE 512,
PRJ_NARRAT_GOALS_EN BLOB SUB_TYPE 1 SEGMENT SIZE 512,
PRJ_NARRAT_EXPECTED_CIV_IMPACT BLOB SUB_TYPE 1 SEGMENT SIZE 512,
PRJ_NARRAT_TARGET_GROUP_SV BLOB SUB_TYPE 1 SEGMENT SIZE 512,
PRJ_NARRAT_TARGET_GROUP_EN BLOB SUB_TYPE 1 SEGMENT SIZE 512,
PRJ_NARRAT_SUMMARY_SV BLOB SUB_TYPE 1 SEGMENT SIZE 512,
PRJ_NARRAT_SUMMARY_EN BLOB SUB_TYPE 1 SEGMENT SIZE 512,
PRJ_NARRAT_LOCAL_ORG BLOB SUB_TYPE 1 SEGMENT SIZE 512,
PRJ_NARRAT_CONTEXT_ANALYSIS BLOB SUB_TYPE 1 SEGMENT SIZE 512,
PRJ_NARRAT_OTHER_CONTRIBUTIONS BLOB SUB_TYPE 1 SEGMENT SIZE 512
);

CREATE PROCEDURE PRJ_ADD_TEXTS_TEST(
PRJ_ID INTEGER,
PRJ_NARRAT_GOALS_SV BLOB SUB_TYPE 1 SEGMENT SIZE 80,
PRJ_NARRAT_GOALS_EN BLOB SUB_TYPE 1 SEGMENT SIZE 80,
PRJ_NARRAT_EXPECTED_CIV_IMPACT BLOB SUB_TYPE 1 SEGMENT SIZE 80,
PRJ_NARRAT_TARGET_GROUP_SV BLOB SUB_TYPE 1 SEGMENT SIZE 80,
PRJ_NARRAT_TARGET_GROUP_EN BLOB SUB_TYPE 1 SEGMENT SIZE 80,
PRJ_NARRAT_SUMMARY_SV BLOB SUB_TYPE 1 SEGMENT SIZE 80,
PRJ_NARRAT_SUMMARY_EN BLOB SUB_TYPE 1 SEGMENT SIZE 80,
PRJ_NARRAT_LOCAL_ORG BLOB SUB_TYPE 1 SEGMENT SIZE 80,
PRJ_NARRAT_CONTEXT_ANALYSIS BLOB SUB_TYPE 1 SEGMENT SIZE 80,
PRJ_NARRAT_OTHER_CONTRIBUTIONS BLOB SUB_TYPE 1 SEGMENT SIZE 80)
AS
begin
IF (PRJ_NARRAT_GOALS_SV IS NOT NULL) THEN
UPDATE PRJ_MAIN_TEST PM
SET PM.PRJ_NARRAT_GOALS_SV = :PRJ_NARRAT_GOALS_SV
WHERE PM.PRJ_ID = :PRJ_ID;

IF (PRJ_NARRAT_GOALS_EN IS NOT NULL) THEN
UPDATE PRJ_MAIN_TEST PM
SET PM.PRJ_NARRAT_GOALS_EN = :PRJ_NARRAT_GOALS_EN
WHERE PM.PRJ_ID = :PRJ_ID;

IF (PRJ_NARRAT_EXPECTED_CIV_IMPACT IS NOT NULL) THEN
UPDATE PRJ_MAIN_TEST PM
SET PM.PRJ_NARRAT_EXPECTED_CIV_IMPACT = :PRJ_NARRAT_EXPECTED_CIV_IMPACT
WHERE PM.PRJ_ID = :PRJ_ID;

IF (PRJ_NARRAT_TARGET_GROUP_SV IS NOT NULL) THEN
UPDATE PRJ_MAIN_TEST PM
SET PM.PRJ_NARRAT_TARGET_GROUP_SV = :PRJ_NARRAT_TARGET_GROUP_SV
WHERE PM.PRJ_ID = :PRJ_ID;

IF (PRJ_NARRAT_TARGET_GROUP_EN IS NOT NULL) THEN
UPDATE PRJ_MAIN_TEST PM
SET PM.PRJ_NARRAT_TARGET_GROUP_EN = :PRJ_NARRAT_TARGET_GROUP_EN
WHERE PM.PRJ_ID = :PRJ_ID;

IF (PRJ_NARRAT_SUMMARY_SV IS NOT NULL) THEN
UPDATE PRJ_MAIN_TEST PM
SET PM.PRJ_NARRAT_SUMMARY_SV = :PRJ_NARRAT_SUMMARY_SV
WHERE PM.PRJ_ID = :PRJ_ID;

IF (PRJ_NARRAT_SUMMARY_EN IS NOT NULL) THEN
UPDATE PRJ_MAIN_TEST PM
SET PM.PRJ_NARRAT_SUMMARY_EN = :PRJ_NARRAT_SUMMARY_EN
WHERE PM.PRJ_ID = :PRJ_ID;

IF (PRJ_NARRAT_LOCAL_ORG IS NOT NULL) THEN
UPDATE PRJ_MAIN_TEST PM
SET PM.PRJ_NARRAT_LOCAL_ORG = :PRJ_NARRAT_LOCAL_ORG
WHERE PM.PRJ_ID = :PRJ_ID;

IF (PRJ_NARRAT_CONTEXT_ANALYSIS IS NOT NULL) THEN
UPDATE PRJ_MAIN_TEST PM
SET PM.PRJ_NARRAT_CONTEXT_ANALYSIS = :PRJ_NARRAT_CONTEXT_ANALYSIS
WHERE PM.PRJ_ID = :PRJ_ID;

IF (PRJ_NARRAT_OTHER_CONTRIBUTIONS IS NOT NULL) THEN
UPDATE PRJ_MAIN_TEST PM
SET PM.PRJ_NARRAT_OTHER_CONTRIBUTIONS = :PRJ_NARRAT_OTHER_CONTRIBUTIONS
WHERE PM.PRJ_ID = :PRJ_ID;
end

And that's is!

Regards and thanks for any input,

/Dany