SQLite Forum

(Deleted)
Login

PRAGMA case_sensitive_like

(1.2) Originally by Trudge with edits by Richard Hipp (drh) on 2020-10-30 18:40:54 from 1.1 [link] [source]

(Original message deleted by the OP)

(2) By Trudge on 2020-10-30 16:37:52 in reply to 1.1 [link] [source]

I'm having trouble trying to do a case-sensitive query using Perl to access an sqlite3 DB. My situation involves artist names, most of which begin with an upper-case letter, but others begin with a lower case letter. For example 'Dead Can Dance' and 'dZihan & Kamien'. I have a web page that offers a group of clickable buttons containing the first letter of an artists's name. This helps in organizing and finding an artist. The page renders correctly - I have a 'D' button and a 'd' button. But when clicked, both give me ALL artists beginning with EITHER letter. The sqlite DB does not seem to be making a case-sensitive search. Some research has lead me to 'PRAGMA case_sensitive_like = 1;'. This does work in the CLI, but how do I implement it in Perl? I've tried $dbh=DBI->connect("dbi:SQLite:$dbname","","",{RaiseError=>1,PrintError=>1,sqlite_unicode=>1,case_sensitive_like=>1}); and my query: $sth=$dbh->prepare(qq{select artists.name,artists.artistid,cds.genre from artists,cds where artists.artistid = cds.artistid and name like ? group by name,artists.artistid,cds.genre order by artists.name;}) or die "Can't prepare statement: $DBI::errstrn"; $sth->execute('$ArtistLetter%') or die "Can't execute statement: $DBI::errstrn"; warn $DBI::errstr if $DBI::err; while (@records=$sth->fetchrow_array()) { . . . } $sth->finish(); When my script runs (Apache on a local server (High Sierra), the address bar in Safari shows the letter searched as 'D' or 'd', but the result is 0 for both. Can anyone explain how to implement this (or any) PRAGMA correctly in Perl please?

(3) By tom (younique) on 2020-10-30 17:02:54 in reply to 2 [link] [source]

I think you have to set it for the connection:

$dbh->exec("PRAGMA case_sensitive_like = 1;");

Prepare your query afterwards.

(4) By Trudge on 2020-10-30 17:16:06 in reply to 3 [link] [source]

Thank you for the quick reply. I had to use 'system': system("$dbh=PRAGMA case_sensitive_like = 1;");

then my query prepare, execute, finish. But this also gives me both 'D' and 'd" results.

(5) By Kees Nuyt (knu) on 2020-10-30 19:07:32 in reply to 4 [source]

Try:

$dbh=DBI->connect("dbi:SQLite:$dbname","","",RaiseError=>1,PrintError=>1,sqlite_unicode=>1});
$dbh->exec("PRAGMA case_sensitive_like = 1;");
$sth=$dbh->prepare(qq{select artists.name,artists.artistid,cds.genre from artists,cds where artists.artistid = cds.artistid and name like ? group by name,artists.artistid,cds.genre order by artists.name;})  or die "Can't prepare statement: $DBI::errstr\n";
$sth->execute('$ArtistLetter%')     or die "Can't execute statement: $DBI::errstr\n";
warn $DBI::errstr if $DBI::err;
while (@records=$sth->fetchrow_array()) {
-- 
Regards,
Kees Nuyt

(6) By Trudge on 2020-10-30 20:07:56 in reply to 5 [link] [source]

OK, tried with 'exec' but errored out: Can't locate object method "exec" via package "DBI::db" ... So tried 'system' like this: system("$dbh=PRAGMA case_sensitive_like = 1;"); This does not present any errors, but also still showed ALL results beginning with 'D' or 'd'. I think we are on the right track though. Thank you for taking time for this.

(7) By Keith Medcalf (kmedcalf) on 2020-10-30 20:52:24 in reply to 6 [link] [source]

It is an SQL statement so prepare it and execute it. What is the difficulty? You seem to be able to prepare and execute other SQL statements.

There is no difference.

(8) By Trudge on 2020-10-30 20:59:42 in reply to 7 [link] [source]

OMG. I'm sure I tried that but may have had something wrong. DBconnect(); $sth=$dbh->prepare(qq{PRAGMA case_sensitive_like = 1}); $sth->execute(); $sth->finish(); This worked perfectly with no errors Showing Artists beginning with d ...

dZihan & Kamien [Ambient / Lounge / Downbeat]

Thank you so much. Now I feel like 2 cents waiting for change :)

(9) By Keith Medcalf (kmedcalf) on 2020-10-31 05:24:21 in reply to 8 [link] [source]

Perhaps you attempted to prepare something like "PRAGMA case_sensitive_like = ?" and then bind the 1/0 as a parameter.

This sort of thing does not work because most pragma's work at prepare time, yet the parameter is not bound until execution time, so you will not get the result you intend.

(10) By Trudge on 2020-10-31 15:45:07 in reply to 9 [link] [source]

Ahh. Thank you. I know I did attempt something like that but got no or poor results. Probably because of this.