The hardest post I had ever write.

I have been writing this post for few times. And it has been review and revise for few times due to the post is too technical and not enough nonsense.

I was thinking whether this will only happen in Informix. I was trying to construct a dynamic SQL that create a trigger which will attach to a table. This could be a very long string, there are 2 candidates taking up this challenge, the varchar, max size is 255 bytes, and lvarchar, max size is 32k bytes.

This text is reference from IBM Informix:

For variable-length strings longer than 255 bytes, you can use the LVARCHAR data type, whose upper limit is 32,739 bytes, instead of VARCHAR. Because LVARCHAR is implemented as a built-in opaque data type, however, you cannot access LVARCHAR columns in distributed queries of remote tables.

Guess what, both of them failed in the test. It is due to the table contain too much fields(columns) in it. It just too long. I think we need a data architecture in our team to take care of this.

To work around this issue, I have to chop the string into portion end up a very ugly SQL code. The most bad code I had ever wrote in my life. Below are the sample code:

execute immediate "CREATE TRIGGER " || trigger_name || " UPDATE OF " || field_name || " ON " || table_name || " REFERENCING OLD as post NEW as pre FOR EACH ROW ( INSERT INTO ( " || shadow_table_name || " ) VALUES ( " || the_value || " )) "

Too bad.