Skip to main content
Marco
Contributor
Nov 8, 2023 9:03 PM UTC
Solved

Inserting data into a SQL table within a business rule

Hi everyone. I'm trying to add values to a SQL table, I have checked with several functions of the BRApi.Database, but none of them work for me. My code example:

#The variables

Dim sqlTest As String = $"INSERT INTO AllocationResults (Step, Amount, Annotation, SourceRule, DriverRule, Type, StepDesc, TargetRule, AllocRule, Generation, Cube, Entity, Consolidation, Scenario, Time, View, Account, Flow, Origin, Ic) VALUES (1, {Amount}, {Annotation}, {SourceRule}, {DriverRule}, {Type}, {StepDesc}, {TargetRule}, {AllocRule}, {Generation}, {Cube}, {Entity}, {Consolidation}, {Scenario}, {Time}, {View}, {Account}, {Flow}, {Origin}, {Ic})"
 
Using dbConnApp As DBConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)
     BRApi.Database.ExecuteActionQuery(dbConnApp, sqlTest, False,True)
End Using 
tledetAnswer
OneStream Employee
Nov 8, 2023 11:26 PM UTC

Change "View" to [View] as view is a reserved T-SQL KEYWORD

View in thread

5 replies

OneStream Employee
tledetOneStream Employee
OneStream Employee
Nov 8, 2023 9:48 PM UTC
With an extensibility rule,
Dim sqlStatement As String = String.Empty
sqlStatement = INSERT INTO [TableName] ([FieldName]) VALUES ('FieldNameValues') replace TableName with AllocationResults and FieldName and FieldNameVales based on the SQL provided.
Next
Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
BRApi.Database.ExecuteSql(dbConnApp, sqlStatement, False)
End Using
May be necessary to log the SQL i.e., BRApi.ErrorLog.LogMessage(si,$"sql = {sqlStatement}") for troubleshooting purposes.
MarcoAuthor
Contributor
Nov 8, 2023 11:03 PM UTC

Hi, the result of the SQL is this, but when checking the Application Database table, no values have been entered.

 

 

INSERT INTO AllocationResults (Step, Amount, Annotation, SourceRule, DriverRule, Type, StepDesc, TargetRule, AllocRule, Generation, Cube, Entity, Consolidation, Scenario, Time, View, Account, Flow, Origin, Ic, UD1, UD2, UD3, UD4, UD5, UD6, UD7, UD8) VALUES (1, -1197045.224463520, 'A_PL_32906_D1201_PL_32911: (Source Value 1,197,045.22 * PctSplit 100.00 %) * (Driver Value 1.00 / Driver Total 1.00) * FxRate 1.0000 * Alloc Sign -1', 'S_PL_32906_D1201', 'D_PL_32906_D1201', 'NetOff', 'Step 1', 'T_PL_32906_PL_32911', 'A_PL_32906_D1201_PL_32911', 1.01, 'HLFPLN', '32902_32906', 'Local', 'ForecastAllocation', '2023M1', 'Periodic', 'cra_639950', 'OutCostsAllocation', 'BeforeAdj', 'None', 'D1201', 'P0000', 'LOCAL_DATA', 'PJ00000', 'None', 'None', 'None', 'None')

 

 

DanielWillis
Contributor
Nov 8, 2023 11:31 PM UTC

Is there no error?

OneStream Employee
tledetOneStream EmployeeAnswer
OneStream Employee
Nov 8, 2023 11:26 PM UTC

Change "View" to [View] as view is a reserved T-SQL KEYWORD

    MarcoAuthor
    Contributor
    Nov 9, 2023 2:11 PM UTC

    Thank you very much, this was what I was missing, I appreciate it a lot

    Terms & ConditionsAccessibility statement