On 14 Mar 2008, at 19:08, 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
Why exactly do you need to have that as part of the join condition
rather than just in the WHERE clause? If you put as part of the search
term then you use bind params much easier and let the *database*
handle the quoting.
_______________________________________________
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/[EMAIL PROTECTED]
