span databases with query

Discussion of open issues, suggestions and bugs regarding MyDAC (Data Access Components for MySQL) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
spinworm
Posts: 7
Joined: Fri 14 Oct 2016 10:04

span databases with query

Post by spinworm » Fri 14 Oct 2016 10:19

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

zeltron73
Posts: 20
Joined: Mon 04 Jun 2012 07:46

Re: span databases with query

Post by zeltron73 » Sat 15 Oct 2016 12:47

Hello,

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
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.

spinworm
Posts: 7
Joined: Fri 14 Oct 2016 10:04

Re: span databases with query

Post by spinworm » Mon 17 Oct 2016 09:57

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.

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: span databases with query

Post by ViktorV » Mon 17 Oct 2016 10:48

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).

spinworm
Posts: 7
Joined: Fri 14 Oct 2016 10:04

Re: span databases with query

Post by spinworm » Mon 17 Oct 2016 15:46

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

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: span databases with query

Post by ViktorV » Tue 18 Oct 2016 08:34

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.

spinworm
Posts: 7
Joined: Fri 14 Oct 2016 10:04

Re: span databases with query

Post by spinworm » Wed 19 Oct 2016 11:11

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

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: span databases with query

Post by ViktorV » Mon 24 Oct 2016 09:17

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

spinworm
Posts: 7
Joined: Fri 14 Oct 2016 10:04

Re: span databases with query

Post by spinworm » Mon 31 Oct 2016 09:28

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.

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: span databases with query

Post by ViktorV » Mon 31 Oct 2016 10:50

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.

spinworm
Posts: 7
Joined: Fri 14 Oct 2016 10:04

Re: span databases with query

Post by spinworm » Wed 02 Nov 2016 08:13

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

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: span databases with query

Post by ViktorV » Wed 02 Nov 2016 09:59

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.

spinworm
Posts: 7
Joined: Fri 14 Oct 2016 10:04

Re: span databases with query

Post by spinworm » Wed 02 Nov 2016 10:17

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

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: span databases with query

Post by ViktorV » Wed 02 Nov 2016 15:04

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

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: span databases with query

Post by ViktorV » Thu 03 Nov 2016 14:24

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.

Post Reply