Topic: insrting the data into two associated tables of mysqldb using rubycode

Hi All,
We have an rails application to upload the streams.But now am trying to upload the data into mysql database with out interacting the rails application using a simple ruby script.

I have two tables called containerformats and transport streams.Transport streams table contains id of containerformats table.
below are the fields of the two tables.

mysql> show fields from containerformats;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| streamName   | varchar(255) | YES  |     | NULL    |                |
| location     | varchar(255) | YES  |     | NULL    |                |
| containerFmt | varchar(255) | YES  |     | NULL    |                |
| created_at   | datetime     | YES  |     | NULL    |                |
| updated_at   | datetime     | YES  |     | NULL    |                |
| source       | varchar(255) | YES  |     | NULL    |                |
| error        | varchar(255) | YES  |     | NULL    |                |
| license      | varchar(255) | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
9 rows in set (0.02 sec)

mysql> show fields from transportstreams;
+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| id                 | int(11)      | NO   | PRI | NULL    | auto_increment |
| numPrograms        | int(11)      | YES  |     | NULL    |                |
| encryptionstd      | varchar(255) | YES  |     | NULL    |                |
| containerformat_id | int(11)      | YES  |     | NULL    |                |
| created_at         | datetime     | YES  |     | NULL    |                |
| updated_at         | datetime     | YES  |     | NULL    |                |
+--------------------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

No

mysql> insert into containerformats(location,streamName,containerFmt),transportstreams(numPrograms,encryptionstd,containerformat_id) values ('\\192.168.247.116\share\log.txt','log.txt','2',1,'-',
containerformats.id);

ERROR 1064 (42000): 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 'transportstreams(numPrograms,encryptions
td,containerformat_id) values ('\\192.16' at line 1

As i dont have  much knowledge on sql i could not able to solve the problem.
Can anyone suggest how to insert the data into more than one table in a single query.And all table has association between them.

thank you
Srikanth

Re: insrting the data into two associated tables of mysqldb using rubycode

You can only insert rows into one table at a time. So you should be able to get it working with

insert into containerformats (location,streamName,containerFmt)
values('\\192.168.247.116\share\log.txt','log.txt','2');

insert into transportstreams (numPrograms,encryptionstd,containerformat_id) values (1, '-', LAST_INSERT_ID());

Note the use of the LAST_INSERT_ID() function to get the foriegn key for the just-inserted row on containerformats. Obviously this only works if you interleave the INSERT statements. If you have multiple rows on transportstreams for each row on containerformats, you will have to save it somewhere with a SELECT LAST_INSERT_ID() as it will get incremented on every successful insert.

Re: insrting the data into two associated tables of mysqldb using rubycode

thank you very much.
I will try and come back to you.