krauzer2 / django-mssql Goto Github PK

What steps will reproduce the problem?
1. Create a model with a DateTimeField
2. Sync the DB
3. Try a query such as:
Entry.objects.filter(pub_date__year=2008)


What is the expected output? What do you see instead?

The generated query contains the following WHERE clause:
"BETWEEN '2008-01-01 00:00:00' and '2008-12-31 23:59:59.999999'"
I did some research and found that (at least on SQL Server 2005) the time
precision is not this exact. SQL server only accepts millisecond values
with 3 decimal place or less. If I change the second datetime value to
'2008-12-31 23:59:59.999' the query works fine.


What version of the product are you using? On what operating system?
SVN rev 126
Windows XP Pro/ SQL Server 2005

Please provide any additional information below.

http://msdn.microsoft.com/en-us/library/ms187819(SQL.90).aspx

Need to add support for detecting auto integer primary keys, probably
boring the "get_description" modifications from an existing patch.

I have a sneaking suspicion that I have an off-by-one style error in the
max_digits/decimal_places handling for DecimalFields.

Or more accurately, that the code that crams Decimals into SQL Server
parameters is setting the length/precision fields wrong.

Add some tests and fix if needed.

DecimalField does not return a Decimal python type but a unicode object.
DecimalField does not save numbers to the right of the decimal point.

I have attached some testcases (as a patch for
tests\test_main\regressiontests\models.py and as testdecimal.py), and a
patch for dbapi.py

Passing in a None query parameter breaks in the underlying ADO
implementation, as the query parameter type can't be inferred.

When saving a decimal that ends with one or more zeros (before the point or
comma), it leads to an error.

I think it has to do with the normalization of the python decimal type and
converting it back to a string:

import decimal
value = d = decimal.Decimal('450.00')
s = str(value.normalize())
print s
#s is now '4.5E+2' instead of '450'

In the patch the trailing zero's are removed on the string value instead of
the decimal value.

Missing pywintypes import, which is used for pywintypes.com_error in
Cursor.nextset().

Also added to "except" in Cursor._execute_command().

Django's test suite won't run w/ django-mssql as the backend.

Currently failing trying to create ``model_inheritence_student `` with:
``There is already an object named 'CK_int_pos_age_254' in the database.``

What steps will reproduce the problem?
1. enable sessions and auth in django middleware
2. try manage.py syncdb on a fresh db (with no superuser yet defined)
3. or try any other SELECT/INSERT using python datetime.datetime parameters
(i.e. setting a session coockie)

What is the expected output? What do you see instead?
Instead of reading/writing the dbtable, you get the error: 
'Conversion failed when converting character string to smalldatetime data
type. (see the following traceback)

The cause is that the datetime parameters passed to datetime/smalldatetime
mssql fileds are like '2008-03-09 17:45:10.156000', but mssql (at least
here on mssql2005) expect maximum tre decimal digit on seconds.
If you use '2008-03-09 17:45:10.156', everything is OK.

here is an example traceback:

ADODBAPI Traceback (most recent call last): File
"c:\Python25\lib\site-packages\django\db\backends\sqlserver_ado\adodb_django.py"
,
line 629, in _executeHelper adoRetVal=self.cmd.Execute() File "<COMObject
ADODB.Command>", line 3, in Execute File
"C:\Python25\Lib\site-packages\win32com\client\dynamic.py", line 258, in
_ApplyTypes_ result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags,
retType, argTypes) + args) com_error: (-2147352567, 'Exception occurred.',
(0, 'Microsoft OLE DB Provider for SQL Server', 'Conversion failed when
converting character string to smalldatetime data type.', None, 0,
-2147217913), None) -- on command: "SELECT
[django_session].[session_key],[django_session].[session_data],[django_session].
[expire_date]
FROM [django_session] WHERE ([django_session].[session_key] = ? AND
[django_session].[expire_date] > ?)" -- with parameters:
['f45482c62c07c46b95d96027ccac73f2', u'2008-03-09 17:45:10.156000']

