[Python] OceanBase (MySQL Mode) fails to execute stored procedure with dynamic SQL in python

Environmen

OceanBase Community Edition 4.2.1 (MySQL mode)

I'm trying to call an OceanBase stored procedure that contains dynamic SQL (PREPARE ... EXECUTE) from Python, but it fails with a vague error.

Here's my stored procedure in OceanBase:

DELIMITER //
CREATE PROCEDURE get_user_data(IN user_id INT)
BEGIN
SET @sql = CONCAT('SELECT * FROM users WHERE id = ', user_id);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;


When I call it from Python using pymysql, it raises an error:

import pymysql

conn = pymysql.connect(
host='127.0.0.1',
port=2881,
user='root@test',
password='',
database='test_db'
)

try:
with conn.cursor() as cursor:
cursor.callproc('get_user_data', (123,)) # Error occurs here
result = cursor.fetchall()
print(result)
finally:
conn.close()


Error Message

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your OceanBase server version for the right syntax to use near ''get_user_data' at line 1")


The stored procedure works fine when called directly in OceanBase CLI (CALL get_user_data(123).

I also tried with cursor.execute("CALL get_user_data(123)") instead of callproc() – same error.

Question

Why does OceanBase (in MySQL mode) reject this stored procedure call from Python, even though it works in the CLI? Is this a driver compatibility issue, or do I need to modify the procedure for Python execution?

Continue reading...