Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 7 of 7 total
Thread Editing Master, Detail Tables in One Transaction
Thu, May 14 2020 5:47 PMPermanent Link

Richard Harding

Wise Nutrition Coaching

Tim

I wish to edit master-detail tables in one transaction. A Brand may have many Models. For example, Caterpillar brand has many models such as D7, D8 and D9.

Auto Transaction are OFF. When the edit form is opened, the Transaction is started. Click the OK button, then the transaction is committed. Click the Cancel button and the transaction is rolled back.

Inserting Rows
===========

Add Rows D12 & D13

{ "Rows":
[{ "RowSet": 14, "EWB_RowID": 28, "Row": { "ID": 34, "Brand": "CAT", "Model": "D12" } }
{ "RowSet": 14, "EWB_RowID": 29, "Row": { "ID": 35, "Brand": "CAT", "Model": "D13" } }] }

Response: Expected “]”, instead found { at 97
This relates to the { at the beginning of the second rowset.
Models are inserted into to the table

Deleting Rows
===========

Delete D22

{ "Operations":
[{ "DataSet": "esModels2",
"RowSet": 14, "Operation": 3, "BeforeRow": { "EWB_RowID": 17, "ID": 36 }, "AfterRow": null },
{ "DataSet": "esModels2",
"RowSet": 14, "Operation": 3, "BeforeRow": { "EWB_RowID": 18, "ID": 31 }, "AfterRow": null }] }

Response: Database commit  response error (Cannot find the row for deleting in the esModels2 datasetSmile

Edit Rows
========

Edit D3, D4, D5

{ "Operations":
[{ "DataSet": "esModels2", "RowSet": 14, "Operation": 2, "BeforeRow": { "EWB_RowID": 6, "ID": 31 }, "AfterRow": { "EWB_RowID": 6, "ID": 31, "Brand": "CAT", "Model": "D3a" } },
{ "DataSet": "esModels2", "RowSet": 14, "Operation": 2, "BeforeRow": { "EWB_RowID": 7, "ID": 23 }, "AfterRow": { "EWB_RowID": 7, "ID": 23, "Brand": "CAT", "Model": "D4b" } },
{ "DataSet": "esModels2", "RowSet": 14, "Operation": 2, "BeforeRow": { "EWB_RowID": 8, "ID": 24 }, "AfterRow": { "EWB_RowID": 8, "ID": 24, "Brand": "CAT", "Model": "D5c" } }] }

Response: Expected “]”, instead found { at 56
Table updated correctly

***************************************

Select Statement
=============
SELECT *
 FROM "Models"
 WHERE Brand = :Brand
 ORDER BY Brand, Model

Update Statement

UPDATE "Models" SET
"ID" = :ID,
"Brand" = :Brand,
"Model" = :Model
WHERE "ID" = :Old_ID

Delete Statement

DELETE FROM "Models"
WHERE "ID" = :Old_ID

The key for the column "ID" is set for true for esBrands2 and esModels2.

Is that all you need?

Richard
Tue, May 19 2020 12:06 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Richard,

<< Inserting Rows
===========

Add Rows D12 & D13

{ "Rows":
[{ "RowSet": 14, "EWB_RowID": 28, "Row": { "ID": 34, "Brand": "CAT", "Model": "D12" } }
{ "RowSet": 14, "EWB_RowID": 29, "Row": { "ID": 35, "Brand": "CAT", "Model": "D13" } }] }

Response: Expected “]”, instead found { at 97
This relates to the { at the beginning of the second rowset. >>

This is fixed, it was related to the echoing of the insert/update rows back to the client.

<< Deleting Rows
===========

Delete D22

{ "Operations":
[{ "DataSet": "esModels2",
"RowSet": 14, "Operation": 3, "BeforeRow": { "EWB_RowID": 17, "ID": 36 }, "AfterRow": null },
{ "DataSet": "esModels2",
"RowSet": 14, "Operation": 3, "BeforeRow": { "EWB_RowID": 18, "ID": 31 }, "AfterRow": null }] }

Response: Database commit  response error (Cannot find the row for deleting in the esModels2 datasetSmile>>

I can't replicate this - I can delete multiple rows in a transaction without issue.  Is this reproducible on your end ?

<< Edit Rows
========

Edit D3, D4, D5

{ "Operations":
[{ "DataSet": "esModels2", "RowSet": 14, "Operation": 2, "BeforeRow": { "EWB_RowID": 6, "ID": 31 }, "AfterRow": { "EWB_RowID": 6, "ID": 31, "Brand": "CAT", "Model": "D3a" } },
{ "DataSet": "esModels2", "RowSet": 14, "Operation": 2, "BeforeRow": { "EWB_RowID": 7, "ID": 23 }, "AfterRow": { "EWB_RowID": 7, "ID": 23, "Brand": "CAT", "Model": "D4b" } },
{ "DataSet": "esModels2", "RowSet": 14, "Operation": 2, "BeforeRow": { "EWB_RowID": 8, "ID": 24 }, "AfterRow": { "EWB_RowID": 8, "ID": 24, "Brand": "CAT", "Model": "D5c" } }] }

Response: Expected “]”, instead found { at 56 >>

This is the same issue as the first with the inserts.

Tim Young
Elevate Software
www.elevatesoft.com
Wed, May 20 2020 7:13 AMPermanent Link

Richard Harding

Wise Nutrition Coaching

Tim,

With the Delete, I am attempting to delete a single Model "D22" which belongs to the Brand "CAT".

I can delete a single and multiple Brands in the one transaction.

I am using two DBnavigators to perform the DB operations - one for Brands and one for Models. The OnButtonClick events look the same for both. I have posted the source files and the DB backup to the binary newsgroup.


CREATE TABLE "Brands"
(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"Brand" VARCHAR(25) COLLATE "ANSI_CI" DEFAULT '<Unknown>' NOT NULL,
CONSTRAINT "brPrimaryIND" PRIMARY KEY ("ID"),
CONSTRAINT "brNameUNQ" UNIQUE ("Brand")
)

CREATE TABLE "Models"
(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"Brand" VARCHAR(25) COLLATE "ANSI_CI" DEFAULT '<Unknown>' NOT NULL,
"Model" VARCHAR(10) COLLATE "ANSI_CI" DEFAULT '<Unknown>' NOT NULL,
CONSTRAINT "mdPrimaryIND" PRIMARY KEY ("ID"),
CONSTRAINT "mdBrandNameFK" FOREIGN KEY ("Brand") REFERENCES "Brands" ("Brand")
  ON UPDATE NO ACTION ON DELETE NO ACTION
)


Partial Content of Brands
---------------------------------
"ID","Brand"
1,"BERCO"
2,"CAT"
3,"KOM OEM"

Partial Content of Models
---------------------------------
"ID","Brand","Model"
25,"CAT","D2"
31,"CAT","D3a"
32,"CAT","D6"
33,"CAT","D7"
34,"CAT","D12"
35,"CAT","D13"
36,"CAT","D22"


Delete Row D22
=============

{ "Operations":
[{ "DataSet": "esModels2",
"RowSet": 14, "Operation": 3, "BeforeRow": { "EWB_RowID": 17, "ID": 36 }, "AfterRow": null },
{ "DataSet": "esModels2",
"RowSet": 14, "Operation": 3, "BeforeRow": { "EWB_RowID": 18, "ID": 31 }, "AfterRow": null }] }

Response: Database commit  response error (Cannot find the row for deleting in the esModels2 dataset


Richard
Sat, May 30 2020 6:04 AMPermanent Link

Richard Harding

Wise Nutrition Coaching

Tim,

Below are the results of Insert, Update and Delete with version 14.

INSERT
======

{ "Operations":
[{ "DataSet": "esModels2", "RowSet": 14, "Operation": 1,
"BeforeRow": null,
"AfterRow": { "EWB_RowID": 14, "ID": null, "Brand": "CAT", "Model": "A111" } }] }

Returned error: Expected "EWB_RowID" instead found "EWBRowID" at 27
Row was inserted


UPDATE
======

{ "Operations":
[{ "DataSet": "esModels2", "RowSet": 14, "Operation": 2,
"BeforeRow": { "EWB_RowID": 1, "ID": 42, "Brand": "CAT", "Model": "A111" },
"AfterRow": { "EWB_RowID": 1, "ID": 42, "Brand": "CAT", "Model": "A1112" } }] }

Returned error: Expected "EWB_RowID" instead found "EWBRowID" at 27
Row was updated

DELETE
======

{ "Operations":
[{ "DataSet": "esModels2", "RowSet": 14, "Operation": 3,
"BeforeRow": { "EWB_RowID": 1, "ID": 41, "Brand": "CAT", "Model": "A121" },
"AfterRow": null }] }

Returned error: Database commit response error (No rows affected by the Delete command execution for the esModels2 dataset)

Different operations are shown above compared to the operations shown in version 13.

Richard
Sat, May 30 2020 9:54 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Richard,

<< Below are the results of Insert, Update and Delete with version 14. >>

You need to recompile your client applications with build 14.  The EWBRowID identifier changed, among other things:

"Just to clarify: there are no changes required for client applications, but you will need to recompile any client applications so that they pick up the new client-side code for the database API.  All of the changes mentioned above are either in the IDE, the web server, or both."

Tim Young
Elevate Software
www.elevatesoft.com
Sat, May 30 2020 9:58 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Richard,

Ignore my last post, I managed to change the row ID name in one place, but not the other.

Tim Young
Elevate Software
www.elevatesoft.com
Sun, Jun 14 2020 7:36 PMPermanent Link

Richard Harding

Wise Nutrition Coaching

Tim,

I have installed EWB3 b15.

Back to editing my CATERPILLAR dozer models. INSERTs and UPDATEs are now working. There is no change with the DELETEs.

The same error occurs attempting to delete a single model or multiple models.

Below is the operations generated when deleting models "A111"  and "D1".

{ "Operations":
[{ "DataSet": "esModels2", "RowSet": 14,
"Operation": 3,
"BeforeRow": { "EWBRowID": 37, "ID": 46, "Brand": "CAT", "Model": "A111" },
"AfterRow": null },
{ "DataSet": "esModels2", "RowSet": 14,
"Operation": 3,
"BeforeRow": { "EWBRowID": 38, "ID": 44, "Brand": "CAT", "Model": "D1" },
"AfterRow": null }] }

This returns a 500 Error: "No rows affected by the Delete command execution for the esModels2 dataset".

"Models" table contains the following.

"ID","Brand","Model"
25,"CAT","D2"
32,"CAT","D6"
33,"CAT","D7"
43,"CAT","D10"
44,"CAT","D1"
45,"CAT","D2"
46,"CAT","A111"

The DELETE SQL for esModels2 is

DELETE FROM "Models"
WHERE "ID" = :Old_ID

Richard
Image