Please use the form below to provide your feedback. Because your feedback is valuable to us, the information you submit in this form is recorded in our issue tracking system (JIRA), which is publicly available. You can track the status of your feedback using the ticket number displayed in the dialog once you submit the form.

Configure Queries

  • Capella Operational
    You can configure the Query Service using request-level query parameters.

    Request-Level Parameters

    To set a request-level parameter, do one of the following:

    • Use the Query Options window in the Query tab.

    • Use the cbq shell at the command line.

    • Make a REST API call to the Data API (Query Service passthrough).

    • Use an SDK client program.

    Generally, use the cbq shell or the Query tab as a sandbox to test queries in your Capella cluster. Use a REST API call or an SDK client program for your production queries.

    • Capella UI

    • Command Line

    • REST API

    To set request-level preferences in the Query tab:

    1. Go to Data Tools  Query and click Query Options to display the Query Options window.

    2. Specify the preferences — if a preference is not explicitly listed, click + next to Named Parameters and add its name and value.

    3. Click Save.

    The Query Options window.

    For more information, see Modify Query Settings.

    To set request-level parameters in cbq, use the \SET command. The parameter name must be prefixed by a hyphen.

    This example sets the request-level timeout, pretty-print, and max parallelism parameters, and runs a query:

    \SET -timeout "30m";
