Solved

Pass through queries in MySQL?

Posted on 2004-09-30
6
280 Views
Last Modified: 2008-03-03
Is there anything conceptually like a Pass-Through (access) query in MySQL?

If not does anyone know of a way to replicate tables from an external ODBC source into MySQL?

Geoff
0
Comment
Question by:gdrnec
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 26

Expert Comment

by:ushastry
ID: 12197477
0
 
LVL 4

Author Comment

by:gdrnec
ID: 12198881
That's close but I can already do table exports from access to MySQL.

What I actually need is a near real time replication from a non access ODBC source. The source is MAS200.

Any other ideas?
0
 
LVL 7

Expert Comment

by:petoskey-001
ID: 12199342
MySql does not have the concept of pass-through queries.  The MySql client can only be used to access MySql servers.  ODBC access is not built into the clients, but is instead a separate layer on top of MySql.

As far as replicating tables from ODBC into MySql, Embarcadero has some nice products for cross-database development.
http://www.embarcadero.com/products/products.html

It depends on how automated you want everything to be.  Replication involves lots of checking for what's changed and if the source database doesn't include some sort of replication tables that say "tableX rows Y though Z have changed", then it's going to be a pretty slow procedure.  You may just want to roll your own using PERL, PHP, or some other simple language.

You say you need near real time access though.  Why do you want to use MySql instead of using the original ODBC as your data source?  PHP can use ODBC, as can most other programs.  What language are you developing in, and why is MySql needed?  

I'm trying to see the big picture.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 4

Author Comment

by:gdrnec
ID: 12200080
The BIG Picture:

I have written a java program for mobile sales (pocketPC). This app communicates over the internet and synchronizes with our database at base. At present the database is in access and is very slow so I have been moving over to MySQL to get some cross vpn performance.

Here is the problem. Much of the data is resident in an accounting system called MAS200. Although MAS200 has an ODBC driver, it is so slow as to make it unusable. Up to this point I have been importing the relavent tables from MAS200 into access (which is slightly faster than the aforementioned ODBC connection).

Although this is still doable from MySQL (I can continue to import the data into MySQL from MAS200 using Access as a conduit), I really need a more elegant solution.

I may just have to write a simple replication service myself as you point out but MAS200 doesn't have any audit tables to key on so I am left with full table imports and that could be problematic. The only easy part is that it is a read only source and therefore only replicates one way (thank goodness).

I was maybe thinking about using a file image of the original MAS200 tables (kind of a btreive idea) to test for changes.....

Hoping for a solution....

Geoff





0
 
LVL 7

Accepted Solution

by:
petoskey-001 earned 125 total points
ID: 12201266
I don't suppose there is any chance you could upgrade to MAS200 SQL?  It uses a MS SQL server and should make the replication you want to do a lot easier, and probably get rid of the need for MySql.  Plus for pocketPC you can use MsSql Server for CE with the built-in replication.  It requires that you setup an IIS server between your DB and handheld, but it works well for basic replication.

Failing that I would write an Access application with VB script to perform this data import at startup and then set it up to run automatically every hour or so.  
0
 
LVL 4

Author Comment

by:gdrnec
ID: 12201706
Well,

Unfortunately, I have heard through the grapevine that Best (makers of MAS200) are considering no longer supporting and devloping for MS SQL server so I can't justify it.

I guess I wanted a miracle but failing that... Thanks for your suggestions. I better get writing.

Geoff
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Whether to use true/false, yes/no or 0/1 11 82
Insert values are dynamic 11 60
MySQL 5.6.30 - daily outages 46 61
Get data from two MySQL tables 6 22
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question