Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Re: Query Formatting

by tcf03 (Deacon)
on Dec 06, 2007 at 10:01 UTC ( #655334=note: print w/replies, xml ) Need Help??


in reply to Query Formatting

This should do what you are looking to do.

#!/usr/bin/perl use strict; use warnings; use DBI; sub dbConnect{ my ($database, $username, $password, $hostname) = @_; my $db = DBI->connect("DBI:SQLite:$database:$hostname", $username, $password) || die "Cannot connect to host database.".$DBI::errstr; return $db; } sub dbDisconnect{ my ($db) = @_; $db->disconnect; } sub dbINSERT{ my $table = shift; my $set = shift; my @values = @_; my($database) = "myDB"; my($username) = "username"; my($password) = "password"; my($hostname) = "myHost.com"; my $db = dbConnect($database, $username, $password, $hostname); my $vals = join',', map { $db->quote($_) } @values; my $query = qq|INSERT into $table ($set) VALUES ($vals)|; print "$query\n"; $query = $db->prepare($query); #LINE 31 $query->execute; #LINE 40 $query->finish; dbDisconnect($db); } sub dbUPDATE{ # Do updates here } sub dbDELETE{ # Do deletes here } dbINSERT('ORGANISM', 'ORG_NAME, GI, NC', "11111111", "some bacteria", +"NC_000000");
Though, I would move the db connection out of the insert/delete/update subs and just do something like this
#!/usr/bin/perl use strict; use warnings; use DBI; sub dbConnect{ my ($database, $username, $password, $hostname) = @_; my $db = DBI->connect("DBI:SQLite:$database:$hostname", $username, $password) || die "Cannot connect to host database.".$DBI::errstr; return $db; } sub dbINSERT{ my $db = shift; my $table = shift; my $set = shift; my @values = @_; my $vals = join',', map { $db->quote($_) } @values; my $query = qq|INSERT into $table ($set) VALUES ($vals)|; print "$query\n"; $query = $db->prepare($query); #LINE 31 return ( $query->execute ) : 0 : 1; } sub dbUPDATE{ # Do updates here } sub dbDELETE{ # Do deletes here } my $db = dbConnect("myDB", "username", "password", "myHost.com"); my $status = ( dbINSERT($db, 'ORGANISM', 'ORG_NAME, GI, NC', "11111111 +", "some bacteria", "NC_000000") == 0 ) ? 0 : 1; END { $db->disconnect }
Its untested, but should work fine for you.

Update added return code from insert and removed disconnect sub. After re-reading your original post - you may wish to re-add the disconnect sub ( and remove the END block ) as this looks to be part of a larger program

Ted
--
"That which we persist in doing becomes easier, not that the task itself has become easier, but that our ability to perform it has improved."
  --Ralph Waldo Emerson

Replies are listed 'Best First'.
Re^2: Query Formatting
by tcf03 (Deacon) on Dec 06, 2007 at 10:17 UTC
    one more addition you may want to consider is setting $db->{RaiseError} or $db->{PrintError} or both.
    sub dbConnect{ my ($database, $username, $password, $hostname) = @_; my $db = DBI->connect("DBI:SQLite:$database:$hostname", $username, $password) || die "Cannot connect to host database.".$DBI::errstr; $db->{RaiseError} = 1; $db->{PrintError} = 1; return $db; }
    Ted
    --
    "That which we persist in doing becomes easier, not that the task itself has become easier, but that our ability to perform it has improved."
      --Ralph Waldo Emerson

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://655334]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (9)
As of 2022-05-23 11:21 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you prefer to work remotely?



    Results (81 votes). Check out past polls.

    Notices?