Announcement

Collapse
No announcement yet.

need a working query string

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

  • need a working query string

    I'm working on a custom module ( are there any others these days?).

    This is a raw MySQL query string that works perfectly in phpMyAdmin:
    Code:
    SELECT * FROM s01_mytablename WHERE mytablecode = 'mycodevalue'
    This is a _Load_Code style query (think Product_Load_Code as the prototype). I doesn't return any result but the EOF isn't found either:
    Code:
    QUERY    = "{ 'SELECT * FROM ' $ g.Store_Table_Prefix $ 'mytablename WHERE ' $ [ g.Library_Filename_Native_DBAPI ].DB_Compare_UPPER( 'mytablecode' ) $ ' = ' $ [ g.Library_Filename_Native_DBAPI ].DB_Compare_UPPER( '?' ) }"
    FIELDS    = "l.code">
    I rewrote the query to avoid the FIELDS attribute and include the opening and closing single quote: No Joy here either.
    Code:
    <MvASSIGN NAME = "l.myquerystring" VALUE = "{ 'SELECT * FROM ' $ g.Store_Table_Prefix $ 'mytablename WHERE ' $ 'mytablecode'  $ ' = \'' $ l.code $ '\'' }">
    
    QUERY    = "{ l.myquerystring }">
    I've also used encodeenties() on l.myquerystring.

    Any ideas on what to check?

    BTW: I have retyped the whole function to eliminate hidden characters as a problem from any copy and paste scenarios.

    Thanks,

    Scott
    What help do you need today!
    Interactive Design Solutions http://www.southbound.com
    MivaMerchant Business Partner | Certified MivaMerchant Web Developer
    My T-shirt Collection is mostly MivaCon T-shirts!!
    Competitive Rates, Popular Modules, and Integrations:
    Product Copy | AutoBaskets | Waitlist Integration| Wholesale Integration

  • Your MvDO file will need to be g.Module_Library_Native_DBAPI and not g.Library_Filename_Native_DBAPI. Are you getting MvQUERY errors? Are you running this in a function with an ERROROUTPUTLEVEL? You definitely need to use the FIELDS attribute or else you'll be exposing your module and customers to SQL injection. Output g.MvQUERY_Error to see any SQL related issues.
    David Carver
    Miva, Inc. | Software Developer

    Comment


    • Thanks David. I'll run this down in my functions.

      No risk with the Field attribute because this was only testing the variable I was passing to the query. Production level won't have this "technique."

      Scott

      What help do you need today!
      Interactive Design Solutions http://www.southbound.com
      MivaMerchant Business Partner | Certified MivaMerchant Web Developer
      My T-shirt Collection is mostly MivaCon T-shirts!!
      Competitive Rates, Popular Modules, and Integrations:
      Product Copy | AutoBaskets | Waitlist Integration| Wholesale Integration

      Comment


      • In cases like this I often use MvEVAL to output the query string to my screen, to make sure it's correct. Also, I would check to make sure that your l.code variable actually contains the intended value; maybe use MvEVAL for that too.

        And Yes, I still write custom modules. I've done about half a dozen new ones so far in 2020, as well as numerous updates to older ones. Some of them have been in use for more than ten years. Makes me feel old ... but in a good way :^) .
        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


        • Hi David,

          I'm not sure if I've moved any further along but, after pulling the query outside of the function and running inline, I am seeing the OpenView error ( with or without the single quotes around the ? ). I don't see any issues with the syntax. Is there?

          Code:
              
          <MvOPENVIEW NAME="Merchant" VIEW="AnSQLView"
                  QUERY = "{ 'SELECT * FROM ' $ g.Store_Table_Prefix $ 'mytablename WHERE ' $ 'mytablecode ' $ ' = \'' $ '?' $ '\'' }"
                  FIELDS = "l.code">
          This is the error:
          MvOPENVIEW: Input parameter count mismatch: Found 1, expected 0
          So, there is the param count mismatch and when doing the CloseView:
          MvCLOSEVIEW: Database 'Merchant' has no view named 'AnSQLView'
          AnSQLView is a made-up name and doesn't exist anywhere else in the module or as a table name (if it matters).

          Scott
          What help do you need today!
          Interactive Design Solutions http://www.southbound.com
          MivaMerchant Business Partner | Certified MivaMerchant Web Developer
          My T-shirt Collection is mostly MivaCon T-shirts!!
          Competitive Rates, Popular Modules, and Integrations:
          Product Copy | AutoBaskets | Waitlist Integration| Wholesale Integration

          Comment


          • what happens when you just use l.code in the query string? (just has a test)
            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


            • Result is exactly the same as the phpmyadmin query in my initial post. IOW, it's what is expected -- with and without the single quotes. However, single quotes seem to be required in phpmyadmin query window for a string value.

              So now am hoping to find out how/why a parameter mismatch is happening.

              Scott
              What help do you need today!
              Interactive Design Solutions http://www.southbound.com
              MivaMerchant Business Partner | Certified MivaMerchant Web Developer
              My T-shirt Collection is mostly MivaCon T-shirts!!
              Competitive Rates, Popular Modules, and Integrations:
              Product Copy | AutoBaskets | Waitlist Integration| Wholesale Integration

              Comment


              • I don't think you can put a question mark inside quotes. That causes the VM to think you're searching for an actual question mark, not a FIELDS parameter. When using a question-mark parameter to pass a string value, you don't need to put quotes around it; the VM will recognize what you're trying to do.
                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


                • Thanks, Kent. I think you're confirming what I've thought about the single quotes. Code in the LSK supports this too IMO. But, I included this report, with/without quotes, because I wanted to try and eliminate a potential question of, "Did you try...xxx...?"

                  It's probably some stupid mistake., but the cause for error message should be enlightening.

                  Scott


                  What help do you need today!
                  Interactive Design Solutions http://www.southbound.com
                  MivaMerchant Business Partner | Certified MivaMerchant Web Developer
                  My T-shirt Collection is mostly MivaCon T-shirts!!
                  Competitive Rates, Popular Modules, and Integrations:
                  Product Copy | AutoBaskets | Waitlist Integration| Wholesale Integration

                  Comment


                  • Your query should just be
                    Code:
                    { 'SELECT * FROM ' $ g.Store_Table_Prefix $ 'mytablename WHERE mytablecode = ?' }
                    And the reason you're getting the MvCLOSEVIEW error is because your MvOPENVIEW was not successful.
                    David Carver
                    Miva, Inc. | Software Developer

                    Comment

                    • Working...
                      X

                      This website uses cookies to identify visitors, track visitors to our website, store login session information and to remember your user preferences. By continuing to use this site you agree to our use of cookies. Learn More.

                      This website uses cookies. By continuing to use this site you agree to our use of cookies. Learn More.

                      Accept