Hi Mike,
afaik you can't INSERT INTO <external_table> VALUES (...) -> -26191
-26191 Incorrect use of an external table tablename in query.
This statement describes an operation that is not allowed on an external
table. The types of operations allowed for external tables are
CREATE EXTERNAL TABLE, SELECT, DROP TABLE, GRANT (only SELECT and INSERT),
REVOKE, CREATE SYNONYM, CREATE SCHEMA, RENAME COLUMN, RENAME TABLE,
SELECT ... INTO EXTERNAL.
The types of operations not allowed for external tables are
TRUNCATE, UPDATE STATISTICS, START VIOLATION, STOP VIOLATION, ALTER TABLE,
UPDATE, DELETE, CREATE TRIGGER, CREATE INDEX, CONNECT BY, INSERT INTO ...
VALUES, LBAC operations, target tables of MERGE statements and template
table for SAMEAS clause of CREATE EXTERNAL TABLE.
Moreover, every new insert, e.g. from an INSERT INTO <external_table> SELECT FROM .... would implicitly wipe out pre-existing external table (i.e. file) content.
If the operation had to occur synchronously (still not 'within same transaction'), maybe CDC, PushData or PostCommit Triggers would be options... with some programming required, but there's demo code (in $INFORMIXDIR/demo) that could easily be adjusted for this purpose.
Or an INSERT trigger with a simply SPL UDR (or something more fancy non-SPL), which would run within same transaction, but also at the cost of INSERT performance.
HTH,
Andreas
------------------------------
Andreas Legner
------------------------------
Original Message:
Sent: Mon January 30, 2023 09:35 AM
From: Mike Walker
Subject: INSERT data to a table and file both
If the table you are inserting into was an external table built on a file, then you would have what you want - a file that can also be used as a table. I expect though that this would have too many restrictions to make it a viable option.
However, you can create an external table on a file, then add an insert trigger on the (real) table so that every insert to the table also automatically performs an insert into the external table which writes to the underlying file.
------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com
Original Message:
Sent: Sat January 28, 2023 02:07 AM
From: Indika Jinadasa
Subject: INSERT data to a table and file both
Dear Friends,
We need to INSERT data to a table and the same time we need the same data to write to a file.
We cannot do this activity from the application end since there are different types of applications doing the INSERT operation.
So, we have to run this activity from DB end only.
Thanks !
------------------------------
Indika Jinadasa
------------------------------