Issue with VERCOLS and external tables
User encountered errors when inserting data from a VERCOLS table into an external table created with SAMEAS. SELECT * failed because VERCOLS columns (ifx_insert_checksum, ifx_row_version) weren't included; explicitly listing them also failed. Workaround found: explicitly name all columns including VERCOLS in both INSERT and SELECT clauses. Issue appears to be a bug with hidden columns in external tables; PMR recommended for investigation.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 11.50.FC5 (I know, near EOL)
I have some tables that are defined WITH VERCOLS. If I do:
CREATE EXTERNAL TABLE my_external_table SAMEAS my_table ...
the external table includes the ifx_insert_checksum and ifx_row_version
columns. But when I do:
INSERT INTO my_external_table
SELECT *
FROM my_table;
I get -26190 Insert into an external table must provide values for all columns
in the table. That's to be expected, since the SELECT * ignores these columns.
But then I do:
INSERT INTO my_external_table
SELECT col_a, col_b, col_c, ..., ifx_insert_checksum, ifx_row_version
FROM my_table;
and I get -236 Number of columns in INSERT does not match number of VALUES.
Just for grins, I tried:
INSERT INTO my_external_table
SELECT *, ifx_insert_checksum, ifx_row_version
FROM my_table;
but I still get the -236.
Any idea how to get the data from the table into the external table?
Create the external table definition explicitly defining the columns
instead of using SAMEAS
But that sounds like a bug... The external table gets created with 12
columns, but the last two have speciall attributes which I think don't
allow them to be used in SQL... that's probably from where the error comes.
But someone from support/development needs to look into this. Please open a
PMR.
Regards
On Fri, Mar 16, 2018 at 9:16 PM, MARK COLLINS <markc@myfastmail.com> wrote:
> IDS 11.50.FC5 (I know, near EOL)
>
> I have some tables that are defined WITH VERCOLS. If I do:
>
> CREATE EXTERNAL TABLE my_external_table SAMEAS my_table ...
>
> the external table includes the ifx_insert_checksum and ifx_row_version
> columns. But when I do:
>
> INSERT INTO my_external_table
> SELECT *
> FROM my_table;>
> I get -26190 Insert into an external table must provide values for all
> columns
> in the table. That's to be expected, since the SELECT * ignores these
> columns.
>
> But then I do:
>
> INSERT INTO my_external_table
> SELECT col_a, col_b, col_c, ..., ifx_insert_checksum, ifx_row_version
> FROM my_table;>
> and I get -236 Number of columns in INSERT does not match number of VALUES.
>
> Just for grins, I tried:
>
> INSERT INTO my_external_table
> SELECT *, ifx_insert_checksum, ifx_row_version
> FROM my_table;>
> but I still get the -236.
>
> Any idea how to get the data from the table into the external table?
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
Fernando,
Thanks for the reply.
I considered doing an explicit definition for the EXTERNAL TABLE, but I didn't
want to go through the effort of decoding colength/coltype from syscolumns if
I didn't have to. I've written a function that handles that, but I'm not 100%
confident that it is completely correct.
Anyway, I did find a way around this shortly after I posted the original
message. If I do:
INSERT INTO my_external_table (col_a, col_b, ... ifx_insert_checksum,
ifx_row_version)
SELECT col_a, col_b, ifx_insert_checksum, ifx_row_version
FROM my_table;
it works as desired. This way, I can use just the column names without having
to worry about the column definitions.
I did go look at syscolumns for the EXTERNAL TABLE and found that it marks the
colattr column for those two VERCOLS columns with 5 and 3, so they are
"hidden". But if they're hidden in the normal table and they're also hidden in
the EXTERNAL TABLE, it seems that the:
INSERT INTO my_external_table SELECT * FROM my_table;
format should work. I would expect both the INSERT and the SELECT to ignore
the hidden columns.
If this version wasn't six weeks from end-of-life, I would submit a PMR. I
hope to test this in 11.70.FC9 soon, and if it shows the same behavior, I will
open a PMR at that time.
>> Create the external table definition explicitly defining the columns
>> instead of using SAMEAS
>> But that sounds like a bug... The external table gets created with 12
>> columns, but the last two have speciall attributes which I think don't
>> allow them to be used in SQL... that's probably from where the error
>> comes.
>> But someone from support/development needs to look into this. Please
>> open a PMR.
My tests were on 12.10 ;)
On Mon, 19 Mar 2018 at 14:33, MARK COLLINS <markc@myfastmail.com> wrote:
> Fernando,
>
> Thanks for the reply.
>
> I considered doing an explicit definition for the EXTERNAL TABLE, but I
> didn't
> want to go through the effort of decoding colength/coltype from syscolumns
> if
> I didn't have to. I've written a function that handles that, but I'm not
> 100%
> confident that it is completely correct.
>
> Anyway, I did find a way around this shortly after I posted the original
> message. If I do:
>
> INSERT INTO my_external_table (col_a, col_b, ... ifx_insert_checksum,
> ifx_row_version)
> SELECT col_a, col_b, ifx_insert_checksum, ifx_row_version
> FROM my_table;>
> it works as desired. This way, I can use just the column names without
> having
> to worry about the column definitions.
>
> I did go look at syscolumns for the EXTERNAL TABLE and found that it marks
> the
> colattr column for those two VERCOLS columns with 5 and 3, so they are
> "hidden". But if they're hidden in the normal table and they're also
> hidden in
> the EXTERNAL TABLE, it seems that the:
>
> INSERT INTO my_external_table SELECT * FROM my_table;>
> format should work. I would expect both the INSERT and the SELECT to ignore
> the hidden columns.
>
> If this version wasn't six weeks from end-of-life, I would submit a PMR. I
> hope to test this in 11.70.FC9 soon, and if it shows the same behavior, I
> will
> open a PMR at that time.
>
> >> Create the external table definition explicitly defining the columns
> >> instead of using SAMEAS
> >> But that sounds like a bug... The external table gets created with 12
> >> columns, but the last two have speciall attributes which I think don't
> >> allow them to be used in SQL... that's probably from where the error
> >> comes.
> >> But someone from support/development needs to look into this. Please
> >> open a PMR.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> --
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
Sounds like a PMR is in order. >> My tests were on 12.10 ;)
Thank you On Mar 19, 2018 2:49 PM, "MARK COLLINS" <markc@myfastmail.com> wrote: > Sounds like a PMR is in order. > > >> My tests were on 12.10 ;) > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >