I am currently working with ES and would like to stay compatible in my code so that I can rebuild the classes and use MS SQL. However, I am currently having to cast/convert from say int? to sbyte and datetime in MySQL.

Also, I have tried to use o.field.value when having a nullable type but I am still having issues with MySQL dates and tinyint. I am having to use Convert.ToSbyte() and Convert.ToDateTime(). I think If I try to rebuild these into MS SQL that it would break due to the casting differences.

 Any best practices?

 

 

 

 

 

First, let me ask. Are you using our Multi-Db support? Are you running with the "extra" metadata map template and so on?

---

EntitySpaces | Twitter | BLOG | Please honor our Software License

http://community.entityspaces.net/forums/thread/3137.aspx
http://community.entityspaces.net/forums/thread/3136.aspx

---

David Neal Parsons
www.entityspaces.net

Thanks Mike and Dave.

 This is exactly what I was needing.

 

I have changed the field types in MySQL from tinyint to bit and rebuilt my classes. I am still seeing these two errors.

 cannot convert from 'sbyte?' to 'bool' 

cannot convert from 'System.DateTime?' to 'System.DateTime' 

I can use Convert.ToSbyte() and Convert.ToDateTime()  but will Convert.ToSbyte() recompile for MS SQL?

 

I had to convert the bit fields back to tinyint due to receiving an invalidcastexcption when trying to convert the ES object (mapped to bit field) to boolean.

 

This sounds like deja vu all over again.

http://community.entityspaces.net/forums/thread/3014.aspx
http://community.entityspaces.net/forums/thread/868.aspx

Are you running your Generated master against SQL Server, and the MetadataMap against MySQL (that is working for us), or the other way around (which we have not tried)?
Are you checking "Target Multiple Databases" when you generate the master? The sbyte data type, while still supported in .NET, is considered deprecated. There is no implicit conversion to bool, which can make multi-db work difficult. In our Northwind port that works with the EntitySpaces Demo, and in our test databases, we use TINYINT(1) UNSIGNED for those columns in MySQL. That maps to byte.

What version of MySQL Connector are you using?

We have all green tests with 5.0.7. About a month ago, the only change I made was to upgrade MySQL Connector to 5.0.8.1. Some, but not all, of our DateTime tests failed, so we still are compiling against 5.0.7. Looking at the 5.0.8 release notes, I see they did make some changes in the Connector relating to DateTime conversion. We have had time to investigate why 5.0.8.1 breaks those tests.

 

---

David Neal Parsons
www.entityspaces.net

Sorry. I had worked on this project several months ago and had put it to the side for a while.

Currently, I am only working with MySQL. I do not have multiple databases selected and I am generating my master and meta against the mySQL database.

I am using the 5.1.3.0 version of the MySQL Connector because I had started looking at the Falcon engine. I am currently using the InnoDB database and not the standard MyISAM.

I tried to assign my fields to TINYINT(1) UNSIGNED and map to byte. I was still thrown an error that I could not convert byte to bool when I tried u.isActive = true; I had to do u.isActive = Convert.ToByte(true) to get it to work. I dont want to have to convert each value and then maybe run into an issue with multiple database.  So, I changed the field type to BIT and mapped BIT to bool and this is working. I did this primarly beause MS SQL uses the BIT flag for boolean fields. Do you see any issues in changing the mapping from bit to boolean?

I need to do some more testing and port the MySQL database to MS SQL and test with the multiple database options. Do you have any recommendations on the best way to port the mySQL database schema to MS SQL?

 

Thanks

The BIT/bool combination should be fine.

We have not tested with Connector 5.1.x. It is still in beta. I'm surprised you can generate from it in MyGeneration since, according to their release notes, they have changed the way certain aspects of the schema are reported. I thought it may have broken MyMeta.

We've looked at a lot of database conversion tools, each with pluses and minuses. They all have their restrictions, like some not converting FK relations, or SPs, or Views. I always seem to fall back on exporting CREATE TABLE, SP, and View scripts, and then doing a lot of search/replace in a text editor.

I usually start with MS SQL and convert to other DBs, so do not have a lot of advice to offer. I believe MySQL's Migration Wizard goes from MS SQL, but not back the other way.

---

David Neal Parsons
www.entityspaces.net