DBAPI: Is it possible to execute procedure with parameters?

[flakobatako]

I' m trying to execute a Stored procedure like this: cur.execute("EXECUTE PROCEDURE sp_test(?)", ("HELLO",))

But i'm getting this error: ifx_pydb::DatabaseError: Exception('Binding Error: [Informix][Informix ODBC Driver]Invalid application buffer type. SQLCODE=-11116')

I also tried the method (as per the https://www.python.org/dev/peps/pep-0249/#callproc): cur.callproc("sp_test", ("TEST",))

Right now the only way i can execute it is like this: param = "HELLO" cur.execute("EXECUTE PROCEDURE sp_test('"+param+"')")

But is not a very good practice to do it like that.

Is there even a way to call an IFX sp with parameters?

The sp param's signature is like this: CREATE PROCEDURE sp_test(greeting CHAR(5))

Thank you in advance.

[jleffler]

You should be able to execute the procedure with placeholder for a host variable in the argument list in much the same you could work with an UPDATE or SELECT statement with placeholders. You'd probably need to know whether the procedure returns anything. If it does, treat it like a SELECT statement; if not, treat it like an UPDATE or DELETE statement.

What that means in detail, I'm not quite sure, but the principle should be sound. (If someone contradicts me, they may be right, though.)

On Thu, Sep 19, 2019 at 2:29 PM flakobatako notifications@github.com wrote:

I' m trying to execute a Stored procedure like this: cur.execute("EXECUTE PROCEDURE sp_test(?)", ("HELLO",))

But i'm getting this error: ifx_pydb::DatabaseError: Exception('Binding Error: [Informix][Informix ODBC Driver]Invalid application buffer type. SQLCODE=-11116')

I also tried the method (as per the https://www.python.org/dev/peps/pep-0249/#callproc): cur.callproc("sp_test", ("TEST",))

Right now the only way i can execute it is like this: param = "HELLO" cur.execute("EXECUTE PROCEDURE sp_test('"+param+"')")

But is not a very good practice to do it like that.

Is there even a way to call an IFX sp with parameters?

The sp param's signature is like this: CREATE PROCEDURE sp_test(greeting CHAR(5))

Thank you in advance.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/OpenInformix/IfxPy/issues/25?email_source=notifications&email_token=ACCAHBSKBZA7KZENIISHF43QKPVL7A5CNFSM4IYQH4FKYY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4HMQ44LQ, or mute the thread https://github.com/notifications/unsubscribe-auth/ACCAHBVAKIN4NC6T5SUDXO3QKPVL7ANCNFSM4IYQH4FA .

-- Jonathan Leffler jonathan.leffler@gmail.com #include Guardian of DBD::Informix - v2018.1031 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused."