Catering for postgres and mysql

[icedlava]

Hi everyone,

I have seen in the code that we are using mysql(i) specific functions and so i have also used these.

However, I see we have a postgres connect function.

Should we then be testing for db type and using the appropriate function in the code for example pg_escape_string vs mysqli_escape_string in the code?

Alternatively, is there work perhaps that needs to be done, or in progress that will provides suitable interface to handle these database specific functions ?

I will continue as is for now, but happy to address the above if necessary.

Thanks for your help in advance!

[icedlava]

Thanks to Exson, I have the answer to my post via email. For others reading:

We can use the DB_escape_string() function, and that will suffice as postgres is apparently deprecated.

[phil]

A few years ago we had full compatibility with postgres... we do have a ConnectDB_postgres.inc somewhere which has all the abstracted DB functions for postgres in .. there was no champion to maintain postgres and test it and I didn't wish to do it so we (I) let it go.

[icedlava]

Hmm now i look at the DB_escape_string() function I'm finding it a problem. It not only escapes the string but also applies htmlspecialchars to it. This is ok if we are outputting to HTML, but I think it is causing problems to store the string in the database already entity encoded.

Should we revise this? Do we need to separate out this functionality to have escaping for db storage, and second to prep for output to HTML?

Thanks.

---

Hi Phil,

A few years ago we had full compatibility with postgres... we do have a ConnectDB_postgres.inc somewhere which has all the abstracted DB functions for postgres in .. there was no champion to maintain postgres and test it and I didn't wish to do it so we (I) let it go.

Yes, I can understand the work involved in trying to maintain for postgres and it's evolution as well as for mysql/mysqli. Perhaps we can look at it again when we have more time.

Thanks,

[TurboPT]

Know that according to php.net, the older [according to them] mysql functions will be considered deprecated as of 5.5.0.

This message appears with each function under their mysql documentation, such as mysql_fetch_array():

Warning

This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information.

Alternatives to this function include:
mysqli_fetch_array()
PDOStatement::fetch()

Here's a little more info from their FAQ, concerning the deprecation:

Why is the MySQL extension (ext/mysql) that I've been using for over 10 years discouraged from use? Is it deprecated? What do I use instead? How can I migrate?

There are three MySQL extensions, as described under the Choosing a MySQL API section. The old API should not be used, and one day it will be deprecated and eventually removed from PHP. It is a popular extension so this will be a slow process, but you are strongly encouraged to write all new code with either mysqli or PDO_MySQL.

[phil]

@Paul - we already have the mysqli interface that users can use by changing their config.php to use mysqli rather than mysql

@Jo - Just needs a developer on the team who prefers to use Postgres and take ownership of the Postgres interface. I am not so interested personally but I understand others may be.

[TurboPT]

@phil ... Understood. I mentioned that to someone in another post, I was merely raising awareness that the older mysql lib is being phased out.
[sometimes these things creep along without realizing]

[tomglare]

It would be a bonus to maintain postgres connectivity if someone can devote themselves to it, but I am not sure if the demand for postgres justifies making it a major priority. Like many people, I would not have touched mysql when it first appeared because it had little inbuilt integrity, in particular lacking foreign keys, but for most purposes it has since caught up with postgres. I still much prefer postgres, particularly the command line tools, but can not afford to spend too much time swimming against the tide.

[phil]

mysql has been transaction safe since webERP's inception ~ 2002. I would not have used it without. innodb was in its infancy - but has been essential to webERP since the very start. Perconia's XtraDB is one solution going forward or SUN's Falcon potentially - when mysql support for innnodb is phased out