http://www.perlmonks.org?node_id=376196

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

Hi,

Could anyone tell me if it is possible to insert a perl array (or hash) into a mysql table?

Here is the script I tried to test it with but it fails on the insert...

#!/usr/bin/perl use DBI; use strict; use warnings; my $id = 1; my @old_array = ("blah0", "blah1", "blah2"); print "Values in original array:\n"; foreach (@old_array) { if ($_) { print "\t- ", $_, "\n" } } # end-foreach my $dbh = &DB_OPEN('test','localhost','3306','user','password'); ################################# # Array Table structure # ################################# # id # array # ################################# # 1 # @array1 # # 2 # @array2 # # etc.... # ################################# my $ins_array = $dbh->prepare("insert into array_table (id,array) valu +es (?,?)"); $ins_array->execute($id,@old_array); $ins_array->finish; my $sel_array = $dbh->prepare("select array from array_table where id= +?"); $sel_array->execute($id); my (@new_array) = $sel_array->fetchrow; $sel_array->finish; print "Values in array from DB:\n"; if (@new_array) { foreach (@new_array) { if ($_) { print "\t- ", $_, "\n" } # end-if } # end-foreach } # end-if sub DB_OPEN { my ($db_name,$host_name,$port,$db_user,$db_pass,) = @_; my $database = "DBI:mysql:$db_name:$host_name:$port"; $dbh = DBI->connect($database,$db_user,$db_pass); } # end-sub exit;

Cheers,
Reagen

Replies are listed 'Best First'.
Re: Perl array into MySQL Database
by CountZero (Bishop) on Jul 21, 2004 at 11:39 UTC
    Either you somehow try to mimic your array structure in the database (that is not easy, esp. if you use deep structures with references to hashes, etc ...) or you serialize your data. Storable and its freeze and thawe functions come to mind in this respect. The variable with the frozen image of your array can then be saved in an appropriate database field.

    CountZero

    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

Re: Perl array into MySQL Database
by tinita (Parson) on Jul 21, 2004 at 11:26 UTC
    Could anyone tell me if it is possible to insert a perl array (or hash) into a mysql table?
    no it isn't. what kind of mysql column type should it be?
    one possibility, though, is to join the array together to a string and save that string. when reading it from the database you have to split it again to an array.
      column type should be varchar.

      Yeah, thats what i currently do (join & split) but was curious if there was a "cleaner" way.

      cheers.
        As you allready know, you have to serialise your array. You do it with split and join and i think there are cleaner ways.
        Split uses regex, which isn't pretty fast.

        You could use Storable which is pretty fast, but produces binary file or DataDumper which produces text files.

        This solutions are also more general then using split & join, they also work for hashes, references (at least Storable).

        ciao
        knoebi

Re: Perl array into MySQL Database
by hmerrill (Friar) on Jul 21, 2004 at 12:17 UTC
    I think your current method of joining the array elements into one string and storing the string in a varchar, and then spliting the string to get the elements again is fine - that's how I would do it. I don't know of a better way.

    One technical nit-pick comment on your code - the sub "DB_OPEN" doesn't explicitly *return* a value. Instead it depends on Perl's concept of returning the result of the last statement executed in the subroutine. Just my preference, but to me the code is cleaner if you explicitly return what is expected from the subroutine:

    sub DB_OPEN { my ($db_name,$host_name,$port,$db_user,$db_pass,) = @_; my $database = "DBI:mysql:$db_name:$host_name:$port"; $dbh = DBI->connect($database,$db_user,$db_pass); return $dbh; # <----------------- :-) } # end-sub
    HTH.
Re: Perl array into MySQL Database
by perldeveloper (Scribe) on Jul 21, 2004 at 14:18 UTC
    If it's an array of strings and the database doesn't support array-typed fields, you could try to create a new table ELEMENTS_TABLE(ID, ELEMENT) which lists all values associated with an ID in the main database, where ID is a FK to the main table. You can retrieve all values associated with the ID by running SELECT ELEMENT FROM ELEMENTS_TABLE WHERE ID=..., the only problem being that the ordering in the original array is not necessarily preserved. To enforce ordering, you'd need to add an extra column (say INDEX) and add ORDER BY INDEX in your select statement. This actually is the only way to do it if the strings can contain basically anything, making it improbable to split by a certain character or set of characters.

    Then to retrieve all values, you would run something like:
    my $sel_array = $dbh->prepare("SELECT ELEMENT FROM ELEMENTS_TABLE WHER +E ID=?"); $sel_array->execute($id); my (@new_array) = map { $_->[0] } @{$sel_array->fetchall_arrayref};
      It's not the only way. For example, you could base64 encode each string, then use something that's not base64 as a field seperator, URLEncode each string and use an ampersand, or implement some method for escaping the seperator. Of course, you'd have to decode this after reading the data back in.
        Of course, and many other ways you can ensure safe splitting or conversion of any kind from one field to an array of text. Hoever, it has at least three disadvantages:
        • Takes more data space
        • Takes time to compute the conversion
        • Doesn't take advantage of the underlying relational database
Re: Perl array into MySQL Database
by dragonchild (Archbishop) on Jul 21, 2004 at 21:54 UTC
    If you want to store an array in a database column, you aren't thinking about databases correctly.

    Basically, you want to pull a bunch of data out of a table based on some set of identifying characteristics. So, you either have

    • a bunch of different pieces of data, in which case they should be columns in their own right (name, address, city, etc)
    • a bunch of data that's the same thing (favorite colors, clothing sizes, etc). Those belong in a child table. So, you'd have an ID for the main row. The second table would have multiple rows with that ID, each having a different value.

    I don't have time now, but I'll be glad to provide an example of each kind later. It would also be helpful if you provided exactly what type of data you're trying to work with. Often, a little analysis goes a long way.

    ------
    We are the carpenters and bricklayers of the Information Age.

    Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

    I shouldn't have to say this, but any code, unless otherwise stated, is untested