Hi,
using Clover Designer 3.0.0 and MySQL v 5.1
If I have a DB_OUTPUT_TABLE with flows out both ports and auto-generate my insert query by selecting all the columns in a table, the query looks something like the following:
INSERT INTO TABLE1 (COL1, COL2, COL3)
VALUES ($COL1, $COL2, $COL3)
RETURNING $COL1:=COL1, $COL2:=COL2, $COL3:=COL3,
However when I run the graph I get the following error message:
ERROR [WatchDog] - Graph execution finished with error
ERROR [WatchDog] - Node DB_OUTPUT_TABLE1 finished with status: ERROR caused by: COL1 (long) cannot be set to "COL1", value: 'COL1'
ERROR [WatchDog] - Node DB_OUTPUT_TABLE1 error details:
org.jetel.exception.BadDataFormatException: COL1 (long) cannot be set to "COL1", value: 'COL1'
The fix is to change the RETURNING line to read as follows:
RETURNING $COL1:=$COL1, $COL2:=$COL2, $COL3:=$COL3,
(noted change to variables with $'s)
and using AUTO_GENERATED for any auto generated fields (like auto-incremented PKs).
Is this how it's supposed to work, or are we doing something off-kilter?
tia
using Clover Designer 3.0.0 and MySQL v 5.1
If I have a DB_OUTPUT_TABLE with flows out both ports and auto-generate my insert query by selecting all the columns in a table, the query looks something like the following:
INSERT INTO TABLE1 (COL1, COL2, COL3)
VALUES ($COL1, $COL2, $COL3)
RETURNING $COL1:=COL1, $COL2:=COL2, $COL3:=COL3,
However when I run the graph I get the following error message:
ERROR [WatchDog] - Graph execution finished with error
ERROR [WatchDog] - Node DB_OUTPUT_TABLE1 finished with status: ERROR caused by: COL1 (long) cannot be set to "COL1", value: 'COL1'
ERROR [WatchDog] - Node DB_OUTPUT_TABLE1 error details:
org.jetel.exception.BadDataFormatException: COL1 (long) cannot be set to "COL1", value: 'COL1'
The fix is to change the RETURNING line to read as follows:
RETURNING $COL1:=$COL1, $COL2:=$COL2, $COL3:=$COL3,
(noted change to variables with $'s)
and using AUTO_GENERATED for any auto generated fields (like auto-incremented PKs).
Is this how it's supposed to work, or are we doing something off-kilter?
tia
-
Hello,
MySql can return only one field, that is really auto generated (AUTO_INCREMENT) and you have to use auto_generated key word in your RETURNING clause to get this field. The rest of returned fields can be copied from input port. For full information see DBOutputTable#Query or DB Table is Defined.
Please sign in to leave a comment.
Comments 1