Warning: Unexpected error message when calling stored procedure with MySQL

AIMMS Knowledge Base Article - KB00019

Warning: Unexpected error message when calling stored procedure with MySQL

The information in this article applies to:

  • AIMMS 4.1 and later

Summary

Stored procedures with out or inout arguments may generate an error when called in a MySQL database.

Symptoms

A somewhat cryptic error: In the AIMMS versions below 4.1 an error is generated stating changes in the database cannot be committed (even if the procedure does not change the database), while in the newer version of AIMMS ( > 4.1) the error states that the argument is not a variable.

Cause

The MySQL ODBC driver does not support out arguments.

Resolution

There is no easy workaround available within AIMMS; future versions the MySQL ODBC driver might resolve this problem. Alternatively you might reformulate the stored procedure to return the desired values as a result set instead of using in/out or out arguments.

Example error messages

In AIMMS versions before 4.1:

"Calling stored procedure DetermineToday in MySQL.dsn failed: Could not commit changes to the data source after executing the current database procedure." "Execution error at line 1 in user-defined function section "gt::DetermineTodayCall". Calling stored procedure DetermineToday in MySQL.dsn failed: Could not commit changes to the data source after executing the current database procedure."

In AIMMS versions 4.1 and later:

"Calling stored procedure DetermineToday in MySQL.dsn : HY000 [MySQL][ODBC 5.3(w) Driver][mysqld-5.5.29-0ubuntu0.12.04.1]OUT or INOUT argument 1 for routine testDBMS.DetermineCurrentDate is not a variable or NEW pseudo-variable in BEFORE trigger, while calling the procedure DetermineCurrentDate."

Last reviewed: August 21, 2014