sqlite queries

[Keith McLeod]

In many of our scripts prior to having the sqlite databases for SMAT, we could easily pull information out of the HL7 messages.

Topic

[Keith McLeod]

Not sure if I was clear on my request.

[David Barr]

I’m not sure where the documentation is for the flavor of Sqlite that you’re using. I found this page that talks about using a CAPTURE function:

http://www.ashleyit.com/blogs/brentashley/2013/11/27/using-regular-expressions-with-sqlite/

I’m not sure whether or not the TCL/Sqlite API that comes with Cloverleaf supports this feature.

[Keith McLeod]

What is included in the folowing files and can we get some documentation or a post?

libSMATJni.so

sqlite3-regex.so

located in $HCIROOT/bin

I know I can sed the keyword REGEXP when sqlite3-regex.so in loaded within sqlite, however I only get a return of 1 or 0.  Is capturing of parenthesed values an option?  Can it be?  This would be helpful in extracting fields or portions of strings in SQLite queries.

select MessageContent REGEXP ‘rPID(?:[^|]*|){3}([^^|r]*)’ from smat_msgs where MessageContent REGEXP ‘rPID(?:[^|]*|){18}(1234567)’;

I get three 1’s back from the database for the messages it matches

1

1

1

Was hoping for the value contained in PID:3.

And David B. thanks.  I have not worked out the detail on this yet…CAPTURE…

[Shabbir Suterwala]

>./sqlite3 your.smatdb

sqlite> .load sqlite3-regex[.dll/.so]  <

---

 edited

sqlite> select MessageContent from smat_msgs where SMATREGEXP(”, rowid, ‘main’) > 0;

Substitute appropriately.

This will work only when your messages are in UTF8. If they are in some other encoding then there are few extra steps between .load and select

[Shabbir Suterwala]

We do not provide capturing capability. However we provide a function called SMATREGEXPDETAIL() in 6.1.1 (about to be released), which will give you offsets and lengths of regex matches. Then you can run sqlite’s substr() function to extract strings.

[Keith McLeod]

What is the syntax for this?  I have 6.1.1 in place.

Here is a tidbit of perl from a link provided by David Barr that provides capture functionality.

Here it is in Perl:

$dbh->func(‘capture’,2,sub {

 my($regex,$string) = @_;

 my($capture) = $string =~ /$regex/;

 return $capture;

}, ‘create_function’);

# get month from YYYY/MM/DD format where month or day could be one or two digits

my $row = $dbh->selectrow_arrayref(

 “SELECT CAPTURE(‘\d{4}/(\d{1,2})/\d{1,2}’, date_created) FROM invoices”

);

[Shabbir Suterwala]

select SMATREGEXPDETAIL(”, rowid, ‘main’) from smat_msgs where SMATREGEXP(”, rowid, ‘main’) > 0;

I believe the above is correct. I will double check and confirm.

Thanks

Shabbir

[Keith McLeod]

Need to have MessageContent in there somewhere.

[Shabbir Suterwala]

No. SMATREGEXP() and SMATREGEXPDETAIL() work on MessageContent field only and by default. No need to specify that. For other fields use REGEXP(). There is no REGEXPDETAIL() yet.

SMATREGEXP() and SMATREGEXPDETAIL() take care of encoding issues in MessageContent. By default UTF8 encoding is used, unless user specifies any other encoding. Let me know if you want details on that.

Please note that these are internal functions and may not have support or documentation.

[Keith McLeod]

Here is what was returned:

highlight:135,163,135,163

highlight:135,163,135,163

highlight:135,163,135,163

I will verify the numbers to see how they match up.

Thanks

[Shabbir Suterwala]

First 2 numbers are byte offsets & lengths. 2nd 2 numbers character offsets and lengths. They will be the same for ASCII characters but different for multi-byte character sets.

[Max Drown (Infor)]

By the way everyone, Shabbir is the Cloverleaf Development Team Lead.

-- Max Drown (Infor)

[Keith McLeod]

I would have expected the first character to be at 164 and the last character at 172 for the data I am using.

[Keith McLeod]

Apparently SQLite is an extension of TCL.

[Author]

Replies