Getting MySQL table info in perl script

...ie the column name, definitions (varchar(100) etc). The most
obvious way is to parse the output from describe table_name. Is there
an easier way? My goal is to create a generic INSERT sub, which takes
as args table_name and a hash reference, eg:

$table_name = "TheTable";
$hash(Int1) = 99;
$hash(Float2) = 1.54;
$hash(String7) = "Goodbye cruel world";

...where Float2, Int1, String7 are some but not all cols in mysql table
TheTable. The sub is invoked as follows:

DoInsert($table_name, \%hash);

DoInsert will generate and execute the INSERT statement, NULLing out
fields not in %hash, single quoting string fields, etc., maybe even
doing data tyep compatibility tests. I would be happy to learn that
some one has beaten me to it, and a procedure like DoInsert already
exists.

Re: Getting MySQL table info in perl script

On Sat, 03 Nov 2007 09:26:17 -0700, Occidental wrote:

> ..ie the column name, definitions (varchar(100) etc). The most obvious
> way is to parse the output from describe table_name. Is there an easier
> way? My goal is to create a generic INSERT sub, which takes as args
> table_name and a hash reference, eg:
>
> $table_name = "TheTable";
> $hash(Int1) = 99;
> $hash(Float2) = 1.54;
> $hash(String7) = "Goodbye cruel world";
>
> ..where Float2, Int1, String7 are some but not all cols in mysql table
> TheTable. The sub is invoked as follows:
>
> DoInsert($table_name, \%hash);
>
> DoInsert will generate and execute the INSERT statement, NULLing out
> fields not in %hash, single quoting string fields, etc., maybe even
> doing data tyep compatibility tests. I would be happy to learn that some
> one has beaten me to it, and a procedure like DoInsert already exists.

A module like that should already be available.

Checkout these namespaces at CPAN:
MySQL::
DBIx::
DBD::
http://search.cpan.org/search?query=MySQL%3A%3A&mode=module

Rather than use DESCRIBE, you might want
Show columns from TableName;

----------------- example -------------
mysql> use rt3
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------------+
| Tables_in_rt3 |
+-------------------------+
| ACL |
| Attachments |
| Attributes |
| CachedGroupMembers |
| CustomFieldValues |
| CustomFields |
| GroupMembers |
| Groups |
| Links |
| ObjectCustomFieldValues |
| ObjectCustomFields |
| Principals |
| Queues |
| ScripActions |
| ScripConditions |
| Scrips |
| Templates |
| Tickets |
| Transactions |
| Users |
| sessions |
+-------------------------+
21 rows in set (0.00 sec)

mysql> show columns from Queues;
+-------------------+--------------+------+-----+---------
| Field | Type | Null | Key | Default | Extra
|-------------------+--------------+------+-----+---------
| id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(200) | NO | UNI | NULL
| |
| Description | varchar(255) | YES | | NULL
| |
| CorrespondAddress | varchar(120) | YES | | NULL
| |
| CommentAddress | varchar(120) | YES | | NULL
| |
| InitialPriority | int(11) | NO | | 0
| |
| FinalPriority | int(11) | NO | | 0
| |
| DefaultDueIn | int(11) | NO | | 0
| |
| Creator | int(11) | NO | | 0
| |
| Created | datetime | YES | | NULL
| |
| LastUpdatedBy | int(11) | NO | | 0
| |
| LastUpdated | datetime | YES | | NULL
| |
| Disabled | smallint(6) | NO | MUL | 0
| |
+-------------------+--------------+------+-----+---------
13 rows in set (0.00 sec)

--
Mark