CODEWITHSUNDEEP
pythonpython-3.xfirebirdfirebird2.5
aziannomness
aziannomness

Reputation: 92

How to properly call UDF (external function) from Firebird database using Python fdb

I am a beginner programmer (actually automated QA tester) working on an internal project (specific to my work) where I am trying to use a UDF (external function aka User Defined Function) that is stored in a Firebird database (I hope I phrased all that correctly).

I'm interfacing to the Firebird database using the fdb driver for Python, I can connect, run basic SQL statements, but when I run the following statement the UDF returns 0 instead of the expected value.

cur.execute("INSERT INTO temp (column1,column2) VALUES(f_udfName(?),?)",(int(line.rstrip('\n')),line.rstrip('\n')))

I'm traversing through a file and each line of the file is an 11-digit number. I am wanting to insert the original number into column1 and the return value of the UDF into column2 of the table temp. When I hit this line of code it completes successfully, but I get a return value of 0 instead of 9-digit number.

I have googled everything I could think of to figure this out. If someone would be able to point me in the correct direction at least, I would greatly appreciate it.

I am using Python 3.4, most recent fdb version, and Firebird 2.5.

EDIT: If I run the code from the python command line and do not parametrize the statement the UDF returns the expected value. Example: 

cur.execute("INSERT INTO temp (column1,column2) VALUES(f_udfName(12345678901),12345678901)")

Upvotes: 2

Views: 1144

Answers (1)

aziannomness
aziannomness

Reputation: 92

As recommended by Mark Rotteveel, changing the SQL statement to CAST the parameter as a BIGINT resolved the problem.

cur.execute("INSERT INTO temp (column1,column2) VALUES(f_udfName(CAST(? AS BIGINT)),?)",(int(line.rstrip('\n')),line.rstrip('\n'))

Attempting to use the long(..) function in Python 3.4 did not work because as of Python 3.0.1 the long(..) function was merged into the int(..) function. Attempting to use int(..) will result in a return value of 0 without the CAST statement.

Upvotes: 2

Related Questions