Re: [Dbix-class] DBIx giving err due to camleCase??? not sure..
Hello Henry,thank you. it was very helpful, I have saved the response.the key
piece that col has to be present on which the key is built, was helpful..
thx for examples also. again very helpful !
thank you!Rajeev
On Monday, August 21, 2017 5:26 PM, Henry Van Styn <vans...@cpan.org> wrote:
find_or_create relies on you supplying enough information to be able to “find”
a single matching row. So, whatever key you tell it to use, you must supply
that column in the data. If your primary key is ‘nodeid’ then you must supply
that. Since it looks like you don’t want to know that, and instead want to use
‘nodeName’ you have two choices (assuming you want to use find_or_create):
1. Get rid of nodeid altogether and make nodename the primary key2. Add a
separate unique constraint on nodename, and then, use that key in the
find_or_create…
Here is an example of what that looks like in the Result class:
https://github.com/vanstyn/Rapi-Blog/blob/master/lib/Rapi/Blog/DB/Result/User.pm#L51
And then here is an example of a find_or_create which makes use of it:
https://github.com/vanstyn/Rapi-Blog/blob/master/devel/import_frew_posts.pl#L43-L47
The critical bit there is that I’m supplying ‘username’ in the column data,
since that is the column of the ‘username_unique’ constraint. So for your case
you would have something like { key => ‘nodename_unique’ } assuming that’s what
you called it.
I don’t know how you are creating your DBIC schema, but if you are using
something like Schema::Loader to generate it from an existing database, you
would need to change the real, underlying schema. That is RDBMS-specific, but
to see what that looks like for the above example in SQLite:
https://github.com/vanstyn/Rapi-Blog/blob/master/sql/rapi_blog.sql#L18 (note
the use of the ‘UNIQUE’ keyword — this sets up an unique index for that column
on the table, which Schema::Loader detects and creates the DBIC constraint for
you)
Finally, if you don’t want to do that, just don’t use find_or_create, and do it
manually (i.e by calling ->search()->first, etc). However, if nodename is not
already required in your schema to be unique, it is probably a mistake. Since
this means there could be more than one row with the same ‘nodename’ value,
which guessing from what I’ve seen so far isn’t what you want. That is the
actual root of the problem — you are not giving DBIC a way to identify the
*single* row to match to “find”.
Hope this helps,Henry
On Aug 21, 2017, at 5:41 PM, Rajeev Prasad <rp.ne...@yahoo.com> wrote:
that does not work.
i get this err:
DBIx::Class::ResultSource::_minimal_valueset_satisfying_constraint(): Unable to
satisfy requested constraint 'primary', missing values for column(s): 'nodeid'
at ./test.pl line 80
when i remove the key part altogether i get new err:
DBIx::Class::Row::store_column(): No such column 'nodeName' on
Mydb::Schema::Result::Node at ./test.pl line 80
and we know that column exist. so i think there is some crap happening with
CamleCase in col names. I will try with changed col names. it is very
frustrating.
On Monday, August 21, 2017 9:38 AM, tirveni yadav <yadav.tirv...@gmail.com>
wrote:
On Mon, Aug 21, 2017 at 7:15 PM, Rajeev Prasad <rp.ne...@yahoo.com> wrote:
> no, nodeID is auto increment field as seen in tabel description. it is
> complaining there is no such key. i dont know why...
>
>
>
> On Monday, August 21, 2017 7:39 AM, tirveni yadav <yadav.tirv...@gmail.com>
> wrote:
>
>
> On Mon, Aug 21, 2017 at 6:50 AM, Rajeev Prasad <rp.ne...@yahoo.com> wrote:
>>
>> why am i getting this err in DBIx::Class ?
>>
>> $./update_db.pl
>> DBIx::Class::ResultSource::unique_constraint_columns(): Unknown unique
>> constraint nodeid on 'node' at ./update_from_ieds.pl line 80
>>
>>
>> relevant code:
>> ...
>> sub addNode
>> {
>> my $node = shift; my $lcNode = lc($node);
>> my $id = $schema
>> ->resultset('Node')
>> ->find_or_create
>> (
>> { nodeName => $lcNode },
>> { key => 'nodeid' }
>> );
>> return $id;
>> }
>
>
> Are you trying to do this:
>
>
> my $id = $schema
> ->resultset('Node')->find_or_create
> (
> {
> nodeName => $lcNode ,
>
> nodeID => $nodeid,
> }
> );
>
In my view, unique constraint naming issue:
Try this:
A.
In Node.pm:
__PACKAGE__->add_unique_constraint(node_nodeid => [ qw/nodeid/ ], );
B.
function add_node
{
my $rs_node = $dbic->resultset('Node');
my $row_node;
$row_node = $rs_node->find_or_create
(
{
nodename => 'October',
nodeid => '10',
Re: [Dbix-class] DBIx giving err due to camleCase??? not sure..
that does not work.
i get this err:
DBIx::Class::ResultSource::_minimal_valueset_satisfying_constraint(): Unable to
satisfy requested constraint 'primary', missing values for column(s): 'nodeid'
at ./test.pl line 80
when i remove the key part altogether i get new err:
DBIx::Class::Row::store_column(): No such column 'nodeName' on
Mydb::Schema::Result::Node at ./test.pl line 80
and we know that column exist. so i think there is some crap happening with
CamleCase in col names. I will try with changed col names. it is very
frustrating.
On Monday, August 21, 2017 9:38 AM, tirveni yadav <yadav.tirv...@gmail.com>
wrote:
On Mon, Aug 21, 2017 at 7:15 PM, Rajeev Prasad <rp.ne...@yahoo.com> wrote:
> no, nodeID is auto increment field as seen in tabel description. it is
> complaining there is no such key. i dont know why...
>
>
>
> On Monday, August 21, 2017 7:39 AM, tirveni yadav <yadav.tirv...@gmail.com>
> wrote:
>
>
> On Mon, Aug 21, 2017 at 6:50 AM, Rajeev Prasad <rp.ne...@yahoo.com> wrote:
>>
>> why am i getting this err in DBIx::Class ?
>>
>> $./update_db.pl
>> DBIx::Class::ResultSource::unique_constraint_columns(): Unknown unique
>> constraint nodeid on 'node' at ./update_from_ieds.pl line 80
>>
>>
>> relevant code:
>> ...
>> sub addNode
>> {
>> my $node = shift; my $lcNode = lc($node);
>> my $id = $schema
>> ->resultset('Node')
>> ->find_or_create
>> (
>> { nodeName => $lcNode },
>> { key => 'nodeid' }
>> );
>> return $id;
>> }
>
>
> Are you trying to do this:
>
>
> my $id = $schema
> ->resultset('Node')->find_or_create
> (
> {
> nodeName => $lcNode ,
>
> nodeID => $nodeid,
> }
> );
>
In my view, unique constraint naming issue:
Try this:
A.
In Node.pm:
__PACKAGE__->add_unique_constraint(node_nodeid => [ qw/nodeid/ ], );
B.
function add_node
{
my $rs_node = $dbic->resultset('Node');
my $row_node;
$row_node = $rs_node->find_or_create
(
{
nodename => 'October',
nodeid => '10',
},
{ key => 'node_nodeid' }
);
return $row_node;
}
--
Regards,
Tirveni Yadav
www.udyansh.org
www.bael.io
What is this Universe ? From what it arises ? Into what does it go?
In freedom it arises, In freedom it rests and into freedom it melts away.
Upanishads.
___
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 giving err due to camleCase??? not sure..
no, nodeID is auto increment field as seen in tabel description. it is
complaining there is no such key. i dont know why...
On Monday, August 21, 2017 7:39 AM, tirveni yadav <yadav.tirv...@gmail.com>
wrote:
On Mon, Aug 21, 2017 at 6:50 AM, Rajeev Prasad <rp.ne...@yahoo.com> wrote:
>
> why am i getting this err in DBIx::Class ?
>
> $./update_db.pl
> DBIx::Class::ResultSource::unique_constraint_columns(): Unknown unique
> constraint nodeid on 'node' at ./update_from_ieds.pl line 80
>
>
> relevant code:
> ...
> sub addNode
> {
> my $node = shift; my $lcNode = lc($node);
> my $id = $schema
> ->resultset('Node')
> ->find_or_create
> (
> { nodeName => $lcNode },
> { key => 'nodeid' }
> );
> return $id;
> }
Are you trying to do this:
my $id = $schema
->resultset('Node')->find_or_create
(
{
nodeName => $lcNode ,
nodeID => $nodeid,
}
);
--
Regards,
Tirveni Yadav
www.bael.io
What is this Universe ? From what it arises ? Into what does it go?
In freedom it arises, In freedom it rests and into freedom it melts away.
Upanishads.
___
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] DBIx giving err due to camleCase??? not sure..
why am i getting this err in DBIx::Class ?
$./update_db.pl
DBIx::Class::ResultSource::unique_constraint_columns(): Unknown unique
constraint nodeid on 'node' at ./update_from_ieds.pl line 80
relevant code:
...
sub addNode
{
my $node = shift; my $lcNode = lc($node);
my $id = $schema
->resultset('Node')
->find_or_create
(
{ nodeName => $lcNode },
{ key => 'nodeid' }
);
return $id;
}
...
other details:
mysql> desc node;
+---+---+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+---+---+--+-+-++
| nodeID | mediumint(5) unsigned | NO | PRI | NULL | auto_increment |
| nodeName | varchar(50) | NO | UNI | NULL | |
| nodeNotes | varchar(1000) | YES | | NULL | |
+---+---+--+-+-++
3 rows in set (0.00 sec)
$ cat Node.pm
use utf8;
package Mydb::Schema::Result::Node;
# Created by DBIx::Class::Schema::Loader
# DO NOT MODIFY THE FIRST PART OF THIS FILE
use strict;
use warnings;
use base 'DBIx::Class::Core';
__PACKAGE__->table("node");
__PACKAGE__->add_columns(
"nodeid",
{
data_type => "mediumint",
extra => { unsigned => 1 },
is_auto_increment => 1,
is_nullable => 0,
},
"nodename",
{ data_type => "varchar", is_nullable => 0, size => 50 },
"nodenotes",
{ data_type => "varchar", is_nullable => 1, size => 1000 },
);
__PACKAGE__->set_primary_key("nodeid");
__PACKAGE__->add_unique_constraint("node", ["nodename"]);
# Created by DBIx::Class::Schema::Loader v0.07045 @ 2017-08-18 19:20:59
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:t6+x6tFtAwGEHH/5qfBW/w
# You can replace this text with custom code or comments, and it will be
preserved on regeneration
1;
___
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 did it find or created?
hi Triveni ji,I have not encountered the 'race cond.' yet in various scripts.
(but) if you explain to me how to break find and replace into a transaction. I
would consider that. I am not very good at DBIx, i just happen to use it.Thank
you.Rajeev
On Sunday, August 20, 2017 6:08 AM, tirveni yadav <yadav.tirv...@gmail.com>
wrote:
On Sat, Aug 19, 2017 at 12:07 AM, Thomas Sibley <trsib...@uw.edu> wrote:
> Use find_or_new instead, and then check $id->in_storage. Make sure to call
> $id->insert when $id->in_storage is false.
>
> This is explained in the documentation for find_or_create, starting with “If
> you need to know if an existing row was found or a new one created…”.
>
>
> On Aug 18, 2017, at 10:34 , Rajeev Prasad <rp.ne...@yahoo.com> wrote:
>
> How do we know wether this function 'found' or created whatever was being
> asked to 'find_or_create' ???
>
> my $id = $schema
> ->resultset('Node')
> ->find_or_create
> (
> { nodeName => $node },
> { key => 'nodeID' }
> );
> how do we know wether $id was already existing in table?
I would suggest that you avoid find_or_create, unless you know what
are you are doing.
find_or_create is subject to Race condition as well.
Hence, best to do it inside a transaction.
Or keep it simple and do a find and then create inside a transaction.
--
Regards,
Tirveni Yadav
www.bael.io
What is this Universe ? From what it arises ? Into what does it go?
In freedom it arises, In freedom it rests and into freedom it melts away.
Upanishads.
___
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] searching decimal colum
i found the solution:
my $n = 33;
my $foo_rs = $schema
->resultset('Test')
->search
({
col1 => { like => '%.'.$n }
})->count;
On Friday, August 18, 2017 2:35 PM, Rajeev Prasad <rp.ne...@yahoo.com>
wrote:
before i go an do some operations. i need to know if the following will work?
so I have this column:
col123.3323.4612.3310.3323.54623.11.33100.33
I want to run two queries
1. select all rows with 23 before decimal (not *23.*, just 23.*).
my @foo = $schema ->resultset('Test')
->search
({
col1 => { like => 23'.%'}
});
print "values @foo\n";
I expect:23.3323.4623.54623.1
2. second i want to run query to select for given value after decimal.my
$foo_rs = $schema->resultset('Table')->search({
col1 => { like =>
23.'%' }});
I expect:23.3312.3310.331.33100.33
I get this error when i test:
$ ./test.pl
String found where operator expected at ./test.pl line 26, near "23'.%'"
(Missing operator before '.%'?)
Possible unintended interpolation of @foo in string at ./test.pl line 29.
syntax error at ./test.pl line 26, near "23'.%'"
Global symbol "@foo" requires explicit package name (did you forget to declare
"my @foo"?) at ./test.pl line 29.
Execution of ./test.pl aborted due to compilation errors.
___
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] searching decimal colum
before i go an do some operations. i need to know if the following will work?
so I have this column:
col123.3323.4612.3310.3323.54623.11.33100.33
I want to run two queries
1. select all rows with 23 before decimal (not *23.*, just 23.*).
my @foo = $schema ->resultset('Test')
->search
({
col1 => { like => 23'.%'}
});
print "values @foo\n";
I expect:23.3323.4623.54623.1
2. second i want to run query to select for given value after decimal.my
$foo_rs = $schema->resultset('Table')->search({
col1 => { like =>
23.'%' }});
I expect:23.3312.3310.331.33100.33
I get this error when i test:
$ ./test.pl
String found where operator expected at ./test.pl line 26, near "23'.%'"
(Missing operator before '.%'?)
Possible unintended interpolation of @foo in string at ./test.pl line 29.
syntax error at ./test.pl line 26, near "23'.%'"
Global symbol "@foo" requires explicit package name (did you forget to declare
"my @foo"?) at ./test.pl line 29.
Execution of ./test.pl aborted due to compilation errors.
___
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] find_or_create did it find or created?
How do we know wether this function 'found' or created whatever was being asked
to 'find_or_create' ???
my $id = $schema
->resultset('Node')
->find_or_create
(
{ nodeName => $node },
{ key => 'nodeID' }
);
how do we know wether $id was already existing in table?
___
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] Strangely does DBIx change table names from MySQL?
Hello,
Note: this question is also posted on Stack Overflow, a few minutes back.
|
| While using ./create-schema-mydb.pl I realized that the table name "People"
is changed to "Person" in DBIx. I am not sure how? or why?
It is not even a reserved word.
in MySQL:people innoDB utf8
create-schema script:$ cat ./create-schema-mydb.pl
#!/usr/bin/perl
use strict;
use warnings;
use DBIx::Class::Schema::Loader qw/make_schema_at/;
make_schema_at(
"Mydb::Schema",
{debug => 0, dump_directory => "../db/",
generate_pod => 0,},
["dbi:mysql:mydb:localhost:3306", 'mydb', 'password'],
);
It shows up like this after create-schema... note the change in name from
People to Person, but inside the .pm file table name is retained as People !!!
Result$ cat Person.pm
use utf8;
package Mydb::Schema::Result::Person;
# Created by DBIx::Class::Schema::Loader
# DO NOT MODIFY THE FIRST PART OF THIS FILE
use strict;
use warnings;
use base 'DBIx::Class::Core';
__PACKAGE__->table("people");
__PACKAGE__->add_columns(
"pplid",
{
data_type => "smallint",
extra => { unsigned => 1 },
is_auto_increment => 1,
is_nullable => 0,
},
...
...
only relevant portion shown above...
Thank you.
Rajeev
|
___
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 HAVING and COUNT error
> SELECT me.i_id, me.t_id FROM itemList me
> WHERE ( t_id IN ( 55 ) )
> GROUP BY i_id
> HAVING count 1 = 1
>
> what is wrong in this SQL??. (I am assuming that by now DBIx syntax is
> good)
here it is only number (55) so count is also just 1. is this not correct?
On Monday, September 5, 2016 12:02 AM, Dmitry L. <dim0...@gmail.com> wrote:
HAVING COUNT(1) = 3
vs
HAVING count 1 = 1
On 5 September 2016 at 06:13, Rajeev Prasad <rp.ne...@yahoo.com> wrote:
> hello all,
>
> thanks for response. sorry if i sent email directly to you. (i think i just
> did a replay all)
>
> i tried that and many other way, but i am going crazy that it just wont
> work
>
> SQL I 'hope' to implement is:
>
> SELECT *
> FROM itemlist
> WHERE t_id IN (1,2,3)
> GROUP BY i_id
> HAVING COUNT(1) = 3
>
>
> CODE:
> $tStr is 55 (has only one element)
> $tCount = 1 (no. of items above.)
>
>> my $obj_rs = $schema->resultset('itemList')->serach(
>> { t_id => { -in => [ $tStr ] }},
>> {
>> group_by => [ qw(i_id) ],
>> having => { 'count 1' => $tCount },
>> }
>> );
>
>
> i get this error:
>
> [cgi:error] [pid 772] [client ] AH01215:
> DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st
> execute failed: You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use near '1
> = '1'' at line 1 [for Statement "SELECT me.t_id, me.i_id FROM itemList me
> WHERE ( t_id IN ( ? ) ) GROUP BY o_id HAVING count 1 = ?" with ParamValues:
> 0='55', 1=1] at /var/ww...
>
>
> which i guess converts to:
>
> SELECT me.i_id, me.t_id FROM itemList me
> WHERE ( t_id IN ( 55 ) )
> GROUP BY i_id
> HAVING count 1 = 1
>
> what is wrong in this SQL??. (I am assuming that by now DBIx syntax is
> good)
>
>
>
>
> On Saturday, September 3, 2016 4:17 AM, Stefan Hornburg (Racke)
> <ra...@linuxia.de> wrote:
>
>
> On 09/03/2016 03:38 AM, Rajeev Prasad wrote:
>> DBIx error in webserver log:
>>
>> [cgi:error] [pid 29640] ... AH01215: Can't locate object method "HAVING"
>> via package "COUNT" (perhaps you forgot to load
>> "COUNT"?)
>>
>>
>> the query:
>>
>> my $obj_rs = $schema->resultset('itemList')->serach(
>> { t_id => { -in => [ $tStr ] }},
>> {
>> group_by => [ qw(i_id) ],
>> HAVING COUNT('t_id') = $tCount
>> }
>> );
>>
>>
>> currently database has no records which match the query. but i was not
>> expecting such an error.
>>
>> what is wrong in my statement?
>
> You can't just drop in literal SQL like that and expect it to work - because
> Perl now assumes
> "HAVING COUNT" being Perl code. I suppose "use strict; use warnings" would
> have alerted you of
> that.
>
> It is possible to you use literal SQL with DBIx::Class, please refer to the
> docs.
>
> I would suggest a different approach - using subqueries. Please take a look
> at the excellent
> advent calendar post from fREW:
>
> http://www.perladvent.org/2012/2012-12-21.html
>
> This is still accurate.
>
> I covered a few use cases in my presentation at the Alpine Perl Workshop:
>
> https://www.linuxia.de/talks/alpine2016/dbic-pr-en-beamer.pdf
>
> Regards
> Racke
>
>>
>> thank you.
>> Rajeev
>>
>>
>> ___
>> 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
>>
>
>
> --
> Ecommerce and Linux consulting + Perl and web application programming.
>
>
>
> ___
> 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
>
>
>
>
> ___
> 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
--
//wbr, Dmitry L.
___
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 HAVING and COUNT error
hello all,
thanks for response. sorry if i sent email directly to you. (i think i just did
a replay all)
i tried that and many other way, but i am going crazy that it just wont work
SQL I 'hope' to implement is:
SELECT *
FROM itemlist
WHERE t_id IN (1,2,3)
GROUP BY i_id
HAVING COUNT(1) = 3
CODE:$tStr is 55 (has only one element)
$tCount = 1 (no. of items above.)
> my $obj_rs = $schema->resultset('itemList')->serach(
> { t_id => { -in => [ $tStr ] }},
> {
> group_by => [ qw(i_id) ],
> having => { 'count 1' => $tCount },
> }
> );
i get this error:
[cgi:error] [pid 772] [client ] AH01215:
DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st
execute failed: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '1 =
'1'' at line 1 [for Statement "SELECT me.t_id, me.i_id FROM itemList me WHERE (
t_id IN ( ? ) ) GROUP BY o_id HAVING count 1 = ?" with ParamValues: 0='55',
1=1] at /var/ww...
which i guess converts to:
SELECT me.i_id, me.t_id FROM itemList me
WHERE ( t_id IN ( 55 ) )
GROUP BY i_id
HAVING count 1 = 1
what is wrong in this SQL??. (I am assuming that by now DBIx syntax is
good)
On Saturday, September 3, 2016 4:17 AM, Stefan Hornburg (Racke)
<ra...@linuxia.de> wrote:
On 09/03/2016 03:38 AM, Rajeev Prasad wrote:
> DBIx error in webserver log:
>
> [cgi:error] [pid 29640] ... AH01215: Can't locate object method "HAVING" via
> package "COUNT" (perhaps you forgot to load
> "COUNT"?)
>
>
> the query:
>
> my $obj_rs = $schema->resultset('itemList')->serach(
> { t_id => { -in => [ $tStr ] }},
> {
> group_by => [ qw(i_id) ],
> HAVING COUNT('t_id') = $tCount
> }
> );
>
>
> currently database has no records which match the query. but i was not
> expecting such an error.
>
> what is wrong in my statement?
You can't just drop in literal SQL like that and expect it to work - because
Perl now assumes
"HAVING COUNT" being Perl code. I suppose "use strict; use warnings" would have
alerted you of
that.
It is possible to you use literal SQL with DBIx::Class, please refer to the
docs.
I would suggest a different approach - using subqueries. Please take a look at
the excellent
advent calendar post from fREW:
http://www.perladvent.org/2012/2012-12-21.html
This is still accurate.
I covered a few use cases in my presentation at the Alpine Perl Workshop:
https://www.linuxia.de/talks/alpine2016/dbic-pr-en-beamer.pdf
Regards
Racke
>
> thank you.
> Rajeev
>
>
> ___
> 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
>
--
Ecommerce and Linux consulting + Perl and web application programming.
___
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
___
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] DBIx HAVING and COUNT error
DBIx error in webserver log:
[cgi:error] [pid 29640] ... AH01215: Can't locate object method "HAVING" via
package "COUNT" (perhaps you forgot to load "COUNT"?)
the query:
my $obj_rs = $schema->resultset('itemList')->serach(
{ t_id => { -in => [ $tStr ] }},
{
group_by => [ qw(i_id) ],
HAVING COUNT('t_id') = $tCount
}
);
currently database has no records which match the query. but i was not
expecting such an error.
what is wrong in my statement?
thank you.Rajeev
___
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] how to repersent N:M in DBIx?
Pl refer to this link, it has my exact problem (but it only tells about SQL solution): http://stackoverflow.com/questions/11409869/mysql-nm-relationship-find-rows-with-several-specific-relations I have three tables:1. products:-> prod-id, prod-name 2. tags:-> tag-id, tag-name 3. product_tags::-> prod-id, csv_tag_id i read elsewhere on internet that this is not a good database/table design. so i am ready to change that too, but i could not think of any other way to represent this relationship. a given product could have multiple tags, but i have to find - at a given time - only those products which have 'ALL' of multiple given tags. the SQL suggested on the page is: SELECT a.* FROM products a INNER JOIN product_tags b ON a.product_id = b.product_id WHERE b.tag_id IN (1,23,54) GROUP BY a.product_id HAVING COUNT(1) = 3thank you.Rajeev ___ 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] fetching a column value directly
thx all !!! On Monday, November 4, 2013 1:46 AM, Lars Dɪᴇᴄᴋᴏᴡ 迪拉斯 da...@cpan.org wrote: Your code is correct. `search` may return several results, so you must either iterate over them or assign/do something with all the results, e.g. -search(…)-all returns a list of Host resultrow objects. If you are sure the hosts is unique and therefore `search` will only find one result, you can simply write: my $host_addr = ………-search(…)-first-addr Note the automatically generated accessor for the column. ___ 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 ___ 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] fetching a column value directly
hello,
I have this host table, which has all unique records. So i want to get a
specific column value for a specific host. i am doing below, but i think it is
not efficient. is there a better way?
my $hostAdd_rs =
$schema-resultset('Host')-search({host=$host},{select=[qw/addr/]});
while (my $rec = $hostAdd_rs-next() ) {
$hostAdd = $rec-get_column('addr');
}
can we not do it in a single line command? without any while loop?
i tried, but getting various errors.
ty.
Rajeev
___
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] why is this returning an hash?
from here i understood, that if i collect output in array element, i will get
an array.
http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/ResultSet.pm#search
in below, $id is unique in the table, so it returns just one row.
my @item_array = $schema-resultset('Mytable')-search(
{ item_id = $id },
{ select = [qw/item_status/] }
);
print print array =@item_array\n;
./test.pl
node status=Db::Schema::Result::Mytable=HASH(0x17b9cf8)
$
I actually/finally want to get the value of an specific column by supplying
$id. not sure how to get that.
___
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] what is the err in this DBIx select...
hello Will,
thx, that worked. but i am not getting anyother item_type returned. there are
many more item_types I would like to get them all, in addition to the two which
have condition associated with them.
From: Will Crawford billcrawford1...@gmail.com
To: Rajeev Prasad rp.ne...@yahoo.com; DBIx::Class user and developer list
dbix-class@lists.scsys.co.uk
Sent: Tuesday, April 16, 2013 6:32 AM
Subject: Re: [Dbix-class] what is the err in this DBIx select...
You need the first arg to search there to be [ ... ] rather than { ... }.
On 15 April 2013 20:47, Rajeev Prasad rp.ne...@yahoo.com wrote:
I am meaning to run...
search records where:
if item_type='XX' _then_ item column should contain '%01par%' or
'%01pqt%'
and,
if item_type='YY' OR item_type='YZ' _then_ item column should contain
'%00mxz%'
and,
for all other item_type no condition (select them)
and i am using following: but i get error in DBIx: err in sql syntax...
so what am i doing arong below?
my @itemListRef = $schema-resultset('Itemlist')-search(
{
{
item_type = 'XX',
item = [{ like = '%01par%' },{ like = '%01pqt%' }]
},
{
item_type = [{ like = 'YY' }, { like = 'YZ' } ],
item = { like = '%00mxz%' }
}
},
{ select = [qw/item_id item item_alias/] }
);
___
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
___
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] what is the err in this DBIx select...
I am meaning to run...
search records where:
if item_type='XX' _then_ item column should contain '%01par%' or
'%01pqt%'
and,
if item_type='YY' OR item_type='YZ' _then_ item column should contain
'%00mxz%'
and,
for all other item_type no condition (select them)
and i am using following: but i get error in DBIx: err in sql syntax...
so what am i doing arong below?
my @itemListRef = $schema-resultset('Itemlist')-search(
{
{
item_type = 'XX',
item = [{ like = '%01par%' },{ like = '%01pqt%' }]
},
{
item_type = [{ like = 'YY' }, { like = 'YZ' } ],
item = { like = '%00mxz%' }
}
},
{ select = [qw/item_id item item_alias/] }
);
___
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] update a row only if any column has changed
thx Brendan, the source is a text file. I guess I will use MySQL tools to load it into the database everyday, deleting the table completely before recreating. i guess thats the best way? From: Brendan Byrd p...@resonatorsoft.org To: Rajeev Prasad rp.ne...@yahoo.com; DBIx::Class user and developer list dbix-class@lists.scsys.co.uk Sent: Friday, April 5, 2013 10:01 PM Subject: Re: [Dbix-class] update a row only if any column has changed On Fri, Apr 5, 2013 at 7:45 PM, Rajeev Prasad rp.ne...@yahoo.com wrote: hello, I have a table with around 2,000,000 records (15 columns). I have to sync this from an outside source once everyday. not all records are changed/removed /new-added everyday. so what is the best way to update only those which have changed/added/or deleted? i can use update_or_create but that will update (re-write the row) even if nothing has changed in the row/record. wont that be an overhead? how can i escape that? what would be the fastest and least resources consuming way to do this table update? I also have another table with 500,000 rows and i wish to implement the same solution to that too. Geesh, when your data loads are hitting 6 and 7 digits rows figures, you should seriously consider the tools that come with the RDBMS, like replication. Is your outside source a modern RDBMS that features master/slave replication? Otherwise, without some sort of field indicator on the source side saying that this was a modified row, you can't tell if it's been changed or not. That means DBIC, or whatever other ETL middleware you choose, will have to compare every single row to see if it exists or not. With that many rows, it's better off to use the UPSERT SQL statements for mass updating. In MySQL, it's commands like REPLACE and INSERT ... ON DUPLICATE KEY UPDATE In Oracle, it's MERGE. You also mentioned removed. So you want an exact copy of the source table? Yeah, screw all of the tip-toeing around the existing data and just TRUNCATE the table, followed by the best BULK INSERT command your RDBMS recommends. (For example, MySQL recommends LOAD DATA INFILE.) And make sure you don't do something crazy, like reading the entire source table into memory via Perl variables. Keep in mind that almost none of this recommends using DBIC. DBIC is for making changes in smaller quantities than what you're asking. YMMV, so consult your DBA folks first before you start dumping massive quantities of data into their servers. -- Brendan Byrd p...@resonatorsoft.org Brendan Byrd bb...@cpan.org ___ 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] update a row only if any column has changed
hello, I have a table with around 2,000,000 records (15 columns). I have to sync this from an outside source once everyday. not all records are changed/removed /new-added everyday. so what is the best way to update only those which have changed/added/or deleted? i can use update_or_create but that will update (re-write the row) even if nothing has changed in the row/record. wont that be an overhead? how can i escape that? what would be the fastest and least resources consuming way to do this table update? I also have another table with 500,000 rows and i wish to implement the same solution to that too. ty. Rajeev ___ 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] how to empty a Table? when there is no primary key
I tried to use delete_all, but there is no primary key in the table so it
failed. what else can i do?
I need to delete all rows from a table and then add new ones based on logic.
this happens once a day
$schema-resultset('Mytable')-delete_all;
DBIx::Class::ResultSet::delete_all(): Operation requires a primary key to be
declared on
___
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] how to build this query? col1 not like %xx% AND col1 like %yy%
thanks Paul, very helpful. and to the point. I was able to chain resultset.
- Original Message -
From: Paul Findlay paul.find...@fonterra.com
To: DBIx::Class user and developer list dbix-class@lists.scsys.co.uk
Cc:
Sent: Tuesday, March 19, 2013 8:26 PM
Subject: RE: [Dbix-class] how to build this query? col1 not like %xx% AND
col1 like %yy%
Hi Rajeev,
Not sure what you have tried, but this should work:
my $rs_1 = $rs-search({
col1 = { -not_like = %xx%, -like = %yy% }
});
Then to do extra searches on the result set, just call search again.
my $rs_2 = $rs_1-search({ col1 = { -not_like = '%zz% }});
Documentation for chaining resultsets:
https://metacpan.org/module/DBIx::Class::ResultSet#Chaining-resultsets
And on where clauses, look at the SQL::Abstract documentation:
The main logic of this module is that things in arrays are OR'ed, and things
in hashes are AND'ed.
https://metacpan.org/module/SQL::Abstract#WHERE-CLAUSES
- Paul
DISCLAIMER
This email contains information that is confidential and which may be legally
privileged. If you have received this email in error, please notify the sender
immediately and delete the email. This email is intended solely for the use of
the intended recipient and you may not use or disclose this email in any way.
___
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
___
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] how to build this query? col1 not like %xx% AND col1 like %yy%
Hello, I tried quite a few things, but it is not working... col1 not like %xx% AND col1 like %yy% OR, if someone can tell me how can i run a select query again on an rs obtained by running a select query on table. 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
[Dbix-class] DBIx Schema Loader ownership question.
I am running following code as myself. but the resulting folders are being
created as www:other I am not able to understand why is that so?
use strict;
use warnings;
use DBIx::Class::Schema::Loader qw/make_schema_at/;
make_schema_at(
Mydb::Schema,
{debug = 0, dump_directory = ../db/, generate_pod = 0,},
[dbi:mysql:bvoiptsm:localhost:3306, 'userid', 'password'],
);
It generates following structure. (see I running as userid = uid(my personal
uid) but the output is owned by www user!). I am not the admin on this server
and I am not sure how much the admin knows about DBIx, so I want to know from
this list, exactly what I should be telling to admin to fix.
ls -ltR
.:
total 1
drwxrwxr-x 3 www other 512 Feb 27 23:23 Mydb
./Mydb:
total 2
drwxrwxr-x 3 www other 512 Feb 27 23:23 Schema
-rw-r- 1 www other 461 Feb 27 23:23 Schema.pm
./Mydb/Schema:
total 1
drwxrwxr-x 2 www other 512 Feb 27 23:23 Result
./Mydb/Schema/Result:
total 4
-rw-r- 1 www other 1227 Feb 27 23:23 Table1.pm
-rw-r- 1 www other 1268 Feb 27 23:23 Table2.pm___
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] Re: help needed in forming a 'and' and 'or' select query using DBIx::Class
Thanks to all for help and explanation as to why so.
I liked the below solution the best. thx again.
From: Dagfinn Ilmari Mannsåker ilm...@ilmari.org
To: dbix-class@lists.scsys.co.uk
Sent: Monday, January 21, 2013 4:34 AM
Subject: [Dbix-class] Re: help needed in forming a 'and' and 'or' select query
using DBIx::Class
Michele Beltrame m...@cattlegrid.info writes:
Hello!
columnA = x AND ( columnB = zz OR columnB LIKE '%mm%' )
It should be like this:
$val = 'mm';
$my_rs = $schema-resultset('Mytable')-search({
columnA = 'x',
-or = [
columnB = 'zz',
columnB = { like = '%'.$val.'%' }
],
});
Or a slightly more concise version:
my $rs = $schema-resultset('MyTable')-search({
columnA = 'x',
columnB = [ 'zz', { like = '%'.$val'%' } ],
});
--
The surreality of the universe tends towards a maximum -- Skud's Law
Never formulate a law or axiom that you're not prepared to live with
the consequences of. -- Skud's Meta-Law
___
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___
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] help needed in forming a 'and' and 'or' select query using DBIx::Class
Hello,
I am trying to build a query based on two column values as folows:
columnA = x (one char, any char - not null)
columnB = zz _or_ x,xx,yyy,mm (comma seperated values or just one value)
I am looking for rows where: (columnB contains either zz or mm...)
columnA = x AND ( columnB = zz OR columnB LIKE '%mm%' )
I am trying following which is not giving correct results.
$val = 'mm';
$my_rs = $schema-resultset('Mytable')-search({
columnA = 'x',
columnB = { like = 'zz' },
columnB = { like = '%'.$val.'%' }
});
pl advice.___
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] list 2 columns for all records in table not working
this straight lift from documentation is not working. what could i be doing
wrong?
my @all_artists = $schema-resultset('Artist')-search(undef, { columns =
[qw/name artistid/], });
my query is:
my @items = $schema-resultset('Itemlist')-search(
undef, #no search condition means all records
{ select = [qw/itemid item/] }
);
print @items;
the output is count of rows in the table, and not the data requested. when i
try to print the array via this loop:
foreach (@items) { print $_\n;
}I get this output...
Itemdb::Schema::Result::Itemlist=HASH(0x90c7000)
Itemdb::Schema::Result::Itemlist=HASH(0x90c73a0)
Itemdb::Schema::Result::Itemlist=HASH(0x90c7190)
Itemdb::Schema::Result::Itemlist=HASH(0x90c70f0)
Itemdb::Schema::Result::Itemlist=HASH(0x90c6f60)
Itemdb::Schema::Result::Itemlist=HASH(0x90c27a0)
.
ty.
Rajeev___
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] list 2 columns for all records in table not working
hello Paul,
thx. That helped. even though i could see the 'HASH' in my output, i was under
the impression (from the doc..) that i should get an array straightway.
now another problem. following is not working, i am currently using work
around..
if ( $str=~/\b$item-itemid\b/ ) {
}
(i tried few things above, in the end) my workaround
my $tmpvar=$item-itemid ;
if ( $str=~/\b$tmpvar\b/ ) {
}
i was thinking if i do not have to use a variable just for one step...
From: Paul Findlay paul.find...@fonterra.com
To: Rajeev Prasad rp.ne...@yahoo.com; DBIx::Class user and developer list
dbix-class@lists.scsys.co.uk
Sent: Tuesday, October 16, 2012 9:30 PM
Subject: RE: [Dbix-class] list 2 columns for all records in table not working
Hi Rajeev,
You are tried to print an array, and then tried to print each object in an
array. Since each object is a blessed hash, the
“Itemdb::Schema::Result::Itemlist=HASH(..)” is appearing because perl doesn’t
know any better way to convert it to a string.
Have you considered:
foreach my $item (@items) {
print $item-itemid, ' ', $item-artistid, \n;
}
Instead?
This is documented in “Basic Usage”
https://metacpan.org/module/DBIx::Class::Manual::Intro#Basic-usage
(sorry about the HTML email everyone)
Paul Findlay
Process Information Engineer
paul.findlay@fonterra.comdirect +64 7 849 7871
Fonterra Co-operative Group Limited, Te Rapa, Hamilton, New Zealand
DISCLAIMER
This email contains information that is confidential and which may be legally
privileged. If you have received this email in error, please notify the sender
immediately and delete the email. This email is intended solely for the use of
the intended recipient
and you may not use or disclose this email in any way.
___
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] resultset search question
Hello, I guess a very basic question.
In the following example: from
http://search.cpan.org/~mstrout/DBIx-Class-0.08202/lib/DBIx/Class/ResultSet.pm
my @cds = $cd_rs-search({ year = 2001 }); # ... WHERE year = 2001 my
$new_rs = $cd_rs-search({ year = 2005 });
are we to understand that, table is 'indexed' or at least has a unique
constraint on the column year? or can I do a search on non-index,
non-constrained, just a normal column?
thank you.
Rajeev___
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, row update problem
thank you Aaron, that was perfect.
From: Aaron Crane dbix-cl...@aaroncrane.co.uk
To: Rajeev Prasad rp.ne...@yahoo.com; DBIx::Class user and developer list
dbix-class@lists.scsys.co.uk
Sent: Thursday, October 4, 2012 5:51 AM
Subject: Re: [Dbix-class] DBIX, row update problem
Rajeev Prasad rp.ne...@yahoo.com wrote:
I am only intending to update a column in a row based on 'item' value. Item
value is unique constraint column.
DBIx code:
$schema-resultset('Itemlist')-update(
{
item = $item,
item_comment = $mycomments
},
{key ='item'} #uniq constraint on item
);
The update method on a resultset only accepts one argument, and that
argument specifies what updates are to be applied to the rows that
would be matched by that resultset. So this code is trying to update
the item and item_comment columns in *every* Itemlist row.
If I understand your goal, you're trying to update the item_comment
column in the (unique) row where the item column has the value
$item. That's done by constraining the resultset to be updated:
$schema-resultset('Itemlist')
-search({ item = $item })
-update({ item_comment = $mycomments });
That is, the search first restricts the resultset to consider only
rows whose item column is $item; then the update updates the
item_comment for any matching row. (Which, in this case, should be
precisely one row.)
Does that help?
--
Aaron Crane ** http://aaroncrane.co.uk/___
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] DBIX, row update problem
I am only intending to update a column in a row based on 'item' value. Item
value is unique constraint column.
DBIx code:
$schema-resultset('Itemlist')-update(
{
item = $item,
item_comment = $mycomments
},
{key ='item'} #uniq constraint on item
);
when i look in table row it is updated!!! but i get this err in apache log and
throws a 500 http error. (pl script dies)
generates this error in apache log:
table name itemlist
DBIx::Class::ResultSet::update(): DBI Exception: DBD::mysql::st execute
failed: Duplicate entry 'item1' for key 'item' [for Statement UPDATE itemlist
SET item = ?, item_comment = ? with ParamValues: 0='bigitem', 1='test
comment'] at xxxyyy.pl line 60, referer: http://xxxyyyzzz
___
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] DBIx how to update_or_create a table with primary index is autoincrement ? getting erroneous data
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?
___
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
found this on web:
So in summary, for tables with autoincrement primary keys -find requires that
you provide the primary key in the query, and -create requires that you don't
- this means that you cannot pass the same query hash to both methods and this
is what -update_or_create does.
so that means my update_or_create is never going to work on an empty table???
really? I am stuck pl help.
From: Rajeev Prasad rp.ne...@yahoo.com
To: DBIx::Class and developer list dbix-class@lists.scsys.co.uk
Sent: Thursday, September 27, 2012 11:55 AM
Subject: [Dbix-class] DBIx how to update_or_create a table with primary index
is autoincrement ? getting erroneous data
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?
___
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___
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
for anyone's help in future... creating a uniq constraint on a table field and using it in supplied field and key field (in update_or_create) resolved the issue. thank you Ronald for input to cement my hunch. From: Ronald J Kimball rkimb...@pangeamedia.com To: Rajeev Prasad rp.ne...@yahoo.com Cc: DBIx::Class user and developer list dbix-class@lists.scsys.co.uk Sent: Thursday, September 27, 2012 1:03 PM Subject: 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
[Dbix-class] search any matching word in given string in table column which has only one word
here is given string of type:... as23,rt54,yh79 and i have to lookup in a table which has column id which has only one four letter word. i have to select that row in which the colum 'id' value matches any of the word in given string... table column: id contents like: | id| |sd32| |wa32| |rt54| after that match row with id='rt54' should be returned. i am not able to figure the sql statement for this? eventually i want to have DBIx statement to use in my perl code. So either suggestion will work for me. ty. Rajeev ___ 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] search any matching word in given string in table column which has only one word
thanks a lot that worked!
From: Rodrigo rodrigol...@gmail.com
To: Rajeev Prasad rp.ne...@yahoo.com; DBIx::Class user and developer list
dbix-class@lists.scsys.co.uk
Cc: mysql list my...@lists.mysql.com
Sent: Saturday, August 11, 2012 11:12 AM
Subject: Re: [Dbix-class] search any matching word in given string in table
column which has only one word
Maybe something like this will do:
my $str = 'as23,rt54,yh79';
$schema-resultset('MyTable')
-search({ id=[ split /,/ = $str ] });
regards,
rodrigo
On Sat, Aug 11, 2012 at 6:05 PM, Rajeev Prasad rp.ne...@yahoo.com wrote:
here is given string of type:... as23,rt54,yh79
and i have to lookup in a table which has column id which has only one four
letter word. i have to select that row in which the colum 'id' value matches
any of the word in given string...
table column: id
contents like:
| id|
|sd32|
|wa32|
|rt54|
after that match row with id='rt54' should be returned.
i am not able to figure the sql statement for this? eventually i want to have
DBIx statement to use in my perl code. So either suggestion will work for me.
ty.Rajeev
___
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
___
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] DBIx schema loader (dump schema using make_schema_at) wierd behaviour
Hello,
am I missing anything? when I dump schema of my database using schema loader's
make_schema_at. Some .pm file names under Result corrosponding to folders have
names chnaged! they do not match to table names!!
e.g. table name costlycookies shows up as Costlycooky.pm !!! another table
cheapcookis show up as Cheapcooky.pm another table named els is showing up a
El.pm !!!
when i cat these files the table names and other details are all good. But due
to this 'wrong' .pm file naming I am getting this error in my code:
DBIx::Class::Schema::resultset(): Can't find source for Costlycookies at
/my/path/cgi-bin/myscript.pl line 53
line 53 in my code:...
$coki_rs = $schema-resultset('Costlycookies')-search(
why is this happening? before this fresh dumping operation, I changed the
indexed key from general indexed key to primary index key on almost all tables,
but the old table's *.pm file names still look good.
___
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] how to avoid adding duplicate row to table containing unique field/column
Hello,
In my test script below, i am trying to figure out how to suppress the error
mesg when the the script try to add a previously presen tentry into the table.
OR better even, to make sure that the script should not even try to update the
table, if the record is already present in table. (table may grow very big in
future).
table:
Action
Keyname
Type
Unique
Packed
Column
Cardinality
Collation
Null
Comment
Edit Drop
PRIMARYBTREE Yes No userid 3 A
Edit Drop
useridBTREE Yes No userid 3 A
mysql desc testdbusers;
+--+-+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+--+-+--+-+-+---+
| userid | char(6) | NO | PRI | NULL | |
| password | varchar(10) | YES | | NULL | |
+--+-+--+-+-+---+
2 rows in set (0.00 sec)
mysql
mysql show index from testdbusers;
+-++--+--+-+---+-+--++--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
| Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-++--+--+-+---+-+--++--++-+
| testdbusers | 0 | PRIMARY | 1 | userid | A
| 3 | NULL | NULL | | BTREE | |
| testdbusers | 0 | userid | 1 | userid | A
| 3 | NULL | NULL | | BTREE | |
+-++--+--+-+---+-+--++--++-+
2 rows in set (0.00 sec)
mysql
script:
#!/usr/bin/perl
use strict;
use warnings;
use lib '../testdb';
use Mysqltestdb::Schema;
my $schema;
my @newusers;
$schema =
Mysqltestdb::Schema-connect(dbi:mysql:dbname=mysqltestdb:localhost:3306,
'root', 'mypassword');
@newusers = (['te1234', 'pass'], ['te4567', 'pass']);
$schema-populate('Testdbuser', [
[qw/userid password/],
@newusers,
]);
I get this info message out from script(error):
DBIx::Class::Schema::populate(): Duplicate entry 'te1234' for key 'PRIMARY' for
populate slice:
{
password = pass,
userid = te1234
}___
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] how to avoid adding duplicate row to table containing unique field/column
there is a find_and_create function which can do the job, i am not able to find
help on it. does anyone know?
From: Rajeev Prasad rp.ne...@yahoo.com
To: dbix-class@lists.scsys.co.uk dbix-class@lists.scsys.co.uk
Sent: Wednesday, October 12, 2011 10:33 AM
Subject: [Dbix-class] how to avoid adding duplicate row to table containing
unique field/column
Hello,
In my test script below, i am trying to figure out how to suppress the error
mesg when the the script try to add a previously presen tentry into the table.
OR better even, to make sure that the script should not even try to update the
table, if the record is already present in table. (table may grow very big in
future).
table:
Action
Keyname
Type
Unique
Packed
Column
Cardinality
Collation
Null
Comment
Edit Drop
PRIMARYBTREE Yes No userid 3 A
Edit Drop
useridBTREE Yes No userid 3 A
mysql desc testdbusers;
+--+-+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+--+-+--+-+-+---+
| userid | char(6) | NO | PRI | NULL | |
| password | varchar(10) | YES | | NULL | |
+--+-+--+-+-+---+
2 rows in set (0.00 sec)
mysql
mysql show index from testdbusers;
+-++--+--+-+---+-+--++--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
| Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-++--+--+-+---+-+--++--++-+
| testdbusers | 0 | PRIMARY | 1 | userid | A
| 3 | NULL | NULL | | BTREE
| |
| testdbusers | 0 | userid | 1 | userid | A
| 3 | NULL | NULL | | BTREE | |
+-++--+--+-+---+-+--++--++-+
2 rows in set (0.00 sec)
mysql
script:
#!/usr/bin/perl
use strict;
use warnings;
use lib '../testdb';
use Mysqltestdb::Schema;
my $schema;
my
@newusers;
$schema =
Mysqltestdb::Schema-connect(dbi:mysql:dbname=mysqltestdb:localhost:3306,
'root', 'mypassword');
@newusers = (['te1234', 'pass'], ['te4567', 'pass']);
$schema-populate('Testdbuser', [
[qw/userid password/],
@newusers,
]);
I get this info message out from script(error):
DBIx::Class::Schema::populate(): Duplicate entry 'te1234' for key 'PRIMARY' for
populate slice:
{
password = pass,
userid = te1234
}
___
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___
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] how to avoid adding duplicate row to table containing unique field/column
thx Rob,
I tried to use it like this:
for my $aref ( @newusers ) {
print adding [@$aref], or say $aref-[0] \n;
$schema-resultset('Testdbuser')-find_or_create(
{
userid = $aref-[0],
password = $aref-[1],
},
{ key = 'userid' }
);
}
getting error:
DBIx::Class::ResultSet::find_or_create(): Unknown unique constraint userid on
'testdbusers' at ./insertdb.pl line 25
see below that my table does have a unique constraint on this column.
From: Rob Kinyon rob.kin...@gmail.com
To: Rajeev Prasad rp.ne...@yahoo.com; DBIx::Class user and developer list
dbix-class@lists.scsys.co.uk
Sent: Wednesday, October 12, 2011 11:41 AM
Subject: Re: [Dbix-class] how to avoid adding duplicate row to table containing
unique field/column
find_or_create is a method on DBIx::Class::ResultSet
On Wed, Oct 12, 2011 at 12:06, Rajeev Prasad rp.ne...@yahoo.com wrote:
there is a find_and_create function which can do the job, i am not able to
find help on it. does anyone know?
From: Rajeev Prasad rp.ne...@yahoo.com
To: dbix-class@lists.scsys.co.uk dbix-class@lists.scsys.co.uk
Sent: Wednesday, October 12, 2011 10:33 AM
Subject: [Dbix-class] how to avoid adding duplicate row to table containing
unique field/column
Hello,
In my test script below, i am trying to figure out how to suppress the error
mesg when the the script try to add a previously presen tentry into the table.
OR better even, to make sure that the script should not even try to update the
table, if the record is already present in table. (table may grow very big in
future).
table:
Action
Keyname
Type
Unique
Packed
Column
Cardinality
Collation
Null
Comment
Edit Drop
PRIMARYBTREE Yes No userid 3 A
Edit Drop
useridBTREE Yes No userid 3 A
mysql desc testdbusers;
+--+-+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+--+-+--+-+-+---+
| userid | char(6) | NO | PRI | NULL | |
| password | varchar(10) | YES | | NULL |
|
+--+-+--+-+-+---+
2 rows in set (0.00 sec)
mysql
mysql show index from testdbusers;
+-++--+--+-+---+-+--++--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
| Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-++--+--+-+---+-+--++--++-+
| testdbusers | 0 | PRIMARY | 1 | userid | A
| 3 | NULL | NULL | | BTREE
| |
| testdbusers | 0 | userid | 1 | userid | A
| 3 | NULL | NULL | | BTREE | |
+-++--+--+-+---+-+--++--++-+
2 rows in set (0.00 sec)
mysql
script:
#!/usr/bin/perl
use strict;
use warnings;
use lib '../testdb';
use Mysqltestdb::Schema;
my $schema;
my
@newusers;
$schema =
Mysqltestdb::Schema-connect(dbi:mysql:dbname=mysqltestdb:localhost:3306,
'root', 'mypassword');
@newusers = (['te1234', 'pass'], ['te4567', 'pass']);
$schema-populate('Testdbuser', [
[qw/userid password/],
@newusers,
]);
I get this info message out from script(error):
DBIx::Class::Schema::populate(): Duplicate entry 'te1234' for key 'PRIMARY'
for populate slice:
{
password = pass,
userid = te1234
}
___
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
___
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
--
Thanks,
Rob Kinyon___
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] Can't find source in DBIx::Class::Schema::resultset()
i referred to dbi schem like below, it is working...
use lib '../testdb';
use Mysqltestdb::Schema;
testdb folder is under which schema is dumped. it is same level as script
folder, so ../testdb
- Original Message -
From: tza...@free.fr tza...@free.fr
To: dbix-class@lists.scsys.co.uk
Cc:
Sent: Friday, October 7, 2011 8:28 AM
Subject: [Dbix-class] Can't find source in DBIx::Class::Schema::resultset()
Hi,
i'm new using DBIx::Class.
I had a postgresql 9.x database named OGS2 with only one table:
CREATE TABLE regles
(
numero integer NOT NULL,
nom character(255),
commentaires character varying(1024),
disabled boolean NOT NULL DEFAULT false,
TypeModif character(3) NOT NULL DEFAULT 'ADD'::bpchar,
DateModif date NOT NULL DEFAULT date_trunc('day'::text, now()),
isSup boolean NOT NULL DEFAULT false,
isDone boolean NOT NULL DEFAULT false,
CONSTRAINT cp_regles_pk PRIMARY KEY (numero )
)
WITH (
OIDS=FALSE,
autovacuum_enabled=true,
toast.autovacuum_enabled=true
);
ALTER TABLE regles
OWNER TO ogsadm;
I ve used successfully DBIx::Class::Schema::Loader who produce 2 files:
Schema.pm in .\lib\OGS2\Schema
Regle.pm in .\lib\OGS2\Schema\result
First question : why my table named regles (with a ending 's') gives me a pm
file Regle.pm (without ending 's') ?
schema.pm is:
package OGS2::Schema;
# Created by DBIx::Class::Schema::Loader
# DO NOT MODIFY THE FIRST PART OF THIS FILE
use strict;
use warnings;
use base 'DBIx::Class::Schema';
__PACKAGE__-load_namespaces;
# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-10-07 14:07:56
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:OrkYcQoWvuWfhpX2V2qYcA
# You can replace this text with custom code or comments, and it will be
preserved on regeneration
1;
Regle.pm is:
package OGS2::Schema::Result::Regles;
# Created by DBIx::Class::Schema::Loader
# DO NOT MODIFY THE FIRST PART OF THIS FILE
use strict;
use warnings;
use base 'DBIx::Class::Core';
__PACKAGE__-table(regles);
__PACKAGE__-add_columns(
numero,
{ data_type = integer, is_nullable = 0 },
nom,
{ data_type = char, is_nullable = 1, size = 255 },
commentaires,
{ data_type = varchar, is_nullable = 1, size = 1024 },
disabled,
{ data_type = boolean, default_value = \false, is_nullable = 0 },
TypeModif,
{
accessor = type_modif,
data_type = char,
default_value = ADD,
is_nullable = 0,
size = 3,
},
DateModif,
{
accessor = date_modif,
data_type = date,
default_value = \date_trunc('day'::text, now()),
is_nullable = 0,
},
isSup,
{
accessor = is_sup,
data_type = boolean,
default_value = \false,
is_nullable = 0,
},
isDone,
{
accessor = is_done,
data_type = boolean,
default_value = \false,
is_nullable = 0,
},
);
__PACKAGE__-set_primary_key(numero);
# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-10-07 14:38:08
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:HILqAopsCr3y9Mpkpm19eA
# You can replace this text with custom code or comments, and it will be
preserved on regeneration
1;
my program ogs-import.pl is:
use Carp;
use Data::Dumper;
use strict;
use warnings;
use DBD::Pg;
use lib::OGS2::Schema;
use lib::OGS2::Schema::Result::Regle;
print Ouverture de la base de données\n;
my
$schema=OGS2::Schema-connect('dbi:Pg:dbname=OGS2;host=localhost','ogsadm','ogsadm1!');
my $regles=$schema-resultset('Regle');
And i got the error:
DBIx::Class::Schema::resultset(): Can't find source for Regle at ogs-import.pl
line 15
any help? regards
Laurent
___
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
___
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] not able to list any records from database
follwoing code is not printing anything... can anyone help plz?
#!/usr/bin/perl
use strict;
use warnings;
use lib '../testdb';
use Mysqltestdb::Schema;
my ($schema, $user, $users_rs);
my (@newusers, @oldusers);
$schema =
Mysqltestdb::Schema-connect(dbi:mysql:dbname=mysqltestdb:localhost:3306,
'root', 'mysql5');
$users_rs = $schema-resultset('Testdbuser')-search(
{ 'userid' = 'test' }, ## WHERE
{
select = [ qw/userid password/ ], ## SELECT
as = [ qw/uid pass/ ],
}
);
while ($user = $users_rs-next ()){
print user is: . $user-uid . \n;
};
database users:
SQL query: SELECT * FROM `testdbusers` LIMIT 0, 30 ;
Rows: 6
userid
password
test12 pass
user1 pass1
user2 pass2
test45 pass
te1234 pass
te4567 pass ___
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] getting error; DBIx::Class not finding Schema created with schema loader
Thanks Uwe, it is able to locate the Schema now, but now I am getting new error.
i could not find something simple (to understand) on internet to figure out/ or
fix this. I would appreciate any help.
$ insertdb.pl
DBIx::Class::Schema::populate(): Can't find source for testdbusers at
./insertdb.pl line 18
$ ls -R testdb
testdb:
Mysqltestdb
testdb/Mysqltestdb:
Schema Schema.pm
testdb/Mysqltestdb/Schema:
Result ResultSet
testdb/Mysqltestdb/Schema/Result:
Testdbtask.pm Testdbuser.pm
testdb/Mysqltestdb/Schema/ResultSet:
modified insertdb.pl=
#!/usr/bin/perl
use strict;
use warnings;
use lib '../';
use Mysqltestdb::Schema;
my ($schema);
my (@users);
$schema = Mysqltestdb::Schema-connect(dbi:mysql:Mysqltestdb:localhost:3306,
'rootuser', 'mysqlpassword');
@users = (['test123'], ['test456']);
$schema-populate('testdbusers', [
[qw/userid/],
@users,
]);
===
From: Uwe Völker u...@uwevoelker.de
To: Rajeev Prasad rp.ne...@yahoo.com; DBIx::Class user and developer list
dbix-class@lists.scsys.co.uk
Sent: Tuesday, August 30, 2011 2:53 AM
Subject: Re: [Dbix-class] getting error; DBIx::Class not finding Schema created
with schema loader
You need to add testdb to @INC:
use lib '../testdb';
(right before use Mysqltestdb::Schema;)
2011/8/30 Rajeev Prasad rp.ne...@yahoo.com:
I tried mentioning the path where schema was created, but i am getting
error: can someone hel resolve? thx. Rajeev
/home/someplace/cgi-bin/insertdb.pl
/home/someplace/testdb/Mysqltestdb/Schema/ResultSet
insertdb.pl===
#!/usr/bin/perl
use strict;
use warnings;
use Mysqltestdb::Schema;
Mysqltestdb::Schema-dump_to_dir('../testdb/');
my $schema =
Mysqltestdb::Schema-connect(dbi:mysql:mysqltestdb:localhost:3306,
'rootuser', 'mysqlpassword');
my @users = (['test123'], ['test456']);
$schema-populate('testdbusers', [
[qw/userid/],
@testdbuserss,
]);
+++
error:
$ insertdb.pl
Can't locate Mysqltestdb/Schema.pm in @INC (@INC contains: /etc/perl
/usr/local/lib/perl/5.10.1 /usr/local/share/perl/5.10.1 /usr/lib/perl5
/usr/share/perl5 /usr/lib/perl/5.10 /usr/share/perl/5.10
/usr/local/lib/site_perl .) at ./insertdb.pl line 6.
BEGIN failed--compilation aborted at ./insertdb.pl line 6.
___
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
___
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] getting error; DBIx::Class not finding Schema created with schema loader
this time with details of schema...
Thanks Uwe, it is able to locate the Schema now, but now I am getting new error.
i could not find something simple (to understand) on internet to figure out/ or
fix this. I would appreciate any help.
$ insertdb.pl
DBIx::Class::Schema::populate(): Can't find source for testdbusers at
./insertdb.pl line 18
modified insertdb.pl=
#!/usr/bin/perl
use strict;
use warnings;
use lib '../testdb';
use Mysqltestdb::Schema;
my ($schema);
my (@users);
$schema = Mysqltestdb::Schema-connect(dbi:mysql:Mysqltestdb:localhost:3306,
'rootuser', 'mysqlpassword');
@users = (['test123'], ['test456']);
$schema-populate('testdbusers', [
[qw/userid/],
@users,
]);
===
$ ls -R testdb
testdb:
Mysqltestdb
testdb/Mysqltestdb:
Schema Schema.pm
testdb/Mysqltestdb/Schema:
Result ResultSet
testdb/Mysqltestdb/Schema/Result:
Testdbtask.pm Testdbuser.pm
testdb/Mysqltestdb/Schema/ResultSet:
$ cat Schema.pm | grep -v ^$|grep -v ^#
package Mysqltestdb::Schema;
use strict;
use warnings;
use base 'DBIx::Class::Schema';
__PACKAGE__-load_namespaces;
1;
Mysqltestdb/Schema/Result$ cat Testdbuser.pm | grep -v ^$|grep -v ^#
package Mysqltestdb::Schema::Result::Testdbuser;
use strict;
use warnings;
use base 'DBIx::Class::Core';
__PACKAGE__-table(testdbusers);
__PACKAGE__-add_columns(
userid,
{ data_type = char, is_nullable = 0, size = 6 },
password,
{ data_type = varchar, is_nullable = 1, size = 10 },
);
__PACKAGE__-set_primary_key(userid);
1;
From: Uwe Völker u...@uwevoelker.de
To: Rajeev Prasad rp.ne...@yahoo.com; DBIx::Class user and developer list
dbix-class@lists.scsys.co.uk
Sent: Tuesday, August 30, 2011 2:53 AM
Subject: Re: [Dbix-class] getting error; DBIx::Class not finding Schema created
with schema loader
You need to add testdb to @INC:
use lib '../testdb';
(right before use Mysqltestdb::Schema;)
2011/8/30 Rajeev Prasad rp.ne...@yahoo.com:
I tried mentioning the path where schema was created, but i am getting
error: can someone hel resolve? thx. Rajeev
/home/someplace/cgi-bin/insertdb.pl
/home/someplace/testdb/Mysqltestdb/Schema/ResultSet
insertdb.pl===
#!/usr/bin/perl
use strict;
use warnings;
use Mysqltestdb::Schema;
Mysqltestdb::Schema-dump_to_dir('../testdb/');
my $schema =
Mysqltestdb::Schema-connect(dbi:mysql:mysqltestdb:localhost:3306,
'rootuser', 'mysqlpassword');
my @users = (['test123'], ['test456']);
$schema-populate('testdbusers', [
[qw/userid/],
@testdbuserss,
]);
+++
error:
$ insertdb.pl
Can't locate Mysqltestdb/Schema.pm in @INC (@INC contains: /etc/perl
/usr/local/lib/perl/5.10.1 /usr/local/share/perl/5.10.1 /usr/lib/perl5
/usr/share/perl5 /usr/lib/perl/5.10 /usr/share/perl/5.10
/usr/local/lib/site_perl .) at ./insertdb.pl line 6.
BEGIN failed--compilation aborted at ./insertdb.pl line 6.
___
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
___
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___
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] getting error; DBIx::Class not finding Schema created with schema loader
I tried mentioning the path where schema was created, but i am getting error:
can someone hel resolve? thx. Rajeev
/home/someplace/cgi-bin/insertdb.pl
/home/someplace/testdb/Mysqltestdb/Schema/ResultSet
insertdb.pl===
#!/usr/bin/perl
use strict;
use warnings;
use Mysqltestdb::Schema;
Mysqltestdb::Schema-dump_to_dir('../testdb/');
my $schema =
Mysqltestdb::Schema-connect(dbi:mysql:mysqltestdb:localhost:3306, 'rootuser',
'mysqlpassword');
my @users = (['test123'], ['test456']);
$schema-populate('testdbusers', [
[qw/userid/],
@testdbuserss,
]);
+++
error:
$ insertdb.pl
Can't locate Mysqltestdb/Schema.pm in @INC (@INC contains: /etc/perl
/usr/local/lib/perl/5.10.1 /usr/local/share/perl/5.10.1
/usr/lib/perl5 /usr/share/perl5 /usr/lib/perl/5.10 /usr/share/perl/5.10
/usr/local/lib/site_perl .) at ./insertdb.pl line 6.
BEGIN failed--compilation aborted at ./insertdb.pl line 6.___
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: Subject: [Dbix-class] Re: using as normal perl module To
i found the mistake, it was incorrect database name in the dbi.. line
but i am still not sure why it did not return ANYTHING (err or otherwise) on
first run.
Also, after my first successful run: I now have all the database and table
schema in my desired location.is this normal? for every table i got:
DBIx::Class::Schema::Loader::make_schema_at(): table has no primary key at
./create-schema-script.pl line 8
From: Rajeev Prasad rp.ne...@yahoo.com
To: DBIx::Class user and developer list dbix-class@lists.scsys.co.uk
Sent: Monday, August 1, 2011 2:01 PM
Subject: Re: Subject: [Dbix-class] Re: using as normal perl module To
for using DBIx, I am not sure what i need to do and what i can do (so new i am
to this).
i have found this page, it is a wonderful resource to me (thanks to author), it
has demystified quite a few things about how to start. It is saying very
clearly that:
To use DBIC you first need to setup your Schema -
the logical model of the database tables, views and relationships.
so i tried:
i have this mysql database with few tables in it:
according to author, after i run this script i should have a directory
structure in my current (or specified) folder, for all my tables and database.
When i run the following code, I get no error, but no output either. can anyone
help plz?
#!/usr/bin/perl
use strict;
use warnings;
use DBIx::Class::Schema::Loader qw/make_schema_at/;
make_schema_at(
'mytestdb::Schema',
{debug = 0, dump_directory = '../db/',
generate_pod = 0,},
['dbi:mysql:robodb:localhost:3306', 'root', 'sqlpassowrd']',
);
i want to create that directory structure one dir above (from where my script
is), hence i want to use ../db/ (../db/ does not exist yet.)
thank you.
Rajeev
From: Al Newkirk awnstu...@gmail.com
To: dbix-class@lists.scsys.co.uk dbix-class@lists.scsys.co.uk
Sent: Saturday, July 30, 2011 9:00 AM
Subject: Subject: [Dbix-class] Re: using as normal perl module To
It seems like you want bypass defining schema, etc.
Yes, we have a way to do that... See the Schema::Loader
___
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
___
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___
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] Re: using as normal perl module
Hello Adam,
thank you.I am totally new to DB access using perl. I have looked at perl DBI
and DBIx::Class. I can get up to speed on DBI comparatively easily i guess.
(But), i want to start and stick to DBIx::Class, as i want to eventually use
Catalyst. Catalyst prefers DBIx::Class, so wanted to start from it.
when i saw CPAN for both I understood DBI methods easily and clearly compared
to DBIx.
use DBI; $dbh = DBI-connect($dsn, $user, $password, { RaiseError = 1,
AutoCommit = 0 });
where as in comparison i do not see (even in your example below) anything
similar in DBIx case. (like) use DBIx::Class; ... etc.
hence the confusion.
I am so new with using perl with DBI, I have no code yet written (sorry).
Hence I was looking for something simple. which fetches data from tables, uses
it and then stores some data into tables. as cgi script.
thank you.
Rajeev
From: Adam Sjøgren a...@koldfront.dk
To: dbix-class@lists.scsys.co.uk
Sent: Friday, July 29, 2011 3:41 PM
Subject: [Dbix-class] Re: using as normal perl module
On Thu, 28 Jul 2011 14:52:35 -0700 (PDT), Rajeev wrote:
all the examples i see are of using the module in a webapp (built with
catalyst). I am only trying to use it in my cgi perl programe, but
finding very less documentation. can someone please refer a URL or
small script which stores and retrieves data from a table using this
module plz.
I recently used DBIx::Class in a very simple command-line script that
reads files from the filesystem and stuffs them into the database.
I looked roughly like this:
#!/usr/bin/perl
use strict;
use warnings;
use utf8;
use File::Slurp qw(slurp);
use MessageID::DB;
my $schema=MessageID::DB-connect('dbi:Pg:dbname=message-id', 'USER',
'PASSWD', { AutoCommit=1, pg_enable_utf8=1 });
my $transaction=sub {
while(my $filename=) {
chomp $filename;
my $article=slurp($filename);
my $message_id=extract_message_id($article, $filename);
$schema-resultset('Article')-create({ message_id=$message_id,
article=$article });
}
};
$schema-txn_do($transaction);
It is trivial, but you asked for something that doesn't use Catalyst and
is small (I left out the helper-sub and the error-checking) :-)
Maybe if you show what you have tried and describe what you wanted, it
is easier to get help.
Best regards,
Adam
--
Good car to drive after a war Adam Sjøgren
a...@koldfront.dk
___
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___
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] using as normal perl module
Hello, all the examples i see are of using the module in a webapp (built with catalyst). I am only trying to use it in my cgi perl programe, but finding very less documentation. can someone please refer a URL or small script which stores and retrieves data from a table using this module plz. thx. Rajeev ___ 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
