How clever is the SqlHelper? If I run "select top 100 * from Blah" will it fail in MySQL? Is there a good way of writing a limited query that will run on both? Or do I have to check the provider and adjust my SQL manually?
As far as I can tell, the SqlHelper will select the appropriate data-access-layer (whether that be SQL Server, MySQL, or other) ... it doesn't do any conversions between T-SQL and MySQL (SQL/PSM) syntax.
You'll probably need to grab all records, then limit them in code.
Lee's right, I think you'd have to either limit in code (not ideal for large datasets), or write some code to run different queries based on the database provider that the site is using.
The syntax for limiting in MySQL/SQL Server is completely different, so I don't think there's a query you culd write that will work on both I'm afraid.
Had a quick look in the "umbraco.DataLayer.DataLayerHelper.CreateSqlHelper" method to see how the API does it.
Basically it checks for the "datalayer" parameter from the connection-string ... and if that's not available, then it assumes you are using SQL Server.
Limit / Top - SQL Server vs MySQL
How clever is the SqlHelper? If I run "select top 100 * from Blah" will it fail in MySQL? Is there a good way of writing a limited query that will run on both? Or do I have to check the provider and adjust my SQL manually?
Hi Rob,
As far as I can tell, the SqlHelper will select the appropriate data-access-layer (whether that be SQL Server, MySQL, or other) ... it doesn't do any conversions between T-SQL and MySQL (SQL/PSM) syntax.
You'll probably need to grab all records, then limit them in code.
Cheers, Lee.
Okay, ta! Do you know off the top of your head how to check the current provider, so I can generate targeted SQL?
Lee's right, I think you'd have to either limit in code (not ideal for large datasets), or write some code to run different queries based on the database provider that the site is using.
The syntax for limiting in MySQL/SQL Server is completely different, so I don't think there's a query you culd write that will work on both I'm afraid.
Had a quick look in the "umbraco.DataLayer.DataLayerHelper.CreateSqlHelper" method to see how the API does it.
Basically it checks for the "datalayer" parameter from the connection-string ... and if that's not available, then it assumes you are using SQL Server.
Cheers, Lee.
Is that the connection string as in the config key "umbracoDbDSN"?
Yes. You can get it in code via the API
Cheers, Lee.
Brilliant, thanks Lee.
is working on a reply...