I'm not sure I follow you here - core language?
Core language, meaning the language that powers the majority of the application in this case, PHP.
far as I can see, the easiest and best way to avoid code duplication there is to use SPs. Sure there's a couple of other ways (which don't make as much sense), like having the Java app and PHP app call the same PHP script which returns the query result (this probably has a swanky name/acronym which I don't know).
RESTful web services.
This would decrease the performance of both apps (especially the Java app if the PHP server and DB server are on separate machines) - so it's clearly better to use SPs.
It does introduce a slight performance hit, at the cost of greater flexibility and reuse. This argument is similar to those by procedural developers who refuse to switch to OOP because they fail to see the additional asbtraction providing any benefit.
If you use a web service you:
1. Decouple from a specific data store (SQL, XML, third party web service, etc)
2. Decouple from the SQL specifics (SELECT is different in MySQL than MSSQL)
3. Increase security
Yes it is another asbtraction layer and yes it comes at the cost of performance, but you further decouple the design, make maintenance a little easier and maybe even improve security. If you provide direct access to a remote MySQL server so that localhost can connect and some desktop application, you increase your chance of having an exploit.
That being said, the web service itself might still use a stored procedure itself and improperly sanitizing the incoming data could still result in an exploit, but allowing third party application (other than localhost) connect to a remote server requires a skilled system admin to make sure everything goes as expected. You lessen the responsibility by using a web service, now your server doesn't need port 3036 open it just keeps on working with port 80 open, or maybe 443 (or whatever SSL listens on).
A web service may be not ALWAYS ON because of various reasons - hardware issues for example.
Your more experienced than me when it comes to system admin, and your experience may be different, but in my experience Apache is one of the most reliable services.
While if the DB fails, all application will fail, that's not true if a web service failure occurs for applications that have direct DB access, is it?
If apache fails, your web services are finished, yes. But if MySQL fails your web services are finished also, because they usually rely on MySQL as well. The client developer (whether extJS or C# or whatever) should be checking for connection activity and responding appropriately on failure, whether the results are a MySQL connection object or null or a empty JSON object or XML, the client is responsible for effective error handling and notification, IMO.
That's why I said "REST is another point of failure introduced".
I understood that much, I just don't agree 100%.

Yes its another point of failure, but realisitically what goes down more often, RDBMS or Apache? Secondly, if the uptime is mission critical, setting up a secondary server in either case should be considered an option, no?
Technically speaking, it's not really another failure point, as Apache is already installed and running under 99% of circumstances, it's just shifting the responsibility exclusively from the RDBMS and sharing the responsibility with the Apache server as well, IMO.
It is not like you need to install and setup yet another system/daemon, such as the case for some servers.
In my particular project, if the web site is down, users can still make orders by using the callcenter service (the desktop application with a direct DB access) ...
And what happens when the DB server goes down? I supose you could cluster your DB's much like you could probably load balance multiple Apache servers??? If this is the case, this is certainly more robust a solution than relying on the DB not to fail over the Apache server or visa-versa.
I see what your saying though:
1. (Your Setup) if Apache goes down users can order through Desktop application as it connects directly to Apache
2. (REST Setup) if Apache goes down users can no longer order via desktop or web site
What I am saying is that even in your case, if the DB goes down, users can no longer order from the desktop or the web, the DB is down. So whether REST is used and Apache fails or direct connection is used and MySQL fails, the end users are stuck without service, so it would make sense to begin looking into redundant system, which is outside the scope of this conversation, unless you want to discuss that I'm listening, probalby best to start a new thread though.
I thought, such APIs are some kind of libraries, not applications.
I wouldn`t call it a library, per se, but definetely an API not so much an application. Just an API that allows anyone to manipulate a remote data store. Adding users, deleting users, etc. How you reneder the results is strictly dependent on the application consuming the results, such as extJS or C# or iPhone, etc.
It can also be said for pure SQL - different RDBMS have different SQL standard implementations... So, I don't think it's a good argument...
Well not in that context, no, but that is not what I am arguing. I am saying that RESTful services remove those requirements. If you can decouple the web application and the desktop application and your iPhone application from MySQL or MSSQL wouldnèt that be an ideal. The the web services layer developers only ever need to worry about SQL specifics, whether they use stored procs or not, etc.
The web application typically uses the model explicitly to avoid unnessecary performance hits, but outside applications would simply invoke the RESTful API and never worry about the specifics of data storage.
So, I see you agree with my previous argument
When I referred to the "DB abstraction layer" and "SP" I meant that only SP calls and results returning should be unified.
I think (as in most cases) we are probably very close to sharing the same understanding, only I would prefer to see a RESTful API services layer used by distinct applications, as opposed to each application explicitly connecting to the SQL DB server. Yes performance hit, but that is always the cost for greater flexibility and more abstraction.
I have some more things to say, but I have to go now to be continued ...
Sounds good, I look forward to it
Cheers,
Alex