\SET -pretty true;
\SET -max_parallelism 3;
SELECT * FROM "world" AS hello;

    For more information, see Parameter Manipulation in the cbq documentation.

    To set request-level parameters with the Data API, specify the parameters in the request body or the query URI.

    In the following examples:

    • $BASEURL is the base URL for the Data API.

    • $USER is the cluster access username.

    • $PASSWORD is the cluster access secret.

    This example sets the request-level timeout, pretty-print, and max parallelism parameters, and runs a query:

    curl $BASEURL/_p/query/query/service -u $USER:$PASSWORD \
  -d 'statement=SELECT * FROM "world" AS hello;
    & timeout=30m
    & pretty=true
    & max_parallelism=3'

    The table below contains details of all request-level parameters, along with examples.

    Table 1. Request-Level Parameters
    Property Schema

    args
    optional

    Supplies the values for positional parameters in the statement. Applicable if the statement or prepared statement contains 1 or more positional parameters.

    The value is an array of JSON values, one for each positional parameter in the statement.

    Refer to Named Parameters and Positional Parameters for details.

    Example: ["LAX",6]

    Any Type array

    atrcollection
    optional

    Specifies the collection where the active transaction record (ATR) is stored. The collection must be present. If not specified, the ATR is stored in the default collection in the default scope in the bucket containing the first mutated document within the transaction.

    The value must be a string in the form "bucket.scope.collection" or "namespace:bucket.scope.collection". If any part of the path contains a special character, that part of the path must be delimited in backticks ``.

    Example: "default:`travel-sample`.transaction.test"

    String

    auto_execute
    optional

    Specifies that prepared statements should be executed automatically as soon as they are created. This saves you from having to make two separate requests in cases where you want to prepare a statement and execute it immediately.

    Refer to Auto-Execute for more information.

    Default: false
    Example: true

    Boolean

    client_context_id
    optional

    A piece of data supplied by the client that is echoed in the response, if present. SQL++ is agnostic about the content of this parameter; it is just echoed in the response.

    • Maximum allowed size is 64 characters; all others will be cut.
    • If it contains an escape character / or quote ", it will be rejected as error code 1110.

    String

    compression
    optional

    Compression format to use for response data on the wire.

    Values are case-insensitive.

    Values: "ZIP", "RLE", "LZMA", "LZO", "NONE"
    Default: "NONE"
    Example: "zip"

    String

    controls
    optional

    Specifies if there should be a controls section returned with the request results.

    When set to true, the query response document includes a controls section with runtime information provided along with the request, such as positional and named parameters or settings.

    If the request qualifies for caching, these values will also be cached in the completed_requests system keyspace.

    Example: true

    Boolean

    creds
    optional

    In the Data API, this parameter is ignored and has no effect.

    In the cbq shell, this parameter specifies credentials within a session. For more information, see Switch Between Credentials.

    Nullable: yes

    Any Type

    durability_level
    optional

    The level of durability for mutations produced by the request.

    If the request contains a BEGIN TRANSACTION statement, or a DML statement with the tximplicit parameter set to true, the durability level is specified for all mutations within that transaction.

    Durability is also supported for non-transactional DML statements. In this case, the kvtimeout parameter is used as the durability timeout.

    If not specified, the default durability level is "majority". Set the durability level to "none" or "" to specify no durability.

    Values: "", "none", "majority", "majorityAndPersistActive", "persistToMajority"
    Default: "majority"
    Example: "none"

    String

    encoded_plan
    optional

    In clusters running Couchbase Server 6.5 and later, this parameter is ignored and has no effect. It is included for compatibility with previous versions.

    String

    encoding
    optional

    Desired character encoding for the query results.

    Only possible value is UTF-8 and is case-insensitive.

    Default: "UTF-8"

    String

    format
    optional

    Desired format for the query results.

    Values are case-insensitive.

    Values: "JSON", "XML", "CSV", "TSV"
    Default: "JSON"
    Example: "XML"

    String

    kvtimeout
    optional

    The approximate time to wait for a KV get operation before timing out. This applies to statements within a transaction, and to non-transactional statements, whether durability_level is set or not.

    If use_replica is enabled for a query, then this parameter also specifies the approximate time to wait before fetching data from a replica vBucket when the active vBucket is inaccessible.

    The value for this parameter is a string. Its format includes an amount and a mandatory unit, e.g. 10ms (10 milliseconds) or 0.5s (half a second). Valid units are:

    • ns (nanoseconds)
    • us (microseconds)
    • ms (milliseconds)
    • s (seconds)
    • m (minutes)
    • h (hours)

    Specify a duration of 0 or a negative duration to disable. When disabled, no timeout is applied and the KV operation runs for however long it takes.

    Default: "2.5s"
    Example: "10ms"

    String

    max_parallelism
    optional

    Specifies the maximum parallelism for the query.

    The default value is the same as the number of partitions of the index selected for the query.

    Example: 3

    Integer (int32)

    memory_quota
    optional

    Specifies the maximum amount of memory the request may use, in MB.

    Specify 0 (the default value) to disable. When disabled, there is no quota.

    This parameter enforces a ceiling on the memory used for the tracked documents required for processing a request. It does not take into account any other memory that might be used to process a request, such as the stack, the operators, or some intermediate values.

    Within a transaction, this setting enforces the memory quota for the transaction by tracking the delta table and the transaction log (approximately).

    Default: 0
    Example: 4

    Integer (int32)

    metrics
    optional

    Specifies that metrics should be returned with query results.

    Default: true
    Example: false

    Boolean

    namespace
    optional

    Specifies the namespace to use. Currently, only the default namespace is available.

    Example: "default"

    String

    natural
    optional

    Couchbase Server 8.0

    The prompt for a natural language request. The Query Service uses the prompt to generate a SQL++ statement.

    If the generated statement is a SELECT statement, the generated statement is returned and executed automatically.

    If the generated statement is not a SELECT statement, the generated statement is returned, but not executed. In this case, you must verify the statement and execute it in a separate request.

    Natural language requests use the Couchbase Capella iQ service as a backend. To make a natural language request, you must have a Couchbase Capella user account.

    This parameter is available in clusters running Couchbase Server 8.0 and later.

    To use this parameter, you must also specify the natural_cred, natural_orgid, and natural_context parameters. If you don't specify all four parameters, the Query Service returns an error.

    Example: "Show me count of airlines per country"

    String

    natural_cred
    optional

    Couchbase Server 8.0

    The Couchbase Capella user account credentials for a natural language request, in the form username:password. Be careful not to expose the credentials in log files or other output.

    This parameter does not support single sign-on (SSO), multi-factor authentication (MFA), or social login credentials such as Google or GitHub.

    Natural language requests use the Couchbase Capella iQ service as a backend. To make a natural language request, you must have a Couchbase Capella user account.

    This parameter is available in clusters running Couchbase Server 8.0 and later.

    To use this parameter, you must also specify the natural, natural_orgid, and natural_context parameters. If you don't specify all four parameters, the Query Service returns an error.

    Example: "username:password"

    String (password)

    natural_orgid
    optional

    Couchbase Server 8.0

    The Couchbase Capella organization ID for a natural language request.

    Natural language requests use the Couchbase Capella iQ service as a backend. To make a natural language request, you must have a Couchbase Capella user account.

    This parameter is available in clusters running Couchbase Server 8.0 and later.

    To use this parameter, you must also specify the natural, natural_cred, and natural_context parameters. If you don't specify all four parameters, the Query Service returns an error.

    UUID (uuid)

    natural_context
    optional

    Couchbase Server 8.0

    A list of paths specifying keyspaces for a natural language request. The Query Service infers the schema of each keyspace, in order to give more precise responses from the natural language request.

    The parameter may contain up to four paths, separated by commas. Spaces are allowed. Each path may be:

    • A full path, in the form bucket.scope.collection or namespace:bucket.scope.collection.

    • A path prefix, in the form namespace:bucket or bucket, to specify the default collection in the default scope.

    • A partial path, in the form collection. In this case, you must specify the query_context parameter to provide the bucket and scope.

    Natural language requests use the Couchbase Capella iQ service as a backend. To make a natural language request, you must have a Couchbase Capella user account.

    This parameter is available in clusters running Couchbase Server 8.0 and later.

    To use this parameter, you must also specify the natural, natural_cred, and natural_orgid parameters. If you don't specify all four parameters, the Query Service returns an error.

    Example: "travel-sample, travel-sample.inventory.airline, airline"

    String

    natural_output
    optional

    Couchbase Server 8.0

    Specifies the required output for a natural language request.

    • sql — The output is a SQL++ statement.

    • jsudf — The output is a CREATE FUNCTION statement which you can use to generate a SQL++ managed JavaScript user-defined function.

    • ftssql — The output is a SQL++ statement which can use a Flex index, if available.

    Natural language requests use the Couchbase Capella iQ service as a backend. To make a natural language request, you must have a Couchbase Capella user account.

    This parameter is available in clusters running Couchbase Server 8.0 and later.

    Values: "sql", "jsudf", "ftssql"
    Default: "sql"

    String

    numatrs
    optional

    Reserved for future use. This parameter is ignored and has no effect.

    Integer (int32)

    pipeline_batch
    optional

    Controls the number of items execution operators can batch for Fetch from the KV.

    Example: 64

    Integer (int32)

    pipeline_cap
    optional

    Maximum number of items each execution operator can buffer between various operators.

    Example: 1024

    Integer (int32)

    prepared
    optional

    Required if statement or natural not provided.

    The name of the prepared SQL++ statement to be executed. Refer to EXECUTE for examples.

    If both prepared and statement are present and non-empty, an error is returned.

    Example: "[127.0.0.1:8091]pricy_hotel"

    String

    preserve_expiry
    optional

    Specifies whether documents should keep their current expiration setting when modified by a DML statement.

    If true, documents will keep any existing expiration setting when modified by a DML statement. If the DML statement explicitly specifies the document expiration, the statement overrides this parameter, and the expiration is changed.

    If false, document expiration is set to 0 when modified by a DML statement, unless the DML statement explicitly specifies the document expiration.

    Not supported for statements in a transaction.

    Default: false
    Example: true

    Boolean

    pretty
    optional

    Specifies the query results returned in pretty format.

    Example: false

    Boolean

    profile
    optional

    Specifies if there should be a profile section returned with the request results. The valid values are:

    • off — No profiling information is added to the query response.

    • phases — The query response includes a profile section with stats and details about various phases of the query plan and execution. Three phase times will be included in the system:active_requests and system:completed_requests monitoring keyspaces.

    • timings — Besides the phase times, the profile section of the query response document will include a full query plan with timing and information about the number of processed documents at each phase. This information will be included in the system:active_requests and system:completed_requests keyspaces.

    If profile is not set as one of the above values, then the profile setting does not change.

    Values: "off", "phases", "timings"
    Example: "phases"

    String

    query_context
    optional

    Specifies the namespace, bucket, and scope used to resolve partial keyspace references within the request.

    The query context may be a full path, containing namespace, bucket, and scope; or a relative path, containing just the bucket and scope. Currently, only the default namespace is available. If the namespace name is omitted, the default namespace in the current session is used.

    Default: "default:"
    Example: "default:travel-sample.inventory"

    String

    readonly
    optional

    Controls whether a query can change a resulting recordset.

    If readonly is true, then the following statements are not allowed:

    • CREATE INDEX
    • DROP INDEX
    • INSERT
    • MERGE
    • UPDATE
    • UPSERT

    When using GET requests, it's best to set readonly to true.

    Default: false
    Example: true

    Boolean

    scan_cap
    optional

    Maximum buffered channel size between the indexer client and the query service for index scans. This parameter controls when to use scan backfill.

    Use 0 or a negative number to disable. Smaller values reduce GC, while larger values reduce indexer backfill.

    Example: 1024

    Integer (int32)

    scan_consistency
    optional

    Specifies the consistency guarantee or constraint for index scanning. The valid values are:

    • not_bounded — No timestamp vector is used in the index scan. This is the fastest mode, because it avoids the costs of obtaining the vector and waiting for the index to catch up to the vector.

    • at_plus — This implements bounded consistency. The request includes a scan_vector parameter and value, which is used as a lower bound. This can be used to implement read-your-own-writes (RYOW).

    • request_plus — This implements strong consistency per request. Before processing the request, a current vector is obtained. The vector is used as a lower bound for the statements in the request. If there are DML statements in the request, RYOW is also applied within the request. (If request_plus is specified in a query that runs during a failover of an index node, the query waits until the rebalance operation completes and the index data has rebalanced before returning a result.)

    • statement_plus — This implements strong consistency per statement. Before processing each statement, a current vector is obtained and used as a lower bound for that statement.

    Values are case-insensitive.

    For multi-statement requests, the default behavior is RYOW within each request. If you want to disable RYOW within a request, add a separate request_consistency parameter that can be set to not_bounded.

    If the request contains a BEGIN TRANSACTION statement, or a DML statement with the tximplicit parameter set to true, then this parameter sets the transactional scan consistency. Refer to Transactional Scan Consistency for details.

    Values: "not_bounded", "at_plus", "request_plus", "statement_plus"
    Default: "not_bounded"
    Example: "at_plus"

    String

    scan_vector
    optional

    Required if scan_consistency is at_plus and scan_vectors not provided.

    Specify the lower bound vector timestamp for one keyspace when using at_plus scan consistency.

    Scan vectors are built of two-element [value, guard] entries:

    • value: a vBucket's sequence number (a JSON number)
    • guard: a vBucket's UUID (a string)

    Scan vectors have two forms:

    1. Full: an array of [value, guard] entries, giving an entry for every vBucket in the system.
    2. Sparse: an object providing entries for specific vBuckets, mapping a vBucket number (a string) to each [value, guard] entry.

    Note that scan_vector can only be used if the query uses at most one keyspace; if it is used for a query referencing more than one keyspace, the query will fail with an error.

    For queries referencing multiple keyspaces, use scan_vectors.

    Example: {"5":[5409393,"VB5ID"],"19":[47574574,"VB19ID"]}

    Object

    scan_vectors
    optional

    Required if scan_consistency is at_plus and scan_vector not provided.

    A map from keyspace names to scan vectors. See scan_vector.

    The scan vectors can be Full or Sparse.

    Object

    scan_wait
    optional

    Can be supplied with scan_consistency values of request_plus, statement_plus and at_plus.

    Specifies the maximum time the client is willing to wait for an index to catch up to the vector timestamp in the request.

    Specifies how much time the client is willing to wait for the indexer to satisfy the required scan_consistency and scan_vector criteria. After receiving the scan request, if the indexer is unable to catch up within the scan_wait time and initiate the scan, the indexer aborts with an error and the scan fails.

    Its format includes an amount and a mandatory unit, e.g. 10ms (10 milliseconds) or 0.5s (half a second). Valid units are:

    • ns (nanoseconds)
    • us (microseconds)
    • ms (milliseconds)
    • s (seconds)
    • m (minutes)
    • h (hours)

    Specify 0 or a negative integer to disable.

    Default: ""
    Example: "30m"

    String (duration)

    signature
    optional

    Include a header for the results schema in the response.

    Default: true
    Example: false

    Boolean

    sort_projection
    optional

    If true, causes statement projection terms to be sorted alphabetically.

    If false (the default), statement projection terms are returned in the order specified by the query.

    Default: false
    Example: true

    Boolean

    statement
    optional

    Required if prepared or natural not provided.

    Any valid SQL++ statement for a POST request, or a read-only SQL++ statement (SELECT, EXPLAIN) for a GET request.

    If both prepared and statement are present and non-empty, an error is returned.

    When specifying the request parameters as form data, the statement may not contain an unescaped semicolon (;). If it does, the Query Service responds with error 1040. To avoid this, either URL-encode the semicolon as %3B, or just omit the semicolon if possible.

    This restriction does not apply when specifying the request parameters in JSON format.

    Example: "SELECT * FROM `travel-sample`.inventory.hotel LIMIT 1"

    String

    timeout
    optional

    Maximum time to spend on the request before timing out (s).

    The value for this parameter is a string. Its format includes an amount and an optional unit: for example, 10ms (10 milliseconds) or 0.5s (half a second). If not specified, the default unit is s (seconds). Valid units are:

    • ns (nanoseconds)
    • us (microseconds)
    • ms (milliseconds)
    • s (seconds)
    • m (minutes)
    • h (hours)

    Specify a duration of 0 or a negative duration to disable. When disabled, no timeout is applied and the request runs for however long it takes.

    If tximplicit or txid is set, this parameter is ignored. The request inherits the remaining time of the transaction as timeout.

    Example: "30m"

    String (duration)

    txdata
    optional

    Transaction data. For internal use only.

    Object

    txid
    optional

    Required for statements within a transaction.

    Transaction ID. Specifies the transaction to which a statement belongs. For use with DML statements within a transaction, rollbacks, and commits.

    The transaction ID should be the same as the transaction ID generated by the BEGIN TRANSACTION statement. The transaction must be active and non-expired.

    Example: "d81d9b4a-b758-4f98-b007-87ba262d3a51"

    UUID (UUID)

    tximplicit
    optional

    Specifies that a DML statement is a singleton transaction.

    When this parameter is true, the Query service starts a transaction and executes the statement. If execution is successful, the Query service commits the transaction; otherwise the transaction is rolled back.

    The statement may not be part of an ongoing transaction. If the txid request-level parameter is set, the tximplicit parameter is ignored.

    Default: false
    Example: true

    Boolean

    txstmtnum
    optional

    Transaction statement number. The transaction statement number must be a positive integer, and must be higher than any previous transaction statement numbers in the transaction. If the transaction statement number is lower than the transaction statement number for any previous statement, an error is generated.

    Example: 10

    Integer (int32)

    txtimeout
    optional

    Maximum time to spend on a transaction before timing out. Only applies to BEGIN TRANSACTION statements, or DML statements for which tximplicit is set. For other statements, it is ignored.

    Within a transaction, the request-level timeout parameter is ignored. The transaction timeout clock starts when the BEGIN WORK statement is successful. Once the transaction timeout is reached, no statement is allowed to continue in the transaction.

    The value for this parameter is a string. Its format includes an amount and a mandatory unit, e.g. 10ms (10 milliseconds) or 0.5s (half a second). Valid units are:

    • ns (nanoseconds)
    • us (microseconds)
    • ms (milliseconds)
    • s (seconds)
    • m (minutes)
    • h (hours)

    Specify a duration of 0 to disable. When disabled, the request-level timeout is set to the default.

    The default is "15s" for cbq files or scripts, "2m" for interactive cbq sessions or redirected input.

    Example: "30m"

    String (duration)

    use_cbo
    optional

    Specifies whether the cost-based optimizer is enabled.

    Example: true

    Boolean

    use_fts
    optional

    Specifies that the query should use a Search index.

    If the query contains a USING FTS hint, that takes priority over this parameter.

    If the query does not contain a USING FTS hint, and this parameter is set to true, all Search indexes are considered for the query. If a qualified Search index is available, it is selected for the query. If none of the available Search indexes are qualified, the available GSI indexes are considered instead.

    Refer to Flex Indexes for more information.

    Default: false
    Example: true

    Boolean

    use_replica
    optional

    Specifies whether a query can fetch data from a replica vBucket if active vBuckets are inaccessible. The possible values are:

    • off — read from replica is disabled for this request.

    • on — read from replica is enabled for this request, unless it has been disabled for all requests at node level.

    • unset — read from replica is specified by the node-level setting. If the node-level setting is also unset, read from replica is disabled for this request.

    Do not enable read from replica when you require consistent results. Only SELECT queries that are not within a transaction can read from replica.

    Reading from replica is only possible if the cluster uses Couchbase Server 7.6.0 or later.

    Note that KV range scans cannot currently be started on a replica vBucket. If a query uses sequential scan and a data node becomes unavailable, the query might return an error, even if read from replica is enabled for the request.

    Values: "off", "on", "unset"
    Default: "unset"
    Example: "on"

    String

    <$identifier>
    additional
    property

    Supplies the value for a named parameter in the statement. Applicable if the statement or prepared statement contains 1 or more named parameters.

    The name of this property consists of two parts:

    1. The $ character or the @ character.
    2. An identifier that specifies the name of the parameter. This starts with an optional underscore (_), followed by an alpha character, followed by one or more alphanumeric characters, and ends with an optional underscore (_).

    If the named parameter contains sensitive information, start and end the name of the parameter (after the initial $ or @) with an underscore (_). This masks the parameter value in the active requests catalog, the completed requests catalog, the response controls section, the cbq shell file history, and the query logs. When masked, a string parameter value is replaced by asterisks (*); other parameter values are replaced by null. Parameter masking is available in clusters running Couchbase Server 7.6.8 and later.

    The value of the named parameter can be any JSON value.

    Refer to Named Parameters and Positional Parameters for details.

    Nullable: yes
    Example: LAX

    Any Type

    Transactional Scan Consistency

    If the request contains a BEGIN TRANSACTION statement, or a DML statement with the tximplicit parameter set to true, then the scan_consistency parameter sets the transactional scan consistency. If you specify a transactional scan consistency of request_plus, statement_plus, or at_plus, or if you specify no transactional scan consistency, the transactional scan consistency is set to request_plus; otherwise, the transactional scan consistency is set as specified.

    Table 2. Transactional scan consistency
    Scan consistency at start of transaction Transactional scan consistency

    Not set

    request_plus

    not_bounded

    not_bounded

    request_plus
    statement_plus
    at_plus

    request_plus

    Any DML statements within the transaction that have no scan consistency set will inherit from the transactional scan consistency. Individual DML statements within the transaction may override the transactional scan consistency. If you specify a scan consistency of not_bounded for a statement within the transaction, the scan consistency for the statement is set as specified. When you specify a scan consistency of request_plus, statement_plus, or at_plus for a statement within the transaction, the scan consistency for the statement is set to request_plus.

    However, request_plus consistency is not supported for statements using a full-text index. If any statement within the transaction uses a full-text index, by means of the SEARCH function or the Flex Index feature, the scan consistency is set to not_bounded for the duration of the Full-Text Search.

    Table 3. Overriding the transactional scan consistency
    Scan consistency for statement within transaction Inherited scan consistency

    Not set

    Transactional scan consistency
    (not_bounded for Full-Text Search)

    not_bounded

    not_bounded

    request_plus
    statement_plus
    at_plus

    request_plus
    (not_bounded for Full-Text Search)

    Named Parameters and Positional Parameters

    You can add placeholder parameters to a statement, so that you can safely supply variable values when you run the statement. A placeholder parameter may be a named parameter or a positional parameter.

    • To add a named parameter to a query, enter a dollar sign $ or an at sign @ followed by the parameter name.

    • To add a positional parameter to a query, enter a dollar sign $ or an at sign @ followed by the number of the parameter, or enter a question mark ?.

    To run a query containing placeholder parameters, you must supply values for the parameters.

    • You can use an additional request-level parameter to supply the value for a named parameter. The name of this property is a dollar sign $ or an at sign @ followed by the parameter name.

    • The args request-level parameter enables you to supply a list of values for positional parameters.

    You can supply the values for placeholder parameters using any of the methods used to specify request-level parameters.

    Examples

    To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

    In the REST API examples:

    • $BASEURL is the base URL for the Data API.

    • $USER is the cluster access username.

    • $PASSWORD is the cluster access secret.

    Example 1. Named Parameters

    • Capella UI

    • Command Line

    • REST API

    The following query uses named parameter placeholders. The parameter values are supplied using the Query Options window.

    Named Parameters

    name

    $country

    value

    "France"

    name

    $altitude

    value

    500

    		 
    SELECT COUNT(*) FROM airport