What version of the product are you using? On what operating system?
Django/0.97-pre-SVN-unknown/sqlserver_ado as checked out from here.
Connecting to a mssql2005 from django development http server
Platform: MS Windows Server 2003R2 Enterprise edition SP2

Please provide any additional information below.

Django pass to sqlserver_ado a lot of string generated from
datetime.datetime python objects and these strings all have microseconds in
range(1000000).

One solutions would be the following:
do a regex an all string parameters inside sqlserver_ado (probably in
adodb_django.py arounf line 566 in _executeHelper method) to strip
microsecond values in datetime.datetime strings.

Any other solutions possible?

Regards,

__peppo

Investigate this submitted null handling patch.

http://code.djangoproject.com/ticket/5461

The above ticket details moving "creation.py" into a "BaseCreation" class.
When this ticket lands, this backend will need to be updated.

Hi flangy,
do you have any idea on this strange behavior?

def getAziNames(settore, compartoId, lettera):
    settore=models.LSettori.objects.get(nome=settore)
    comparto=models.LComparti.objects.get(id=compartoId, 
    settore=settore)
    aziende=models.Azienda.objects.filter(settore=settore,
    comparto=comparto,
    anagrafica__ragSoc__istartswith=lettera)
    res=[]
    for azienda in aziende:
        print azienda.anagrafica.pk

If I call the above method I get the error:

