About Transaction SQL++ in Query Workbench

SQL++
1

“I am using ‘SQL++’ in Query Workbench to execute transactions. However, I am having trouble pinpointing the issue because using a regular UPDATE statement to modify data works fine, but encountering problems when incorporating transactions.”

This is not a problem

UPDATE `Analytics`.`Commerce`.`customers` AS cu 
USE KEYS "00000324-ecf0-43a9-814e-63c4ab057ce2"
SET cu.name ="kevin",cu.address.zipcode=8888
RETURNING  VALUE cu

However

BEGIN TRANSACTION;
UPDATE `Analytics`.`Commerce`.`customers` AS cu 
USE KEYS "00000324-ecf0-43a9-814e-63c4ab057ce2"
SET cu.name ="kevin",cu.address.zipcode=8888
RETURNING  VALUE cu
COMMIT;

ERROR Msg:

_sequence_num _sequence_query _sequence_query_status _sequence_result
1 BEGIN TRANSACTION; success [{txid:e41b66c9-9538-4e7c-98cb-f813adf4df86}]
2 \nUPDATE Analytics.Commerce.customers AS cu \nUSE KEYS \00000324-ecf0-43a9-814e-63c4ab057ce2\nSET cu.name =\kevin,cu.address.zipcode=8888\nRETURNING VALUE cu\nCOMMIT; Bad Request [{code:3000,msg:syntax error - line 6, column 1, at: COMMIT (reserved word),query_from_user:explain \nUPDATE Analytics.Commerce.customers AS cu \nUSE KEYS \00000324-ecf0-43a9-814e-63c4ab057ce2\nSET cu.name =\kevin,cu.address.zipcode=8888\nRETURNING VALUE cu\nCOMMIT;}]
2

Remove begin/commit statements and execute as transaction (which adds those and link all statements as same tranasction)

1 Like
3

I’m not sure why? Still receiving errors.

START TRANSACTION;
UPDATE customers as cu 
USE KEYS ='00000324-ecf0-43a9-814e-63c4ab057ce2'
SET cu.name ="Bowen"
_sequence_num _sequence_query _sequence_query_status _sequence_result
1 START TRANSACTION; success [{txid:41f1e2ec-56dd-4be2-8527-b9f4c50c4ec4}]
2 \nUPDATE customers as cu \nUSE KEYS =‘00000324-ecf0-43a9-814e-63c4ab057ce2’\nSET cu.name =\Bowen|Bad Request [{code:3000,msg:syntax error - line 3, column 10, near ‘USE KEYS’, at: =,query_from_user:explain \nUPDATE customers as cu \nUSE KEYS =‘00000324-ecf0-43a9-814e-63c4ab057ce2’\nSET cu.name =\Bowen}]
4

The example below was successful; however, for some reason, the data inside the document did not get updated.

BEGIN TRANSACTION;
UPDATE `Analytics`.`Commerce`.`customers` AS cu 
USE KEYS "00000324-ecf0-43a9-814e-63c4ab057ce2"
SET cu.name ="Bowen",cu.address.zipcode=8888
RETURNING cu
_sequence_num _sequence_query _sequence_query_status _sequence_result
1 BEGIN TRANSACTION; success [{txid:81827377-e569-46ab-bf3d-2d56a80f50ce}]
2 \nUPDATE Analytics.Commerce.customers AS cu \nUSE KEYS \00000324-ecf0-43a9-814e-63c4ab057ce2\nSET cu.name =\Bowen,cu.address.zipcode=8888\nRETURNING cu success [{cu:{address:{city:St. Louis, MO,street:201 Main St.,zipcode:8888},custid:C13,name:Bowen,rating:481}}]
5

What I understand from @vsr1’s instructions is that you should not use any transaction DML (i.e. Begin transaction/start transaction/commit). Just put your select/update etc. and use the Run-as-Tx button.

2 Likes
6

@Bowen Are you running the query using the on-prem UI or the Capella UI? Right now, a back-end bug is preventing transactions from running properly in the Capella UI.

7

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.