Very Computer

I'm using version 6.5 so I don't have the Replace function at my disposal.

The problem:
I'm inserting into a table from a JAVA servlet.  The JDBC/ODBC bridge driver
will not allow my inserts to include any linefeed/carriage return
characters.  They all get stripped out.  Because of that I am inserting the
string [LINE] wherever line breaks should occur.

In my insert Stored Procedure I can use WHILE,  CHARINDEX and STUFF to
remove the [LINE] strings and replace them with CHAR(13) + CHAR(10).  This
is working just fine for my VarChar variables, but I can't use these
functions on a text variable.

PATINDEX will do the same as CHARINDEX and will work against a text value,
but I don't see a way to duplicate the STUFF function.

For now, I am planning on just leaving the [LINE] strings in the text fields
and use a replace function when they data is viewed in my Access report, but
I would sure like to fix the data instead.

Any help appreciated.

Quote:> I'm using version 6.5 so I don't have the Replace function at my disposal.

> The problem:
> I'm inserting into a table from a JAVA servlet.  The JDBC/ODBC bridge
driver
> will not allow my inserts to include any linefeed/carriage return
> characters.  They all get stripped out.  Because of that I am inserting
the
> string [LINE] wherever line breaks should occur.

> In my insert Stored Procedure I can use WHILE,  CHARINDEX and STUFF to
> remove the [LINE] strings and replace them with CHAR(13) + CHAR(10).  This
> is working just fine for my VarChar variables, but I can't use these
> functions on a text variable.

> PATINDEX will do the same as CHARINDEX and will work against a text value,
> but I don't see a way to duplicate the STUFF function.

> For now, I am planning on just leaving the [LINE] strings in the text
fields
> and use a replace function when they data is viewed in my Access report,
but
> I would sure like to fix the data instead.

> Any help appreciated.

As a follow-up to my own question:

Is it correct that MS' JDBC driver for SQL Server will only work with SQL
Server 2000?  I suspect that a proper driver would solve the problem of the
line-feed characters being dropped so I wouldn't need the work-around in the
first place.

I have heard both that it flat-out doesn't work and also that is just isn't
"supported" on the older versions.

[posted and mailed, please reply in news]

Here is a script that uses UPDATETEXT for the task and which appears to
do what you are asking for. It's not the fastest in town, but...

   CREATE TABLE texttabell(a int NOT NULL,
                           b text NOT NULL)
   go
   INSERT texttabell VALUES (1, '')
   go

   'We are here inserting text in which newlines are placed with [LINE]
    because of some funny quirk of a JDBC [LINE] driver. However in
    this text there is also real newlines, because [LINE] eventually
    this code sample will make it to a newsgroup posting, where we
    only have a column [LINE] width of 80 chars to play with. We want
    the text be > 255 chars [LINE], so that we know that the sample
    really works [LINE] on SQL 6.5.'
   go
   SELECT * FROM texttabell
   go

   WHILE 1 = 1
   BEGIN

      FROM texttabell

   END
   go
   SELECT datalength(b), b FROM texttabell

--

I support PASS - the definitive global community for SQL Server
professionals - http://www.sqlpass.org

The download page says that "will be supported" and then goes on to
list SQL Server 2000. It doesn't say that it does not work with
6.5, but I would guess that it doesn't. Chances are good that it
uses functionality, for instance system functions, which is not available
in SQL 6.5.

If you look at http://www.algonet.se/~sommar/mssql/unix.html you find
some links to other JDBC drivers. (Myself I only know that Java is
close to Sumatra, so don't ask me any details.)

--

I support PASS - the definitive global community for SQL Server
professionals - http://www.sqlpass.org

Looks like a winner.  I won't be in a position to try it until Monday
though.

Thanks;