Announcement

Collapse
No announcement yet.

Cannot use LIMIT on MySQL within a grid application

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Cannot use LIMIT on MySQL within a grid application

    If you have a grid application you cannot use limit as keyword. If you have select ... where ... order by ... limit 10 then SC will add 'ASC' to the sentence which will generate an sql error. If I do

    select ... where ... order by ... desc

    It works, the ASC is not added. But as soon as I append the limit statement the ASC is added again. It's an annoying bug as it will prevent me from using the grid as a nested grid because the resultset will become too large.
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

  • #2
    SC use LIMIT by default, don't use it manually ... SC sets LIMIT 0, (2 + Settings / Lines per Page). Bug or feature or a buggy feature?
    Best regards: - Reinhard -

    I use ScriptCase 8 Enterprise Edition, Version 8.(latest)

    Comment


    • #3
      It would be a great option... if it should work. In my case I use the grid as a nested grid. I set the lines/page to 2. I expect 4 records then. But when I click the + to open the nested grid I get all elements of the array and the lines/page option is ignored.
      Albert Drent
      aducom software netherlands
      scriptcase partner, reseller, support and (turn-key) development
      www.scriptcase.eu / www.scriptcase.nl

      Comment


      • #4
        So a buggy feature (I just tried it) ...
        Best regards: - Reinhard -

        I use ScriptCase 8 Enterprise Edition, Version 8.(latest)

        Comment


        • #5
          It depends. When you see the pagelimit to show a number of records and links to follow-up pages I guess it's a good option. But this doesn't work when the grid is used as a nested grid (you don't have the toolbar to move to other pages). In my case, I only want to show the 10 most significant items. With a complex statement I limit the searchresult by period of the last 120 days but there should be a possibility to limit the search. If I enter a limit, SC should ignore it's own default settings. Why is it appending ASC for?????
          Albert Drent
          aducom software netherlands
          scriptcase partner, reseller, support and (turn-key) development
          www.scriptcase.eu / www.scriptcase.nl

          Comment


          • #6
            Hello,

            You should set pagination to total on your grid's settings. By doing so you will be able to set a limit on your where clause.

            regards,
            Bernhard Bernsmann

            Comment


            • #7
              That would be nice, but it's a no - go:

              Fout
              Fout bij het openen van de database
              You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'asc' at line 1
              SELECT orgcode, meldingsdatum, zh_melding, percziek, herkomst, dohmpersnr from ziekmeldingafd where dohmpersnr=10107 order by meldingsdatum DESC LIMIT 6 asc


              It is based on this sql:
              SELECT
              dohmpersnr,
              herkomst,
              orgcode,
              meldingsdatum,
              zh_melding,
              percziek,
              redenziekcode,
              toelichting,
              deelbetrekkingperc,
              urenperweek,
              zieksequence,
              mutdat,
              mutby
              FROM
              ziekmeldingafd
              WHERE
              dohmpersnr=[glob_dohmpersnr]
              ORDER BY
              meldingsdatum DESC
              LIMIT 6

              This is with pagination to total. So it still doesn't work... The ASC again...
              It's a bug.
              Albert Drent
              aducom software netherlands
              scriptcase partner, reseller, support and (turn-key) development
              www.scriptcase.eu / www.scriptcase.nl

              Comment


              • #8
                Hello Mr. Drent,

                I believe the issue is happening due to your ORDER BY clause. Could you remove the order by statement from your SQL, and use SC sorting settings (http://downloads.scriptcase.com.br/d...Odenacao.htm)? Which will do the same thing.

                If it doesn't work please let me know. Also notice that a palliative measure would be to pass the current clause (the limit part) to retrive only the IDs, and use those IDs on the where clause of an outer sql.

                E.j.:

                SELECT
                dohmpersnr,
                herkomst
                FROM
                ziekmeldingafd
                WHERE
                SomeID IN (SELECT SomeID FROM ziekmeldingafd WHERE dohmpersnr=[glob_dohmpersnr] ORDER BY meldingsdatum DESC LIMIT 6)

                regards,
                Bernhard Bernsmann

                Comment

                Working...
                X