Mailing List Archive: Catalyst: Users

Trouble inserting data after migrate database from SQLite to My SQL

Index | Next | Previous | View Threaded

jamoros at etsetb

Nov 10, 2009, 5:53 AM

Post #1 of 4 (945 views)
Permalink

Trouble inserting data after migrate database from SQLite to My SQL

Hi all,

I'm developing a little web application (a remote lab that works with
Catalyst+LabView). Until now I was using SQLite, but due to new
requirements I've had to migrate the database.

I've created a new model:
perl script/ilabrs_create.pl model DB DBIC::Schema ilabrs::Schema
create=static components=TimeStamp,EncodedColumn dbi:mysql:ilabrs

and then, after restarting the server all seemed to work. Actually,
list, edit and erase from the database is working fine. But when I want
to add data, it doesn't work.

The problem appears when an id it's not provided. If I'm not wrong,
"my $exp = $c->model('DB::Activitats')-> find_or_new({id=> $id});"
should solve that. But: "$c->log->debug("Experiment ID:".$exp->id);",
prints nothing.

I'm absolutely clueless. I've tried to split de edit function in two:
-one to actually edit (my $exp = $c->model('DB::Activitats')->
find({id=> $id});) which still works properly.

-and a second one to add (my $exp = $c->model('DB::Activitats')->
new({});) which doesn't because $exp->id is empty and
$exp->update_or_insert;
cause an exception:

DBI Exception: DBD::mysql::st execute failed: Column 'id' cannot be
null...

Any suggestion, idea, clue... will be welcome.

Thanks,

Jordi

_______________________________________________
List: Catalyst [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/

steve at matsch

Nov 10, 2009, 6:05 AM

Post #2 of 4 (884 views)
Permalink

Re: Trouble inserting data after migrate database from SQLite to My SQL [In reply to]

Jordi AmorÃ³s wrote:
> Hi all,
>
> I'm developing a little web application (a remote lab that works with
> Catalyst+LabView). Until now I was using SQLite, but due to new
> requirements I've had to migrate the database.
>
> I've created a new model:
> perl script/ilabrs_create.pl model DB DBIC::Schema ilabrs::Schema
> create=static components=TimeStamp,EncodedColumn dbi:mysql:ilabrs
>
> and then, after restarting the server all seemed to work. Actually,
> list, edit and erase from the database is working fine. But when I want
> to add data, it doesn't work.
>
> The problem appears when an id it's not provided. If I'm not wrong,
> "my $exp = $c->model('DB::Activitats')-> find_or_new({id=> $id});"
> should solve that. But: "$c->log->debug("Experiment ID:".$exp->id);",
> prints nothing.
>
> I'm absolutely clueless. I've tried to split de edit function in two:
> -one to actually edit (my $exp = $c->model('DB::Activitats')->
> find({id=> $id});) which still works properly.
>
> -and a second one to add (my $exp = $c->model('DB::Activitats')->
> new({});) which doesn't because $exp->id is empty and
> $exp->update_or_insert;
> cause an exception:
>
> DBI Exception: DBD::mysql::st execute failed: Column 'id' cannot be
> null...
>
> Any suggestion, idea, clue... will be welcome.
>
> Thanks,
>
> Jordi
>
>
>
>
>
>
>
>
> _______________________________________________
> List: Catalyst [at] lists
> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
> Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
> Dev site: http://dev.catalyst.perl.org/
>
>
>
Is your 'id' set to auto-increment??? What data type is it? I usually
set the 'id' to auto-increment, and set it as an integer. I would also
recommend using DBIC_TRACE=1 from the command line if possible to get a
better description of the actual problem.

Steve