WHERE country = $country AND geo.alt > @altitude;

    The named parameters and named parameter placeholders in this example use a mixture of @ and $ symbol prefixes. You can use either of these symbols as preferred.

    The following query uses named parameter placeholders. The parameter values are supplied using the cbq shell.

    \SET -@country "France";
\SET -$altitude 500;

SELECT COUNT(*) FROM airport
WHERE country = $country AND geo.alt > @altitude;

    The named parameters and named parameter placeholders in this example use a mixture of @ and $ symbol prefixes. You can use either of these symbols as preferred.

    The following query uses named parameter placeholders. The parameter values are supplied using the Data API.

    curl -v $BASEURL/_p/query/query/service \
     -d 'statement=SELECT COUNT(*) FROM airport
                   WHERE country = $country AND geo.alt > @altitude
       & query_context=travel-sample.inventory
       & $country="France" & $altitude=500' \
     -u $USER:$PASSWORD

    The named parameters and named parameter placeholders in this example use a mixture of @ and $ symbol prefixes. You can use either of these symbols as preferred.

    Example 2. Numbered Positional Parameters

    • Capella UI

    • Command Line

    • REST API

    The following query uses numbered positional parameter placeholders. The parameter values are supplied using the Query Options window.

    Positional Parameters

    $1

    "France"

    $2

    500

    		 
    SELECT COUNT(*) FROM airport
