I need to use an Auto-operator script to connect to a external Database. How do I do this?

Document ID : KB000046250
Last Modified Date : 06/07/2018
Show Technical Document Details
Introduction:
Problem:
I tried to create a NAS Auto-operator (AO) LUA script to connect to an external database but I can not get it to work.
Can you please provide an example of making a database connection string for the NAS LUA scripting?


Solution:
When connecting to oracle, MS SQL and MySQL it is required that a working ODBC driver be installed to reference from the NAS LUS script.
NOTE: Please see the vendor documentation for installing drivers for the operating system in use.

the simplest format os to connect to the UIM database that the primary is connecting to
Opens the NiS:
         database.open("provider=nis;database=nis;driver=none")
        
To connect to other database or other types of database please see below:
Opens a separate SQLite database file:
database.open("my_private.db")

Opens MS Access database:
database.open ("Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\\ myluadb.mdb;Uid=xxx;Pwd=yyy")

Connection string for MSSQL:
 
database.open ("Driver={SQL Server};Data Source=Database server;Initial Catalog=Database name;User Id=Username;Password=Password;")
        Or
        database.open ("Driver={SQL Server}; Server=servername; Database=database; Uid=username; Pwd=password;")



NOTE: On windows system you will need to download and install ODBC drivers for MYSQL and ORACLE. MS no longer ships an oracle or MYSQL drivers with the OS.

Connection string for MySQL:
database.open ("Driver={MySQL ODBC 5.1 Driver};Server=Database server;Option=131072;Stmt=;Database=Database name;User=Username Password=Password;")


Connection string for Oracle on LINUX:
database.open ("Provider=Oracle;Data Source=oracle_server;Initial Catalog=oracle_database;User ID=oracle_user;Password=oracle_pwd;ServiceName=oracle_service;Port=oracle_port;")



Connection string for Oracle on Windows Setup:
On a windows server the following must be done:
1) download and install the Oracle ODAC 64 ODBC drivers.
2) I downloaded and installed the second download package from the below link.
64-bit Oracle Data Access Components (ODAC) for Windows
ODAC122010Xcopy_x64.zip

3) in the 64 bit ODBC section of windows I could then see a new driver called
    Oracle in OraClient12Home1

Connection string for Oracle on Windows:    
database.open("Driver={Oracle in OraClient12Home1};Dbq=//servername:port/servicename;Uid=username;Pwd=password;;")

Example Windows Script:
print("Start database connection 1")
-- Connect to the database
local oracle_server = "<Servername>"
local oracle_user = "<UserName>"
local oracle_pwd = "<Userpassword>"
local oracle_service = "<ServiceName>"
local oracle_port = "1521"
local oracle_database= "CA_UIM"
local database_driver ="Oracle in OraClient12Home1"
 
if oracle_pwd ~= "" then
    -- Build connection string
       local cs = "Driver={"..database_driver.."};Dbq=//"..oracle_server..":"..oracle_port.."/"..oracle_service..";Uid="..oracle_user..";Pwd="..oracle_pwd..";"
      print (cs)
    local rc = database.open(cs,false)
      print(rc)
    if rc == NIME_OK then
        local rs = database.query ("SELECT * FROM CA_UIM.CM_COMPUTER_SYSTEM")
        printf ("Number of QoS objects: %d",#rs)
            for i=1,#rs do
               print("Name Column has: " .. rs[i].NAME .. ", CS_ID Column has: " .. rs[i].CS_ID)
            end
    else
        print ("Failed to open database using the provided connection string!")
    end

    database.close()
end



Example Linux Script:
print("Start database connection 1")
-- Connect to the database
local oracle_server = "<ServerName>"
local oracle_user = "<OracleUser>"
local oracle_pwd = "<UserPassword>"
local oracle_service = "<OracleServiceName>"
local oracle_port = "1521"
local oracle_database= "CA_UIM"
local database_driver ="Oracle in OraClient12Home1"

function getRow(dataTable, rowNum, isNIS)
local realRow
    if (isNIS) then
        realRow = dataTable[rowNum]
    else
        realRow = {}
        for key, val in pairs(dataTable[rowNum]) do
            realRow[key] = val
            realRow[string.lower(key)] = val
            realRow[string.upper(key)] = val
        end
    end
 
    return realRow
end

 
if oracle_pwd ~= "" then
    -- Build connection string
    local cs = "Provider=Oracle;Data Source="..oracle_server..";Database="..oracle_database..";User ID="..oracle_user..";Password="..oracle_pwd..";ServiceName="..oracle_service..";Port="..oracle_port..";"
    print (cs)
    local rc = database.open(cs,false)
      print(rc)
    if rc == NIME_OK then
        local rs = database.query ("Select * from CA_UIM.cm_computer_system order by CS_ID")
        printf ("Number of QoS objects: %d",#rs)
        for I=1,#rs do
            nextRow = getRow(rs, I, false)
            print("For Row " .. I .. " NAME is " .. nextRow.NAME .. " and CA_ID is " .. nextRow.CS_ID)
        end
    else
        print ("Failed to open database using the provided connection string!")
    end

     database.close()
end




 
Instructions:
Please Update This Required Field