(-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider for SQL 
Server', 'Cannot create new connection because in manual or distributed 
transaction mode.', None, 0, -2147467259), None)
....

To have everything working as expected I need to avaluate the query set 
with an explicit aziende=list(aziende) before the loop.

Does this means that the iterator protocol on querysets does not works with 
foreign fiels?

This is definitely not a problem because the solution is easy, but probably 
needs to be documented somewhere. Or maybe I am just missing something...

I am on Django 1.0/django-mssql-r-156/MSSQL 2005 (cluster)

Thanks,

__peppo 

SQL Server 2008 adds some new datatypes, DATE (without time) for instance:
http://www.sqlteam.com/article/using-the-date-data-type-in-sql-server-2008

Investigate options for providing optional SQL Server 2008 support in the
introspection module. By default, though, we should target 2005/2000.

Reported in email, the return values for Stored Procedures are not being 
returned correctly.

Waiting for repro case. 

This backend is currently using IDENTITY columns for primary
key/auto-increment fields.

SQL Server does not allow values to be inserted into these fields unless
"INSERT_IDENTITY" is ON; values for these fields are provided by
dumpdata/loaddata fixtures.

Either need to bracket a loaddata with SET INSERT_IDENTITY commands or find
some other fix.

Paging support will not work if the query selects two columns with the same
name, even if they are from separate tables.

For instance:
select [a].[b], [a].[b_id], [b].[b] from [a] inner join [b] on
[b].[id]=[a].[b_id]
will fail on the 2 columns named [b].

What steps will reproduce the problem?
1. manage.py syncdb


Attached is screenshot of trace (copy/paste didn't work)

What version of the product are you using? On what operating system?

django svn 7888

Attached is a diff resolving the issue. 

What steps will reproduce the problem?
1. Create a name = CharField(max_lenght=100)
2. Save a record with a "\" in it ex. "c:\windows\file.ext"
3. Search filter(name__contains="""c:\windows\file.ext""") or
filter(name__endswith="""ws\file.ext""")


What is the expected output? What do you see instead?
No results.
On sqlprofile there is a "\\" in query 


What version of the product are you using? On what operating system?
Trunk version of mssql

The offsets used when slicing queries are off-by-one; need to write the
slicing tests with the real expected results, and verify w/ paging tests.

Like DateTime fields, Django flattens out Decimal field values to strings
before passing them to the database layer. MS SQL doesn't like accepting a
string value for a numeric paramter.

The Real Ultimate Fix would be for Django to pass the decimal to the
backends to deal with, but possibly a workaround can be found in the meantime.

SQL Server has no built-in support for regex matching. However, there are
several add-in libraries for SQL Server 2000 and up that provide this,
ranging from .NET/CLR implementations to ones that use the VBScript RegExp
object.

This backend should be configurable to allow an (as-yet undecided) optional
Regex library to be used.

Replace INFORMATION_SCHEMA based queries with sql-server specific metadata
views and sprocs.

Need to add support for filtering Decimals w/ strings:
http://code.djangoproject.com/changeset/8143

django-pyodbc renamed their backend's module to "sql_server.pyodbc".

That makes a lot of sense, when I think about it, so I'm considering
renaming this backend's module to "sql_server.ado".

Haven't decided on anything, but feel free to "vote" in the comments.

If there are two positive integer columns with the same name in database,
the creation process will fail because of the constrain key CK_int_pos_...
conflict.

You should change the creation.py from:
        'PositiveIntegerField': 'int CONSTRAINT [CK_int_pos_%(column)s]
CHECK ([%(column)s] >= 0)',
        'PositiveSmallIntegerField': 'smallint CONSTRAINT
[CK_smallint_pos_%(column)s] CHECK ([%(column)s] >= 0)',

to:

        'PositiveIntegerField': 'int CONSTRAINT
[CK_int_pos_%(column)s%(creation_counter)d] CHECK ([%(column)s] >= 0)',
        'PositiveSmallIntegerField': 'smallint CONSTRAINT
[CK_smallint_pos_%(column)s%(creation_counter)d] CHECK ([%(column)s] >= 0)',

to avoid this problem.

What steps will reproduce the problem?
1. Create a model with a pk or table name matching a reserved word.
2. Add an item.
3. Attempt to view the detailed page in auto admin.

What version of the product are you using? On what operating system?

v24

Please provide any additional information below.

DatabaseOperations.last_insert_id() was the faulting function. The vars
pk_name and table_name were not being quoted when building the SQL.
Attached is a diff.

What steps will reproduce the problem?
--------------------------------------
1. Installed latest versions of SVN versions of django and django-mssql
installed. created simple model using
PhoneNumberField,BooleanField,CharField,TextField and added 1 row to the
model. Clicking the 'History' button in the Admin edit record page
generates a database error.

What is the expected output? What do you see instead?
-------------------------------------------------------
DatabaseError at /admin/ps_addbook/customer/1/history/

--ADODBAPI Traceback (most recent call last): File
"C:\Python\lib\site-packages\django\db\backends\sqlserver_ado\adodb_django.py",
line 640, in _executeHelper adoRetVal=self.cmd.Execute() File "<COMObject
ADODB.Command>", line 3, in Execute File
"C:\Python\lib\site-packages\win32com\client\dynamic.py", line 258, in
_ApplyTypes_ result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags,
retType, argTypes) + args) com_error: (-2147352567, 'Exception occurred.',
(0, 'Microsoft OLE DB Provider for SQL Server', 'The data types text and
nchar are incompatible in the equal to operator.', None, 0, -2147217900),
None) -- on command: "SELECT
[django_admin_log].[id],[django_admin_log].[action_time],[django_admin_log].[use
r_id],[django_admin_log].[content_type_id],[django_admin_log].[object_id],[djang
o_admin_log].[object_repr],[django_admin_log].[action_flag],[django_admin_log].[
change_message],[auth_user].[id],[auth_user].[username],[auth_user].[first_name]
,[auth_user].[last_name],[auth_user].[email],[auth_user].[password],[auth_user].
[is_staff],[auth_user].[is_active],[auth_user].[is_superuser],[auth_user].[last_
login],[auth_user].[date_joined]
FROM [django_admin_log] , [auth_user] WHERE
([django_admin_log].[content_type_id] = ? AND
[django_admin_log].[object_id] = ?) AND [django_admin_log].[user_id] =
[auth_user].[id] ORDER BY [django_admin_log].[action_time] ASC" -- with
parameters: [9, u'1']

Request Method:     GET

Request URL:    http://footprints:8080/admin/ps_addbook/customer/1/history/

Exception Type:     DatabaseError

Exception Value:    --ADODBAPI Traceback (most recent call last): File
"C:\Python\lib\site-packages\django\db\backends\sqlserver_ado\adodb_django.py",
line 640, in _executeHelper adoRetVal=self.cmd.Execute() File "<COMObject
ADODB.Command>", line 3, in Execute File
"C:\Python\lib\site-packages\win32com\client\dynamic.py", line 258, in
_ApplyTypes_ result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags,
retType, argTypes) + args) com_error: (-2147352567, 'Exception occurred.',
(0, 'Microsoft OLE DB Provider for SQL Server', 'The data types text and
nchar are incompatible in the equal to operator.', None, 0, -2147217900),
None) -- on command: "SELECT
[django_admin_log].[id],[django_admin_log].[action_time],[django_admin_log].[use
r_id],[django_admin_log].[content_type_id],[django_admin_log].[object_id],[djang
o_admin_log].[object_repr],[django_admin_log].[action_flag],[django_admin_log].[
change_message],[auth_user].[id],[auth_user].[username],[auth_user].[first_name]
,[auth_user].[last_name],[auth_user].[email],[auth_user].[password],[auth_user].
[is_staff],[auth_user].[is_active],[auth_user].[is_superuser],[auth_user].[last_
login],[auth_user].[date_joined]
FROM [django_admin_log] , [auth_user] WHERE
([django_admin_log].[content_type_id] = ? AND
[django_admin_log].[object_id] = ?) AND [django_admin_log].[user_id] =
[auth_user].[id] ORDER BY [django_admin_log].[action_time] ASC" -- with
parameters: [9, u'1']

Exception Location: 
C:\Python\lib\site-packages\django\db\backends\sqlserver_ado\adodb_django.py
in standardErrorHandler, line 61

Python Executable:  C:\Python\python.exe

Python Version:     2.5.2

Python Path:    ['D:\\Django\\testproject',
'C:\\Python\\lib\\site-packages\\docutils-0.4-py2.5.egg',
'C:\\Python\\DLLs\\Http', 'D:\\Django\\testproject',
'C:\\WINDOWS\\system32\\python25.zip', 'C:\\Python\\DLLs',
'C:\\Python\\lib', 'C:\\Python\\lib\\plat-win', 'C:\\Python\\lib\\lib-tk',
'C:\\Python', 'C:\\Python\\lib\\site-packages',
'C:\\Python\\lib\\site-packages\\win32',
'C:\\Python\\lib\\site-packages\\win32\\lib',
'C:\\Python\\lib\\site-packages\\Pythonwin']


What version of the product are you using? On what operating system?
--------------------------------------------------------------------
Windows 2003, SQL Server 2005


Please provide any additional information below.
I'm not entirely sure this is django-mssql related but thought I'd start
here, the problem seems to be that when authentication is enabled, a table
called django_admin_log is created with a field object_id set as text. I
modified the table to change object_id from text to int and the queries
work as expected now.

The Queryset-refactor branch of Django has landed on trunk, so need to make
sure that this backend still works, and see what new features (if any) I
can take advantage of.

Also, regenerate any necessary patches against a more recent svn tag.

If you have a query, for example:

"INSERT INTO mytable (mycharfield1, mycharfield2, myintfield) VALUES (%s,
%s, %s)" with params (None, "text", 1)

