search

subsonic / SubSonic-3.0

SubSonic 3.0 for the .NET 3.5 Framework
http://subsonic.github.io/
558 stars 210 forks source link

Grave "accents" incorrect with MySql provider #90

Open shawiiing opened 15 years ago

shawiiing commented 15 years ago

3.0.0.3 SimpleRepository issue with MySQL Server 5.1 and .Net Connector 6.0.4

var p = repo.GetPaged(index, pagesize)

Throws a MySQL syntax exception when run. After doing some digging (getting the logs to work) this is the query that is being executed:

SELECT COUNT(id) AS `CountOfid` FROM Sales; //Does not work

SELECT COUNT(id) AS CountOfid FROM Sales; //Ran this to make sure it wasn't an issue in the DB and it runs like a champ.

shawiiing commented 15 years ago

Ok so got brave and downloaded the source and hacked my own fix.

Looks like removing all of the grave characters (`) in the method below resolves the issue. I need to do more testing to make sure they weren't in there for a reason.

The method in question is: protected override string GenerateAggregateSelect(Aggregate aggregate)

Elrinth commented 13 years ago

the ` are there for a reason incase of conflict with mysql default stuff like: KEY I do use Key as fieldname for a couple of things so that would probably NOT work for you.

Reading what you wrote about there is a backslash before CountOfid which definatly shouldn't be there :)

Is it difficult to become a coder for subsonic? ie, to be able to committ fixes n features?

rally25rs commented 13 years ago

I also rely on the (`) character being in certain queries to handle the use of reserved words in some tables. Please do not remove that.

Elrinth commented 12 years ago

I've got additional comments to this. Read: http://groups.google.com/group/subsonicproject/browse_thread/thread/cf7fa838f21aa0d8

Basically what I wrote is: When using linq comparer: x.Key.Contains("something") subsonic generates an incorrect query. The problem lies in the ANSISqlGenerator.cs in the following function: public virtual string GenerateConstraints() I figured out it does a Findcolumn(c.ColumnName) but here columnname is blank/null. The ColumnName should be Key so I simply did on the row before: if (string.IsNullOrEmpty(c.Columnname)) c.ColumnName = c.ConstructionFragment;

This fixed the problem and I haven't had any issues with adding the lines, tho I haven't hard tested as I'm currently working on getting the whole site working in MSSQL :(

This fix should be submitted to the Git source!