Perl DBI Sample with MySQL DDL

Question

I've been experimenting with Perl and MySql and wrote this code for connecting and writing to a database:

#   MySQL DDL to create database used by code 
#
#   CREATE DATABASE sampledb;
#
#   USE sampledb;
#   
#   CREATE TABLE `dbtable` (
#     `id`  int(11) NOT NULL AUTO_INCREMENT,
#     `demo` longtext,
#     PRIMARY KEY  (`id`)
#   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# PERL MODULES
use strict;
use warnings;
use DBI; #http://dbi.perl.org

# CONFIG VARIABLES
my $platform = "mysql";
my $database = "sampledb";
my $host = "localhost";
my $port = "3306";
my $username = "root";
my $password = "password";

# DATA SOURCE NAME
my $dsn = "dbi:$platform:$database:$host:$port";

# PERL DBI CONNECT
my $connect = DBI->connect($dsn, $username, $password);

# VARS for Examples
my $query;
my $query_handle;
my $id;
my $demo;

# Example 1 using prepare() and execute() INSERT

    # SAMPLE VARIABLE AND VALUES TO PASS INTO SQL STATEMENT
    $id = 1;
    $demo = "test";

    # INSERT
    $query = "INSERT INTO dbtable (id, demo) VALUES ('$id', '$demo')";
    $query_handle = $connect->prepare($query);
    $query_handle->execute();
    undef $query;

# Example 2 using do() UPDATE   

    # SAMPLE VARIABLE AND VALUES TO PASS INTO SQL STATEMENT
    $id = 1;
    $demo = "test 2";

    # UPDATE
    $query = "UPDATE dbtable SET demo = '$demo' WHERE id = $id";
    $query_handle = $connect->do($query);
    undef $query;

Is this the correct/idiomatic way to access a database in Perl? Are there any other improvements I could make?

Would you describe what exactly your code does? I see a DDL and some insert and update statements. It's all right to ask for a general review, but we need more context. — Michael K
– Michael K, Commented Mar 28, 2011 at 15:49
@Michael K: It's Perl for a database connector and sample INSERT and UPDATE, and very common code. Do you know Perl? Is there a template for information expected? Thanks! — blunders
– blunders, Commented Mar 29, 2011 at 12:56
@Olli: Yes, it's working code -- and I wanted feedback; question is closed, and not posting again without and understanding of WHY it was closed. Thought that was the point of CodeReview. May I missing something? Thanks! — blunders
– blunders, Commented Mar 29, 2011 at 12:58
@blunders: With your comments in mind I've edited the post to add that context in. The confusion arises because we aren't certain why the code was written. If it was meant as a module in a larger program we would read it differently than what it appears it is, a proof-of-concept database access standalone. Please look at my edits and edit the post as necessary - they should at least give you a guide to the information that would be helpful. I'm reopening the post now that there's more information available. — Michael K
– Michael K, Commented Mar 29, 2011 at 13:14

Michael K · Accepted Answer · 2011-03-29 14:54:34Z

3

Couple of things I noticed:

When you connect to the database, you don't check that the connection succeeded. The most common way I've seen to do this in Perl is:

my $connect = DBI->connect($dsn, $username, $password)
   or die "Connection Error:  DBI::errstr\n";

If the connection fails, the program will then display the connection failure message so you can see what's going on.

I see that you use two different ways of accessing the database. It would be better to pick one and use it for all your inserts/updates unless there is a compelling reason not to - it keeps future readers from trying to figure out why you did it differently. In this case I'd reccomend using execute since it allows you to execute multiple times:

$query_handle = $connect->prepare("INSERT INTO dbtable (id, demo) VALUES (?, ?)");
$query_handle->execute($id, $demo);
$id = 2;
$demo = "test2";
$query_handle->execute($id, $demo);

Also don't forget to disconnect:

$connect->disconnect;

edited Mar 29, 2011 at 14:54

answered Mar 29, 2011 at 13:53

Michael K

2,9091 gold badge22 silver badges24 bronze badges

\$\begingroup\$ +1 @Michael K: If I'm reading your answer correctly, you're saying "do()" does not allow for placeholders, but execute() does, right, or no? Main reason I've heard to use placeholders is to prevent SQL injection; which in this case would not be an issue, since the code is only used as an utility for importing data from flat files to a database. Agree about everything else, thanks!! \$\endgroup\$

blunders
– blunders

2011-03-29 14:45:26 +00:00
Commented Mar 29, 2011 at 14:45
\$\begingroup\$ @blunders: I'm sorry, I made a mistake there. do is for executing a query only once and does allow placeholders. The advantage of prepare/execute is that it creates a procedure on the database that you can execute multiple times with many arguments. So you could prepare the statement and then loop over the file, calling execute for every data set. It's faster than creating a new query every time, like do. \$\endgroup\$

Michael K
– Michael K

2011-03-29 14:57:48 +00:00
Commented Mar 29, 2011 at 14:57
\$\begingroup\$ I edited my answer to reflect that. \$\endgroup\$

Michael K
– Michael K

2011-03-29 14:59:23 +00:00
Commented Mar 29, 2011 at 14:59

Add a comment |

Stack Exchange Network

Perl DBI Sample with MySQL DDL

1 Answer 1

You must log in to answer this question.

Hot Network Questions

Perl DBI Sample with MySQL DDL

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions