October 25, 2016, 6:41pm
I’m trying to run EXECUTE for a prepared statement in N1QL. Looking at the docs:
There is no example for using EXECUTE in N1QL, only using CURL.
I have successfully prepared and executed a statement using the following:
PREPARE prepared1 FROM SELECT * FROM `beer-sample` LIMIT 100;
I can prepare a second statement using:
PREPARE prepared2 FROM SELECT * FROM `beer-sample` WHERE geo.accuracy != $acc AND country = $co;
However I have not been able to find a way to execute the statement using:
EXECUTE prepared2 ... ;
where the … includes the arguments $acc and $co (I’ve tried using WHERE|USING|WITH $acc=…, and I’ve tried using brackets and creating an object).
Is it possible to use the EXECUTE with arguments in N1QL - and if so, how? There is no documentation for this that I could find.
Note: I know that it can be done with Curl, I would just like to do it from the workbench using N1QL.
You can do this with cbq shell.
@isha, can you point to an example.
October 25, 2016, 8:27pm
@isha - To be clear, I am looking to do this in the workbench, not using the cbq shell.
@eben, does Query Workbench support N1QL EXECUTE?
October 25, 2016, 8:41pm
@geraldss - Query Workbench permits “EXECUTE”, but it does not support specification of values for parameters, which limits the usefulness.
(In fact, N1QL does not currently support specification of parameter values for prepared statements. Isha has a non-N1QL command for doing so in cbq.)
The N1QL REST API supports parameters.
October 25, 2016, 8:50pm
@eben - Is adding specification of parameter values for prepared statements planned for any time soon?
October 25, 2016, 9:00pm
Support for parameters is not on the feature list for the next release, but it would not be out of the question should there be sufficient demand heard by product management.
October 25, 2016, 9:10pm
@eben Thanks for the quick response.
It seems odd to me that this feature is not already available with prepared statements as part of N1QL as it makes them currently impossible to execute from the workbench.
If a syntax has not yet been proposed for N1QL for this, can I suggest:
EXECUTE prepared_statement USING $param1 = value1 [, $param2 = value2 ...];
This follows a similar pattern to MySQL:
PostgreSQL has a slightly different syntax (without the ‘USING’):
And Oracle seems similar to PG.
Definitely +1 from me for adding this to N1QL
October 25, 2016, 9:29pm
You can do this using the cbq shell . For example
from SELECT a.name, s.flight, s.utc, r.sourceairport, r.destinationairport, r.equipment
UNNEST r.schedule s
travel-sample a ON KEYS r.airlineid
WHERE r.sourceairport=$airport AND
ORDER BY a.name;
\SET -$airport “SJC”; # Set named parameter airport for source airport
\SET -args [“LAX”, 6 ]; # Set positional parameters for destination airport and day
execute airinfo; # Execute prepared statement airing
October 25, 2016, 9:32pm
@isha Thanks for the example