Getting data out of MySQL

Paul McCullagh implemented an interesting way to get BLOBs out of a MySQL database by using a URL. It is really very simple:

We start by creating a table using any streaming enabled storage engine (a streaming enable storage engine, is an engine that supports the server-side streaming API):


use test;

CREATE TABLE notes_tab (
n_id INTEGER PRIMARY KEY,
n_text BLOB
) ENGINE=pbxt;

INSERT notes_tab VALUES (1, "This is a BLOB streaming test!");

Now assuming the MySQL server is on the localhost, and the BLOB streaming engine has been set to port 8080, you can open your browser, and enter this URL:

http://localhost:8080/test/notes_tab/n_text/n_id=1

I suggested that we could extend this to returning data from any table using JSON which would provide a very nice REST access method into the data without having to go through the overhead of logging in and dealing with a SQL statement.

Advertisements

6 Responses to Getting data out of MySQL

  1. Hi François,

    As you have noted, the BLOB streaming engine turns MySQL into a “RESTful” application. I am sure this will please Roy Fielding, and fits perfectly into the MySQL Web 2.0 strategy!

    What you are now suggesting is that we enable retrieval of entire records (or a selection of records) in JSON. Then, as you say, an application could just GET the data it wants out of the database (it could also use PUT to insert, POST to update and DELETE to … delete records, for that matter).

    Excellent idea! :) I will make sure that the server-side streaming API makes this possible!

  2. Hi Paul

    Great, I would have settled for GET, but making support for PUT, POST and DELETE possible would be great.

  3. noel says:

    POST would require some kind of expected structure though would it not? XML?

    Very cool nonetheless. What about more complex tables/rows? What if I wanted to select 5 columns at once out of a table by its key? What does the return structure give me?

  4. noel says:

    oh NM…that’s what you’re saying use JSON for! Duh…

  5. I took a look at JSON recently and implemented it for a personal project here. I found it to be very well thought out, it is very lightweight and requires less resources to parse than XML which makes it a very nice alternative when you don’t need all the features of XML.

  6. noel says:

    We used JSON for one thing in wiserearth but I didn’t implement it. I should make an excuse to use it for something. My current project has a REST API, but they already settled on XML for it.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: