StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » MySqlDataSourceItem...?

MySqlDataSourceItem...?

Edhy Rijo — Thu, 29 Apr 2010 18:54:49 GMT

Well the day has come :D

I have a small project and have to be done with MySQL no other way around. Based on Trent suggestions I created a MySQLDataSourceItem.vb class using the SQLDataSourceItem.vb class in SF source code.

I basically replaced all MS-SQL commands with their counterpart for MySQL but of course I started to hit some blocks, for now the GetDataTable() method is not working, it uses a SqlDependency() object which I have not been able to find a substitute for MySql

[code][codesnippet]

''' <summary>

''' Executes the given command on the data source and returns the results.

''' </summary>

''' <param name="Command"></param>

''' <param name="CallBack"></param>

''' <returns></returns>

''' <remarks></remarks>

Public Overloads Overrides Function GetDataTable(ByVal Command As System.Data.Common.DbCommand, ByVal CallBack As OnChangeEventHandler) As System.Data.DataTable

'-- Establish locals

Dim loDep As SqlDependency

'-- Add the handler to the dependency

If CallBack IsNot Nothing Then

loDep = New SqlDependency()

AddHandler loDep.OnChange, CallBack

loDep.AddCommandDependency(CType(Command, SqlCommand))

Try

'SqlDependency.Start(Command.Connection.ConnectionString)

SqlDependency.Start(CreateBlankDbConnection().ConnectionString)

Catch ex As Exception

MsgBox(ex.Message)

End Try

End If

'-- Execute the command

Return MyBase.GetDataTable(Command, CallBack)

End Function

[/codesnippet][/code]

I would appreciate if somebody could take a look at this class (attached) and help out getting this to work.

I am using the MySQL .Net Connector which it is working just fine from the Server Explorer in VS2010, I am able to connect to the database and see the tables. In order to use the BOM I had to create a dummy DDT project to map the BO from the BOM. At this point I am just trying to read the MySQL table, but I also will need to update it as well.

Trent, Dustin, Steve, please we really, really, really need more commitment to the SF community and the framework. :pinch:

RE: MySqlDataSourceItem...?

Edhy Rijo — Thu, 29 Apr 2010 18:54:49 GMT

Hi Peter,

Well, I am an independent consultant, I sell solutions to my customers, and many of them already have a database working on a hosting ISP and I have no choice other than provide them with a solution with the current database. In this particular case, the application reads some data from VFP tables which it works pretty well with SF since there is full support for it in DDT & BOM, and then updates a MySQL database which is hosted on the web and is used by a php website.

I tried to convince the customer to use MS-SQL Express and probably replace the php solution with an ASP.Net/SF solution, but budget is not available to replace what is working for them already.

Bottom line is that as a consultant either we deliver the solution or the customer will look for somebody else.

MySQL is a very popular database and I bet you that SF will get more customers to buy SF if support for it is added. Currently DDT & BOM supports MS-Access & ORACLE, that is nice, but who is seriously using MS-Access? we have been asking for MySQL support for over a year and even though Trent saids it is in the list of things to add to SF version x the reality is that we don't know when this will happen and projects are just being dumped. :crying:

Sorry for show up my frustration in this matter.

RE: MySqlDataSourceItem...?

Edhy Rijo — Thu, 29 Apr 2010 18:54:30 GMT

Hi Peter,

I tried to convince the customer to use MS-SQL Express and probably replace the php solution with an ASP.Net/SF solution, but budget is not available to replace what is working for them already.

Bottom line is that as a consultant either we deliver the solution or the customer will look for somebody else.

Sorry for show up my frustration in this matter.

RE: MySqlDataSourceItem...?

Peter Jones — Thu, 29 Apr 2010 18:26:24 GMT

Hi Edhy,

Just an aside if I may...

We have a small project coming up in the next few months and I was going to use SQL Express (or whatever the free version is these days) and I'm just interested as to why you selected MySQL rather than SQL Express. I wasn't going to look at alternative databases but you have now got me wondering...

Cheers, Peter

RE: MySqlDataSourceItem...?

Edhy Rijo — Thu, 29 Apr 2010 14:51:12 GMT

[quote][b]Greg McGuffey (04/29/2010)[/b][hr]I.e. you can delete it...let's translate that into Texan...you can smoke it! :D[/quote]

Thanks Greg, yes that is what I did. Now I am fighting "DateTime/Date" translations. :D

Like I said it would be a painful process. Have anybody seen Trent? :crying:

RE: MySqlDataSourceItem...?

Greg McGuffey — Thu, 29 Apr 2010 14:07:47 GMT

Edhy, one thought I had was to look at the OracleDataSourceItem and see what it did. It can't be using the a SqlDependency object right? Well, it turns out that GetDataTable is implemented in DbDataSourceItem and you don't even need to deal with it. The OracleDataSourceItem does not include this function at all.

I.e. you can delete it...let's translate that into Texan...you can smoke it! :D

RE: MySqlDataSourceItem...?

Edhy Rijo — Thu, 29 Apr 2010 09:36:46 GMT

Hi Bill, Keith,

Thanks a lot for the rescue, commenting the code allow me to move forward and at least now I can fill the BO. Now to see if I can insert/modify records.

Keith, thanks for sharing your class, will take a look, but I need to be able to modify and insert records, normal operation basically.

This could be very frustrating process specially when the database has a .Net driver wich works from plain .net :D

RE: MySqlDataSourceItem...?

Keith Chisarik — Thu, 29 Apr 2010 09:27:18 GMT

Bill is right, you can comment it out, that is exactly what I did on my MYSQLDataSourceItem which I have attached.

It is read only as that was my only requirement for my web project. It has been working every day for about two years which is the last time I looked at it until today. It might save you some time.

RE: MySqlDataSourceItem...?

Bill Cunnien — Thu, 29 Apr 2010 09:01:24 GMT

Not sure if this means anything, but...

[quote]SqlDependency requires the .NET Framework version 2.0 and SQL Server 2005.[/quote]

Also...

[quote]SqlDependency was designed to be used in ASP.NET or middle-tier services where there is a relatively small number of servers having dependencies active against the database. It was not designed for use in client applications, where hundreds or thousands of client computers would have SqlDependency objects set up for a single database server.[/quote]

These notes are from [url=http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx]MSDN documentation[/url].

You could probably remove the SqlDependency stuff since it is a small project which likely does not rely on any middle-tier infrastructure.

Have fun,
Bill