使用Django-MSSQL调用存储过程

Question

我正在尝试在与我合作的多数据库项目上调用存储过程。我有2个存储过程可以正常工作，但是此特定的存储过程不起作用并返回错误：

Traceback (most recent call last):
File "d:ProjectsDjangoAAAVirtualEnvlibsite-packagesdjangocorehandlersexception.py", line 39, in inner
response = get_response(request)
File "d:ProjectsDjangoAAAVirtualEnvlibsite-packagesdjangocorehandlersbase.py", line 187, in _get_response
response = self.process_exception_by_middleware(e, request)
File "d:ProjectsDjangoAAAVirtualEnvlibsite-packagesdjangocorehandlersbase.py", line 185, in _get_response
response = wrapped_callback(request, *callback_args, **callback_kwargs)
File "d:ProjectsDjangoAAAVirtualEnvlibsite-packagesdjangoviewsdecoratorscsrf.py", line 58, in wrapped_view
return view_func(*args, **kwargs)
File "d:ProjectsDjangoAAAVirtualEnvlibsite-packagesdjangoviewsgenericbase.py", line 68, in view
return self.dispatch(request, *args, **kwargs)
File "d:ProjectsDjangoAAAVirtualEnvlibsite-packagesrest_frameworkviews.py", line 483, in dispatch
response = self.handle_exception(exc)
File "d:ProjectsDjangoAAAVirtualEnvlibsite-packagesrest_frameworkviews.py", line 443, in handle_exception
self.raise_uncaught_exception(exc)
File "d:ProjectsDjangoAAAVirtualEnvlibsite-packagesrest_frameworkviews.py", line 480, in dispatch
response = handler(request, *args, **kwargs)
File "d:ProjectsDjangoHRSyshrshrs_apiviews.py", line 79, in post
result_set = cursor.fetchall()
File "d:ProjectsDjangoAAAVirtualEnvlibsite-packagesdjangodbutils.py", line 101, in inner
return func(*args, **kwargs)
File "d:ProjectsDjangoAAAVirtualEnvlibsite-packagesdjangodbutils.py", line 94, in __exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
File "d:ProjectsDjangoAAAVirtualEnvlibsite-packagesdjangoutilssix.py", line 685, in reraise
raise value.with_traceback(tb)
File "d:ProjectsDjangoAAAVirtualEnvlibsite-packagesdjangodbutils.py", line 101, in inner
return func(*args, **kwargs)
File "d:ProjectsDjangoAAAVirtualEnvlibsite-packagessqlserver_adodbapi.py", line 719, in fetchall
return self._fetch()
File "d:ProjectsDjangoAAAVirtualEnvlibsite-packagessqlserver_adodbapi.py", line 671, in _fetch
self._raiseCursorError(FetchFailedError, 'Attempting to fetch from a closed connection or empty record set')
File "d:ProjectsDjangoAAAVirtualEnvlibsite-packagessqlserver_adodbapi.py", line 488, in _raiseCursorError
eh(self.connection, self, errorclass, errorvalue)
File "d:ProjectsDjangoAAAVirtualEnvlibsite-packagessqlserver_adodbapi.py", line 103, in standardErrorHandler
raise errorclass(errorvalue)
django.db.utils.Error: Attempting to fetch from a closed connection or empty record set

这就是我称为存储过程的方式：

cursor = connections['backend'].cursor()
    try:
        cursor.callproc('[dbo].[GetLogonControlJSON]', ['10011', 'encrypted', '127.0.0.1'])
        result_set = cursor.fetchall()
    finally:
        cursor.close()

我也这样做了，但是我仍然有同样的错误：

cursor = connections['backend'].cursor()
try:
    cursor.execute('EXEC [dbo].[GetLogonControlJSON] @LogonID=%s, @Password=%s, @IP=%s' % ('10011', 'encrypted', '127.0.0.1'))
    result_set = cursor.fetchall()
finally:
    cursor.close()

请帮助我。：(

谢谢！

Answer 1

在这种情况下，您应该使用execute()方法而不是callproc()。

错误消息告诉您一切：

尝试从封闭的连接或空记录集获取

看文档(第二个示例(：存储过程