Page 2 of 2 First 12
  • Jump to page:
    #16
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    Thanks for the followup. I guess MySQL was going through a rough patch back then, from what I read not everybody was equally happy about Oracle taking over.
  2. #17
  3. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,690
    Rep Power
    6351
    I had nearly the opposite experience with MySQL. We were a MySQL/Oracle shop when Oracle's buyout began. We suddenly found ourselves with really terrible MySQL functionality. "Enterprise" features in MySQL stopped working entirely. Replication had critical bugs which would crash entire clusters. Speaking of, clustering would crash constantly, and the error messages were just random numbers, no descriptive text or manual references. Calling support was met with a sales picture for Oracle proper. Oracle was still too expensive, so we soldiered on with MySQL as best we could. I eventually hacked together an Oracle control node which controlled racks of MySQL nodes. This was for a data warehouse with hundreds of billions of real-time-searchable rows. Naturally, shortly after my warehouse hit production, Cassandra and Couch and the other early nosql databases rose in popularity and my system only spent 3-4 years in production before it was replaced.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  4. #18
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    There have not been any replies for a couple of days now, have you guys given up? That would be a waste, after the wonderfully angry posts some of you made...

    But don't feel bad, it's this way on most forums. A small group of regulars have *very* strong opinions about things and explode in pure rage whenever they are contradicted. Then they spend one, maybe two very angry posts trying to assert their authority, without actually thinking about what their opponent is saying, and then move on to new topics that desperately need their attention.... any topic other than the one where you have to think about the subject.

    Oh well, I guess my search for intelligent life on forums continues...

    Comments on this post

    • ManiacDan disagrees : Outright SAYING that you're trolling is a good way to get disliked.
  6. #19
  7. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,905
    Rep Power
    3969
    Originally Posted by Vinny42
    There have not been any replies for a couple of days now, have you guys given up? That would be a waste, after the wonderfully angry posts some of you made...
    Given up on what? Trying to convince you that PDO is the way to go? It's not my prerogative to tell to what's better or worse. You asked why people like PDO and what benefits it offers. I've expressed my opinion on that topic. If you disagree so be it.

    If you want some responses to your previous posts then:
    That is exactly my problem with PDO; databases don't have the same API. You can pretend that they do, but they dont.
    That leads to caveats like http://php.net/manual/en/pdo.lastinsertid.php
    "This method may not return a meaningful or consistent result across different PDO drivers, because the underlying database may not even support the notion of auto-increment fields or sequences."
    Yes, not all databases are exactly the same, but for the most basic run a query, process the results, they follow pretty much the same pattern. It's up to you to know the limitations of things beyond the basics such as how exactly transactions work, auto-increment, etc.

    One of the nice things about PDO is that you can just extend it to provide customization for databases that don't quite follow the pattern. For example you could add in a method to get the next ID from a sequence and use that rather than the lastInsertId concept if you database of choice requires that.

    There is a lot more to multiple-database-compatibility that just sticking in somthing like PDO, but something like PDO does provide a nice foundation upon which to begin.

    Using PDO doesn't somehow obligate you to actually make your app available for multiple DB's either. You may intend for your app to only use Mysql, or only use PgSQL, but that doesn't mean you automatically just throw PDO out the window because it has one potential feature you don't plan to use. As I mentioned before, I've dealt with multiple DB's over multiple projects. None of the projects were ever intended to run with any DB other than the one they were originally designed with, but by using PDO I didn't have to remember a bunch of different function names/parameter lists just to do basic querying for the different DBs. All of that basic interaction was consistent across each project.

    And that brings me to the most common mistake which is that PDO helps for portability. PDO doesn't and cannot translate queries, so when you move to a different database you will have to check and modify all your queries anyway.
    It's not PDO's job to fix your query text. PDO's job is to give you a consistent API for executing those queries and receiving their results. Ensuring the query text is compatible across databases is a job for a different tool like a query builder library.

    Again, didn't have that problem with PgSQL to begin with, perhaps my dislike of PDO is based more on the fact that it solves problems for MySQL that PgSQL didn't have in the first place.
    That could be true. I have no experience with PgSQL, only MySQL and SQL Server and a very tiny bit of SQLite. I happen to find the mysqli extension's api to be horrid so I switched to PDO very quickly. I've not used the sqlsrv extension much since by the time I needed it, I was already a PDO fan and there was a PDO_SQLSRV drive. From what I've seen of the sqlsrv extension though, it's not as bad as mysqli's, but PDO is still nicer.

    But no, I don't get the point of PDO. Databases are always implemented using some database access object that wraps the calls to mysql_, mysqli_, PDO, PgSQL_ etc.
    That is exactly the point of PDO. PDO is that wrapper which you are talking about. If you're going to build your own classes to wrap all the native API's into a consistent API for your application to use, why not just use PDO and save your self the trouble of writing up all that code.

    PDO is not intended to be just another engine you add to your DB access abstraction layer. PDO is intended to BE your DB abstraction later, or at least the foundation of it.

    Comments on this post

    • codergeek42 agrees : ^ Pretty much exactly this.
    Recycle your old CD's, don't just trash them



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  8. #20
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    Trying to convince you that PDO is the way to go?
    No, just a little feedback will do. Some people apparently felt very strongly in their first post, but don't bother to do a second post, which makes it hard to take their arguments seriously.

    Your reply was objective and I appreciate that.

    So I'll reply to your reply and see if that brings up any interesting points. it's probably going to be long and repetitive but I'll try to be nice :-)

    It's up to you to know the limitations of things beyond the basics such as how exactly transactions work, auto-increment, etc.
    Ok, so am I missing something here or does that mean that in order to work with PDO you need to know how the vendor's own API works, which is what having a generic
    API was supposed to avoid, wasn't it?

    For example you could add in a method to get the next ID from a sequence and use that rather than the lastInsertId concept if you database of choice requires that.
    But then you are wrapping a generic API in a custom API that offers the same functions as the vendor API (well, not really, but you get my point).


    but that doesn't mean you automatically just throw PDO out the window because it has one potential feature you don't plan to use
    It's the other way around, PDO pretends that PgSQL has auto_increment which always returns zero. It's like MySQL with MyISAM and transactions, they don't give an error, but they dont do anything either. That's not good.


    It's not PDO's job to fix your query text
    I know that, but it's what many people think; "just replace the constructor and boom you've migrated to a different database".

    Ensuring the query text is compatible across databases is a job for a different tool like a query builder library.
    Querybuilders are a whole different story (well, fantasy really, more like nightmare actually )


    That is exactly the point of PDO. PDO is that wrapper which you are talking about. If you're going to build your own classes to wrap all the native API's into a consistent API for your application to use, why not just use PDO and save your self the trouble of writing up all that code.
    Because PDO is not consistent. :-)

    If you're going to wrap PDO anyway, just to get rid of the inconsistencies, and then I'm back to where I started; there's only a few lines of code that actually call the vendor API so why bother with an API that may or may not work as advertised and just create your own wrapper class taht calls the consistent vendor API and use that in your projects?

    But as one of the shouty people said: I live alone in a universe where people actually do this wrapping thing :-) Apparently the rest of the PHP developers just put their PDO calls anywhere they want in the code.
  10. #21
  11. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,905
    Rep Power
    3969
    Originally Posted by Vinny42
    Ok, so am I missing something here or does that mean that in order to work with PDO you need to know how the vendor's own API works, which is what having a generic
    API was supposed to avoid, wasn't it?
    What I mean by that is it is up to you to know things like the fact that transactions only work in mysql with InnoDB tables, or that certain statements are not compatible with transactions and cause an automatic commit.

    Likewise if your database of choice does not support auto-inc like behavior and instead requires you to get a sequence value first, then you need to realize lastInsertId is not for you.

    But then you are wrapping a generic API in a custom API that offers the same functions as the vendor API (well, not really, but you get my point).
    I'm drawing a bit on old knowledge since the only DB I've used which did sequences rather than auto-inc like behavior was Oracle back when I was in a data management course in college. Based on what I recall the concept of sequences and auto-inc are fundamentally incompatible with each other. When using a sequence you get the ID first, then use it in your insert. When using auto-inc you insert without an ID to have one generated then get that ID after. Wrapping both methods in a single generic API is not really possible, and is something you the developer have to account for when creating your app.

    For example the old Pear::DB package would simulate sequences for mysql by creating a single column table if I remember right. So if you wanted you're app to be compatible with mysql and oracle you'd skip auto-inc all together in your tables and write your app to use the simulated sequences for mysql. This is something that could be accomplished by extending PDO to add a few sequence related functions. Granted PDO probably should have included some sequence management stuff from the start, but it didn't.


    It's the other way around, PDO pretends that PgSQL has auto_increment which always returns zero. It's like MySQL with MyISAM and transactions, they don't give an error, but they dont do anything either. That's not good.
    That just sounds like a poor choice on the part on whoever developed the PDO driver for PgSQL. If an auto-inc like feature is truly not supported by PgSQL then calls to lastInsertId probably should have throw an exception indicating as such I would think. Based on what little reading in the PHP manual I've done in the past 5 minutes it sounds like lastInsertId could be implemented for PgSQL, whoever created the driver simply didn't do so fully. I base this on these couple of quotes:
    Originally Posted by PDO::lastInsertId page
    For example, PDO_PGSQL requires you to specify the name of a sequence object for the name parameter.
    so it works, but requires a sequence name where as Mysql doesn't.

    Originally Posted by pg_last_oid page
    If the name of the sequence is unknown, the pg_get_serial_sequence PostgreSQL 8.0 function is necessary.

    PostgreSQL 8.1 has a function LASTVAL that returns the value of the most recently used sequence in the session. This avoids the need for naming the sequence, table or column altogether.
    Seems to indicate that it'd be possible for PDO_PGSQL to support lastInsertId without requiring the name, it maybe just has not been coded properly.

    The Mysql transaction thing is something I would put upon the developer to account for any not PDO_MYSQL. It's not PDO's job to make sure that when you try and use a transaction you've properly setup your DB with InnoDB tables and don't call things like DROP DATABASE blah;. Mysql does support transactions, so there is no reason for beginTransaction or related functions to fail. You just need to ensure that your specific application supports transactions by using the proper table types and not using incompatible queries.


    There are some instances of things being inconsistent across drivers of PDO, but this is usually just due to whoever implemented that database's driver did not do the job fully or completely. For example lastInsertId did not work in the SQL Server version either for a while, and the quote method was buggy. Neither of these where inherently a PDO problem though, it was a problem of driver developer not being thorough.

    I know that, but it's what many people think; "just replace the constructor and boom you've migrated to a different database".
    That an issue of people just not being educated properly and spreading mis-information. Such problems are not isolated to PDO, and are something the PHP community at large need to help correct.

    Because PDO is not consistent. :-)
    PDO is about as consistent as it can be in my experience. Not any more inconsistent than any other abstraction layer I've seen that tries to deal with multiple databases. As mentioned, a number of the inconsistencies are simply caused by the author(s) of the driver either not being thorough in their implementations.

    As I've mentioned though, if PDO is not perfect for you, one of the benefits of it is you can most likely still use it as a foundation and just extend it to bring it up to par, saving a lot of time not having to develop an entire abstraction layer from scratch.

    If you already have a working and well-featured abstraction layer that you're happy with then there is no need to go out and replace it with PDO just because PDO exists now. For those who do not already have an abstraction layer though, PDO provides a nice foundation. It's also a nice api that is easy to learn and use for those who don't even care about abstraction and just want something that works and lets them connect to mysql (or whatever they are using).
    Recycle your old CD's, don't just trash them



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  12. #22
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    Seems to indicate that it'd be possible for PDO_PGSQL to support lastInsertId without requiring the name, it maybe just has not been coded properly.
    That woud probably be worse than returning zero :-)
    Like all sequence-using databases, PgSQL is not limited to using one sequence per table, or even to using a sequence once per table, it can even uer multiple sequences multiple times in a single statement.

    The most common methods of using a sequence is the "get it before you use it" method that you mentioned, or PgSQL's "INSERT RETURNING" syntax where you can run an INSERT query an tell it to return a resultset like a SELECT statement, containing a set of named fields used in the INSERT. So, an INSERT can return multiple values including but not limited to sequences.


    For those who do not already have an abstraction layer though, PDO provides a nice foundation. It's also a nice api that is easy to learn and use for those who don't even care about abstraction and just want something that works and lets them connect to mysql (or whatever they are using).
    Well, I guess that's the recurring argument; PDO's API is more userfriendly than MySQL's API. So much in fact that the inconsistencies are taken for granted.
    I guess it just annoys me much more than others. But then again I get annoyed with the fact that MySQL returns NULL when you divide by zero, and the idea that you can mix transactional and nontransactional tables... or the repeatable-read default....brrr..
Page 2 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo