"max" datatypes support of SQL Server 2005 for DBD-ODBC

Hi all,

I had a need to support [n]varchar(max) and varbinary(max) datatypes of
MS SQL Server 2005, and patched DBD-ODBC.
Below is the diff from the current svn.

Thanks,
Fumiaki Yoshimatsu

Index: C:/DBD-ODBC/dbdimp.c
==================== =====3D=
==================== =====3D=
=================3D
--- C:/DBD-ODBC/dbdimp.c=09(revision 3682)
+++ C:/DBD-ODBC/dbdimp.c=09(working copy)
@@ -1761,7 +1761,6 @@
=09 /* patch to allow binary types 3/24/99 courtesy of Jon
=09 * Smirl
=09 */
-=09 case SQL_VARBINARY:
=09 case SQL_BINARY:
=09 fbh->ftype =3D SQL_C_BINARY;
=09 break;
@@ -1782,6 +1781,14 @@
=09 fbh->ColDisplaySize =3D sizeof(TIMESTAMP_STRUCT);
=09 break;
#endif
+ case SQL_VARBINARY:
+ fbh->ftype =3D SQL_C_BINARY;
+ case SQL_VARCHAR:
+ case SQL_WVARCHAR:
+ if (fbh->ColDef == 0) {
+ fbh->ColDisplaySize =3D DBIc_LongReadLen(imp_sth);
+ }
+ break;
}

