MySQL .Net Connector Issue Executing Routine

Question

I'm having an issue getting my code to execute a MySQL routine.

Keeps popping error:

Procedure or function 'ShortenedURLS' cannot be found in database 'Get'.

Routine

DELIMITER $$

USE `o7thurlshortner`$$

DROP PROCEDURE IF EXISTS `Get.ShortenedURLS`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Get.ShortenedURLS`(IN `ID` BIGINT)
    NO SQL
SELECT `ShortID`, `ShortCode`, `URL`, `ClickThroughs`
FROM `Shortener`
WHERE `AccountID` = ID$$

DELIMITER ;

Code - Accessing and running the routine

    internal DbDataReader GetResults()
    {
        try
        {
            // check for parameters
            if (AreParams())
            {
                PrepareParams(_Cmd);
            }
            // set our connection
            _Cmd.Connection = _Conn;
            // set the type of query to run
            _Cmd.CommandType = _QT;
            // set the actual query to run
            _Cmd.CommandText = _Qry;
            // open the connection
            _Cmd.Connection.Open();
            // prepare the command with any parameters that may have gotten added
            _Cmd.Prepare();
            // Execute the SqlDataReader, and set the connection to close once returned
            _Rdr = _Cmd.ExecuteReader(CommandBehavior.CloseConnection);
            // clear out any parameters
            _Cmd.Parameters.Clear();
            // return our reader object
            return (!_Rdr.HasRows) ? null : _Rdr;
        }
        catch (DbException SqlEx)
        {
            _Msg += "Acccess.GetResults SqlException: " + SqlEx.Message;
            ErrorReporting.WriteEm.WriteItem(SqlEx, "o7th.Class.Library.Data.MySql.Access.GetResults", _Msg);
            return null;
        }
        catch (Exception ex)
        {
            _Msg += "Acccess.GetResults Exception: " + ex.Message;
            ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.MySql.Access.GetResults", _Msg);
            return null;
        }
    }

Code - to fire it off

        IList<Typing> _T = Wrapper.GetResults<Typing>("Get.ShortenedURLS",
            System.Data.CommandType.StoredProcedure,
            new string[] { "?ID" },
            new object[] { 1 },
            new MySqlDbType[] { MySqlDbType.Int32 },
            false);

Update

Verified this does work properly once I fireoff a routine without a . in it.

How can I get this to work if my routines do have .'s, I cannot simply re-write existing procedures in a production database tied to a high traffic website...

Then I would try to rename the procedure to something without the Get. prefix. It seems that Get is mistakenly interpreted as a database name — Steve
– Steve, Commented Jan 31, 2014 at 15:42
That is not a possibility as the procedures are in use in a production environment in a PHP site — Kevin
– Kevin, Commented Jan 31, 2014 at 15:44
All right but at least a check to exclude this possibility should be tried. However, do you have the latest version of the MySql connector installed? — Steve
– Steve, Commented Jan 31, 2014 at 15:55
Yeah, in fact just installed it :). I've got a wrapper class built for MS SQL, and because I've been doing alot of MySQL stuff lately I wanted to incorporate it into a seperate MySQL wrapper :) I'll see if I've got access rights to create a new proceudre to test it with — Kevin
– Kevin, Commented Jan 31, 2014 at 16:01

iamkrillin · Accepted Answer · 2014-02-08 17:54:06Z

2

+50

In order to call you stored procedure you do have to wrap the name of it in backticks since it contains the special character .

However, there is a bug in the mysql connector code that is causing it to escape it again. When you specify

cmd.CommandType = CommandType.StoredProcedure;

The code branches during execute reader as you can see below...

if (statement == null || !statement.IsPrepared)
{
  if (CommandType == CommandType.StoredProcedure)
    statement = new StoredProcedure(this, sql);
  else
    statement = new PreparableStatement(this, sql);
}

// stored procs are the only statement type that need do anything during resolve
statement.Resolve(false); // the problem occurs here

part of what Resolve does is fix the procedure name..

//StoredProcedure.cs line 104-112
private string FixProcedureName(string name)
{
    string[] parts = name.Split('.');
    for (int i = 0; i < parts.Length; i++)
        if (!parts[i].StartsWith("`", StringComparison.Ordinal))
            parts[i] = String.Format("`{0}`", parts[i]);
    if (parts.Length == 1) return parts[0];
    return String.Format("{0}.{1}", parts[0], parts[1]);
}

As you can see here, anytime the stored procedure name has a . in it, the name of the stored procedure is broken up into parts and escaped individually, and this is causing your code to not be able to call your stored procedure.

So your only options to fix this are..

1) Open a bug with oracle to fix the provider (assuming there is not one already open)
2) Change the name of your stored procedure to not use a .
3) Download the code for the provider, fix it, recompile
4) Find another connector

answered Feb 8, 2014 at 17:54

iamkrillin

6,8761 gold badge27 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kevin Over a year ago

5) Call it as a Text type and use Call Get.ShortenedURLs (?ID) works as well... but Im sure its not nearly as efficient as using StoredProcedure as my command type.

Kevin Over a year ago

although... i may do #3 as well :). Thanks for the explenation on this

Collectives™ on Stack Overflow

MySQL .Net Connector Issue Executing Routine

Routine

Code - Accessing and running the routine

Code - to fire it off

Update

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Routine

Code - Accessing and running the routine

Code - to fire it off

Update

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related