02-08-2024 09:03 AM - last edited on 02-08-2024 11:33 AM by JackLacava
Hi All,
does anyone built a Smart Integration Function and compressed the datatable with CompressJsonObject and then, in the extensibility rule, inflate it with InflateJsonObject using the variable bulkRemoteResults.resultDataCompressed ?
I'm getting the error: "Object reference not set to an instance of an object". If I compress itand inflate it in function worked and in the extensibility rule worked. But, compress in the function and inflate in the extensibility rule didnt work.
This is an example in the guide, so I'm not sure if I'm missing something.
Thx in advance
02-08-2024 09:18 AM
Hi Jorge,
Are you using the example code and its doing this? Post your code and we can put extra eyes on it to help spot the bug if there is one.
02-08-2024 09:57 AM
This is the SIC code
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Data.SqlClient
Imports OneStream.Shared.Common
Imports OneStreamGatewayService
Namespace OneStream.BusinessRule.SmartIntegrationFunction.SIC_Functions
Public Class MainClass
' Function to bulk copy a compressed data table to a SQL database table
' Pass in compressed data table, database table name and data source name
Public Shared Function RunOperation(dtCompress As CompressionResult,tablename As String,
datasource As String) As String
' --------------------------------------------------------------------------------------
---------
' Get SQL connection string
Dim connString As String = APILibrary.GetRemoteDataSourceConnection(datasource)
' Inflate compressed datatable
Dim dt As DataTable = CompressionHelper.InflateJsonObject(Of DataTable)
(New SessionInfo,dtCompress)
If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
' Check data table has been created and is populated
' Create sql connection to DWH
Using sqlTargetConn As SqlConnection = New SqlConnection(connString)
sqlTargetConn.Open ' Open connection
Using bulkCopy = New SqlBulkCopy(sqlTargetConn)
bulkCopy.DestinationTableName = tableName ' DWH table
bulkCopy.BatchSize = 5000
bulkCopy.BulkCopyTimeout = 30
bulkCopy.WriteToServer(dt) ' Bulk copy data table to database table
End Using
End Using
Else
Throw New Exception("Problem uncompressing data in SIC gateway")
End If
Return $"{dt.Rows.Count} rows bulk inserted into table {tableName}"
End Function
End Class
End Namespace
This is the Extensibility code
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
Namespace OneStream.BusinessRule.Extender.SIC_BulkCopyExample
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api
As Object, ByVal args As ExtenderArgs) As Object
Try
' SIC Gateway name
Dim sicGatewayName As String = "jl-db-achqa1-gateway"
' SIC remote rule
Dim sicRemoteRule As String = "SIC_Functions"
' SIC remote rule function
Dim sicRemoteRuleFunction As String = "RunOperation"
' Create and populate DataTable
Dim dt As New DataTable()
dt.Columns.Add("Scenario", GetType(String))
dt.Columns.Add("Time", GetType(String))
dt.Columns.Add("Entity", GetType(String))
dt.Columns.Add("Account", GetType(String))
dt.Columns.Add("Amount", GetType(Double))
dt.Rows.Add("Actual", "2023M3", "Houston Heights", "Net Sales", 100.25)
dt.Rows.Add("Actual", "2023M3", "South Houston", "Net Sales", 1230.66)
' Compress data table before passing into remote business rule
Dim dtCompress As CompressionResult = CompressionHelper.CompressJsonObject
(Of DataTable)(si, dt, XFCompressionAlgorithm.DeflateStream)
Dim dtObj(2) As Object ' Create object to store arguments for remote business rule
dtObj(0) = dtCompress ' compressed datatable
dtObj(1) = "SIC_WriteBack" ' remote database table name
dtObj(2) = "RevenueMgmt" ' remote data source name
' Execute remote business rule to bulk copy to target table
Dim bulkRemoteResults As RemoteRequestResultDto
=BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, sicRemoteRule,
dtObj, sicGatewayName,sicRemoteRuleFunction,String.Empty, False, 600)
' Get result status
If bulkRemoteResults.RemoteResultStatus <>
RemoteMessageResultType.RunOperationReturnObject Then ' Check if successful
' Failed, do something
BRAPi.ErrorLog.LogMessage(si,"Failed with status:" & bulkRemoteResults.
RemoteResultStatus.ToString)
End If
' Get returned message
Dim returnedMsg As String = CompressionHelper.InflateJsonObject(Of String)
(si,bulkRemoteResults.resultDataCompressed)
BRAPi.ErrorLog.LogMessage(si,returnedMsg)
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace
02-08-2024 11:27 AM - edited 02-08-2024 11:28 AM
Above is the original code, I update it this way:
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Data.SqlClient
Imports OneStream.Shared.Common
Imports OneStreamGatewayService
Namespace OneStream.BusinessRule.SmartIntegrationFunction.JH_SIC_Test
Public Class MainClass
Public Shared Function RunOperation(datasource As String) As CompressionResult
' -----------------------------------------------------------------------------------------------
Dim dt As New DataTable()
dt.Columns.Add("Scenario", GetType(String))
dt.Columns.Add("Time", GetType(String))
dt.Columns.Add("Entity", GetType(String))
dt.Columns.Add("Account", GetType(String))
dt.Columns.Add("Amount", GetType(Double))
dt.Rows.Add("Actual", "2023M3", "Houston Heights", "Net Sales", 100.25)
dt.Rows.Add("Actual", "2023M3", "South Houston", "Net Sales", 1230.66)
Dim dataSet As New DataSet()
Dim dtCompress As CompressionResult = CompressionHelper.CompressJsonObject(Of DataTable)(New SessionInfo, dt, XFCompressionAlgorithm.DeflateStream)
Return dtCompress
End Function
End Class
End Namespace
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
Namespace OneStream.BusinessRule.Extender.JH_EXT_Test
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
Try
'Set Parameters
Dim sicGatewayName As String = "gateway" '
Dim sicRemoteRule As String = "JH_SIC_Test"
Dim sicRemoteRuleFunction As String = "RunOperation"
Dim dtObj(0) As Object ' Create object to store arguments for remote business rule
dtObj(0) = "OLEDB"
' Execute remote business rule
Dim bulkRemoteResults As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, sicRemoteRule, dtObj, sicGatewayName, sicRemoteRuleFunction,String.Empty, False, 600)
' Get result status
If bulkRemoteResults.RemoteResultStatus <> RemoteMessageResultType.RunOperationReturnObject Then ' Check if successful
MsgLog.AddLine("Status: " & bulkRemoteResults.RemoteResultStatus.ToString)
End If
' Get returned message
Dim returnedMsg As DataTable = CompressionHelper.InflateJsonObject(Of DataTable)(si,bulkRemoteResults.resultDataCompressed)
Dim result As New Text.StringBuilder
For Each row As DataRow In returnedMsg.Rows
result.AppendLine( row("customer_id").ToString() & "," & row("name").ToString() & "," & row("address").ToString() & "," & row("website").ToString() & "," & row("credit_limit").ToString())
Next
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace
02-08-2024 11:29 AM
You might get to the bottom of this with some checks for null/empty objects. I added a few to see if it will help spot the error:
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Data.SqlClient
Imports OneStream.Shared.Common
Imports OneStreamGatewayService
Namespace OneStream.BusinessRule.SmartIntegrationFunction.SIC_Functions
Public Class MainClass
' Function to bulk copy a compressed data table to a SQL database table
' Pass in compressed data table, database table name and data source name
Public Shared Function RunOperation(dtCompress As CompressionResult, tablename As String, datasource As String) As String
If dtCompress Is Nothing Then Throw New ArgumentNullException(NameOf(dtCompress))
If String.IsNullOrWhiteSpace(tablename) Then Throw New ArgumentException("Table name cannot be null or empty.", NameOf(tablename))
If String.IsNullOrWhiteSpace(datasource) Then Throw New ArgumentException("Data source name cannot be null or empty.", NameOf(datasource))
' Get SQL connection string
Dim connString As String = APILibrary.GetRemoteDataSourceConnection(datasource)
If String.IsNullOrWhiteSpace(connString) Then Throw New InvalidOperationException("Failed to retrieve a valid connection string.")
' Inflate compressed datatable
Dim dt As DataTable = CompressionHelper.InflateJsonObject(Of DataTable)(New SessionInfo, dtCompress)
If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
Using sqlTargetConn As SqlConnection = New SqlConnection(connString)
sqlTargetConn.Open
Using bulkCopy = New SqlBulkCopy(sqlTargetConn)
bulkCopy.DestinationTableName = tablename
bulkCopy.BatchSize = 5000
bulkCopy.BulkCopyTimeout = 30
bulkCopy.WriteToServer(dt)
End Using
End Using
Else
Throw New Exception("Problem uncompressing data in SIC gateway")
End If
Return $"{dt.Rows.Count} rows bulk inserted into table {tablename}"
End Function
End Class
End Namespace
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
Namespace OneStream.BusinessRule.Extender.SIC_BulkCopyExample
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
If si Is Nothing Then Throw New ArgumentNullException(NameOf(si))
Try
' SIC Gateway name
Dim sicGatewayName As String = "jl-db-achqa1-gateway"
If String.IsNullOrWhiteSpace(sicGatewayName) Then Throw New ArgumentException("SIC Gateway name cannot be null or empty.", NameOf(sicGatewayName))
' SIC remote rule
Dim sicRemoteRule As String = "SIC_Functions"
If String.IsNullOrWhiteSpace(sicRemoteRule) Then Throw New ArgumentException("SIC remote rule cannot be null or empty.", NameOf(sicRemoteRule))
' SIC remote rule function
Dim sicRemoteRuleFunction As String = "RunOperation"
If String.IsNullOrWhiteSpace(sicRemoteRuleFunction) Then Throw New ArgumentException("SIC remote rule function cannot be null or empty.", NameOf(sicRemoteRuleFunction))
' Create and populate DataTable
Dim dt As New DataTable()
dt.Columns.Add("Scenario", GetType(String))
dt.Columns.Add("Time", GetType(String))
dt.Columns.Add("Entity", GetType(String))
dt.Columns.Add("Account", GetType(String))
dt.Columns.Add("Amount", GetType(Double))
dt.Rows.Add("Actual", "2023M3", "Houston Heights", "Net Sales", 100.25)
dt.Rows.Add("Actual", "2023M3", "South Houston", "Net Sales", 1230.66)
If dt.Rows.Count = 0 Then Throw New InvalidOperationException("DataTable is empty.")
' Compress data table before passing into remote business rule
Dim dtCompress As CompressionResult = CompressionHelper.CompressJsonObject(Of DataTable)(si, dt, XFCompressionAlgorithm.DeflateStream)
If dtCompress Is Nothing Then Throw New InvalidOperationException("Failed to compress DataTable.")
Dim dtObj(2) As Object ' Create object to store arguments for remote business rule
dtObj(0) = dtCompress ' compressed datatable
dtObj(1) = "SIC_WriteBack" ' remote database table name
dtObj(2) = "RevenueMgmt" ' remote data source name
' Execute remote business rule to bulk copy to target table
Dim bulkRemoteResults As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, sicRemoteRule, dtObj, sicGatewayName, sicRemoteRuleFunction, String.Empty, False, 600)
' Get result status
If bulkRemoteResults.RemoteResultStatus <> RemoteMessageResultType.RunOperationReturnObject Then ' Check if successful
' Failed, do something
BRAPi.ErrorLog.LogMessage(si, "Failed with status:" & bulkRemoteResults.RemoteResultStatus.ToString)
End If
' Get returned message
Dim returnedMsg As String = CompressionHelper.InflateJsonObject(Of String)(si, bulkRemoteResults.resultDataCompressed)
BRAPi.ErrorLog.LogMessage(si, returnedMsg)
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace
02-08-2024 11:32 AM
Thx Robb,
let me take a look!