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
    Need to offer Shipping Insurance?
    Interactive Design Solutions https://www.myids.net
    MivaMerchant Business Partner | Certified MivaMerchant Web Developer
    Competitive Rates, Custom Modules and Integrations, Store Integration
    AutoBaskets|Advanced Waitlist Integration|Ask about Shipping Insurance Integration
    My T-shirt Collection is mostly MivaCon T-shirts!!

    #2
    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


      #3
      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

      Need to offer Shipping Insurance?
      Interactive Design Solutions https://www.myids.net
      MivaMerchant Business Partner | Certified MivaMerchant Web Developer
      Competitive Rates, Custom Modules and Integrations, Store Integration
      AutoBaskets|Advanced Waitlist Integration|Ask about Shipping Insurance Integration
      My T-shirt Collection is mostly MivaCon T-shirts!!

      Comment


        #4
        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


          #5
          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
          Need to offer Shipping Insurance?
          Interactive Design Solutions https://www.myids.net
          MivaMerchant Business Partner | Certified MivaMerchant Web Developer
          Competitive Rates, Custom Modules and Integrations, Store Integration
          AutoBaskets|Advanced Waitlist Integration|Ask about Shipping Insurance Integration
          My T-shirt Collection is mostly MivaCon T-shirts!!

          Comment


            #6
            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


              #7
              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
              Need to offer Shipping Insurance?
              Interactive Design Solutions https://www.myids.net
              MivaMerchant Business Partner | Certified MivaMerchant Web Developer
              Competitive Rates, Custom Modules and Integrations, Store Integration
              AutoBaskets|Advanced Waitlist Integration|Ask about Shipping Insurance Integration
              My T-shirt Collection is mostly MivaCon T-shirts!!

              Comment


                #8
                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


                  #9
                  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


                  Need to offer Shipping Insurance?
                  Interactive Design Solutions https://www.myids.net
                  MivaMerchant Business Partner | Certified MivaMerchant Web Developer
                  Competitive Rates, Custom Modules and Integrations, Store Integration
                  AutoBaskets|Advanced Waitlist Integration|Ask about Shipping Insurance Integration
                  My T-shirt Collection is mostly MivaCon T-shirts!!

                  Comment


                    #10
                    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