ag4ve has asked for the wisdom of the Perl Monks concerning the following question:

I've got a database with a table that links all of my data together (has no real information) and some one to many relationships and some one to one relationships. my issue is inserting into the table.

my sample code is as follows:

#!/usr/bin/perl

use strict;
use warnings;

use lib './lib';
use TEST::Schema;

my $schema = TEST::Schema->connect( 'dbi:mysql:database=test;host=192.
+168.15.222',
                                       'user',
                                       'pass');

my $main = $schema->resultset( 'Main' );

$schema->txn_do( sub {
   $main->find_or_create( {
      data =>     {
         some_dat => 'blah',
         more_dat => 'more blah'
      }, info1 => {
         info1_1 =>  'unique blah 1',
         info1_2 =>  'unique blah 2'
      }, info2 => {
         info2_1 =>  'more ublah 1',
         info2_2 =>  'more ublah 2'
      }
   } );
} );

[download]

this code produces the following error:

swilson@swilson-mbp-vdebian:~/perl/test$ ./test.pl 
DBIx::Class::ResultSet::find_or_create(): DBI Exception: DBD::mysql::s
+t execute failed: You have an error in your SQL syntax; check the man
+ual that corresponds to your MySQL server version for the right synta
+x to use near 'MORE_DAT 'more blah' AND me.data SOME_DAT 'blah' ) AND
+ me.pk IS NULL ) )' at line 1 [for Statement "SELECT me.pk, me.data_f
+k FROM main me WHERE ( ( ( me.data MORE_DAT ? AND me.data SOME_DAT ? 
+) AND me.pk IS NULL ) )" with ParamValues: 0='more blah', 1='blah'] a
+t ./test.pl line 16

[download]

i think it would be easier just to show my database schema in mysql sense i am using what dbicdump created with: dbicdump -o dump_directory=./lib -o inflect_plural -o debug=1 TEST::Schema 'dbi:mysql:test;host=192.168.15.222' user pass

the sql is as follows:


mysql> show create table main;
+-------+-------------------------------------------------------------
+---------------------------------------------------------------------
+---------------------------------------------------------------------
+-
----------------------------------------------------------------------
+---------------------------------------------------------------------
+---------------------------------------------------------------------
+-
--------------------------+
| Table | Create Table                                                
+                                                                     
+                                                                     
+ 
                                                                      
+                                                                     
+                                                                     
+ 
                          |
+-------+-------------------------------------------------------------
+---------------------------------------------------------------------
+---------------------------------------------------------------------
+-
----------------------------------------------------------------------
+---------------------------------------------------------------------
+---------------------------------------------------------------------
+-
--------------------------+
| main  | CREATE TABLE `main` (  `pk` int(10) unsigned NOT NULL AUTO_I
+NCREMENT,  `data_fk` int(10) unsigned NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `main_data` (`data_fk`),
  CONSTRAINT `main_info2` FOREIGN KEY (`pk`) REFERENCES `info2` (`info
+2_pk`),
  CONSTRAINT `main_data` FOREIGN KEY (`data_fk`) REFERENCES `data` (`d
+_pk`),  CONSTRAINT `main_info1` FOREIGN KEY (`pk`) REFERENCES `info1`
+ (`info1_pk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |+-------+---------------------
+---------------------------------------------------------------------
+---------------------------------------------------------------------
+-----------------------------------------
----------------------------------------------------------------------
+---------------------------------------------------------------------
+---------------------------------------------------------------------
+---------------------------+
1 row in set (0.00 sec)

mysql> show create table data;
+-------+-------------------------------------------------------------
+---------------------------------------------------------------------
+---------------------------------------------------------------------
+-
--------------+
| Table | Create Table                                                
+                                                                     
+                                                                     
+ 
              |+-------+----------------------------------------------
+---------------------------------------------------------------------
+---------------------------------------------------------------------
+----------------
--------------+
| data  | CREATE TABLE `data` (
  `d_pk` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `some_dat` varchar(20) DEFAULT NULL,  `more_dat` varchar(20) DEFAULT
+ NULL,
  PRIMARY KEY (`d_pk`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------
+---------------------------------------------------------------------
+---------------------------------------------------------------------
+---------------+
1 row in set (0.00 sec)

[download]

i should be able to have multiple main fields for a data field


mysql> show create table info1;
+-------+-------------------------------------------------------------
+---------------------------------------------------------------------
+---------------------------------------------------------------------
+-
----------------------------------------------------------------------
+-------------+
| Table | Create Table                                                
+                                                                     
+                                                                     
+ 
                                                                      
+             |
+-------+-------------------------------------------------------------
+---------------------------------------------------------------------
+---------------------------------------------------------------------
+---------------------------------------------------------------------
+---------------+
| info1 | CREATE TABLE `info1` (
  `info1_pk` int(10) unsigned NOT NULL,
  `info1_1` varchar(20) DEFAULT NULL,
  `info1_2` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`info1_pk`),
  CONSTRAINT `info1_main` FOREIGN KEY (`info1_pk`) REFERENCES `main` (
+`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------
+---------------------------------------------------------------------
+---------------------------------------------------------------------
+---------------------------------------------------------------------
+---------------+
1 row in set (0.00 sec)

mysql> show create table info2;
+-------+-------------------------------------------------------------
+---------------------------------------------------------------------
+---------------------------------------------------------------------
+---------------------------------------------------------------------
+---------------+
| Table | Create Table                                                
+                                                                     
+                                                                     
+                                                                     
+               |
+-------+-------------------------------------------------------------
+---------------------------------------------------------------------
+---------------------------------------------------------------------
+---------------------------------------------------------------------
+---------------+
| info2 | CREATE TABLE `info2` (
  `info2_pk` int(10) unsigned NOT NULL,
  `info2_1` varchar(20) DEFAULT NULL,
  `info2_2` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`info2_pk`),
  CONSTRAINT `info2_main` FOREIGN KEY (`info2_pk`) REFERENCES `main` (
+`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------
+---------------------------------------------------------------------
+---------------------------------------------------------------------
+---------------------------------------------------------------------
+---------------+
1 row in set (0.00 sec)

[download]

there should be a one to one relationship between the info tables and main

Replies are listed 'Best First'.
Re: dbic insert into multiple related tables
by Corion (Patriarch) on May 05, 2011 at 06:59 UTC

    Have you looked at the SQL that gets generated and checked that the generated SQL makes sense? I'm no MySQL expert, but I'd check that MySQL supports updateable joins and/or updateable views before trying to insert into a joined table.

[reply]

Back to Seekers of Perl Wisdom