11-09-2023 01:35 AM
I need to delete a single record in the Error Log but my Data Adapter won't let me, complaining about "unsafe SQL statements".
In a rather bone-headed move, while developing a BR, I accidentally wrote an insanely large block of text to the Error Log -- like somewhere between 1.3 and 1.5 million characters -- in a single record via BRApi.ErrorLog.LogMessage.
It's made the Error Log workspace page pretty much unusable because it's trying to read that record to display it. It takes 5 or 6 minutes to display the Error Log.
After finding the UniqueID for the offending record in the ErrorLog table, I tried the following SQL in a Data Adapter to remove the record:
DELETE TOP (1) from myOneStream_Framework.dbo.ErrorLog where UniqueID = 'bb5158a6-aae9-471c-b883-edddc8640a5e'
Apparently, OneStream thinks I am a cyber-criminal and blocked my code execution with the following message:
Database query error. The specified SQL statement is not safe.
I also tried:
UPDATE myOneStream_Framework.dbo.ErrorLog
SET Description = 'log entry trimmed'
where UniqueID = 'bb5158a6-aae9-471c-b883-edddc8640a5e'
And was again rewarded with a resounding slap on the hand:
Database query error. The specified SQL statement is not safe.
Any suggestions on how I can get rid of this monstrous record?
Solved! Go to Solution.
11-09-2023 02:00 AM - edited 11-09-2023 02:01 AM
Hey Kurt
You can't execute an insert/delete statement from a SQL adapter. You'll need to do this from a Business Rule.
I normally just create an Extender Business Rule. Something like this should work:
Namespace OneStream.BusinessRule.Extender.ExecuteSQL
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
Try
'Create connection to Framework database
Using dbConn As DbConnInfo = BRApi.Database.CreateFrameworkDbConnInfo(si)
'Execute SQL
BRApi.Database.ExecuteActionQuery(dbconn, "UPDATE ErrorLog Set Description = 'log entry trimmed' Where UniqueID = 'bb5158a6-aae9-471c-b883-edddc8640a5e'", False, True)
End Using
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace
Then Execute it using the play button:
Regards,
Mark
11-09-2023 02:00 AM - edited 11-09-2023 02:01 AM
Hey Kurt
You can't execute an insert/delete statement from a SQL adapter. You'll need to do this from a Business Rule.
I normally just create an Extender Business Rule. Something like this should work:
Namespace OneStream.BusinessRule.Extender.ExecuteSQL
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
Try
'Create connection to Framework database
Using dbConn As DbConnInfo = BRApi.Database.CreateFrameworkDbConnInfo(si)
'Execute SQL
BRApi.Database.ExecuteActionQuery(dbconn, "UPDATE ErrorLog Set Description = 'log entry trimmed' Where UniqueID = 'bb5158a6-aae9-471c-b883-edddc8640a5e'", False, True)
End Using
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace
Then Execute it using the play button:
Regards,
Mark
11-09-2023 02:10 AM
Mark -- worked like a charm!!! Thanks!
Unrelated, how do you insert a 'code block' like you did with your code example? I'm used to seeing an icon that essentially inserts the <code> tag.
11-09-2023 02:15 AM
No problem.
Step1
Step2
Then just make sure you choose the correct language:
11-09-2023 02:20 AM
That's what I was looking for and what I've used in other forum sites! Guess what's not available when you create a post? Not sure why inserting a code block is only available in a Reply, but thanks for pointing me in the right direction.
11-09-2023 02:24 AM
Yeah, it's actually a bug that OneStream are working on. Hopefully it will be sorted soon!
11-09-2023 04:44 AM
Hi, you are probably aware of this option, but posting this for interested folks: There is an option to write error logs into a file rather than the actual error log to avoid exactly what happened here ( 😉 ). Not only for single entries into the error log, but sometimes users write potentially hundreds of thousands of error logs into the error log, flooding it that way.
[How to] Log into a file instead of the Error Log - OneStream Community (onestreamsoftware.com)