At 10:41 AM 31/01/2004 +0000, you wrote:

>Here it is.
>
>IBExpert tells me I am using gds32.dll, the file version is 6.3.0.4027.
>Product version 1.5.0.4027

OK. I needed to eliminate the possibility that you were using a client
version that had the XSQLDA bug.

Now - the causes of your problems.

I will start by explaining to you that the Insert method of a dataset does
*not* do anything to the data on the server. It opens an empty row
structure which, in your case, represents all of the input parameters of
your executable stored procedure. (In a regular dataset, based on a live
query, it would open an empty row structure representing all of the fields
in your SELECT query).

So, effectively what your code does is the following:

1. Tries to change the input parameters of the SELECT stored procedure
2. Then, after that, calls Insert. This creates the empty structure of
input fields for your EXECUTABLE procedure, so all you get is execptions.
3. After that, you call Post, which tries to execute the SP in the
InsertSQL. It tries to get the input parameter values for it from the new
row structure, but of course it is full of empty strings or nulls, as the
case may be, because you just called Insert. Meanwhile, the current row of
the SELECT procedure is in a fine old mess.

OK, so this part is wrong. Let's correct that:

procedure TDataModule2.waProjectExplorerNewProject(Sender: TWebTreeview;
value1, value2: String);
begin
with IBO_QRY_GET_MY_PROJECTS do begin
if not prepared then prepare;

Insert; // c a l l I N S E R T h e r e !!!

// Now, because you are loading a row of fields here, these are not
Params but Fields:

// NO. ParamByName('wwwROW_ID').Value := '-1'; // dummy we are doing
an INSERT
FieldByName('wwwCON_ID').Value := Literal['CST_CON_ID'];
FieldByName('wwwDESC_TEXT').Value := value1;
FieldByName('wwwProjectType').Value := Literal['sbProjectType'];
try
tWebTreeview(sender).treeview.SaveFileToBlob(a0,
TIB_ColumnBlob(FieldByName('wwwCAD')));
except
// RVR 07JAN2003 exception handling!
end;
if not assigned(FieldByName('wwwCAD')) then
FieldByName('wwwCAD').Clear; //ensures empty blob is null
try

tWebTreeview(sender).treeview.SaveTreeviewToBlob(tWebTreeview(sender).treevi
ew, TIB_ColumnBlob(FieldByName('wwwProject')));
except
// RVR 07JAN2003 exception handling!
end;
if not assigned(ParamByName('wwwProject')) then
FieldByName('wwwProject').Clear; //ensures empty blob is null


You have a field missing here: what about wwwProjectType?

// NO !!! IBO_QRY_GET_MY_PROJECTS.Insert;

// Now (when the SP is fixed) we are ready to post:
try
Post;
except
// some exception handling here
end;
IB_Transaction.Commit;

Now, the next problem is your executable SP. I see that you tried to mimic
my logic but unfortunately you didn't quite cover the situation.

Here are your input fields for PROCEDURE INS_OR_UPD_OR_DEL_E_PROJECT:

WWWDEL CHAR(1),
WWWROW_ID INTEGER,
WWWCON_ID INTEGER,
WWWDESC_TEXT VARCHAR(255),
WWWCAD BLOB SUB_TYPE 0 SEGMENT SIZE 80,
WWWPROJECT BLOB SUB_TYPE 0 SEGMENT SIZE 80,
WWWPROJECTTYPE VARCHAR(30))

Here are your input parameters for the execute call in your InsertSQL method:

EXECUTE PROCEDURE INS_OR_UPD_OR_DEL_E_PROJECT(
'N',
:WWWROW_ID,
:WWWCON_ID,
:WWWDESC_TEXT,
:WWWCAD,
:WWWPROJECT,
:WWWPROJECTTYPE)

As I've already pointed out, your parameter assignment are missing
wwwProjectType, so fix that. And, when calling this in InsertSQL, use a
constant for wwwRow_ID, not a placeholder, and put -1 there.

Next, when you are calling this INS_OR_UPD_OR_DEL_E_PROJECT, the purpose of
the Del flag is to indicate 'Y' if it is a delete, 'N' (or any other single
byte except 'Y') if not a delete. And so you did that in your
InsertSQL. In fact, putting -1 into the wwwRow_ID is the thing that is
meant to tell the SP whether it is an update or an insert.

Now, the logic of your SP is all wrong. I'll copy it below and then give
you a new version with the logic corrected.

Wrong logic for InsertSQL:
EXECUTE PROCEDURE INS_OR_UPD_OR_DEL_E_PROJECT('N', :WWWROW_ID, :WWWCON_ID,
:WWWDESC_TEXT, :WWWCAD, :WWWPROJECT, :WWWPROJECTTYPE)

Wrong logic:

