Hey All,

I have been using SS for a bit now, but only with MS SQL.  I am interested in starting a project with MySQL and (of course) will be using SS.  My question is this: exactly what version of SS should I use with the newest MySQL (5.0.51).

I have read LarryB comment on using the "latest version" of the code, if your are going to be using MySQL.  When he says, "latest", does he mean SS v2.1 Beta?

Any and all help/advice is greatly appreciated.

TIA

I am using MySql with SubSonic for some time now and the combination that I have been working on is SubSonic 2.1 rev. 266 but a customized one which has some changes. Most of these changes (the most important ones) are already commited in the latest SubSonic source code and although I have not checked out the latest release from SVN I think the latest release should work just fine as long as you use do not use the new Query engine (that propably works as well but I didn't have time to play with it). Anyway as far as the MySql Connector goes I am using a customized version of the latest release from their SVN, version 5.1.5. Again I had to make some changes to it (bug fixes, performance enhancements) and most of my changes are commited as well. So get the latest SubSonic code, the latest MySql code and play with it. It should work fine, if you have any problems please post a reply in this post and I will try to take a look at it as soon as possible.  

Hey ncichris,

Thanks for the reply!

Yes, I spent the better part of the weekend playing with MySQL and SubSonic.  All is well.  VERY well, I might add.

I pulled the latest SubSonic code from CodePlex and went with that.  No major issues.  I did notice that the scaffold doesn't seem to write (or at least format) dates properly for update or insertion.  I'm sure this is either being attended to, or does work, somehow, and I am just being a dork. Do you have it working, or know how to get it working?

What tools do you use for MySQL?  I am using Navicat.  It seems to handle the bulk of what I need to do.  But am always curious if there is something better out there.

Again, thanks for replying and LMK what you think on these other points.

I happy to hear that it is almost.. working for you. Unfortunately dates work for me just fine although in the past I did have some issues with the date control when used with other cultures, and the javascript formatting being applied on the control during user interaction. What exactly is the problem you are having with dates?

I am not familiar with Navicat but I am using Core Lab's tools which are integrated into Visual Studio and I am pretty happy with it. The most useful feature they have and I love it, is debugging stored procedures. They also have their own MySql connector that is supposed to be quite good (although I don't use it).

 

So, just to clarify, Inserting and Updating Date fields in MySQL is WORKING for you via the SubSonic Scaffold control?

Now are you using the data type "DATE" or "DATETIME"?  I believe my table was using "DATETIME".  And it was NOT able to update or insert a record, via the scaffold, as the error reported improper date format.  ie. '02-10-2008' was not acceptable -- which I believe the calendar control embedded in the edit page of the scaffold, formats and presents.

Do you have a work around for this?  Does this truly work for you, with the scaffold?

LMK - TIA

PS> I will look into the Core Lab's tools.  I have not heard of them, and will investigate immediately.  Thanks for the tip.

Yes, I just tested it again to make sure. My scaffolds work fine with columns of type DateTime, although my format is MM/DD/YYY not MM-DD-YYYY.

Well this is certainly good news.  I definitely need to get this working on my end. 

Do you do anything special in the Scaffold tag to allow for this?  Or is it working, right out of the box?

Perhaps you could paste in a code sample of your scaffold construct?  ie. the markup/control tag, that is on the page, that is working...

LMK - TIA

Well there is nothing special in the generated code in the scaffold I have something like:

<td class="scaffoldEditItem"><subsonic:CalendarControl ID="ctrlDatePurchased" runat="server" ></subsonic:CalendarControl></td>

and in the code behind I have something like:

ctrlDatePurchased.SelectedDate = item.DatePurchased;

As far as I remember the default format for the calendar is MM/dd/yyyy. and the default Javascript format is

%m/%d/%Y %I:%M %p

