span databases with query
span databases with query
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
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
Re: span databases with query
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.
You can use aliase like this :
Code: Select all
SELECT
i1.Part,
i1.Int_Company,
i2.laser,
FROM ODMEU.interchange2013 i1
LEFT OUTER JOIN WAREHOUSE.inv_lasermark i2
ON (i1.Part = i2.part)
WHERE i1.Part = :part
Both databases must reside on the same server. If not, you have to use the FEDERATED storage engine.
Regards.
Re: span databases with query
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.
Re: span databases with query
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).
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).
Re: span databases with query
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
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
Re: span databases with query
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.
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.
Re: span databases with query
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
Thanks
Re: span databases with query
When you get any results concerning this question, please let us know.
Re: span databases with query
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.
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.
Re: span databases with query
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.
Re: span databases with query
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
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
Re: span databases with query
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.
Re: span databases with query
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
Regards, Ian
Re: span databases with query
We have received your sample and and investigation is in progress. We will notify you about the results as any are available.
Re: span databases with query
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.
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.