Subsonic 3 and Activerecord isn't generating MySQL Stored Procedures

Question

It seems that the included T4 templates (or the one in the SVN trunk for that matter) just skips generating SPs for MySQL... When running StoredProcedures.ttinclude together with MySQL.ttinclude, I get the error "Compiling transformation: The name 'GetSPs' does not exist in the current context".

GetSPs is defined for SQLServer and I saw that someone wrote his own for Oracle, but does anyone have a clue how the proper GetSPs()-method should look like for MySQL?

Personally I don't think it's really functional unless I can run my own SPs :/

Answer 1

I tinkered around a bit with the code from version 2 and came up with a little code (probably not 100% in all situations) that did the trick for me. Put this in MySQL.ttinclude to get Stored Procedures generated. Enjoy!

string[] GetSPList()
{
    var result=new List();
    const string sql = "SELECT routine_name FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = ?databaseName";
    StringBuilder sList = new StringBuilder();

    using(conn=new MySqlConnection(ConnectionString))
    {
     MySqlCommand cmd = new MySqlCommand(sql, conn);

     cmd.Parameters.AddWithValue("?databaseName", DatabaseName);
     conn.Open();
     using(IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
     {
      bool isFirst = true;
      while(rdr.Read())
      {
       if(!isFirst)
        sList.Append('|');
       isFirst = false;
       sList.Append(rdr[0]);
      }
      rdr.Close();
     }
    }
    return sList.ToString().Split(new char[] {'|'}, StringSplitOptions.RemoveEmptyEntries);
}

List GetSPParams(string spName)
{
    var result=new List();
    MySqlCommand cmd = new MySqlCommand();
    using(conn=new MySqlConnection(ConnectionString))
    {
     conn.Open();
     cmd.Connection = conn;
     cmd.CommandText = spName;
     cmd.CommandType = CommandType.StoredProcedure;

     try
     {
      MySqlCommandBuilder.DeriveParameters(cmd);
     }
     catch
     {
     }

     if(cmd.Parameters.Count > 0)
     {
      foreach(MySqlParameter param in cmd.Parameters)
      {
       SPParam p = new SPParam();
       p.SysType = GetSysType(param.MySqlDbType.ToString());
       p.DbType = param.DbType.ToString();
       p.Name = param.ParameterName;
       p.CleanName=CleanUp(p.Name);
       result.Add(p);
      }
     }
     conn.Close();
    }

    return result;
}

List GetSPs(){
    var result=new List();
    string[] spNames = GetSPList();

    foreach(string spName in spNames){
        var sp=new SP();
        sp.Name=spName;
     sp.CleanName=CleanUp(sp.Name);
     sp.Parameters=GetSPParams(sp.Name);
     result.Add(sp);        
    }

    return result;
}

Answer 2

I got this code to run by adding it to my MySQL.ttinclude however I had to change the generic List's to typed lists. eg: List<SPParam> and List<SP>. The code worked after that though ;-)