CASTing existing LVARCHAR data to TEXT?

I have a developer who has run into the following problem. There is a column in a table whose data type is currently LVARCHAR(4096). This developer wants to alter the column so that its data type is TEXT. A straight MODIFY does not work; Informix tells us a CAST is required. Being an Informix newbie, I went and learned all about CREATE CAST. What function should I supply to convert an LVARCHAR to TEXT? Or am I out of luck? If it is easier to answer, I've also posted a StackOverflow question on the subject: http://stackoverflow.com/questions/6270011/how-do-i-create-a-cast-in-informix-to -cast-an-lvarchar-to-text Thanks, Laird

Why are you/the developer modifying the column to type TEXT? This is a simple large object type with limited functionality. What is the purpose of the column? How will it be used? Given that information, we can supply a good recommendation for the best type for this column. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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. On Tue, Jun 7, 2011 at 2:55 PM, LAIRD NELSON <ljnelson@gmail.com> wrote: > I have a developer who has run into the following problem. > > There is a column in a table whose data type is currently LVARCHAR(4096). > This > developer wants to alter the column so that its data type is TEXT. > > A straight MODIFY does not work; Informix tells us a CAST is required. > > Being an Informix newbie, I went and learned all about CREATE CAST. What > function should I supply to convert an LVARCHAR to TEXT? Or am I out of > luck? > > If it is easier to answer, I've also posted a StackOverflow question on the > subject: > > http://stackoverflow.com/questions/6270011/how-do-i-create-a-cast-in-informix-to -cast-an-lvarchar-to-text > > Thanks, > Laird > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf307f3784501ba104a5260f77

The column is apparently intended to store the full contents of a text document of some kind. Our Informix DBA suggested TEXT for this purpose. He specifically recommended that we not use CLOB here. For my own curiosity and education, however, supposing for the moment that the unspoken subtext here--i.e. that using TEXT is most likely bad in some way--is in fact not true: is there a way to perform an LVARCHAR to TEXT conversion? Or, as I'm beginning to gather from hours of searching and manual reading, is that impossible? Thanks for your help. Best, Laird

Oh, should have mentioned: there is no requirement to--for example--have random access to the, say, middle of the data, or anything like that. The column is read and written as a single block. Best, Laird

Not impossible, and there is nothing inherently wrong with either BLOB/CLOB or DATA/TEXT types. I just wanted to make certain that the type choice was appropriate. One 'problem' with using TEXT is that you have to retrieve the entire blob, you cannot fetch a subset (not strictly true, but true for most purposes). Another is that you cannot search within one. Simple blobs are opaque to the engine. One option is to create a new table and copy the data from the original table, including the LVARCHAR column, to the new table, with the TEXT column, using software. One simple option is my dbcopy utility which can do the copy by making the conversion from a string to a blob within the program itself. Dbcopy is included in the package utils2_ak which you can download from the IIUG Software Repository. It is open source and free. Enjoy. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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. On Tue, Jun 7, 2011 at 5:55 PM, LAIRD NELSON <ljnelson@gmail.com> wrote: > The column is apparently intended to store the full contents of a text > document of some kind. Our Informix DBA suggested TEXT for this purpose. He > specifically recommended that we not use CLOB here. > > For my own curiosity and education, however, supposing for the moment that > the > unspoken subtext here--i.e. that using TEXT is most likely bad in some > way--is > in fact not true: is there a way to perform an LVARCHAR to TEXT conversion? > Or, as I'm beginning to gather from hours of searching and manual reading, > is > that impossible? > > Thanks for your help. > > Best, > Laird > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec50fe0cdf87e4704a5276943

On Tue, Jun 7, 2011 at 14:55, LAIRD NELSON <ljnelson@gmail.com> wrote: > The column is apparently intended to store the full contents of a text > document of some kind. Our Informix DBA suggested TEXT for this purpose. He > specifically recommended that we not use CLOB here. > It would be interesting to know the DBA's reasons for recommending against CLOB. The primary one that I can think of is that it means the DBA has to configure a Smart Blobspace to store the CLOB values. However, the advantage is that either there are already casts available or the casts can be created reasonably easily with the MI interface. AFAIK, the MI interface does not support BYTE and TEXT blobs (for various ancient historical reasons), which makes it hard to write the casts. > For my own curiosity and education, however, supposing for the moment that > the > unspoken subtext here--i.e. that using TEXT is most likely bad in some > way--is > in fact not true: is there a way to perform an LVARCHAR to TEXT conversion? > Or, as I'm beginning to gather from hours of searching and manual reading, > is > that impossible? > The trouble is support via MI - or lack thereof. It is not intrinsically impossible; the server could be modified to support it. It has not been done, as I understand it. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --0015176f09964e22e404a5277ab9

