Forum Discussion

Montreux's avatar
Montreux
New Contributor III
2 years ago

ID Field in Custom SQL and Setting a Unique Default for New Rows

Hi,

I have a custom SQL table for people planning and want an ID field as my primary key. 

(The existing fields that would be good for a primary key need to editable (e.g. entry date) so I cannot use a set of existing fields as my primary key.  Setting a field as a primary key makes that field not editable.) 

My question is how to set a field's default with a SQL code, "select NEWID()" which generates a unique code?  I tried creating a parameter as a Bound List and SQL code with this query but when the dashboard with the SQL table editor is opened I get a pop up window asking for an input for this parameter.  

How are primary keys normally handled in this case?  Thanks.

Dashboards
  • RobbSalzmann's avatar
    RobbSalzmann
    2 years ago

    Montreux Yes, I use Dashboard Extenders for this.  Here's some example code to help you get started.  I almost never use the STE's internal save/update functionality.  Besides being fussy and undocumented, it doesn't (that I know of) stub out for data validation.

     

    Private Function SaveRecords(si As SessionInfo, args As DashboardExtenderArgs) As Object
  Dim strSql As String = String.Empty
  Dim strDate As String = String.Empty
  Dim saveDataTaskResult As Object = Nothing
  Dim pKTheId As String = String.Empty
  Dim strValidationMsg = String.Empty

  Try
    'Not cancelling the default save even though nothing will be saved using the STE allows the UI to refresh with the new values
    saveDataTaskResult = New XFSqlTableEditorSaveDataTaskResult() With {
      .IsOK = True,
      .ShowMessageBox = False,
      .Message = "",
      .CancelDefaultSave = False
    }
    pKTheId = args.NameValuePairs.XFGetValue("pKTheId", "0")
    For Each row As XFEditedDataRow In args.SqlTableEditorSaveDataTaskInfo.EditedDataRows
      'Add any data validation here
      strValidationMsg = ValidateRecord(si, args, row)
      strDate = Date.Parse(row.ModifiedDataRow.Item("CustomDate")).ToString("yyyy-MM-dd")
      strSql = $"UPDATE XFW_CustomTable SET CustomAmount={row.ModifiedDataRow.Item("CustomAmount")}, Descr='{row.ModifiedDataRow.Item("Descr")}' WHERE CustomDate='{strDate}' and pKTheId={pKTheId}"
      Using DbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
        BRApi.Database.ExecuteSql(DbConn, strSQL, True)
      End Using
    Next
    'We already added the new/updated records.  No need to use the SqlTableEditor to do it - so we clear them here.
        args.SqlTableEditorSaveDataTaskInfo.EditedDataRows.RemoveAll(Function(x) x.InsertUpdateOrDelete = DbInsUpdateDelType.Insert)
        args.SqlTableEditorSaveDataTaskInfo.EditedDataRows.RemoveAll(Function(x) x.InsertUpdateOrDelete = DbInsUpdateDelType.Update)

    Catch ex As Exception
         Throw New XFException($"{Environment.NewLine}{Me.GetType().ToString()}.{System.Reflection.MethodBase.GetCurrentMethod().Name}(): {ex.Message}:{Environment.NewLine}{strSql}", ex)
    End Try
  
  Return saveDataTaskResult
End Function

private function ValidateRecord(si As SessionInfo, args As DashboardExtenderArgs, row As XFEditedDataRow) As String
  Dim strValidationMsg as String = String.Empty
  'validation code here...
  
  return strValidationMsg
End Function


Private Function GetNextpKTheId(si As SessionInfo) As String
    Dim pKTheId As String = String.Empty
  Dim strSql as String = String.Empty

    Try
    strSql = "SELECT COALESCE(MAX(pKTheId), 0)+1 as NextpKTheId From XFW_CustomTable"
    Using DbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
      pKTheId = BRApi.Database.ExecuteSql(DbConn, strSQL, True).Rows(0).Item("NextpKTheId")
    End Using
     Catch ex As Exception
        Throw New XFException($"{Environment.NewLine}{Me.[GetType]().ToString()}.{System.Reflection.MethodBase.GetCurrentMethod().Name}(): {ex.Message}:{Environment.NewLine}{strSql}", ex)
    End Try

    Return pKTheId
End Function

     

  • Omkareshwar's avatar
    Omkareshwar
    Contributor II
    2 years ago

    You can use GUID to have a primary key 

     

    				Dim sSQL As New Text.StringBuilder
				Dim sGUID As Guid = Guid.NewGuid 'Create a new Unique ID
				
				sSQL.Append($"INSERT INTO TableName VALUES (")
				sSQL.Append($"'{sGUID}','{sUserName}',GETDATE(),'{sPOV}')")
				
				'BRApi.ErrorLog.LogMessage(si,"SQL",sql.ToString)
				
				Using DbConn
					DbConn.BeginTrans()
					BRApi.Database.ExecuteActionQuery(DbConn,sSQL.ToString,False,True)		
					DbConn.CommitTrans()
				End Using

     

     

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

      Hi, thanks for your idea.  I'm curious where this is run from?  A Dashboard Extender rule to update the table?  My concern is that if 2 identical rows are saved from the SQL table editor or Spreadsheet, the problem exists before this rule can be run to make the 2 rows unique with the snippet you've shared.  I thought setting a field's default value would be best suited for this.  Thanks for your input.

  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    2 years ago

    If you want to create an integer ID field in your table that is in numerical order, you can use the following:

    Dim strSql as String = "SELECT COALESCE(MAX(MyID), 0)+1 as MyID From MyTable"
