Hi,

me with an odd problem again. A user updated a couple of databases from about 2 years ago. For each database he got an error after updating the database scheme:

2022-06-01 09:27:50 Message: Error in function name "PathSort"., Error Number: 591118, Stack: RuntimeRaiseException _Z23throwValentineExceptionjRKN3fbl6StringE _Z12ProceedErrorRN3fbl10xExceptionE _Z20Database_SqlSelect_1P16REALobjectStructP16REALstringStructiii VDatabase.SqlSelect%o<VCursor>%o<VDatabase>si8i8i8 VCursorController.Query%b%o<VCursorController> MainWindow.MainWindow.LoadData%%o<MainWindow.MainWindow> MainWindow.MainWindow.Event_Requery%%o<MainWindow.MainWindow>
2022-06-01 09:27:50 select RecID, MailboxPath, WhereClause, PathSort(MailboxPathNatSort) from mailbox order by 4
2022-06-01 09:27:50 Requery finished

The error only showed up directly after updating the database and then not again. A diagnosis said that the databases are okay.

The PathSort function is the basis of my database scheme:

try

  'add function for proper sorting of mailbox paths

  theSQL = "CREATE OR REPLACE"

  theSql = theSql + " FUNCTION PathSort(thePath STRING) RETURNS STRING BEGIN CASE"

  theSql = theSql + " WHEN LOWER(list_nthitem(thePath, 1, '" + globals.MailboxDivider + "')) = 'inbox' THEN RETURN concat('1:',"

  theSql = theSql + " REPLACE(thePath, '" + globals.MailboxDivider + "', chr(10)));"

  theSql = theSql + " WHEN LOWER(list_nthitem(thePath, 1, '" + globals.MailboxDivider + "')) = 'sent' THEN RETURN concat('2:',"

  theSql = theSql + " REPLACE(thePath, '" + globals.MailboxDivider + "', chr(10)));"

  theSql = theSql + " WHEN LOWER(list_nthitem(thePath, 1, '" + globals.MailboxDivider + "')) = 'sent messages' THEN RETURN"

  theSql = theSql + " concat('2:', REPLACE(thePath, '" + globals.MailboxDivider + "', chr(10)));"

  theSql = theSql + " WHEN thePath = 'Trash' THEN RETURN concat('7:', REPLACE(thePath, '" + globals.MailboxDivider + "',chr(10)));"

  theSql = theSql + " WHEN thePath = 'Spam' THEN RETURN concat('3:', REPLACE(thePath, '" + globals.MailboxDivider + "', chr(10)));"

  theSql = theSql + " WHEN LOWER(list_nthitem(thePath, 1, '" + globals.MailboxDivider + "')) = 'views' THEN RETURN concat('4:',REPLACE(thePath, '" + globals.MailboxDivider + "', chr(10)));"

  theSql = theSql + " WHEN LOWER(list_nthitem(thePath, 1, '" + globals.MailboxDivider + "')) = 'Mailboxes' THEN RETURN concat('5:', REPLACE(thePath, '" + globals.MailboxDivider + "', chr(10)));"

  theSql = theSql + " ELSE RETURN concat('6:', REPLACE(thePath, '" + globals.MailboxDivider + "', chr(10)));"

  theSql = theSql + " END CASE END"

  theError = ValentinaDB.SqlExecute(theSQL)

catch Err as VException

  FinalError = theError

  Globals.theErrorLog.LogItem "Error creating trigger: " + theSql

  Globals.theErrorLog.LogItem "Error: " + str(Err.ErrorNumber)

end try

Any idea what might cause the error? Of course, I can't reproduce the behaviour myself.