Forum Discussion

Marco's avatar
Marco
Contributor II
2 years ago

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 
Methodology
  • tledet's avatar
    tledet
    2 years ago

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

  • tledet's avatar
    tledet
    New Contributor III
    2 years ago

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

    • Marco's avatar
      Marco
      Contributor II
      2 years ago

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

  • tledet's avatar
    tledet
    New Contributor III
    2 years ago
    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.
    • Marco's avatar
      Marco
      Contributor II
      2 years ago

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