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.

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:

  1. Ensure that all of the prerequisites have been met for using SODA for C. See SODA for C Prerequisites.

  2. Grant database role SODA_APP to the database schema (user account) where you intend to store SODA collections. (Replace placeholder user here by a real account name.)

    GRANT SODA_APP TO user;
    
  3. Create a program file containing the C code in Example 3-1, but set variables usr, passwd, and connstr to values appropriate string values for your database account and instance.

  4. Compile the file and build an executable program from it as you would for any OCI program.

  5. 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-started then enter that at the command-line prompt:

    > soda-get-started

    If you want the program to drop the collection when done with it then pass the argument drop to 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_OBJECT as the mode parameter to function OCIEnvNlsCreate() 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:

Example 3-1 Getting Started Run-Through

This example code does the following:

  1. Creates an Oracle Call Interface (OCI) environment in object mode, allocates the error handle, and gets a session using OCISessionGet().

  2. Creates and opens a SODA document collection, using the default collection configuration (metadata).

  3. Creates a SODA document with some JSON content.

  4. Inserts the document into the collection.

  5. Gets the inserted document back. Its other components, besides the content, are generated automatically.

  6. Prints the unique document key, which is one of the components generated automatically.

  7. Finds the document in the collection, providing its key.

  8. Prints some of the document components: key, version, last-modified time stamp, creation time stamp, media type, and content.

  9. Optionally drops the collection, cleaning up the database table that is used to store the collection and its metadata.

  10. 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 these variables to strings with the appropriate user name and password.
  // (Be sure to replace the placeholders user and password used here.)
  OraText      usr[30] = user;
  OraText      passwd[30] = password;

  // Set variable connstr to a string value 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 *)user,
                   (ub4)strlen((char *)user),
                   (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 the value of variable collectionName, with default metadata
  rc = OCISodaCollCreate(svchp,
                         collectionName,
                         (ub4) strlen(collectionName),
                         &collhp,
                         errhp,
                         OCI_DEFAULT);

  if (rc != OCI_SUCCESS)
  {
    printf("OCISodaCollCreate failed\n");
    goto finally;
  }

  // Create a 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 auto-generated 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 *) errhp, OCI_HTYPE_SVCCTX);

  if (envhp) 
    (void ) OCIHandleFree((dvoid *) envhp, OCI_HTYPE_ENV);
  return rc;
}

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:

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);

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.

3.5 Discovering Existing Collections with SODA for C

To discover existing collections, use OCI functions OCISodaCollList() and OCISodaCollGetNext().

See Also:

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;

rc = OCISodaCollList(svchp,
                     startName,
                     (ub4) strlen(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);
}
while(1);

