Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 30 of 30

Displaying posts with tag: xml (reset)

Getting XML and HTML output.
+1 Vote Up -1Vote Down

Many people use MySQL in the console to execute a simple command, but did you know that MySQL can format the result of the query directly to XML or html?

The option -X will output the query result as XML
-H can be used to generate a HTML table containing the data.

$ mysql -X -e "select * from test" test
<?xml version="1.0"?>
<resultset statement="select * from test" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<field name="test">152710</field>

Output as HTML

$ mysql -H -e "select * from test" test
<table border="1">
Linux Documentation Writer Wanted!
Employee +1 Vote Up -0Vote Down

The Oracle Linux and Virtualization Documentation Team is seeking an experienced Technical Writer
with a focus on writing documentation for the Oracle Linux product. (The MySQL Documentation Team is part of that group as well.)

Applicants should be located in either Ireland, the UK, Sweden, Norway,

  [Read more...]
Linux Documentation Writer Wanted!
Employee +0 Vote Up -0Vote Down

The Oracle Linux and Virtualization Documentation Team is seeking an experienced Technical Writer
with a focus on writing documentation for the Oracle Linux product. (The MySQL Documentation Team is part of that group as well.)

Applicants should be located in either Ireland, the UK, Sweden, Norway,

  [Read more...]
Guidelines for generating XML
+0 Vote Up -0Vote Down

Over the last little while I've come across quite a few XML feed generators written in PHP, with varying degrees of 'correctness'. Even though generating XML should be very simple, there's still quite a bit of pitfalls I feel every PHP or (insert your language)-developer should know about.

1. You are better off using an XML library

This is the first and foremost rule. Most people end up generating their xml using simple string concatenation, while there are many dedicated tools out there that really help you generate your own XML.

