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: =20
INSERTing strings containing '\' or '\' into the database

=20

Result:
Query succeeds but the Backslash and the following or are =
removed from the string

=20

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

=20

Resolution:=20
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@schuler-ag.com


> -----Ursprüngliche Nachricht-----
> Von: Martin Evans [mailto:martin.evans@easysoft.com]
> Gesendet: Dienstag, 30. Januar 2007 15:54
> An: dbi-users@perl.org
> Betreff: Re: CLOB Problem with DBD::ODBC/DBD::ADO for SQL Server
>=20
>=20
> Mickautsch, Alfred wrote:
> >> -----Ursprüngliche Nachricht-----
> >> Von: Martin Evans [mailto:martin.evans@easysoft.com]
> >> Gesendet: Dienstag, 30. Januar 2007 12:40
> >> An: dbi-users@perl.org
> >> Betreff: Re: AW: AW: Re: AW: CLOB Problem with=20
> 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=20
> 100% sure the=20
> >> 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=20
> >> going through=20
> >> fine in both directions.
> > [...]
> >=20
> > Yes, it is a weird problem. There seems to be a byte count=20
> limit under which this effect does not occur. It happens with=20
> my text example of 102858 bytes. With a text of 569 bytes it=20
> does not seem to happen. It is very confusing.
> >=20
> > Thank you for your efforts.
> >=20
> > Servus -- Alfred
>=20
> As you indicate, the length has something to do with it. At=20
> 24000 chrs=20
> it works and at 48000 chrs you lose each \ followed by a=20
> . It=20
> is of no consolation to you but I have duplicated it with the MS SQL=20
> Server driver and also demonstrated it works fine with our sql server=20
> driver. There is a difference. The MS SQL Server driver execs=20
> sp_execute=20
> to do the insert with the literal text whilst our driver calls=20
> sp_execute with a parameter.
>=20
> I had thought turning off translation might help but it doesn't.
>=20
> I am of the opinion this may be a ms sql server odbc driver=20
> bug in which=20
> your options are limited unless you have a support contract=20
> or can come=20
> up with a workaround.
>=20
> Martin
> --=20
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
>=20

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

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 '\' or '\' into the database
>
>
>
> Result:
> Query succeeds but the Backslash and the following or 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@schuler-ag.com
>
>
>> -----Ursprüngliche Nachricht-----
>> Von: Martin Evans [mailto:martin.evans@easysoft.com]
>> Gesendet: Dienstag, 30. Januar 2007 15:54
>> An: dbi-users@perl.org
>> Betreff: Re: CLOB Problem with DBD::ODBC/DBD::ADO for SQL Server
>>
>>
>> Mickautsch, Alfred wrote:
>>>> -----Ursprüngliche Nachricht-----
>>>> Von: Martin Evans [mailto:martin.evans@easysoft.com]
>>>> Gesendet: Dienstag, 30. Januar 2007 12:40
>>>> An: dbi-users@perl.org
>>>> 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
>> . 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
>
>