Dim myId as Integer = 0
Using DbConn
	DbConn.BeginTrans()
	myId = BRApi.Database.ExecuteActionQuery(DbConn,strSql,False,True).Rows(0).Item("MyId")		
	DbConn.CommitTrans()
End Using
    • Montreux's avatar
      Montreux
      New Contributor III
      2 years ago

      Hi, thanks also for you input.  I'll ask the same question to you that I did for the other good response to my question.  Where this is run from?  A Dashboard Extender rule?  My concern is that if 2 identical rows are saved from the SQL table editor or Spreadsheet then the problem exists before this rule can be run to make the 2 rows unique.  The SQL table editor's field's default value would be best suited for this but SQL rule don't work here.  Thanks again.

      • RobbSalzmann's avatar
        RobbSalzmann
        Valued Contributor II
        2 years ago

        Montreux Yes, I use Dashboard Extenders for this.  Here's some example code to help you get started.  I almost never use the STE's internal save/update functionality.  Besides being fussy and undocumented, it doesn't (that I know of) stub out for data validation.

         

        Private Function SaveRecords(si As SessionInfo, args As DashboardExtenderArgs) As Object
  Dim strSql As String = String.Empty
  Dim strDate As String = String.Empty
  Dim saveDataTaskResult As Object = Nothing
  Dim pKTheId As String = String.Empty
  Dim strValidationMsg = String.Empty

  Try
    'Not cancelling the default save even though nothing will be saved using the STE allows the UI to refresh with the new values
    saveDataTaskResult = New XFSqlTableEditorSaveDataTaskResult() With {
      .IsOK = True,
      .ShowMessageBox = False,
      .Message = "",
      .CancelDefaultSave = False
    }
    pKTheId = args.NameValuePairs.XFGetValue("pKTheId", "0")
    For Each row As XFEditedDataRow In args.SqlTableEditorSaveDataTaskInfo.EditedDataRows
      'Add any data validation here
      strValidationMsg = ValidateRecord(si, args, row)
      strDate = Date.Parse(row.ModifiedDataRow.Item("CustomDate")).ToString("yyyy-MM-dd")
      strSql = $"UPDATE XFW_CustomTable SET CustomAmount={row.ModifiedDataRow.Item("CustomAmount")}, Descr='{row.ModifiedDataRow.Item("Descr")}' WHERE CustomDate='{strDate}' and pKTheId={pKTheId}"
      Using DbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
        BRApi.Database.ExecuteSql(DbConn, strSQL, True)
      End Using
    Next
    'We already added the new/updated records.  No need to use the SqlTableEditor to do it - so we clear them here.
        args.SqlTableEditorSaveDataTaskInfo.EditedDataRows.RemoveAll(Function(x) x.InsertUpdateOrDelete = DbInsUpdateDelType.Insert)
        args.SqlTableEditorSaveDataTaskInfo.EditedDataRows.RemoveAll(Function(x) x.InsertUpdateOrDelete = DbInsUpdateDelType.Update)

    Catch ex As Exception
         Throw New XFException($"{Environment.NewLine}{Me.GetType().ToString()}.{System.Reflection.MethodBase.GetCurrentMethod().Name}(): {ex.Message}:{Environment.NewLine}{strSql}", ex)
    End Try
  
  Return saveDataTaskResult
End Function

private function ValidateRecord(si As SessionInfo, args As DashboardExtenderArgs, row As XFEditedDataRow) As String
  Dim strValidationMsg as String = String.Empty
  'validation code here...
  
  return strValidationMsg
End Function


Private Function GetNextpKTheId(si As SessionInfo) As String
    Dim pKTheId As String = String.Empty
  Dim strSql as String = String.Empty

    Try
    strSql = "SELECT COALESCE(MAX(pKTheId), 0)+1 as NextpKTheId From XFW_CustomTable"
    Using DbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
      pKTheId = BRApi.Database.ExecuteSql(DbConn, strSQL, True).Rows(0).Item("NextpKTheId")
    End Using
     Catch ex As Exception
        Throw New XFException($"{Environment.NewLine}{Me.[GetType]().ToString()}.{System.Reflection.MethodBase.GetCurrentMethod().Name}(): {ex.Message}:{Environment.NewLine}{strSql}", ex)
    End Try

    Return pKTheId
End Function

         

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

    This makes a lot of sense now.  Running the rule to first validate, then set the ID, then save is the order I'm looking for.  Thanks a lot!