Description:
When calling a Stored Procedure with the database name (<database>.<procedure_name> it fails because the database name is used to generate SET commands for the variables. The raw database name is used in the variable name and MySQL does not support dots in the argument names.
The error returned is:
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.GetValueAndTimestamp_arg1' at line 1
MySQL itself does support calling procedures with their full name (<database>.<procedure_name>) so believe this should be possible from the Python connector also.
How to repeat:
Replace the <....> with values in the script and run it to create a simple Stored Procedure which takes an INT as an input then SELECTs and returns the value and the current timestamp.
DROP PROCEDURE IF EXISTS <database-name>.GetValueAndTimestamp;
DELIMITER //
create procedure <database-name>.GetValueAndTimestamp(IN user_value INT)
BEGIN
SET @user_value = user_value;
SELECT @user_value AS 'user_value', CURRENT_TIMESTAMP as 'timestamp';
END //
DELIMITER ;
Then run the following Python script, with mysql-connector-python package installed, against the database used.
import mysql.connector
database_config = {'user': '<username>',
'password': '<password>',
'host': '<database-host>',
'port': 3306,
'database': '<database-name>'
}
if __name__ == '__main__':
cnx = mysql.connector.connect(**database_config)
cursor = cnx.cursor()
proc_name = "<database-name>.GetValueAndTimestamp"
proc_args = (5, )
result = cursor.callproc(proc_name, proc_args)
print(result)
Suggested fix:
Issue is coming from cursor_cext.py here, where the procName is used to build the SET queries for arguments.
argnames = []
argtypes = []
if args:
for idx, arg in enumerate(args):
argname = argfmt.format(name=procname, index=idx + 1)
argnames.append(argname)
if isinstance(arg, tuple):
argtypes.append(" CAST({0} AS {1})".format(argname,
arg[1]))
self.execute("SET {0}=%s".format(argname), (arg[0],))
else:
argtypes.append(argname)
self.execute("SET {0}=%s".format(argname), (arg,))
Instead of using the raw procname it could split the procname on "." and take the right-most slice, which should be the name of the actual procedure being called.
Description: When calling a Stored Procedure with the database name (<database>.<procedure_name> it fails because the database name is used to generate SET commands for the variables. The raw database name is used in the variable name and MySQL does not support dots in the argument names. The error returned is: mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.GetValueAndTimestamp_arg1' at line 1 MySQL itself does support calling procedures with their full name (<database>.<procedure_name>) so believe this should be possible from the Python connector also. How to repeat: Replace the <....> with values in the script and run it to create a simple Stored Procedure which takes an INT as an input then SELECTs and returns the value and the current timestamp. DROP PROCEDURE IF EXISTS <database-name>.GetValueAndTimestamp; DELIMITER // create procedure <database-name>.GetValueAndTimestamp(IN user_value INT) BEGIN SET @user_value = user_value; SELECT @user_value AS 'user_value', CURRENT_TIMESTAMP as 'timestamp'; END // DELIMITER ; Then run the following Python script, with mysql-connector-python package installed, against the database used. import mysql.connector database_config = {'user': '<username>', 'password': '<password>', 'host': '<database-host>', 'port': 3306, 'database': '<database-name>' } if __name__ == '__main__': cnx = mysql.connector.connect(**database_config) cursor = cnx.cursor() proc_name = "<database-name>.GetValueAndTimestamp" proc_args = (5, ) result = cursor.callproc(proc_name, proc_args) print(result) Suggested fix: Issue is coming from cursor_cext.py here, where the procName is used to build the SET queries for arguments. argnames = [] argtypes = [] if args: for idx, arg in enumerate(args): argname = argfmt.format(name=procname, index=idx + 1) argnames.append(argname) if isinstance(arg, tuple): argtypes.append(" CAST({0} AS {1})".format(argname, arg[1])) self.execute("SET {0}=%s".format(argname), (arg[0],)) else: argtypes.append(argname) self.execute("SET {0}=%s".format(argname), (arg,)) Instead of using the raw procname it could split the procname on "." and take the right-most slice, which should be the name of the actual procedure being called.