then executeHelper will convert this into:

"INSERT INTO mytable (mycharfield1, mycharfield2, myintfield) VALUES (NULL,
?, ?)", with input_params = ((1, "text"), (2, 1))

However it then tries to put "text" into cmd.Parameters(1) instead of
Paramters(0), because the None was not added as a paramter. ie the indexes
in input_params are wrong.

So the command fails with:
Converting Parameter #1: adInteger, u'text'

The problem is that in the first parameters loop (dbapi.py line 374), we
are skipping the Null parameters, but in the second loop (line 390), we are
using "i" as the index into cmd.Parameters, when "i" could skip some numbers.

Might be easier just to check the attached patch to see what I mean :)

Using rev.85 on Windows Server 03

The ADODBI code this project forked from has some conversion logic for
decimal types (currency and floats) that tries to convert values, and if
that fails, converts to a string and replaces commas (euro) with points
(US) for the decimal separator.

I'm not sure if this code is actually needed, as I haven't dug into it, and
certainly don't have my locale set non-US ever.

I thinking, or at least hoping, that its dead code and can be removed, but
some tests will need to be added to make sure.

http://code.djangoproject.com/ticket/7805

If the above ticket goes in, need to remove the "ImageField" definition
from creation.py

What steps will reproduce the problem?

Test attached


