06-23-2023 11:52 AM
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.
Solved! Go to Solution.
06-26-2023 08:41 AM - edited 06-26-2023 08:43 AM
@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
06-23-2023 01:38 PM - edited 06-23-2023 01:38 PM
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
06-26-2023 02:55 AM
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.
06-23-2023 02:46 PM
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
06-26-2023 02:58 AM
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.
06-26-2023 08:41 AM - edited 06-26-2023 08:43 AM
@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
06-26-2023 08:51 AM
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!