Hi! I am developing web interface for PHP5 (specifically on the Zend Framework) to bacula. I use PHP PDO module. And in the process of work, I was confronted with some difficulties.
For example, when writing SQL queries, which will be equally performed for MySQL and PostgreSQL. I am referring to DB Catalog. The fact is that the names of tables and fields in different registers for MySQL and PostgreSQL: src/cats/make_mysql_tables.in src/cats/make_postgresql_tables.in An example may clarify this: Example 1. PHP PDO is not used. http://webacula.svn.sourceforge.net/viewvc/webacula/devel/pgsql_tests/noPDO_test.php?view=markup --- noPDO_test.php #!/usr/bin/php <?php echo "\n----------------- MySQL test\n\n"; $db = mysql_connect("localhost", "root", ""); mysql_select_db("bacula"); $res = mysql_query("SELECT * FROM Filename LIMIT 3;"); while ( $row = mysql_fetch_assoc($res) ) { echo $row['FilenameId'], "\t", $row['Name'], "\n"; } echo "\n----------------- PostgreSQL test\n\n"; $db = pg_connect("host='localhost' dbname='bacula' user='root' password=''"); $res = mysql_query("SELECT * FROM Filename LIMIT 3;"); //$res = mysql_query("SELECT * FROM filename LIMIT 3;"); while ( $row = mysql_fetch_assoc($res) ) { echo $row['FilenameId'], "\t", $row['Name'], "\n"; // echo $row['filenameid'], "\t", $row['name'], "\n"; } ?> Both test (for MySQL and PostgreSQL) will be successful. It does not matter what request will be for PostgreSQL: SELECT * FROM filename or SELECT * FROM Filename Similarly, no matter how the results will then be processed: $row['FilenameId'] or $row['filenameid'] As we can see the names of fields case insensitive. The situation will change if we use the PHP PDO. Example 2. Using PDO. http://webacula.svn.sourceforge.net/viewvc/webacula/devel/pgsql_tests/PDO_test.php?view=markup --- PDO_test.php #!/usr/bin/php <?php echo "\n----------------- MySQL test\n\n"; // connect to DB $database = new PDO("mysql:dbname=bacula;host=localhost"); $stmt = $database->prepare("select * from Filename limit 3;"); if ( $stmt->execute() ) { while ($row = $stmt->fetch()) { $filenameid = $row['FilenameId']; $name = $row['Name']; echo $filenameid, "\t", $name, "\n"; } } echo "\n----------------- PostgreSQL test\n\n"; // connect to DB $database = new PDO("pgsql:dbname=bacula;host=localhost"); $stmt = $database->prepare("select * from filename limit 3;"); // is OK //$stmt = $database->prepare('select * from "Filename" limit 3;'); // ok if ( $stmt->execute() ) { while ($row = $stmt->fetch()) { $filenameid = $row['filenameid']; // ok // $filenameid = $row['FilenameId']; // Error! Field name is case sensitive ! $name = $row['name']; // ok // $name = $row['Name']; // Error! Field name is case sensitive ! echo $filenameid, "\t", $name, "\n"; } } ?> If we reference to the field as $row['FilenameId'] then a error occured. I think (and not only I) that in the future, it makes sense to do the same field names, tables. src/cats/make_mysql_tables.in src/cats/make_postgresql_tables.in However, such changes will not be back compatible with earlier versions bacula. PS. I can help carry out this work. Thanks. -- with best regards ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ Bacula-devel mailing list Bacula-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel
