Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Re: DBI search for forward slash

by haukex (Bishop)
on Nov 08, 2020 at 07:34 UTC ( #11123486=note: print w/replies, xml ) Need Help??


in reply to DBI search for forward slash

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.

Replies are listed 'Best First'.
Re^2: DBI search for forward slash
by luxs (Beadle) on Nov 08, 2020 at 07:36 UTC
    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=127.0.0.1", $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.

        Sorry for delay. THis is my code
        $dbh->do("CREATE TABLE IF NOT EXISTS `test` ( `string` VARCHAR(256))" +); my $string = '/a/b/c/d'; my $I = qq{ INSERT INTO `test` (`string`) VALUES (?) }; my $sI = $dbh->prepare($I); $sI->execute($string); my $R1 = qq{ SELECT * FROM `test`}; my $sR1 = $dbh->prepare($R1); my $row1 = $sR1->execute(); say "total1: $row1"; while( my $L1 = $sR1->fetchrow_hashref ) { say "$L1->{'string'}"; } my $R2 = qq{ SELECT * FROM `test` WHERE `string`=?}; my $sR2 = $dbh->prepare($R2); my $row2 = $sR2->execute(); say "total2: $row2"; while( my $L2 = $sR2->fetchrow_hashref ) { say "$L2->{'string'}"; }
        produce:
        total1: 1 /a/b/c/d total2: 0E0

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://11123486]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (4)
As of 2021-05-17 15:05 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Perl 7 will be out ...





    Results (157 votes). Check out past polls.

    Notices?