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

Alfred,

Thank you for coming back here to let us know how you got on and the resolution. The answer you got from Microsoft is very interesting and it is certainly worth remembering the workaround.

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

Mickautsch, Alfred wrote:
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


.