which basically adds the time as well. Other than that the control will try to read the culture info from your system. If you have changed the defaults (or the defaults for your region is MM-dd-yyyy then this can cause a problem. If you could tell me exactly what the problem is with the exception data and the stack trace it would be much easier to troubleshoot this. Also if you are generating Code behind and not using the Auto Scaffold you could do a debug and tell me what the value is of the calendar control text box before that value is set to the property.

Ok, cool.  I will look at this, as soon as I can -- this is a project I am working on, from home.  So I can't look until I get back there, this evening.  But I will check it out, staright away and let you know.

No, I have not changed any culture defaults.  I am using the buildprovider and simply drag'n'dropping a SS Scaffold control on the page and setting the "table" element, within the tag to a MySQL table.

The page comes up just fine.  It even enters "Edit" mode just fine.  But when I click "Save" it errors.

Here is the content of that error:

****************************************************************************************************************************************************

Server Error in '/dd6' Application.
--------------------------------------------------------------------------------

Incorrect date value: '6/7/2005' for column 'news_date' at row 1
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: SubSonic.SqlQueryException: Incorrect date value: '6/7/2005' for column 'news_date' at row 1

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. 

Stack Trace:

[SqlQueryException: Incorrect date value: '6/7/2005' for column 'news_date' at row 1]
   SubSonic.Update.Execute() +256
   SubSonic.Scaffold.UpdateRecord(String primaryKeyValue) +439
   SubSonic.Scaffold.SaveEditor() +32
   SubSonic.Scaffold.btnSave_Click(Object sender, EventArgs e) +25
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746

 

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433

****************************************************************************************************************************************************

HTH.  I really NEED to get scaffolds working.  And it just seems like these date fields are the "fly in the ointment".

LMK - TIA

Hmmm...that seems to have quieted you down a bit.  Was this confusing or not the info you needed?

LMK, as I will help in whatever way I can to get this resoved.

Thanks in advance.

Sorry for that it was 11pm at my side of the world... I had to go home. Anyway it would be really helpful if you could build subsonic from the latest SVN in debug mode and reference in your project the dll with the debugging info so we can see in more detail the stack trace. Also if you can capture the Update/Insert commands and send them too that would be great. I would do this my self but unfortunately I don't have that much time right now. By the way does this happen on updates/inserts or both?

hi, what about foreing keys?

subsonic does´n do these....

for example:

subSonic Does :: People.IdCar

it doesnt do :: People.Car

why...

i made the foreing keys ..

thanks... 

 

 

Hey Bob,

I am sorry, but I can't decipher what you mean by the above post...

If you are stating that foreign keys are not supported by SS, via MySQL, you are mistaken.  I have complete foreign key functionality.

Even the scaffold supports them and properly displays a drop-down of the related table's data.  So I can't imagine THAT is what you are saying.

Please clarify your situation.  TIA

 HI....

Sorry for my english....

i can´t write very well

 

Let´s try again....

well

i'm trying mysql with subsonic and the generated code betwen mysql an SQLserver are diffents...

for example:

1 people has many cars

and

1 car has one people 

-- on SQlServer:

 Car.People  //(objetct People)

 --on mySql

 Car.IdPeople //(int)

 its right? 

on mysql i havent suport to relationships?

it´s just whith id's?

 

see my tables:

DROP TABLE IF EXISTS `vendas`.`produto`;
CREATE TABLE  `vendas`.`produto` (
  `idProduto` int(10) unsigned NOT NULL auto_increment,
  `idFornecedor` int(10) unsigned NOT NULL,
  `descricao` varchar(45) NOT NULL,
  `idPreco` int(10) unsigned NOT NULL,
  `quantidade` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`idProduto`),
  KEY `FK_Produto_Fornecedor` (`idFornecedor`),
  CONSTRAINT `FK_Produto_Fornecedor` FOREIGN KEY (`idFornecedor`) REFERENCES `fornecedor` (`idFornecedor`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

AND

 

 

DROP TABLE IF EXISTS `vendas`.`fornecedor`;
CREATE TABLE  `vendas`.`fornecedor` (
  `idFornecedor` int(10) unsigned NOT NULL auto_increment,
  `nomeFantasia` varchar(45) NOT NULL,
  `razaoSocial` varchar(45) NOT NULL,
  `CNPJ` varchar(15) NOT NULL,
  `endereco` varchar(45) NOT NULL,
  `bairro` varchar(45) NOT NULL,
  `estado` varchar(2) NOT NULL,
  `telefone1` varchar(10) default NULL,
  `telefone2` varchar(10) default NULL,
  `celular` varchar(10) default NULL,
  `email` varchar(30) default NULL,
  `cep` varchar(15) NOT NULL,
  `fax` varchar(10) default NULL,
  PRIMARY KEY  (`idFornecedor`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

there is a foreing key.....

but it  doesn' t work....

 

thanks..... 

 

 

 

 

there is that im my generated code, bellow the properties:

//no foreign key tables defined (0)
        

//no ManyToMany tables defined (0)

 

but i made the foreing keys.....