Announcement

Collapse
No announcement yet.

MivaSQL and semicolons?

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

    MivaSQL and semicolons?

    Hi folks --

    A couple of years ago, IIRC, someone posted a notice that MivaSQL sometimes requires semicolons at the end of queries, otherwise it will generate error message. Since then, I've usually put semicolons at the end of all queries I write, to be safe for both MivaSQL and MySQL.

    In the last couple of weeks, I've gotten a couple of reports of module errors, and now it seems like the problem is the exact opposite. MivaSQL is saying:

    Syntax Error: Expected operator, found ';'

    -- when it encounters a perfectly valid query that ends with a semicolon. In some cases, the error message is "SQL command not properly ended."

    So, a few questions:

    - What's the real deal on semicolons? Did something change recently?

    - Is Miva starting to use MivaSQL by default when creating new stores, or upgrading old ones? I'm surprised that two of my clients have had these problems recently; both of them had been using my module for a long time. I wonder if there are going to be more of these popping up?

    - Can we get an update to MivaSQL that will fix this? If so, how soon?

    Thanks --
    Last edited by Kent Multer; 03-02-15, 03:16 PM.
    Kent Multer
    Magic Metal Productions
    http://TheMagicM.com
    * Web developer/designer
    * E-commerce and Miva
    * Author, The Official Miva Web Scripting Book -- available on-line:
    http://www.amazon.com/exec/obidos/IS...icmetalproducA

    #2
    Re: MivaSQL and semicolons?

    MivaSQL should never have required semicolons in any query, as its parser has never (to my knowledge) even recognized semicolons.

    5.21 fixed a long-standing bug in the SQL parser that would allow semicolons and other unrecognized characters through, which is probably why you're seeing issues now. That particular bug had a number of major query logic ramifications (such as treating "!=" as "="), so we have no plans to roll back the fix.

    We are not deploying any new sites with MivaSQL. All of our new deployments are MySQL/MariaDB. I suspect you are seeing issues now due to the 5.21/5.22 engine being more widely deployed.

    Comment


      #3
      Re: MivaSQL and semicolons?

      FWIW...We dropped support of MivaSQL about 3/4 years ago. No one who is on that type of platform is serious about running an eCommerce web site and if they aren't serious, they are not buying our modules or using our development services.
      Bruce Golub
      Phosphor Media - "Your Success is our Business"

      Improve Your Customer Service | Get MORE Customers | Edit CSS/Javascript/HTML Easily | Make Your Site Faster | Get Indexed by Google | Free Modules | Follow Us on Facebook
      phosphormedia.com

      Comment


        #4
        Re: MivaSQL and semicolons?

        So, just to be clear, is this problem going to show up on MySQL as well as MivaSQL, when users go to the 5.21 engine?

        And is semicolon really an "unrecognized" character? I thought it was a standard part of the SQL grammar.

        Thanks --
        Kent Multer
        Magic Metal Productions
        http://TheMagicM.com
        * Web developer/designer
        * E-commerce and Miva
        * Author, The Official Miva Web Scripting Book -- available on-line:
        http://www.amazon.com/exec/obidos/IS...icmetalproducA

        Comment


          #5
          Re: MivaSQL and semicolons?

          That sucks Kent. Fortunate for me, I abandoned the semicolon long ago, however, nearly every example I've found has the semicolon at the end of the statement. This includes http://www.w3schools.com/sql/default.asp and http://dev.mysql.com

          It also seems that the semicolon is expected in the ANSI SQL-92 standard.

          MivaScript.com links to http://www.sql-statements.com (awful site) where none of the examples contain a semicolon.

          I you were writing a SQL batch script that contains multiple lines then the ; would terminate each line. But I don't think that's possible with MivaScript.

          I suspect this change could break a lot of queries.

          Perhaps its time do add some content to the SQL sections on MivaScript.com
          Ray Yates
          "If I have seen further, it is by standing on the shoulders of giants."
          --- Sir Isaac Newton

          Comment


            #6
            Re: MivaSQL and semicolons?

            Originally posted by RayYates View Post

            Perhaps its time do add some content to the SQL sections on MivaScript.com

            Yes please. Its hard for my little brain to translate "MySQL" resources explanations and mivascript mysql statements.
            Bruce Golub
            Phosphor Media - "Your Success is our Business"

            Improve Your Customer Service | Get MORE Customers | Edit CSS/Javascript/HTML Easily | Make Your Site Faster | Get Indexed by Google | Free Modules | Follow Us on Facebook
            phosphormedia.com

            Comment


              #7
              Re: MivaSQL and semicolons?

              Ray brought up a good point:
              Originally posted by RayYates View Post
              If you were writing a SQL batch script that contains multiple lines then the ; would terminate each line. But I don't think that's possible with MivaScript.
              From time to time, I've considered writing an MvQUERY that sends a series of UPDATE, INSERT, or DELETE commands, separated by semicolons. That could speed up the execution, compared to using a separate MvQUERY for each one. But from what's been written here, it seems like that won't work (and never would have, if I'd tried it). Burch, is that correct?

              Thanks --
              Kent Multer
              Magic Metal Productions
              http://TheMagicM.com
              * Web developer/designer
              * E-commerce and Miva
              * Author, The Official Miva Web Scripting Book -- available on-line:
              http://www.amazon.com/exec/obidos/IS...icmetalproducA

              Comment


                #8
                Re: MivaSQL and semicolons?

                I checked with Burch and he confirmed, MivaSQL has never supported compound statements.
                Thanks,

                Rick Wilson
                CEO
                Miva, Inc.
                [email protected]
                https://www.miva.com

                Comment

                Working...
                X