If you have do it. Then my code might help you
use DBI; use diagnostics; use strict;
my $database="Database"; my $username="postgres"; my $password="123";
my ($mbd, $sth1);
$mbd = DBI->connect("dbi:Pg:dbname=$database", "$username", "$password") or die "Error: $DBI::errstr \n";
$sth1 = $mbd->prepare
("
INSERT INTO public.table1 (field1, field2, field3, field4)
VALUES (?::varchar(10), ?::varchar(8) , ?::float4 , ?::varchar(15))
");
### Here comes your perl script for didactic purposes I only get 4 variables ######
### Inserting into the database ############
$sth1->execute ($normal[0], $normal[1], $normal[2], $normal[3])
or die "Error $DBI::errstr\n";
Lawrence Statton wrote:
Hi, can anybody address me to online resources to learn how to connect to a PostgreSQL DB server through a Perl script? Code examples are welcome ;-) TIA
------------------------- begin perl code -----------------------
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
our $_dbh;
use constant DB_DSN => 'dbi:Pg:dbname=test;'; use constant DB_USER => 'your-user-identity-not-mine'; use constant DB_PASSWORD => 'your-secret-password-not-mine' ;
sub DBH
{
return $_dbh if $_dbh && $_dbh->ping;
for my $tries (1..10) {
$_dbh = DBI->connect( DB_DSN, DB_USER, DB_PASSWORD );
return $_dbh if $_dbh && $_dbh->ping ;
sleep ( $tries * 3 ) ; }
}
my $sth = DBH->prepare( q { SELECT CITY.NAME, STATE.NAME FROM CITY JOIN STATE ON CITY.STATE = STATE.ABBREV WHERE CITY.POPULATION > 50000 } ) or die "Prepare: ", DBH->errstr ;
$sth->execute( ) or die "Execute: ", $sth->errstr ;
while (my ($city, $state) = $sth->fetchrow_array ) {
print "$city is in $state\n"; }
--------------------------- end perl code -------------------------
Of course this entire program is predicated on having a database ...
Here is the one I used in pg_dump format (editted for brevity)
CREATE TABLE state ( name character varying, abbrev character(2) NOT NULL );
COPY state (name, abbrev) FROM stdin; Alabama AL California CA Oregon OR Washington WA Nevada NV Idaho ID Arizona AZ \.
ALTER TABLE ONLY state ADD CONSTRAINT state_pkey PRIMARY KEY (abbrev);
CREATE TABLE city ( name character varying, state character(2), population integer );
COPY city (name, state, population) FROM stdin; San Francisco CA 750000 Phoenix AZ 1200000 Portland OR 540000 Montgomery AL 200000 Birmingham AL 242000 Redding CA 81000 Eugene OR 138000 Olympia WA 42500 Seattle WA 570000 Las Vegas NV 480000 Boise ID 185000 Tuscon AZ 486000 \.
ALTER TABLE ONLY city ADD CONSTRAINT "$1" FOREIGN KEY (state) REFERENCES state(abbrev);
NB - There are embedded tabs in the COPY sections that your mailer might destroy.
-- If I have seen further it is by standing on the shoulders of the giants Isaac Newton
-- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] <http://learn.perl.org/> <http://learn.perl.org/first-response>
