Writing data by using solidDB® SA without SQL

Programmer Guide : solidDB® SA : Writing data by using solidDB® SA without SQL

Writing data by using solidDB^® SA without SQL

With solidDB^® SA, data is written using cursors.

For delete and update operations, after the cursor is created, a search is performed so that the cursor points to the row that is to be updated and deleted. For insert operations, after the cursor is created, the insertion row(s) are immediately written to the cursor. solidDB^® SA also enables passing several rows for insertion inside a single network message.

Performing insert operations

solidDB^® SA functions required for insert operations are listed in the table below.

After solidDB^® creates a cursor to a certain table, variables are bound to columns, rows are written to the cursor, and then the cursor is closed.

If you use SaArrayInsert to insert more than one row in a single message, then you must perform an explicit flush to send the rows to the database.

Steps	SA function(s)	Comment
1 Create a cursor	SaCursorCreate
2 Binding variables to cursor	SaCursorColData, SaCursorColDate, SaCursorColDateFormat, SaCursorColDFloat, SaCursorColDouble, SaCursorColDynData, SaCursorColDynstr, SaCursorColFloat, SaCursorColInt, SaCursorColLong, SaCursorColStr, SaCursorColTime, SaCursorColTimestamp
3 Open the cursor	SaCursorOpen
4 Write a row(s) to the cursor	SaArrayInsert for more than one row or SaCursorInsert for a single row	Perform this in a loop if necessary
5 Free the cursor	SaCursorFree
6 Flush the network message to the server	SaArrayFlush	Necessary only if using SaArrayInsert.

The following code sample excerpt demonstrates how to write four rows of data in a single network message using the SaArrayInsert function. In the code, a call to SaArrayFlush flushes all rows to the server so they are passed in the same network message.

scur = SaCursorCreate(scon, "SAEXAMPLE");

/* Bind variables to columns. */
SaCursorColInt(scur, "INTC", &intc);
SaCursorColStr(scur, "CHARC", &str);

/* Open the cursor. */
SaCursorOpen(scur);

/* Insert values to the table. The column values are taken
* from the user variables that are bound to columns.
*/
for (intc = 2; intc <= 5; intc++) {
switch (intc) {
    case 2:
      str = "B";
      break;
    case 3:
      str = "C";
      break;
    case 4:
      str = "D";
      break;
    case 5:
      str = "E";
      break;
    }
    SaArrayInsert(scur);
  }

/* Close the cursor. */
SaCursorFree(scur);

/* Flush the inserts to the server. */
SaArrayFlush(scon, NULL);

Performing update and delete operations

solidDB^® SA functions required for basic update and delete operations are listed in the table below.

After solidDB^® creates a cursor to a specific table, variables are bound to columns of the table, and the cursor is opened. Before the actual search begins, the constraints for finding the row for deletion are set. If there are more rows to be updated, each of the rows requires a separate fetch before they are updated or deleted. After the operation, the cursor is freed.

Steps	SA function(s)	Comment
1 Create a cursor	SaCursorCreate
2 Binding variables to cursor	SaCursorColData, SaCursorColDate, SaCursorColDateFormat, SaCursorColDFloat, SaCursorColDouble, SaCursorColDynData, SaCursorColDynstr, SaCursorColFloat, SaCursorColInt, SaCursorColLong, SaCursorColStr, SaCursorColTime, SaCursorColTimestamp
3 Open the cursor	SaCursorOpen
4 Set the search constraint for the row to be updated or deleted	SaCursorEqual, SaCursorAtleast, SaCursorAtmost
5 Start a search for the row to be updated or deleted	SaCursorSearch
6 Fetch the row to be updated or deleted	SaCursorNext
7 Perform actual update or delete	SaCursorUpdate or SaCursorDelete	For updates, the new values need to be in variables bound in step 2.
8 Free the cursor	SaCursorFree

The following code sample excerpt demonstrates how to update a row in a table using SaCursorUpdate. Note that in the code, the new values for the update are in variables which are bound to the columns of the table using SaCursorColInt and SaCursorColStr after the cursor is created.

scur = SaCursorCreate(scon, "SAEXAMPLE");

/* Bind variables to columns INTC and CHARC of test table. */
SaCursorColInt(scur, "INTC", &intc);
SaCursorColStr(scur, "CHARC", &str);

/* Open the cursor. */
SaCursorOpen(scur);

/* Set search constraint. */
str = "D"; SaCursorEqual(scur, "CHARC");

/* Start a search. */
SaCursorSearch(scur);

/* Fetch the column. */
SaCursorNext(scur);

/* Update the current row in the cursor. */
intc = 1000;
str = "D Updated";
SaCursorUpdate(scur);

/* Close the cursor. */
SaCursorFree(scur);