Обсуждение: Reading PG data from MySQL stored procedure
Here's a real long shot, but what the heck...
I have a user that's using a system that gives him a single MySQL DB handle to work with. He must get all his data through this handle. He wants some of my PG based data. Not only can't he open a new DB handle to my PG DB, but he cannot even run something at the system level (otherwise I'd just give him a "psql -P pager=off <db> -c "select foo from bar" "). So it has to come "through" MySQL somehow, MySQL V5.1 no less.
It appears that they have some sort of external language capability for stored procedures in their V6. And they have something else which'll run an external object (UDF something or other). But what I'm throwing out there is a question regarding any known data interchange functionality that might exist between these 2 different SQL DB engines.
As I said, a real long shot.
Thanks for any comments (even if it's NO :-) )
On Thu, Oct 20, 2011 at 2:22 PM, Gauthier, Dave <dave.gauthier@intel.com> wrote: > Here's a real long shot, but what the heck... > > > > I have a user that's using a system that gives him a single MySQL DB handle > to work with. He must get all his data through this handle. He wants some > of my PG based data. Not only can't he open a new DB handle to my PG DB, > but he cannot even run something at the system level (otherwise I'd just > give him a "psql -P pager=off <db> -c "select foo from bar" "). So it has to > come "through" MySQL somehow, MySQL V5.1 no less. > > > > It appears that they have some sort of external language capability for > stored procedures in their V6. And they have something else which'll run an > external object (UDF something or other). But what I'm throwing out there > is a question regarding any known data interchange functionality that might > exist between these 2 different SQL DB engines. First question: why in the world can't you do the processing on the client side? There are gajillion and one data transfer/ETL tools! mysql of that has pretty much zero server side extensiblity AFAIK. Of course, it would be quite possible from postgres to query mysql database, but I digress... merlin
The software system they are being forced to use gives them the ability to send queries to a MySQL which has already beenconnected to. However, they do have the authority to add things to that DB, like stored procedures. This user isn'tcoding anything per-se, they're just using the interface provided. But they can "call" a stored procedure/functionbecause that's ligit sql. The data that flows from that goes into other parts of the system for reporting,etc... . -----Original Message----- From: Merlin Moncure [mailto:mmoncure@gmail.com] Sent: Thursday, October 20, 2011 3:33 PM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Reading PG data from MySQL stored procedure On Thu, Oct 20, 2011 at 2:22 PM, Gauthier, Dave <dave.gauthier@intel.com> wrote: > Here's a real long shot, but what the heck... > > > > I have a user that's using a system that gives him a single MySQL DB handle > to work with. He must get all his data through this handle. He wants some > of my PG based data. Not only can't he open a new DB handle to my PG DB, > but he cannot even run something at the system level (otherwise I'd just > give him a "psql -P pager=off <db> -c "select foo from bar" "). So it has to > come "through" MySQL somehow, MySQL V5.1 no less. > > > > It appears that they have some sort of external language capability for > stored procedures in their V6. And they have something else which'll run an > external object (UDF something or other). But what I'm throwing out there > is a question regarding any known data interchange functionality that might > exist between these 2 different SQL DB engines. First question: why in the world can't you do the processing on the client side? There are gajillion and one data transfer/ETL tools! mysql of that has pretty much zero server side extensiblity AFAIK. Of course, it would be quite possible from postgres to query mysql database, but I digress... merlin
On 10/21/2011 03:56 AM, Gauthier, Dave wrote: > The software system they are being forced to use gives them the ability to send queries to a MySQL which has already beenconnected to. However, they do have the authority to add things to that DB, like stored procedures. This user isn'tcoding anything per-se, they're just using the interface provided. But they can "call" a stored procedure/functionbecause that's ligit sql. The data that flows from that goes into other parts of the system for reporting,etc... . AFAIK, the only way you'd be able to get from MySQL to Pg directly would be to install a user-defined function written in C that used libpq to connect to Pg. You can't do that over a basic connection to MySQL, you need the ability to install binaries on the server. The system is too locked down to permit what you want to do on the MySQL end. You'd have to make a connection to Pg from the client side, extract the data you wanted and send it down the connection "handle" for MySQL that you already have. There's a bit too much hand-waving and not enough specifics about language, environment, etc to say anything more. Is this some kind of report-writing system? A RAD environment? What? -- Craig Ringer
A big part of thisinquiry has to do with feasibility. Another option is to approach those who control that system whichthe user has to use and get them to be more accommodating with regard to attaching to more than one DB and realizingthat there are other DB engines than MySQL. Armed with the info you guys have given me, I will propose to them that they ask their app provider the means to attach tomultiple ODBC served DBs (this thing is a Windows based app). That way they can use MySQL and PG and whatever else theymight have to deal with in the future. Many thanks for the input. As I said, it was a long shot. So I got what I expected. At least now I can tell them that Iam not alone is my opinion that getting PG date througn MySQL is a bad idea. Thanks Again! -----Original Message----- From: Craig Ringer [mailto:ringerc@ringerc.id.au] Sent: Thursday, October 20, 2011 10:23 PM To: Gauthier, Dave Cc: Merlin Moncure; pgsql-general@postgresql.org Subject: Re: [GENERAL] Reading PG data from MySQL stored procedure On 10/21/2011 03:56 AM, Gauthier, Dave wrote: > The software system they are being forced to use gives them the ability to send queries to a MySQL which has already beenconnected to. However, they do have the authority to add things to that DB, like stored procedures. This user isn'tcoding anything per-se, they're just using the interface provided. But they can "call" a stored procedure/functionbecause that's ligit sql. The data that flows from that goes into other parts of the system for reporting,etc... . AFAIK, the only way you'd be able to get from MySQL to Pg directly would be to install a user-defined function written in C that used libpq to connect to Pg. You can't do that over a basic connection to MySQL, you need the ability to install binaries on the server. The system is too locked down to permit what you want to do on the MySQL end. You'd have to make a connection to Pg from the client side, extract the data you wanted and send it down the connection "handle" for MySQL that you already have. There's a bit too much hand-waving and not enough specifics about language, environment, etc to say anything more. Is this some kind of report-writing system? A RAD environment? What? -- Craig Ringer
On 10/21/2011 11:21 AM, Gauthier, Dave wrote: > A big part of thisinquiry has to do with feasibility. Another option is to approach those who control that system whichthe user has to use and get them to be more accommodating with regard to attaching to more than one DB and realizingthat there are other DB engines than MySQL. > > Armed with the info you guys have given me, I will propose to them that they ask their app provider the means to attachto multiple ODBC served DBs (this thing is a Windows based app). That way they can use MySQL and PG and whatever elsethey might have to deal with in the future. > > Many thanks for the input. As I said, it was a long shot. So I got what I expected. At least now I can tell them thatI am not alone is my opinion that getting PG date througn MySQL is a bad idea. It's not necessarily a bad idea, it's just not going to be _possible_ with the constraints on their use of MySQL and their limited access to the system. It's really handy to be able to fetch data from one DB via another; that's what dblink and dbilink in PostgreSQL are for. MySQL has various foreign data engines for the same purpose. If the DBs of interest are co-located it makes a LOT more sense to get one to pull data from the other rather than sending it from DBa to a remote client that then sends it back to DBb. You just can't do that, because you don't seem to have any kind of useful access to the system the DB runs on. -- Craig Ringer
Craig Ringer <ringerc@ringerc.id.au> wrote: On 10/21/2011 03:56 AM, Gauthier, Dave wrote: > The software system they are being forced to use gives them the ability to send queries to a MySQL which has already beenconnected to. However, they do have the authority to add things to that DB, like stored procedures. This user isn'tcoding anything per-se, they're just using the interface provided. But they can "call" a stored procedure/functionbecause that's ligit sql. The data that flows from that goes into other parts of the system for reporting,etc... . AFAIK, the only way you'd be able to get from MySQL to Pg directly would be to install a user-defined function written in C that used libpq to connect to Pg. You can't do that over a basic connection to MySQL, you need the ability to install binaries on the server. The system is too locked down to permit what you want to do on the MySQL end. You'd have to make a connection to Pg from the client side, extract the data you wanted and send it down the connection "handle" for MySQL that you already have. There's a bit too much hand-waving and not enough specifics about language, environment, etc to say anything more. Is this some kind of report-writing system? A RAD environment? What? -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general