Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

DBI search for forward slash

by luxs (Beadle)
on Nov 08, 2020 at 06:44 UTC ( #11123483=perlquestion: print w/replies, xml ) Need Help??

luxs has asked for the wisdom of the Perl Monks concerning the following question:

Simple DBI search with slashes do not return anything

my $string = '/a/b/c'; my $req = qq{ SELECT * FROM `texts` WHERE `string`=?}; my $sth = $dbh->prepare($req); my $row = $sth->execute($string); say "row = $row";
produce: row = 0E0

Table `texts`: string varchar(256) utf8_general_ci

How can I perform exact search strings with with slashes?

Replies are listed 'Best First'.
Re: DBI search for forward slash
by hippo (Bishop) on Nov 08, 2020 at 11:43 UTC
    How can I perform exact search strings with with slashes?

    Like this:

    use strict; use warnings; use DBI; use Test::More tests => 2; my $dbh = DBI->connect('dbi:SQLite:dbname=/tmp/testdb','',''); $dbh->do ('CREATE TABLE foo (bar VARCHAR(255) NOT NULL)'); my $sth = $dbh->prepare ('INSERT INTO foo values (?)'); $sth->execute ('This has no slashes'); $sth->execute ('/a/b/c'); my $want = '/a/b/c'; $sth = $dbh->prepare ('SELECT * FROM foo WHERE bar = ?'); my $res = $sth->execute ($want); ok $res, 'True result'; my ($str) = $sth->fetchrow_array; is $str, $want, 'Strings match';

    Essential reading:


Re: DBI search for forward slash
by Corion (Patriarch) on Nov 08, 2020 at 07:05 UTC

    Are you certain that there is a row with the column string containing exactly /a/b/c? The database seems to think no.

    What database are you using?

    Does running the SQL from the console work? SELECT * FROM `texts` WHERE `string`='/a/b/c'

      Yes, phpMyAdmin returns proper line with this request. I'm using MySql with MyISAM storage.

      I can find in this table any string with one slash at the begining, but not in the middle of the string.

        But = only compares for identity. You want LIKE, and you want the % placeholder:

        SELECT * FROM `texts` WHERE `string` LIKE '%/a/b/c%'

        ... or, with placeholders:

        SELECT * FROM `texts` WHERE `string` LIKE ?

        (and then in the ->execute() call, add the placeholders to your search string)

Re: DBI search for forward slash
by haukex (Bishop) on Nov 08, 2020 at 07:34 UTC
    Simple DBI search

    You say "search" but are using WHERE for exact matches. Are you sure you don't mean LIKE?

    Otherwise, you'll have to provide an SSCCE that reproduces this behavior, because I don't really see anything wrong with the code snippet you've shown, and you haven't shown any sample data.

      Yes, I would like to perform exact search. I need to find field with exact string match. Anyway - LIKE is fail as well
        Anyway - LIKE is fail as well

        Sorry, but that is not an adequate problem description. Please see How do I post a question effectively? and I know what I mean. Why don't you?, and provide a Short, Self-Contained, Correct Example, like the following. Note how it is exactly the code you posted, except that it works fine for me.

        use warnings; use strict; use feature 'say'; use DBI; my $dbh = DBI->connect( "DBI:mysql:database=testing;host=", $ENV{USER}, 'barfoo', { RaiseError => 1, AutoCommit => 1 }); $dbh->do('DROP TABLE IF EXISTS texts'); $dbh->do(<<'ENDSQL'); CREATE TABLE texts ( string VARCHAR(256) ); ENDSQL $dbh->do('INSERT INTO texts (string) VALUES ("/a/b/c");'); # --- my $string = '/a/b/c'; my $req = qq{ SELECT * FROM `texts` WHERE `string`=?}; my $sth = $dbh->prepare($req); my $row = $sth->execute($string); say "row = $row"; # --- my $sth2 = $dbh->prepare( q{ SELECT * FROM texts WHERE string LIKE '%/a/b/c%' }); $sth2->execute; say "row = $row";

        This prints row = 1 twice, as expected.

        This is how I spun up a fresh test MySQL instance. To change the server's port number, change the -p3306:3306 to something like -p13306:3306 in the docker command, add the argument --port=13306 to the mysql command, and add the argument ;port=13306 to the DBI connection string in the Perl code.

        $ docker run --rm --name testmysql -p3306:3306 -e MYSQL_ROOT_PASSWORD=foobar -d mysql:5
        # wait a few seconds for it to start
        $ echo "CREATE USER '$USER' IDENTIFIED BY 'barfoo'; CREATE DATABASE testing; GRANT ALL PRIVILEGES ON testing.* TO '$USER';" | mysql --protocol=TCP --user=root --password=foobar
        # after testing:
        $ docker stop testmysql

        Several smaller edits to add more information.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11123483]
Approved by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (3)
As of 2022-05-16 15:13 GMT
Find Nodes?
    Voting Booth?
    Do you prefer to work remotely?

    Results (63 votes). Check out past polls.