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

     

     

    • DanielWillis's avatar
      DanielWillis
      Valued Contributor
      2 years ago

      Is there no error?