CODEWITHSUNDEEP
pythondjangosql-server-2008tastypiedjango-pyodbc
Muniu
Muniu

Reputation: 187

Django pyodbc Stored Procedure Error - not all arguments converted during string formatting

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"

Upvotes: 0

Views: 1701

Answers (1)

Michał Niklas
Michał Niklas

Reputation: 54342

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]

Upvotes: 2

Related Questions