How to expose a MySQL database as OData

18
votes

I currently have a mySQL database and want interact with it using OData.

In other words, I want to expose the data in this form: http://services.odata.org/Northwind/Northwind.svc/

I've found several tutorials on how to do this (for example, http://msdn.microsoft.com/en-us/vs2010trainingcourse_buildingappandservicesusingodatavs2010_topic3.aspx) if I have Microsoft Visual Studio. But I'm a mac girl. :(

I've also looked at the libraries at the odata homepage (under libraries, and then mysql). But I can't get them to work.

Is there any easy SIMPLE library that can handle this for me?

Thanks so much for the help!!

1
mysqlxmldatabaseodata
Well the one from OData already seems to be quite simple and easy, and by quickly reading the 4-steps tutorial, I don't believe it is possible to be simpler than that. Perhaps you should rather tell us where you are stuck at. – RandomSeed
When going through the OData PHP Producer Library User Guide, I'm unable to find the QueryProvider.php file anywhere (in the installed code or on my XAMPP server). There is NorthwindQueryProvider, WordPressQueryProvider, and IDataServiceProvider... – AllieCat
If you are referring to page 67, this must be a typo. The previous paragraph talks about configuring a file named NorthWindQueryProvider.php for IIS. So I would I would try this file. I think they got mixed between NorthWind and WordPress. I would replace all references to "WordPress" by "NorthWind". Oh and by the way, I now realise the 4-step tutorial actually refers to a 70-page manual... Not quite the same indeed. – RandomSeed
Ah - that it's a typo makes sense. So I've done that and struggled to step 3. But when I call (php MySQLConnector.php...) I get an "Error: include_once(Doctrine/DBAL/DriverManager.php): failed to open stream: No such file or directory..!!!" error when I KNOW DoctrineDBAL is installed. I don't want to say this after all the work to get to step 3, but I don't think OData's "4-Step" process is going to work. It's just been one issue after another.... I'm actually quite surprised the OData website suggests this "connector". – AllieCat
I just found this copyright notice on one of the pages: February 19, 2013 Microsoft Open Technologies Inc. I wouldn't be surprised if MS have modified the functionality, and forgotten to tell anyone about it! Combined with the fact that there have been no code comits on the github page for over a year! I'm not really an MS hater, but this is what I don't like about the 'standards' they produce, just have a look at the 'officeOpen' XML standard, which they produced, and now actively don't attempt to align thier output with. – DaveM

1 Answers

21
votes

I've figured out how to do it - using odata4j. I've documented my steps below in case anyone else wants to do something similar.

You will need to:

  • generate a JPA model from your database
  • use odata4j's NorthwindJpaProducerExample.java script

Detailed steps are below:

  1. Odata4j is an open-source Odata Producer/Consumer in Java. Therefore, you will need to set up Eclipse for EE Developers with a database. I suggest this tutorial if you are new to Eclipse.
  2. Follow these instructions to generate a JPA model.
  3. Go to Odata4j and download the latest archive zip
  4. Add odata4j-bundle-x.x.jar to your build path (it is found in the bundles file).
  5. Insert the following scripts from Odata4j, found on their github: NorthwindJpaProducerExample, JPAProvider, and DatabaseUtils (requires slf4j). (To be honest, I just copy and pasted them into Eclipse). At this point, your Project Explorer bar should look like this (without model.main):

Project Explorer Bar

At this point, right-click the project and select Build Path > Configure Build Path. Add the following "External Jars" from your Odata4j archive file.

External Jars

Now edit the code in NorthwindJpaProducerExample in the following ways:

  1. Change the string "endpointUri" to whatever url you want the oData at
  2. Change the string "persistenceUnitName" to the name of your entity in persistence.xml (as you can in the image above, mine was called "createJPA".)

code sample in NorthwindJpaProducerExample

And then you have OData!

My OData

I ran in to a couple problems while following these steps and will document them here in case you have them also.

  • In order to successfully follow step 2 (generate JPA) each table MUST have a primary key. Do it - I'm not joking.
  • After editing the code, I had a "BigInteger" error. JPAProducer does not support bigInteger field types. Go back to your database and change the size of your column to a regular int.
  • After changing your database in ANY manner, make sure to refresh you database and to clean the project. This will make you and Eclipse happy.
  • I don't think this will be necessary for everyone, but I did need to add a HyperSQL driver dependency. If you get a HSQL error, go to here and get the latest stable version. Add the hsqldb.jar to your Build Path.

Not SUPER easy, but a lot better than a 70+ page manual.