Thank you, Art. This is what I was afraid of--that "one option" is sounding like the only option. :-( Now I'll bring some more context into this. We're using the most excellent Liquibase (http://www.liquibase.org) to manage the database, and its Informix support is for the most part quite fine (a few warts here and there). It's also open source (and I strongly encourage the IIUG to get involved to beef up the support, point out errors, etc.). At any rate, it is possible within Liquibase to do some pretty sophisticated work using the JDBC driver. Presumably, then, if I were to copy data from the LVARCHAR column into, say, a new column or a new column in another table with a datatype of TEXT by using, e.g., PreparedStatement.setString(), and allowing the driver to do whatever magic it does to perform the conversion I'd be OK? Best, Laird

One last point of note. Several people had suggested to me that I try creating a new table with the TEXT column and then doing an INSERT INTO that table from the old table. i.e. something like this (pseudo-DDL): -- create the new table with a TEXT column CREATE TABLE new_table (...various columns..., text_column TEXT) -- select all rows out of the old table and copy them into the new table INSERT INTO new_tableSELECT ...various stuff including the lvarchar column ... FROM old_table It was suggested to me that my company already has an in-house tool that does this sort of thing successfully. But when I try it, I get a different variant on the same old error (from within the JDBC driver, v3.70JC1): A blob data type must be supplied within this context. SQLState: IX000 ErrorCode: -111 This would accord nicely with what people have been hinting at--i.e. that without some other setup work there is no SQL on the planet that will perform this cast for me. My next attempt will be to do this sort of translation by using a mutable ResultSet in the JDBC driver. My hope there is that since the driver is contractually bound to allow me to feed characters into a TEXT or CLOB column that I can perform the actual conversion by selecting the old value out and then inserting or updating it from within JDBC. Best, Laird

Assuming the liquibase folk have coded to allow that and transfer the data from the character string into one of the input formats supported by blob columns, yes. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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. On Tue, Jun 7, 2011 at 7:46 PM, LAIRD NELSON <ljnelson@gmail.com> wrote: > Thank you, Art. This is what I was afraid of--that "one option" is sounding > like the only option. :-( > > Now I'll bring some more context into this. We're using the most excellent > Liquibase (http://www.liquibase.org) to manage the database, and its > Informix > support is for the most part quite fine (a few warts here and there). It's > also open source (and I strongly encourage the IIUG to get involved to beef > up > the support, point out errors, etc.). > > At any rate, it is possible within Liquibase to do some pretty > sophisticated > work using the JDBC driver. Presumably, then, if I were to copy data from > the > LVARCHAR column into, say, a new column or a new column in another table > with > a datatype of TEXT by using, e.g., PreparedStatement.setString(), and > allowing > the driver to do whatever magic it does to perform the conversion I'd be > OK? > > Best, > Laird > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf3071cec23f6f6304a55c412a

Dbcopy.ec can do this job for you and it is completely free! If for some reason you have any trouble using it, let me know and I'll fix it, typically within a week at the outside. Just down load the utils2_ak package from the IIUG Software Repository (www.iiug.org/software) and 'make' it. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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. On Thu, Jun 9, 2011 at 10:37 AM, LAIRD NELSON <ljnelson@gmail.com> wrote: > One last point of note. > > Several people had suggested to me that I try creating a new table with the > TEXT column and then doing an INSERT INTO that table from the old table. > i.e. > something like this (pseudo-DDL): > > -- create the new table with a TEXT column > CREATE TABLE new_table (...various columns..., text_column TEXT)> > -- select all rows out of the old table and copy them into the new table > INSERT INTO new_table> SELECT ...various stuff including the lvarchar column ... > FROM old_table > > It was suggested to me that my company already has an in-house tool that > does > this sort of thing successfully. But when I try it, I get a different > variant > on the same old error (from within the JDBC driver, v3.70JC1): > > A blob data type must be supplied within this context. > SQLState: IX000 > ErrorCode: -111 > > This would accord nicely with what people have been hinting at--i.e. that > without some other setup work there is no SQL on the planet that will > perform > this cast for me. > > My next attempt will be to do this sort of translation by using a mutable > ResultSet in the JDBC driver. My hope there is that since the driver is > contractually bound to allow me to feed characters into a TEXT or CLOB > column > that I can perform the actual conversion by selecting the old value out and > then inserting or updating it from within JDBC. > > Best, > Laird > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf307f37ce0367d904a55ca4b7

On Fri, Jun 10, 2011 at 10:46 AM, Art Kagel <art.kagel@gmail.com> wrote: > Dbcopy.ec can do this job for you and it is completely free! > Thanks, Art. I forgot to mention that I am doing this within Java and as part of what amounts to an installer using the most excellent Liquibase tool (http://www.liquibase.org/; it's open source, please check it out; the Informix support could probably use an expert touch). So I need to be able to do it from a JDBC driver or from native Informix SQL/DDL. I've discovered that given an LVARCHAR (or VARCHAR) column and a TEXTcolumn, there is no way using just SQL to copy data from one to the other. Can't do it via ALTER TABLE, can't do it via SELECT INTO, can't do it via UPDATE, etc. Fortunately, the JDBC driver appears to do this conversion just fine. So my solution (which is odd in that it is very specific and very general all at the same time :-)) is to accomplish an ALTER TABLE foo MODIFY...statement using a JDBC mutable ResultSet: 1. Create a new column in the table with the LVARCHAR column. Give that column a type of TEXT. 2. Using the JDBC driver, SELECT * FROM table, making sure to specify that the ResultSet should be CONCUR_UPDATABLE. ( http://download.oracle.com/javase/6/docs/api/java/sql/ResultSet.html#CONCUR_UPDA TABLE ) 3. Iterate through the ResultSet. Do this for each row: resultSet.updateObject("text_column", resultSet.getObject("lvarchar_column")); resultSet.updateRow(); 4. Drop the old LVARCHAR column 5. Rename the TEXT column back to what the LVARCHAR column was named This is specific, because of course it only works (I think) in one table--most JDBC driver implementations place a restriction on what kinds of ResultSets may be updated in this manner. It's general because no JDBC driver for any database that I'm aware of that supports mutable result sets will bark about a simple one-table update like this, and it forces the type conversion to happen at the driver level. Anyhow, thanks everyone for your help! Best, Laird --0015173fedc08ceac704a55ce368