Today I’m very pleased. My UDF lib ‘lib_mysqludf_xql’ has been mentioned in an article by Jon Stephens about ‘Using XML in MySQL 5.1 and 6.0′.
The lib_mysqludf_xql you can query relational data and return XML. It will give you the same functionality as SQL/XML in Oracle and MS SQL. Compares to other methods like using CONCAT or generating XML in a PHP script, this method is much easier and will perform much better.
You can find the library at mysqludf.org, including some documentation. You can also find a bunch of other nice libs there.
Do note that you need to have root privileges on the server to install MySQL UDFs.
Hi
I can’t seem to be able to install your library…
I am running ubuntu 7.10 and mysql 5.0.45
1 – ran apt-get install libxml2-dev
2 – got the library sources, ran gunzipp and tar
3 – ran configure with ./configure –with-mysql=/usr/bin/mysql_config
4 – ran make & make install, then libtool –finish /usr/local/lib/lib_mysqludf_xql
So far all seemed fine but when I now run mysql -u root
(Reply cut in two…)
…So far all seemed fine but when I now run mysql -u root
(please remove last comments)
mysql -u root (less then) installdb.sql
i get
ERROR 1126 (HY000) at line 15: Can’t open shared library ‘lib_mysqludf_xql.so’ (errno: 22 lib_mysqludf_xql.so: cannot open shared object file: No such file or directory)
Any thoughts?
thanks a bunch
Gal
Please ask these questions on the mailing list, not my blog.
You should run: show variables like ‘plugin_dir’;
If that doesn’t give any value do:
./configure –prefix=/usr
make && make install && make installdb
else do
./configure –with-mysql=/usr/bin/mysql_config –with-libxml2=/usr –libdir=[PREFIX_DIR]
Or you can just copy the lib to the correct path.
MySQL isn’t looking for libs in /usr/local/lib.
Good luck
Super great tip.
I was just trying to figure out the best way to do xml extract
Cheers,
Mitch
אזרחות פולנית
Where can I get the Windows version of this lib. thank you.
How do i use it with windows.Please help!!!!! It’s very urgent
!!!!
Hi indhu,
There is a DLL available for windows at http://mysqludf.com/lib_mysqludf_xql. Simply download that and put it into the plugin directory.
Here is my mysql-to-xml over http sample script:
Create index.php in your document-root directory with this minimal content:
And hit url in browser like below examples:
http://url/?q=selct now()
http://url/?q=select * from table-name limit 2
http://url/?show tables
This script is just a small sample using which we can easily query mysql over HTTP and the output as xml. Further to this we can extend this sample script to handle json output and even RESTful operations
Need your suggestion of using this style.
Create index.php in your document-root directory with this minimal content:
php-start-tag
$u = “mysql-read-only-user”;
$p = “mysql-read-only-user-password”;
$db = “database-name”;
$S = “path-of-mysql-socket-file”
$query = stripslashes($_GET["q"]);
if (!$query) {
exit(“NO SQL – Please provide ?q=the-sql-statement in the url”);
}
$cmd = “mysql -u$u -p$p -S$S $db –xml -e ‘$query’”;
$output = passthru(“$cmd”, $stat);
if ($stat != 0) {
header(“HTTP/1.1 500 Internal Server Error”);
exit(“ERROR 500 – Can not process ‘$query’”);
}
php-end-tag
I tried placing the DLL mentioned in my plugin directory, as specified by :
mysql> show variables like ‘%plug%’;
+—————+——————————————+
| Variable_name | Value |
+—————+——————————————+
| plugin_dir | C:\Program Files\MySQL\MySQL Server 5.1\ |
+—————+——————————————+
1 row in set (0.00 sec)
However, I still get the error ERROR 1126 (HY000): Can’t open shared library ‘lib_mysqludf_xql.dll’ (errno: 2 )
I have Visual C++ Express installed as well as the windows SDK. I’m using Windows 7 and mysql 5.1.52-community. Any help would be greatly appreciated as I’m completely stuck.
Thanks!
Nate (Un)fortunately I’m not a Windows user my self, so I don’t know anything about it. The DLL was provided by a contributor. I do know that this DLL isn’t working with 64bit windows.
Probably the best this is to build it yourself:
http://rpbouman.blogspot.com/2007/09/creating-mysql-udfs-with-microsoft.html
We’re looking for a contributor that knows his way around Windows to build all the UDFs. Nate, if your up to it, please let me know.