- added pyodbc for sybase driver.

- generalized the "freetds" / "unicode statements" behavior of MS-SQL/pyodbc
into the base Pyodbc connector, as this seems to apply to Sybase as well.
- generalized the python-sybase "use autocommit for DDL" into the pyodbc
connector.  With pyodbc, the "autocommit" flag on connection is used,
as Pyodbc seems to have more database conversation than python-sybase that
can't otherwise be suppressed.
- Some platforms will now interpret certain literal values
as non-bind parameters, rendered literally into the SQL
statement.   This to support strict SQL-92 rules that are
enforced by some platforms including MS-SQL and Sybase.
In this model, bind parameters aren't allowed in the
columns clause of a SELECT, nor are certain ambiguous
expressions like "?=?".  When this mode is enabled, the base
compiler will render the binds as inline literals, but only across
strings and numeric values.  Other types such as dates
will raise an error, unless the dialect subclass defines
a literal rendering function for those.  The bind parameter
must have an embedded literal value already or an error
is raised (i.e. won't work with straight bindparam('x')).
Dialects can also expand upon the areas where binds are not
accepted, such as within argument lists of functions
(which don't work on MS-SQL when native SQL binding is used).

1 files changed, 38 insertions, 12 deletions

diff --git a/test/sql/test_query.py b/test/sql/test_query.py
index a189594b7..8664ba6dc 100644
--- a/test/sql/test_query.py
+++ b/test/sql/test_query.py
@@ -234,8 +234,9 @@ class QueryTest(TestBase):
     def test_order_by_label(self):
         """test that a label within an ORDER BY works on each backend.
         
-        simple labels in ORDER BYs now render as the actual labelname 
-        which not every database supports.
+        This test should be modified to support [ticket:1068] when that ticket
+        is implemented.  For now, you need to put the actual string in the
+        ORDER BY.
         
         """
         users.insert().execute(
@@ -246,26 +247,30 @@ class QueryTest(TestBase):
         
         concat = ("test: " + users.c.user_name).label('thedata')
         eq_(
-            select([concat]).order_by(concat).execute().fetchall(),
+            select([concat]).order_by("thedata").execute().fetchall(),
             [("test: ed",), ("test: fred",), ("test: jack",)]
         )
         
         eq_(
-            select([concat]).order_by(concat).execute().fetchall(),
+            select([concat]).order_by("thedata").execute().fetchall(),
             [("test: ed",), ("test: fred",), ("test: jack",)]
         )
 
         concat = ("test: " + users.c.user_name).label('thedata')
         eq_(
-            select([concat]).order_by(desc(concat)).execute().fetchall(),
+            select([concat]).order_by(desc('thedata')).execute().fetchall(),
             [("test: jack",), ("test: fred",), ("test: ed",)]
         )
 
-        concat = ("test: " + users.c.user_name).label('thedata')
-        eq_(
-            select([concat]).order_by(concat + "x").execute().fetchall(),
-            [("test: ed",), ("test: fred",), ("test: jack",)]
-        )
+        @testing.fails_on('postgresql', 'only simple labels allowed')
+        @testing.fails_on('sybase', 'only simple labels allowed')
+        def go():
+            concat = ("test: " + users.c.user_name).label('thedata')
+            eq_(
+                select([concat]).order_by(literal_column('thedata') + "x").execute().fetchall(),
+                [("test: ed",), ("test: fred",), ("test: jack",)]
+            )
+        go()
         
         
     def test_row_comparison(self):
@@ -768,11 +773,18 @@ class QueryTest(TestBase):
         assert len(r) == 0
 
     @testing.emits_warning('.*empty sequence.*')
-    @testing.fails_on('firebird', "kinterbasdb doesn't send full type information")
+    @testing.fails_on('firebird', "uses sql-92 rules")
+    @testing.fails_on('sybase', "uses sql-92 rules")
     @testing.fails_if(lambda: 
                          testing.against('mssql+pyodbc') and not testing.db.dialect.freetds,
-                         "not supported by Windows ODBC driver")
+                         "uses sql-92 rules")
     def test_bind_in(self):
+        """test calling IN against a bind parameter.
+        
+        this isn't allowed on several platforms since we
+        generate ? = ?.
+        
+        """
         users.insert().execute(user_id = 7, user_name = 'jack')
         users.insert().execute(user_id = 8, user_name = 'fred')
         users.insert().execute(user_id = 9, user_name = None)
@@ -784,7 +796,21 @@ class QueryTest(TestBase):
         assert len(r) == 3
         r = s.execute(search_key=None).fetchall()
         assert len(r) == 0
+    
+    @testing.emits_warning('.*empty sequence.*')
+    def test_literal_in(self):
+        """similar to test_bind_in but use a bind with a value."""
+
+        users.insert().execute(user_id = 7, user_name = 'jack')
+        users.insert().execute(user_id = 8, user_name = 'fred')
+        users.insert().execute(user_id = 9, user_name = None)
 
+        s = users.select(not_(literal("john").in_([])))
+        r = s.execute().fetchall()
+        assert len(r) == 3
+        
+        
+        
     @testing.emits_warning('.*empty sequence.*')
     @testing.fails_on('firebird', 'FIXME: unknown')
     @testing.fails_on('maxdb', 'FIXME: unknown')