PDO does it use syntax of some DBMS or do they all work?

Question

PDO does it use syntax of some DBMS or do they all work?

Navigation

#1 by (4 votes)
#2 by (1 votes)

3

When considering PDO usage , one of the key usability is the scope of multiple databases .

Example scenario

Different ways to filter 5 records, which vary depending on the 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

Questions

Does PDO have a "pattern" more like some particular bank?
- When I use PDO, can I use any of the forms above that will "convert" to the bank?
- When I "change" my database, would I have to change all queries, or does the PDO make this automatic?

php sql pdo

asked by anonymous 03.10.2018 / 13:35

2 answers

1

According to this "Article"

[...] PDO, does not generate SQL statements dynamically, so it's no use you use some SQL Server-specific SQL command, change to MySQL. In this case it would be necessary to change this command.

Soon the PDO just executes the command given by you, and that command must be in the pattern of the chosen database.

03.10.2018 / 13:51

How to filter time in a datime field in eloquent? Select higher PHP value

score 4 · Accepted Answer

TL; DR

You have to rewrite all the queries .

Detailing

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

One of the reasons people use PDO is to be able to switch databases one day. And this is already a fallacy. People do not do this. They create a complication for something that has almost zero chance of happening. And if it happens the cost of adaptation is absurdly greater than access to the API. And if it is the database the problem is likely to have to trade other things. Wrong decisions almost never come alone. In many cases you have to switch to the language. Apart from the extreme cases it is a matter of proper tuning of the DB rather than swapping it. There are examples of huge sites using all DBs, even SQLite, so trading the DB will rarely be the solution, and if you really need it, it's such a radical change in your site profile that changing everything will not be a problem.

But what most people do not see is that the SQL commands to be used, and even the database modeling must be different according to the SGDB chosen. The worst thing you can do is to use a template thinking for MySQL and switch to Oracle, and use the queires of each other, because even though it works they will have different compromises and the result will be worse.

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

You can not say that you will have to change all queries , but if you are lucky you will. Yes, luck, because it will make you rethink the whole system and this will be very good. Azar thought he made his life easier.

And I did not even mention when changing persistence is not just switching from MySQL to SQL Server, but to any data model that does not even keep the relational. If the DB's decision was wrong it's likely to only fix it in real-world mode.

Another reason people choose PDO is security. That's all misinformation. There is no reason for him to be safer. Indirectly it can be even more insecure.

I've seen people saying that it's faster, it's rare, it's true. There is no way for an extra bed to be faster than the original.

So almost always the choice for its use is wrong. I am not saying that there is no case to use, but it is very rare that it is necessary and useful.

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

As far as I know, there is nothing that can do the abstraction that you want, and even if it is impossible to have something with a good result. Already tried in other languages, very capable teams did everything, evolved, and still catches more or less well a portion of the cases, the rest serves poorly.