3 Using SODA for C
How to access SODA for C is described, as well as how to use it to perform create, read (retrieve), update, and delete (CRUD) operations on collections. CRUD operations are also called “read and write operations” in this document.
- Getting Started with SODA for C
How to access SODA for C is described, as well as how to use it to create a database collection, insert a document into a collection, and retrieve a document from a collection. - Creating a Document Collection with SODA for C
Use OCI functionOCISodaCollCreate()to create a collection, if you do not care about the details of its configuration. This creates a collection that has the default metadata. To create a collection that is configured in a nondefault way, use functionOCISodaCollCreateWithMetadata()instead, passing it custom metadata, expressed in JSON. - Opening an Existing Document Collection with SODA for C
Use OCI functionOCISodaCollOpen()to open an existing document collection. - Checking Whether a Given Collection Exists with SODA for C
To check for the existence of a collection with a given name, use OCI functionOCISodaCollOpen(). The function returnsOCI_SUCCESSif the collection was successfully opened, which means that it exists. If no such collection exists then the collection-handle pointer isNULL. - Discovering Existing Collections with SODA for C
To discover existing collections, use OCI functionsOCISodaCollList()andOCISodaCollGetNext(). - Dropping a Document Collection with SODA for C
To drop a document collection, use OCI functionOCISodaCollDrop(). - Creating Documents with SODA for C
Various ways to create a SODA document are described, along with the components of a document. - Inserting Documents into Collections with SODA for C
Various ways to insert a document into a SODA collection are described. - Saving Documents Into a Collection with SODA for C
You can use OCI functionsOCISodaSave(),OCISodaSaveWithCtnt(),OCISodaSaveAndGet(),OCISodaSaveAndGetWithCtnt(), andOCISodaSaveAndGetWithOpts()to save documents into a collection, which means inserting them if they are new or updating them if they already belong to the collection. (Such an operation is sometimes called "upserting".) - SODA for C Read and Write Operations
For all read operations, and for all write operations other than insertions and saves into a collection, you: (1) allocate an operation-options handle, (2) set some of its attributes to specify a particular operation, and (3) pass the handle to a generic function that performs the operation. - Finding Documents in Collections with SODA for C
To find documents in a collection use functionOCISodaFind(), passing it an operation-options handle that specifies the particular find operation. To find the unique document that has a given key you can alternatively use OCI convenience functionOCISodaFindOneWithKey(), which does not require an operation-options handle. - Setting the Prefetch Size for SODA Find Operations
A call to functionOCISodaFind()prefetches multiple documents in order to reduce the number of required client–database round trips byOCISodaDocGetNext(). You can change the number of documents used for a prefetch batch by setting attributeOCI_ATTR_SODA_FETCH_ARRAY_SIZEon the operation handle. - Replacing Documents in a Collection with SODA for C
You can use functionOCISodaReplOneAndGet()to replace a document in a collection, passing it an operation-options handle that specifies the key of the document to replace as well as the new, replacement document. It returns that replacement document, but with all of its metadata filled in, as the result document. - Removing Documents from a Collection with SODA for C
You can remove documents from a collection using functionsOCISodaRemove()andOCISodaRemoveOneWithKey(). - Truncating a Collection (Removing All Documents) with SODA for C
You can empty, or truncate, a collection, which means remove all of its documents, using functionOCISodaCollTruncate(). - Indexing the Documents in a Collection with SODA for C
Indexing can improve the performance of QBEs. To index the documents in a SODA collection, use functionOCISodaIndexCreate(), passing it a textual JSON index specification. This can specify support for B-tree, spatial, full-text, and ad hoc indexing, and it can specify support for a JSON data guide. - Getting a Data Guide for a Collection with SODA for C
You use functionOCISodaDataGuideGet()orOCISodaDataGuideGetWithOpts()to obtain a data guide for a collection. A data guide is a JSON document that summarizes the structural and type information of the JSON documents in the collection. It records metadata about the fields used in those documents. - Handling Transactions with SODA for C
You can handle individual read and write operations, or groups of them, as a database transaction.
3.1 Getting Started with SODA for C
How to access SODA for C is described, as well as how to use it to create a database collection, insert a document into a collection, and retrieve a document from a collection.
Note:
Don’t worry if not everything in this topic is clear to you on first reading. The necessary concepts are developed in detail in other topics. This topic should give you an idea of what is involved overall in using SODA.
To get started with SODA for C, follow these steps:
-
Ensure that all of the prerequisites have been met for using SODA for C. See SODA for C Prerequisites.
-
Grant database role
SODA_APPto the database schema (user account) where you intend to store SODA collections. (Replace placeholderuserhere by a real account name.)GRANT SODA_APP TO user; -
Create a program file containing the C code in Example 3-1, but set variables
usr,passwd, andconnstrto values appropriate string values for your database account and instance. -
Compile the file and build an executable program from it as you would for any OCI program.
-
Run the program.
You can run it just by entering the program name on the command line. For example, if the name is
soda-get-startedthen enter that at the command-line prompt:> soda-get-startedIf you want the program to drop the collection when done with it then pass the argument
dropto it on the command line:> soda-get-started drop
Caution:
Do not use SQL to drop the database table that underlies a collection. Dropping a collection involves more than just dropping its database table. In addition to the documents that are stored in its table, a collection has metadata, which is also persisted in Oracle Database. Dropping the table underlying a collection does not also drop the collection metadata.
Note:
-
All C code you have that uses SODA for C features must first initialize the environment in OCI object mode, passing
OCI_OBJECTas the mode parameter to functionOCIEnvNlsCreate()here. -
All SODA handles (document, collection, and any others) need to be explicitly freed using function
OCIHandleFree()when your program no longer needs them. (In particular, a handle for a document with large content can be associated with a lot of memory.)
See Also:
-
Oracle Call Interface Programmer's Guide for information about building an OCI application
-
Oracle Call Interface Programmer's Guide for basic information about OCI programming
Example 3-1 Getting Started Run-Through
This example code does the following:
-
Creates an Oracle Call Interface (OCI) environment in object mode, allocates the error handle, and gets a session using function
OCISessionGet(). -
Creates and opens a SODA document collection, using the default collection configuration (metadata).
-
Creates a SODA document with some JSON content.
-
Inserts the document into the collection.
-
Gets the inserted document back. Its other components, besides the content, are generated automatically.
-
Prints the unique document key, which is one of the components generated automatically.
-
Finds the document in the collection, providing its key.
-
Prints some of the document components: key, version, last-modified time stamp, creation time stamp, media type, and content.
-
Optionally drops the collection, cleaning up the database table that is used to store the collection and its metadata.
-
Frees all allocated handles.
Whether or not the collection is dropped is decided at runtime. To drop
the collection you provide the command-line argument drop to the executable
program.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>
static sword status;
int main(int argc, char *argv[])
{
sword rc = OCI_SUCCESS;
OCIEnv *envhp = NULL;
OCIError *errhp = NULL;
OCISvcCtx *svchp = NULL;
OCIAuthInfo *authhp = NULL;
OCISodaColl *collhp = NULL;
OCISodaDoc *dochp = NULL;
boolean isDropped = FALSE;
ub4 docFlags = OCI_DEFAULT;
OraText *collectionName = (oratext *)"MyJSONCollection";
OCISodaDoc *foundDochp = NULL;
OCISodaDoc *origDochp = NULL;
// Document content: JSON data
char documentContent[30] = "{\"name\":\"Alexander\"}";
// Set variables usr and passwd to strings for user name and password.
// (Be sure to replace placeholders user and password used here.)
OraText usr[30] = user;
OraText passwd[30] = password;
// Set variable connstr to a string composed of the host name,
// port number, and service name of your database instance.
// (Be sure to replace placeholders host, port, and service used here.)
OraText connstr[50] = "host:port/service";
OraText *key = NULL;
ub4 keyLen = 0;
OraText *content = NULL;
ub4 contentLen = 0;
OraText *version = NULL;
ub4 versionLen = 0;
OraText *lastModified = NULL;
ub4 lastModifiedLen = 0;
OraText *mediaType = NULL;
ub4 mediaTypeLen = 0;
OraText *createdOn = NULL;
ub4 createdOnLen = 0;
// Set up environment. OCI_OBJECT is required for all SODA C code.
rc = OCIEnvNlsCreate(&envhp,
OCI_OBJECT,
NULL,
NULL,
NULL,
NULL,
0,
NULL,
0,
0);
if (rc != OCI_SUCCESS)
{
printf ("OCIEnvNlsCreate failed\n");
goto finally;
}
// Allocate error handle.
rc = OCIHandleAlloc((dvoid *) envhp,
(dvoid **) &errhp,
OCI_HTYPE_ERROR,
(size_t) 0,
(dvoid **) 0);
if (rc != OCI_SUCCESS)
{
printf ("OCIHandleAlloc: OCI_HTYPE_ERROR creation failed\n");
goto finally;
}
// Allocate authentication-information handle.
rc = OCIHandleAlloc ((dvoid *)envhp,
(dvoid **)&authhp,
(ub4)OCI_HTYPE_AUTHINFO,
(size_t)0,
(dvoid **)0);
if (rc != OCI_SUCCESS)
{
printf ("OCIHandleAlloc: OCI_HTYPE_AUTHINFO creation failed\n");
goto finally;
}
// Set variable usr to the user name.
rc = OCIAttrSet ((dvoid *)authhp,
(ub4)OCI_HTYPE_AUTHINFO,
(dvoid *)usr,
(ub4)strlen((char *)usr),
(ub4)OCI_ATTR_USERNAME,
(OCIError *)errhp);
if (rc != OCI_SUCCESS)
{
printf ("OCIAttrSet: OCI_ATTR_USERNAME failed\n");
goto finally;
}
// Set variable passwd to the password.
rc = OCIAttrSet ((dvoid *)authhp,
(ub4)OCI_HTYPE_AUTHINFO,
(dvoid *)passwd,
(ub4)strlen((char *)passwd),
(ub4)OCI_ATTR_PASSWORD,
(OCIError *)errhp);
if (rc != OCI_SUCCESS)
{
printf ("OCIAttrSet: OCI_ATTR_PASSWORD failed\n");
goto finally;
}
// Get service handle.
// This provides service and error handles we can use for service calls.
rc = OCISessionGet ((OCIEnv *)envhp,
(OCIError *)errhp,
(OCISvcCtx **)&svchp,
(OCIAuthInfo *)authhp,
(OraText *)connstr,
(ub4)strlen((char *)connstr),
(OraText *)NULL,
(ub4)0,
(OraText **)0,
(ub4 *)0,
(boolean *)0,
(ub4)OCI_DEFAULT);
if (rc != OCI_SUCCESS)
{
printf("OCISessionGet failed\n");
goto finally;
}
// Create collection named by variable collectionName, using
// default metadata.
rc = OCISodaCollCreate(svchp,
collectionName,
(ub4) strlen(collectionName),
&collhp,
errhp,
OCI_DEFAULT);
if (rc != OCI_SUCCESS)
{
printf("OCISodaCollCreate failed\n");
goto finally;
}
// Create document with content provided by variable documentContent.
rc = OCISodaDocCreate(envhp,
documentContent,
(ub4) strlen(documentContent),
docFlags,
&dochp,
errhp,
OCI_DEFAULT);
if (rc != OCI_SUCCESS)
{
printf("OCISodaDocCreate failed\n");
goto finally;
}
// Because OCISodaInsertAndGet returns the result document as dochp,
// we first save the pointer to the original input document handle,
// which was returned by OCISodaDocCreate, as origDochp.
// This lets us free the original document handle later.
origDochp = dochp;
// Insert the document into the collection.
rc = OCISodaInsertAndGet(svchp,
collhp,
&dochp,
errhp,
OCI_SODA_ATOMIC_COMMIT);
if (rc != OCI_SUCCESS)
{
printf("OCISodaInsertAndGet failed\n");
goto finally;
}
// Get the autogenerated key of the inserted document.
rc = OCIAttrGet((dvoid *) dochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &key,
&keyLen,
OCI_ATTR_SODA_KEY,
errhp);
if (rc != OCI_SUCCESS)
{
printf("OCIAttrGet for OCI_ATTR_SODA_KEY failed\n");
goto finally;
}
// Find the document using its key.
printf("Find the document by its auto-generated key %.*s\n",
keyLen,
key);
rc = OCISodaFindOneWithKey(svchp,
collhp,
key,
keyLen,
OCI_DEFAULT,
&foundDochp,
errhp,
OCI_DEFAULT);
if (rc != OCI_SUCCESS)
{
printf("OCISodaFindOneWithKey failed\n");
goto finally;
}
// Get and print components of found document.
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &key,
&keyLen,
OCI_ATTR_SODA_KEY,
errhp);
if (rc != OCI_SUCCESS)
{
printf("OCIAttrGet for OCI_ATTR_SODA_KEY failed\n");
goto finally;
}
printf("Key: %.*s\n", keyLen, key);
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &version,
&versionLen,
OCI_ATTR_SODA_VERSION,
errhp);
if (rc != OCI_SUCCESS)
{
printf("OCIAttrGet for OCI_ATTR_SODA_VERSION failed\n");
goto finally;
}
printf("Version: %.*s\n", versionLen, version);
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &lastModified,
&lastModifiedLen,
OCI_ATTR_SODA_LASTMOD_TIMESTAMP,
errhp);
if (rc != OCI_SUCCESS)
{
printf("OCIAttrGet for OCI_ATTR_SODA_LASTMOD_TIMESTAMP failed\n");
goto finally;
}
printf("Last-modified: %.*s\n", lastModifiedLen, lastModified);
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &createdOn,
&createdOnLen,
OCI_ATTR_SODA_CREATE_TIMESTAMP,
errhp);
if (rc != OCI_SUCCESS)
{
printf("OCIAttrGet for OCI_ATTR_SODA_CREATE_TIMESTAMP failed\n");
goto finally;
}
printf("Created: %.*s\n", createdOnLen, createdOn);
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &mediaType,
&mediaTypeLen,
OCI_ATTR_SODA_MEDIA_TYPE,
errhp);
if (rc != OCI_SUCCESS)
{
printf("OCIAttrGet for OCI_ATTR_SODA_MEDIA_TYPE failed\n");
goto finally;
}
printf("Media Type: %.*s\n", mediaTypeLen, mediaType);
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &content,
&contentLen,
OCI_ATTR_SODA_CONTENT,
errhp);
if (rc != OCI_SUCCESS)
{
printf("OCIAttrGet for OCI_ATTR_SODA_CONTENT failed\n");
goto finally;
}
printf("Content: %.*s \n", contentLen, content);
// Drop the collection if argument "drop" was provided.
if ((argc > 1) && (strcmp(argv[1], "drop") == 0))
{
rc = OCISodaCollDrop(svchp,
collhp,
&isDropped,
errhp,
OCI_DEFAULT);
if (rc != OCI_SUCCESS)
{
printf("OCISodaCollDrop failed\n");
goto finally;
}
else
{
printf("Collection dropped\n");
}
}
finally:
// Release the session and free all handles.
if (collhp)
(void ) OCIHandleFree((dvoid *) collhp, OCI_HTYPE_SODA_COLLECTION);
if (dochp)
(void ) OCIHandleFree((dvoid *) dochp, OCI_HTYPE_SODA_DOCUMENT);
if (origDochp)
(void ) OCIHandleFree((dvoid *) origDochp, OCI_HTYPE_SODA_DOCUMENT);
if (foundDochp)
(void ) OCIHandleFree((dvoid *) foundDochp, OCI_HTYPE_SODA_DOCUMENT);
(void ) OCISessionRelease(svchp, errhp, (oratext *)0, 0, OCI_DEFAULT);
if (authhp)
(void ) OCIHandleFree ((dvoid *)authhp, (ub4)OCI_HTYPE_AUTHINFO);
if (errhp)
(void ) OCIHandleFree((dvoid *) errhp, OCI_HTYPE_ERROR);
if (svchp)
(void ) OCIHandleFree((dvoid *) svchp, OCI_HTYPE_SVCCTX);
if (envhp)
(void ) OCIHandleFree((dvoid *) envhp, OCI_HTYPE_ENV);
return rc;
}
Related Topics
Parent topic: Using SODA for C
3.2 Creating a Document Collection with SODA for C
Use OCI function OCISodaCollCreate()
to create a collection, if you do not care about the
details of its configuration. This creates a
collection that has the default metadata. To create a
collection that is configured in a nondefault way, use
function
OCISodaCollCreateWithMetadata()
instead, passing it custom metadata, expressed in
JSON.
For each of
these functions, if a collection with the same
name already exists then it is simply opened and
its handle is returned. For function
OCISodaCollCreateWithMetadata(),
if the metadata passed to it does not match that
of the existing collection then the collection is
not opened and an error is raised. (To match, all
metadata fields must have the same
values.)
Example 3-2 uses function
OCISodaCollCreate() to create a
collection that has the default configuration
(default metadata). It returns the collection as
an OCISodaColl handle.
A collection that has the default collection metadata has the following characteristics:
-
It can store only JSON documents.
-
Each of its documents has these components: key, content, creation time stamp, last-modified time stamp.
-
Keys are automatically generated for documents that you add to the collection.
The default collection configuration is recommended in most cases, but collections are highly configurable. When you create a collection you can specify things such as the following:
-
Whether the collection can store only JSON documents.
-
The presence or absence of columns for document creation time stamp, last-modified time stamp, and version.
-
Methods of document key generation, and whether keys are client-assigned or generated automatically.
-
Methods of version generation.
-
Storage details, such as the name of the table that stores the collection and the names and data types of its columns.
This configurability also lets you map a new collection to an existing database table.
Note:
Unless otherwise stated, the remainder of this documentation assumes that a collection has the default configuration.
See Also:
-
Oracle Database Introduction to Simple Oracle Document Access (SODA) for information about the default naming of a collection table
-
Oracle Database Introduction to Simple Oracle Document Access (SODA) for reference information about collection metadata components
-
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaCollCreate() -
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaCollCreateWithMetadata()
Example 3-2 Creating a Collection That Has the Default Metadata
This example
creates collection MyCollection
with the default metadata. Note that function
OCISodaCollCreate() does not,
itself, perform a database commit
operation.
OCISodaColl *collhp = NULL;
OraText *collectionName = (OraText *)"MyCollection";
rc = OCISodaCollCreate(svchp,
(const OraText *)collectionName,
(ub4)strlen(collectionName),
&collhp,
errhp,
OCI_DEFAULT);3.3 Opening an Existing Document Collection with SODA for C
Use OCI function OCISodaCollOpen() to open an existing document collection.
See Also:
Oracle Call Interface Programmer's Guide for information about OCI function OCISodaCollOpen()
Example 3-3 Opening an Existing Document Collection
This example uses OCI function OCISodaCollOpen() to open the collection named MyCollection. It returns an OCISodaColl handle that represents this collection as the value of the fourth parameter (collhp in this example). The function return value is OCI_SUCCESS for success or OCI_ERROR for failure. If the value returned is OCI_ERROR then there is no existing collection named MyCollection.
OCISodaColl *collhp = NULL;
OraText *collectionName = "MyCollection";
rc = OCISodaCollOpen(svchp,
collectionName,
(ub4) strlen(collectionName),
&collhp,
errhp,
OCI_DEFAULT);
if (!collhp) printf("Collection %s does not exist\n", collectionName);Parent topic: Using SODA for C
3.4 Checking Whether a Given Collection Exists with SODA for C
To check for the existence of a collection with a given name, use OCI function OCISodaCollOpen(). The function returns OCI_SUCCESS if the collection was successfully opened, which means that it exists. If no such collection exists then the collection-handle pointer is NULL.
Example 3-3 illustrates this. If MyCollection names an existing collection then that collection is opened, and collection-handle collhp points to it. If MyCollection does not name an existing collection then after invoking function OCISodaCollOpen() the value of collection-handle collhp is still NULL.
Related Topics
Parent topic: Using SODA for C
3.5 Discovering Existing Collections with SODA for C
To discover existing collections, use OCI functions
OCISodaCollList() and OCISodaCollGetNext().
See Also:
-
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaCollList() -
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaGetNext()
Example 3-4 Printing the Names of All Existing Collections
This example uses OCI function OCISodaCollList() to obtain a
collection cursor (collectionCursor). It then iterates over the cursor,
printing out each collection
name.
OCISodaCollCursor *collectionCursor;
OCISodaColl *collhp;
OraText *startName = NULL;
ub4 startNameLen = 0;
OraText *collectionName = NULL;
ub4 collectionNameLen = 0;
sword rc;
rc = OCISodaCollList(svchp,
startName,
startNameLen,
&collectionCursor,
errhp,
OCI_DEFAULT);
if (rc != OCI_SUCCESS) goto finally;
do
{
rc = OCISodaCollGetNext(svchp,
collectionCursor,
&collhp,
errhp,
OCI_DEFAULT);
if (rc == OCI_NO_DATA || rc == OCI_INVALID_HANDLE || rc == OCI_ERROR) goto finally;
rc = OCIAttrGet((dvoid *) collhp,
OCI_HTYPE_SODA_COLLECTION,
(dvoid *) &collectionName,
&collectionNameLen,
OCI_ATTR_SODA_COLL_NAME,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("%s\n", collectionName);
if (collhp) OCIHandleFree((dvoid *) collhp, (ub4) OCI_HTYPE_SODA_COLLECTION);
}
while(1);
finally:
if (collectionCursor) OCIHandleFree((dvoid *) collectionCursor, (ub4)OCI_HTYPE_SODA_COLL_CURSOR);
In this example, startName is NULL, and
startNameLen is 0. As a result, the cursor iterates over
all collections in the database.
Alternatively, you could
iterate over only a subset of the existing collections. For that, you could set
startName to an existing collection name, such as
"myCollectionB", and set startNameLen to its string
length. The cursor would then iterate over only that collection and the collections whose
names come after that collection name alphabetically. The collections would be iterated over
in alphabetic order of their names.
For example, if the existing
collections are "myCollectionA", "myCollectionB", and
"myCollectionC", and if startName is
"myCollectionB", then the cursor iterates over
"myCollectionB" and "myCollectionC", in that
order.
Parent topic: Using SODA for C
3.6 Dropping a Document Collection with SODA for C
To drop a document collection, use OCI function
OCISodaCollDrop().
Unlike Oracle SQL statement DROP TABLE, function
OCISodaCollDrop() does not implicitly perform a commit
operation before and after it drops the collection. To complete the collection
removal you must explicitly commit all uncommitted writes to the collection
before invoking
OCISodaCollDrop().
Dropping a collection using a collection handle does not free
the handle. You must use OCI function OCIHandleFree() to free a
handle.
Caution:
Do not use SQL to drop the database table that underlies a collection. Dropping a collection involves more than just dropping its database table. In addition to the documents that are stored in its table, a collection has metadata, which is also persisted in Oracle Database. Dropping the table underlying a collection does not also drop the collection metadata.
Note:
Day-to-day use of a typical application that makes use of SODA does not require that you drop and re-create collections. But if you need to do that for any reason then this guideline applies.
Do not drop a collection and then re-create it with different metadata if there is any application running that uses the collection in any way. Shut down any such applications before re-creating the collection, so that all live SODA handles are released.
There is no problem just dropping a collection. Any read or write operation on a dropped collection raises an error. And there is no problem dropping a collection and then re-creating it with the same metadata. But if you re-create a collection with different metadata, and if there are any live applications using SODA handles, then there is a risk that a stale collection is accessed, and no error is raised in this case.
See Also:
Oracle Call Interface Programmer's Guide for information about OCI
function OCISodaCollDrop()
Example 3-5 Dropping a Document Collection
This example uses OCI function OCISodaCollDrop() to
drop a collection. (Variable collhp is assumed to point to an
existing collection — an OCISodaColl instance).
If the collection cannot be dropped because of uncommitted write operations then an
error is returned. If the collection is dropped successfully, the value of out
parameter dropStatus is TRUE; otherwise it is
FALSE.
If the collection-handle argument
(collhp in this example) no longer references an existing
collection then no error is returned, but dropStatus is
FALSE after the invocation of
OCISodaCollDrop().
boolean dropStatus = FALSE;
rc = OCISodaCollDrop(svchp, collhp, &dropStatus, errhp, OCI_DEFAULT);
3.7 Creating Documents with SODA for C
Various ways to create a SODA document are described, along with the components of a document.
SODA for C represents a document using a
OCISodaDoc handle. This is a carrier of document content
and other document components, such as the document key. Document components are handle
attributes.
Here is an example of the content of a JSON document:
{ "name" : "Alexander",
"address" : "1234 Main Street",
"city" : "Anytown",
"state" : "CA",
"zip" : "12345"
}A document has these components:
-
Key
-
Content
-
Creation time stamp
-
Last-modified time stamp
-
Version
-
Media type (
"application/json"for JSON documents)
You can create a document in these ways:
-
By invoking a OCI function that is specifically designed to create a document:
OCISodaDocCreate(),OCISodaDocCreateWithKey(), orOCISodaDocCreateWithKeyAndMType().Example 3-6 and Example 3-7 illustrate this. They both create a document handle. In each case the media type for the created document defaults to
"application/json", and the other document components default toNULL. -
By invoking function
OCIHandleAlloc()with handle typeOCI_HTYPE_SODA_DOCUMENT, to create an empty document (handle).Example 3-8 illustrates this.
You can use function OCIAttrSet() to define (set)
document components (document-handle attributes), whether or not they already have
values.
If you use the second approach (OCIHandleAlloc()) to create a document
then you must invoke function OCIAttrSet() to set the content
component. If you intend the document to be written to a collection with client-assigned
keys then you must also invoke it to set the key. If you intend the document to have
non-JSON content then you must also invoke it to set the media
type.
However you create a document, you can reuse the handle for multiple document operations. For example, you can change the content or other components, passing the same handle to different write operations.
In a collection, each document must
have a key. You must provide the key when you create the document only if you expect
to insert the document into a collection that does not automatically generate keys
for inserted documents. By default, collections are configured to automatically generate
document keys. Use function OCISodaDocCreate() if the key is to be
automatically generated; otherwise, supply the key (as parameter key) to
OCISodaDocCreateWithKey(), or
OCISodaDocCreateWithKeyAndMType().
Use function
OCISodaDocCreateWithKeyAndMType() if you want to provide the document
media type (otherwise, it defaults to "application/json"). This can be
useful for creating non-JSON documents (using a media type other than
"application/json").
Whichever document-creation
function you use, invoking it sets the document components that you provide (the content,
possibly the key, and possibly the media type) to the values you provide for them. And it
sets the values of the creation time stamp, last-modified time stamp, and version to
null.
You get document components using OCI
function OCIAttrGet(), which is the same way you get the value of any
handle attribute. You pass the type of the component you want to get to
OCIAttrGet() as the fifth argument.
Table 3-1 Document Handle Attributes (Document Components)
| Attribute | Description |
|---|---|
|
|
The unique key for the document. |
|
|
The creation time stamp for the document. |
|
|
The last-modified time stamp for the document. |
|
|
The media type for the document. |
|
|
The document version. |
|
|
The document content. |
Immediately after you create a document, OCIAttrGet()
returns these values for components:
-
Values explicitly provided to the document-creation function
-
"application/json", forOCI_ATTR_SODA_MEDIA_TYPE, if the media type was not provided to the creation function -
NULLfor other components
See Also:
-
Oracle Database Introduction to Simple Oracle Document Access (SODA) for an overview of SODA documents
-
Oracle Database Introduction to Simple Oracle Document Access (SODA) for restrictions that apply for SODA documents
-
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaDocCreate() -
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaDocCreateWithKey() -
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaDocCreateWithKeyAndMType() -
Oracle Call Interface Programmer's Guide for information about OCI function
OCIHandleAlloc() -
Oracle Call Interface Programmer's Guide for information about OCI function
OCIAttrSet()
Example 3-6 Creating a Document with JSON Content
This example uses OCISodaDocCreate() to create a document handle and fill the document with content. It then frees the document handle.Foot 1
OCISodaDoc *dochp = NULL;
OraText *documentContent = "{\"name\":\"Alexander\"}";
ub4 docFlags = OCI_DEFAULT;
rc = OCISodaDocCreate(envhp,
documentContent,
(ub4) strlen(documentContent),
docFlags,
&dochp,
errhp,
OCI_DEFAULT)
// Make further use of handle dochp...
if (dochp) OCIHandleFree((dvoid *) dochp, (ub4) OCI_HTYPE_SODA_DOCUMENT);
Example 3-7 Creating a Document with Document Key and JSON Content
This example is similar to Example 3-6, but it uses OCISodaDocCreateWithKey(),
providing the document key (myKey) as well as the document content. It then
gets and prints the non-null document components that were set by
OCISodaDocCreateWithKey(): the key, the content and the media type. It
then frees the document
handle.
OCISodaDoc *dochp = NULL;
OraText *documentContent = "{\"name\":\"Alexander\"}";
OraText *key = "myKey";
ub4 docFlags = OCI_DEFAULT;
sword rc = OCI_SUCCESS;
OraText *finalKey;
ub4 finalKeyLen = 0;
OraText *finalContent;
ub4 finalContentLen = 0;
OraText *media;
ub4 mediaLen = 0;
rc = OCISodaDocCreateWithKey(envhp,
documentContent,
(ub4) strlen(documentContent),
key,
(ub4) strlen(key),
docFlags,
&dochp,
errhp,
OCI_DEFAULT)
if (rc != OCI_SUCCESS) goto finally;
// Get and print the key, content and media type, which were set
// by OCISodaDocCreateWithKey().
OCIAttrGet((dvoid *) dochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &finalKey,
&finalKeyLen,
OCI_ATTR_SODA_KEY,
errhp);
printf ("Key: %.*s\n", finalKeyLen, finalKey);
OCIAttrGet((dvoid *) dochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &finalContent,
&finalContentLen,
OCI_ATTR_SODA_CONTENT,
errhp);
printf ("Content: %.*s\n", finalContentLen, finalContent);
OCIAttrGet((dvoid *)dochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &media,
&mediaLen,
OCI_ATTR_SODA_MEDIA_TYPE,
errhp);
printf ("Media type: %.*s\n", mediaLen, media);
finally:
if (dochp) OCIHandleFree((dvoid *) dochp, (ub4) OCI_HTYPE_SODA_DOCUMENT);
This is the printed output:
Key: myKey
Content: {"name" : "Alexander"}
Media type: application/json
Example 3-8 Creating an Empty Document and Then Defining Components
sword rc = OCI_SUCCESS;
OCISodaDoc *dochp = NULL;
OraText *documentContent= "{\"name\":\"Alexander\"}";
rc = OCIHandleAlloc((void *) envhp,
(void **) &dochp,
OCI_HTYPE_SODA_DOCUMENT,
(size_t) 0,
(dvoid **) 0);
if (rc != OCI_SUCCESS) goto finally;
rc = OCIAttrSet(dochp,
OCI_HTYPE_SODA_DOCUMENT,
documentContent,
(ub4) strlen(documentContent),
OCI_ATTR_SODA_CONTENT,
errhp);
finally: ...
3.8 Inserting Documents into Collections with SODA for C
Various ways to insert a document into a SODA collection are described.
If you have created a document handle, you can use
function OCISodaInsert() or
OCISodaInsertAndGet() to insert the document into a
collection. These functions create document keys automatically, unless the
collection is configured with client-assigned keys and the input document
provides the key. These functions take a document handle as one of their
arguments.
For convenience, you can alternatively use
function OCISodaInsertWithCtnt() or
OCISodaInsertAndGetWithCtnt() to insert a document
without having created a document handle. You provide only the content and
(optionally) the key for the document. (The key is needed only when
inserting into a collection that has client-assigned keys.)
If the target collection is configured for documents that have creation and last-modified time-stamp components, then all of the document-insertion functions automatically set these components. If the collection is configured to generate document versions automatically then the insertion functions also set the version component. (The default collection configuration provides both time-stamp components and the version component.)
In addition to inserting the document, functions
OCISodaInsertAndGet() and
OCISodaInsertAndGetWithCtnt() return a result
document. The result document contains the generated document components,
such as the key, version, created-on timestamp, and last-modified timestamp.
It does not contain the content of the inserted
document.
Function OCISodaInsertAndGetWithOpts() is like
OCISodaInsertAndGet(), but it inserts a document
according to options in an OCISodaOperationOptions handle.
In particular, you can use this to pass a monitoring hint to the SQL code
that underlies SODA.
You can set attribute OCI_ATTR_SODA_HINT on the
operation-options handle, to provide the text for a SQL hint syntax (that
is, the hint text, without the enclosing SQL comment syntax
/*+...*/). Use only hint
MONITOR (turn on monitoring) or
NO_MONITOR (turn off monitoring).
(You can use this to pass any SQL hints, but
MONITOR and NO_MONITOR are the
useful ones for SODA, and an inappropriate hint can cause the optimizer to
produce a suboptimal query plan.)
Note:
If the collection is configured with client-assigned document keys (which is not the default case), and the input document provides a key that identifies an existing document in the collection, then these methods return an error.
See Also:
-
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaInsert() -
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaInsertAndGet() -
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaInsertWithCtnt() -
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaInsertAndGetWithCtnt() -
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaBulkInsert() -
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaBulkInsertAndGet() -
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaBulkInsertWithCtnt() -
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaBulkInsertAndGetWithCtnt()
Example 3-9 Inserting a Document into a Collection
This example
creates a document and inserts it into a collection using function
OCISodaInsert(). The use of mode parameter
OCI_SODA_ATOMIC_COMMIT ensures that the insertion and
any other outstanding operations are
committed.
OCISodaDoc *dochp = NULL;
OraText *documentContent = "{\"name\":\"Alexander\"}";
rc = OCISodaDocCreate(envhp,
documentContent,
(ub4) strlen(documentContent),
OCI_DEFAULT,
&dochp,
errhp,
OCI_DEFAULT);
if (rc != OCI_SUCCESS) goto finally:
rc = OCISodaInsert(svchp,
collhp,
dochp,
errhp,
OCI_SODA_ATOMIC_COMMIT);
finally: ...
Example 3-10 Inserting a Document into a Collection and Getting the Result Document
This example creates a document and inserts it into a
collection using function OCISodaInsertAndGet(), which also
returns the result document, after insertion. The example then gets (and
prints) each of the generated components from that result document (which
contains them): the creation time stamp, the last-modified time stamp, the
media type, and the version. To obtain each of these components it uses
function OCIAttrGet(), passing the type of the component:
OCI_ATTR_SODA_CREATE_TIMESTAMP,
OCI_ATTR_SODA_LASTMOD_TIMESTAMP,
OCI_ATTR_SODA_MEDIA_TYPE, and
OCI_ATTR_SODA_VERSION.
sword rc = OCI_SUCCESS;
OraText *key = "myKey1";
OraText *documentContent = "{\"name\":\"Alexander\"}";
ub4 docFlags = OCI_DEFAULT;
OCISodaDoc *dochp = NULL;
OCISodaDoc *origDochp = NULL;
OraText *resultKey;
ub4 resultKeyLen = 0;
OraText *resultCreatedOn;
ub4 resultCreatedOnLen = 0;
OraText *resultLastModified;
ub4 resultLastModifiedLen = 0;
OraText *resultVersion;
ub4 resultVersionLen = 0;
OraText *resultMedia;
ub4 resultMediaLen = 0;
// Create a document with key "myKey1".
rc = OCISodaDocCreateWithKey(envhp,
documentContent,
(ub4) strlen(documentContent),
key,
(ub4) strlen(key),
docFlags,
&dochp,
errhp,
OCI_DEFAULT);
if (rc != OCI_SUCCESS) goto finally;
// Insert the document into a collection.
// collhp is a collection-handle pointer. We assume the collection it
// points to was configured to use client-assigned keys.
// Because OCISodaInsertAndGet returns the result document as dochp, we
// first save the pointer to the original input document handle, which
// is returned by OCISodaDocCreateWithKey, as origDochp.
// This lets us free the original document handle later.
origDochp = dochp;
rc = OCISodaInsertAndGet(svchp,
collhp,
&dochp,
errhp,
OCI_SODA_ATOMIC_COMMIT);
if (rc != OCI_SUCCESS) goto finally;
// Print some components of the result document. (For brevity we
// omit checking for a return value of OCI_SUCCESS in all
// OCIAttrGet() calls here.)
OCIAttrGet((dvoid *)dochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *)&resultCreatedOn,
&resultCreatedOnLen,
OCI_ATTR_SODA_CREATE_TIMESTAMP,
errhp);
printf ("Created-on time stamp: %.*s\n", resultCreatedOnLen, resultCreatedOn);
OCIAttrGet((dvoid *)dochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *)&resultLastModified,
&resultLastModifiedLen,
OCI_ATTR_SODA_LASTMOD_TIMESTAMP,
errhp);
printf ("Last-modified time stamp: %.*s\n", resultLastModifiedLen, resultLastModified);
OCIAttrGet((dvoid *)dochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *)&resultVersion,
&resultVersionLen,
OCI_ATTR_SODA_VERSION,
errhp);
printf ("Version: %.*s\n", resultVersionLen, resultVersion);
OCIAttrGet((dvoid *)dochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *)&resultMedia,
&resultMediaLen,
OCI_ATTR_SODA_MEDIA_TYPE,
errhp);
printf ("Media type: %.*s\n", resultMediaLen, resultMedia);
finally:
// Free the document handles.
if (origDochp) OCIHandleFree((dvoid *) origDochp, (ub4) OCI_HTYPE_SODA_DOCUMENT);
if (dochp) OCIHandleFree((dvoid *) dochp, (ub4) OCI_HTYPE_SODA_DOCUMENT);
Example 3-11 Inserting a Document into a Collection Without Providing a Handle
This example uses function
OCISodaInsertWithCtnt() to insert a document into a
collection without providing a document handle. Only the document key and
content are provided as arguments.
Here we assume that we
are inserting the document into a collection that is configured with
client-assigned keys. If you instead insert a document into a collection
configured for auto-generated keys then pass NULL as the
key argument and 0 as the key-length argument (which
immediately follows the key
argument).
OraText *documentContent = "{\"name\":\"Hannibal\"}";
OraText *key = "myKey2";
rc = OCISodaInsertWithCtnt(svchp,
collhp,
key,
(ub4) strlen(key),
(void *)documentContent,
(ub4) strlen(documentContent),
errhp,
OCI_SODA_ATOMIC_COMMIT);
3.9 Saving Documents Into a Collection with SODA for C
You can use OCI functions OCISodaSave(),
OCISodaSaveWithCtnt(), OCISodaSaveAndGet(),
OCISodaSaveAndGetWithCtnt(), and
OCISodaSaveAndGetWithOpts() to save documents
into a collection, which means inserting them if they are new or updating them
if they already belong to the collection. (Such an operation is sometimes called
"upserting".)
Just as for the similarly named functions that only insert or replace documents, the names of the save functions indicate their different behaviors.
-
The save functions named with
WithCtntinsert or replace a document whose content you provide, instead of a document handle. If keys for the collection are client-assigned then you provide the document key also. -
The save functions named with
AndGetreturn a result document, which contains the generated document components, such as the key, version, created-on timestamp, and last-modified timestamp. It does not contain the content of the inserted document. (It does not contain the document content.) These functions require a document handle as argument. -
Function
OCISodaSaveAndGetWithOpts()is likeOCISodaSaveAndGet(), but it saves a document according to options in anOCISodaOperationOptionshandle. In particular, you can use this to pass a monitoring hint to the SQL code that underlies SODA.You can set attribute
OCI_ATTR_SODA_HINTon the operation-options handle, to provide the text for a SQL hint syntax (that is, the hint text, without the enclosing SQL comment syntax/*+...*/). Use only hintMONITOR(turn on monitoring) orNO_MONITOR(turn off monitoring).(You can use this to pass any SQL hints, but
MONITORandNO_MONITORare the useful ones for SODA, and an inappropriate hint can cause the optimizer to produce a suboptimal query plan.)
See Also:
-
Monitoring Database Operations in Oracle Database SQL Tuning Guide for complete information about monitoring database operations
-
MONITOR and NO_MONITOR Hints in Oracle Database SQL Tuning Guide for information about the syntax and behavior of SQL hints
MONITORandNO_MONITOR
Example 3-12 Saving Documents Into a Collection with SODA for C
This example creates a document with a client-assigned key, then it uses
OCI function OCISodaSaveAndGet() to save the document to a
collection having handle collhp. This inserts the document and then
retrieves it. The example then prints some of the document components.
sword rc = OCI_SUCCESS;
OraText *key = "myKey1";
OraText *documentContent = "{\"name\":\"Alexander\"}";
ub4 docFlags = OCI_DEFAULT;
OCISodaDoc *dochp = NULL;
OCISodaDoc *origDochp = NULL;
OraText *resultKey;
ub4 resultKeyLen = 0;
OraText *resultCreatedOn;
ub4 resultCreatedOnLen = 0;
OraText *resultLastModified;
ub4 resultLastModifiedLen = 0;
OraText *resultVersion;
ub4 resultVersionLen = 0;
OraText *resultMedia;
ub4 resultMediaLen = 0;
// Create a document with key "myKey1". dochp is the document handle.
rc = OCISodaDocCreateWithKey(envhp,
documentContent,
(ub4) strlen(documentContent),
key,
(ub4) strlen(key),
docFlags,
&dochp,
errhp,
OCI_DEFAULT);
if (rc != OCI_SUCCESS) goto finally;
// Save value of document pointer returned by OCISodaDocCreateWithKey()
// as origDochp, in order to free it later.
origDochp = dochp;
// Save the document to a collection (handle collhp).
// OCISodaSaveAndGet() returns the result document as dochp.
// This example assumes the collection uses client-assigned keys.
rc = OCISodaSaveAndGet(svchp,
collhp,
&dochp,
errhp,
OCI_SODA_ATOMIC_COMMIT);
if (rc != OCI_SUCCESS) goto finally;
// Print some components of the inserted document.
// (For brevity we omit checking for return value OCI_SUCCESS.)
OCIAttrGet((dvoid *)dochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *)&resultCreatedOn,
&resultCreatedOnLen,
OCI_ATTR_SODA_CREATE_TIMESTAMP,
errhp);
printf ("Created-on time stamp: %.*s\n", resultCreatedOnLen, resultCreatedOn);
OCIAttrGet((dvoid *)dochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *)&resultLastModified,
&resultLastModifiedLen,
OCI_ATTR_SODA_LASTMOD_TIMESTAMP,
errhp);
printf ("Last-modified time stamp: %.*s\n", resultLastModifiedLen, resultLastModified);
OCIAttrGet((dvoid *)dochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *)&resultVersion,
&resultVersionLen,
OCI_ATTR_SODA_VERSION,
errhp);
printf ("Version: %.*s\n", resultVersionLen, resultVersion);
OCIAttrGet((dvoid *)dochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *)&resultMedia,
&resultMediaLen,
OCI_ATTR_SODA_MEDIA_TYPE,
errhp);
printf ("Media type: %.*s\n", resultMediaLen, resultMedia);
finally:
// Free the document handles.
if (origDochp) OCIHandleFree((dvoid *) origDochp, (ub4) OCI_HTYPE_SODA_DOCUMENT);
if (dochp) OCIHandleFree((dvoid *) dochp, (ub4) OCI_HTYPE_SODA_DOCUMENT);
Parent topic: Using SODA for C
3.10 SODA for C Read and Write Operations
For all read operations, and for all write operations other than insertions and saves into a collection, you: (1) allocate an operation-options handle, (2) set some of its attributes to specify a particular operation, and (3) pass the handle to a generic function that performs the operation.
These are the read-operation functions:
-
OCISodaFindOne()— Find and return at most one document. -
OCISodaFind()— Find multiple documents and return a cursor to them. -
OCISodaDocCount()— Find multiple documents and return the number of documents found.
These are the write-operation functions:
-
OCISodaReplOne()— Replace one document. -
OCISodaReplOneAndGet()— Replace one document and return the result document. -
OCISodaRemove()— Remove multiple documents.
You use function OCIHandleAlloc() to allocate an empty
operation-options handle:
OCISodaOperationOptions *opthp;
// Create an empty operation options handle.
rc = OCIHandleAlloc((void *) envhp,
(void **)&opthp,
OCI_HTYPE_SODA_OPER_OPTIONS,
(size_t) 0,
(dvoid **) 0);You use function OCIAttrSet() to set a single attribute of an
operation-options handle. For example, this sets attribute filter with value
{"name:"Ruth"}:
OraText * filter = "{\"name\" : \"Ruth\"}";
// Set the filter on the operation options handle.
rc = OCIAttrSet(opthp,
OCI_HTYPE_SODA_OPER_OPTIONS,
filter,
strlen(filter),
OCI_ATTR_SODA_FILTER,
errhp);There is no attribute that represents multiple document keys. For an operation
that involves multiple keys you use function OCISodaOperKeysSet() to set
them.
Note:
If you use function OCIAttrSet() to set attribute OCI_ATTR_SODA_KEY on an operation-options handle, and you also use function OCISodaOperKeysSet() to set multiple keys on the same handle, then only the latest of the two settings takes effect. The effect of the first function invoked is overridden by the effect of the second.
See Also:
- Oracle Call Interface Programmer's Guide for information about the SODA attributes that can be set on an operation-options handle
- Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaOperKeySet()
Parent topic: Using SODA for C
3.11 Finding Documents in Collections with SODA for C
To find documents in a collection use function
OCISodaFind(), passing it an operation-options handle that specifies the
particular find operation. To find the unique document that has a given key you can
alternatively use OCI convenience function OCISodaFindOneWithKey(), which does
not require an operation-options handle.
See Also:
-
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaFind() -
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaFindOneWithKey()
Example 3-13 Finding All Documents in a Collection
This example first obtains a cursor for a query result list that contains each document in a collection. It then uses the cursor in a while statement to get and print the components of each document, as a string.
OraText *key = NULL;
ub4 keyLen = 0;
OraText *content = NULL;
ub4 contentLen = 0;
OraText *version = NULL;
ub4 versionLen = 0;
OraText *lastModified = NULL;
ub4 lastModifiedLen = 0;
OraText *mediaType = NULL;
ub4 mediaTypeLen = 0;
OraText *createdOn = NULL;
ub4 createdOnLen = 0;
ub4 findFlags = OCI_DEFAULT;
OCISodaDocCursor *cursorhp = NULL;
OCISodaDoc *foundDochp = NULL;
OCISodaOperationOptions *opthp;
// Allocate an empty operation-options handle.
rc = OCIHandleAlloc((void *) envhp, (void **)&opthp,
OCI_HTYPE_SODA_OPER_OPTIONS, (size_t) 0,
(dvoid **) 0);
if (rc != OCI_SUCCESS) goto finally;
// Find all documents in the collection.
//
// Because the operation-options handle (opthp) is empty, no conditions
// are set on the find operation, so all documents are returned.
//
// collhp is an OCISodaColl pointer, representing an open collection.
//
// cursorhp is a OCISodaDocCursor pointer to a returned cursor over the
// resulting document set.
rc = OCISodaFind(svchp,
collhp,
opthp,
findFlags,
&cursorhp,
errhp,
OCI_DEFAULT);
if (rc != OCI_SUCCESS) goto finally;
// Fetch each document from the cursor, and print all of its components.
while (OCISodaDocGetNext(svchp,
cursorhp,
&foundDochp,
errhp,
OCI_DEFAULT)
== OCI_SUCCESS)
{
// Get and print components of found document.
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &key,
&keyLen,
OCI_ATTR_SODA_KEY,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Key: %.*s\n", keyLen, key);
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &version,
&versionLen,
OCI_ATTR_SODA_VERSION,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Version: %.*s\n", versionLen, version);
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &lastModified,
&lastModifiedLen,
OCI_ATTR_SODA_LASTMOD_TIMESTAMP,
if (rc != OCI_SUCCESS) goto finally;
printf("Last-modified: %.*s\n", lastModifiedLen, lastModified);
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &createdOn,
&createdOnLen,
OCI_ATTR_SODA_CREATE_TIMESTAMP,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Created: %.*s\n", createdOnLen, createdOn);
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &mediaType,
&mediaTypeLen,
OCI_ATTR_SODA_MEDIA_TYPE,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Media Type: %.*s\n", mediaTypeLen, mediaType);
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &content,
&contentLen,
OCI_ATTR_SODA_CONTENT,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Content: %.*s \n", contentLen, content);
// Important: free document handle before fetching next document.
// This releases memory associated with the current document.
if (foundDochp)
(void) OCIHandleFree((dvoid *) foundDochp, OCI_HTYPE_SODA_DOCUMENT);
}
finally:
// Free all handles.
if (cursorhp)
(void) OCIHandleFree((dvoid *) cursorhp, OCI_HTYPE_SODA_DOC_CURSOR);
if (opthp)
(void) OCIHandleFree((dvoid *) opthp, OCI_HTYPE_SODA_OPER_OPTIONS);
if (collhp)
(void) OCIHandleFree((dvoid *) collhp, OCI_HTYPE_SODA_COLLECTION);
if (foundDochp)
(void) OCIHandleFree((dvoid *) foundDochp, OCI_HTYPE_SODA_DOCUMENT);
Example 3-14 Finding the Unique Document That Has a Given Document Key
This example sets up an operations options handle
with the given UUID key (E914016C41174F6CBF7C877C7F9EB4C2), which it passes
to function OCISodaFindOne() to find the document with that key.
After finding the document, the example uses function
OCIAttrGet() to retrieve the document key and content, and then it prints
them. Finally, it frees the document handles that were allocated for the collection,
document, and operations options.
As an alternative to setting the key
attribute on an operation-options handle and using OCISodaFindOne(), you
can use convenience function OCISodaFindOneWithKey. It accepts a key
argument directly, in place of an operation-options handle.
OraText *key = NULL;
ub4 keyLen = 0;
OraText *content = NULL;
ub4 contentLen = 0;
ub4 findFlags = OCI_DEFAULT;
OraText *inKey = "E914016C41174F6CBF7C877C7F9EB4C2";
OCISodaDoc *foundDochp = NULL;
OCISodaOperationOptions *opthp;
// Allocate an empty operation-options handle.
rc = OCIHandleAlloc((void *) envhp, (void **)&opthp,
OCI_HTYPE_SODA_OPER_OPTIONS, (size_t) 0,
(dvoid **) 0);
if (rc != OCI_SUCCESS) goto finally;
// Set key of document we want to find, on operation-options handle.
rc = OCIAttrSet(opthp,
OCI_HTYPE_SODA_OPER_OPTIONS,
inKey,
strlen(inKey),
OCI_ATTR_SODA_KEY,
errhp);
if (rc != OCI_SUCCESS) goto finally;
// Find the document with the key, by way of the operation-options handle.
//
// collhp is an OCISodaColl pointer, representing an open collection.
rc = OCISodaFindOne(svchp,
collhp,
opthp,
findFlags,
&foundDochp,
errhp,
OCI_DEFAULT);
if (rc != OCI_SUCCESS) goto finally;
// Get and print components of found document.
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &key,
&keyLen,
OCI_ATTR_SODA_KEY,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Key: %.*s\n", keyLen, key);
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &content,
&contentLen,
OCI_ATTR_SODA_CONTENT,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Content: %.*s \n", contentLen, content);
finally:
// Free all handles.
if (collhp)
(void) OCIHandleFree((dvoid *) collhp, OCI_HTYPE_SODA_COLLECTION);
if (foundDochp)
(void) OCIHandleFree((dvoid *) foundDochp, OCI_HTYPE_SODA_DOCUMENT);
if (opthp)
(void) OCIHandleFree((dvoid *) opthp, OCI_HTYPE_SODA_OPER_OPTIONS);
Example 3-15 Finding Multiple Documents with Specified Document Keys
This example finds three documents using their keys.
The keys and their string lengths, as arrays, and the number of keys (3) are passed to
function OCISodaOperKeysSet(), which sets up the operation-options handle
appropriately. (You cannot set multiple keys and their lengths using standard function
OCIAttrSet().) The example then invokes function
OCISodaFind(), passing it the handle.
This example
uses function OCISodaFind to find three documents using their keys. The
keys and their string lengths, as arrays, and the number of keys (3) are passed to function
OCISodaOperKeysSet(), which sets up the operation-options handle with
this information. (You cannot set multiple keys and their lengths using standard function
OCIAttrSet().)
Note:
If you use function OCIAttrSet() to set attribute OCI_ATTR_SODA_KEY on an operation-options handle, and you also use function OCISodaOperKeysSet() to set multiple keys on the same handle, then only the latest of the two settings takes effect. The effect of the first function invoked is overridden by the effect of the second.
OraText *key = NULL;
ub4 keyLen = 0;
OraText *content = NULL;
ub4 contentLen = 0;
ub4 findFlags = OCI_DEFAULT;
OraText *keys[3] = {"6B67A10BC6EB4FB7BFA1ECE7E697C507",
"9195598AA9FB4F1CBFA376F35BF78588",
"7FD55EED38BE4F70BF327F8132394E8B"};
ub4 keyLengths[3];
int i = 0;
OCISodaDocCursor *cursorhp = NULL;
OCISodaDoc *foundDochp = NULL;
OCISodaOperationOptions *opthp;
// Allocate an empty operation-options handle.
rc = OCIHandleAlloc((void *) envhp, (void **)&opthp,
OCI_HTYPE_SODA_OPER_OPTIONS, (size_t) 0,
(dvoid **) 0);
if (rc != OCI_SUCCESS) goto finally;
// Fill array of key lengths.
for(i=0; i<3; i++)
keyLengths[i] = strlen(keys[i]);
// Set keys and their lengths on the operation-options handle.
//
// Use function OCISodaOperKeysSet(). (You cannot set keys
// and their lengths using standard function OCIAttrSet().)
rc = OCISodaOperKeysSet(opthp,
keys,
keyLengths,
3,
errhp,
OCI_DEFAULT);
if (rc != OCI_SUCCESS) goto finally;
// Find documents that match the keys set in operation-options handle.
//
// collhp is an OCISodaColl pointer, representing an open collection.
//
// cursorhp is a OCISodaDocCursor pointer to a returned cursor over the
// resulting document set.
rc = OCISodaFind(svchp,
collhp,
opthp,
findFlags,
&cursorhp,
errhp,
OCI_DEFAULT);
if (rc != OCI_SUCCESS) goto finally;
// Fetch each document from the cursor, and its key and content.
while (OCISodaDocGetNext(svchp,
cursorhp,
&foundDochp,
errhp,
OCI_DEFAULT)
== OCI_SUCCESS)
{
// Get and print components of found document.
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &key,
&keyLen,
OCI_ATTR_SODA_KEY,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Key: %.*s\n", keyLen, key);
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &content,
&contentLen,
OCI_ATTR_SODA_CONTENT,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Content: %.*s \n\n", contentLen, content);
// Important: Free the document handle before fetching the next document.
// This releases memory associated with the current document.
if (foundDochp)
(void) OCIHandleFree((dvoid *) foundDochp, OCI_HTYPE_SODA_DOCUMENT);
}
finally:
// Free all handles.
if (cursorhp)
(void) OCIHandleFree((dvoid *) cursorhp, OCI_HTYPE_SODA_DOC_CURSOR );
if (opthp)
(void) OCIHandleFree((dvoid *) cursorhp, OCI_HTYPE_SODA_OPER_OPTIONS );
if (collhp)
(void) OCIHandleFree((dvoid *) collhp, OCI_HTYPE_SODA_COLLECTION);
if (foundDochp)
(void) OCIHandleFree((dvoid *) foundDochp, OCI_HTYPE_SODA_DOCUMENT);
Example 3-16 Finding Documents with a Filter Specification
Function OCISodaFind() provides a
powerful way to filter JSON documents in a collection. To use it you pass an
operation-options handle that specifies attribute OCI_ATTR_SODA_FILTER as a
JSON query-by-example (QBE, also called a filter specification).
The syntax of filter specifications is an expressive pattern-matching language for JSON documents. This example uses only a very simple QBE, just to indicate how you make use of one in SODA for C.
This example sets operation-options handle attribute
OCI_ATTR_SODA_FILTER to a filter that specifies JSON documents whose
name field has value "Alexander". It then uses the
operation-options handle to find the documents that match that filter. Finally, it prints
the key and content of each found
document.
OraText *key = NULL;
ub4 keyLen = 0;
OraText *content = NULL;
ub4 contentLen = 0;
ub4 findFlags = OCI_DEFAULT;
OraText *filter = "{ \"name\" : \"Alexander\"}";
OCISodaDocCursor *cursorhp = NULL;
OCISodaDoc *foundDochp = NULL;
OCISodaOperationOptions *opthp;
// Allocate an empty operation-options handle.
rc = OCIHandleAlloc((void *) envhp, (void **)&opthp,
OCI_HTYPE_SODA_OPER_OPTIONS, (size_t) 0,
(dvoid **) 0);
if (rc != OCI_SUCCESS) goto finally;
// Set the filter (QBE) on the operation-options handle.
rc = OCIAttrSet(opthp,
OCI_HTYPE_SODA_OPER_OPTIONS,
filter,
strlen(filter),
OCI_ATTR_SODA_FILTER,
errhp);
if (rc != OCI_SUCCESS) goto finally;
// Find all documents that match filter set in operation-options handle.
//
// collhp is an OCISodaColl pointer, representing an open collection.
//
// cursorhp is a OCISodaDocCursor pointer to a returned cursor over the
// resulting document set.
rc = OCISodaFind(svchp,
collhp,
opthp,
findFlags,
&cursorhp,
errhp,
OCI_DEFAULT);
if (rc != OCI_SUCCESS) goto finally;
// Fetch each document from the cursor, and print its key and content.
while (OCISodaDocGetNext(svchp,
cursorhp,
&foundDochp,
errhp,
OCI_DEFAULT)
== OCI_SUCCESS)
{
// Get and print key and content of found document.
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &key,
&keyLen,
OCI_ATTR_SODA_KEY,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Key: %.*s\n", keyLen, key);
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &content,
&contentLen,
OCI_ATTR_SODA_CONTENT,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Content: %.*s \n\n", contentLen, content);
// Important: Free the document handle before fetching next document.
// This releases memory associated with the current document.
if (foundDochp)
(void) OCIHandleFree((dvoid *) foundDochp, OCI_HTYPE_SODA_DOCUMENT);
}
finally:
// Free all handles.
if (cursorhp)
(void) OCIHandleFree((dvoid *) cursorhp, OCI_HTYPE_SODA_DOC_CURSOR );
if (opthp)
(void) OCIHandleFree((dvoid *) cursorhp, OCI_HTYPE_SODA_OPER_OPTIONS );
if (collhp)
(void) OCIHandleFree((dvoid *) collhp, OCI_HTYPE_SODA_COLLECTION);
if (foundDochp)
(void) OCIHandleFree((dvoid *) foundDochp, OCI_HTYPE_SODA_DOCUMENT);
Example 3-17 Finding Documents with a Filter Specification and Pagination
This example uses function
OCISodaFind() in a pagination query. It passes an operation-options
handle that specifies attribute OCI_ATTR_SODA_FILTER as a QBE, as well as
attributes OCI_ATTR_SODA_SKIP (the number of documents to skip) and
OCI_ATTR_SODA_LIMIT (the maximum number of documents to return). Except
for specifying pagination (skip and limit) this example is the same as Example 3-16.
OraText *key = NULL;
ub4 keyLen = 0;
OraText *content = NULL;
ub4 contentLen = 0;
ub4 findFlags = OCI_DEFAULT;
OraText *filter = "{ \"name\" : \"Alexander\"}";
ub4 skip = 1000;
ub4 limit = 100;
OCISodaDocCursor *cursorhp = NULL;
OCISodaDoc *foundDochp = NULL;
OCISodaOperationOptions *opthp;
// Allocate an empty operation-options handle.
rc = OCIHandleAlloc((void *) envhp, (void **)&opthp,
OCI_HTYPE_SODA_OPER_OPTIONS, (size_t) 0,
(dvoid **) 0);
if (rc != OCI_SUCCESS) goto finally;
// Set the filter (QBE) on the operation-options handle.
rc = OCIAttrSet(opthp,
OCI_HTYPE_SODA_OPER_OPTIONS,
filter,
strlen(filter),
OCI_ATTR_SODA_FILTER,
errhp);
if (rc != OCI_SUCCESS) goto finally;
// Set the number of documents to skip on the operation-options handle.
rc = OCIAttrSet(opthp,
OCI_HTYPE_SODA_OPER_OPTIONS,
&skip,
0,
OCI_ATTR_SODA_SKIP,
errhp);
if (rc != OCI_SUCCESS) goto finally;
// Set the limit of the number of documents to return, on the
// operation-options handle.
rc = OCIAttrSet(opthp,
OCI_HTYPE_SODA_OPER_OPTIONS,
&limit,
0,
OCI_ATTR_SODA_LIMIT,
errhp);
if (rc != OCI_SUCCESS) goto finally;
// Find all documents in collection that match the filter set in the
// operation-options handle.
// Honor skip and limit values set in the handle.
//
// collhp is an OCISodaColl pointer, representing an open collection.
//
// cursorhp is a OCISodaDocCursor pointer to a returned cursor over the
// resulting document set.
rc = OCISodaFind(svchp,
collhp,
opthp,
findFlags,
&cursorhp,
errhp,
OCI_DEFAULT);
if (rc != OCI_SUCCESS) goto finally;
// Fetch each document from the cursor, and print its key and content.
while (OCISodaDocGetNext(svchp,
cursorhp,
&foundDochp,
errhp,
OCI_DEFAULT)
== OCI_SUCCESS)
{
// Get and print components of found document.
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &key,
&keyLen,
OCI_ATTR_SODA_KEY,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Key: %.*s\n", keyLen, key);
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &content,
&contentLen,
OCI_ATTR_SODA_CONTENT,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Content: %.*s \n\n", contentLen, content);
// Important: Free the document handle before fetching the next document.
// This releases memory associated with the current document.
if (foundDochp)
(void) OCIHandleFree((dvoid *) foundDochp, OCI_HTYPE_SODA_DOCUMENT);
}
finally:
// Free all handles.
if (cursorhp)
(void) OCIHandleFree((dvoid *) cursorhp, OCI_HTYPE_SODA_DOC_CURSOR );
if (opthp)
(void) OCIHandleFree((dvoid *) cursorhp, OCI_HTYPE_SODA_OPER_OPTIONS );
if (collhp)
(void) OCIHandleFree((dvoid *) collhp, OCI_HTYPE_SODA_COLLECTION);
if (foundDochp)
(void) OCIHandleFree((dvoid *) foundDochp, OCI_HTYPE_SODA_DOCUMENT);
Example 3-18 Finding a Particular Version of a Document
This example uses
function OCISodaFindOne() with an operation-options handle that specifies
the version, as well as the key, of the document to find.
When specifying the document version you typically specify the key as well. But you can specify the version along with a filter, provided the filter specifies at most one document in the collection.
OraText *key = NULL;
ub4 keyLen = 0;
OraText *content = NULL;
ub4 contentLen = 0;
OraText *version = NULL;
ub4 versionLen = 0;
ub4 findFlags = OCI_DEFAULT;
OraText *inKey = "E914016C41174F6CBF7C877C7F9EB4C2";
OraText *inVersion =
"7CCEF2F54035DE9A9D64653645DBEF7E61B92142F2E41B3F6144262A5F7BC054";
OCISodaDocCursor *cursorhp = NULL;
OCISodaDoc *foundDochp = NULL;
OCISodaOperationOptions *opthp;
// Allocate an empty operation-options handle.
rc = OCIHandleAlloc((void *) envhp, (void **)&opthp,
OCI_HTYPE_SODA_OPER_OPTIONS, (size_t) 0,
(dvoid **) 0);
if (rc != OCI_SUCCESS) goto finally;
// Set the key on the operation-options handle.
rc = OCIAttrSet(opthp,
OCI_HTYPE_SODA_OPER_OPTIONS,
inKey,
strlen(inKey),
OCI_ATTR_SODA_KEY,
errhp);
if (rc != OCI_SUCCESS) goto finally;
// Set the version on the operation-options handle.
rc = OCIAttrSet(opthp,
OCI_HTYPE_SODA_OPER_OPTIONS,
inVersion,
strlen(inVersion),
OCI_ATTR_SODA_VERSION,
errhp);
if (rc != OCI_SUCCESS) goto finally;
// Find document that matches key and version set on
// operation-options handle.
rc = OCISodaFindOne(svchp,
collhp,
opthp,
findFlags,
&foundDochp,
errhp,
OCI_DEFAULT);
if (rc != OCI_SUCCESS) goto finally;
// Get the found document and print its key, version, and content.
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &key,
&keyLen,
OCI_ATTR_SODA_KEY,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Key: %.*s\n", keyLen, key);
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &version,
&versionLen,
OCI_ATTR_SODA_VERSION,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Version: %.*s\n", versionLen, version);
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &content,
&contentLen,
OCI_ATTR_SODA_CONTENT,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Content: %.*s \n", contentLen, content);
finally:
// Free all handles.
if (collhp)
(void) OCIHandleFree((dvoid *) collhp, OCI_HTYPE_SODA_COLLECTION);
if (foundDochp)
(void) OCIHandleFree((dvoid *) foundDochp, OCI_HTYPE_SODA_DOCUMENT);
if (opthp)
(void) OCIHandleFree((dvoid *) opthp, OCI_HTYPE_SODA_OPER_OPTIONS);
Example 3-19 Counting the Number of Documents Found
This example uses function OCISodaDocCount()
to get a count of all of the documents in a collection that satisfy a given filter
specification.
OraText *filter = "{ \"name\" : \"Alexander\"}";
ub8 count = 0;
OCISodaOperationOptions *opthp;
// Allocate an empty operation-options handle.
rc = OCIHandleAlloc((void *) envhp, (void **)&opthp,
OCI_HTYPE_SODA_OPER_OPTIONS, (size_t) 0,
(dvoid **) 0);
if (rc != OCI_SUCCESS) goto finally;
// Set the filter (QBE) on the operation-options handle.
rc = OCIAttrSet(opthp,
OCI_HTYPE_SODA_OPER_OPTIONS,
filter,
strlen(filter),
OCI_ATTR_SODA_FILTER,
errhp);
if (rc != OCI_SUCCESS) goto finally;
// Number of documents that match filter set on operation-options
// handle is returned as count.
rc = OCISodaDocCount(svchp,
collhp,
opthp ,
&count,
errhp,
OCI_DEFAULT);
if (rc != OCI_SUCCESS) goto finally;
printf ("Number of matching documents: %d\n", count);
finally:
// Free all handles.
if (collhp)
(void) OCIHandleFree((dvoid *) collhp, OCI_HTYPE_SODA_COLLECTION );
if (opthp)
(void) OCIHandleFree((dvoid *) opthp, OCI_HTYPE_SODA_OPER_OPTIONS );
Example 3-20 Using Full-Text Search To Find Documents in a Heterogeneous Collection Using SODA For C
This example uses QBE operator $textContains to perform a
full-text search of a heterogeneous collection, which is one that
has the media type column. For example, Microsoft Word, Portable Document Format (PDF), and
plain-text documents can all be searched using $textContains.
(You use QBE operator $contains, not
$textContains, to perform full-text search of a collection of JSON
documents.)
The search pattern in this example is Rogers, which means
search for that literal text anywhere in a document of collection
collhp.
OCISodaDocCursor *cursor = NULL;
OCISodaOperationOptions *opthp = NULL;
oratext *filter;
// Allocate an empty operation options handle.
rc = OCIHandleAlloc((void *) envhp, (void **)&opthp,
OCI_HTYPE_SODA_OPER_OPTIONS, (size_t) 0,
(dvoid **) 0);
if (rc != OCI_SUCCESS) goto finally;
// Set the QBE filter attribute on the operation handle
filter = (oratext *) "{ \"$textContains\" : \"Rogers\" }";
rc = OCIAttrSet(opthp, OCI_HTYPE_SODA_OPER_OPTIONS, filter, (ub4) strlen(filter),
OCI_ATTR_SODA_FILTER, errhp);
if (rc != OCI_SUCCESS) goto finally;
// Get document cursor using operation options handle
rc = OCISodaFind(svchp, collhp, opthp, OCI_DEFAULY, &cursor,
errhp, OCI_SODA_ATOMIC_COMMIT);
if (rc != OCI_SUCCESS) goto finally;
finally:
// Free all handles.
if (cursor)
(void) OCIHandleFree((dvoid *) cursorhp, OCI_HTYPE_SODA_DOC_CURSOR);
if (opthp)
(void) OCIHandleFree((dvoid *) opthp, OCI_HTYPE_SODA_OPER_OPTIONS);The syntax of the search-pattern value for $textContains is
the same as that for SQL function contains, and the resulting behavior is
the same. This means, for instance, that you can query for text that is near some other
text, or query use fuzzy pattern-matching. (If the search-pattern argument contains a
character or a word that is reserved with respect to Oracle Text search then you must escape
that character or word.)
In order to use operator $textContains to search a
collection, you must first have defined an Oracle Text search index on the content column of
the collection, using SQL. This SQL code does that; it creates index
mySearchIndex on content column myContentColumn of
collection myTextCollection.
CREATE SEARCH INDEX mySearchIndex ON
myTextCollection(myContentColumn)Related Topics
Parent topic: Using SODA for C
3.12 Setting the Prefetch Size for SODA Find Operations
A call to function OCISodaFind() prefetches multiple
documents in order to reduce the number of required client–database round trips by
OCISodaDocGetNext(). You can change the number of documents used for a
prefetch batch by setting attribute OCI_ATTR_SODA_FETCH_ARRAY_SIZE on the
operation handle.
The default value of attribute OCI_HTYPE_SODA_OPER_OPTIONS is 100,
meaning that calls to OCISodaDocGetNext() do not make round trips
until the internal prefetch buffers contain 100 documents. The higher the attribute
value, the fewer the number of round trips.
Example 3-21 Setting the Prefetch Array Size
This example sets the prefetch array size to 125, so that each round trip can fetch up to 125 documents.
OraText *key = NULL;
ub4 keyLen = 0;
OraText *content = NULL;
ub4 contentLen = 0;
OraText *version = NULL;
ub4 versionLen = 0;
OraText *lastModified = NULL;
ub4 lastModifiedLen = 0;
OraText *mediaType = NULL;
ub4 mediaTypeLen = 0;
OraText *createdOn = NULL;
ub4 createdOnLen = 0;
ub4 findFlags = OCI_DEFAULT;
ub4 pfchsz;
OCISodaDocCursor *cursorhp = NULL;
OCISodaDoc *foundDochp = NULL;
OCISodaOperationOptions *opthp;
// Allocate an empty operation-options handle.
rc = OCIHandleAlloc((void *) envhp, (void **)&opthp,
OCI_HTYPE_SODA_OPER_OPTIONS, (size_t) 0,
(dvoid **) 0);
if (rc != OCI_SUCCESS) goto finally;
// Set array prefetch size to 125, overriding the default value of 100.
pfchsz = 125;
OCIAttrSet(opthp, OCI_HTYPE_SODA_OPER_OPTIONS, &pfchsz, 0,
OCI_ATTR_SODA_FETCH_ARRAY_SIZE, errhp)
// Find all documents in the collection.
//
// The operation-options handle (opthp) is empty — no conditions
// are set on the find operation — so all documents are returned.
//
// collhp is an OCISodaColl pointer, representing an open collection.
//
// cursorhp is a OCISodaDocCursor pointer to a returned cursor over the
// resulting document set.
rc = OCISodaFind(svchp,
collhp,
opthp,
findFlags,
&cursorhp,
errhp,
OCI_DEFAULT);
if (rc != OCI_SUCCESS) goto finally;
// Fetch each document from the cursor, and print all of its components.
while (OCISodaDocGetNext(svchp,
cursorhp,
&foundDochp,
errhp,
OCI_DEFAULT)
== OCI_SUCCESS)
{
// Get and print the components of the found document.
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &key,
&keyLen,
OCI_ATTR_SODA_KEY,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Key: %.*s\n", keyLen, key);
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &version,
&versionLen,
OCI_ATTR_SODA_VERSION,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Version: %.*s\n", versionLen, version);
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &lastModified,
&lastModifiedLen,
OCI_ATTR_SODA_LASTMOD_TIMESTAMP,
if (rc != OCI_SUCCESS) goto finally;
printf("Last-modified: %.*s\n", lastModifiedLen, lastModified);
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &createdOn,
&createdOnLen,
OCI_ATTR_SODA_CREATE_TIMESTAMP,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Created: %.*s\n", createdOnLen, createdOn);
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &mediaType,
&mediaTypeLen,
OCI_ATTR_SODA_MEDIA_TYPE,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Media Type: %.*s\n", mediaTypeLen, mediaType);
rc = OCIAttrGet((dvoid *) foundDochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &content,
&contentLen,
OCI_ATTR_SODA_CONTENT,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Content: %.*s \n", contentLen, content);
// Important: free document handle before fetching next document.
// This releases memory associated with the current document.
if (foundDochp)
(void) OCIHandleFree((dvoid *) foundDochp, OCI_HTYPE_SODA_DOCUMENT);
}
finally:
// Free all handles.
if (cursorhp)
(void) OCIHandleFree((dvoid *) cursorhp, OCI_HTYPE_SODA_DOC_CURSOR);
if (opthp)
(void) OCIHandleFree((dvoid *) opthp, OCI_HTYPE_SODA_OPER_OPTIONS);
if (collhp)
(void) OCIHandleFree((dvoid *) collhp, OCI_HTYPE_SODA_COLLECTION);
if (foundDochp)
(void) OCIHandleFree((dvoid *) foundDochp, OCI_HTYPE_SODA_DOCUMENT);
Parent topic: Using SODA for C
3.13 Replacing Documents in a Collection with SODA for C
You can use function OCISodaReplOneAndGet() to replace a
document in a collection, passing it an operation-options handle that specifies the key of the
document to replace as well as the new, replacement document. It returns that replacement
document, but with all of its metadata filled in, as the result document.
Function OCISodaReplOne() is the same as
OCISodaReplOneAndGet(), except that it does not return the result
document with completed metadata.
These are the most generic document-replacement functions. There are also other, convenience functions for more specific use cases.
You can use these convenience functions if only the document content is to be replaced. Instead of passing them a replacement document, you pass just the new (JSON) content as a textual argument.
-
OCISodaReplOneAndGetWithCtnt() -
OCISodaReplOneWithCtnt()
You can use these convenience functions if only the document key is to be specified. Instead of passing them an operation-options handle, you pass just the replacement document and the key of the document to replace. This means that you cannot specify a filter, document version, and so on.
-
OCISodaReplOneAndGetWithKey() -
OCISodaReplOneWithKey()
The functions with AndGet in their
name return the new (result) document as the value of the same parameter that was used for
the input document, so you can get its components.
Whichever replacement
function you use, it returns a Boolean value as output parameter
isReplaced, indicating whether the replacement operation was
successful.
You can update a document using pessimistic locking by setting the lock
attribute on the operation options handle to TRUE when you obtain the
document. This is illustrated in Example 3-24.
See Also:
-
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaReplOne() -
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaReplOneAndGet() -
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaReplOneWithCtnt() -
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaReplOneAndGetWithCtnt() -
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaReplOneWithKey() -
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaReplOneAndGetWithKey()
Example 3-22 Replacing a Document, Given Its Key, and Getting the Result Document Using SODA For C
This example creates a new document as a replacement for the document with UUID
key "3C03C00FA3904FC2BF5182C424A2C6C1". It uses OCI function
OCISodaReplOneAndGet() to replace the document having that key, and it
gets the result document.
It uses function OCIAttrGet()
to retrieve various components from the result document, which it prints. The use of mode
parameter OCI_SODA_ATOMIC_COMMIT ensures that the replacement and any other
outstanding operations are
committed.
OCISodaDoc *dochp = NULL;
OCISodaDoc *tempDochp = NULL;
// Document content: JSON data.
char documentContent[30] = "{\"name\":\"LiLing\"}";
ub4 docFlags = OCI_DEFAULT;
OraText *key = NULL;
ub4 keyLen = 0;
OraText *content = NULL;
ub4 contentLen = 0;
OraText *version = NULL;
ub4 versionLen = 0;
OraText *lastModified = NULL;
ub4 lastModifiedLen = 0;
OraText *mediaType = NULL;
ub4 mediaTypeLen = 0;
OraText *createdOn = NULL;
ub4 createdOnLen = 0;
boolean isReplaced = FALSE;
OCISodaOperationOptions *opthp;
OraText *inKey = "3C03C00FA3904FC2BF5182C424A2C6C1";
// Create a temporary replacement document, which has documentContent
// as its content.
rc = OCISodaDocCreate(envhp,
documentContent,
(ub4) strlen(documentContent),
docFlags,
&dochp,
errhp,
OCI_DEFAULT);
if (rc != OCI_SUCCESS)
{
printf("OCISodaDocCreate failed\n");
goto finally;
}
// Allocate an empty operation-options handle.
rc = OCIHandleAlloc((void *) envhp, (void **)&opthp,
OCI_HTYPE_SODA_OPER_OPTIONS, (size_t) 0,
(dvoid **) 0);
if (rc != OCI_SUCCESS) goto finally;
// Set the document-key attribute on the operation-options handle.
rc = OCIAttrSet(opthp,
OCI_HTYPE_SODA_OPER_OPTIONS,
inKey,
strlen(inKey),
OCI_ATTR_SODA_KEY,
errhp);
if (rc != OCI_SUCCESS) goto finally;
// OCISodaReplOneAndGet returns the result document as dochp, so
// before calling it we save a pointer, tempDochp, to the handle that
// was returned by OCISodaDocCreate. Later we free tempDochp.
tempDochp = dochp;
// Replace the document that has the key set in the operation-options
// handle with the new, replacement document pointed to by dochp,
// and get back the result document.
//
// The result document has the content of the replacement document,
// but it also has all of the other document components, automatically
// populated by SODA when the replacement document was inserted.
rc = OCISodaReplOneAndGet(svchp,
collhp,
opthp,
&dochp,
&isReplaced,
errhp,
OCI_SODA_ATOMIC_COMMIT);
if (rc != OCI_SUCCESS) goto finally;
if (isReplaced) printf ("Document was replaced.\n");
// Get and print the components of the document after replacement.
rc = OCIAttrGet((dvoid *) dochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &key,
&keyLen,
OCI_ATTR_SODA_KEY,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Key: %.*s\n", keyLen, key);
rc = OCIAttrGet((dvoid *) dochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &version,
&versionLen,
OCI_ATTR_SODA_VERSION,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Version: %.*s\n", versionLen, version);
rc = OCIAttrGet((dvoid *) dochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &lastModified,
&lastModifiedLen,
OCI_ATTR_SODA_LASTMOD_TIMESTAMP,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Last-modified: %.*s\n", lastModifiedLen, lastModified);
rc = OCIAttrGet((dvoid *) dochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &createdOn,
&createdOnLen,
OCI_ATTR_SODA_CREATE_TIMESTAMP,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Created: %.*s\n", createdOnLen, createdOn);
rc = OCIAttrGet((dvoid *) dochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &mediaType,
&mediaTypeLen,
OCI_ATTR_SODA_MEDIA_TYPE,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Media Type: %.*s\n", mediaTypeLen, mediaType);
finally:
// Release the session and free all handles, including the handle
// of the temporary document.
if (collhp)
(void) OCIHandleFree((dvoid *) collhp, OCI_HTYPE_SODA_COLLECTION);
if (dochp)
(void) OCIHandleFree((dvoid *) dochp, OCI_HTYPE_SODA_DOCUMENT);
if (opthp)
(void) OCIHandleFree((dvoid *) opthp, OCI_HTYPE_SODA_OPER_OPTIONS);
if (tempDochp)
(void) OCIHandleFree((dvoid *) tempDochp, OCI_HTYPE_SODA_DOCUMENT);
Example 3-23 Replacing a Particular Version of a Document Using SODA For C
To implement optimistic locking when replacing a document, you can specify both key and version, as in this example.
OCISodaDoc *dochp = NULL;
OCISodaDoc *tempDochp = NULL;
// Document content: JSON data.
char documentContent[30] = "{\"name\":\"Esmeralda\"}";
ub4 docFlags = OCI_DEFAULT;
OraText *key = NULL;
ub4 keyLen = 0;
OraText *content = NULL;
ub4 contentLen = 0;
OraText *version = NULL;
ub4 versionLen = 0;
boolean isReplaced = FALSE;
OCISodaOperationOptions *opthp;
OraText *inKey = "3C03C00FA3904FC2BF5182C424A2C6C1";
OraText *inVersion =
"BD0A8E86428FFD68A00FAE7833B41404637EE0A31791B36EC4C78A5782272448";
// Create a temporary replacement document, which has documentContent
// as its content.
rc = OCISodaDocCreate(envhp,
documentContent,
(ub4) strlen(documentContent),
docFlags,
&dochp,
errhp,
OCI_DEFAULT);
if (rc != OCI_SUCCESS)
{
printf("OCISodaDocCreate failed\n");
goto finally;
}
// Allocate an empty operation-options handle.
rc = OCIHandleAlloc((void *) envhp, (void **)&opthp,
OCI_HTYPE_SODA_OPER_OPTIONS, (size_t) 0,
(dvoid **) 0);
if (rc != OCI_SUCCESS) goto finally;
// Set the key of the document we want to replace on the
// operation-options handle.
rc = OCIAttrSet(opthp,
OCI_HTYPE_SODA_OPER_OPTIONS,
inKey,
strlen(inKey),
OCI_ATTR_SODA_KEY,
errhp);
if (rc != OCI_SUCCESS) goto finally;
// Set the version of the document we want to replace on the
// operation-options handle.
rc = OCIAttrSet(opthp,
OCI_HTYPE_SODA_OPER_OPTIONS,
inVersion,
strlen(inVersion),
OCI_ATTR_SODA_VERSION,
errhp);
if (rc != OCI_SUCCESS) goto finally;
// OCISodaReplOneAndGet returns the result document as dochp, so
// before calling it we save a pointer, tempDochp, to the handle that
// was returned by OCISodaDocCreate. Later we free tempDochp.
tempDochp = dochp;
// Replace the document that has the key and version set in the
// operation-options handle with the new, replacement document pointed
// to by dochp, and get back the result document.
//
// The result document has the content of the replacement document,
// but it also has all of the other document components, automatically
// populated by SODA when the replacement document was inserted.
rc = OCISodaReplOneAndGet(svchp,
collhp,
opthp,
&dochp,
&isReplaced,
errhp,
OCI_SODA_ATOMIC_COMMIT);
if (rc != OCI_SUCCESS) goto finally;
if (isReplaced) printf ("Document was replaced.\n");
// Get and print the components of found document after replacement.
rc = OCIAttrGet((dvoid *) dochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &key,
&keyLen,
OCI_ATTR_SODA_KEY,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Key: %.*s\n", keyLen, key);
rc = OCIAttrGet((dvoid *) dochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &version,
&versionLen,
OCI_ATTR_SODA_VERSION,
errhp);
if (rc != OCI_SUCCESS) goto finally;
printf("Version: %.*s\n", versionLen, version);
finally:
// Release the session and free all handles, including handle of the
// temporary document.
if (collhp)
(void) OCIHandleFree((dvoid *) collhp, OCI_HTYPE_SODA_COLLECTION);
if (dochp)
(void) OCIHandleFree((dvoid *) dochp, OCI_HTYPE_SODA_DOCUMENT);
if (opthp)
(void) OCIHandleFree((dvoid *) opthp, OCI_HTYPE_SODA_OPER_OPTIONS);
if (tempDochp)
(void) OCIHandleFree((dvoid *) tempDochp, OCI_HTYPE_SODA_DOCUMENT);
Example 3-24 Locking a Document For Update (Replacement) Using SODA For C
This example is similar to Example 3-22, but it uses persistent locking: it locks the document until
the replacement operation is finished. It acquires a lock on the document by setting the
lock attribute on the operation options handle to TRUE.
OCISodaDoc *dochp = NULL;
OCISodaDoc *tempDochp = NULL;
OCISodaDoc *foundDochp = NULL;
char documentContent[30] = "{\"name\":\"LiLing\"}";
ub4 docFlags = OCI_DEFAULT;
boolean doLock;
boolean isReplaced = FALSE;
OCISodaOperationOptions *opthp;
OraText *inKey = "3C03C00FA3904FC2BF5182C424A2C6C1";
// Allocate an empty operation-options handle.
rc = OCIHandleAlloc((void *) envhp, (void **)&opthp,
OCI_HTYPE_SODA_OPER_OPTIONS, (size_t) 0,
(dvoid **) 0);
if (rc != OCI_SUCCESS) goto finally;
// Set the document-key attribute on the operation-options handle.
rc = OCIAttrSet(opthp, OCI_HTYPE_SODA_OPER_OPTIONS,
inKey, strlen(inKey), OCI_ATTR_SODA_KEY, errhp);
if (rc != OCI_SUCCESS) goto finally;
// Set the lock attribute to TRUE on the operation-options handle to obtain
// the document with lock.
doLock = TRUE;
rc = OCIAttrSet(opthp, OCI_HTYPE_SODA_OPER_OPTIONS, &doLock, 0,
OCI_ATTR_SODA_LOCK, errhp);
if (rc != OCI_SUCCESS) goto finally;
// Find the document with the key, by way of the operation-options handle
// that has key and lock attributes set.
// collhp is an OCISodaColl pointer, representing an open collection.
rc = OCISodaFindOne(svchp, collhp, opthp, findFlags,
&foundDochp, errhp, OCI_DEFAULT);
if (rc != OCI_SUCCESS) goto finally;
// The document is now locked for update - other users cannot update it
// until it is released.
// Create a temporary replacement document, with documentContent as its content.
rc = OCISodaDocCreate(envhp, documentContent, (ub4) strlen(documentContent),
docFlags, &dochp, errhp, OCI_DEFAULT);
if (rc != OCI_SUCCESS)
{
printf("OCISodaDocCreate failed\n");
goto finally;
}
// OCISodaReplOneAndGet returns the result document as dochp, so
// before calling it we save a pointer, tempDochp, to the handle that
// was returned by OCISodaDocCreate.
tempDochp = dochp;
// Replace the document that has the key set in the operation-options
// handle with the replacement document, dochp, and get back result document.
//
// The result document has the content of the replacement document,
// but it also has all of the other document components, automatically
// populated by SODA when the replacement document was inserted.
rc = OCISodaReplOneAndGet(svchp, collhp, opthp, &dochp,
&isReplaced, errhp, OCI_SODA_ATOMIC_COMMIT);
if (rc != OCI_SUCCESS) goto finally;
if (isReplaced) printf ("Document was replaced.\n");
// OCI_SODA_ATOMIC_COMMIT commits the transaction, thereby releasing the lock
// obtained on the document.
finally:
// Free all handles.
if (dochp)
(void) OCIHandleFree((dvoid *) dochp, OCI_HTYPE_SODA_DOCUMENT);
if (opthp)
(void) OCIHandleFree((dvoid *) opthp, OCI_HTYPE_SODA_OPER_OPTIONS);
if (tempDochp)
(void) OCIHandleFree((dvoid *) tempDochp, OCI_HTYPE_SODA_DOCUMENT);
if (foundDochp)
(void) OCIHandleFree((dvoid *) foundDochp, OCI_HTYPE_SODA_DOCUMENT);
3.14 Removing Documents from a Collection with SODA for C
You can remove documents from a collection using functions
OCISodaRemove() and OCISodaRemoveOneWithKey().
To remove one or more documents you can use function OCISodaRemove(),
passing it an operation-options handle. If you want to remove a single document, specified
by its key, then you can alternatively use convenience function
OCISodaRemoveOneWithKey(). It does not require an operation-options
handle — you pass it the key directly.
Whichever document-removal function you use, the function returns the number of documents removed as an out parameter.
See Also:
-
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaRemove() -
Oracle Call Interface Programmer's Guide for information about OCI function
OCISodaRemoveOneWithKey()
Example 3-25 Removing a Document from a Collection Using a Document Key
This example removes the document with UUID key "E914016C41174F6CBF7C877C7F9EB4C2". The use of mode parameter OCI_SODA_ATOMIC_COMMIT ensures that the removal and any other outstanding operations are committed.
OraText *inKey = "E914016C41174F6CBF7C877C7F9EB4C2";
ub8 removeCount = 0;
OCISodaOperationOptions *opthp;
// Allocate an empty operation-options handle.
rc = OCIHandleAlloc((void *) envhp, (void **)&opthp,
OCI_HTYPE_SODA_OPER_OPTIONS, (size_t) 0,
(dvoid **) 0);
if (rc != OCI_SUCCESS) goto finally;
// Set the document-key attribute on the operation-options handle.
rc = OCIAttrSet(opthp,
OCI_HTYPE_SODA_OPER_OPTIONS,
inKey,
strlen(inKey),
OCI_ATTR_SODA_KEY,
errhp);
if (rc != OCI_SUCCESS) goto finally;
// Remove the document that has the key set in the
// operation-options handle.
rc = OCISodaRemove(svchp,
collhp,
opthp,
&removeCount,
errhp,
OCI_SODA_ATOMIC_COMMIT);
if (rc != OCI_SUCCESS) goto finally;
if (removeCount > 0)
printf("Successfully removed document.\n");
else
printf("Document with specified key was not found.\n");
finally:
// Free all handles.
if (collhp)
(void) OCIHandleFree((dvoid *) collhp, OCI_HTYPE_SODA_COLLECTION);
if (opthp)
(void) OCIHandleFree((dvoid *) opthp, OCI_HTYPE_SODA_OPER_OPTIONS);
Example 3-26 Removing a Particular Version of a Document
This example uses function OCISodaRemove() with an operation-options handle that specifies the version, as well as the key, of the document to remove. This is useful for implementing optimistic locking, for write operations.
When specifying the document version you typically specify the key as well. But you can specify the version along with a filter, provided the filter specifies at most one document in the collection.
ub8 removeCount = 0;
OraText *inKey = "0C6132FC780D4F16BF9561FC9E2B4F98";
OraText *inVersion =
"7CCEF2F54035DE9A9D64653645DBEF7E61B92142F2E41B3F6144262A5F7BC054";
OCISodaOperationOptions *opthp;
// Allocate an empty operation-options handle,
rc = OCIHandleAlloc((void *) envhp, (void **)&opthp,
OCI_HTYPE_SODA_OPER_OPTIONS, (size_t) 0,
(dvoid **) 0);
if (rc != OCI_SUCCESS) goto finally;
// Set the document-key attribute on the operation-options handle.
rc = OCIAttrSet(opthp,
OCI_HTYPE_SODA_OPER_OPTIONS,
inKey,
strlen(inKey),
OCI_ATTR_SODA_KEY,
errhp);
if (rc != OCI_SUCCESS) goto finally;
// Set the document-version attribute on the operation-options handle.
rc = OCIAttrSet(opthp,
OCI_HTYPE_SODA_OPER_OPTIONS,
inVersion,
strlen(inVersion),
OCI_ATTR_SODA_VERSION,
errhp);
if (rc != OCI_SUCCESS) goto finally;
// Remove document that has the key and version set in the
// operation-options handle.
rc = OCISodaRemove(svchp,
collhp,
opthp,
&removeCount,
errhp,
OCI_SODA_ATOMIC_COMMIT);
if (rc != OCI_SUCCESS) goto finally;
if (removeCount > 0)
printf("Successfully removed document.\n");
else
printf("Document with specified key was not found.\n");
finally:
// Free all handles.
if (collhp)
(void) OCIHandleFree((dvoid *) collhp, OCI_HTYPE_SODA_COLLECTION);
if (opthp)
(void) OCIHandleFree((dvoid *) opthp, OCI_HTYPE_SODA_OPER_OPTIONS);
Example 3-27 Removing Documents from a Collection Using Document Keys
This example uses function OCISodaOperKeysSet() to set operation-options handle attributes for key and key length for two documents. It then invokes function OCISodaRemove() to remove the documents that have those keys. Function OCISodaOperKeysSet() accepts an array of keys, an array of the corresponding key lengths, and the number of keys as arguments. (You cannot set multiple keys and their lengths using standard function OCIAttrSet().)
Note:
If you use function OCIAttrSet() to set attribute OCI_ATTR_SODA_KEY on an operation-options handle, and you also use function OCISodaOperKeysSet() to set multiple keys on the same handle, then only the latest of the two settings takes effect. The effect of the first function invoked is overridden by the effect of the second.
OraText *keys[2] = {"ACF8C4BDA3E44F4CBF802C9708D00C10",
"787B22133B254F0CBF2DB9975E277913"};
ub4 keyLengths[2];
ub8 removeCount = 0;
int i = 0;
OCISodaOperationOptions *opthp;
// Allocate an empty operation-options handle.
rc = OCIHandleAlloc((void *) envhp, (void **)&opthp,
OCI_HTYPE_SODA_OPER_OPTIONS, (size_t) 0,
(dvoid **) 0);
if (rc != OCI_SUCCESS) goto finally;
// Fill array of key lengths.
for(i=0; i<2; i++)
keyLengths[i] = strlen(keys[i]);
// Set keys and key lengths on operation-options handle.
rc = OCISodaOperKeysSet(opthp,
keys,
keyLengths,
2,
errhp,
OCI_DEFAULT);
if (rc != OCI_SUCCESS) goto finally;
// Remove documents matching the keys in the operation-options handle.
rc = OCISodaRemove(svchp,
collhp,
opthp,
&removeCount,
errhp,
OCI_SODA_ATOMIC_COMMIT);
if (rc != OCI_SUCCESS) goto finally;
if (removeCount > 0)
printf("Successfully removed %d documents.\n", removeCount);
else
printf("Document with specified keys were not found.\n");
finally:
// Free all handles.
if (opthp)
(void) OCIHandleFree((dvoid *) opthp, OCI_HTYPE_SODA_OPER_OPTIONS);
if (collhp)
(void) OCIHandleFree((dvoid *) collhp, OCI_HTYPE_SODA_COLLECTION);
Example 3-28 Removing JSON Documents from a Collection Using a Filter
This example uses a filter to remove the JSON documents whose greeting field has value "hello". It then prints the number of documents removed.
ub8 removeCount = 0;
OraText *filter = "{\"greeting\" : \"hello\"}";
OCISodaOperationOptions *opthp;
// Allocate an empty operation-options handle.
rc = OCIHandleAlloc((void *) envhp, (void **)&opthp,
OCI_HTYPE_SODA_OPER_OPTIONS, (size_t) 0,
(dvoid **) 0);
if (rc != OCI_SUCCESS) goto finally;
// Set the filter (query-by-example, or QBE) on the
// operation-options handle.
rc = OCIAttrSet(opthp,
OCI_HTYPE_SODA_OPER_OPTIONS,
filter,
strlen(filter),
OCI_ATTR_SODA_FILTER,
errhp);
if (rc != OCI_SUCCESS) goto finally;
// Remove documents matching the filter (QBE) set in
// operation-options handle.
rc = OCISodaRemove(svchp,
collhp,
opthp,
&removeCount,
errhp,
OCI_SODA_ATOMIC_COMMIT);
if (rc != OCI_SUCCESS) goto finally;
if (removeCount > 0)
printf("Successfully removed %d documents.\n", removeCount);
else
printf("No documents matching the filter were found.\n");
finally:
//Free all handles.
if (collhp)
(void) OCIHandleFree((dvoid *) collhp, OCI_HTYPE_SODA_COLLECTION);
if (opthp)
(void) OCIHandleFree((dvoid *) opthp, OCI_HTYPE_SODA_OPER_OPTIONS);
Related Topics
Parent topic: Using SODA for C
3.15 Truncating a Collection (Removing All Documents) with SODA for C
You can empty, or truncate, a collection, which means remove all of its
documents, using function OCISodaCollTruncate().
The function returns OCI_SUCCESS if successful.
Example 3-29 Truncating a Collection (Removing All Documents)
OCISvcCtx *svchp;
OCIError *errhp;
OCISodaColl *collhp;
sword rc;
rc = OCISodaCollTruncate(svchp,
collhp,
errhp,
OCI_SODA_ATOMIC_COMMIT);
if (rc == OCI_SUCCESS)
printf("Successfully truncated the collection.\n");Related Topics
Parent topic: Using SODA for C
3.16 Indexing the Documents in a Collection with SODA for C
Indexing can improve the performance of QBEs. To index the documents in a SODA collection, use function OCISodaIndexCreate(), passing it a textual JSON index specification. This can specify support for B-tree, spatial, full-text, and ad hoc indexing, and it can specify support for a JSON data guide.
-
A B-tree index is used to index particular scalar JSON fields.
-
An Oracle Spatial and Graph index is used to index GeoJSON (spatial) data.
-
A JSON search index can improve the performance of:
-
QBEs that you might not anticipate or use regularly — it is a general purpose index.
-
QBEs that use operator
$contains— full-text search.
-
-
A JSON search index can also provide persistent recording and automatic updating of JSON data-guide information.
If a JSON search index is defined, and if a B-tree index or a spatial index applies to a given QBE, the B-tree or spatial index is generally used for that QBE, in preference to the (more general) search index.
The invocation of function OCISodaIndexCreate() is the same for each kind of index you create. The only difference is the index specification that is passed to the function as an argument.
You drop an index on a SODA collection using function OCISodaIndexDrop(), passing it the index name.
You can obtain an index specification or all index specifications for a
collection, using function OCISodaIndexGet() or
OCISodaIndexList(), respectively.
For function OCISodaIndexGet() you provide the index, and
optionally the relevant database schema name, as arguments. (The values used for the schema and
index names are identifiers in the data dictionary. In particular, they must follow the same
letter case, so if they were created in SQL without using any double quotation marks then they
must be uppercase.)
See Also:
-
OCISodaIndexCreate() in Oracle Call Interface Programmer's Guide
-
OCISodaIndexDrop() in Oracle Call Interface Programmer's Guide
-
OCISodaIndexGet() in Oracle Call Interface Programmer's Guide
-
OCISodaIndexList() in Oracle Call Interface Programmer's Guide
-
Overview of SODA Indexing in Oracle Database Introduction to Simple Oracle Document Access (SODA)
-
SODA Index Specifications (Reference) in Oracle Database Introduction to Simple Oracle Document Access (SODA)
-
JSON Search Index for Ad Hoc Queries and Full-Text Search in Oracle Database JSON Developer’s Guide
-
Persistent Data-Guide Information: Part of a JSON Search Index Oracle Database JSON Developer’s Guide
-
Using GeoJSON Geographic Data in Oracle Database JSON Developer’s Guide
Example 3-30 Creating a B-Tree Index for a JSON Field with SODA for C
This example creates a B-tree non-unique index for numeric field address.zip of the JSON documents in a collection that has handle collhp. A B-tree index specification can be recognized by the presence of field fields.
// Index specification for B-tree index on field address.zip.
OraText *indexSpec = "{\"name\" : \"ZIPCODE_IDX\", \
\"fields\" : [{\"path\" : \"address.zip\", \
\"datatype\" : \"number\", \
\"order\" : \"asc\"}]}";
// Create the index.
rc = OCISodaIndexCreate(svchp, collhp, indexSpec, strlen(indexSpec), errhp, OCI_DEFAULT);
Example 3-31 Creating a JSON Search Index with SODA for C
This example indexes the documents in a collection that has handle collhp for ad hoc queries and full-text search (QBEs that use operator $contains), and it automatically accumulates and updates data-guide information about your JSON documents (aggregate structural and type information). The index specification has only field name (no field fields).
// Index specification for JSON search index.
OraText *indexSpec = "{\"name\" : \"SEARCH_AND_DATA_GUIDE_IDX\"}";
// Create the index.
rc = OCISodaIndexCreate(svchp, collhp, indexSpec, strlen(indexSpec), errhp, OCI_DEFAULT);
The simple index specification it uses is equivalent to this one, which makes explicit the default values:
{"name" : "SEARCH_AND_DATA_GUIDE_IDX",
"dataguide" : "on",
"search_on" : "text_value"}If you instead wanted only ad hoc indexing then you would explicitly specify a value of "off" for field dataguide. If you instead wanted only data-guide support then you would explicitly specify a value of "none" for field search_on.
Note:
To create a data
guide-enabled JSON search index, or to data guide-enable an existing JSON search
index, you need database privilege CTXAPP and Oracle Database
Release 12c (12.2.0.1) or later.
Example 3-32 Dropping an Index with SODA for C
To drop an index on a SODA collection, just pass the index name to function OCISodaIndexDrop(). This example drops index ZIPCODE_IDX.
boolean isDropped = FALSE;
// Drop the index named ZIPCODE_IDX.
rc = OCISodaIndexDrop(svchp,
"ZIPCODE_IDX",
strlen("ZIPCODE_IDX"),
&isDropped,
errhp,
OCI_DEFAULT);
printf ("isDropped %d\n", isDropped);
Example 3-33 Getting an Index Specification with SODA for C
This example uses function OCISodaIndexGet() to get the
specification used to define index idx, in the database schema that is
currently connected, for the documents in SODA collection collhp.
static void printIndex(OCISvcCtx *svchp, OCIEnv *envhp, OCISodaColl *collhp,
oratext *idx, ub4 idxlen, ub4 flags, OCIError *err
{
OCIString *spec = NULL;
sword rc;
printf("Describing index: %.*s\n", idxlen, idx);
rc = OCISodaIndexGet(svchp, collhp, idx, idxlen, NULL, 0,
flags, &spec, errhp, mode);
if (rc != OCI_SUCCESS) return;
if (spec)
{
oratext *str = OCIStringPtr(envhp, spec);
ub4 slen = OCIStringSize(envhp, spec);
printf("Index specification: %.*s\n", slen, str);
}
}Example 3-34 Getting All Index Specifications For a Collection with SODA for C
This example uses function OCISodaIndexList() to retrieve, in
variable specs, all index specifications defined for the documents in
collection collhp. It then prints them, along with their count (obtained using
function OCICollSize()).
static
void printIndexes(OCISvcCtx *svchp, OCIEnv *envhp, OCISodaColl *collhp,
ub4 flags, OCIError *errhp, ub4 mode)
{
OCIColl *specs = NULL;
sb4 ndocs;
sword rc;
printf("Listing indexes\n");
rc = OCISodaIndexList(svchp, collhp, OCI_SODA_AS_AL32UTF8, &specs, errhp, 0);
if (rc != OCI_SUCCESS) return;
rc = OCICollSize(envhp, errhp, specs, &ndocs);
if (rc != OCI_SUCCESS) return;
printf("No. of indexes: %d\n", ndocs);
for (int i = 0 ; i < ndocs ; i++)
{
void *specstr;
boolean exists = FALSE;
rc = OCICollGetElem(envhp, errhp, (const OCIColl *)specs, i, &exists,
(void **) &specstr, (void **) NULL);
if (rc != OCI_SUCCESS) return;
if (exists)
{
OCIString *item = *(OCIString **)specstr;
oratext *spec = NULL;
ub4 specLen = 0;
spec = OCIStringPtr(envhp, item);
specLen = OCIStringSize(envhp, item);
printf("Index %d:\n%.*s\n", i, specLen, spec);
}
}
}Parent topic: Using SODA for C
3.17 Getting a Data Guide for a Collection with SODA for C
You use function OCISodaDataGuideGet() or
OCISodaDataGuideGetWithOpts() to obtain a data guide for a collection. A
data guide is a JSON document that summarizes the structural and type information
of the JSON documents in the collection. It records metadata about the fields used in those
documents.
Note:
SODA for C support for JSON data guide was added in Oracle Database 18.3. You need that database release or later to use this SODA feature.
There are two alternative ways to create a data guide for a collection:
-
Use function
OCISodaDataGuideGetWithOpts()together with operation-options handle attributeOCI_ATTR_SODA_SAMPLE_PCTor a query-by-example (QBE) filtering operation. This creates a data guide dynamically from scratch, for only the documents selected by your query. You can thus limit the set of documents on which the data guide is based. Example 3-35 illustrates this.(This method corresponds to using SQL function
json_dataguide.) -
Use function
OCISodaDataGuideGet(). This always creates a data guide based on all documents in the collection. Example 3-36 illustrates this.This method makes use of persistent data-guide information that is stored as part of a JSON search index, so before you can use this method you must first create a data guide-enabled JSON search index on the collection. Example 3-31 shows how to do that. The data-guide information in the index is persistent, and is updated automatically as new JSON content is added.
(This method corresponds to using PL/SQL function
get_index_dataguide.)
The index-based function, OCISodaDataGuideGet(), incurs an
ongoing cost of updating relevant data persistently: document writes (creation and updating)
entail index updates. But because data-guide information is readily available in the index,
it need not be gathered from scratch when generating the data-guide document.
Because function OCISodaDataGuideGetWithOpts() starts from
scratch each time, a typical use of it involves applying the method to only a random sample
of documents or to only the documents that satisfy some filter (QBE). Example 3-35 illustrates using a random sample of documents.
Example 3-35 Creating a Data Guide Dynamically with SODA for C
This example uses function OCISodaDataGuideGetWithOpts(),
together with operation-options handle attribute
OCI_ATTR_SODA_SAMPLE_PCTFoot 2 to obtain a data guide for a
random sample of the documents in collection collhp. The percent chance for
any given document to be included in the sample is 40% (argument value
40).
The example pretty-prints the content of the data-guide document in the flat format. Finally, it frees the temporary LOB used for the data-guide document.
You use attribute OCI_ATTR_SODA_SAMPLE_PCT only for
read operations — it is ignored for write operations. Creating a dynamic data guide
is a typical use case for OCI_ATTR_SODA_SAMPLE_PCT. You can also use it to
obtain a cursor.
Another common way to limit the documents represented by a dynamically created
data guide, besides using a random sample, is to use a query-by-example (QBE) filtering
operation in place of OCI_ATTR_SODA_SAMPLE_PCT.
OCISodaDoc *dgdochp = NULL;
OCISodaOperationOptions *opthp = NULL;
oratext *content;
ub4 contentLen;
double sample_pct = 40.0;
rc = OCIHandleAlloc((void *) envhp, (void **)&opt,
OCI_HTYPE_SODA_OPER_OPTIONS, (size_t) 0,
(dvoid **) 0
if (rc != OCI_SUCCESS) goto finally;
// Set the sample size (40%) on the operation-options handle.
rc = OCIAttrSet(opr, OCI_HTYPE_SODA_OPER_OPTIONS, &sample_pct, 0,
OCI_ATTR_SODA_SAMPLE_PCT, errhp)
if (rc != OCI_SUCCESS) goto finally;
// Create a data guide dynamically, from scratch.
// dgdochp is the handle for the data-guide document.
rc = OCISodaDataGuideGetWithOpts(svchp, collhp, opthp,
OCI_SODA_DG_FMT_HIERARCHICAL,
OCI_SODA_DATAGUIDE_PRETTY,
OCI_SODA_AS_AL32UTF8,
&dgdochp, errhp, OCI_DEFAULT));
if (rc != OCI_SUCCESS) goto finally;
rc = OCIAttrGet((dvoid *)dgdochp, OCI_HTYPE_SODA_DOCUMENT,
(dvoid *)&content, &contentLen,
OCI_ATTR_SODA_CONTENT, errhp)
if (rc != OCI_SUCCESS) goto finally;
printf("Dataguide: %.*s\n", contentLen, content);
finally:
// Free all handles.
if (dgdochp)
(void) OCIHandleFree((dvoid *) dgdochp, OCI_HTYPE_SODA_DOCUMENT);
if (opthp)
(void) OCIHandleFree((dvoid *) opthp, OCI_HTYPE_SODA_OPER_OPTIONS);See Also:
OCISodaDataGuideGetWithOpts() in Oracle Call Interface Programmer's Guide
Example 3-36 Creating a Data Guide Using a JSON Search Index with SODA for C
This example gets a data guide for a collection with collection handle collhp, using function OCISodaDataGuideGet(). It then prints the content of the data-guide document.
OCISodaDoc *dgdochp = NULL;
OraText *content = NULL;
ub4 contentLen = 0;
// Create a data guide based on a JSON search index defined on the
// collection. dgdochp is the handle for the data-guide document.
rc = OCISodaDataGuideGet(svchp,
collhp,
OCI_DEFAULT,
&dgdochp,
errhp,
OCI_DEFAULT);
if (rc != OCI_SUCCESS) goto finally;
rc = OCIAttrGet((dvoid *) dgdochp,
OCI_HTYPE_SODA_DOCUMENT,
(dvoid *) &content,
&contentLen,
OCI_ATTR_SODA_CONTENT,
errhp);
if (rc != OCI_SUCCESS) goto finally;
// Print the content of the data-guide document.
printf("Data guide: %.*s \n", contentLen, content);
finally:
// Free all handles.
if (collhp)
(void) OCIHandleFree((dvoid *) collhp, OCI_HTYPE_SODA_COLLECTION);
if (dgdochp)
(void) OCIHandleFree((dvoid *) dgdochp, OCI_HTYPE_SODA_OPER_OPTIONS);
See Also:
OCISodaDataGuideGet() in Oracle Call Interface Programmer's Guide
Parent topic: Using SODA for C
3.18 Handling Transactions with SODA for C
You can handle individual read and write operations, or groups of them, as a database transaction.
You do this in either of these ways:
-
Use execution mode parameter
OCI_SODA_ATOMIC_COMMITwhen you invoke a SODA operation. If an operation is executed in this mode and it completes successfully then the current transaction is committed after completion.As is usual for a commit, this commits all outstanding changes, not just changes made by the SODA operation. However, if the operation fails then only changes made by the SODA operation are rolled back; any uncommitted changes made prior to invocation of the SODA operation are not rolled back.
-
Use function
OCITransCommit()orOCITransRollback(), to commit or roll back, respectively, the current transaction. These are standard Oracle Call Interface (OCI) functions; they are not SODA-specific.
SODA operations of creating and dropping a collection do not automatically commit before or after they perform their action. (This differs from the behavior of SQL DDL statements, which commit both before and after performing their action.)
One consequence of this is that, before a SODA collection can be dropped, any outstanding write operations to it must be committed or rolled back. This is because function OCISodaCollDrop() does not itself commit before it performs its action. In this, its behavior differs from that of a SQL DROP TABLE statement.
Related Topics
See Also:
-
Oracle Call Interface Programmer's Guide for information about execution mode parameter
OCI_SODA_ATOMIC_COMMIT -
Oracle Call Interface Programmer's Guide for information about Oracle Call Interface (OCI) support for transactions
-
Oracle Call Interface Programmer's Guide for information about OCI function
OCITransCommit() -
Oracle Call Interface Programmer's Guide for information about OCI function
OCITransRollback()
Parent topic: Using SODA for C
Footnote Legend
Footnote 1: The handle is freed here immediately, just as a reminder to free it when you are done with it (the same as any other handle). In practice you would make use of the handle in some way before freeing it.Footnote 2: The syntax here corresponds to that of the sample_clause of a SQL SELECT statement.