MySQL Data Source Item class


Author
Message
Edhy Rijo
E
StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)
Group: StrataFrame Users
Posts: 2.4K, Visits: 23K
As many of you may have read in other posts I have been asking for MySQL support for a while and fortunately and unfortunately the time arrived where I had the need to communicate with Visual FoxPro and MySQL databases from the same StrataFrame project.



Thanks to comments and code from Keith Chisarik, Bill Cunnien, Greg McGuffey and other related posts I found from Trent Taylor (from SF Team) I was able to create a MySQLDataSourceItem.vb class attached (in VB only sorry BigGrin). Due to time constraint I just added some comments to the class and tested the functionality to Read, Add, Modify without stored procedures, just the simple stuff needed for this project.



I am using the MySQLConnectorclass version 6.3 with has a very good documentation and so far many of the method/properties found in the MS-SQL connector exist in the MySQL connector and that made it easier to translate the original base class.



Still none of the SF tools Connection Manager class, DDT & BOM works with MySQL using the .Net connector, only the OleDB seems to be supported and I did not try the OleDb. I had to create my own Connection Manager form which was not that complicated since MySQLConnector have a MySqlConnectionStringBuilder as well so that made it easier.



Talking about Connection String Builders, Trent, I don't know if you are aware that MS released the code the use for their Data Connection Dialog to create string connections for any database supported by Visual Studio. I could assume that if the SF team uses this code in the SF Connection Manager then you could allow the developer to connect to any database using the SF tools, of course we would need to create the Data Source Item required making SF more generic for the developer's need.



Sorry for the long post and I hope other SF developers can benefit from this experience. Now getting ready to work with VistaDB. BigGrin



P.S.

I am still hoping to see direct support for MySQL and VistaDB database from SF soon Hehe Business wise it will be really helpful to see serious commitment with some release dates for the new functionality, technology and coming updates to StrataFrame. Smile

Edhy Rijo

Attachments
MySqlDataSourceItem.zip (226 views, 8.00 KB)
Russell Scott Brown
Russell Scott Brown
StrataFrame User (136 reputation)StrataFrame User (136 reputation)StrataFrame User (136 reputation)StrataFrame User (136 reputation)StrataFrame User (136 reputation)StrataFrame User (136 reputation)StrataFrame User (136 reputation)StrataFrame User (136 reputation)StrataFrame User (136 reputation)
Group: Forum Members
Posts: 124, Visits: 597
Thanks for the update and good job Edhy!

I can't wait to see how VistaDB goes and how far you can get with that.

I think we should also be looking at Sybase iAnywhere Advantage Database which looks very promising on the client side and is free. 

You can check it out at:

http://www.sybase.com/products/databasemanagement/advantagedatabaseserver

Russ Brown (Using C#2010/SQL Server 2008)

balexander
balexander
StrataFrame Beginner (4 reputation)StrataFrame Beginner (4 reputation)StrataFrame Beginner (4 reputation)StrataFrame Beginner (4 reputation)StrataFrame Beginner (4 reputation)StrataFrame Beginner (4 reputation)StrataFrame Beginner (4 reputation)StrataFrame Beginner (4 reputation)StrataFrame Beginner (4 reputation)
Group: Forum Members
Posts: 4, Visits: 9
Hi Edhy,

We're in a similar situation as you.  We are actually running both MSSQL and MySQL in the same shop, with a slow/steady migration from My to MS.

We're about to start a new project where we find the need for both connecting to MS and My from the same application.  Eventually the connection to the MySQL will go away and refactoring that code from a StrataFrame BO (My) to StrataFrame BO (MS) would be a lot easier than migrating an entirely different connection method to StrataFrame.

Can you give more details as to how to implement your MySQLDataSourceItem?  We have StrataFrame source if I need to compile it in.

Any advice to point us in the right direction would be GREATLY appreciated.

Thank you,
Barrett

Edhy Rijo
E
StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)
Group: StrataFrame Users
Posts: 2.4K, Visits: 23K
balexander (05/12/2010)
Can you give more details as to how to implement your MySQLDataSourceItem? We have StrataFrame source if I need to compile it in.



Any advice to point us in the right direction would be GREATLY appreciated.




Hi Barrett,



Sure, this is what you need to do:

1.- Copy the MySQLDataSourceItem class anywhere in your project (Must be VB, if not you will have to convert it.



2.- In the App.Main.vb, modify the SetDataSources and manually add the code to add a new data source item to the DataSources collection based on my class, here is some code to do that:







'-- Create the MySQL connection using the MySqlConnectionStringBuilder class from the connector

Dim MySQLconnStringBuilder As New MySql.Data.MySqlClient.MySqlConnectionStringBuilder

With MySQLconnStringBuilder

.Server = "Your MySQL Server IP"

.UserID = "MySQL User ID"

.Password = "MySQL Password"

.Database = "Database Name"



'-- I found these properties useful for my project.

.PersistSecurityInfo = True

.ConvertZeroDateTime = True

.DefaultCommandTimeout = 50

End With

Dim mMySQLConnectionString As String = MySQLconnStringBuilder.ToString



'-- Add a data source item using the custom MySqlDataSourceItem.vb class and very important..

' ... pass a DataSouceKey, in this case I used "MySQL"

DataLayer.DataSources.Add(New Business.Data.MySqlDataSourceItem("MySQL", mMySQLConnectionString))







3.-To create the Business Objects, I had to use the DDT, I created a DDT project and manually defined the database and tables. In the Table Properties, use the database name as the Schema also and keep your names using the same case as in MySql since MySql is case sensitive, I have not tested otherwise, but just to be safe I keep all names with the same case as in MySql database. Then in the BOM I mapped the BO to the DDT project and setup any null properties as appropriate, then generate the partial class.



4.- In the BO don't forget to enter the DataSourceKey value, so the BO will know which connection to use, in my sample I use "MySQL".



5.- Design your form and Data Access methods in the same way as MS-SQL and you should be OK.



I have to mention that in my project I am only working with 2 MySQL tables and have not tested all possible scenarios like relations, stored procedures, cascade deletes, concurrency, etc. just basic Add/Edit/Delete and of course some data access SQL statements with parameters, here is an example of that:







Imports MySql.Data.MySqlClient



'''

''' Bring all records using the Customer Number

'''


'''

'''

Public Sub FillByCustomerNumber(ByVal CustomerNumber As String)

Using cmd As New MySqlCommand

cmd.CommandText = String.Format("Select * From {0} where cust_number = @CustomerNumber", Me.TableName)

cmd.Parameters.AddWithValue("@CustomerNumber", CustomerNumber).MySqlDbType = MySqlDbType.VarChar



Me.FillDataTable(cmd)

End Using

End Sub






Edhy Rijo

balexander
balexander
StrataFrame Beginner (4 reputation)StrataFrame Beginner (4 reputation)StrataFrame Beginner (4 reputation)StrataFrame Beginner (4 reputation)StrataFrame Beginner (4 reputation)StrataFrame Beginner (4 reputation)StrataFrame Beginner (4 reputation)StrataFrame Beginner (4 reputation)StrataFrame Beginner (4 reputation)
Group: Forum Members
Posts: 4, Visits: 9
Thank you so much!
Jason Seidell
Jason Seidell
StrataFrame Novice (59 reputation)StrataFrame Novice (59 reputation)StrataFrame Novice (59 reputation)StrataFrame Novice (59 reputation)StrataFrame Novice (59 reputation)StrataFrame Novice (59 reputation)StrataFrame Novice (59 reputation)StrataFrame Novice (59 reputation)StrataFrame Novice (59 reputation)
Group: Forum Members
Posts: 59, Visits: 180
Just as a follow up we were able to implement this solution fairly well.  How we solved running the bo mapper is by having our MySql DBA run a MySQLDump to just create the insert table statements.  Then reworked to run on MSSQL and then pointed the BO mapper to the empty MSSQL tables (which have an identical structure to the MySQL tables).  At run time they bind, retrieve, and save records from MySQL exactly the same way they would from MSSQL.  We did run into a few minor data type problems, which we were able to solve by changing the datatype on the MSSQL tables and then rebuilding the BOs or using the BO mapper custom field modifications.  It's been about month now so I can't remember exactly what we ran into, but they were so minor we easily solved them.

Currently we have used this approach to map about 50+ tables from 4 MySQL databases, and it's running without any real problems.  Although I will admit, right now we are pretty much using those MySQL BOs for read access to the tables.  We did setup a few test screens and were able to call saves without any problems but just haven't had time to rework the internal apps yet, so there maybe further difficulties once we dig into it deeper for the GUI applications but I feel confident any issues will not be major and easily solved.

Thanks for leading the way on this!

Jason Seidell

Programmer/Analyst

Infinedi

Edhy Rijo
E
StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)
Group: StrataFrame Users
Posts: 2.4K, Visits: 23K
Jason Seidell (07/27/2010)
J... How we solved running the bo mapper is by having our MySql DBA run a MySQLDump to just create the insert table statements. Then reworked to run on MSSQL and then pointed the BO mapper to the empty MSSQL tables (which have an identical structure to the MySQL tables).




Hi Jason,



Thanks for sharing your experiences with MySQL, that looks like a lot of work for any project, it should be easier. I have not tested yet, but I believe in the BOM you can use a MySQL OleDB driver to read the schema for your BOs, if possible, please try and let us know.

Edhy Rijo

GO

Merge Selected

Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...




Similar Topics

Reading This Topic

Login

Explore
Messages
Mentions
Search