B4J Tutorial [SQLite] Make Custom Functions and Regex

Discussion in 'B4J Tutorials' started by Roycefer, Dec 10, 2017.

  1. Roycefer

    Roycefer Well-Known Member Licensed User

    SQLite3 has the REGEXP keyword. That means we can do SQLite queries like
    SELECT * FROM table1 WHERE col1 REGEXP '\d+[a-z]+\d*';

    Unfortunately, the REGEXP keyword references an SQLite user defined function that is not defined by default. That means if we want to use REGEXP in our SQLite commands, we have to define it ourselves. The attached project demonstrates how to create your own user defined functions that use B4J code and how to define a REGEXP function that SQLite will call when you use the REGEXP keyword in a command.

    An overview:
    1. We create a Java method (called "createNewSQLiteFunc") with inline Java. This method will take 3 arguments, an SQL Connection, a String for the function name (the name we will use in our SQLite commands to call that function) and a String for the B4X function name (the name of the B4X Sub).
    2. Next, we create a B4X Sub that will call the inline Java method created in Step 1.
    3. Next, we write our B4X Sub that will execute the code we want executed by SQLite. Pay special attention to the parameters passed to the B4X Sub by the inline Java code. Also, our B4X Sub should return an Int.
    In the attached project, our B4X Sub is called TestFunc and we named TESTFUNC in SQLite, allowing us to run SQLite commands like
    SELECT * FROM table1 WHERE TESTFUNC(payload0,payload1)>0;
    The process for implementing REGEXP is a little simpler:
    1. The Regex logic is implemented in the Java method called createREGEXP.
    2. The B4X Sub createREGEXPFunc calls that Java method.
    This allows us to make calls like
    SELECT * FROM table1 WHERE payload0 REGEXP '\d+\w+';
    SELECT * FROM table1 WHERE REGEXP('\d+\w+', payload0);
    (the two syntax are equivalent).

    Note that createNewSQLiteFunc and createREGEXP are called right after sql.InitializeSQLite is called.

    This will not work in B4A or B4i or B4R.

    Attached Files:

    wl, Mashiane, rboeck and 2 others like this.
  2. wl

    wl Well-Known Member Licensed User

    Great !

    Is there als a way we can write a custom B4J function to return a 'calculated' fieldvalue ?
    So code that provides a fieldvalue ?

    Something like

    'SELECT MyFunction(fieldvalue1), fieldvalue2, ...'

  3. Roycefer

    Roycefer Well-Known Member Licensed User

    I think it should be possible. I suggest you look at the example attached to the original post and try incrementally modifying it until you have achieved what you want.
  4. AnandGupta

    AnandGupta Active Member Licensed User

    Ahh...I was so happy when I started reading the post, but you killed it at the end.
    Do you watch GOT a lot ?


    Jorge M A likes this.
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice