Inserting data into a SQL table within a business rule

Forum|Forum|2 years ago
November 8, 2023
2 replies
0 views

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

Methodology

Best answer by tledet

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

T

tledet

OneStream Employee

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.

M

MarcoAuthor

Contributor

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')