Click here to Skip to main content
15,893,190 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a MySql-based application that works fine. It uses stored procs, and all parameter names start with an underscore _
My hosting provider recently "upgraded" my development database from MySql to MariaDB, claiming 100% compatibility. Now I can't even log in on the development system, getting an error that the first-named parameter is "unknown". Testing shows that if I change all the parameter names to start @ instead of _, all works fine.
However to do this would mean updating scores of stored procedures, and hundreds of lines of code, impacting the client's live systems and probably requiring some downtime.
In the longer term, I will ditch the hosting provider who made this change without warning or notification and refuses to accept that MariaDB and MySql are NOT the same thing.
In the short term, to allow me to continue development testing on the MariaDB database, is there a workaround that will allow me to declare a setting per-procedure, or at worst to modify each parameter reference in the procedure, without needing to rename all parameters?

What I have tried:

Renaming all parameters (e.g. _ID to @ID) works but isn't an acceptable solution.
I've tried escaping all parameter names within the stored procedure (e.g. `_ID`) but this does not resolve the problem; still get
C#
SQL Error (1054): Unknown column '_ID' in 'field list'
Simplified version of SP:
CREATE PROCEDURE `spUserLoad`(
	IN `_ID` INT,
	IN `_UserName` VARCHAR(50)
)
BEGIN
    SELECT * FROM tUser
      WHERE `_ID` = ID
      OR (`_ID` = 0 AND UserName = `_UserName`);
END
Posted
Updated 25-Jun-19 2:13am

I have seen a similar question somewhere on this subject. Your best bet would be to try Get Involved - MariaDB.org[^].
 
Share this answer
 
woops, red-face time.
I was getting that "Unknown column name" error when using MySqlWorkbench or HeidiSQL to execute the SP directly; seems that you can't use named parameters in the CALL statement like that (you can in Sql Server).
I was also getting the same "Unknown column name" error when executing the application; turns out that (some time ago!) I added some additional logging that tried to inspect the command, and treated the _ID parameter differently (by design). However I was referencing "ID" instead of "_ID" in my code, and re-reading the error messages, yes, it was complaining about ID missing from the parameter list, not _ID. Disabling that new diagnostic logging resolved my problem.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900