Error:
DatabaseError: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB
Provider for SQL Server', "Invalid column name alias_for_a'.", None, 0,
-2147217900), None)

Generated Query:
SELECT _row_num, [alias_for_a], [id], [a] FROM ( SELECT ROW_NUMBER() OVER (
ORDER BY [alias_for_a] ASC) as _row_num, (a) AS [alias_for_a],
[regressiontests_paginatortable].[id], [regressiontests_paginatortable].[a]
FROM [regressiontests_paginatortable] ) as QQQ where 1 < _row_num and
_row_num <= 2

Inserting two records w/ the same primary key should raise an Integrity 
Error instead of a more general Database Error.

Need to add the DatabaseOperations to support this Django change:
http://code.djangoproject.com/changeset/8131

The logic to find the "column name" when aliasing paginated queries breaks
on columns like this:

(SELECT Organization.Name FROM Organization WHERE
Organization.OrganizationID = IP.OrganizationID) AS [organization_name]

Need smarter logic to handle [table].[column] as well as "AS"'d things.

The ADO driver this backend uses has wide support for possible ADO 
datatypes. SQL Server itself maps to only a subset of these types.

Since this backend is SQL Server specific, and not ADO general (ie, we 
don't support Access as a backend), consider removing non-SQL Server 
datatypes.

These other datatypes aren't being tested, so they probably aren't being 
converted properly, and lead to some confusion in the code (decimal? money? 
currency?)

(The original ADODBAPI project should be used for wide-ranging ADO database 
support in Python.)

From Cedric Collins:

I just ran into an issue today trying to use syncdb to create a table for a
model with a OneToOneField where I specified primary_key=True. I believe
OneToOneFields were primary keys by default before qs-refactor. Now, it is
passed in as an option, but unique is set by default instead. The option
'allows_unique_and_pk = False' must be set in DatabaseFeatures or both
conditions are used when creating the field, which causes a SQL Server error.

What steps will reproduce the problem?
1. Create a table name with a space inside
2. python manage.py inspectdb
3. kill Bill Gates

This is the output with a table named Ricerca Identificatori:

class Ricerca Identificatori(models.Model):
Traceback (most recent call last):
  File "manage.py", line 11, in <module>
    execute_manager(settings)
  File "C:\Work\Guttadauro\Map\map\lib\django\core\management\__init__.py",
line 272, in execute_manager
    utility.execute()
  File "C:\Work\Guttadauro\Map\map\lib\django\core\management\__init__.py",
line 219, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "C:\Work\Guttadauro\Map\map\lib\django\core\management\base.py",
line 72, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "C:\Work\Guttadauro\Map\map\lib\django\core\management\base.py",
line 86, in execute
    output = self.handle(*args, **options)
  File "C:\Work\Guttadauro\Map\map\lib\django\core\management\base.py",
line 168, in handle
    return self.handle_noargs(**options)
  File
"C:\Work\Guttadauro\Map\map\lib\django\core\management\commands\inspectdb.py",
line 10, in handle_noargs
    for line in self.handle_inspection():
  File
"C:\Work\Guttadauro\Map\map\lib\django\core\management\commands\inspectdb.py",
line 38, in handle_inspection
    relations = introspection_module.get_relations(cursor, table_name)
  File
"C:\Work\Guttadauro\Map\map\lib\django\db\backends\sqlserver_ado\introspection.p
y",
line 19, in get_relations
    source_field_dict = _name_to_index(cursor, table_name)
  File
"C:\Work\Guttadauro\Map\map\lib\django\db\backends\sqlserver_ado\introspection.p
y",
line 16, in _name_to_index
    return dict([(d[0], i) for i, d in
enumerate(get_table_description(cursor, table_name))])
  File
"C:\Work\Guttadauro\Map\map\lib\django\db\backends\sqlserver_ado\introspection.p
y",
line 8, in get_table_description
    cursor.execute("SELECT * FROM %s where 1=0" % (table_name))
  File "C:\Work\Guttadauro\Map\map\lib\django\db\backends\util.py", line
18, in execute
    return self.cursor.execute(sql, params)
  File
"C:\Work\Guttadauro\Map\map\lib\django\db\backends\sqlserver_ado\adodb_django.py
",
line 701, in execute
    self._executeHelper(operation,False,parameters)
  File
"C:\Work\Guttadauro\Map\map\lib\django\db\backends\sqlserver_ado\base.py",
line 49, in _executeHelper
    Database.Cursor._executeHelper(self, sql, isStoredProcedureCall,
parameters)
  File
"C:\Work\Guttadauro\Map\map\lib\django\db\backends\sqlserver_ado\adodb_django.py
",
line 652, in _executeHelper
    self._raiseCursorError(DatabaseError,tracebackhistory)
  File
"C:\Work\Guttadauro\Map\map\lib\django\db\backends\sqlserver_ado\adodb_django.py
",
line 457, in _raiseCursorError
    eh(self.conn,self,errorclass,errorvalue)
  File
"C:\Work\Guttadauro\Map\map\lib\django\db\backends\sqlserver_ado\adodb_django.py
",
line 59, in standardErrorHandler
    raise errorclass(errorvalue)
django.db.backends.sqlserver_ado.adodb_django.DatabaseError:
--ADODBAPI
Traceback (most recent call last):
   File
"C:\Work\Guttadauro\Map\map\lib\django\db\backends\sqlserver_ado\adodb_django.py
",
line 638, in _executeHelper
    adoRetVal=self.cmd.Execute()
   File "<COMObject ADODB.Command>", line 3, in Execute
   File "C:\PYTHON25\lib\site-packages\win32com\client\dynamic.py", line
258, in _ApplyTypes_
    result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType,
argTypes) + args)
 com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB
