0

I have a django function that calls a stored procedure in SQL Server 2008, via pyodbc as follows:

def new_user(self):
        # create a cursor
        cur = connection.cursor()
    try:
        cur.execute("{CALL sp_newPerson  (?,?,?,?,?)}",
                   (self.id, self.fname, self.lname, self.address))
        #returns 1 when there is a result set.
        #if cur.return_value == 1:        
        # grab the results
        results = cur.fetchall()
            return results
    finally:
            cur.close()

Whenever the function is called, I get "error_message": "not al l arguments converted during string formatting"

Muniu
  • 187
  • 1
  • 2
  • 15

1 Answers1

2

It seems that sp_newPerson() has 5 arguments (if I correctly counted all ?), but you tried to execute it with only 4 parameters, or it has 4 parameters, but you added one ? more:

cur.execute("{CALL sp_newPerson(?,?,?,?,?)}", (self.id, self.fname, self.lname, self.address, self.something))

or:

cur.execute("{CALL sp_newPerson(?,?,?,?)}", (self.id, self.fname, self.lname, self.address))

EDIT:

It seems that you are trying to use function or procedure with OUT parameters.

I don't have MS SQL to check if it works with Python ODBC prepared calls. Other databases have problems with it. Is is probably hard to write Python ODBC code to get return value or OUT parameter. From C or Delphi ODBC driver gets buffer allocated before executing such function/procedure so it can be filled. In Python I do not know if it is possible. I have found that PreparedStatements works in JDBC (I use it from Jython), even with JDBC-ODBC bridge, but JDBC uses specialized API to specify input and output parameters. See also my question about calling function from Oracle DB: How to retrieve result of Oracle database function via ODBC?

I think the best you can do is to use workaround. If sp_newPerson() is a function:

  1. Convert it into SELECT. It can not work in some databases, especially Oracle, when function change database (use INSERT, UPDATE or DELETE):

    cur.execute("SELECT sp_newPerson(?,?,?,?)", (self.id, self.fname, self.lname, self.address))
for row in cur.fetchall(): 
    r = row[0]

  2. Convert it into INSERT, of course you will have to create tmp table, clear it, etc.:

    r = cur.execute("INSERT INTO tmp (new_person_id) VALUES (sp_newPerson(?,?,?,?))", (self.id, self.fname, self.lname, self.address))
if r == 1:
    cur.execute("SELECT new_person_id FROM tmp")
    for row in cur.fetchall(): 
        r = row[0]
Community
  • 1
  • 1
Michał Niklas
  • 53,067
  • 18
  • 70
  • 114
  • Thanks for pointing that out Michal. I went ahead and executed without the extra arg and threw "error_message": "execute() takes at most 3 arguments (4 given)" I then stumbled on this http://msdn.microsoft.com/en-us/library/ms131685.aspx resource, and the following description: Input and input/output parameters can be omitted from procedure calls. If a procedure is called with parentheses but without any parameters, the driver instructs the data source to use the default value for the first parameter. For example: {call procedure_name()} – Muniu Sep 19 '12 at 07:26
  • Can you show your updated code? I think you wrongly call `execute()`, maybe there is comma in wrong place. In your example the 3 arguments should be: 1:self, 2:string with call and '?', 3:list of arguments. – Michał Niklas Sep 19 '12 at 07:51
  • Can you also show definition (just "prototype") of `sp_newPerson()` procedure? I would like to see its parameters. – Michał Niklas Sep 19 '12 at 07:52
  • `GO DECLARE @return_value int EXEC @return_value = [dbo].[sp_newPerson] @id = 23', @fname = N'John', @lname = N'Doe', @address = '134 Calloway' SELECT 'Return Value' = @return_value GO` – Muniu Sep 19 '12 at 10:59
  • I came across this http://stackoverflow.com/questions/7824161/passing-parameters-to-stored-procedures-using-pyodbc?rq=1 but results are the same in terms of error message produced – Muniu Sep 19 '12 at 11:26