Re: [Dbix-class] DBIx how to update_or_create a table with primary index is autoincrement ? getting erroneous data
On Thu, Sep 27, 2012 at 12:55 PM, Rajeev Prasad rp.ne...@yahoo.com wrote:
friends,
I have a Table to update (empty right now), and the regularly 'update'
existing info with changes AND add any new record which come up.
source data is coming from file. one record per line.
my @my_arr; #has 500 uniq entries
$schema-resultset('Mytable')-update_or_create(
{
field1 = $my_arr[0],
field2 = $my_arr[1],
field3 = $my_arr[2],
...
},
{key ='primary'}#field_id, auto increment value this field is
NOT supplied in variables above.
);
What is happening is: DBIX is first adding the 'first' record, then
updating it with consecutive records!!! it is not creating a fresh record
for any record in the file. so after it finishes running, I am left with
one record in Table which is updated 500 times, and has the last record in
file as the table record.
how to fix this?
You've asked update_to_create() to find the existing row based solely on
the primary key, but you haven't provided a value for the primary key.
Therefore, it assumes that all the rows are the same row.
Do you have any unique constraints on this table other than primary key?
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.scsys.co.uk
Re: [Dbix-class] DBIx how to update_or_create a table with primary index is autoincrement ? getting erroneous data
Yes, that is correct. For update_or_create() to work, it needs a way to find the original row, if it exists. Ronald On Thu, Sep 27, 2012 at 1:40 PM, Rajeev Prasad rp.ne...@yahoo.com wrote: so far, I do not, but I want one filed to be uniq, lets call it field1. so should i set a uniq constraint on it, and then use that field in update_or_create? rather than the primary index? ty. ___ 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.scsys.co.uk
Re: [Dbix-class] not able to list any records from database
On Thu, Sep 1, 2011 at 5:52 PM, Rajeev Prasad rp.ne...@yahoo.com wrote:
thx.
but when i put search item as 'test12' I get error:
$ get-data-from-db.pl
Use of uninitialized value in concatenation (.) or string at ./
get-data-from-db.pl line 23.
user is:
here is the code again, i think i have defined every variable.
==
#!/usr/bin/perl
use strict;
use warnings;
use lib '../testdb';
use Mysqltestdb::Schema;
my ($schema, $user, $users_rs, $userid);
my (@newusers, @oldusers);
$schema =
Mysqltestdb::Schema-connect(dbi:mysql:dbname=mysqltestdb:localhost:3306,
'root', 'mysql5');
$users_rs = $schema-resultset('Testdbuser')-search(
{ 'userid' = 'test12' },## WHERE
{
select = [ qw/userid password/ ], ## SELECT
as = [ qw/uid pass/ ],
}
);
while ($user = $users_rs-next ()){
print user is: . $user-userid . \n;
};
=
You've selected userid as uid, but you're still trying to access it as
$user-userid.
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.scsys.co.uk
Re: [Dbix-class] Duplicate me.id in WHERE clause of simple query
On Mon, Jan 24, 2011 at 8:40 AM, Winfried Neessen winfr...@neessen.netwrote:
sub list :Path :Args( 0 )
{
## Get object and context
my ( $self, $c ) = @_;
## Get ResultSet
my $rs = $c-model( 'DB::Vorgaenge' );
## Sort the ResultSet by ID
my $search = $rs-search( undef, { order_by = 'id' } );
## Store the vorgaenge details in the stash
$c-{ 'stash' }-{ 'vorgaenge' } = $search;
}
I don't see anything here that would cause a WHERE cause to be added in the
first place, and the class name doesn't match the table name in your debug
output.. Are you sure this is the right code snippet?
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.scsys.co.uk
Re: [Dbix-class] Chained resultsets and WHERE operators
On Tue, Nov 9, 2010 at 8:36 AM, Mike Raynham catal...@mikeraynham.co.ukwrote:
I have created a query which determines if a given date range exists within
date ranges stored in the database:
###
# SELECT * FROM my_table WHERE
# (start_date = $start_date AND end_date = $start_date) OR
# (start_date = $end_date AND end_date = $end_date)
my $date_ranges = $rs-search({
-or = [
-and = [
start_date = { '=', $start_date },
end_date = { '=', $start_date },
],
-and = [
start_date = { '=', $end_date },
end_date = { '=', $end_date },
],
],
});
Is there a way that I can cause the two queries to be chained together with
an OR instead of an AND?
At the moment, I have got round the problem by using the non-chained query
at the top of this post, and specifying out-of-range defaults for dates that
have not been supplied.
I don't think there is a way to merge search conditions with an OR when
chaining resultsets. I would suggest doing something like this:
my $date_ranges = $rs-search({
-or = [
$start_date
? (-and = [
start_date = { '=', $start_date },
end_date = { '=', $start_date },
])
: (),
$end_date
? (-and = [
start_date = { '=', $end_date },
end_date = { '=', $end_date },
])
: (),
],
});
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.scsys.co.uk
Re: [Dbix-class] DBIx::Class::Schema::Loader Can't use string error
On Wed, Oct 27, 2010 at 10:26 AM, Dave Howorth
dhowo...@mrc-lmb.cam.ac.uk wrote:
$ ./create-schema.pl
Dumping manual schema for QD1x::Schema to directory . ...
Schema dump completed.
Failed to reload class QD1x::Schema::Result::EntryAttribute: Can't use
string (QD1x::Schema::Result::EntryAttri) as a HASH ref while strict
refs in use at /usr/local/share/perl/5.10.1/DBIx/Class/Row.pm line 864.
Compilation failed in require at (eval 650) line 3.
Line 864 of DBIx/Class/Row.pm is here:
sub set_column {
my ($self, $column, $new_value) = @_;
# if we can't get an ident condition on first try - mark the object
as unidentifiable
# (by using an empty hashref) and store the error for further diag
unless ($self-{_orig_ident}) { # *** line 864 ***
try {
$self-{_orig_ident} = $self-ident_condition
}
catch {
$self-{_orig_ident_failreason} = $_;
$self-{_orig_ident} = {};
};
}
...
set_column() is being called on the name of your class, rather than an
object of the class. However, I'm not sure why set_column() would
even be called during the schema creation process...
Are there any calls to set_column() in
QD1x/Schema/Result/EntryAttribute.pm, perhaps as
__PACKAGE__-set_column(...)?
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.scsys.co.uk
Re: [Dbix-class] find_or_create and unique constraints
On Sat, Oct 2, 2010 at 8:16 PM, Bill Moseley mose...@hank.org wrote: Seems like -single (and thus find_or_create) should throw an exception if more than one row is returned, columns passed to find_or_create must satisfy a defined unique constraint, and if a single row is found any non-constraint columns specified should be updated. find_or_create() should not update an already existing row. That's what update_or_create() is for. 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.scsys.co.uk
Re: [Dbix-class] prefetch problem
On Fri, Aug 13, 2010 at 10:39 AM, Marc Perez markkus...@gmail.com wrote:
I'm doing the next query:
my $files = $catVar-model('cmsDB::Files')-search(
{
'me.cmsobj_id' = $this-cmsobj_id,
-or = [mime_type = 'image/jpeg',mime_type =
'image/gif',mime_type = 'image/png'],
'fieldfiles.language_id' = $langId
},
{
join = 'fieldfiles',
prefetch = 'fieldfiles'
}
);
When I try to access the fieldfiles data:
while( my $file = $files-next() ) {
print($file-fieldfiles-name)
}
I get the error:
Can't locate object method name via package DBIx::Class::ResultSet
Because fieldfiles is a has_many relationship, calling the accessor
in scalar context returns a resultset object, not a row object. You
probably want to do one of these:
while (my $file = $files-next) {
my $fieldfiles = $file-fieldfiles;
while (my $fieldfile = $fieldfiles-next) {
print $fieldfile-name;
}
}
while (my $file = $files-next) {
foreach my $fieldfile ($file-fieldfiles) {
print $fieldfile-name;
}
}
You could also do something like this:
while (my $file = $files-next) {
print join , , $file-fieldfiles-get_column('name')-all;
}
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.scsys.co.uk
Re: [Dbix-class] Enforcing read-only objects
On Wed, Aug 11, 2010 at 6:09 PM, Ian Docherty (icydee)
dbix-cl...@iandocherty.com wrote:
Ignoring for the moment the artists, cds, tracks etc. I could see it
something like the following. (top of my head, first impressions, probably
totally borked)
In your application code.
my $factory = MyApp::Business-new({schema = $schema});
my $label = $factory-find_label($id);
$label-set_active;
The factory code might have something like the following
package MyApp::Business;
use Moose;
has 'schema' = (is = 'ro', required = 1);
sub find_label {
my ($self, $id) = @_;
return $self-schema-resultset('DB::Label')-find($id);
}
Your Label class would be something like.
package MyApp::Business::Label;
use Moose;
has 'dbic_obj' = (is = 'ro', required = 1);
sub set_active {
my ($self) = @_;
die Is Readonly if $self-dbic_obj-is_readonly;
$self-dbic_obj-active( 1 );
$self-dbic_obj-update;
}
Note. The above code is totally untested and likely to be broken and is only
an indication of the general method! Please don't flame me for syntax errors
but feel free to suggest alternative ways of doing this. I am still learning
too!
Please don't consider this a flame. :) I just wanted to point out
that your find_label() method is returning a DBIC row object, but I
presume you intended for it to return a MyApp::Business::Label object.
I believe that would look something like this:
sub find_label {
my ($self, $id) = @_;
return MyApp::Business::Label-new(
dbic_obj = $self-schema-resultset('DB::Label')-find($id)
);
}
(Also untested, etc.)
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.scsys.co.uk
Re: [Dbix-class] Automatic quoting
On Mon, Jun 7, 2010 at 6:04 PM, fREW Schmidt fri...@gmail.com wrote: On Mon, Jun 7, 2010 at 12:04 AM, Toby Corkindale toby.corkind...@strategicdata.com.au wrote: Only speaking on behalf of myself, but I see: 1) You shouldn't be making db tables or columns based on reserved words anyway, as sooner or later someone will want to log in manually to do something in SQL, and be cursing you. I disagree with the former. What if you have a column like, say, username or login? That should be totally allowed. What database has username or login as reserved words? 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.scsys.co.uk
Re: [Dbix-class] paging question
James Marca wrote: From reading the docs, starting with http://search.cpan.org/~frew/DBIx-Class-0.08115/lib/DBIx/Class/Storage/DBI.pm#DBIx::Class_specific_connection_attributes and then linking to http://search.cpan.org/perldoc?DBI#prepare_cached, I see this for 'prepare_cached': Like prepare except that the statement handle returned will be stored in a hash associated with the $dbh. If another call is made to prepare_cached with the same $statement and %attr parameter values, then the corresponding cached $sth will be returned without contacting the database server. So if I understand this correctly, what is happening is that prepare_cached is caching a statement that depends on both the query *and* the attributes of the query. And because my attributes change with each $data_rs-search($query) line, I never end up resuing a statement handle, right? What I want is to get a prepared statement handle reused for a query with different bind values, without having to dive down into the DBI connection to do it. prepare_cached() does what you want. %attrs is the hash of attributes, such as RaiseError or FetchHashKeyName, not the list of bind values. The list of bind values isn't even passed to prepare_cached(). 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.scsys.co.uk
Re: [Dbix-class] Strict sequence primary key
Wes Cravens wrote: In an effort to create strictly sequential (Incrementing Integer no gaps) primary keys my method has traditionally been: BEGIN; LOCK table; SELECT MAX(id) FROM table; INSERT INTO table...; COMMIT; I'm relatively new to DBIC and have not really seen a way to do this unless I create a wrapper to handle it. Is there something I am missing? Why do require strictly sequential primary keys? 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.scsys.co.uk
Re: [Dbix-class] Fwd: [Catalyst] Selecting from more tables (DBIC - bug?)
Thomas L. Shinnick wrote: I'm having some of the same questions regarding +select and +as . It seems that using '+select' and '+as' does not stop other columns from being returned. I'm using DBIC 0.08115. What do you expect +select and +as to do?? +select Indicates additional columns to be selected from storage. Works the same as select but adds columns to the selection. +as Indicates additional column names for those added via +select. See as. +select and +as are behaving exactly as documented. If you want to select /only/ the specified columns use select and as, not +select and +as. 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.scsys.co.uk
Re: [Dbix-class] Duplicate entry error on create
Dermot wrote: 2009/12/9 Eden Cardim edencar...@gmail.com: Dermot == Dermot paik...@googlemail.com writes: Dermot Hi, I am getting the following error when I attempt to Dermot create a record. Dermot DBD::mysql::st execute failed: Duplicate entry '0' for key 3 Dermot [for Statement INSERT INTO products ( distribution, Dermot caption, contributor_id, ...) VALUES(( ?, ?, ?, ?, ?, ?, ?, Dermot ?, ?, ?, ? ) with ParamValues: 0='1', 1='Earth', 2='3') Dermot Is 'key 3' referring to 'the 3rd item in the INSERT list Dermot (contributor_id)? No, that's a mysql error, you're probably violating a constraint on your table with those values. Yes. I was hoping I could find out what 'key 3' was referring to. The third unique index on the table. 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.scsys.co.uk
[Dbix-class] register_source() regression in 0.08102
I'm currently using the following code in one of my schema modules,
based on the message
http://www.mail-archive.com/dbix-class@lists.scsys.co.uk/msg02613.html
my $new_month = '200905';
my $user_session_source = __PACKAGE__-source('UserSession');
my $new_source = $user_session_source-new($user_session_source);
$new_source-name(user_session_$new_month);
__PACKAGE__-register_source(UserSession$new_month = $new_source);
This works fine in 0.08010, but in 0.08102, I get the following warning:
DBIx::Class::Schema::register_source():
AdEngine::Schema::AdEngineDB::UserSession already has a source, use
register_extra_source for additional sources at
/usr/local/lib/site_perl/AdEngine/Schema/AdEngineDB.pm line 27
What is the correct way to implement this with 0.08102? I'm skeptical
about register_extra_source(), because I'm not registering an additional
source for UserSession; I'm registering a new source for UserSession200905.
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.scsys.co.uk
Re: [Dbix-class] Oracle xmltype
Morgon Hed wrote:
You can set '+select' and '+as', and stick that in a
resultset method, so
the code just becomes
$stream_rs-with_xml_field-...
Thanks for the reply but I don't quite understand what you are saying.
I can easily do the follwing:
my $s1 = $schema-resultset('Stream')-search(
{},
{
select = [ 'another_col', {
'xmltype.getCLobVal' = 'xml' } ],
as = [qw/another_col
xml/],
}
);
Then I can of course retrieve more columns - why would I want to use '+select'
(which is not even mentioned in the DBIx::Class::ResultSet pod)?
It certainly is mentioned in the DBIx::Class::ResultSet documentation:
http://search.cpan.org/~ribasushi/DBIx-Class-0.08102/lib/DBIx/Class/ResultSet.pm#+select
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.scsys.co.uk
Re: [Dbix-class] is_nullable
Dermot wrote: 2009/3/18 Ronald J Kimball rkimball+dbixcl...@pangeamedia.com: Dermot wrote: is_nullable = 0 and NOT NULL mean the same thing, don't they? I think it's one of them there double negatives :) is_nullable Set this to a true value for a columns that is allowed to contain NULL values. This is currently only used by deploy in DBIx::Class::Schema. Exactly. A true value means the column is allowed to contain NULL values. A false value means the column is NOT allowed to contain NULL values. 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.scsys.co.uk
Re: [Dbix-class] DBIx::Class::Resultset - DBI Exception near PATH
on...@houseofdesign.de wrote:
$hashref-{'users_reference'} does nox exist. I had problems with undef
variables in a hash. I generally put a scalar in front of my variables,
this fixes the undef value in hash-problem
e.g.
-find_or_create({
...
users_reference = scalar $hashref-{'users_reference'},
...
});
There is no difference between
{
users_reference = $hashref-{'users_reference'},
}
and
{
users_reference = scalar $hashref-{'users_reference'},
}
$hashref-{'users_reference'} is already a scalar. Either way, you will
get the value of $hashref-{'users_reference'}, or undef if the key
doesn't exist.
You're confusing this case with calling a subroutine that could return
an empty list:
{
users_reference = $cgi-param('users_reference'),
}
versus
{
users_reference = scalar $cgi-param('users_reference'),
}
The former snippet will not do the right thing if
$cgi-param('users_reference') returns zero values or more than one
value. scalar() forces it to return a single value.
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.scsys.co.uk
[Dbix-class] get_inflated_columns and selecting specific columns
With search(), you can specify a set of columns to return, e.g.:
select = [qw/ item_id name /],
However, unlike get_columns(), get_inflated_columns() doesn't limit
itself to those columns. Instead, it returns values for all the columns
in the table. Of course, the value will beundef for any columns that
weren't actually selected.
Is this the intended behavior? It isn't clear to me from the documentation.
I'm using DBIx::Class 0.08007.
Example below.
thanks,
Ronald
#!/usr/bin/perl
use strict;
use warnings;
use TestApp::Model::TestDB;
use Data::Dump qw/ dump /;
my $schema = TestApp::Model::TestDB-new();
my $test_items_rs = $schema-resultset('TestItem');
while (my $test_item = $test_items_rs-next) {
print dump({ $test_item-get_columns }), \n;
print dump({ $test_item-get_inflated_columns }), \n;
}
print \n;
$test_items_rs =
$test_items_rs-search(undef, { select = [qw/ item_id name /] });
while (my $test_item = $test_items_rs-next) {
print dump({ $test_item-get_columns }), \n;
print dump({ $test_item-get_inflated_columns }), \n;
}
__END__
{ info = This is the first item, item_id = 0, name = Item One }
{ info = This is the first item, item_id = 0, name = Item One }
{ item_id = 0, name = Item One }
{ info = undef, item_id = 0, name = Item One }
___
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]
Re: [Dbix-class] SQL Function in update
My example using NOW() was just to show that I already know about the
usual solution (ref to string) for this problem. I am already using
DateTime as you suggest.
I'm calling GREATEST so that the value of paused_until will only be
updated if the new value is greater than the current value.
thanks,
Ronald
Hartmaier Alexander wrote:
Hello Ronald!
In my opinion ist better to fetch the datetime in your model to be database
function independent and pass that to update and let the database specific
deflator make what it's best at:
$row-mydate(DateTime-now( time_zone= 'UTC' ));
$row-update;
or
$row-update(mydate = DateTime-now(time_zone= 'UTC' ));
What should GREATEST do in your case?
-Alex
-Original Message-
From: Ronald J Kimball [mailto:[EMAIL PROTECTED]
Sent: Monday, March 31, 2008 5:03 PM
To: [EMAIL PROTECTED]
Subject: [Dbix-class] SQL Function in update
Yes, it's another question about using SQL functions in an update. I
know I can pass a reference to a string containing literal SQL, as in:
$row-update({ mydate = \ 'NOW()' });
However, in this case I need to pass arguments to the function, one of
which needs to be quoted. Here's what I'm using right now:
my $date = DateTime-now();
my $until = GREATEST(paused_until, .
$schema-storage-dbh-quote($dt) . );
$row-update({ paused_until = \$until });
Ideally, I could do something like this:
$row-update({ paused_until =
{ GREATEST = [ \ 'paused_until', $dt ] } });
Suggestions?
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/[EMAIL PROTECTED]
[Dbix-class] Error with escaped apostrophe and bind param
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/[EMAIL PROTECTED]
Re: [Dbix-class] Error with escaped apostrophe and bind param
Ash Berlin wrote: 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. Because I need to /exclude/ those rows. I select from user, with a left outer join against user_message, and select only the rows where user_message.message_id is null. 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/[EMAIL PROTECTED]
Re: [Dbix-class] result testing
The call to $rs-first returns undef, so you're trying to call
undef-id. That's why you get the error. Is that enough of a hint? :)
Try this:
myErrorFunc() unless $rs-first;
If id is a primary or unique key, you could do this instead:
myErrorFunc() unless $c-model('AppModelDB::Clients')-find({id=$id});
Ronald
Angel Kolev wrote:
Hi guys.
How i can check if my $rs =
$c-model('AppModelDB::Clients')-search({id=$id}); succeed ?
If i try myErrorFunc() unless defined $rs-first-id (or something
like this) the result is always Caught exception in. Cant call
method id on undefined value.. I just wanna know if that ID
exists in my DB and throw Error screen if not.
Thanks
___
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]
