Bug #45149 Calling SP from MSSQL causes syntax error near '{?=call "my_proc;1(?,?)}'

• Submitted: 28 May 2009 6:49
• Modified: 17 Feb 2017 6:50
• Reporter: Bogdan Degtyariov
• Status: Won't fix
• Category: Connector / ODBC
• Severity: S4 (Feature request)
• Version: 3.51, 5.0
• OS: Windows
• Assigned to: Bogdan Degtyariov
• CPU Architecture: Any
• Tags: linked server, MSSQL, stored procedure

[28 May 2009 6:49] Bogdan Degtyariov

Description:
Microsoft SQL Server generates stored procedures calls that cannot be executed in MySQL. The resulting query looks as '{?=call "my_proc;1(?,?)}'.

How to repeat:
Create a linked MySQL server in MSSQL. Try to call a stored procedure using T-SQL syntax referring the linked server name as follows:

exec MYSQL_LINKED...my_proc 1,2

Suggested fix:
parse the query and transform {?=call "my_proc;1(?,?)} into call my_proc(?,?)

[17 Feb 2017 6:50] Bogdan Degtyariov

Posted by developer:
 
Attempted to re-verify the issue with the most recent version of Connector/ODBC 5.3.7 and MS SQL Server 2014.
It looks that the initial approach to the problem was not correct.
The correct T-SQL syntax for the stored procedures on the linked server would be as follows:

 1. If a procedure returns result-set:

    SELECT * FROM OPENQUERY("MYSQL_LINKED_SERVER", 'CALL my_proc(1, ''abc'')');

 2. If a procedure does not return result-set (it can be used with procedures returning
    result-sets as well, but 1 cannot be used without the result set):

    exec('call my_insert_proc(13)') at MYSQL_LINKED_SERVER;

It worked correctly both ways.