need a working query string

[ids]

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:

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:

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.

<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

[dcarver]

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.

[ids]

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

[Kent Multer]

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 :^) .

[ids]

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?

    
<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

[Bruce - PhosphorMedia]

what happens when you just use l.code in the query string? (just has a test)

[ids]

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

[Kent Multer]

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.

[ids]

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

[dcarver]

Your query should just be

{ '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.