WHERE country = $1 AND geo.alt > @2;

    In this case, the first positional parameter value is used for the placeholder numbered $1, the second positional parameter value is used for the placeholder numbered @2, and so on.

    The numbered positional parameter placeholders in this example use a mixture of @ and $ symbol prefixes. You can use either of these symbols as preferred.

    The following query uses numbered positional parameter placeholders. The parameter values are supplied using the cbq shell.

    \SET -args ["France", 500];

SELECT COUNT(*) FROM airport
WHERE country = $1 AND geo.alt > @2;

    In this case, the first positional parameter value is used for the placeholder numbered $1, the second positional parameter value is used for the placeholder numbered @2, and so on.

    The numbered positional parameter placeholders in this example use a mixture of @ and $ symbol prefixes. You can use either of these symbols as preferred.

    The following query uses numbered positional parameter placeholders. The parameter values are supplied using the Data API.

    curl -v $BASEURL/_p/query/query/service \
     -d 'statement=SELECT COUNT(*) FROM airport
                   WHERE country = $1 AND geo.alt > @2
       & query_context=travel-sample.inventory
       & args=["France", 500]' \
     -u $USER:$PASSWORD

    In this case, the first positional parameter value is used for the placeholder numbered $1, the second positional parameter value is used for the placeholder numbered @2, and so on.

    The numbered positional parameter placeholders in this example use a mixture of @ and $ symbol prefixes. You can use either of these symbols as preferred.

    Example 3. Unnumbered Positional Parameters

    • Capella UI

    • Command Line

    • REST API

    The following query uses unnumbered positional parameter placeholders. The parameter values are supplied using the Query Options window.

    Positional Parameters

    $1

    "France"

    $2

    500

    		 
    SELECT COUNT(*) FROM airport
WHERE country = ? AND geo.alt > ?;

    In this case, the first positional parameter value is used for the first ? placeholder, the second positional parameter value is used for the second ? placeholder, and so on.

    The following query uses unnumbered positional parameter placeholders. The parameter values are supplied using the cbq shell.

    \SET -args ["France", 500];

SELECT COUNT(*) FROM airport
WHERE country = ? AND geo.alt > ?;

    In this case, the first positional parameter value is used for the first ? placeholder, the second positional parameter value is used for the second ? placeholder, and so on.

    The following query uses unnumbered positional parameter placeholders. The parameter values are supplied using the Data API.

    curl -v $BASEURL/_p/query/query/service \
     -d 'statement=SELECT COUNT(*) FROM airport
                   WHERE country = ? AND geo.alt > ?
       & query_context=travel-sample.inventory
       & args=["France", 500]' \
     -u $USER:$PASSWORD

    In this case, the first positional parameter value is used for the first ? placeholder, the second positional parameter value is used for the second ? placeholder, and so on.

    For more information and examples, including examples using SDKs, see the Prepare Statements for Reuse guide.