Perl DBI – Some usefull commands

Return a reference to column names

$name = $sth->{NAME};

Number of affected rows


New auto-incremented id

$latestId = $dbh->{‘mysql_insertid’}

Uploading large data. ‘INSERT INTO’ command takes long amount of time. Use ‘LOAD DATA’ command to upload bulk data from a text file.

  • Be careful. If the file to be uploaded resides on the server, it still needs to be accessible by the mysql server. E.g. you can place them under /tmp
  • You can specify NULL characters by ‘\N’, e.g. “9,\N,276,John…”

Perl: Some usefull tips

Some handy Perl commands that is not used as much.

Use ‘-‘ to refer to STDOUT & STDIN.

open(INP, ‘-‘) —> STDIN
open(OUT, ‘>-) —> STDOUT
$fh = IO::File->new(‘-‘,’w’) —> STDOUT

Following the filehandle with ‘-|’ opens the output of a shell command as a file.

open($fh, ‘-|’, ‘ls -l’);
open($fh, ‘-|’, ‘cat tmp.txt | sort’);

Similarly IO::Pipe can be used to get the same affect:

$fh = new IO::Pipe;
$fh->reader(qw(ls -l));

Then you can print the output

while (<$fh>) { print; }

Open a zipped file

open(INP, ‘-|’, ‘gunzip < tmp.txt.gz’);

Print the output of a subroutine inside a string

print “3+4 is @{ [addTwoNumbers(3,4)] }\n”;

Use IO::File to store filehandles into a hash array.

$fh{‘infile’} = IO:File->new(“tmp.txt”, “r”);
while (my $line = $fh{‘infile’}->getline) {
print $line;