Oracle DB API Wrapper

overview note this solution supports select functions as well as insert, update, delete functions requirements a litmus edge is setup an oracle db is accessible through the network how to use the solution using docker after checkout, a tar gz file is expected to be downloaded log in to your litmusedge, navigate to applications > images click on (\[27 icon icon="fa fa plus"]) upload image and upload the tar file there once uploaded successfully, navigate to applications > containers and enter the docker run command here is an example to run oracle db api wrapper with docker docker run dt name oracle gateway p \<host port> 3000 \\ e log level='\<log level>' \\ e log max size='\<log max size>' \\ e log max files='\<log max files>' \\ \ restart=always \\ oracle gateway\ latest configuration options ⚠️ when using docker, the following environment variables must be set before running the container key description dt run the containers in detached mode and with terminal name oracle gateway assign a name to the container (optional) p \<host port> 3000 map a host port to port 3000 of the container (required) e log level='\<log level>' set the log level for the application (optional, defaults to 'info') e log max size='\<log max size>' set the maximum size for log files (optional, defaults to '20m') e log max files='\<log max files>' set the maximum number of files for log rotation (optional, defaults to '14d') restart=always automatically restart the container if it exits unexpectedly oracle gateway\ latest docker image tag and version example request body used in flows { "querystr" "select 1 from dual", "user" "sys", "password" "dbpasswordexample\@123", "mode" "thn", "privilege" true, "connstring" "(description=(address=(protocol=tcp)(host='\<server>')(port='1521'))(connect data=(server='\<server>')(service name='xe')))", "server" "\<server>", "port" 1521, "hostname" "\<server>", "sid" "xe", "servicename" "xe" } method url description extras post /api/data parameters in body mode one of the following ("cns","srn","sid","thk","thn") > cns connection string (default) parameters user, password, connstring, querystr > srn service name parameters user, password, hostname, port, servicename, querystr > sid session id parameters user, password, hostname, port, sid, querystr > thk thick mode using oracle client connection parameters user, password, connstring, querystr > thn thin mode using oracle client connection parameters user, password, connstring, querystr example le flow \[ { "id" "01740c8932692cc4", "type" "tab", "label" "oracledb connection", "disabled" false, "info" "", "env" \[] }, { "id" "e6cf2c52c4ec85dd", "type" "inject", "z" "01740c8932692cc4", "name" "", "props" \[ { "p" "payload" }, { "p" "topic", "vt" "str" } ], "repeat" "", "crontab" "", "once" false, "oncedelay" 0 1, "topic" "", "payload" "", "payloadtype" "date", "x" 200, "y" 200, "wires" \[ \[ "57137f9e ce895" ] ] }, { "id" "94b070f38334a607", "type" "debug", "z" "01740c8932692cc4", "name" "", "active" true, "tosidebar" true, "console" false, "tostatus" false, "complete" "true", "targettype" "full", "statusval" "", "statustype" "auto", "x" 990, "y" 120, "wires" \[] }, { "id" "3eb9c0257dda01e3", "type" "http request", "z" "01740c8932692cc4", "name" "", "method" "use", "ret" "txt", "paytoqs" "ignore", "url" "", "tls" "", "persist" false, "proxy" "", "insecurehttpparser" false, "authtype" "", "senderr" false, "headers" \[], "x" 690, "y" 200, "wires" \[ \[ "1ef3217515dd667e" ] ] }, { "id" "57137f9e ce895", "type" "function", "z" "01740c8932692cc4", "name" "set connection parameters", "func" "const ip = '10 30 50 1 3000'; //ip address to the oracle gateway api wrapper container and port \nconst ip db = '10 20 30 40' //ip address of the oracle database\n\nmsg method ='post';\nmsg url = `http //${ip}/api/data`;\nmsg headers={\n 'content type' 'application/json'\n}\n\nmsg payload = {\n querystr \\"select 1 from dual\\",\n user \\"sys\\", // oracle db username\n password \\"dbpasswordexample\@123\\", // oracle db password \n mode \\"thn\\", // connection type (aka mode)\n privilege true, // true if accessing as db administrator false if access as db user\n connstring `(description=(address=(protocol=tcp)(host='${ip db}')(port='1521'))(connect data=(server='${ip db}')(service name='xe')))`, // ensure all connection string is accurate \n}\n\nmsg redirect= \\"follow\\"\n\nreturn msg;", "outputs" 1, "timeout" "", "noerr" 0, "initialize" "", "finalize" "", "libs" \[], "x" 440, "y" 200, "wires" \[ \[ "3eb9c0257dda01e3" ] ] }, { "id" "1ef3217515dd667e", "type" "json", "z" "01740c8932692cc4", "name" "", "property" "payload", "action" "", "pretty" false, "x" 870, "y" 200, "wires" \[ \[ "94b070f38334a607" ] ] } ]