[KLUG Members] Inserting data into MySQL]

[Jamie McCarthy]

This was a fun little project... Justin, I stole your code and
rewrote it to use a regex to do the parsing, and the DBI module to
write the data into a table.  I used MySQL for my example but it
would work with any database.

The code is here:

    http://lvalue.com/card-read.txt

The tricky part is:

    DBI->connect(
        "DBI:mysql:database=test_db;host=localhost",
        "jamie",
        "",
        {} )

This works on my machine because I've created a database "test_db",
and in MySQL, by default, any db named beginning with "test_" can be
read and written by any user.  So I'm connecting as user "jamie"
with password "" and it works.  You'll want to edit those for your
DB, see "perldoc DBI" for details.  ("perldoc DBI" is where I stole
the "insert_hash" function from, BTW :)


Here's a sample run on my machine "ralph":

ralph:~# mysql mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 351177 to server version: 3.23.49-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop database test_db;
Query OK, 3 rows affected (0.00 sec)

mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye
ralph:~# exit
logout
jamie@ralph:~$ mysql test_db
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 351179 to server version: 3.23.49-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table test_table ( cardname varchar(64) not null primary key, cardcolor varchar(2), cardtext text, cardnum varchar(12) ) type=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> describe test_table;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| cardname  | varchar(64) |      | PRI |         |       |
| cardcolor | char(2)     | YES  |     | NULL    |       |
| cardtext  | text        | YES  |     | NULL    |       |
| cardnum   | varchar(12) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> exit
Bye
jamie@ralph:~$ /web/lvalue.com/card-read.pl 
reading from __DATA__
jamie@ralph:~$ mysqldump test_db
-- MySQL dump 8.21
--
-- Host: localhost    Database: test_db
---------------------------------------------------------
-- Server version       3.23.49-log

--
-- Table structure for table 'test_table'
--

CREATE TABLE test_table (
  cardname varchar(64) NOT NULL default '',
  cardcolor char(2) default NULL,
  cardtext text,
  cardnum varchar(12) default NULL,
  PRIMARY KEY  (cardname)
) TYPE=MyISAM;

--
-- Dumping data for table 'test_table'
--


INSERT INTO test_table VALUES ('Ancestor\'s Chosen','W','\"First strike When Ancestor\'s Chosen comes into play, you gain 1 life for each card in your graveyard.\"','1/143');
INSERT INTO test_table VALUES ('Aven Warcraft','W','Creatures you control get +0/+2 until end of turn. Threshold - Creatures you control also gain protection from the color of your choice until end of turn. (You have threshold if seven or more cards are in your graveyard.)','2/143');

jamie@ralph:~$