/* make sure alignment is accounted for on all types, including

Re: "max" datatypes support of SQL Server 2005 for DBD-ODBC

Fumiaki Yoshimatsu wrote:

> I had a need to support [n]varchar(max) and varbinary(max) datatypes of
> MS SQL Server 2005, and patched DBD-ODBC.
> Below is the diff from the current svn.

Does anyone know what, if any, support for these DBD::Sybase has?

--
Steve Sapovits steves06@comcast.net

Re: "max" datatypes support of SQL Server 2005 for DBD-ODBC

DBD::Sybase will support whatever the underlying client libs are capable of
handling.

Michael




Extranet
steves06@comcast.net - 21/08/2006 15:27

To: fumiakiy

cc: dbi-users


Subject: Re: "max" datatypes support of SQL Server 2005 for DBD-ODBC


Fumiaki Yoshimatsu wrote:

> I had a need to support [n]varchar(max) and varbinary(max) datatypes of
> MS SQL Server 2005, and patched DBD-ODBC.
> Below is the diff from the current svn.

Does anyone know what, if any, support for these DBD::Sybase has?

--
Steve Sapovits steves06@comcast.net




This message and any attachments (the "message") is
intended solely for the addressees and is confidential.
If you receive this message in error, please delete it and
immediately notify the sender. Any use not in accord with
its purpose, any dissemination or disclosure, either whole
or partial, is prohibited except formal approval. The internet
can not guarantee the integrity of this message.
BNP PARIBAS (and its subsidiaries) shall (will) not
therefore be liable for the message if modified.

---------------------------------------------

Ce message et toutes les pieces jointes (ci-apres le
"message") sont etablis a l'intention exclusive de ses
destinataires et sont confidentiels. Si vous recevez ce
message par erreur, merci de le detruire et d'en avertir
immediatement l'expediteur. Toute utilisation de ce
message non conforme a sa destination, toute diffusion
ou toute publication, totale ou partielle, est interdite, sauf
autorisation expresse. L'internet ne permettant pas
d'assurer l'integrite de ce message, BNP PARIBAS (et ses
filiales) decline(nt) toute responsabilite au titre de ce
message, dans l'hypothese ou il aurait ete modifie.

Re: "max" datatypes support of SQL Server 2005 for DBD-ODBC

michael.peppler@bnpparibas.com wrote:

> DBD::Sybase will support whatever the underlying client libs are capable of
> handling.

I'll throw the wrench in now: I'm using the DBD::Sybase bulk copy
interface as well. That's working great but I'm getting a failure
on a table that has a varchar(max) column. I haven't isolated it to
that yet -- I'm going to test with and without that. In the meantime,
if you have any knowledge to bestow on me regarding varchar(max) and
bulk copies, let me know.

This is a RH Linux environment, FreeTDS drivers. Perl 5.8.7,
DBD::Sybase 1.07. Target database is MS SQL Server 9.0.2047.

Bulk loading rocks by the way Michael. ;-)

>
> Michael
>
>
>
>
> Extranet
> steves06@comcast.net - 21/08/2006 15:27
>
> To: fumiakiy
>
> cc: dbi-users
>
>
> Subject: Re: "max" datatypes support of SQL Server 2005 for DBD-ODBC
>
>
> Fumiaki Yoshimatsu wrote:
>
>> I had a need to support [n]varchar(max) and varbinary(max) datatypes of
>> MS SQL Server 2005, and patched DBD-ODBC.
>> Below is the diff from the current svn.
>
> Does anyone know what, if any, support for these DBD::Sybase has?
>
> --
> Steve Sapovits steves06@comcast.net
>
>
>
>
> This message and any attachments (the "message") is
> intended solely for the addressees and is confidential.
> If you receive this message in error, please delete it and
> immediately notify the sender. Any use not in accord with
> its purpose, any dissemination or disclosure, either whole
> or partial, is prohibited except formal approval. The internet
> can not guarantee the integrity of this message.
> BNP PARIBAS (and its subsidiaries) shall (will) not
> therefore be liable for the message if modified.
>
> ---------------------------------------------
>
> Ce message et toutes les pieces jointes (ci-apres le
> "message") sont etablis a l'intention exclusive de ses
> destinataires et sont confidentiels. Si vous recevez ce
> message par erreur, merci de le detruire et d'en avertir
> immediatement l'expediteur. Toute utilisation de ce
> message non conforme a sa destination, toute diffusion
> ou toute publication, totale ou partielle, est interdite, sauf
> autorisation expresse. L'internet ne permettant pas
> d'assurer l'integrite de ce message, BNP PARIBAS (et ses
> filiales) decline(nt) toute responsabilite au titre de ce
> message, dans l'hypothese ou il aurait ete modifie.
>


--
Steve Sapovits steves06@comcast.net

Re: "max" datatypes support of SQL Server 2005 for DBD-ODBC

There is no configuration that can be done at the DBD::Sybase level for the
BLK handling - if you are limited to 255 chars then it is most likely a
limitation of the BLK api implementation of FreeTDS.

Michael





Extranet
steves06@comcast.net - 21/08/2006 15:39

To: Michael PEPPLER

cc: fumiakiy, dbi-users


Subject: Re: "max" datatypes support of SQL Server 2005 for DBD-ODBC


michael.peppler@bnpparibas.com wrote:

> DBD::Sybase will support whatever the underlying client libs are capable
of
> handling.

I'll throw the wrench in now: I'm using the DBD::Sybase bulk copy
interface as well. That's working great but I'm getting a failure
on a table that has a varchar(max) column. I haven't isolated it to
that yet -- I'm going to test with and without that. In the meantime,
if you have any knowledge to bestow on me regarding varchar(max) and
bulk copies, let me know.

This is a RH Linux environment, FreeTDS drivers. Perl 5.8.7,
DBD::Sybase 1.07. Target database is MS SQL Server 9.0.2047.

Bulk loading rocks by the way Michael. ;-)

>
> Michael
>
>
>
>
> Extranet
> steves06@comcast.net - 21/08/2006 15:27
>
> To: fumiakiy
>
> cc: dbi-users
>
>
> Subject: Re: "max" datatypes support of SQL Server 2005 for DBD-ODBC
>
>
> Fumiaki Yoshimatsu wrote:
>
>> I had a need to support [n]varchar(max) and varbinary(max) datatypes of
>> MS SQL Server 2005, and patched DBD-ODBC.
>> Below is the diff from the current svn.
>
> Does anyone know what, if any, support for these DBD::Sybase has?
>
> --
> Steve Sapovits steves06@comcast.net
>
>
>
>
> This message and any attachments (the "message") is
> intended solely for the addressees and is confidential.
> If you receive this message in error, please delete it and
> immediately notify the sender. Any use not in accord with
> its purpose, any dissemination or disclosure, either whole
> or partial, is prohibited except formal approval. The internet
> can not guarantee the integrity of this message.
> BNP PARIBAS (and its subsidiaries) shall (will) not
> therefore be liable for the message if modified.
>
> ---------------------------------------------
>
> Ce message et toutes les pieces jointes (ci-apres le
> "message") sont etablis a l'intention exclusive de ses
> destinataires et sont confidentiels. Si vous recevez ce
> message par erreur, merci de le detruire et d'en avertir
> immediatement l'expediteur. Toute utilisation de ce
> message non conforme a sa destination, toute diffusion
> ou toute publication, totale ou partielle, est interdite, sauf
> autorisation expresse. L'internet ne permettant pas
> d'assurer l'integrite de ce message, BNP PARIBAS (et ses
> filiales) decline(nt) toute responsabilite au titre de ce
> message, dans l'hypothese ou il aurait ete modifie.
>


--
Steve Sapovits steves06@comcast.net

Re: "max" datatypes support of SQL Server 2005 for DBD-ODBC

michael.peppler@bnpparibas.com wrote:

> There is no configuration that can be done at the DBD::Sybase level for the
> BLK handling - if you are limited to 255 chars then it is most likely a
> limitation of the BLK api implementation of FreeTDS.

In this case, the data itself is either small or NULL for the test
cases. And I believe I've loaded other columns (e.g., varchar(2000))
with more than 255. My thinking is maybe it's something about that
type -- much like you see issues with image, text, and Oracle CLOB
types sometimes in different areas.

I'll post back once I run tests ... it may not even be that. Here's
the error I get back from the driver:

DBD::Sybase::db commit failed: Server message number=4815 severity=17
state=1 line=1 server=NEXDB\DEV text=Received an invalid column length
from the bcp client for colid 6.OpenClient message: LAYER = (2)
ORIGIN = (5) SEVERITY = (1) NUMBER = (140)


>
> Michael
>
>
>
>
>
> Extranet
> steves06@comcast.net - 21/08/2006 15:39
>
> To: Michael PEPPLER
>
> cc: fumiakiy, dbi-users
>
>
> Subject: Re: "max" datatypes support of SQL Server 2005 for DBD-ODBC
>
>
> michael.peppler@bnpparibas.com wrote:
>
>> DBD::Sybase will support whatever the underlying client libs are capable
> of
>> handling.
>
> I'll throw the wrench in now: I'm using the DBD::Sybase bulk copy
> interface as well. That's working great but I'm getting a failure
> on a table that has a varchar(max) column. I haven't isolated it to
> that yet -- I'm going to test with and without that. In the meantime,
> if you have any knowledge to bestow on me regarding varchar(max) and
> bulk copies, let me know.
>
> This is a RH Linux environment, FreeTDS drivers. Perl 5.8.7,
> DBD::Sybase 1.07. Target database is MS SQL Server 9.0.2047.
>
> Bulk loading rocks by the way Michael. ;-)
>
>> Michael
>>
>>
>>
>>
>> Extranet
>> steves06@comcast.net - 21/08/2006 15:27
>>
>> To: fumiakiy
>>
>> cc: dbi-users
>>
>>
>> Subject: Re: "max" datatypes support of SQL Server 2005 for DBD-ODBC
>>
>>
>> Fumiaki Yoshimatsu wrote:
>>
>>> I had a need to support [n]varchar(max) and varbinary(max) datatypes of
>>> MS SQL Server 2005, and patched DBD-ODBC.
>>> Below is the diff from the current svn.
>> Does anyone know what, if any, support for these DBD::Sybase has?
>>
>> --
>> Steve Sapovits steves06@comcast.net
>>
>>
>>
>>
>> This message and any attachments (the "message") is
>> intended solely for the addressees and is confidential.
>> If you receive this message in error, please delete it and
>> immediately notify the sender. Any use not in accord with
>> its purpose, any dissemination or disclosure, either whole
>> or partial, is prohibited except formal approval. The internet
>> can not guarantee the integrity of this message.
>> BNP PARIBAS (and its subsidiaries) shall (will) not
>> therefore be liable for the message if modified.
>>
>> ---------------------------------------------
>>
>> Ce message et toutes les pieces jointes (ci-apres le
>> "message") sont etablis a l'intention exclusive de ses
>> destinataires et sont confidentiels. Si vous recevez ce
>> message par erreur, merci de le detruire et d'en avertir
>> immediatement l'expediteur. Toute utilisation de ce
>> message non conforme a sa destination, toute diffusion
>> ou toute publication, totale ou partielle, est interdite, sauf
>> autorisation expresse. L'internet ne permettant pas
>> d'assurer l'integrite de ce message, BNP PARIBAS (et ses
>> filiales) decline(nt) toute responsabilite au titre de ce
>> message, dans l'hypothese ou il aurait ete modifie.
>>
>
>
> --
> Steve Sapovits steves06@comcast.net
>
>


--
Steve Sapovits steves06@comcast.net

Re: "max" datatypes support of SQL Server 2005 for DBD-ODBC

I wrote:

> In this case, the data itself is either small or NULL for the test
> cases. And I believe I've loaded other columns (e.g., varchar(2000))
> with more than 255. My thinking is maybe it's something about that
> type -- much like you see issues with image, text, and Oracle CLOB
> types sometimes in different areas.
>
> I'll post back once I run tests ... it may not even be that. Here's
> the error I get back from the driver:
>
> DBD::Sybase::db commit failed: Server message number=4815 severity=17
> state=1 line=1 server=NEXDB\DEV text=Received an invalid column length
> from the bcp client for colid 6.OpenClient message: LAYER = (2)
> ORIGIN = (5) SEVERITY = (1) NUMBER = (140)

More info:

I can eventually get the problem set to load by playing around
with NULL (undef) column values. The difference from my initial
working sets seems to be that the working sets all loaded tables
with only varchar columns. This set has some numeric columns and
those appear to be the issue -- by setting those instead of passing
undef, I can get data to load. The columns are all defined as
nullable.

Even with that, trying to load to a table with varchar(max) results
in a memory fault/core dump. That is, I created a new test table
with varchar(max) replaced with varchar(2000). The varchar(2000)
version doesn't core dump but both tables fail if non-varchar
columns are given undef to load.

I read the docs again and they mention conversions and catching
those errors. I set that up but the errors all appear to be
bypassing that ... They all appear to be happening on commit. Still
playing around with error catching so I may have missed something
there.

If anything above triggers any thoughts or ideas, let me know.

--
Steve Sapovits steves06@comcast.net