Managing database connections using APIs

There are a number of connection-related APIs for you to manage the database connections or view the connection status during runtime. You can find the list of APIs in the Controllers > ConnectionController.cs file in the ServerAPIs project:

  • api/connection/loadone: Loads the configuration of a given connection;

  • api/connection/loadgroup: Loads the configuration of a given CacheGroup;

  • api/connection/loadall: Loads all the connection configuration;

  • api/connection/addone: Adds a connection configuration;

  • api/connection/addrange: Adds a group of connection configuration;

  • api/connection/addgroup: Adds a group of empty connection configuration, and copies connection configuration list from the specified CacheGroup;

  • api/connection/edit: Edits a connection configuration;

  • api/connection/removeone: Removes a connection configuration;

  • api/connection/removegroup: Removes a CacheGroup and the connection configuration in it.

For details on how each of the APIs is defined, please check the ConnectionController.cs file. For documentations, refer to View documentations for PowerServer Management APIs.The following example shows you how to call the api/connection/loadall API in PowerScript to get all the current connection configuration:

//-------------------------------------loadall----------------------------------------
httpclient  lhc_client
string ls_url
string ls_json

lhc_client = create httpclient

//Load all connection
ls_url = "http://localhost:5000/api/connection/loadall"
//This URL should be replaced with the actual IP address and port number of PowerServer Web APIs
//If there are multiple .NET servers, obtain one by one
//lhc_client.SetRequestHeader("Authorization", $token, true)  //If authorization is enabled
lhc_client.sendrequest("Get",ls_url)

if lhc_client.getresponsestatuscode() = 200 then
                lhc_client.getresponsebody(ls_json)
                //parse the json
                wf_getsessions(ls_json)
end if

//-------------------------------------------------------------------------------------------------

And the response is like below:

[{"cachegroup":"Default","items":[{"cachename":"ora","configuration":{"connectiontype":2,"host":"172.16.3.98","port":1521,"odbcname":"","odbcdriver":"","userid":"dbo","password":"appeon","database":"pdborcl","enablepooling":true,"minpoolsize":0,"maxpoolsize":100,"connectionlifetime":0,"connectiontimeout":30,"commandtimeout":30,"otheroptions":"","dynamicconnection":false}},{"cachename":"Test classes","configuration":{"connectiontype":5,"host":"172.16.9.52","port":5432,"odbcname":"","odbcdriver":"","userid":"postgres","password":"postgres","database":"qa_datawindow","enablepooling":true,"minpoolsize":0,"maxpoolsize":100,"connectionlifetime":0,"connectiontimeout":30,"commandtimeout":30,"otheroptions":"","dynamicconnection":false}},{"cachename":"Test123","configuration":{"connectiontype":0,"host":"172.16.9.52","port":5432,"odbcname":"","odbcdriver":"","userid":"postgres","password":"postgres","database":"qa_datawindow","enablepooling":true,"minpoolsize":0,"maxpoolsize":100,"connectionlifetime":0,"connectiontimeout":30,"commandtimeout":30,"otheroptions":"","dynamicconnection":false}}]},{"cachegroup":"Developer","items":[{"cachename":"sql","configuration":{"connectiontype":0,"host":"172.16.3.243","port":1433,"odbcname":"","odbcdriver":"","userid":"sa","password":"Appeon123!@#","database":"Qa_datawindow","enablepooling":true,"minpoolsize":0,"maxpoolsize":100,"connectionlifetime":0,"connectiontimeout":30,"commandtimeout":30,"otheroptions":"","dynamicconnection":false}}]}]