ALTER TABLE to change CHAR/VARCHAR/LVARCHAR to TEXT

Hello, I am looking for a pure SQL (or maybe using some stored procedure code) to change a CHAR / VARCHAR / LVARCHAR column to a TEXT column, with data inside. I know there is a CLOB type but we want to use TEXT. It must not use dbaccess (LOAD/UNLOAD): It must be pure SQL or SP that can be called from Java/JDBC or 4GL or any language with Informix client interface, to do basic PREPARE+EXECUTE or EXECUTE IMMEDIATE with a set of pure SQL commands that are defined in a script file. Any suggestion or workaround is welcome. Thanks! Seb ------------------------------ SEBASTIEN SF FLAESCH ------------------------------ #Informix

Sebastian: You can do this in ESQL/C code (and that means that you can code an ESQL/C function that is callable from a 4GL program to do the work). It involves PREPARING the insert statement, then DESCRIBE the statement into an sqlda structure, then tweak the structure so that the data location of the TEXT column points to whatever buffer you have fetched the LVARCHAR column into (as well as pointing the remaining columns' data locations to the rest of the data), setting the loc_type to LOCMEMORY for the TEXT column. Once that is done, you can open a PUT cursor on that prepared INSERT statement, OPEN it, and then you FETCH each source row into memory and PUT it to the PUT cursor to insert it into the target table. The demo file $INFORMIXDIR/demo/esqlc/ blobload.ec shows an example of inserting a file containing blob data (setting the loc_type to LOCFNAME) but it is one example of how to do this. Another is the code for my dbcopy.ec utility in my utils2_ak package. However, because it uses FETCH ARRAY technology to fetch data from the source table very quickly you cannot use dbcopy as is because you cannot fetch LVARCHAR data using the FETCH ARRAY method, but the code to perform the insert into the target table that contains a TEXT or BYTE type column is good. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. ------Original Message------ Hello, I am looking for a pure SQL (or maybe using some stored procedure code) to change a CHAR / VARCHAR / LVARCHAR column to a TEXT column, with data inside. I know there is a CLOB type but we want to use TEXT. It must not use dbaccess (LOAD/UNLOAD): It must be pure SQL or SP that can be called from Java/JDBC or 4GL or any language with Informix client interface, to do basic PREPARE+EXECUTE or EXECUTE IMMEDIATE with a set of pure SQL commands that are defined in a script file. Any suggestion or workaround is welcome. Thanks! Seb ------------------------------ SEBASTIEN SF FLAESCH ------------------------------ #Informix

Hi Sebastien, It takes some processing steps, but how about considering using an external table? Below is what I tested using the stores_demo sample table. drop table "informix".cat3; drop table "informix".cat4; -- step 1. Create external table to download source data (table 'cat2') create external table "informix".cat3 ( catalog_num serial not null , stock_num smallint not null , manu_code char(3) not null , cat_descr varchar(255), cat_advert varchar(255,65) ) using (datafiles ('disk:/tmp/cat3.unl'), format 'delimited'); -- step 2. Create target table to upload source data create table "informix".cat4 ( catalog_num serial not null , stock_num smallint not null , manu_code char(3) not null , cat_descr text, -- varchar->text cat_advert text -- varchar->text ); -- step 3. Download source data insert into cat3 select catalog_num,stock_num,manu_code,cat_descr,cat_advert from cat2; -- step 4. Changing the external table schema (Downloaded file is not deleted.) drop table "informix".cat3; create external table "informix".cat3 ( catalog_num serial not null , stock_num smallint not null , manu_code char(3) not null , cat_descr text, cat_advert text ) using (datafiles ('disk:/tmp/cat3.unl'), format 'delimited'); -- step 5. Upload data to target table insert into cat4 select * from cat3;​ ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

Thank you Art! Ideally it should be pure SQL or SPL just to execute from a programming language with prepare/execute or execute immediate... The program code must be generic as it will read from a command script to execute the SQL. It is to implement a database schema upgrade program mainly with ALTER TABLE commands. In fact I could also write a Genero BDL program doing the job, or Java/JDBC I guess. It must run on the server side, as data has to be transmitted to the client application to convert char types to TEXT. Seb ------------------------------ SEBASTIEN SF FLAESCH ------------------------------

Thank you SangGyu! That looks promising, I will take a look! Just need to deal with DB object dependencies like foreign keys, table used in views or stored procedures, since this process creates a new table from the original one. Obviously all of this would be much easier if Informix would support ALTER TABLE tabname MODIFY (colname TEXT/CLOB) Seb ------------------------------ SEBASTIEN SF FLAESCH ------------------------------