_______________________________________________
List: Catalyst [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/

jshirley at gmail

Nov 10, 2009, 7:20 AM

Post #3 of 4 (875 views)
Permalink

Re: Trouble inserting data after migrate database from SQLite to My SQL [In reply to]

On Tue, Nov 10, 2009 at 5:53 AM, Jordi Amorós <jamoros [at] etsetb>wrote:

> Hi all,
>
> I'm developing a little web application (a remote lab that works with
> Catalyst+LabView). Until now I was using SQLite, but due to new
> requirements I've had to migrate the database.
>
> I've created a new model:
> perl script/ilabrs_create.pl model DB DBIC::Schema ilabrs::Schema
> create=static components=TimeStamp,EncodedColumn dbi:mysql:ilabrs
>
> and then, after restarting the server all seemed to work. Actually,
> list, edit and erase from the database is working fine. But when I want
> to add data, it doesn't work.
>
> The problem appears when an id it's not provided. If I'm not wrong,
> "my $exp = $c->model('DB::Activitats')-> find_or_new({id=> $id});"
> should solve that. But: "$c->log->debug("Experiment ID:".$exp->id);",
> prints nothing.
>
>
I'm absolutely clueless. I've tried to split de edit function in two:
> -one to actually edit (my $exp = $c->model('DB::Activitats')->
> find({id=> $id});) which still works properly.
>
> -and a second one to add (my $exp = $c->model('DB::Activitats')->
> new({});) which doesn't because $exp->id is empty and
> $exp->update_or_insert;
> cause an exception:
>
> DBI Exception: DBD::mysql::st execute failed: Column 'id' cannot be
> null...
>
> Any suggestion, idea, clue... will be welcome.
>
> Thanks,
>
>
This is a DBIC question, as such the answer is clearly found in the DBIC
manual:
http://search.cpan.org/~ribasushi/DBIx-Class-0.08112/lib/DBIx/Class/ResultSet.pm#find_or_new

To summarize, ->new or ->find_or_new will not create a record in the
database (and as such, no id is available). If you want to create a record,
triggering the auto_increment, then you must use create or a subsequent
->insert after a ->new call.

Also of note, DBIx::Class has its own mailing list that you should consider
subscribing to.

Thanks,
-Jay

jamoros at etsetb

Nov 10, 2009, 7:45 AM

Post #4 of 4 (880 views)
Permalink

Re: Trouble inserting data after migrate database from SQLite to My SQL [In reply to]

>
>
>
> This is a DBIC question, as such the answer is clearly found in the
> DBIC manual:
> http://search.cpan.org/~ribasushi/DBIx-Class-0.08112/lib/DBIx/Class/ResultSet.pm#find_or_new
>
> To summarize, ->new or ->find_or_new will not create a record in the
> database (and as such, no id is available). If you want to create a
> record, triggering the auto_increment, then you must use create or a
> subsequent ->insert after a ->new call.
>
>
> Also of note, DBIx::Class has its own mailing list that you should
> consider subscribing to.
>
> Thanks,
> -Jay
> _______________________________________________
> List: Catalyst [at] lists
> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
> Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
> Dev site: http://dev.catalyst.perl.org/

First of all, my apologies I've changed the subject of my previous
message and due to that, now it's not placed where it have to be.

With your permission (I wish), I copy & paste my answer to Steve:

"> Is your 'id' set to auto-increment???
Yes, it is.

> What data type is it? I usually
> set the 'id' to auto-increment, and set it as an integer.
I also do it.

> I would also
> recommend using DBIC_TRACE=1 from the command line if possible to get
a
> better description of the actual problem.

> Steve
>

Thank you, very much. DBIC_TRACE=1 have provided me the following error:
"No _dbh_last_insert_id() method found in DBIx::Class::Storage::DBI.
Since the method of obtaining the autoincrement id of the last insert
operation varies greatly between different databases, this method must
be individually implemented for every storage class."

I've made a quick search, and I've found:
http://rt.cpan.org/Public/Bug/Display.html?id=40265#txn-566706

I've upgraded my version of DBIx::Class and it's all working again.

Cheers

Jordi"

You're right Jay, it was a DBIx::Class issue. Thank you, for answering.

Jordi

_______________________________________________
List: Catalyst [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads