Re: from pgsql_perl5 to DBD-Pg
- From: "Bob" <catdogbeloved@xxxxxxxxx>
- Date: 28 Feb 2007 03:11:50 -0800
How to port code from Pg[1] to DBI[2]
[bare bones instructions]
=======================================
Tested and working thus far:
----------------------------
Connect to the SQL server:
[1]: $dbh = Pg::connectdb("dbname=$dbname");
[2]: $dbh = DBI->connect("DBI:Pg:dbname=$dbname", "", "", {AutoCommit
=> 1});
Query the SQL server:
[1]: $dbh->exec("<SQL STATEMENT>");
[2]: $dbh->do("<SQL STATEMENT>"); # non select
[2]: $sth = $dbg->prepare("<SQL STATEMENT>"); $sth->execute; # select
Return the error message of the query, if any:
[1]: $dbh->status == 0
[2]: (not defined $dbh->errstr)
[1]: $dbh->resultStatus == 1 # command ok
[2]: (not defined $sbh->errstr)
[1]: $dbh->resultStatus == 7 # fatal error
[2]: (defined $dbh->errstr)
Return the number of records in the query result:
[1]: $ntuples = $sth->ntuples
[2]: $results = $sth->fetchall_arrayref(); $ntuples = @$sth;
N.B. The documented method is "$sth->rows", but it does not work. The
method @$sth is not documented, but works. RECOMMENDATION FOR Tim
Bunce: Define and document a method "$sth->nrecords" that works.
Return the number of fields in the query result:
[1]: $sth->nfields
[2]: $sth->{NUM_OF_FIELDS}
N.B. RECOMMENDATION FOR Tim Bunce: Define and document a method "$sth-
nfields" as alias for "$sth->{NUM_OF_FIELDS}".
Return the field name associated with the given field number:
[1] $sth->fname($i)
[2]: $sth->{NAME}->[$i]
Return the value of the given record and field number:
[1]: $value = $sth->getvalue($r,$f);
[2]: $results = $sth->fetchall_arrayref(); $value = $results->[$r]
[$f];
very simple example:
-------------------------------
[1]:
my $bag = $dbh->exec("<SQL STATEMENT>);
for (my $n = 0; $n < $bag->rows; $n++) { print $bag->getvalue($n,0) .
"\n"; }
[2] (assuming autocommit=1):
my $bag2 = $dbh->prepare("<SQL STATEMENT>");
$bag2->execute;
my $bag = $bag2->fetchall_arrayref();
my $bag_rows = @$bag;
for (my $n = 0; $n < $bag_rows; $n++) { print $bag->[$n][0] . "\n"; }
Pending solution thus far:
--------------------------
In case the last query was an INSERT command it returns the oid of the
inserted
tuple:
[1]: $dbh->oidStatus
[2]: $dbg->pg_oid_status [untested]
Print all the records in an intelligent manner:
[1]: $sth->print($fout, $header, $align, $standard, $html3, $expanded,
$pager, $
fieldSep, $tableOpt, $caption)
[2]: ? - open thread at http://groups.google.it/group/perl.dbi.users/browse_thre
ad/thread/3fa50f7b647ada6d?hl=en
.
- References:
- from pgsql_perl5 to DBD-Pg
- From: Bob
- from pgsql_perl5 to DBD-Pg
- Prev by Date: Re: (Fwd) DBI's method for reading [row x,field y]
- Next by Date: AW: CLOB Problem with DBD::ODBC/DBD::ADO for SQL Server
- Previous by thread: Re: from pgsql_perl5 to DBD-Pg
- Next by thread: (alleged-)Null-Operation speeds up DBD::Oracle by factor 25
- Index(es):
Relevant Pages
|
