Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Re^2: DBI search for forward slash

by luxs (Beadle)
on Nov 08, 2020 at 07:36 UTC ( #11123487=note: print w/replies, xml ) Need Help??


in reply to Re: DBI search for forward slash
in thread DBI search for forward slash

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

Replies are listed 'Best First'.
Re^3: DBI search for forward slash
by haukex (Bishop) on Nov 08, 2020 at 08:18 UTC
    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
        my $R2 = qq{ SELECT * FROM `test` WHERE `string`=?}; my $sR2 = $dbh->prepare($R2); my $row2 = $sR2->execute();

        You forgot to pass $string to ->execute as a value for the placeholder. $sR2->execute($string); works as expected.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (2)
As of 2021-05-09 11:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Perl 7 will be out ...





    Results (100 votes). Check out past polls.

    Notices?