1. Beatrix Willius
  2. Valentina Database ADK
  3. Donnerstag, März 23 2017, 04:00 PM
  4.  Abonnieren via E-Mail
After trying to speed up my function with the help of Ivan I've found out that the simpler function doesn't make the select faster. Therefore, I wanted to try the methods. But the documentation is a bit thin.

http://valentina-db.com/docs/dokuwiki/v7/doku.php?id=valentina:vcomponents:vkernel:table:methods

says

NOTE: It is similar to most computer languages that contain VARIABLES and FUNCTIONS. You can read the value of variable and assign a new value to it.


In the examples I found:


cmd = cmd + "mfld_string STRING(40) METHOD('upper(fld_string)'), "
cmd = cmd + "mfld_varchar VARCHAR(2000) METHOD('upper(fld_varchar)'), "


Do the methods have at least a case function? What about nthfield? The lovely lpad? Is the syntax similar to the SQL syntax? Can I simply copy the SQL from the function into the method?

Mit freundlichen Grüßen/Regards

Beatrix Willius

http://www.mothsoftware.com
Mail Archiver X: The email archiving solution for professionals
Kommentar
There are no comments made yet.
Ruslan Zasukhin Akzeptierte Antwort
Hi Beatrix,

Inside of METHOD() you can use any valid SQL EXPRESSION.

so yes, sin, cos, trim, nth, everything you can find in the section of WIKI

VSQL / Expressions
Kommentar
There are no comments made yet.
Beatrix Willius Akzeptierte Antwort
Hi Ruslan,

thanks for the fast answer. I played around 5 minutes in ValentinaStudio and didn't get a result. Will try again tomorrow.

Mit freundlichen Grüßen/Regards

Beatrix Willius

http://www.mothsoftware.com
Mail Archiver X: The email archiving solution for professionals
Kommentar
There are no comments made yet.
Beatrix Willius Akzeptierte Antwort
I can't get this to work. There is a table called "Mailbox" with a field "MailboxPath".

Simple stuff for method definition works fine:


right( list_nthitem( MailboxPath, 1, ':::' ), 1 )


But when I use a simple case statement:


CASE when right( list_nthitem( MailboxPath, 1, ':::' ), 1 ) = 0 And right( list_nthitem( MailboxPath, 1, ':::' ), 2 ) = 0 And right( list_nthitem( MailboxPath, 1, ':::' ), 3 ) = 0 then
return MailboxPath;
END CASE


the result is only a null for all fields. Do I need to use a return statement or not? There is no syntax error. Just the nulls.

What am I doing wrong here?

Mit freundlichen Grüßen/Regards

Beatrix Willius

http://www.mothsoftware.com
Mail Archiver X: The email archiving solution for professionals
Kommentar
There are no comments made yet.
Beatrix Willius Akzeptierte Antwort
Got it: no return, no begin, no semicolons:


CASE when right( list_nthitem( MailboxPath, 1, ':::' ), 1 ) = 0 then MailboxPath END
Kommentar
There are no comments made yet.
François Van Lerberghe Akzeptierte Antwort
Hi Beatrix,

1) I don't think you can declare variable in method. Perhaps you could use another method field for that.
You declare first a method field (for example "YourFirstMethodField " ) for : list_nthitem( MailboxPath, 1, ':::' ).
Next you use this field in your other method field : CASE when right( YourFirstMethodField, 1 ) = 0 then MailboxPath END

2) I'm interested in your speed tests results. Are methods faster than stored procedure ?
Kommentar
There are no comments made yet.
Beatrix Willius Akzeptierte Antwort
Hi Francois,

1) I'll try the intermediate method. For clarity this would be nice. And I really need to do the feature request to have natural sorting.

2) The original function took 2 seconds to get the cursor and again 2 seconds to walk through the cursor for a couple of hundred records in the table. When using the method everything takes less than a second.
Kommentar
There are no comments made yet.
François Van Lerberghe Akzeptierte Antwort
Thus, method field is faster than stored procedure (at least in this case). Interesting.
Thank you for this information, Beatrix.
Kommentar
There are no comments made yet.
  • Seite :
  • 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