simple-oracledb - npm Package Compare versions

Comparing version 0.1.2 to 0.1.3

docs/api.md

@@ -58,2 +58,4 @@ ## Classes
     * [#batchInsert(sql, bindParamsArray, options, callback)](#Connection+batchInsert)
+    * [#batchUpdate(sql, bindParamsArray, options, callback)](#Connection+batchUpdate)
+    * [#batchInsertOrUpdate(insert, sql, bindParamsArray, options, callback)](#Connection+batchInsertOrUpdate) ℗
     * [#modifyParams(argumentsArray)](#Connection+modifyParams) ⇒ <code>object</code> ℗
@@ -348,2 +350,61 @@ * [#createCallback(callback, commit, [output])](#Connection+createCallback) ⇒ <code>function</code> ℗
 ```
+<a name="Connection+batchUpdate"></a>
+### Connection#batchUpdate(sql, bindParamsArray, options, callback)
+Enables to run an UPDATE SQL statement multiple times for each of the provided bind params.<br>
+This allows to update to same table multiple different rows with one single call.<br>
+The callback output will be an array of objects of same as oracledb conection.execute (per row).<br>
+All LOBs for all rows will be written to the DB via streams and only after all LOBs are written the callback will be called.<br>
+The function arguments used to execute the 'update' are exactly as defined in the oracledb connection.execute function, however the options are mandatory and
+the bind params is now an array of bind params (one per row).
+
+**Access:** public  
+
+| Param | Type | Description |
+| --- | --- | --- |
+| sql | <code>string</code> | The SQL to execute |
+| bindParamsArray | <code>object</code> | An array of instances of object/Array bind parameters used to specify the values for the columns per row |
+| options | <code>object</code> | Any execute options |
+| [options.autoCommit] | <code>object</code> | If you wish to commit after the update, this property must be set to true in the options (oracledb.autoCommit is not checked) |
+| [options.lobMetaInfo] | <code>object</code> | For LOB support this object must hold a mapping between DB column name and bind variable name |
+| callback | <code>[AsyncCallback](#AsyncCallback)</code> | Invoked with an error or the update results (if LOBs are provided, the callback will be triggered after they have been fully written to the DB) |
+
+**Example**  
+```js
+connection.batchUpdate('UPDATE mylobs SET name = :name, clob_column1 = EMPTY_CLOB(), blob_column2 = EMPTY_BLOB() WHERE id = :id', [ //no need to specify the RETURNING clause in the SQL
+  { //first row values
+    id: 110,
+    clobText1: 'some long clob string', //add bind variable with LOB column name and text content (need to map that name in the options)
+    blobBuffer2: new Buffer('some blob content, can be binary...')  //add bind variable with LOB column name and text content (need to map that name in the options)
+  },
+  { //second row values
+    id: 111,
+    clobText1: 'second row',
+    blobBuffer2: new Buffer('second rows')
+  }
+], {
+  autoCommit: true, //must be set to true in options to support auto commit after update is done, otherwise the auto commit will be false (oracledb.autoCommit is not checked)
+  lobMetaInfo: { //if LOBs are provided, this data structure must be provided in the options object and the bind variables parameter must be an object (not array)
+    clob_column1: 'clobText1', //map oracle column name to bind variable name
+    blob_column2: 'blobBuffer2'
+  }
+}, function onResults(error, output) {
+  //continue flow...
+});
+```
+<a name="Connection+batchInsertOrUpdate"></a>
+### Connection#batchInsertOrUpdate(insert, sql, bindParamsArray, options, callback) ℗
+Internal function to run batch INSERT/UPDATE commands.
+
+**Access:** private  
+
+| Param | Type | Description |
+| --- | --- | --- |
+| insert | <code>boolean</code> | True for insert, false for update |
+| sql | <code>string</code> | The SQL to execute |
+| bindParamsArray | <code>object</code> | An array of instances of object/Array bind parameters used to specify the values for the columns per row |
+| options | <code>object</code> | Any execute options |
+| [options.autoCommit] | <code>object</code> | If you wish to commit after the insert/update, this property must be set to true in the options (oracledb.autoCommit is not checked) |
+| [options.lobMetaInfo] | <code>object</code> | For LOB support this object must hold a mapping between DB column name and bind variable name |
+| callback | <code>[AsyncCallback](#AsyncCallback)</code> | Invoked with an error or the insert/update results (if LOBs are provided, the callback will be triggered after they have been fully written to the DB) ``` |
+
 <a name="Connection+modifyParams"></a>
@@ -350,0 +411,0 @@ ### Connection#modifyParams(argumentsArray) ⇒ <code>object</code> ℗

lib/connection.js

@@ -427,2 +427,66 @@ 'use strict';
 Connection.prototype.batchInsert = function (sql, bindParamsArray, options, callback) {
+    this.batchInsertOrUpdate(true, sql, bindParamsArray, options, callback);
+};
+
+/**
+ * Enables to run an UPDATE SQL statement multiple times for each of the provided bind params.<br>
+ * This allows to update to same table multiple different rows with one single call.<br>
+ * The callback output will be an array of objects of same as oracledb conection.execute (per row).<br>
+ * All LOBs for all rows will be written to the DB via streams and only after all LOBs are written the callback will be called.<br>
+ * The function arguments used to execute the 'update' are exactly as defined in the oracledb connection.execute function, however the options are mandatory and
+ * the bind params is now an array of bind params (one per row).
+ *
+ * @function
+ * @memberof! Connection
+ * @public
+ * @param {string} sql - The SQL to execute
+ * @param {object} bindParamsArray - An array of instances of object/Array bind parameters used to specify the values for the columns per row
+ * @param {object} options - Any execute options
+ * @param {object} [options.autoCommit] - If you wish to commit after the update, this property must be set to true in the options (oracledb.autoCommit is not checked)
+ * @param {object} [options.lobMetaInfo] - For LOB support this object must hold a mapping between DB column name and bind variable name
+ * @param {AsyncCallback} callback - Invoked with an error or the update results (if LOBs are provided, the callback will be triggered after they have been fully written to the DB)
+ * @example
+ * ```js
+ * connection.batchUpdate('UPDATE mylobs SET name = :name, clob_column1 = EMPTY_CLOB(), blob_column2 = EMPTY_BLOB() WHERE id = :id', [ //no need to specify the RETURNING clause in the SQL
+ *   { //first row values
+ *     id: 110,
+ *     clobText1: 'some long clob string', //add bind variable with LOB column name and text content (need to map that name in the options)
+ *     blobBuffer2: new Buffer('some blob content, can be binary...')  //add bind variable with LOB column name and text content (need to map that name in the options)
+ *   },
+ *   { //second row values
+ *     id: 111,
+ *     clobText1: 'second row',
+ *     blobBuffer2: new Buffer('second rows')
+ *   }
+ * ], {
+ *   autoCommit: true, //must be set to true in options to support auto commit after update is done, otherwise the auto commit will be false (oracledb.autoCommit is not checked)
+ *   lobMetaInfo: { //if LOBs are provided, this data structure must be provided in the options object and the bind variables parameter must be an object (not array)
+ *     clob_column1: 'clobText1', //map oracle column name to bind variable name
+ *     blob_column2: 'blobBuffer2'
+ *   }
+ * }, function onResults(error, output) {
+ *   //continue flow...
+ * });
+ * ```
+ */
+Connection.prototype.batchUpdate = function (sql, bindParamsArray, options, callback) {
+    this.batchInsertOrUpdate(false, sql, bindParamsArray, options, callback);
+};
+
+/**
+ * Internal function to run batch INSERT/UPDATE commands.
+ *
+ * @function
+ * @memberof! Connection
+ * @private
+ * @param {boolean} insert - True for insert, false for update
+ * @param {string} sql - The SQL to execute
+ * @param {object} bindParamsArray - An array of instances of object/Array bind parameters used to specify the values for the columns per row
+ * @param {object} options - Any execute options
+ * @param {object} [options.autoCommit] - If you wish to commit after the insert/update, this property must be set to true in the options (oracledb.autoCommit is not checked)
+ * @param {object} [options.lobMetaInfo] - For LOB support this object must hold a mapping between DB column name and bind variable name
+ * @param {AsyncCallback} callback - Invoked with an error or the insert/update results (if LOBs are provided, the callback will be triggered after they have been fully written to the DB)
+ * ```
+ */
+Connection.prototype.batchInsertOrUpdate = function (insert, sql, bindParamsArray, options, callback) {
     var self = this;
@@ -434,2 +498,7 @@
 
+    var operation = 'update';
+    if (insert) {
+        operation = 'insert';
+    }
+
     //create tasks
@@ -439,3 +508,3 @@ var tasks = [];
         tasks.push(function executeTask(asyncCallback) {
-            self.insert(sql, bindParams, options, asyncCallback);
+            self[operation](sql, bindParams, options, asyncCallback);
         });
@@ -442,0 +511,0 @@ });

package.json

 {
   "name": "simple-oracledb",
-  "version": "0.1.2",
+  "version": "0.1.3",
   "description": "Extend capabilities of oracledb with simplified API for quicker development.",
@@ -5,0 +5,0 @@ "author": {

README.md

@@ -22,2 +22,3 @@ # simple-oracledb
     * [batchInsert](#usage-batchInsert)
+    * [batchUpdate](#usage-batchUpdate)
     * [release](#usage-release)
@@ -329,2 +330,34 @@ * [rollback](#usage-rollback)
 
+<a name="usage-batchUpdate"></a>
+## 'connection.batchUpdate(sql, bindVariablesArray, options, callback)'
+Enables to run an UPDATE SQL statement multiple times for each of the provided bind params.<br>
+This allows to update to same table multiple different rows with one single call.<br>
+The callback output will be an array of objects of same as oracledb conection.execute (per row).<br>
+All LOBs for all rows will be written to the DB via streams and only after all LOBs are written the callback will be called.<br>
+The function arguments used to execute the 'update' are exactly as defined in the oracledb connection.execute function, however the options are mandatory and
+the bind params is now an array of bind params (one per row).
+
+```js
+connection.batchUpdate('UPDATE mylobs SET name = :name, clob_column1 = EMPTY_CLOB(), blob_column2 = EMPTY_BLOB() WHERE id = :id', [ //no need to specify the RETURNING clause in the SQL
+  { //first row values
+    id: 110,
+    clobText1: 'some long clob string', //add bind variable with LOB column name and text content (need to map that name in the options)
+    blobBuffer2: new Buffer('some blob content, can be binary...')  //add bind variable with LOB column name and text content (need to map that name in the options)
+  },
+  { //second row values
+    id: 111,
+    clobText1: 'second row',
+    blobBuffer2: new Buffer('second rows')
+  }
+], {
+  autoCommit: true, //must be set to true in options to support auto commit after update is done, otherwise the auto commit will be false (oracledb.autoCommit is not checked)
+  lobMetaInfo: { //if LOBs are provided, this data structure must be provided in the options object and the bind variables parameter must be an object (not array)
+    clob_column1: 'clobText1', //map oracle column name to bind variable name
+    blob_column2: 'blobBuffer2'
+  }
+}, function onResults(error, output) {
+  //continue flow...
+});
+```
+
 <a name="usage-release"></a>
@@ -396,2 +429,3 @@ ## 'connection.release([callback])'
 | ----------- | ------- | ----------- |
+| 2015-12-29  | v0.1.3  | Added connection.batchUpdate |
 | 2015-12-22  | v0.1.2  | Added streaming of query results with new option streamResults=true |
@@ -398,0 +432,0 @@ | 2015-12-21  | v0.1.1  | Rename streamResults to splitResults |

test/integration/integration-spec.js

@@ -887,3 +887,125 @@ 'use strict';
         });
+
+        describe('batchUpdate', function () {
+            it('batchUpdate - LOB data', function (done) {
+                var table = 'TEST_ORA_BTCH_UPD1';
+
+                var longClobText = 'this is a really long line of test data\n';
+                var index;
+                var buffer = [];
+                for (index = 0; index < 1000; index++) {
+                    buffer.push(longClobText);
+                }
+                longClobText = buffer.join('');
+
+                initDB(table, [], function (pool) {
+                    pool.getConnection(function (err, connection) {
+                        assert.isUndefined(err);
+
+                        connection.batchInsert('INSERT INTO ' + table + ' (COL1, COL2, LOB1, LOB2) values (:value1, :value2, EMPTY_CLOB(), EMPTY_BLOB())', [
+                            {
+                                value1: 'test',
+                                value2: 123,
+                                clob1: longClobText,
+                                blob2: new Buffer('blob text here')
+                            },
+                            {
+                                value1: 'test2',
+                                value2: 455,
+                                clob1: longClobText,
+                                blob2: new Buffer('second row')
+                            }
+                        ], {
+                            autoCommit: true,
+                            lobMetaInfo: {
+                                LOB1: 'clob1',
+                                LOB2: 'blob2'
+                            }
+                        }, function (error, results) {
+                            assert.isNull(error);
+                            assert.equal(2, results.length);
+                            assert.equal(1, results[0].rowsAffected);
+                            assert.equal(1, results[1].rowsAffected);
+
+                            connection.query('SELECT * FROM ' + table + ' ORDER BY COL1 ASC', [], {
+                                resultSet: false
+                            }, function (queryError, jsRows) {
+                                assert.isNull(queryError);
+                                assert.deepEqual([
+                                    {
+                                        COL1: 'test',
+                                        COL2: 123,
+                                        COL3: undefined,
+                                        COL4: undefined,
+                                        LOB1: longClobText,
+                                        LOB2: new Buffer('blob text here')
+                                    },
+                                    {
+                                        COL1: 'test2',
+                                        COL2: 455,
+                                        COL3: undefined,
+                                        COL4: undefined,
+                                        LOB1: longClobText,
+                                        LOB2: new Buffer('second row')
+                                    }
+                                ], jsRows);
+
+                                connection.batchUpdate('UPDATE ' + table + ' SET COL1 = :value1, LOB1 = EMPTY_CLOB(), LOB2 = EMPTY_BLOB() WHERE COL2 = :value2', [
+                                    {
+                                        value1: 'testU1',
+                                        value2: 123,
+                                        clob1: 'NEW CLOB1',
+                                        blob2: new Buffer('NEW BLOB')
+                                    },
+                                    {
+                                        value1: 'testU2',
+                                        value2: 455,
+                                        clob1: 'NEW CLOB2',
+                                        blob2: new Buffer('AND ANOTHER NEW BLOB')
+                                    }
+                                ], {
+                                    autoCommit: true,
+                                    lobMetaInfo: {
+                                        LOB1: 'clob1',
+                                        LOB2: 'blob2'
+                                    }
+                                }, function (updateError, updateResults) {
+                                    assert.isNull(updateError);
+                                    assert.equal(2, updateResults.length);
+                                    assert.equal(1, updateResults[0].rowsAffected);
+                                    assert.equal(1, updateResults[1].rowsAffected);
+
+                                    connection.query('SELECT * FROM ' + table + ' ORDER BY COL1 ASC', [], {
+                                        resultSet: false
+                                    }, function (queryError, jsRows) {
+                                        assert.isNull(queryError);
+                                        assert.deepEqual([
+                                            {
+                                                COL1: 'testU1',
+                                                COL2: 123,
+                                                COL3: undefined,
+                                                COL4: undefined,
+                                                LOB1: 'NEW CLOB1',
+                                                LOB2: new Buffer('NEW BLOB')
+                                            },
+                                            {
+                                                COL1: 'testU2',
+                                                COL2: 455,
+                                                COL3: undefined,
+                                                COL4: undefined,
+                                                LOB1: 'NEW CLOB2',
+                                                LOB2: new Buffer('AND ANOTHER NEW BLOB')
+                                            }
+                                        ], jsRows);
+
+                                        end(done, connection);
+                                    });
+                                });
+                            });
+                        });
+                    });
+                });
+            });
+        });
     }
 });

New alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Fixed alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

369327

increased by9.47%

Lines of code

7909

increased by8.31%

Number of lines in readme file

451

increased by8.15%

No dependency changes