The Coding Notebook
Memorable coding moments of a software engineer
Using Associative Array with OCCI setDataBufferArray
In a recent project I had to connect from Node.js to Oracle and run some stored-procedure, I have used the node-oracle module to do that, and it worked quite nicely. The problem was that some of my stored-procedure used associative arrays as parameters, and node-oracle did not support this type of args, so how do you do this using occi?


Having zero knowledge about occi, and no real experience with cpp, this wasn't a straight forward task...
I have used [this](https://drive.google.com/file/d/1WzythtcXOE7hAfdKDwnUNZk8F2JMBNZl_QRKz1_KPf0mJ8AC4RODwaYpAh4Y/edit?usp=sharing) presentation to get some background and hints on occi.

## Let's Code
OK, let assume we have created a connection and now we create our statement and set the __out__ parameter:

```cpp
// Create env and connection
Environment *env = Environment::createEnvironment();
Connection *conn=env->createConnection("user", "pass", "db/tns");

// Create a new statement with PL/SQL that executes out SP
// first argument is NVARCHAR2 array and the second is the output
Statement *stmt = conn->createStatement("Begin TEST_PKG.sp_get_strings(:1, :2); End;");

// Set the out param, say it's a ref cursor
stmt->registerOutParam(2, oracle::occi::OCCICURSOR);
```

So far so good, next we need to set the vector param, the easy way is to use the `setVector` method on statement:
```cpp
// The setVector API
void setVector(Statement *stmt, unsigned int column, const vector &vect, const string &sqltype);

// Create some strings array
vector phonenos;
phonenos.push_back(“111-222-3333”);
phonenos.push_back(“447-555-2323”);
phonenos.push_back(“575-232-5885”);

// Set the vector param (Assume "PHONELIST" is some vector type in Oracle)
setVector(stmt, 1, phonenos, “SCOTT.PHONELIST”);
```

This should work well... almost... Note that `setVector` last param is the sqlType, which is the array type we defined in Oracle, `setVector` will NOT work if this array type is defined within a package, and in my case, it was. What do we do? we use `setDataBufferArray` instead, this is from Oracle docs:
```cpp
void setDataBufferArray(
// The first parameter is 1, the second is 2...
unsigned int paramIndex,

// Pointer to user-allocated buffer. It should have size*arraySize bytes in it
void *buffer,

// Type of the data that is provided (or retrieved) in the buffer
Type type,

// Maximum number of elements in the array
ub4 arraySize,

// Pointer to number of current elements in the array
ub4 *arrayLength,

// Size of the data buffer for each element
sb4 elementSize,

// Pointer to an array of lengths. elementLength[i] has the length of the ith element of the array.
ub2 *elementLength,

// Pointer to an array of indicators. An indicator for every buffer element.
sb2 *ind = NULL,

// Pointer to an array of return codes.
ub2 *rc = NULL);
```

And now in English:
**paramIndex** - The index of the parameter in the statement (in our case above: 1)

**buffer** - A pointer to a memory chuck you should allocate where the array you want to pass is.
Each "cell" in the array should have the same bytes size. You can think of "buffer" as a "byte matrix" (as opposed to jagged array). This is most relevant to strings array where each element has different length.
For example assume we have this array we want to pass: `["Hi", "you", "there"]`, a buffer like this would be invalid: `{Hi\0you\0there\0}`
The 1st cell is 3 bytes, the 2nd is 4 bytes and the last is 6 bytes.
A correct buffer would be: `{Hi\0...you\0..there\0}`, here, each "cell" is 6 bytes, while each **element** has different size.

**type** - The oracle DB type of the array elements. [Here](http://docs.oracle.com/cd/A91202_01/901_doc/appdev.901/a89860/cci04typ.htm#1003747) you can find a good reference.
NOTE that only types of the form `OCCI_SQLT_xxx` are permitted(!)

**arraySize** - The number of "cells" you have allocated
**arrayLength** - Pointer to the number of "elements" currently in the array. You can allocate array with 100 cells but put only 10 elements in it, so `arraySize` is 100 while `arrayLength` is 10 (this is relevant if you reuse the allocated memory chunk).

**elementSize** - Remember we said each "cell" should have the same size? `elementSize` should be that size.
**elementLength** - A pointer to array where each element holds the actual "element" size of the array.
In our example above `{Hi\0...you\0..there\0}`:
```cpp
elementSize = 6
elementLength = [3, 4, 6]
```

**ind & rc** - I ignored and just passed `NULL`

Here is an example of building the buffer when we get a string array:
```cpp
// In our case "arr" is v8::Array but it could be any other array
// Find the longest string, this is necessary in order to create a buffer later (need fixed-sized cells).
// Of course this can be optimized if you know your strings would not be longer than X, so you can
// use X+1 (for \0) as the longestString. This is a memory/performance trade-off.
int longestString = 0;
for(unsigned int i = 0; i < arr->Length(); i++) {
Local currVal = arr->Get(i);
if (currVal->ToString()->Utf8Length() > longestString)
longestString = currVal->ToString()->Utf8Length();
}

// Add 1 for '\0'
++longestString;

// Create a long char* that will hold the entire array, it is important to create a FIXED SIZE array,
// meaning all strings have the same allocated length.
char* strArr = new char[arr->Length() * longestString];
ub2* elementLength = new ub2[arr->Length()];

// loop thru the arr and copy the strings into the strArr
int bytesWritten = 0;
for(unsigned int i = 0; i < arr->Length(); i++) {
Local currVal = arr->Get(i);
String::Utf8Value utfStr(currVal);

// Copy this string onto the strArr (we put \0 in the beginning as this is what strcat expects).
strArr[bytesWritten] = '\0';
strncat(strArr + bytesWritten, *utfStr, longestString);
bytesWritten += longestString;

// Set the length of this element, add +1 for the '\0'
elementLength[i] = utfStr.length() + 1;
}

void* buffer = strArr;
oracle::occi::Type elementsType = oracle::occi::OCCI_SQLT_STR;
ub4 arrayLength = arr->Length();
sb4 elementsSize = longestString;

// Set buffer on statement
stmt->setDataBufferArray(1, buffer, elementsType,
arrayLength, arrayLength, elementsSize,
elementLength, NULL, NULL);
```

Another example in case we have a Number array (Number type is OCCI_SQLT_NUM and is unsigned char[21])

```cpp
// In our case "arr" is v8::Array but it could be any other array
// Allocate memory for the numbers array, Number in Oracle is 21 bytes
unsigned char* numArr = new unsigned char[arr->Length() * 21];
ub2* elementLength = new ub2[arr->Length()];

for(unsigned int i = 0; i < arr->Length(); i++) {
Local currVal = arr->Get(i);

// Make sure the number is within Oracle number limits
double d = currVal->ToNumber()->Value();
if (d > 9.99999999999999999999999999999999999999*std::pow(10, 125) ||
d < -9.99999999999999999999999999999999999999*std::pow(10, 125)) {
// Return error or whatever
return;
}

// Convert the JS number into Oracle Number and get its bytes representation
oracle::occi::Number n = d;
oracle::occi::Bytes b = n.toBytes();
elementLength[i] = b.length();
b.getBytes(&numArr[i*21], b.length());
}

void* buffer = numArr;
oracle::occi::Type elementsType = oracle::occi::OCCI_SQLT_NUM;
ub4 arrayLength = arr->Length();
sb4 elementsSize = 21;

// Set buffer on statement
stmt->setDataBufferArray(1, buffer, elementsType, arrayLength,
arrayLength, elementsSize,
elementLength, NULL, NULL);
```

That's it, always remember to look at this [reference](http://docs.oracle.com/cd/A91202_01/901_doc/appdev.901/a89860/cci04typ.htm#1003747) to make sure you are using the correct types...
Enjoy !