cancel
Showing results for 
Search instead for 
Did you mean: 

Data Gateway Add Connection API

Saravanan
Contributor III

I am trying to connect with a database inside Archer DB server using Archer OOB Flexible SQL Data Gateway Connector.  I am able to generate the session token, but getting error with Add Connection API. 

 

We are using Archer 6.7.00000.1184.  The same connection string is working through a data feed.  Kindly help me to resolve the issue.

 

Below is my JSON request and the error i am getting

 

API URL: http://xxx.xx.xx/RSAArcher/platformapi/DataGateway/Connections

JSON Request:

{
"TargetId": 374,
"Connection": {
"LocationAlias": "Test_DG1",
"DatagatewayConnector": {
"ConnectorType": "ArcherTech.Datasource.Content.GenericSql.GenericSqlDataSource",
"Properties": [
{
"Name": "dbProviderType",
"Value": "System.Data.SqlClient"
},
{
"Name": "connectionString",
"Value": "Data Source=ARCHER6-6\MSSQLXX;Initial Catalog=Test_DB_DataG;User ID=User12;Password=XXX;TransparentNetworkIPResolution=False"
},
{
"Name": "tableName",
"Value": "dbo.Table_1"
},
{
"Name": "idColumnName",
"Value": "Field_Id"
},
{
"Name": "idColumnType",
"Value": "smallint"
}
]
}
}
}

 

Error Response:

{"Message":"An error has occurred.","ExceptionMessage":"Object reference not set to an instance of an object.
","ExceptionType":"System.NullReferenceException","StackTrace":"
at ArcherApi.Controllers.ModuleDataGatewayController.Post(DataGateway moduleDataGateway)\r\n
at lambda_method(Closure , Object , Object[] )\r\n
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass6_1.<GetExecutor>b__3(Object instance, Object[] methodParameters)\r\n
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)\r\n---
End of stack trace from previous location where exception was thrown ---\r\n
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n
at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__1.MoveNext()\r\n---
End of stack trace from previous location where exception was thrown ---\r\n
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n
at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__5.MoveNext()\r\n---
End of stack trace from previous location where exception was thrown ---\r\n
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n
at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__15.MoveNext()"}

22 REPLIES 22

DavidFreeman
Archer Employee
Archer Employee

Howdy,

 

Take a look at the elements.  This should get you going in the right direction.

 

pastedImage_1.png

Hi David,

 

I have updated JSON details, but still facing the same error:-( .  Does it require any other pre-settings to be configured? 

Hi,

 

What exactly is the "Id" that you're using.  That should be the ID of the DG connector, not the module or level.   Unfortunately, there is nowhere on the front end to identify that value.  You can use the API to pull that information.  Here's some SQL that can help, as well.

 

Note that this SQL or any other provided is not supported by RSA and you will use it at your own risk.  Take backups and other appropriate measures before running these scripts.

 

David

Hi,

 

Thanks for the queries.  I will take required backups to run the query and get back to you on that.  But in the Data Connector week 2 demo (https://community.rsa.com/community/products/archer-grc/archer-customer-partner-community/blog/2020/01/17/ffth-follow-up-flexible-sql-connector-data-gateway-rest-api ) and the example api provided in the documentation does not include this Id variable.  Is this version specific to include this Id variable for Add Connection api?

 

Saravanan

Sorry, you're right.  I didn't see it was AddConnection.  The TargetId is the levelid, not the module, correct?

Yes, its the Level id not the module id.  But facing that issue anyways.

Hi Jeff,

 

Its throwing the same error.  But i found the issue.  Archer API template application showed an additional information that JSON format is invalid.  The backslash in the Data Source is causing the issue.  I tried to escape it by adding two slashes.  But datasource is saved in the backed as "Data Source=ARCHER6-6\\MSSQLXX;Initial Catalog=Test_DB_DataG;User ID=User12;Password=XXX;TransparentNetworkIPResolution=False".  I need to find a way to escape backslash character, to make it work.

Saravanan
Contributor III

I am still facing the issue with "\" character in the data source.  If anyone handled backslash character in you Add Connection API, please let me know the details to resolve the issue.