2009-09-24

SubSonic 3 with MySQL

Recently I've tried to user the new version of SubSonic (3.0.0.3) with MySQL.
for two days i was struggling to make it work.
I've tried the new MySQL .NET connector (6.1) and every time got errors.

Eventually, I've downloaded the code of both, MySQL and SubSonic and tried to understand what the heck they are doing there.

I've used the MySQL t4 that was supplied with the subsonic 3, renamed it to SQLServer.ttinclude and it still didnt work.
After breaking it down and solving one part after the other i've realized that the MySQL include file is missing support of Stored Procedures.
So I've rewrote it out of the SQL Server template, and using the MySQL Schema.
Here it is:

List GetSPParams(string spName){
var result=new List();
string[] restrictions = new string[4] { DatabaseName, null, spName, null };
using(conn=new MySqlConnection(ConnectionString)){
conn.Open();
var sprocs=conn.GetSchema("PROCEDURE PARAMETERS", restrictions);
conn.Close();
foreach(DataRow row in sprocs.Select("", "ORDINAL_POSITION")){
SPParam p=new SPParam();
p.SysType=GetSysType(row["DATA_TYPE"].ToString());
p.DbType=GetDbType(row["DATA_TYPE"].ToString()).ToString();
p.Name=row["PARAMETER_NAME"].ToString().Replace("@","");
p.CleanName=CleanUp(p.Name);
result.Add(p);
}
}
return result;
}

List GetSPs(){
var result=new List();
//pull the SPs
DataTable sprocs=null;
DataTable parameters=null;
using(conn=new MySqlConnection(ConnectionString)){
conn.Open();
sprocs=conn.GetSchema("PROCEDURES");
conn.Close();
}
foreach(DataRow row in sprocs.Rows){
string spType=row["ROUTINE_TYPE"].ToString();
var sp=new SP();
sp.Name=row["ROUTINE_NAME"].ToString();
if(spType=="PROCEDURE" &! sp.Name.StartsWith("sp_")){
sp.CleanName=CleanUp(sp.Name);
sp.Parameters=GetSPParams(sp.Name);
result.Add(sp);
}
}
return result;
}

Just add it to the ttinclude of the MySQL and all the stored will be imported like a charm.

Next quest: changing the template to support localizations !

C'ya

2 comments:

JeffGos said...
This comment has been removed by the author.
JeffGos said...

Thanks - I'll add this to my script

If anyone needs some info on setting up SubSonic with MySQL you can find a post I wrote on it here:

Setting up SubSonic and MySQL

pip install pymssql fails with 'sqlfront.h': No such file or directory

I've tried to install pymssql on Windows using command line: pip install pymssql The operation fails with an error: fatal error C108...