Problem add BSON column in a table
Jean-Guy Charron got error 201 adding a BSON column with a PUT sbspace clause in one ALTER TABLE (14.10.FC10). He worked around it with two separate ALTER statements; Mike Walker saw the same. Philip Yeh showed one statement works with a comma before put, and Mike confirmed the documentation examples mostly omit it. Solved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi,
I have a syntax error when trying to alter a table to add a bson column to a sbspace.
If I create the table with the bson column everything work.
Example:
drop table if exists "prog".jgc;create table "prog".jgc
(
jgc_seq bigserial not null ,
jgc_sdbo1_seq bigint not null ,
jgc_tabname varchar(128) not null ,
jgc_tab_seq bigint not null ,
jgc_type char(1) not null ,
jgc_rec_date datetime year to second
default current year to second,
jgc_data "informix".bson
) put jgc_data in ( sbspace1_sbs )
extent size 1000 next size 1000 lock mode row;
But if I alter and existing table I have a syntax error
drop table if exists "prog".jgc;create table "prog".jgc
(
jgc_seq bigserial not null ,
jgc_sdbo1_seq bigint not null ,
jgc_tabname varchar(128) not null ,
jgc_tab_seq bigint not null ,
jgc_type char(1) not null ,
jgc_rec_date datetime year to second
default current year to second
)
extent size 1000 next size 1000 lock mode row;
alter table "prog".jgc
add (
jgc_data "informix".bson
) put jgc_data in ( sbspace1_sbs )
;
Database selected.
Table dropped.
Table created.
201: A syntax error has occurred.
Error in line 17Near character position 4
Database closed.
What's I'm doing wrong ?
dbaccess -versionProgram Name: dbaccess
Build Version: 14.10.FC10WE
Build Number: N164
Build Host: njdc-lxibm01
Build OS: Linux 3.10.0-693.el7.x86_64
Build Date: Tue Mar 7 18:17:20 CST 2023
Build Timestamp: 2023-03-07T16:45:54-06
GLS Version: glslib-7.00.FC7
Best Regards,
------------------------------
Jean-Guy Charron
------------------------------
Ok I found the solution. I need to add the column first and do another alter for the put clause. alter table "prog".jgc add ( jgc_data "informix".bson ) ; alter table "prog".jgc put jgc_data in ( sbspace1_sbs ) ; It's a bit strange the we can't to it at once. ------------------------------ Jean-Guy Charron ------------------------------
Ha - I was just about to suggest the same thing! I see the same behavior that the PUT throws a syntax error if it is included along with the ADD of the new column. But executing it as 2 distinct alter statements (ADD and PUT), then it seems to work. Weird though as this syntax should be acceptable according to the documentation. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------
You can complete the alter in one sql: alter table "prog".jgc add ( jgc_data "informix".bson ), put jgc_data in ( sbspace1_sbs ); Philip Yeh. ------------------------------ Philip Yeh ------------------------------
Thanks Philip - the comma is key here. The examples in the online documentation miss the comma...except in one of the examples, and I missed that. Cheers. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------