perl script to create xml from mysql query - out of memory

Question

I need to generate an XML file from database records, and I get the error "out of memory". Here's the script I am using, it's found on Google, but it's not suitable for me, and it's also killing the server's allocated memory. It's a start though.

#!/usr/bin/perl

use warnings;
use strict;
use XML::Simple;
use DBI;

my $dbh = DBI->connect('DBI:mysql:db_name;host=host_address','db_user','db_pass')
  or die DBI->errstr;

# Get an array of hashes
my $recs = $dbh->selectall_arrayref('SELECT * FROM my_table',{ Columns => {} });

# Convert to XML where each hash element becomes an XML element
my $xml = XMLout( {record => $recs}, NoAttr => 1 );

print $xml;

$dbh->disconnect;

This script only prints the records, because I tested with a where clause for a single row id.

First of all, I couldn't manage to make it to save the output to a file.xml.
Second, I need somehow to split the "job" in multiple jobs and then put together the XML file all in one piece.

I have no idea how to achieve both.

Constraint: No access to change server settings.

hi, it does not seems to be bash, but a perl scripts

ajreal
– ajreal

2013-03-30 00:32:04 +00:00
Commented Mar 30, 2013 at 0:32 — ajreal
– ajreal, Commented Mar 30, 2013 at 0:32

Neil Slater · Accepted Answer · 2013-03-30 07:51:04Z

4

These are problem lines:

my $recs = $dbh->selectall_arrayref('SELECT * FROM my_table',{ Columns => {} });

This reads the whole table into memory, representing every single row as an array of values.

my $xml = XMLout( {record => $recs}, NoAttr => 1 );

This is probably even larger structure, it is a the whole XML string in one go.

The lowest memory-use solution needs to involve loading the table one item at a time, and printing that item out immediately. In DBI, it is possible to make a query so that you fetch one row at a time in a loop.

You will need to play with this before the result looks like your intended output (I haven't tried to match your XML::Simple output - I'm leaving that to you:

print "<records>\n";

my $sth = $dbh->prepare('SELECT * FROM my_table');
$sth->execute;

while ( my $row = $sth->fetchrow_arrayref ) {
  # Convert db row to XML row
  print XMLout( {row => $row}, NoAttr => 1 ),"\n";
}

print "</records>\n";

Perl can use e.g. open( FILEHANDLE, mode, filename ) to start access to a file and print FILEHANDLE $string to print to it, or you could just call your script and pipe it to a file e.g. perl myscript.pl > table.xml

answered Mar 30, 2013 at 7:51

Neil Slater

27.4k6 gold badges80 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

john Over a year ago

thank you very much for your answer! it woks but it pull out all the details from db... i need to get only a few, (id, name, user, email, phone, date). This will do the job, "SELECT id, name, user, email, phone, date FROM my_table" but now it comes to the xml format with must be like this <user_ids> <user_id>2323</user_id><user_first_name>john</user_first_name> <user_id>123</user_id><user_email>[email protected]</user_email> <user_phone>123-123-1234</user_hone><user_date>2013-01-01</user_date></user_ids> And i didn't manage to output the rows to an xml file...

Neil Slater Over a year ago

If you want specific XML elements, you will need to name them in Perl so that XML::Simple knows what to do with them. You can do this by creating a Perl hash from your array. If the first db columns are id and name then you could start with my $hashed_row = { user_id => $row->[0], user_first_name => $row->[1] } - I think from that you can work out how to make the rest. You may still have more to do, but I think that should get you close

Jizzle · Accepted Answer · 2013-03-30 00:33:19Z

0

It's the select * with no contraints that will be killing your memory. Add some constraint to your query ie date or id and use a loop to execute the query and do your output in chunks. That way you won't need to load the whole table in mem before your get started on the output.

answered Mar 30, 2013 at 0:33

Jizzle

2141 silver badge9 bronze badges

Collectives™ on Stack Overflow

perl script to create xml from mysql query - out of memory

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related