0

I am using pymssql for Python 2.7 with MS SQL Driver. While running some queries on tables having sql_variant column, I am getting bad data. When I cast data, I am able to get correct values. Is there any way I can skip explicit CAST.

sam
  • 11
  • 1
  • 5
  • With python based adodb api, this explicit cast is not required. But due to some constraints on using adodb api, I am trying PyMSMSql. Is this casting of sql_variant column always required? Please guide as I am new to pymssql. – sam Dec 20 '16 at 06:25

1 Answers1

0

Was fighting the same issue for some time now, but I haven't found any fix yet. CAST seems the only thing to work, so I had to re-write my SQLs into something like:

sql = 'SELECT *, CAST (sql_varian_field AS BIGINT) as my_field_fixed FROM myTable'

and just refering to the last column there.

Although pymssql makes it possible to add some sql when connecting in conn_properties:

pymssql.connect(server='.', user='', password='', database='', timeout=0, login_timeout=60, charset='UTF-8', as_dict=False, host='', appname=None, port='1433', conn_properties, autocommit=False, tds_version='7.1')

Is there any SQL specialist that can answer the following question: is there any simple SQL command that would make the DB handle all sql_variant fields as some pre-defined types for entire session? Something that could be passes as conn_propeties any time a pymssql.connect is called?

zborecque
  • 391
  • 1
  • 3
  • 6