Thread: Inquiry From Form [pgsql]

I code with PHP and use it to communicate with MySQL, if I started using PostgreSQL would I have to change my coding to
communicatewith the database? 

On Fri, 15 Nov 2002, William wrote:

> I code with PHP and use it to communicate with MySQL, if I started using
> PostgreSQL would I have to change my coding to communicate with the
> database?

Probably, but only slightly.

I changed my DB from MySQL to PostgreSQL and had to change each SQL call
slightly.  It seems to me the difference was PostgreSQL needs to know the
number of rows gotten and then loop through them with a for loop, with
MySQL I was able to use a while loop without checking the number of rows
first.

It could have been my inexperience that caused me to miss the obvious,
though.

In perl there is the DBI module and php has DBX.  With it you use a common
calling cenvention across a bunch of Databases (FrontBase, Microsoft SQL
Server, MySQL, ODBC, PostgreSQL, Sybase-CT, Oracle)


From the manual at php.net:

 -----------------------------------------------

The dbx functions allow you to access all supported databases using a
single calling convention. The dbx-functions themselves do not interface
directly to the databases, but interface to the modules that are used to
support these databases.

 -----------------------------------------------

Of course, not all these DBs offer the same features, so you'd still not
be database independent unless you limit your DB calls to the common ones,
most likely SELECT, INSERT, UPDATE, and DELETE.  But if you are using
MySQL maybe that's all you are doing anyway.......

I don't have the dbx module available on my machine and since I'm
committed to using (and learning more about) PostgreSQL I don't use the
dbx.  But it seems it would be a good way to go......


Maybe someone else will chime in with more info.

brew

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sunday 17 November 2002 05:43 pm, you wrote:
> On Fri, 15 Nov 2002, William wrote:
> > I code with PHP and use it to communicate with MySQL, if I started using
> > PostgreSQL would I have to change my coding to communicate with the
> > database?
>
> Probably, but only slightly.
>
> I changed my DB from MySQL to PostgreSQL and had to change each SQL call
> slightly.  It seems to me the difference was PostgreSQL needs to know the
> number of rows gotten and then loop through them with a for loop, with
> MySQL I was able to use a while loop without checking the number of rows
> first.
>
[snip]

Using PostgreSQL 7.2 and PHP 4.1.2 in Linux, I found that some code is
becoming similar with the MySQL+PHP. For instance, you don't need to know the
numrows first to do a loop that display a query result.

New PostgreSQL+PHP way:

while($display=pg_fetch_array($getquery))
    {
        ...
    }

MySQL way :
while($display=mysql_fetch_array($getquery))
    {
        ...
    }

You can also use pg_numrows() instead of pg_num_rows() and some other 'new'
syntax that are like mysql way.

HTH
Budi.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE92HE1ZVSaxFm5xCIRAr1pAKCTnSD+hUoXe3+L+XXDONwhne49uACgtkGZ
CsVGw+0Y9nji8MZj8D50e7s=
=3lBF
-----END PGP SIGNATURE-----

On Fri, 15 Nov 2002, William wrote:

> I code with PHP and use it to communicate with MySQL, if I started
> using PostgreSQL would I have to change my coding to communicate with
> the database?

Not really.  The only issue is if you used MySQL proprietary stuff.
There's a lot of things in MySQL that are workarounds for it not being a
transactional database that won't work in Postgresql, but using the
"right" method (i.e. a transaction or ANSI SQL) will work just fine.

The only other thing to change is your mysql_xxx commands to pgsql_xxx
commands.

Also, Postgresql doesn't have a pgsql_lastinsert_id like MySQL, instead,
you do it like this:

(Warning pseudocode... :-)

begin;
insert into table yada (field1, field2) values (val1, val2);
select currval('yada_seq');
insert into table yada_child (field1, field2, y_id) values (val1, val2,
y_id);
commit;

i.e. you use currval('seqname') to find out what the id was that was just
inserted.

On Sun, 17 Nov 2002 brew@theMode.com wrote:

>
> On Fri, 15 Nov 2002, William wrote:
>
> > I code with PHP and use it to communicate with MySQL, if I started using
> > PostgreSQL would I have to change my coding to communicate with the
> > database?
>
> Probably, but only slightly.
>
> I changed my DB from MySQL to PostgreSQL and had to change each SQL call
> slightly.  It seems to me the difference was PostgreSQL needs to know the
> number of rows gotten and then loop through them with a for loop, with
> MySQL I was able to use a while loop without checking the number of rows
> first.
>
> It could have been my inexperience that caused me to miss the obvious,
> though.

The ability to use the

while ($row = pg_fetch_array(res)){

}

construct wasn't included in PHP until somewhere around 4.1 or 4.2 I
think.

on 11/18/02 10:07 AM, scott.marlowe@ihs.com purportedly said:

>> I code with PHP and use it to communicate with MySQL, if I started
>> using PostgreSQL would I have to change my coding to communicate with
>> the database?
>
> Not really.  The only issue is if you used MySQL proprietary stuff.
> There's a lot of things in MySQL that are workarounds for it not being a
> transactional database that won't work in Postgresql, but using the
> "right" method (i.e. a transaction or ANSI SQL) will work just fine.

There are other non-obvious gotchas. For instance, MySQL is much more
"tolerant" (to put it nicely) with column constraints. Take the following
table example:

CREATE TABLE some_table ( number INT NOT NULL, date DATE NOT NULL );

INSERT INTO some_table (date) VALUES ('2002-05-09');
    -> MySQL accepts and sets "number" to 0
    -> Postgres rejects with error (NOT NULL constraint)
Same goes if you insert only "number" and not date: Postgres rejects, MySQL
accepts with date value '0000-00-00'.

INSERT INTO some_table VALUES(1,'0000-00-00');
    -> MySQL accepts
    -> Postgres rejects with error--Postgres does not have empty dates or
times

INSERT INTO some_table VALUES(2, 2002-06-05);
    -> MySQL accepts (has valid date constants)
    -> Postgres rejects: dates must be quoted.

You should thoroughly test your application before making it live, as these
types of issues are not always obvious in the code. These gotchas are the
biggest problems for programmers who have learned SQL from MySQL, which,
IMHO, encourages sloppy SQL coding.

Other than these situations, the biggest difficulty with conversion is with
the database schema itself.

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"