1. Beatrix Willius
  2. Valentina Studio
  3. Friday, March 29 2024, 01:38 PM
  4.  Subscribe via email
Following the discussion at https://valentina-db.com/en/discussions/9968-improve-speed-of-sql-query I made a new field and a trigger to get a faster performance.

The final piece is to update existing data to the new field. I can select "PathSort(MailboxPathNatSort)" fine:

select RecID, MailboxPath, WhereClause, PathSort(MailboxPathNatSort) from mailbox order by 4

But if I want to update the new field then I get a kernel error:

UPDATE mailbox SET MailboxPathNormalised = PathSort(mailbox.MailboxPathNatSort)

See the attached screenshots for the result of the 2 queries. What is wrong with the update query?
Attachments (2)
Comment
There are no comments made yet.
Ivan Smahin Accepted Answer
Looks weird. "Unexpected token '5'" - just no idea where it comes from. Probably some hidden parsing for vStudio internals...
As for me the query looks fine.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 1
Ruslan Zasukhin Accepted Answer
And on the second picture letter 't' looks to be selected.

Maybe some hidden character there?

SQL Editor has a button - to show hidden chars, try it
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 2
Beatrix Willius Accepted Answer
No, it's not the invisible chars. I also tried an old and more simple update query. It doesn't work on this computer (screenshot 1) but works on the second computer (screenshot 2).
Attachments (2)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 3
Beatrix Willius Accepted Answer
Of course, it's the database and not the computer. Yesterday I added a trigger to the mailbox table. But why would a trigger create a syntax error in an update query?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 4
Beatrix Willius Accepted Answer
As I thought the trigger is the problem. I expected to see a syntax error when creating the trigger but the error shows up when the trigger is triggered.

What is wrong with the trigger?


CREATE OR REPLACE TRIGGER update_mailboxpathnormalised
AFTER INSERT OR UPDATE ON mailbox
REFERENCING NEW AS NewRow
FOR EACH ROW
BEGIN
DECLARE @normalized_path STRING;
SET @normalized_path = PathSort(NewRow.MailboxPathNatSort);
DECLARE @query STRING;
SET @query = 'UPDATE mailbox SET mailboxpathnormalised = ''' + @normalized_path + ''' WHERE RecID = ' + NewRow.ReciD;

EXECUTE @query;
END;
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 5
Ivan Smahin Accepted Answer
Hmm, why do you need to construct and execute an update statement?
I guess the trigger body should be as simple as

NewRow.mailboxpathnormalised = PathSort(NewRow.MailboxPathNatSort);
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 6
Beatrix Willius Accepted Answer
I only work with triggers every couple of years. I need to add the new line to an existing trigger. But this gives me a syntax error:


create or replace trigger change_mailboxname
after update on mailbox
referencing new as NewRow
for each row
begin
set NewRow.mailboxpathnormalised = PathSort(NewRow.MailboxPathNatSort);
set @NewValue = list_nthitem(NewRow.MailboxPath, list_itemcount(NewRow.MailboxPath, ':::'), ':::');
set @ID = NewRow.ReciD;
declare query string;
set query = 'update Message set MainMailbox = @NewValue where Message.MailboxID = @ID';
execute query;
end;


13:43:13 Kernel error: 0x91504. Trigger "change_mailboxname": time "AFTER" is not supported in this context.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 7
Ivan Smahin Accepted Answer
I mean something like this one:


CREATE OR REPLACE FUNCTION PathSort(thePath STRING) RETURNS STRING
BEGIN
CASE
WHEN LOWER(list_nthitem(thePath, 1, ':::')) = 'inbox' THEN
RETURN CONCAT('1:', REPLACE(thePath, ':::', CHR(10)));

WHEN LOWER(list_nthitem(thePath, 1, ':::')) = 'sent' THEN
RETURN CONCAT('2:', REPLACE(thePath, ':::', CHR(10)));

WHEN LOWER(list_nthitem(thePath, 1, ':::')) = 'sent messages' THEN
RETURN CONCAT('2:', REPLACE(thePath, ':::', CHR(10)));

WHEN thePath = 'Trash' THEN
RETURN CONCAT('7:', REPLACE(thePath, ':::', CHR(10)));

WHEN thePath = 'Spam' THEN
RETURN CONCAT('3:', REPLACE(thePath, ':::', CHR(10)));

WHEN LOWER(list_nthitem(thePath, 1, ':::')) = 'views' THEN
RETURN CONCAT('4:', REPLACE(thePath, ':::', CHR(10)));

WHEN LOWER(list_nthitem(thePath, 1, ':::')) = 'Mailboxes' THEN
RETURN CONCAT('5:', REPLACE(thePath, ':::', CHR(10)));

ELSE
RETURN CONCAT('6:', REPLACE(thePath, ':::', CHR(10)));
END CASE
END



create table t1 ( f1 string, f2 String )

--
CREATE OR REPLACE TRIGGER update_mailboxpathnormalised
BEFORE INSERT OR UPDATE ON t1
REFERENCING NEW AS NewRow
FOR EACH ROW
BEGIN
NewRow.f2 = PathSort(NewRow.f1);
END;



Test it:

insert into t1 (f1) values ( '::abc::' );
select * from t1;
-- ::abc::, '6:::abc::'



update t1 set f1 = ':aa:' where recid = 1
select * from t1;
-- :aa:, '6::aa:'
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 8
Ivan Smahin Accepted Answer
Kernel error: 0x91504. Trigger "change_mailboxname": time "AFTER" is not supported in this context.



...
after update on mailbox
...
set NewRow.mailboxpathnormalised = PathSort(NewRow.MailboxPathNatSort);
...


Look, it makes sense - that "set" statement makes no sense "after update", so you should use "before" time to change mailboxpathnormalised value before real "update" happens
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 9
Ivan Smahin Accepted Answer
Now you want to update linked record in Message table...

set @NewValue = list_nthitem(NewRow.MailboxPath, list_itemcount(NewRow.MailboxPath, ':::'), ':::');
set @ID = NewRow.ReciD;
declare query string;
set query = 'update Message set MainMailbox = @NewValue where Message.MailboxID = @ID';
execute query;


Yes, it should work, the only thing - you are using "global vars" just to build the query string. I would recommend to use "local vars" instead (no big deal, just accuracy). Something like this one:

declare NewValue STRING;
set NewValue = list_nthitem(NewRow.f1, list_itemcount(NewRow.f1, ':::'), ':::');
declare ID INTEGER;
set ID = NewRow.ReciD;
declare query string;
set query = 'update Message set MainMailbox = \'' + NewValue + '\' where Message.MailboxID = ' + ID;
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 10
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories