Article Details
Id:21222
Product:finPOWER Connect
Type:NEW
Version:4.01.00
Opened:28/02/2024
Closed:29/02/2024
Released:29/04/2024
Job: J032992
High Importance

Business Layer; Ability to Add/Update/Delete records in Custom database tables has been added

The ability for Scripts to add, update and delete records in custom database tables is now available.

NOTE: Custom tables must have the same permissions as system tables, e.g., in SQL Server, the "appPOWER" role must have the correct permissions for all custom tables that need to be updated using this new functionality.

A "Custom" database table is one that is named starting with one of the following prefixes:

  • Custom_
  • Cust_

NOTE: When using the ISUpdateQueryBuilder with custom tables, Joins cannot currently be used, for security reasons.

Public Function Main(parameters As ISKeyValueList) As Boolean
  
  Dim dqb As ISDeleteQueryBuilder 
  Dim Pk As Integer
  Dim Success As Boolean
  Dim uqb As ISUpdateQueryBuilder 

  ' Assume Success
  Success = True

  ' Add
  If Success Then
    uqb = finBL.Database.CreateUpdateQueryBuilderForCustomTable()
    With uqb
      .Table = "Custom_Test"
      .SetIdentity("Pk")      
      .SetString("Name", "John Smith")
      .SetDateTime("UpdatedUtcDate", finBL.TimeZoneFunctions.GetCurrentUtcDateTime())
      
      Success = .ExecuteAddNew(Pk)
    End With
  End If
  
  ' Update
  If Success Then
    uqb = finBL.Database.CreateUpdateQueryBuilderForCustomTable()
    With uqb
      .Table = "Custom_Test"
      .SetString("Name", "Smith, John")
      .SetDateTime("UpdatedUtcDate", finBL.TimeZoneFunctions.GetCurrentUtcDateTime())
      
      With .SqlWhere 
        .AppendComparisonInteger("Pk", "=", Pk)
      End With
      
      Success = .ExecuteUpdateSingle()
    End With
  End If

  ' Delete
  If Success Then
    dqb = finBL.Database.CreateDeleteQueryBuilderForCustomTable()
    With dqb
      .Table = "Custom_Test"
      
      With .SqlWhere 
        .AppendComparisonInteger("Pk", "=", 2)
      End With
      
      Success = .ExecuteDeleteSingle()
    End With
  End If

  Return Success

End Function