finally:
if (collectionCursor) OCIHandleFree((dvoid *) collectionCursor, (ub4)OCI_HTYPE_SODA_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.

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.

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(), or OCISodaDocCreateWithKeyAndMType().

    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 to NULL.

  • By invoking function OCIHandleAlloc() with handle type OCI_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 at least the content component.

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

OCI_ATTR_SODA_KEY

The unique key for the document.

OCI_ATTR_SODA_CREATE_TIMESTAMP

The creation time stamp for the document.

OCI_ATTR_SODA_LASTMOD_TIMESTAMP

The last-modified time stamp for the document.

OCI_ATTR_SODA_MEDIA_TYPE

The media type for the document.

OCI_ATTR_SODA_VERSION

The document version.

OCI_ATTR_SODA_CONTENT

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", for OCI_ATTR_SODA_MEDIA_TYPE, if the media type was not provided to the creation function

  • NULL for other components

See Also:

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.

OCISodaDoc *dochp =  NULL;
OraText    *documentContent = "{\"name\":\"Alexander\"}";
ub4         docFlags = OCI_DEFAULT;

rc = OCISodaDocCreate(envhp,
                      documentContent,
                      (ub4) strlen(documentContent),
                      docFlags,
                      &dochp,
                      errhp,
                      OCI_DEFAULT)

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 OCISodaDocCreate(): 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 OCISodaDocCreate().
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, which contains the generated document components, such as the key, and which does not contain the content of the inserted document.

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:

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 Finding Documents in Collections with SODA for C

To find the document in a collection that has a given key, use OCI function OCISodaFindOneWithKey(). Each document has a unique key.

See Also:

Oracle Call Interface Programmer's Guide for information about OCI function OCISodaFindOneWithKey()

Example 3-12 Finding the Single Document That Has a Given Document Key

This example uses function OCISodaFindOneWithKey() to find the single document whose key is "key1". It then uses function OCIAttrGet() to retrieve various document components and prints them. Finally, it frees the document handle that was allocated.

If no document is found for the supplied key then function OCISodaFindOneWithKey() returns status code OCI_NO_DATA and the document returned (foundDochp in this example) is NULL.

sword rc = OCI_SUCCESS;

// (For illustration, assign a value that could be autogenerated.)
OraText    *key = "FEE804F00B614F88BF357A695F4F73AB";
ub4         keyLen = strlen(key); 

OraText    *content;
ub4         contentLen = 0;
OraText    *createdOn;
ub4         createdOnLen = 0;
OraText    *lastModified;
ub4         lastModifiedLen = 0;
OraText    *version;
ub4         versionLen = 0;
OraText    *media;
ub4         mediaLen = 0;
OCISodaDoc *foundDochp = NULL;

rc = OCISodaFindOneWithKey(svchp,
                           collhp,
                           key,
                           keyLen,
                           findFlags,
                           &foundDochp,
                           errhp,
                           OCI_DEFAULT);

if (rc != OCI_SUCCESS)
{
  if (rc == OCI_NO_DATA)
  // OCI_NO_DATA return code means document was not found
  {
    printf ("Document with the supplied key not found in the collection\n");
  }
   goto finally;
}

// Print components of found document.
// (Skip the key component - it is the key used to find the document.)
// (For brevity we omit checking the return values of the OCIAttrGet calls here.)

OCIAttrGet((dvoid *)foundDochp,
           OCI_HTYPE_SODA_DOCUMENT,
           (dvoid *)&content,
           &contentLen,
           OCI_ATTR_SODA_CONTENT,
           errhp);
printf ("Content: %.*s\n", contentLen, content);

OCIAttrGet((dvoid *)foundDochp,
           OCI_HTYPE_SODA_DOCUMENT,
           (dvoid *)&createdOn,
           &createdOnLen,
           OCI_ATTR_SODA_CREATE_TIMESTAMP,
           errhp);
printf ("Created-on time stamp: %.*s\n", createdOnLen, createdOn);

OCIAttrGet((dvoid *)foundDochp,
           OCI_HTYPE_SODA_DOCUMENT,
           (dvoid *)&lastModified,
           &lastModifiedLen,
           OCI_ATTR_SODA_LASTMOD_TIMESTAMP,
           errhp);
printf ("Last-modified time stamp: %.*s\n", lastModifiedLen, lastModified);

OCIAttrGet((dvoid *)foundDochp,
           OCI_HTYPE_SODA_DOCUMENT,
           (dvoid *)&version,
           &versionLen,
           OCI_ATTR_SODA_VERSION,
           errhp);
printf ("Version: %.*s\n", versionLen, version);

OCIAttrGet((dvoid *)foundDochp,
           OCI_HTYPE_SODA_DOCUMENT,
           (dvoid *)&media,
           &mediaLen,
           OCI_ATTR_SODA_MEDIA_TYPE,
           errhp);
printf ("Media type: %.*s\n", mediaLen, media);

...

finally:
  if (foundDochp) OCIHandleFree((dvoid *) foundDochp, (ub4) OCI_HTYPE_SODA_DOCUMENT);

3.10 Replacing Documents in a Collection with SODA for C

To replace a document in a collection, given its key, use OCI function OCISodaReplOneWithKey() or OCISodaReplOneAndGetWithKey(). The latter also returns the new (result) document, so you can get its components.

In addition to replacing the content, the replacement functions update the values of the last-modified time stamp and the version (for a collection that has columns last-modified and version, which is the case by default). The replacement functions also replace the media type with the media type provided in the input document. Document replacement does not change the document key or the creation time stamp.

See Also:

Example 3-13 Replacing a Document in a Collection, Given Its Key, and Getting the Result Document

This example uses OCI function OCISodaReplOneAndGetWithKey() to replace a document in a collection, given its key, "myKey1" and get the result document. It uses function OCIAttrGet() to retrieve various document components, which it prints. The use of mode parameter OCI_SODA_ATOMIC_COMMIT ensures that the replacement and any other outstanding operations are committed.

sword rc = OCI_SUCCESS;

OraText    *key = "myKey1";
OraText    *documentContent = "{\"name\":\"Mark\"}";
ub4         docFlags = OCI_DEFAULT;
OCISodaDoc *dochp = NULL;
OCISodaDoc *origDochp = NULL;
boolean     isReplaced = FALSE;
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 custom key "myKey1"
rc = OCISodaDocCreateWithKey(envhp,
                             documentContent,
                             (ub4) strlen(documentContent),
                             key,
                             (ub4) strlen(key),
                             docFlags,
                             &dochp,
                             errhp,
                             OCI_DEFAULT);

if (rc != OCI_SUCCESS) goto finally;

// Assume that the collection pointed to by collhp has client-assigned keys, and
// assume that it has an existing document with key "myKey1".  Replace that
// document with the newly created document.
//
// Because OCISodaReplOneAndGetWithKey returns the result document as dochp, we
// first save the pointer to the original input document handle returned by the
// OCISodaDocCreateWithKey above as origDochp.  This lets us free the original
// document handle later.
 
origDochp = dochp;
rc = OCISodaReplOneAndGetWithKey(svchp,
                                 collhp,
                                 key,
                                 (ub4) strlen(key),
                                 &dochp,
                                 &isReplaced,
                                 errhp,
                                 OCI_SODA_ATOMIC_COMMIT);

if (rc != OCI_SUCCESS) goto finally;

// If isReplace is set to FALSE, no existing document with key "myKey1" was
// found in the collection.
if (isReplace == FALSE) goto finally;

// Print result document components.
// (For brevity we omit checking the return values of the OCIAttrGet calls here.)

// The key component is unchanged
OCIAttrGet((dvoid *)dochp,
           OCI_HTYPE_SODA_DOCUMENT,
           (dvoid *)&resultKey,
           &resultKeyLen,
           OCI_ATTR_SODA_KEY,
           errhp);
printf ("Key: %.*s\n", resultKeyLen, resultKey);

// The created-on time stamp is unchanged
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);

