Re: Porting Code to Postgresql

On Wed, 15 Oct 2003 10:16:29 -0400 Errol Neal <sysadmins@enhtech.com> wrote:
> There is a php based sourceforge project called mailwatch.
> (http://www.sourceforge.net/projects/mailwatch) It logs data from the excellent
> Mailscanner security product into a mysql database. Now, I am not a php
> programmer,
> and I am barely a Postgres DBA, but I would really like to port the code to
> Postgresql.
> I have my trust Postgresql Book which covers the API for Postgresql
... and the
> PHP statements
> used for Postgresql seem almost identical to those used for Mysql. I
> understand that there are
> some slight differences in the data types supported by Mysql and
> Postgresql, however are the differences
> between the two Databases and API's that great to make task impossible for
> an unexperienced person
> such as myself?

it shouldn't be too awful as long as you're willing to learn.

watch for case folding issues. i ultimately ended up always making my table
names and column names all lower because of the way that php behaves with
case. if you have mixed case stuff, then ultimately you will end up
spending a lot of time chasing annoying, stupid bugs because php doesn't
require variables to be initialized, it just creates them, so you could end
up referencing $row->ColumnName and getting null because php put it in
$row->columnname.

when iterating over a result set, be aware that in at least some versions
of the php->postgresql interface,

while( $row = pg_fetch_object( $result, $row))
{
   ...
}

will error at the last row instead of returning a false value. you need to do:

$count = pg_numrows( $result);
for( $i = 0; $i < $count; $i++){
    $row = pg_fetch_object( $result, $row);
    ...
}

if you want it to work.

richard
--
Richard Welty                                         rwelty@averillpark.net
Averill Park Networking                                         518-573-7592
    Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security