I've started to work on switching the database abstraction layer used by Geneone to use PEAR::MDB2. At the moment, we use PEAR::DB. DB hasn't been updated for almost a year and MDB2 is thought of it's successor (there was talk of renaming it to DB2 to mark it as a successor of DB). MDB2 is also supposed to be a lot faster than DB and it has a few nice new features and is supposed to work better with PDO.
It's going to take quite a lot of work as every single database query in Geneone needs to be rewritten because I have used the ! placeholder (literal placeholder) to insert table names, which MDB2 doesn't support.
e.g. preparing a query as SELECT * FROM ! and then using Gene::tableName() to insert the table name later on. This actually would cause problems with some real prepared queries as an extension such as mysqli won't support the ! placeholder and DB probably emulates it.
I also make heavy use of using prepared queries in the query() function. In PEAR::DB, you could provide a query with the prepared statement as the first argument and the values in the second. This isn't allowed in MDB2 and you have to prepare the statement and then execute it. A couple of extra lines of code needed everywhere.
There's also heavy use of autoExecute which again doesn't exist in MDB2.
The most annoying thing about MDB2 is the lack of decent documentation. DB and MDB both have fantastic documentation which makes them really to use. Sure there's phpDoc Documentation but it's confusing and is no replacement for real documentation.
Hi. I’m also converting to MDB2 from DB. The most annoying thing I’m encountering is the necessity of having to include the datatypes during an update. For example:
$query = "UPDATE ip_location SET primary_ip = ?, loc_id = ?, component_type_id = ? "
. " WHERE primary_ip = ?";
$vals = array($ip, $loc_id, $device_id, $original_ip);
$types = array(‘text’, ‘integer’, ‘integer’, ‘text’);
$sth = $db_conn->prepare($query, $types);
$result = $sth->execute($vals);
If I omit the $types (it is an optional argument), I get an error about inserting text into an integer field.
The PEAR::DB library (and the perl DBD modules) don’t require one to specify the $types. I always thought that was one of the really nice aspects of coding in scripting languages that were looser with data types. It’s going require quite a bit of coding to port from DB to MDB2 because of the $types issue. I don’t suppose you’ve come across any way to have MDB2 do more of the work in this regard?
Thanks,
Ethan (ethan@ethanbrown.org)
Hi there,
You don’t have to specify datatypes. PEAR::MDB2 will detect the datatype of the variable you send to it and use that if you don’t provide a datatype. So if you have a string which contains a number, it’ll detect a string and fail.
You can get around this by manually typecasting e.g. by doing:
I’m actually using this method at the moment. It is a bit strange that MDB2 has this behavior in a weakly-typed programming language but that’s just the way it is.
I believe specifying the types actually keeps your code cleaner and makes it run faster so it may be worth going through all your code and specifying datatypes.