IDS Forum

Re: CASTing existing LVARCHAR data to TEXT?

Posted By: Laird Nelson
Date: Friday, 10 June 2011, at 11:05 a.m.

In Response To: Re: CASTing existing LVARCHAR data to TEXT? (Art Kagel)

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_UPDATABLE

)

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

Messages In This Thread

• CASTing existing LVARCHAR data to TEXT?
  LAIRD NELSON -- Tuesday, 7 June 2011, at 2:55 p.m.
  • Re: CASTing existing LVARCHAR data to TEXT?
    Art Kagel -- Tuesday, 7 June 2011, at 5:39 p.m.
    • Re: CASTing existing LVARCHAR data to TEXT?
      LAIRD NELSON -- Tuesday, 7 June 2011, at 5:55 p.m.
      • Re: CASTing existing LVARCHAR data to TEXT?
        LAIRD NELSON -- Tuesday, 7 June 2011, at 5:57 p.m.
      • Re: CASTing existing LVARCHAR data to TEXT?
        Art Kagel -- Tuesday, 7 June 2011, at 7:16 p.m.
        • Re: CASTing existing LVARCHAR data to TEXT?
          LAIRD NELSON -- Tuesday, 7 June 2011, at 7:46 p.m.
          • Re: CASTing existing LVARCHAR data to TEXT?
            Art Kagel -- Friday, 10 June 2011, at 10:19 a.m.
      • Re: CASTing existing LVARCHAR data to TEXT?
        Jonathan Leffler -- Tuesday, 7 June 2011, at 7:21 p.m.
        • Re: CASTing existing LVARCHAR data to TEXT?
          LAIRD NELSON -- Thursday, 9 June 2011, at 10:37 a.m.
          • Re: CASTing existing LVARCHAR data to TEXT?
            Art Kagel -- Friday, 10 June 2011, at 10:46 a.m.
            • Re: CASTing existing LVARCHAR data to TEXT?
              Laird Nelson -- Friday, 10 June 2011, at 11:05 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.