Re^3: DBI search for forward slash

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";
[download]

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.

Comment on Re^3: DBI search for forward slash Select or Download Code

Replies are listed 'Best First'.
Re^4: DBI search for forward slash by luxs (Beadle) on Nov 08, 2020 at 08:31 UTC
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'}"; } [download] produce: `total1: 1 /a/b/c/d total2: 0E0` [download]	[reply] [d/l] [select]
Re^5: DBI search for forward slash by haukex (Archbishop) on Nov 08, 2020 at 08:34 UTC
my $R2 = qq{ SELECT * FROM `test` WHERE `string`=?}; my $sR2 = $dbh->prepare($R2); my $row2 = $sR2->execute(); [download] You forgot to pass `$string` to `->execute` as a value for the placeholder. `$sR2->execute($string);` works as expected.	[reply] [d/l] [select]
Re^6: DBI search for forward slash by luxs (Beadle) on Nov 08, 2020 at 08:43 UTC
yes i've spot this typo. It is working like that now But really, I'm trying to understand when is my problem now. my old code, with table, produced via phpMyAdmin not working. yes, i've check and it is clear for me now - my foreigner table has spaces in the string which was not easy to spot, but spaces are still counting in exact match.... Oh again silly error.... Feel really sorry for this realyl stupid question	[reply]
Re^7: DBI search for forward slash by haukex (Archbishop) on Nov 08, 2020 at 08:46 UTC


Come for the quick hacks, stay for the epiphanies.
	PerlMonks