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

In reply to dbic insert into multiple related tables by ag4ve

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.