[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[Bacula-devel] MySQL, PostgreSQL the names of the fields and case sensitive


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@xxxxxxxxxxxxxxxxxxxxx
https://lists.sourceforge.net/lists/listinfo/bacula-devel


This mailing list archive is a service of Copilotco.