Prepared statement using EXECUTE in N1QL with arguments

SQL++
1

Hi,

I’m trying to run EXECUTE for a prepared statement in N1QL. Looking at the docs:

http://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/prepare.html#story-h2-2

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;

and

EXECUTE prepared1;

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.

Thanks.

2

You can do this with cbq shell. @isha, can you point to an example.

3

@geraldss Thanks.

@isha - To be clear, I am looking to do this in the workbench, not using the cbq shell.

Thanks.

4

@eben, does Query Workbench support N1QL EXECUTE?

5

@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.)

6

The N1QL REST API supports parameters.

1 Like
7

@eben - Is adding specification of parameter values for prepared statements planned for any time soon?

8

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.

9

@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:

http://dev.mysql.com/doc/refman/5.7/en/execute.html

PostgreSQL has a slightly different syntax (without the ‘USING’):

https://www.postgresql.org/docs/9.1/static/sql-execute.html

And Oracle seems similar to PG.

Definitely +1 from me for adding this to N1QL :wink:

1 Like
10

Hi @Eugaia

You can do this using the cbq shell . For example

prepare airinfo
from SELECT a.name, s.flight, s.utc, r.sourceairport, r.destinationairport, r.equipment
FROM travel-sample r
UNNEST r.schedule s
JOIN travel-sample a ON KEYS r.airlineid
WHERE r.sourceairport=$airport AND
r.destinationairport=$1 AND
s.day=$2
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
…

Thanks
Isha

1 Like
11

@isha Thanks for the example