B4J Tutorial [SQLite] Make Custom Functions and Regex

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+';

or

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.

 

[wl]

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, ...'

Thanks

 

[Roycefer]

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.

 

[AnandGupta]

This will not work in B4A

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

Regards,

Anand