Applicable Versions: 5.50.0102 and above
As of DataPA V5.5 (5.50.0102) it is possible to use an REST web service API call to DataPA to create and maintain SQL based Subjects in DataPA as well as ODBC based Systems.
IMPORTING SQL SYSTEMS
The table below shows the details of the web service call that needs to be made in order to maintain Systems in DataPA Enterprise:
| PROPERTY | VALUE |
| URL | http://<server>:<port>/DataPA/EnterpriseInfo.svc/ImportSQLSystems |
| METHOD | POST |
| CONTENT TYPE | application/json |
REQUEST JSON
The request will be a JSON document that represents an import object class that contains three properties containing the information need to create one or more Systems in DataPA.
This ImportObject class has three properties:
| PROPERTY | DESCRIPTION |
| userid | The userid of a user on DataPA Enterprise that has permission to maintain Systems. |
| password | The password of a user on DataPA Enterprise that has permission to maintain Systems. |
| jsondatatable | A JSON array that represents multiple System operations. The of these rows is made up of a number of properties shown in the table below. |
| JSONDATATABLE PROPERTY | DESCRIPTION |
| system | The name of this System. This is the unique identifier for the System. |
| description | The description this System should now have. |
| connectionname | The name of this connection in DataPA |
| host | The host to be used for this ODBC connection. This is optional a blank value will indicate that the connection should be made directly, |
| connectionstring | The ODBC connection string that should be used. |
| promptuser | A flag to indicate whether the user should be prompted to enter a username and password to make the connection to the datasource. |
| username | The user name that should be used when execting the SQL query string to obtain the datatable. |
| password | The password that should be used when execting the SQL query string to obtain the datatable. |
| delete | A flag which if set to true means that the subject with the matching ID should be deleted form the DataPA setup. |
As the import method processes each of the rows it will test the connection for each one. If the the connection cannot be made an error will be reported against that row.
Below is an example of the request JSON that would be used to add two new Systems:
| { "ImportObject":{ "userid":"admin", "password":"admin123", "jsondatatable":"[{\"system\": \"Local AdventureWorks v2\", \"description\": \"\", \"connectionname\": \"Default\", \"host\": \"\", \"connectionstring\": \"dsn=AdventureWorks\", \"promptuser\": true, \"username\": \"sa\", \"password\": \"abc123\", \"delete\": false}, {\"system\": \"Local AdventureWorks v3\", \"description\": \"\", \"connectionname\": \"Default\", \"host\": \"\", \"connectionstring\": \"dsn=AdventureWorks\", \"promptuser\": true, \"username\": \"sa\", \"password\": \"abc123\", \"delete\": false} ]" } } |
RESPONSE JSON
The reponse will be a JSON document that is made up of one or more items with each item corresponding to each row in the jsondatatable property of the request.
| PROPERTY | DESCRIPTION |
| system | The name of the system the System that was being added, amended or deleted. |
| success | A flag to indicate if the import of this System was successful or not. |
| errormessage | If the import of this System was not successful then the error message that resulted. |
Below is example JSON returned from the request JSON noted above:
| [ { "system": "Local AdventureWorks v2", "success": true, "errormessage": "" }, { "system": "Local AdventureWorks v3", "success": true, "errormessage": "" } ] |
IMPORTING SQL SUBJECTS
The table below shows the details of the web service call that needs to be made in order to maintain Subjects in DataPA Enterprise:
| PROPERTY | VALUE |
| URL | http://<server>:port>/DataPA/EnterpriseInfo.svc/ImportSQLSubjects |
| METHOD | POST |
| CONTENT TYPE | application/json |
REQUEST JSON
The request will be a JSON document that represents an import object class that contains three properties containing the information need to create one or more Subjects in DataPA.
This ImportObject class has three properties:
| PROPERTY | DESCRIPTION |
| userid | The userid of a user on DataPA Enterprise that has permission to maintain Subjects. |
| password | The password of a user on DataPA Enterprise that has permission to maintain Subjects. |
| jsondatatable | A JSON array that represents multiple Subject operations. The of these rows is made up of a number of properties shown in the table below. |
| JSONDATATABLE PROPERTY | DESCRIPTION |
| id | The ID of the Subject to be created. This must be unique across all Subjects in the DataPA configuration, even Subjects in other systems. If not ID is passed then a GUID will be generated by DataPA and passed back in the response. If this ID is already ise by an existing Subject then the operation will be considered to be an amend operation rather than an add. |
| system | The system this Subject should be in if a new Subject is being added. |
| title | The title this Subject should now have. |
| description | The description this Subject should now have. |
| sqlquerystring | The SQL query string this Subject should now have. This needs to be a SELECT statement that returns a valid datatable from the datasource that the system default connection is pointing to. |
| username | The user name that should be used when execting the SQL query string to obtain the datatable. |
| password | The password that should be used when execting the SQL query string to obtain the datatable. |
| delete | A flag which if set to true means that the subject with the matching ID should be deleted form the DataPA setup. |
A the import method processes each of the rows it will execute the SQL query string to obtain a data table for each one. The fields in the data table will then become the fields available in the Subject. This will be done for each row.
Below is an example of the request JSON that would be used to add two new Subjects:
| { "ImportObject":{ "userid":"admin", "password":"admin123", "jsondatatable":"[ {\"id\": \"\",\"system\":\"Local AdventureWorks\",\"title\":\"test6\",\"description\": \"\",\"sqlquerystring\":\"select * from AdventureWorks.Sales.Customer\",\"username\": \"sa\",\"password\":\"abc123\",\"delete\":false}, {\"id\": \"\",\"system\":\"Local AdventureWorks\",\"title\":\"test7\",\"description\": \"\",\"sqlquerystring\":\"select * from AdventureWorks.Sales.Customer\",\"username\": \"sa\",\"password\":\"abc123\",\"delete\":false} ]" } } |
RESPONSE JSON
The reponse will be a JSON document that is made up of one or more items with each item corresponding to each row in the jsondatatable property of the request.
| PROPERTY | DESCRIPTION |
| id | The ID of the Subject created in DataPA. If this was provided in the request then this will just be the same. |
| system | The name of the system the Subject was imported to. |
| title | The title of the subject which was imported. |
| success | A flag to indicate if the import of this Subject was successful or not. |
| errormessage | If the import of this Subject was not successful then the error message that resulted. |
Below is example JSON returned from the request JSON noted above:
| [ { "id": "0ee70a30-ad0e-4794-b7d2-255580158292", "system": "Local AdventureWorks", "title": "test6", "success": true, "errormessage": "" }, { "id": "4bdb7282-8015-49ed-805f-2bed1c3ba761", "system": "Local AdventureWorks", "title": "test7", "success": true, "errormessage": "" } ] |
EXAMPLE FOR DOWNLOAD
A VB.NET application that uses this API is available here (
NB:This uses the NewtonSoft JSON.NET assembly to converting a .NET datatable to and from JSON.