CODEWITHSUNDEEP
c#mysql.netc#-4.0.net-4.0
Kevin
Kevin

Reputation: 2688

MySQL .Net Connector Issue Executing Routine

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...

Upvotes: 1

Views: 146

Answers (1)

iamkrillin
iamkrillin

Reputation: 6876

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

Upvotes: 2

Related Questions