mysql> create function xml_get_state(id int) returns text
-> no sql language xmlrpc external name
-> 'xmlrpc://betty.userland.com/RPC2;examples.getStateName';
Query OK, 0 rows affected (0.00 sec)
mysql> select xml_get_state(40);
+-------------------+
| xml_get_state(40) |
+-------------------+
| South Carolina |
+-------------------+
1 row in set (0.42 sec)
Now I just need to check that it works for more complex resultsets... Trawl the web for open-access trivial XML-RPC services or build a test XML-RPC server?
4 comments:
Anthony, this is GREAT! I love it!
Would it be possible to do the same thing to fetch XML and JSON documents over plain HTTP or FTP - i.o.w. no special protocol?
And...can you please put this stuff in 5.2? ;)
kind regards,
Roland
Hi Roland,
It is trivial to create a simple plug-in which will simply fetch a document from the web and return it as a function value.
Best route would probably involve using a library like libcurl to handle the wire protocol.
Feel free to download the code and experiment,
Regards,
Antony
that is pretty neat.
it'd be great if it used curl - so it could handle nearly any protocol. hopefully simple things like REST.
it would be neat if you could tell it what format to expect in return as well.
SELECT foo FROM function_name(40) USING JSON or something? I don't know. I hate XML-RPC, SOAP, etc. I like REST+JSON or REST+XML. Having it come back in a row/column fashion and be fully capable of ORDER, LIMIT and other SELECT operations would be totally cool too.
I'm still not quite sure how much I would use this though. It winds up making MySQL more than the database server but also the entire data source, which could be putting too many eggs in one basket.
I'd love to see MySQL cluster/replication/proxy continue to become a coherent shared-nothing architecture solution that has zero latency, always consistent, load balanced, redundant, scalable views of the data (and any other buzzwords)
Hi Mike,
I am using the xmlrpc-c library which may use libwww or libcurl. Which one depends purely upon the options used to compile the xmlrpc-c library.
What you're looking for are Table functions - currently not yet supported by MySQL but their syntax looks a bit like:
SELECT * FROM TABLE(function_name(40)) AS foo
As to when such a thing would be supported, I don't know. I'll look into it.
Regards
Antony.
Post a Comment