Using Query API to fetch data
You can use the QueryRequest
to construct queries to filter data from
your NoSQL table.
To execute your query, you use the NoSQLHandle.query()
API.
See Oracle NoSQL
Java SDK API Reference for more details on the various classes and methods.
- Iterator: Use
NoSQLHandle.queryIterable(QueryRequest)
to get an iterable that contains all the results. - Partial Results: To compute and retrieve the full result set of a query, the
same
QueryRequest
instance will, in general, have to be executed multiple times (viaNoSQLHandle.query(oracle.nosql.driver.ops.QueryRequest)
. Each execution returns aQueryRequest
, which contains a subset of the result set.
String sqlstmt_allrows="SELECT * FROM stream_acct";
private static void fetchRows(NoSQLHandle handle,String sqlstmt)
throws Exception {
try (
QueryRequest queryRequest =
new QueryRequest().setStatement(sqlstmt_allrows);
QueryIterableResult results =
handle.queryIterable(queryRequest)){
for (MapValue res : results) {
System.out.println("\t" + res);
}
}
}
String sqlstmt_allrows=
"SELECT account_expiry, acct.acct_data.lastName,
acct.acct_data.contentStreamed[].showName
FROM stream_acct acct WHERE acct_id=1";
Note:
To fetch data from a child table, specify the full name of the table (parent_tablename.child_tablename) in the sql statement. Download the full code TableJoins.java from the examples to understand how to fetch data from a parent-child table here.To execute a query use the borneo.NoSQLHandle.query()
method.
- Use
borneo.NoSQLHandle.query_iterable()
to get an iterable that contains all the results of a query. - You can loop through partial results by using the
borneo.NoSQLHandle.query()
method. For example, to execute a SELECT query to read data from your table, aborneo.QueryResult
contains a list of results. And if theborneo.QueryRequest.is_done()
returns False, there may be more results, so queries should generally be run in a loop. It is possible for single request to return no results but the query still not done, indicating that the query loop should continue.
sqlstmt = 'SELECT * FROM stream_acct'
def fetch_data(handle,sqlstmt):
request = QueryRequest().set_statement(sqlstmt)
print('Query results for: ' + sqlstmt)
result = handle.query(request)
for r in result.get_results():
print('\t' + str(r))
sqlstmt = 'SELECT account_expiry, acct.acct_data.lastName,
acct.acct_data.contentStreamed[].showName
FROM stream_acct acct WHERE acct_id=1'
Note:
To fetch data from a child table, specify the full name of the table (parent_tablename.child_tablename) in the sql statement. Download the full code TableJoins.py from the examples to understand how to fetch data from a parent-child table here.To execute a query use the Client.Query
function. When
execute on the cloud service, the amount of data read by a single query request is limited
by a system default and can be further limited using
QueryRequest.MaxReadKB
. This limits the amount of data read and not the
amount of data returned, which means that a query can return zero results but still have
more data to read. For this reason queries should always operate in a loop, acquiring more
results, until QueryRequest.IsDone()
returns true, indicating that the
query is done.
querystmt := "select * FROM stream_acct"
func fetchData(client *nosqldb.Client, err error,
tableName string, querystmt string)(){
prepReq := &nosqldb.PrepareRequest{ Statement: querystmt,}
prepRes, err := client.Prepare(prepReq)
if err != nil {
fmt.Printf("Prepare failed: %v\n", err)
return
}
queryReq := &nosqldb.QueryRequest{
PreparedStatement: &prepRes.PreparedStatement,}
var results []*types.MapValue
for {
queryRes, err := client.Query(queryReq)
if err != nil {
fmt.Printf("Query failed: %v\n", err)
return
}
res, err := queryRes.GetResults()
if err != nil {
fmt.Printf("GetResults() failed: %v\n", err)
return
}
results = append(results, res...)
if queryReq.IsDone() {
break
}
}
for i, r := range results {
fmt.Printf("\t%d: %s\n", i+1,
jsonutil.AsJSON(r.Map()))
}
}
querystmt := "SELECT account_expiry, acct.acct_data.lastName,
acct.acct_data.contentStreamed[].showName
FROM stream_acct acct where acct_id=1"
Note:
To fetch data from a child table, specify the full name of the table (parent_tablename.child_tablename) in the sql statement. Download the full code TableJoins.go from the examples to understand how to fetch data from a parent-child table here.- Use the
query
method to execute a query. This method returns a Promise ofQueryResult
,which is a plain JavaScript object containing an array of resulting rows as well as a continuation key. You can use thequery
method in two ways:- You can call the
query
method only once for queries that access at most one row. These queries can only include select statements based on the primary key (the where clause must specify equality based on the complete primary key). In all other cases, you can use eitherquery
in a loop orqueryIterable
method. - You can call the
query
method in a loop to retrieve multiple rows. As the amount of data returned by a query is limited by the system default and can be further limited by setting themaxReadKB
property in theQueryOpt
argument of thequery
one invocation of thequery
method can't return all the available results. To address this issue, run the query in a loop until thecontinuationKey
inQueryResult
becomes null/undefined.
- You can call the
- Iterate over the query results using the
queryIterable
method. This method returns an iterable object that you can iterate over with a for-await-of loop. You need not manage the continuation in this method.Note:
With thequeryIterable
method, you can also use theQueryOpt
argument with properties other thancontinuationKey
.
const querystmt = 'SELECT * FROM stream_acct';
async function fetchData(handle,querystmt) {
const opt = {};
try {
do {
const result = await handle.query(querystmt, opt);
for(let row of result.rows) {
console.log(' %O', row);
}
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
console.error(' Error: ' + error.message);
}
}
const querystmt =
'SELECT account_expiry, acct.acct_data.lastName,
acct.acct_data.contentStreamed[].showName
FROM stream_acct acct WHERE acct_id=1';
query
method to provide type hints for the
rows returned in the QueryResult
. This need not be the same as table row
schema (unless using SELECT * query) as the query can include projections, name
aliases, aggregate values, and so forth. Download the full code QueryData.ts
from the examples here.interface StreamInt {
acct_Id: Integer;
profile_name: String;
account_expiry: TIMESTAMP;
acct_data: JSON;
}
/* fetches data from the table */
async function fetchData(handle: NoSQLClient,querystmt: string) {
const opt = {};
try {
do {
const result = await handle.query<StreamInt>(querystmt, opt);
for(let row of result.rows) {
console.log(' %O', row);
}
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
console.error(' Error: ' + error.message);
}
}
const querystmt =
'SELECT account_expiry, acct.acct_data.lastName,
acct.acct_data.contentStreamed[].showName
FROM stream_acct acct WHERE acct_id=1';
To execute a query, you may call QueryAsync
method or call
GetQueryAsyncEnumerable
method and iterate over the resulting async
enumerable. You may pass options to each of these methods as
QueryOptions
. QueryAsync
method return
Task<QueryResult<RecordValue>>
.
QueryResult
contains query results as a list of RecordValue instances,
as well as other information. When your query specifies a complete primary key, it is
sufficient to call QueryAsync
once. The amount of data returned by the
query is limited by the system. It could also be further limited by setting
MaxReadKB
property of QueryOptions
. This means that
one invocation of QueryAsync
may not return all available results. This
situation is dealt with by using continuation key. Non-null continuation key in
QueryResult
means that more query results may be available. This means
that queries should run in a loop, looping until the continuation key becomes null. See
Oracle
NoSQL Dotnet SDK API Reference for more details of all classes and methods.
private const string querystmt ="SELECT * FROM stream_acct";
private static async Task fetchData(NoSQLClient client,String querystmt){
var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
await DoQuery(queryEnumerable);
}
//function to display result
private static async Task
DoQuery(IAsyncEnumerable<QueryResult<RecordValue>> queryEnumerable){
Console.WriteLine(" Query results:");
await foreach (var result in queryEnumerable) {
foreach (var row in result.Rows)
{
Console.WriteLine();
Console.WriteLine(row.ToJsonString());
}
}
}
private const string querystmt =
"SELECT account_expiry, acct.acct_data.lastName,
acct.acct_data.contentStreamed[].showName
FROM stream_acct acct WHERE acct_id=1";
Note:
To fetch data from a child table, specify the full name of the table (parent_tablename.child_tablename) in the sql statement. Download the full code TableJoins.cs from the examples to understand how to fetch data from a parent-child table here.