FAQ
the quote() function is quite lame in my experience - for raw SQL I use:

# get rid of any backshlased single quotes
$value =~ s/\\'/'/g;

# Fixup regular single quotes
$value =~ s/'/', char(39), '/g;

# Get rid of question marks
$value =~ s/\?/', char(63), '/g;

So "What's up" ends up like: 'What', char(39), 's up'

Mark

Ronald J Kimball wrote:
I'm getting an error with the following query, which finds all users who
have not yet been sent a message from the specified user with the
specified subject(s).

Due to SQL::Abstract limitations, I'm providing some literal SQL for the
join clause, which happens to contain an escaped apostrophe.

my $user_id = 43;
my @old_subjects = ("What's up");

my $users =
$schema->resultset('User')->search(
{ 'user_message.message_id' => undef,
'me.status' => 1,
},
{ from => [
{ me => 'user' },
[ { user_message => 'user_message',
-join_type => 'left' },
{ 'user_message.recip_user_id' => 'me.user_id',
'user_message.subject' =>
\ ('IN (' .
join(', ', map $schema->storage->dbh->quote($_),
@old_subjects) .
')'),
'user_message.sender_user_id' => $user_id,
},
],
],
},
);

my $count = $users->count;


Error message:

DBIx::Class::ResultSet::count(): DBI Exception: DBD::mysql::st
bind_param failed: Illegal parameter number [for Statement "SELECT
COUNT( * ) FROM user me LEFT JOIN user_message user_message ON (
user_message.recip_user_id = me.user_id AND user_message.sender_user_id
= 43 AND user_message.subject IN ('What\'s up') ) WHERE ( me.status = ?
AND user_message.message_id IS NULL )"]


Without the apostrophe in the message subject, the query works fine.


Is this a known issue? Are there any workarounds? (If necessary, I can
just run this query directly through DBI rather than using DBIC.)

DBIx::Class 0.08007
DBI 1.50
DBD::mysql 3.0002


thanks,
Ronald

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive:
http://www.grokbase.com/group/dbix-class@lists.rawmode.org

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 2 of 9 | next ›
Discussion Overview
groupdbix-class @
categoriesperl, catalyst
postedMar 14, '08 at 7:08p
activeMar 14, '08 at 9:31p
posts9
users4
websitedbix-class.org
irc#dbix-class

People

Translate

site design / logo © 2021 Grokbase