problem connecting client to a postgresSQL server

On Fri, Nov 09, 2001 at 05:22:10PM +0800, darwin wrote:

Hi!! Im Darwin a student from DATA COllege San Fernando
Pampanga, Philppines. I am a new memeber in your mailing
list. Hope you could accomodate me.

I just want to consult anyone out there who could help me in
my problem regarding "HOW TO CONNECT A CLIENT TO A
POSTGRESSQL SERVER USING PERL ?". I am having a hard since
August and its bugging me cause I could not finish my
thesis.

Use dbi.
You will need the DBI and DBD::Pg modules.
For more info see http://dbi.perl.org/.

- Einar Karttunen

On Friday 09 November 2001 03:22, darwin wrote:

Hi!! Im Darwin a student from DATA COllege San Fernando
Pampanga, Philppines. I am a new memeber in your mailing
list. Hope you could accomodate me.

I just want to consult anyone out there who could help me in
my problem regarding "HOW TO CONNECT A CLIENT TO A
POSTGRESSQL SERVER USING PERL ?". I am having a hard since
August and its bugging me cause I could not finish my
thesis.

I'm hopping that a generous mind could share something about
it.

Hi,

Look into the Perl DBD/DBI interface. Hit http://www.cpan.org/ and do a
search on DBI. Works like a charm.

GB

--
GB Clark II | Roaming FreeBSD Admin
gclarkii@VSServices.COM | General Geek
CTHULU for President - Why choose the lesser of two evils?

We also use this interface, very successfully.
Apart from DBI, you will also need DBD::Pg

Tielman J de Villiers
BondNet Pty Ltd

-----Original Message-----
From: GB Clark II [mailto:gclarkii@vsservices.com]
Sent: Friday, November 09, 2001 12:23 PM
To: darwin; pgsql-general@postgresql.org
Subject: Re: [GENERAL] problem connecting client to a postgresSQL server

On Friday 09 November 2001 03:22, darwin wrote:

Hi!! Im Darwin a student from DATA COllege San Fernando Pampanga,
Philppines. I am a new memeber in your mailing list. Hope you could
accomodate me.

I just want to consult anyone out there who could help me in my
problem regarding "HOW TO CONNECT A CLIENT TO A POSTGRESSQL SERVER
USING PERL ?". I am having a hard since August and its bugging me
cause I could not finish my thesis.

I'm hopping that a generous mind could share something about it.

Hi,

Look into the Perl DBD/DBI interface. Hit http://www.cpan.org/ and do a
search on DBI. Works like a charm.

GB

--
GB Clark II | Roaming FreeBSD Admin
gclarkii@VSServices.COM | General Geek
CTHULU for President - Why choose the lesser of two evils?

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Hello GB,

Install DBI modules for PgSQL and start working

HERE IS SAMPLE PROGRAM

---------------------- PROGRAM STARTS HERE -----------------------
#! <your path to perl>

use DBI;

# DATABASE HANDLE DECLARATION
my $dbHandle;

# QUERY VARIABLE DECLARATION
my $dbQuery;

# QUERY VARIABLE HANDLE DECLARATION
my $dbQueryHandle;

# VARIABLE TO STORE THE HASH REFS
my $dbRow;

# ESTABLISH CONNECTION TO THE PG-SQL AND CONCERNED DATABASE
$dbHandle=DBI->connect( "dbi:Pg:dbname=testdb", "username", "password") || die $DBI::errstr ;

# QUERY A SIMPLE TABLE
$dbQuery="select testcode,testname from testtab";

# PREPARE THE QUERY
$dbQueryHandle=$dbHandle->prepare($dbQuery) || die $dbHandle->errstr;

# EXECUTE THE QUERY
$dbQueryHandle->execute || die $dbHandle->errstr;

# FETCH A ROW

if($dbRow=$dbHandle->fetchrow_hashref())
{
# PRINT THE FETCHED RESULT SET
print "\n TESTCODE: $dbRow->{testcode}";
print "\n TESTNAME: $dbRow->{testname}";
}

$dbQueryHandle->finish || die $dbHandle->errstr;

$dbHandle->disconnect || die $DBI::errstr;

--------------------PROGRAM ENDS----------------------
if you're connecting to some remote machine, just add the IP address
in the DBI connect statement.

Hope I have helped u a bit

--
Best regards,
Gurudutt mailto:guru@indvalley.com

Life is not fair - get used to it.
Bill Gates

Friday, November 09, 2001, 3:53:23 PM, you wrote:

GCI> On Friday 09 November 2001 03:22, darwin wrote:

I've got a very big program that I wrote using Pg.pm, and now I'd like to
convert it to using DBD:Pg. Can anybody give me any general tips on how
to do that? Just convert all the $conn->exec to $DBH->do, or is it more
complicated than that?

Also, the big program does a whole bunch of
$conn->exec("SELECT * FROM tablename WHERE indexed_column = '$key'");
and things like that. Is there a way in DBD:Pg I can re-parse those
queries with "...indexed_column = :1" and then supply a different key each
time? I remember when I used to write C programs that accessed Oracle
(about 8 years ago, so the details are hazy), that was a major performance
improvement.

--
Paul Tomblin <ptomblin@xcski.com>, not speaking for anybody
"I had to kill him -- he was starting to make sense."

Paul Tomblin <ptomblin@xcski.com> writes:

[converting Pg.pm to DBD::Pg]

Also, the big program does a whole bunch of
$conn->exec("SELECT * FROM tablename WHERE indexed_column = '$key'");
and things like that. Is there a way in DBD:Pg I can re-parse those
queries with "...indexed_column = :1" and then supply a different key each
time? I remember when I used to write C programs that accessed Oracle
(about 8 years ago, so the details are hazy), that was a major performance
improvement.

You can basically do:

$stmt = $dbh->prepare("SELECT * FROM tablename WHERE indexed_column = ?");

$stmt->execute($key1);
$stmt->execute($key2);

This is nice because it will do any escaping in $key that needs to be
done.

However, PG doesn't cache query plans in this situation (unlike
Oracle) so you won't see a big performance win.

(Apologies if my syntax is off; I mostly use Java now.)

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863