Description of problem: When using the REGEXP function in a SQLite statement in a PHP script, I encounter the following error: `SQLite3::prepare(): Unable to prepare statement: 1, no such function: REGEXP` I am very surprised because this code has been working for years on previous Mageia versions and it broke when I upgraded to Mageia 9. Also, on SQLiteBrowser, the same query works without error. I looked at https://stackoverflow.com/questions/24037982/how-to-use-regexp-in-sqlite and I installed all *pcre* RPMs with no success. I currently don't have a /usr/lib/sqlite3/pcre.so file on my system. I could not identify which package is supposed to provide for it. Is it possible that the SQLite3 PHP module is now compiled without PCRE support? Version-Release number of selected component (if applicable): php-sqlite3-8.2.14-1.mga9.x86_64 How reproducible: always Steps to Reproduce: 1. Download the attached test database and PHP script 2. Open SQLiteBrowser and run "SELECT * FROM mytable WHERE myfield REGEXP 'a'", it works fine 3. Run `php test.php`, observe the error: "PHP Warning: SQLite3::prepare(): Unable to prepare statement: 1, no such function: REGEXP" test.db : CREATE TABLE "mytable" ( "myfield" TEXT ); test.php : <?php $db = new SQLite3('test.db'); $db->prepare("SELECT * FROM mytable WHERE myfield REGEXP 'a'");
Created attachment 14257 [details] PHP test file
Created attachment 14258 [details] SQLite3 DB test file
CC: (none) => mageia
Mark I consult you about this bug because you nurse php
I think the package in question is 'php-sqlite3' which is in the main PHP SRPM. To try this, I installed more & more packages - PHP, web server, SQLite - until it ran at last; as described: $ /usr/bin/php test.php PHP Warning: SQLite3::prepare(): Unable to prepare statement: 1, no such function: REGEXP in /home/lewis/tmp/test.php on line 4 Was unsure about the truncated PHP script, but it did not seem to matter. I would assign this anyway to Marc; the best candidate.
Source RPM: (none) => php-8.2.14-1.mga9.src.rpmAssignee: bugsquad => mageiaCC: mageia => (none)
from the manual (https://www.sqlite.org/lang_expr.html#regexp): "The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If an application-defined SQL function named "regexp" is added at run-time, then the "X REGEXP Y" operator will be implemented as a call to "regexp(Y,X)"." As stated here: https://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query a function has to be added, so this script runs without error (regexp function NOT verified): <?php $db = new SQLite3('test.db'); $db->createFunction('regexp', function ($pattern, $data, $delimiter = '~', $modifiers = 'isuS') { if (isset($pattern, $data) === true) { return (preg_match(sprintf('%1$s%2$s%1$s%3$s', $delimiter, $pattern, $modifiers), $data) > 0); } return null; } ); $db->prepare("SELECT * FROM mytable WHERE myfield REGEXP 'a'"); I am not aware something in php-sqlite has changed, since I really don't use it.