Devart Forums

Hello

A simple query below, the connection is set for the interchange2013 db table but how do I get the query to recognise the db WAREHOUSE - I'm obviously missing a trick here?

Thanks

SELECT
interchange2013.Part,
interchange2013.Int_Company,
inv_lasermark.laser,
FROM ODMEU.interchange2013
LEFT OUTER JOIN WAREHOUSE.inv_lasermark
ON interchange2013.Part = inv_lasermark.part
WHERE interchange2013.Part = :part

Hello,

You can use aliase like this : The database you mention in the connection object is the default database, that is the one that is used when you haven't reference it.

Both databases must reside on the same server. If not, you have to use the FEDERATED storage engine.

Regards.

Thanks for that but I'm still missing a trick! My query runs without issue in Forge but not in a Delphi app at runtime. I am refused connection at the first field on the second db #42000 command denied to user warehouse'@' auser.com for table interchange2013. Both db's are on the same server.

The 'YourUserName'@'YourComputerName' message is a server response to an authentication fail. This message means that no user with the 'YourUserName' name exists or you have entered an invalid password, or a user with the 'YourUserName' name has no server access rights from the 'YourComputerName' PC.
To solve the problem, you should add the necessary rights to the YourUserName account or create a user with the necessary rights. The needed information can be found in the MySQL reference manual (e.g.: http://dev.mysql.com/doc/refman/5.5/en/grant.html).

I can run the query within Forge without an issue, as soon as I paste it into a query component I get the error

ODMEU.ip_application.batch_num,
ODMEU.ip_application.ERP_Company,
WAREHOUSE.inv_lasermark.laser,
WAREHOUSE.inv_odm901.supplier_acc,

The error appears to be that the query is trying to find inv_lasermark in ODMEU
(command denied to user 'opapp' inv_lasermark) which is ODMEU when the table resides in WAREHOUSE

I am using Securebridge but, like I say, it works in Forge so I am missing something, it will be something stupid I'm sure!

Thanks

Please provide the exact full error message you receive. Also, make sure you use the same credentials and connection settings in both dbForge and MyDAC.
In order to get a detailed answer, please compose a small sample that demonstrates the described behavior and, if possible, provides test access to your database, and send it using the contact form https://www.devart.com/company/contactform.html including scripts for creating database objects.

OK, I will do that when I get chance, I have "fixed" the problem by replication of the tables I need for the time being.
Thanks

When you get any results concerning this question, please let us know.

Hi

I have returned to this issue and I'm sure it is a lack of knowledge on my part. How do I get a single query to recognise it needs to look at more than one connection during query execution? Whenever I drop a query onto a form it insists on a connection to connect with, that connection then has a problem when looking for an alternative database (on the same server) due to the password/user combination not being correct. As I do not have this problem with the same query in dbForge I can only assume there is something I haven't discovered for this type of query?

All the best.

As we wrote earlier, in order to get a detailed answer, please compose a small sample that demonstrates the described behavior and, if possible, provides test access to your database, and send it using the contact form https://www.devart.com/company/contactform.html including scripts for creating database objects.

Hello

I did send a test project in but not had an acknowledgement yet. Maybe you could just tell me what I am doing wrong, supply a code snippet, tell me if it's a bug or VCL cannot achieve what I am trying to do? It would really help me.

Regards, Ian

Unfortunately, we have not received a letter from you with the example, demonstrating the issue. In order to get a detailed answer, please compose a small sample that demonstrates the described behavior and, if possible, provides test access to your database and send it to support*devart*com including scripts for creating database objects.

I submitted a project on the form you referred me to a few days ago! Please just provide a simple example project that connects to two db's via one query, it doesn't even have to work but I can then see what I am doing wrong.

Regards, Ian

We have received your sample and and investigation is in progress. We will notify you about the results as any are available.

While investigating your example, we detected that each of three users has access only to his database. Therefore you get an error about the lack of rights, when running the "USE DBName" line under a logged-user, who does not have privileges to DBName. If you comment all 3 calls of the above line, there is an error about the rights absence for executing the select command to a table in the database, which the user does not have the rights to. This behavior is the same when using the MyDAC and dbForge Studio for MySQL.
This behavior is connected with MySQL functionality particularity, but not MyDAC and there's no way we can affect it. You can verify this by executing an appropriate query using the standard means, for example, MySQL Workbench.
To solve the issue, please use the following guidelines:
- delete in your example the lines "USE DBName;"
- give the user the mpe-app rights to execute the select command for the tables, present in the query from the ODMEU and WAREHOUSE databases.