// The last-modified time stamp was updated
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);

// The version was updated
OCIAttrGet((dvoid *)dochp,
           OCI_HTYPE_SODA_DOCUMENT,
           (dvoid *)&resultVersion,
           &resultVersionLen,
           OCI_ATTR_SODA_VERSION,
           errhp);
printf ("Version: %.*s\n", resultVersionLen, resultVersion);

// The media type was updated with the media type of the document pointed to by
// dochp, which is the default media type, application/json.
OCIAttrGet((dvoid *)dochp,
           OCI_HTYPE_SODA_DOCUMENT,
           (dvoid *)&resultMedia,
           &resultMediaLen,
           OCI_ATTR_SODA_MEDIA_TYPE,
           errhp);
printf ("Media type: %.*s\n", resultMediaLen, resultMedia);

...

// Free the document handles
finally:
    
  if (origDochp) OCIHandleFree((dvoid *) origDochp, (ub4) OCI_HTYPE_SODA_DOCUMENT);

  if (dochp) OCIHandleFree((dvoid *) dochp, (ub4) OCI_HTYPE_SODA_DOCUMENT);

3.11 Removing Documents from a Collection with SODA for C

To remove a document from a collection, given its key, use OCI function OCISodaRemoveOneWithKey().

See Also:

Oracle Call Interface Programmer's Guide for information about OCI function OCISodaRemoveOneWithKey()

Example 3-14 Removing a Document from a Collection Using a Document Key

This example removes the document whose document key is "mykey1". The removal status, which is provided by out parameter isRemoved (TRUE if the document was removed; FALSE if not), is printed. The use of mode parameter OCI_SODA_ATOMIC_COMMIT ensures that the removal and any other outstanding operations are committed.

boolean isRemoved = FALSE;
rc = OCISodaRemoveOneWithKey(svchp,
                             collhp, 
                             "mykey1",
                             (ub4) strlen("mykey1"),
                             &isRemoved,
                             errhp, 
                             OCI_SODA_ATOMIC_COMMIT);
if ((rc == OCI_SUCCESS) && (isRemoved == TRUE))
  printf("Document removed\n");

3.12 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_COMMIT when 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 for the SODA operation are rolled back; any uncommitted changes made prior to invocation of the SODA operation are not rolled back.

  • Use function OCITransactionCommit() or OCITransactionRollback(), 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.

See Also: