[Node.js] oracle db 연동

oracle DataBase를 사용하기 위해서 해당 프로젝트에 oracledb를 설치.

 

npm install –-save oracledb

 

dbConfig.js : db 연결 정보

/* Copyright (c) 2015, 2019, Oracle and/or its affiliates. All rights reserved. */

/******************************************************************************
 *
 * You may not use the identified files except in compliance with the Apache
 * License, Version 2.0 (the "License.")
 *
 * You may obtain a copy of the License at
 * http://www.apache.org/licenses/LICENSE-2.0.
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 * NAME
 *   dbconfig.js
 *
 * DESCRIPTION
 *   Holds the credentials used by node-oracledb examples to connect
 *   to the database.  Production applications should consider using
 *   External Authentication to avoid hard coded credentials.
 *
 *   To create a database user see https://www.youtube.com/watch?v=WDJacg0NuLo
 *
 *   Applications can set the connectString value to an Easy Connect
 *   string, or a Net Service Name from a tnsnames.ora file or
 *   external naming service, or it can be the name of a local Oracle
 *   database instance.
 *
 *   If node-oracledb is linked with Instant Client, then an Easy
 *   Connect string is generally appropriate.  The basic syntax is:
 *
 *     [//]host_name[:port][/service_name][:server_type][/instance_name]
 *
 *   Commonly just the host_name and service_name are needed
 *   e.g. "localhost/orclpdb1" or "example.com/XEPDB1"
 *
 *   The Easy Connect syntax was enhanced in Oracle Database 19c to
 *   allow more options, refer to the documentation:
 *   https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=GUID-B0437826-43C1-49EC-A94D-B650B6A4A6EE
 *
 *   If using a tnsnames.ora file, the file can be in a default
 *   location such as $ORACLE_HOME/network/admin/tnsnames.ora or
 *   /etc/tnsnames.ora.  Alternatively set the TNS_ADMIN environment
 *   variable and put the file in $TNS_ADMIN/tnsnames.ora.
 *
 *   If connectString is not specified, the empty string "" is used
 *   which indicates to connect to the local, default database.
 *
 *   External Authentication can be used by setting the optional
 *   property externalAuth to true.  External Authentication allows
 *   applications to use an external password store such as Oracle
 *   Wallet so passwords do not need to be hard coded into the
 *   application.  The user and password properties for connecting or
 *   creating a pool should not be set when externalAuth is true.
 *
 * TROUBLESHOOTING
 *   Errors like:
 *     ORA-12541: TNS:no listener
 *   or
 *     ORA-12154: TNS:could not resolve the connect identifier specified
 *   indicate connectString is invalid.
 *
 *   The error:
 *     ORA-12514: TNS:listener does not currently know of requested in connect descriptor
 *   indicates connectString is invalid.  You are reaching a computer
 *   with Oracle installed but the service name isn't known.
 *   Use 'lsnrctl services' on the database server to find available services
 *
 *****************************************************************************/

module.exports = {
    user: process.env.NODE_ORACLEDB_USER || "userId",
    password: process.env.NODE_ORACLEDB_PASSWORD || "password",
    connectString: process.env.NODE_ORACLEDB_CONNECTIONSTRING || "localhost:1521/xe"
  };

 

 

connect.js : DB연동

 

/* Copyright (c) 2015, 2019, Oracle and/or its affiliates. All rights reserved. */

/******************************************************************************
 *
 * You may not use the identified files except in compliance with the Apache
 * License, Version 2.0 (the "License.")
 *
 * You may obtain a copy of the License at
 * http://www.apache.org/licenses/LICENSE-2.0.
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 * NAME
 *   connect.js
 *
 * DESCRIPTION
 *   Tests a basic connection to the database.
 *   See dbconfig.js for information on connectString formats.
 *
 *   For a connection pool example see connectionpool.js
 *
 *   This example uses Node 8's async/await syntax.
 *
 *****************************************************************************/
var oracledb = require('oracledb');
oracledb.autoCommit = true;

var dbConfig = require('./dbConfig.js');

/*
oracledb.getConnection(
    {
        user:dbConfig.user,
        password:dbConfig.password,
        connectString:dbConfig.connectString
    },
    function(err,connection){  
        if(err){
            console.error(err.message);
            return;
        }
        console.log('Connection was successful!');
        //console.log(connection);
        //conn=connection;
        //console.log(conn);
        //doExecute('SELECT *FROM USER_TB',connection);
        //doClose(connection); 
        return connection;
    });
*/
    module.exports = {
        doExecute: function doExecute(query){
            oracledb.getConnection(
                {
                    user:dbConfig.user,
                    password:dbConfig.password,
                    connectString:dbConfig.connectString
                },
                function(err,connection){  
                    if(err){
                        console.error(err.message);
                        return;
                    }
                    console.log('Connection was successful!');
                    //console.log(connection);
                    //conn=connection;
                    //console.log(conn);
                    //doExecute('SELECT *FROM USER_TB',connection);
                    //doClose(connection); 
                    connection.execute(query,function(err,result){
                        console.log(query);
                        if(err){
                            console.error(err.message);
                            doRelease(connection);
                            return;
                        }
                        //console.log(result);
                        console.log("insert");
                        doRelease(connection);
                    });
                });
            
           
        },
        doRelease: function doRelease(connection)
        {
          connection.release(
            function(err) {
              if (err) {
                console.error(err.message);
              }
            });
        }
      };



// DB 종료 //
function doClose(connection){
    connection.close(
        function(err){
            if(err){
                console.error(err.message);
                return;
            }
        });
}

//Query 실행
function doExecute(query,connection){
    connection.execute(query,function(err,result){
        if(err){
            console.err(err.message);
            doRelease(connection);
            return;
        }
        console.log(result.rows);
    });
}

// DB연결해제//
function doRelease(connection)
{
  connection.release(
    function(err) {
      if (err) {
        console.error(err.message);
      }
    });
}