CODEWITHSUNDEEP
node.jsoracle-databasefile
todotresde
todotresde

Reputation: 1790

NodeJs + OracleDB + load sql file

The problem is simple, I cant load an sql file using the oracledb connector. It seems it doesn´t supoport more than one sentence.

Any idea how to load an sql file?

var oracledb = require('oracledb');
var fs = require('fs');

fs.readFile("test.sql", function(err, data) {
    if (err) {
        throw err;
    }

    connect(data.toString());
});

function connect(sql) {
    oracledb.getConnection({
            user: "****",
            password: "***",
            connectString: "****"
        },
        function(err, connection) {
            if (err) {
                console.error(err.message);
                return;
            }
            connection.execute(
                sql, [],
                function(err, result) {
                    if (err) {
                        console.error(err.message);
                        doRelease(connection);
                        return;
                    }
                    console.log(result.metaData);
                    console.log(result.rows);
                    doRelease(connection);
                });
        });
}

function doRelease(connection) {
    connection.release(
        function(err) {
            if (err) {
                console.error(err.message);
            }
        });
}

It throws an error:

ORA-00911: invalid character

The sql is here:

select * from DEFECTO;
select * from ESQUEMA;

Upvotes: 2

Views: 2618

Answers (3)

Adam Fendley
Adam Fendley

Reputation: 66

Battled with this for a long time before realizing it's the semicolon that the connection.execute() method doesn't like. So long as your SQL statement doesn't conclude with one, reading from a file works.

Upvotes: 1

Christopher Jones
Christopher Jones

Reputation: 10496

As you discovered, node-oracledb's connection.execute() method only executes one statement. This is the same as other languages. Instead of putting your SQL statements in a SQL*Plus file, try putting them in a .js file as an array of strings. Then process each element of the array. This will give you better control over transactions and error handling.

Upvotes: 0

todotresde
todotresde

Reputation: 1790

Well, I found a workaround for multiple sql statements, and is just using exec:

exec('echo exit | sqlplus -S ****/***@//****:1521/**** @sqlfile.sql', ["bash"],
                function(error, stdout, stderr) {
                    callback();
                }
            );

Upvotes: 1

Related Questions