In PHP land the best example is XMLWriter. It is actually quite easy to use:

  • $xmlWriter = new
  •   [Read more...]
    Breaking news: SHOW INNODB STATUS ported to XML
    +1 Vote Up -1Vote Down

    If you’re like me, you’ve gotten tired of writing endless test cases for parsers that can understand the thousands of variations of text output by SHOW INNODB STATUS. I’ve decided to solve this issue once and for all by patching MySQL and InnoDB to output XML, the universal markup format, so tools can understand and manipulate it easily. Here’s a sample snippet:

    100320 15:46:24 INNODB MONITOR OUTPUT
    ... text omitted, but you get the idea ...

    PS: Yes, this is a late April Fool’s joke.

    Related posts:

  • Don’t forget about SHOW PROFILES It seems t
  •   [Read more...]
    Restoring XML-formatted MySQL dumps
    +1 Vote Up -0Vote Down
    EAVB_VFZUHIARHI To whom it may concern -

    The mysqldump program can be used to make logical database backups. Although the vast majority of people use it to create SQL dumps, it is possible to dump both schema structure and data in XML format. There are a few bugs (#52792, #52793) in this feature, but these are not the topic of this post.

    XML output from mysqldump

    Dumping in XML format is done with the --xml or -X option. In addition, you should use the

      [Read more...]
    Kontrollbase – revision 297 fixes Reporter-CLI “alert_22″ sub-routine
    +0 Vote Up -0Vote Down
    Quick note to let our users know that there was an XML tag closure error on the “alert_22″ subroutine in the “bin/kontroll-reporter-cli.pl” script. This does not affect the webapp portion of Kontrollbase – only reports generated via the command line reporter script. It is not a fatal error but will cause the XML file to […]
    Kontrollbase reporter XML Parser error has been fixed
    +0 Vote Up -0Vote Down
    If you have seen the following error on the Perf Report tab “Message: SimpleXMLElement::__construct()…” – it has been fixed in revision 281. This only affects alerts 11 and 12 so you might not run into it immediately. The solution is to either remove lines “586, 590, 639, 650″ from the bin/kontroll-reporter-5.0.x_linux-x86-2.0.1.pl file, or to run [...]
    RESTful PHP Web Services – reviewed
    +1 Vote Up -0Vote Down

    I’ve been using a lot of RESTful services these days and have been waiting for a good book that is dedicated to the topic. I recently received a copy of ‘RESTful PHP Web Services’, which does a successful job of outlining proven concepts in current web technology. If you want to learn the methods for creating and consuming RESTful services then you will find many examples in this book. From the architectural plans to well thought out code samples, the book covers a lot of ground in a relatively quick read.

    The first chapter gives the reader a quick introduction to RESTful services and the most common PHP frameworks in use at the time of writing. I particularly enjoyed the section on the Zend framework due to

      [Read more...]
    2nd IKS Workshop: The Web 3.0 and Open Source Semantic Search
    +0 Vote Up -0Vote Down

    Rome is a great city and it will host a bunch of great people (including me ) at November 12-13. This is when the second IKS Project workshop will take place. The goal of this workshop is to start working on an Open Source software stack that allows other Open Source projects and software vendors to leverage semantic search technologies.

    IKS is an EU-funded project with an overall budget of 8.5 million Euros. The first workshop back in May saw two dozen of bright Open Source CMS minds discussing a semantic stack in general. This time, it will also make sense for non-CMS-related Open Source projects and vendors to join.


      [Read more...]
    MySQL, PHP, XML = mysql-dba.com
    +0 Vote Up -0Vote Down

    This is a basic heads up post, perhaps even blatant self marketing. So, please continue reading.

    If anyone recalls the website http://mysql-dba.com they would know that it’s based on the planet.py codebase that is written in python. I originally wrote a simple php script that utilized the lastRSS.php class for parsing feeds on the backend for archival purposes to be used at a later date. I say archival and later date because the site itself did not utilize any of the relational data storage to run the site. The site’s python code and cache was updated by cron scripts every 15 minutes and new data was scp’d from my dev server to my webhost’s servers.  This process eventually was quite randomly run since my development server rack in the garage at home gets really hot during the summer months

      [Read more...]
    The ephemeral glory of books
    +0 Vote Up -0Vote Down
    A picture says it all. Special offers at a FrOSCon books booth.
    For how long is an IT book in its prime?
    Improved Cacti monitoring templates for MySQL
    +0 Vote Up -0Vote Down

    Download MySQL Cacti templates

    As promised, I’ve created some improved software for monitoring MySQL via Cacti. I began using the de facto MySQL Cacti templates a while ago, but found some things I needed to improve about them. As time passed, I rewrote everything from scratch. The resulting templates are much improved.

    You can grab the templates by browsing the source repository on the project’s homepage.

    In no particular order, here are some things I improved:

    • Standard polling interval and graph size by default.
    • Full captions on every graph; you don’t have to guess at how big the values are. Each graph has current, max, and
      [Read more...]
    Which MyXML?
    +0 Vote Up -0Vote Down
    Ok. We may all (maybe not really "all") agree that XML in a relational DB is a cool thing. But which kind of approach should we use to store non tabular data in a typical tabular infrastructure?

    Personally I don't like hybrids. The core of a RDBMS is designed to be efficient and with a relatively small footprint. Inflating the core with some features that would probably be helpful to not more than the 30% of the projects (to be optimistic, but this percentage is a good reason to think about XML in a DB), does not justify the extra overhead.
    Unfortunately, an XML storage engine. On the contrary, it would make things worse, because we would translate queries twice - Xquery to SQL then to XQuery again (the big fans of MySQL internals will forgive me for this simplification).

    My interest for a XOR (XML Over Relational) approach goes back to several years ago, mainly because

      [Read more...]
    How To Add A File Extension To vim Syntax Highlighting
    +1 Vote Up -0Vote Down

    Today I was asked a question about defining custom extensions for vim syntax highlighting such that, for example, vim would know that example.lmx is actually of type xml and apply xml syntax highlighting to it. I know vim already automatically does it not just based on extension but by looking for certain strings inside the text, like

    After digging around I found the solution. Add the following to ~/.vimrc (the vim configuration file):

    syntax on
    filetype on
    au BufNewFile,BufRead *.lmx set filetype=xml

    After applying it, my .lmx file is highlighted:

      [Read more...]
    PostgreSQL Conference East 2008
    +0 Vote Up -0Vote Down

    As a I wrote a couple of days ago, I went to the second day of PostgreSQL Conference East 2008 last Sunday. I had a good time and really enjoyed meeting everyone, listening, learning, and occasionally talking. I asked a number of fearless-newbie questions that paid off handsomely: people were very willing to humor me. I also left with a beautiful t-shirt, mug, and bag combo thanks to EnterpriseDB. The bag has already been put to use for a grocery shopping trip.

    Note to conference/website organizers: I can’t link to anything but the front page, so I assume my link above will someday point to the 2009 conference, or the 2008 West conference. It would be good to give

      [Read more...]
    Is it time for MyXML?
    +0 Vote Up -0Vote Down
    Yes, that's correct, it's not a typo. Who needs XML over RDBMS?
    So far, there has been a relatively small group of users who were demanding more XML features within or combined with MySQL. Perhaps it's because XML is not a big deal in the classic LAMP implementation of web-based services, but the more we penetrate the Enterprise market, the more XML is becoming an interesting topic. I am quite sure that we will move from the classic "nice to have" to the typical "commodity feature" in quite a short time, especially if we will improve our presence as the back end DB for J2EE apps and web services.

    Let's be honest, the new nice XML features in 5.1 are still away from a good and proper XML coverage. They are great if we are going to store XML elements within columns of a table, but they are not really helpful if we need to use an XML interface to interact with our DB.

      [Read more...]
    MySQL Adds XML and XPath Support
    +0 Vote Up -0Vote Down

    I was browsing around the web and ran across this article at xml.com, XML Moves to mySQL. Being a heavy XML user, I had to read the article. It looks like MySQL is expanding the built-in support for XML.

    This article isn't very detailed but it links to Using XML in MySQL 5.1 and 6.0 at mySQL.com which is very detailed.

    I like the ability that is built in to support loading XML from files. That's a feature I wish Oracle would work on. Even in 11g, that functionality is still limited.

    MySQL also adds ExtractValue() and UpdateXML() support. If you're manipulating XML much, you know that these two functions are needed.

    From what I know of MySQL and

      [Read more...]
    XML Article on MySQL DevZone
    Employee +0 Vote Up -0Vote Down

    I’ve written an article highlighting some recent developments with XML support in MySQL 5.1 and MySQL 6.0. Topics include:

    • Outputting MySQL data in XML format; includes a look at the 3rd-party lib_mysqludf_xql library
    • Getting XML into MySQL; includes a discussion of the LOAD XML statement (new in MySQL 6.0)
    • The ExtractValue() and UpdateXML() functions (new in MySQL 5.1)
    • Security issues, including a look at a little nasty known as “XPath injection”

    You can read it at the MySQL Developer Zone.

    Alexander Barkov contributed a nifty stored procedure and a very helpful pre-publication review. Thanks, Bar!

    Web enabling MySQL
    +0 Vote Up -0Vote Down
    Mark Atwood has published an interesting post with his wishlist for MySQL. One of his points is already (partly) covered with the LOAD XML command I made for MySQL 6.0 (or is it 5.2).

    The main problem with it is perhaps that it works (like the LOAD DATA command) only with files, but in a web world you want it to be able to take input from other sources. You also need to be able to send an XML to several tables, with foreign keys coordinated etc.

    Mark also has a wishlist for the opposite, the SELECT OUTFILE command. I have made a couple of stored procedures to help with this, but an extended SELECT OUTFILE would be mucke aesier to use and give much more possibilities. It would be fun to work on this, we'll see if I have the time, now that I am almost ready with my relocation..
    Oracle 11g: New Pivot Table Feature
    +0 Vote Up -0Vote Down

    I was very pleased when I heard about Oracle adding pivot functionality in select statements. Finally — we wouldn’t have to copy the data to a spreadsheet or code a ton of sum(case when col1 = 'X' then amount else 0 end) total_X for each column we would want to display.

    I am basically looking for three things in a pivot-style query:

  • the ability to specify which column will be pivoted as one or more columns in the resulting query
  • row subtotals
  • column subtotals
  • The first item is the only one that really matters. I can work around the other two, so let’s get started.

    Create a test table with a few rows:

    create table bob_pivot_test( id number, cost number, tx_date date, product_type varchar2(50));
    insert into bob_pivot_test values (1,10.99,sysdate-10,'Toy');
    insert into bob_pivot_test values
      [Read more...]
    DrupalCampLA (Free) - come see me speak on Flex & Drupal
    +0 Vote Up -0Vote Down

    I'll be speaking on using Drupal with Adobe technologies such as Flex, AIR, and Flash. The event is totally free but you must register.

    DrupalCampLA - http://upcoming.yahoo.com/event/221082/

    Follow up on LOAD XML
    +0 Vote Up -0Vote Down
    A few weeks ago (time flies..) i posted a short note about my LOAD XML contribution. It has actually been available for some time, veven if it is not so easy to find. It is included in MySql 5.2.5:
    documentation - quite good I think!

    It is a bit sad that it is included in 5.2 (still in alfa) and not in 5.1. Since it is a separate command it will not affect existing installations and would not be risky. That faster it gets included the faster we will get feedback on it, which is what I believe is needed right now. Is this something that MySql users need and will use? Or will they use other tools to read XML data into their MySql databases?

    LOAD XML reads data into a MySql table in much the same way as LOAD

      [Read more...]
    LOAD XML contribution added to MySQL
    +0 Vote Up -0Vote Down
    I got news from the MySQL people that my LOAD XML contribution is added to release 5.2. Check the manual page.

    The aim of the contribution is to simplify reading of XML data into a MySQL database. The LOAD XML command supports three different xml formats:
    - field values as attributes
    - field values as tags
    - the format produced by the mysql -x command, with the field name as an attribute and field value as a tag

    If the LOAD XML command finds a matching field in the target table, the value is inserted, otherwise it is ignored. If you have used the LOAD DATA command (most of us have) you should recognize much of the functionality, LOAD XML works much the same way.
    More on XML output from MySql
    +0 Vote Up -0Vote Down
    In a previous post I described how you could create XML output from SQL using stored functions. But that was a flat structure, with only one table and simple XML structure with a tag for every row in the result set, and column values as attributes or sub tags. Part of the real power of the XML format is that it supports more complicated structures, with hierarchical data. If you need to produce XML for integration with another application you will probably have to produce a XML with data from several tables, and a hierarchical structure. To accomplish this in MySQL we can use the GROUP_CONCAT function, combined with our XML stored functions.

    An example:
    We take our select statement from the previous post, that gives us a list of tables in xml format:

    select xml_tag('table',null, concat(xml_attr('name',t.table_name),

      [Read more...]
    Comments on MySql XML output
    +0 Vote Up -0Vote Down
    I have had some comments on my stored functions for XML output from MySql. First of all, publishning the code as a part of a blog post was not a good choice. All xml chars, like < and > must be escaped, and I missed some. I should probably have used the xml_escape function to precess my text...

    That is fixed now, and also I have put the function definitions on the MySql forge, here. Please do not hesitate to tell me if you find any errors, or have suggestions for improvement.

    Another comment was about the xml functionality of 'mysql --xml'. Yes, there is a function for generating xml output from MySql, I do know that. But if you need to output xml from your application there are a number of problems with it:
    1) it has to be invoked from the command line, so you need to start a command

      [Read more...]
    XML output from MySql
    +0 Vote Up -0Vote Down
    Many times you need to produce XML output from your MySql database. MySql has no built-in support for creating XML in it's SQL implementation but you can easily add support using the stored function feature.

    To make xml output easier I have made three small stored SQL functions. They produce xml fragments, which you can easily put together to get a complete xml:

    xml_escape(value) replace characters not allowed in xml with the escape sequences
    xml_attr(name, value) create an xml attribute
    xml_tag(tagname, tagvalue, attrs, subtags) create a tag, optionally with

    Lets see some examples. First a real simple one:

    select xml_tag('table',table_name,null,null)
    from information_schema.tables
    where table_schema = 'INFORMATION_SCHEMA'

    Gives you something

      [Read more...]
    XPath Variables in MySQL 5.1.20
    Employee +0 Vote Up -0Vote Down

    A few days ago, Alexander Barkov pushed some changes to the MySQL 5.1 tree that I’ve been waiting to see for some time — variable support for XPath functions used with ExtractValue() and UpdateXML(). (This was a fix for Bug #26518, BTW.) This will be available in MySQL 5.1.20 (or grab the MySQL 5.1 source from bkbits and build it yourself, if you just can’t wait).

    Two slightly different notations are supported, depending on the context, and what sort of checking you want done on the values:

  • If you don’t want or need type checking, prefix the variable name with $@, like this: $@myvar. However, if you do this,
  •   [Read more...]
    LOAD XML submitted
    +0 Vote Up -0Vote Down
    I have been working on an addition to MySQL the latest month's or so, adding XML import to MySQL. This weekend I decided that the version was good enough to submit to MySQL. You can find it at http://lists.mysql.com/internals/34169. Good enough to submit yes, probably not good enough for production, but feedback would be good.

    But all in all I am quite satisfied with it.
    - focus is on easy-of-use, with automatic matching of tags and attributes to field names
    - support for three different xml formats: using tags for field values, using attributes or the format used by oth MySQL tools (not a good format, but anyway..)
    - works very much like th CSV file load that has been a feature of MySQL for very long

    I can think of some additions, like support for binary fields (probably does not work today), better handling of character encoding etc, improved

      [Read more...]
    AJAX Tutorial with Prototype
    +0 Vote Up -0Vote Down

    Here's the AJAX prototype example that I used in my AJAX presentation today.

    I wanted to give an example of a good use of AJAX, and at the same time keep it simple. So I thought a good example would be to build a zip code verifier. As soon as the person enters the zip code it makes a request to the server to see if the zip code is in the database, and returns the city and state.

    So the user first sees this:

    Once the zip code is entered, and the response received it looks like this:

    If the zip code is not found in the db:

    Ok, now lets build it...

    Download prototype, and a zip  [Read more...]

    Showing entries 1 to 30 of 30

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.