Forum Discussion

Jorge_Haces's avatar
Jorge_Haces
New Contributor II
12 months ago

Smart Integration Function with CompressJsonObject

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

Connectors
Extenders
  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    12 months ago

    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

     

     



     

     

  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    12 months ago

    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.

  • Jorge_Haces's avatar
    Jorge_Haces
    New Contributor II
    12 months ago

    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

     

     

    • Jorge_Haces's avatar
      Jorge_Haces
      New Contributor II
      12 months ago

      Above is the original code, I update it this way:

      • Return a CompressionResult type in the SIC BR

       

      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
      • Receive it an inflate it, in the Extensible BR
      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