AW: CLOB Problem with DBD::ODBC/DBD::ADO for SQL Server

Hallo,

I got a solution for this problem so I thought you might be interested to hear it.
At Martin's advice I called Microsoft and Hans Lindgren from Microsoft Product Support confirmed the bug and found a workaround. I got the the OK from Hans Lindgren to post the summary of the case to the list, so here it is:

---
Action:
INSERTing strings containing '\<LF>' or '\<CR><LF>' into the database



Result:
Query succeeds but the Backslash and the following <LF> or <CR><LF> are removed from the string



Cause:
The T-SQL string parser removes these characters due to a parser artefact.



Resolution:
Parameters passed using RPC (remote procedure calls) are not parsed in the same way as strings used in T-SQL. Altering the INSERTion behaviour to use RPC avoids this issue (in this case the work around is to create a SP that handles the insert and to call it using RPC).
---

Besides the Microsoft case I did my own debugging and found out, that the bug does only occur when the data is sent to SQL Server with SQL_DATA_AT_EXEC and SQLParamData/SQLPutData. But this was not confirmed by Microsoft so I cannot guarantee that this works so in all cases. Nevertheless it works for me.

Servus -- Alfred


--
Alfred Mickautsch

Schuler Business Solutions AG
Karl-Berner-Str. 4
D-72285 Pfalzgrafenweiler
tel: +49 (0)74 45 830-184
fax: +49 (0)74 45 830-349
e-mail: alfred.mickautsch@xxxxxxxxxxxxxx


-----Ursprüngliche Nachricht-----
Von: Martin Evans [mailto:martin.evans@xxxxxxxxxxxx]
Gesendet: Dienstag, 30. Januar 2007 15:54
An: dbi-users@xxxxxxxx
Betreff: Re: CLOB Problem with DBD::ODBC/DBD::ADO for SQL Server


Mickautsch, Alfred wrote:
-----Ursprüngliche Nachricht-----
Von: Martin Evans [mailto:martin.evans@xxxxxxxxxxxx]
Gesendet: Dienstag, 30. Januar 2007 12:40
An: dbi-users@xxxxxxxx
Betreff: Re: AW: AW: Re: AW: CLOB Problem with
DBD::ODBC/DBD::ADO for
SQL Server
[...]
I insert 24 chars (16 \ and 8 line feeds) and I get back 24 chrs.

I am unsure why the code does not insert CR\LF as I am
100% sure the
file itself contains CR\LF.

I have tried with ntext, text and char fields - no difference.

I have run it through a tds spy and can see the line feeds
going through
fine in both directions.
[...]

Yes, it is a weird problem. There seems to be a byte count
limit under which this effect does not occur. It happens with
my text example of 102858 bytes. With a text of 569 bytes it
does not seem to happen. It is very confusing.

Thank you for your efforts.

Servus -- Alfred

As you indicate, the length has something to do with it. At
24000 chrs
it works and at 48000 chrs you lose each \ followed by a
<linefeed>. It
is of no consolation to you but I have duplicated it with the MS SQL
Server driver and also demonstrated it works fine with our sql server
driver. There is a difference. The MS SQL Server driver execs
sp_execute
to do the insert with the literal text whilst our driver calls
sp_execute with a parameter.

I had thought turning off translation might help but it doesn't.

I am of the opinion this may be a ms sql server odbc driver
bug in which
your options are limited unless you have a support contract
or can come
up with a workaround.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com


SCHULER Business Solutions AG
Aktiengesellschaft mit Sitz in D-72285 Pfalzgrafenweiler, Karl-Berner-Straße 4
Registergericht Stuttgart HRB 430947
Vorstand: Uwe Jonas, Harald Sieber
Vorsitzender des Aufsichtsrates: Gerhard Schuler

.

Relevant Pages

  • Re: which driver support setQueryTimeout
    ... > application that depends on Microsoft JDBC and so I have some further ... > this product as to which company will be providing support, ... Microsoft licenced a SQL Server 2000-only version of the Data ... Direct JDBC Connect 2.x driver, rebranded it as the freely downloadble SQL ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Unix applications and supported MSSQL driver
    ... Microsoft does not support the driver, Data Direct are the support point for ... Microsoft Windows. ... The database is inteded to be MS SQL SERVER 2000 or 2005. ... Does a supported driver exist? ...
    (microsoft.public.sqlserver.connect)
  • Re: JDBC 3.0?
    ... SQL Server is planning to ship an updated JDBC driver for the next version ... SQL Programming Model, SQL Server ... > I'm worried that Microsoft will eventually stop updating their 'free' JDBC ...
    (microsoft.public.sqlserver.jdbcdriver)
  • RE: [dbi] Re: MsSQL DBD::ODBC IsNull and undef
    ... I'd guess this is because the SQL Server ODBC driver is looking at your ... Martin J. Evans ... can you run your test script again but creating an OOB log file ...
    (perl.dbi.users)
  • Re: Padding using Type 4 Drivers
    ... |> value than what you have set in SQL Server 2000. ... |> settings that you require. ... |> Are you using the Microsoft JDBC driver or a third-party driver?. ...
    (microsoft.public.sqlserver.jdbcdriver)