CREATE PROCEDURE INS_OR_UPD_OR_DEL_E_PROJECT(
WWWDEL CHAR(1),
WWWROW_ID INTEGER,
WWWCON_ID INTEGER,
WWWDESC_TEXT VARCHAR(255),
WWWCAD BLOB SUB_TYPE 0 SEGMENT SIZE 80,
WWWPROJECT BLOB SUB_TYPE 0 SEGMENT SIZE 80,
WWWPROJECTTYPE VARCHAR(30))
RETURNS (
O_ROW_ID INTEGER)
AS
DECLARE VARIABLE ID INTEGER;
BEGIN
IF (:WWWDEL = 'Y') THEN -- really an UPDATE
UPDATE E_PROJECT
SET ACTIVE_FLG = 'N'
WHERE ROW_ID = :wwwROW_ID;
ELSE
BEGIN -- INSERT
IF (EXISTS (
SELECT 1 FROM E_PROJECT
WHERE ROW_ID = :wwwROW_ID)) THEN
UPDATE E_PROJECT
SET PROJECT = :wwwPROJECT
WHERE ROW_ID = :wwwROW_ID;
ELSE
IF (wwwROW_ID IS NULL) THEN BEGIN
id = GEN_ID(GEN_E_PROJECT_ROW_ID, 1);

INSERT INTO E_PROJECT (CREATED_BY, LAST_UPD_BY, CST_CON_ID,
DESC_TEXT, CAD, PROJECT, PROJECTTYPE)
VALUES (:wwwCON_ID, :wwwCON_ID, :wwwCON_ID, :wwwDESC_TEXT, :wwwCAD,
:wwwPROJECT, :wwwPROJECTTYPE);
end
ELSE
INSERT INTO E_PROJECT (ROW_ID, CREATED_BY, LAST_UPD_BY, CST_CON_ID,
DESC_TEXT, CAD, PROJECT, PROJECTTYPE)
VALUES (:id, :wwwCON_ID, :wwwCON_ID, :wwwCON_ID, :wwwDESC_TEXT, :wwwCAD,
:wwwPROJECT, :wwwPROJECTTYPE);
O_ROW_ID = id;
END
END

InsertSQL with corrected logic:

EXECUTE PROCEDURE INS_OR_UPD_OR_DEL_E_PROJECT('N', -1, :WWWCON_ID,
:WWWDESC_TEXT, :WWWCAD, :WWWPROJECT, :WWWPROJECTTYPE)

B U T see my comments 'way down below about this new row_id....

Corrected logic for SP:

CREATE PROCEDURE INS_OR_UPD_OR_DEL_E_PROJECT(
WWWDEL CHAR(1),
WWWROW_ID INTEGER,
WWWCON_ID INTEGER,
WWWDESC_TEXT VARCHAR(255),
WWWCAD BLOB SUB_TYPE 0 SEGMENT SIZE 80,
WWWPROJECT BLOB SUB_TYPE 0 SEGMENT SIZE 80,
WWWPROJECTTYPE VARCHAR(30))

/* RETURNS (O_ROW_ID INTEGER) - no, can't do this when it's tied to a
dataset */
AS
/* DECLARE VARIABLE ID INTEGER; not needed */
BEGIN
IF (:WWWDEL = 'Y') THEN /* it's a DELETE */
DELETE FROM E_PROJECT
WHERE ROW_ID = :wwwROW_ID;
ELSE
BEGIN /* test whether row already exists */
IF (EXISTS (
SELECT 1 FROM E_PROJECT
WHERE ROW_ID = :wwwROW_ID)) THEN
UPDATE E_PROJECT
SET PROJECT = :wwwPROJECT
WHERE ROW_ID = :wwwROW_ID;
END
ELSE /* it's an update */
BEGIN
/* no existing row so get a new row_id */
wwwROW_ID = GEN_ID(GEN_E_PROJECT_ROW_ID, 1);

INSERT INTO E_PROJECT (ROW_ID, CREATED_BY, LAST_UPD_BY, CST_CON_ID,
DESC_TEXT, CAD, PROJECT, PROJECTTYPE)
VALUES (:wwwROW_ID, :wwwCON_ID, :wwwCON_ID, :wwwCON_ID, :wwwDESC_TEXT,
:wwwCAD,
:wwwPROJECT, :wwwPROJECTTYPE);

END
/* O_ROW_ID = id; won't work */
END

Now, it looks to me as if you were trying to bend around the possibility
that something other than -1 would come through on an insert. IMO, it
should, and then you will have your new row_id in the application *before*
you perform the insert.

Look at my demo code and copy the AfterInsert procedure that calls the
IBODatabase.Gen_ID( ) method. Use that and don't load -1 (as I have it
above) and don't assign the parameter yourself at all. Then, if you want
to locate the new record when you go back and close and reopen the
selectable SP, then store the value as a variable that you can get at after
the InsertSQL has executed.

Oh, and why you can't read a return parameter from an executable SP if it's
in an xxxxSQL property...the fields and params properties in this setup are
somewhat intertwined. You *can* read a return structure back from an
executable SP when it's run independently in its own accessible statement
object. But anyway, this isn't a cool way to get this value, as I've
already explained.

Helen