Provider for SQL Server', "Invalid object name 'RICERCA'.", None, 0, -214721786
5), None)
-- on command: "SELECT * FROM RICERCA IDENTIFICATORI where 1=0"
-- with parameters: ()

I added a "order_with_respect_to" field to a model's meta class, and
queries on that table broke in a weird way.

Either "order_with_respect_to" isn't working with this backend, or there is
some other trick I'm missing.

Using .extra(select={...}) on a query adds more columns to the results than
the number of fields in the model.

This is getting picked up by django-mssql's pagination implementation, as
it's looking for > # fields to know when to strip off the "row_number" value.

Need to rewrite pagination support to not rely on the # of columns to know
when to trigger.

SQL Server doesn't support "SELECT ... LIMIT ... OFFSET ..." syntax; it has
a "SELECT TOP ..." form.

LIMIT/OFFSET can be supported by converting to a comparable TOP, and
slicing off enough rows before returning.

Use code based on this ticket:
http://code.djangoproject.com/ticket/1429

What steps will reproduce the problem?
1. manage.py syncdb
2. Create a view and matching model
3. manage.py syncdb

What is the expected output? What do you see instead?

It should not try to create a table for the view. It throws a name conflict
exception

Attached is a patch to include View names in
DatabaseIntrospection.gettable_list()