Converting TEXT field to CHAR(5000) field
Posted in 1997
I need to assign the first 5000 characters of a TEXT field to a CHAR(5000) field in the same record every time the TEXT field is updated or when a new record is added. The users do not have access to the char(5000) field. They read/write the TEXT field. (This is due to problems with the client application updating the char(5000) field). I first attacked the problem with 2 triggers: one for updates and one for inserts. However, Informix did not like a simple assignment (i.e. SET charfied = textfield). So I wrote a stored procedure to handle the conversion and the triggers would call the procedure. I failed again because SPL cannot handle the only method that would achieve my goal: dump the TEXT field to a system file and then pick up the text and load it to the char(5000) field (i.e. I needed to use UNLOAD, LOAD or LOCATE IN FILE for the TEXT variable). So I wrote a 4GL program which did the job and the stored procedure consisted of one SYSTEM statement calling the 4GL program. I though I was there but I run into a wall: a client/server connection to Informix Online 5.04 is unable to handle a SYSTEM call in a stored procedure. According to the technical support people, that is a known bug. It appears that my only choice is to upgrade the engine to Online 6 or 7. This seems somewhat drastic to me. Is there any other way to handle this problem? Any help would be appreciated. Thank you. Gabriel