Re: VARCHAR(max) in SP output params with TDS>=7.2 and rpc API

2014-11-23 18:31 GMT+00:00 Ramiro Morales <[email protected]>:
> Hi all,
>
> I've found FreeTDS master can't handle the new in TDS 7.2 ability to
> handle values longer than 8000 chars.
>
> I've opened a merge request which adds tests that show the failure:
>
> https://gitorious.org/freetds/freetds/merge_requests/32
>
> I tried to modify the RPC parameter handling rpc API code to get this
> working by following what can be read at
> http://www.freetds.org/tds.html#login7 ('Types' sub-section):
>
> There is a call-out in that table for the XSYBVARBINARY, XSYBVARCHAR
> and XSYBNVARCHAR data types that reads:
>
> "* Under TDS 7.2+ these types allow size to be -1, representing
> varchar(max), varbinary(max) and nvarchar(max)."
>
> Unfortuately my attemps were unsuccessful. I suspect what is described
> there doesn't apply to the RPC SP API.
>
> Any hints on how these data types need to be represented on the wire
> are welcome. I'd be willing to try to implement the required changes.
>
> Regards,
>
> PS: My use case is about output parameters, but it's possible input
> ones also need work. See https://github.com/pymssql/pymssql/issues/275
>


Hi Ramiro,
  I'm trying to have a look at your patch. I didn't fix the
compatibility with Sybase but I did some test and I came up with some
patches. One problem is that there are not strictly speaking output
parameters, a parameter can be input or input/output. The trick to use
NVARCHAR was to be able to insert any character into database but of
course you limit to 4000 instead of 8000. I tried at first to use
VARCHAR(MAX) as it has no limit, server returns it but it put these
field last. Not a big deal but could break application which expect
field in a given order (like our test). See
https://gitorious.org/freetds/mars-freetds/commits/856eed7d1d529377db33f452204aa344c8d778ad
branch, it's messy but I put some changes. Basically the rule I turned
up is: if VARCHAR is up to 4000 use NVARCHAR, if not use VARCHAR. I
also changed your sql script to use char/nchar function so file
encoding is not an issue (it tuned out that file was utf-8 but client
iso8859-1 so the single character you specified turned out to be 2
characters).

You should also use SYBVARCHAR and not 231, our dblib convert every
string and should not deal with wide characters.

I tried to switch client to utf-8 (commented out) and characters are
returned correctly but test fail for lengths returned. I'm not sure
that returning twice the length is fine.

Frediano