Custom query multiple variables

Wappler General How To
#1

Struggling with the variables in a custom query.

When I use ONE variable (:P1) it works fine, but as soon as I include another one (:P2) it does not provide a result.

To illustrate, this is my query:

Screen Shot 2020-08-09 at 10.08.01
Screen Shot 2020-08-09 at 10.08.01699×268 21.3 KB

The above works well. But as soon as I replace UserID = 1 with UserID = :P2, it doesn’t work.

I searched the forum but couldn’t find any answers. What am I doing wrong?

#2

What server model and database are you using?

#3

Good question, how can I find this info? - sorry I am still kinda new to this stuff…

#4

Does this help?

Screen Shot 2020-08-09 at 14.01.46

#5

So you have included the extra variable P2 in your list of variables, but I don’t see it used in your custom query?

Maybe you forgot to replace the value 1 with :P2

#6

That is exactly the issue, once I replace value 1 with :P2 the query doesn’t work anymore.

Then, when I only use the :P2 and delete the ModuleID = :P1, it works… :confused:

#7

This is probably because the value of your identity is empty, like when the user is not logged in yet.

#8

Thanks for the suggestion, but unfortunately still no luck. Also, shouldn’t the “test value” override the “expression” when pressing the play button (execute query) in this window?

#9

Do you get an error or just an empty resultset?

#10

@patrick sorry for delay - I get an empty result.

#11

Do you know if the adminsecurity.identity expression returns the correct id? Perhaps it is null or an other unexpected value, the query will then return an empty result since no records match the condition.

#12

Thanks @patrick for the suggestion but I don’t think that is the problem.

This is my table:
Screen Shot 2020-08-19 at 10.58.54

When I use :P1 only, it works:Screen Shot 2020-08-19 at 10.55.13

When I add “ModuleID” and define it, it works too. So far so good.
Screen Shot 2020-08-19 at 10.55.27

However, when I replace the ModuleID with a variable :P2 (=“objectives”), it stops working:
Screen Shot 2020-08-19 at 11.02.40
Screen Shot 2020-08-19 at 10.55.41

#13

Here 2 thing you could try

  1. replace :P1 and :P2 with a ?.
  2. use different parameter names like :userid and :moduleid.
#14

Hi @patrick - thanks for your suggestions and apologies it took a while to get into this.

  1. Using different parameters doesn’t make a difference.
  2. Replacing with ? works! What is the logic behind this? Any reading I can do to better understand?

Struggling with this issue for a while over various projects, so some further insight on how to address this would be very much welcomed.

#15

? Parameters are “positional” and the colon parameters are “named”. You can google named and positional parameters for whichever db server you are using to learn more.

#16

Thanks, that makes sense. Still wondering why named variables is giving me issues. Could this be a database server issue?