Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Rob Watkins 369 posts 701 karma points
    Feb 28, 2011 @ 11:31
    Rob Watkins
    0

    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?

  • Lee Kelleher 4026 posts 15836 karma points MVP 13x admin c-trib
    Feb 28, 2011 @ 13:00
    Lee Kelleher
    0

    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.

  • Rob Watkins 369 posts 701 karma points
    Feb 28, 2011 @ 13:55
    Rob Watkins
    0

    Okay, ta! Do you know off the top of your head how to check the current provider, so I can generate targeted SQL?

  • Tim 1193 posts 2675 karma points MVP 4x c-trib
    Feb 28, 2011 @ 13:56
    Tim
    0

    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.

  • Lee Kelleher 4026 posts 15836 karma points MVP 13x admin c-trib
    Feb 28, 2011 @ 14:07
    Lee Kelleher
    1

    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.

  • Rob Watkins 369 posts 701 karma points
    Feb 28, 2011 @ 14:09
    Rob Watkins
    0

    Is that the connection string as in the config key "umbracoDbDSN"?

  • Lee Kelleher 4026 posts 15836 karma points MVP 13x admin c-trib
    Feb 28, 2011 @ 14:12
    Lee Kelleher
    0

    Yes. You can get it in code via the API

    umbraco.GlobalSettings.DbDSN  (reference the "businesslogic.dll")

    Cheers, Lee.

  • Rob Watkins 369 posts 701 karma points
    Feb 28, 2011 @ 14:15
    Rob Watkins
    0

    Brilliant, thanks Lee.

Please Sign in or register to post replies

Write your reply to:

Draft