'MAX' length VARBINARY for MSSQL

[Former user]

MSSQL provides a text 'MAX' keyword that specifies the maximum length of binary data in a column. However currently the length parameter to a VARBINARY can only be empty or a number. This means that the following code will not work:

t = Table('test', metadata, Column('data', VARBINARY(40))) t.data.cast(VARBINARY('MAX'))

As suggested here http://stackoverflow.com/questions/3068129/define-a-varbinarymax-column-using-sqlalchemy-on-ms-sql-server the addition in dialects\mssql\base.py adding of the following method to MSTypeCompiler solves this problem:

def visit_VARBINARY(self, type_): if type_.length == 'MAX': return "VARBINARY(MAX)" else: return "VARBINARY(%d)" % type_.length

[Former user]

Please note that this should also be done for {{{NVARCHAR(MAX)}}}. Also the {{{MAX}}} should support case-insensitive comparison.

[Mike Bayer]

just FYI we have a recipe for that here:

http://www.sqlalchemy.org/docs/reference/ext/compiler.html?highlight=compiler#changing-compilation-of-types

[Mike Bayer]

we've got most of this in 0.7, just need to add VARBINARY:

diff -r 4f1274fc1fc675e2a482b68d658b36597f243c31 lib/sqlalchemy/dialects/mssql/base.py
--- a/lib/sqlalchemy/dialects/mssql/base.py Wed Feb 02 19:03:20 2011 -0500
+++ b/lib/sqlalchemy/dialects/mssql/base.py Thu Feb 03 11:02:17 2011 -0500
@@ -586,6 +586,10 @@
         return self._extend("NVARCHAR", type_, 
                     length = type_.length or 'max')

+    def visit_VARBINARY(self, type_):
+        return "VARBINARY" + (type_.length and "(%d)" 
+                    % type_.length or "max")
+
     def visit_date(self, type_):
         if self.dialect.server_version_info < MS_2008_VERSION:
             return self.visit_DATETIME(type_)

[Mike Bayer]

bc52cae455577fec6d97a7273adb04d2a8c0fba6

[Mike Bayer]

Removing milestone: 0.7.0 (automated comment)