Click here to Skip to main content
14,022,159 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
PROBLEM:

I need to perform INNER JOIN between 2 SQL queries, but that query would have to access 2 MySQL databases located on their own servers.

Small example might explain better the problem I face:
select * 
from Table1,        -- Table1 is located on server 72.93.200.11
INNER JOIN Table2   -- Table2 is located on server 109.93.1.219
on Table1.Id = Table2.Id;

RELEVANT INFORMATION:
  • MFC and ODBC are used for database access
  • I haven't used ODBC, nor MFC for database programming before
  • application is a legacy one, so I can not use C++ 11 or newer
  • Visual Studio 2008 is used

If further information is required please leave a comment.

QUESTION:

If using m_strFilter[^] with the second CRecordset is possible, can you instruct me how to do it (again, I have no prior experience with MFC and ODBC)?

I will accept C++ solution as well, but remember that I may not use C++ 11 or newer, since the application is a legacy one.

What I have tried:

This SO post[^] suggest usage of FEDERATED ENGINE.

I am reluctant to use this approach since it has poor performance, according to various comments in the post.

Other option would be to perform both queries separately, and filter recordsets in code.

I have successfully executed separate queries, but m_strFilter accepts fixed string only, not CRecordset.

While writing this question, I am trying to figure out how to bypass the above limitation.
Posted
Updated 20-Feb-19 4:48am

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

You create 2 connections in your program; one to each server.

Query one; then query the other using the results of the first.

Of course, this requires thinking beyond "join all".

SQL Servers support "distributed queries". You can get to MySQL via ODBC from SQL Server.

Linked servers and distributed queries | SQL Bad Practices[^]
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web03 | 2.8.190417.4 | Last Updated 20 Feb 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100