Does PDO use DBMS syntax or do they all work?

Asked

Viewed 86 times

3

When considering the use of PDO, one of the main usability is the scope of several databases.

Example scenario

Different ways to filter 5 records, which vary by database:

SELECT TOP 5 campo FROM tabela

SELECT campo FROM tabela LIMIT 5

SELECT campo FROM
(SELECT * FROM tabela ORDER BY campo)
WHERE ROWNUM <= 5

Doubts

  • PDO has a "default" more like some specific bank?
    • When I use PDO, I can use any of the above ways that will "convert" according to the database?
    • When I "change" my database, I would have to change all querys, or the PDO makes it automatic?
php sql pdo

2 answers

4


TL; DR

You have to rewrite "all" the darlings.

Detailing

You just asked about the PDO fallacy. It is an abstraction mechanism to the database access API, not the database commands.

One of the reasons people use PDO is that they can one day change databases. And that’s already a fallacy. People don’t do that. They create a complication for something that has almost zero chance of happening. And if it happens the cost of adaptation is absurdly higher than access to the API. And if it is the database the problem then it is likely that you will have to exchange other things. Wrong decisions almost never come alone. In many cases you have to change up the language.

Apart from the extreme cases it is a matter of tuning DB’s adequate than changing it. There are examples of huge websites using all Dbs, even Sqlite, so changing the DB will rarely be the solution, and if you really need it, it’s such a radical change of site profile that changing everything will not be a problem.

But what most people don’t see is that the SQL commands to be used, and even the database modeling has to be different according to the SGDB chosen. The worst thing you can do is use a thinking model for Mysql and switch to Oracle, and use the darlings of each other, because even if it works, they will have different commitments and the result will be worse.

You will still need to send specific commands from each database. It won’t convert anything, it won’t interpret what you wrote in the queries and commands sent to the server, it won’t help you at all.

Can’t say you’ll have to change them all darlings, but if you’re lucky you will. Yes, lucky, because it will make you rethink the whole system and it will be very good. Bad luck if you think it has made your life easier.

And I didn’t even mention when the change of persistence is not only changing from Mysql to SQL Server, but from the entire data model that even maintains the relational. If DB’s decision was wrong it is likely that only real fix with full change of model.

Another reason people choose PDO is safety. That’s just disinformation. There’s no reason for it to be safer. Indirectly can be even more insecure.

I’ve seen people say it’s faster, it’s rare, but some people talk. There’s no way an extra layer can be faster than the original.

So almost always the choice for its use is mistaken. I’m not saying I don’t have a case to use, but it’s very rare that it’s necessary and useful.

And be prepared that it has several disadvantages too, but this is outside the scope of the question.

As far as I know, there’s nothing that makes the abstraction you want, and even if you do, it’s impossible to have anything with a good result. Have tried in other languages, very skilled teams did everything, evolved, and still meets more or less well a portion of the cases, the rest meets badly.

  • The "advantage" of the PDO in being able to be used with several banks, then it would not be the "exchange" of bank (which is rare as I said), but rather the possibility of working with several banks, and thus "unifying" a whole "system", for example, a specific system that collects data from various systems, and processes them for another purpose!?

  • 3

    It can be seen this way, but almost always working with several is also an architectural error. But even if you have, probably as a legacy, to have to access different Dbs, the PDO is not yet useful because each operation will access a bank and you know what it is, you don’t need it. The same problem of exchange applies to a universal system, access would have to be more specialized, there is no escape from it. To tell the truth all use of PDO is by laziness and/ or ignorance.

  • 3

    It’s like people using web GUI to universalize. It’s bad, there are several disadvantages, but people think that this is how they are gaining productivity. Yes, in the short run, in the long run it will be worse. Use of PDO is essentially a Hoax.

  • Got it! The context I have is an information processing system of all other systems in a company. It will collect all data from others and bring specific processed data. The idea of PDO would be to create standard processing functions, where I would have "common queries", but with different databases, so the only thing that would change would be the query, so it would have a function that I just need to pass the query differently, and the rest of the processing would be the same. So I don’t need 1 function for each type of bank. That was the idea, that’s why the question. Thank you!

  • This may be a useful case, but only understanding the case more deeply to affirm. On the other hand maybe this function is exactly what the PDO does, there comes back to make no sense, that is, the PDO is useful when it will create something that the PDO already does, becomes a paradox.

  • But it does not make the "conversion" of syntax, only the execution in several databases. What I’m seeing is that the only thing that changes is the syntax, the rest is standard (connection, bind, etc).

  • 2

    This "only" is also known as "all that matters"

  • Friend @Maniero, allow me not to disagree, but question your answer, since that is the purpose of this site. I believe that in the official PHP documentation it is quite clear what PDO is and isn’t, so the first paragraph of your answer is perfect.

  • @Maniero, over the second paragraph, in the documentation is "PDO does not provide a database abstraction; it doesn’t rewrite SQL or emulate Missing Feature". So if there are people who use it to port the application from one DBMS to another, they are using it the wrong way. In my understanding, the choice for the PDO is because of practicality (not laziness). You can work on one project with Mysql, another with Postgresql, another on Oracle, Sybase, Sqlite, etc., and always use PDO. The long term learning curve is smaller for the programmer.

  • Obviously each DBMS has its own particularities. About security, I believe this should be done in layers, and the use of Prepared Statements add a good layer of security in the application, and PDO has support and you don’t have to learn how they work Prepared statements in different types of connection, learn once. I believe there are other benefits, such as error control through exceptions. Much more convenient to use a Try catch than several if Else.

  • Finally, briefly, I believe that the use of PDO is for these benefits, and not for porting Sgdbs. For those who are learning PHP now, I would encourage its use, its use is quite simple, it does not bring any extra complexity to the project. Performance and stability are excellent. I myself have never come across an error from PDO.

  • 2

    @Paulosakamoto Learning curve is good in the short term. You’re right in what you say, the PDO doesn’t provide what people think it provides )portability), so I say it’s a Hoax. And what he actually provides is not something necessary. It’s a very small facility to justify his adoption, which has a cost. The native resources have everything the PDO has and the right way, the PDO does it wrong. for me exception is the goto of the XXI century, almost nobody knows how to do right, so better stay away from it. And in language of script makes little sense anyway. You can use right, others ñ

Show 7 more comments

1

According to this "Article"

[...] PDO, does not generate SQL commands dynamically, so, no use you use some SQL command specific to SQL Server and then switch to Mysql. In this case you would need to change this command.

Soon the PDO only executes the command given by you, and such command needs to be in the default of the chosen database.

Browser other questions tagged php sql pdo

You are not signed in. Login or sign up in order to post.