Skip to content

Instantly share code, notes, and snippets.

@changjie-lin
Last active September 17, 2015 09:45
Show Gist options
  • Select an option

  • Save changjie-lin/82fb14f5b0e26e56ca7e to your computer and use it in GitHub Desktop.

Select an option

Save changjie-lin/82fb14f5b0e26e56ca7e to your computer and use it in GitHub Desktop.
var fs = require('fs');
var oracledb = require('oracledb');
var dbConfig = require('./dbConfig.js');
var async = require('async');
var SegfaultHandler = require('segfault-handler');
SegfaultHandler.registerHandler();
var numRows = 10;
var connection = null;
var inFileName = 'clobexample.txt';
var tableName = 'testlob';
var sqlCreateTab =
"BEGIN " +
" DECLARE " +
" e_table_exists EXCEPTION; " +
" PRAGMA EXCEPTION_INIT(e_table_exists, -00942); " +
" BEGIN " +
" EXECUTE IMMEDIATE ('DROP TABLE " + tableName + " '); " +
" EXCEPTION " +
" WHEN e_table_exists " +
" THEN NULL; " +
" END; " +
" EXECUTE IMMEDIATE (' " +
" CREATE TABLE " + tableName +" ( " +
" num NUMBER(10), " +
" content CLOB, " +
" CONSTRAINT " + tableName + "_pk PRIMARY KEY (num) " +
" )" +
" '); " +
"END; ";
async.series([
function(callback) {
oracledb.getConnection(
dbConfig,
function(err, conn) {
if (err) { console.error(err.message); return; }
connection = conn;
callback();
}
);
},
function(callback) {
connection.execute(
sqlCreateTab,
function(err) {
if (err) { console.error(err.message); return; }
callback();
}
);
},
function(callback) {
insertClob(1, callback);
},
function(callback) {
insertClob(2, callback);
},
function(callback) {
insertClob(3, callback);
},
function(callback) {
connection.execute(
//"SELECT DBMS_LOB.SUBSTR(content, 40, 1) FROM " + tableName,
"SELECT num, content FROM " + tableName,
[],
{ resultSet: true },
function(err, result) {
if (err) { console.error(err.message); return; }
//fetchRowsFromRS(result.resultSet, numRows, callback);
fetchOneRowFromRS(connection, result.resultSet, callback);
}
);
}
], function(err) {
if (err) {
console.error(err.message);
}
console.log("DONE!");
});
function insertClob(id, cb)
{
connection.execute(
"INSERT INTO " + tableName + " VALUES (:n, EMPTY_CLOB()) RETURNING content INTO :lobbv",
{ n: id, lobbv: { type: oracledb.CLOB, dir: oracledb.BIND_OUT } },
function(err, result) {
if (err) { console.error(err.message); return; }
if (result.rowsAffected !=1 || result.outBinds.lobbv.length != 1) {
throw new Error('Error getting a LOB locator');
}
var lob = result.outBinds.lobbv[0];
var inStream = fs.createReadStream(inFileName);
inStream.on('end', function() {
console.log("inStream on 'end' event");
connection.commit( function(err) {
if(err)
console.error(err.message);
else {
console.log("Row " + id + " inserted successfully.");
cb(); // insertion done
}
});
});
inStream.on('error', function(err) {
console.log("inStream.on 'error' event");
console.error(err);
});
inStream.pipe(lob);
}
);
}
function fetchOneRowFromRS(connection, resultSet, cb)
{
resultSet.getRow(
function(err, row) {
if(err) {
console.error(err.message);
doClose(connection, resultSet);
} else if (!row) {
doClose(connection, resultSet);
} else {
async.series([
function(callback) {
var text = '';
var lob = row[1];
lob.setEncoding('utf8');
lob.on('data', function(chunk) {
text += chunk;
});
lob.on('end', function() {
console.log(text);
callback();
});
lob.on('error', function(err) {
console.log("lob.on 'error' event");
console.error(err.message);
});
},
function(callback) {
fetchOneRowFromRS(connection, resultSet, callback);
}
], cb);
}
}
);
}
function doRelease(connection, cb)
{
connection.release(
function(err)
{
if (err) { console.error(err.message); }
cb();
});
}
function doClose(connection, resultSet, cb)
{
resultSet.close(
function(err)
{
if (err) { console.error(err.message); }
doRelease(connection, cb);
});
}
@changjie-lin
Copy link
Author

Hit the issue successfully after modifying the case.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment