Content of this series:

The power of OData – Part 1 – Introduction

The power of OData – Part 2 – Setting up the Environment

The power of OData – Part 3 – Creating a data Model

      The power of OData – Part 4 – Creating an OData service (this part)

The power of OData – Part 5 – Consuming the service with SAPUI5

Hey everyone and welcome to the next part of my series. Today, we are going to create the needed source code to create an OData feed from our database. It took me a little longer to write this part because I had to make some changes to one of the tools we will use in this part and pushed them into the repository. Until my pull-request is accepted, we have to use my repository link; I will update this post if the code is integrated into the official branch.

So far, we should have a working Apache Webserver running with PHP 5.5 and a MySQL server with our “customer” database and some sample data in it. In our website’s root directory, we should have a “customer_api” directory with the standard OData PHP Producer library inside. If you need more information about setting up the environment, begin reading the other posts of this series as mentioned above.

First of all, we won’t code much. There exist some libraries and scripts that will create the needed source code for us based on our database design. Furthermore, the documentation on the classes, interfaces and methods is very good. As the created filed will suit our need here, I won’t go much into detail but explain what kind of file is needed for.

Let’s start by getting the required packages. Download the following libraries:

The first download will create the scripts for us based on our database design. Doctrine here is needed in order to access and analyse the database. In general, it is a nice framework for creating abstract versions of a database in PHP. With the help of doctrine, we can have PHP classes that refer to our database. The benefit is that we work with objects here instead of database queries. (So far about Doctrine, it’s only a very very rough description).

Now extract both archives. Within your Doctrine-extract, you should find a Doctrine folder:

/wp-content/uploads/2014/06/1_465362.png

Copy this folder inside your connectors’ folder, so that its structure looks like this:

/wp-content/uploads/2014/06/2_465363.png

  1. That was the configuration needed so far. Now let’s have fun 🙂

Open up your XAMPP control panel and start your MySQL server.

Fire up a console and navigate to your Connector’s folder and execute the following command:

php MySQLConnector.php /db=customer /u=root /pw= /srvc=Customer /h=127.0.0.1

Here are the options:

  • /db= name of the database you want to use
  • /u= username of the database user. I take root in this clean XAMPP installation
  • /pw= password of the database user. For root in a XAMPP installation the password is not set
  • /srvc= the name of the service we want to create, we can change this later again
  • /h= the host of the database system. Localhost would also be possible but I read that there might be some issues with this.

/wp-content/uploads/2014/06/3_465365.png

The system will ask you of you want to modify the EDMX file. Type “n” here and hit return. It should now create the needed script files.

We don’t have to care much about this EDMX file. It is generated by Doctrine and describes our database and all included tables in a xml format.

Within your Connector’s folder navigate now to the OutputFiles folder lying under ODataConnectorForMySQL:

/wp-content/uploads/2014/06/4_465366.png

Within that, there is a folder called Customers

/wp-content/uploads/2014/06/5_465367.png

As I said the script to create the service with a capital C. Now copy this folder and go to your services folder under C:\xampp\htdocs\customer_api\:

/wp-content/uploads/2014/06/6_465368.png

Delete anything in this folder and insert your service folder:

Finally copy the service.config.xml file from your Customer folder to the services folder. The final folder structure should look like this:

/wp-content/uploads/2014/06/7_465369.png

Now let’s see if it works. Start Apache from your XAMPP control panel (MySQL should be started because of the above configuration), start a browser and open this URL: http://localhost/customer_api/Customer.svc

If you get some XML listing showing your tables, everything went well.

Now open http://localhost/customer_api/Customer.svc/$metadata

You will get a description of your tables and columns.

To access, let’s say all customers, go here: http://localhost/customer_api/Customer.svc/customers

As I said, we can change the service name now, if we want. Open the xml file you just copied to the services folder:

/wp-content/uploads/2014/06/8_465370.png

The content here says how the service is called (here “Customer.svc”), where it is located and what URL should be called.

If you look into the Customer folder, there are 5 files that were previously created. The EDMX file is not relevant here and could also be deleted. Important of those 5 files are the following 3:

  • CustomerDataService.php
  • CustomerMetadata.php
  • CustomerQueryProvider.php

In the first file, a reference to the others is done and objects are instantiated. So if any of those classes change or you create everything manually, here is the mapping part. Within the metadata file, a class representation of our database structure is created. This includes key fields as well as all relations between the tables. Within the QueryProvider file, the queries itself are created. This is the place where we also could define custom queries or add some Join statements for example.

More about those files can be found in the documentation of the OData PHP producer. I think that the MySQLConnector here is a good tool for starting with custom services.

In the next and last part, I will consume the services from an UI5 application The power of OData – Part 5 – Consuming the service with SAPUI5.

To report this post you need to login first.

18 Comments

You must be Logged on to comment or reply to a post.

  1. Paulo Cesar de Biasi Vantini

    Hi Marc,

    I´m excited about this config but after following your steps I could not have the output from URL http://localhost/customer_api/Customer.svc maybe I did something wrong or something is missing. It says the object could not be found.

    -When I browse for the path http://localhost/customer_api/services/service.config.xml

    I can get the xml output according to the print below.

    -When I run http://localhost/customer_api/ 

    It still returns “No configuration info found for “

    Do I have change something or some path is wrong ?

    It is something with .htaccess file?

    I wait for your help.

    Thanks a lot!

    the prints:

    After downloading  odatamysqlphpconnect-2e98cfd0acfe and DoctrineDBAL-2.3.4-full.tar

    /wp-content/uploads/2014/06/1_465436.png

    /wp-content/uploads/2014/06/2_465446.png/wp-content/uploads/2014/06/3_465447.png/wp-content/uploads/2014/06/4_465448.png/wp-content/uploads/2014/06/5_465449.png

    (0) 
    1. Marc Augustin Post author

      Seems like rewriting isn’t working properly. Did you do the changes in httpd.conf as in the environment tutorial?

      I’ll attach my HTTPD.conf there. You could also try to change the following:

      Search in HTTPD.conf for “AllowOverride”. If it says “none” or something else than “All” change the line to the following:

      “AllowOverride All”

      There sometimes are issues with rewriting under Windows. As we have a test environment here, you can this without worrying.

      That is doesn’t find anything for the “customer_api” URL is correct, as it expects some “*.svc” to find.

      Let me know if this helps.

      (0) 
      1. Paulo Cesar de Biasi Vantini

        Marc,

        I changed all the lines to AllowOverride All in httpd.conf restarted the server and It still does no rewrite to Customer.svc. I also used your httpd.conf file, and still does not rewrite. As you said it seems like an issue with rewritting under windows. Could you please attach or send me your whole xampp/apache/conf folder, your php.ini and your final folder customer_api ?

        So that I can check if it is any config that was missing or it is something with my operational system environment not allowing me to rewrite through the .htaccess file.

        Thanks.

        (0) 
          1. Paulo Cesar de Biasi Vantini

            Hey dude,

            you rock!  Now it is perfectly working!

            /wp-content/uploads/2014/06/7_466278.png

            I´ve tried the files separetely and it seems like it was an issue with the .htaccess file which in my case was considered in text format. Also, your php.ini was better structured.

            Anyway, I am thrilled with your remarkable developments and waiting for your next posts.

            May God bless you,

            Thanks!

            (0) 
            1. Douglas Oliveira

              Hello,

              I’m facing the same issue, so the solution seems to be using the php.ini and the htaccess sent by, isn’t? Can you share here, so if anyone else has the same problem the solution will be already here?

              BR,

              Doug

              (0) 
    1. Jakob Ludewig

      Hello Kevin,

      I don’t know whether this is still relevant for you but I ran into the same problem which could be solved by installing the xsl extension for php. On Ubuntu it was a simple ‘apt-get install php5-xsl’ and everything ran smoothly.

      (0) 
  2. Bernard Fricaud

    Hi All,

    i have try to generate the output files. but i get a error :-(.

    I using DoctrineDBAL-2.3.4-full.tar.gz and  the latest producer  OData Producer Library for PHP V1.2  and connector OData MySQL Connector for PHP V1.0 .

    PHP 5.4.34

    MYSQL 5.6.21

    The Error:

    Error: Non-static method Inflector::singularize() should not be called statically, assuming $this from incompatible context..!!!

    Are there some knowed  compatibility issues?

    Bernard

    @Marc: thanks for the good documentation 🙂

    (0) 
    1. Bernard Fricaud

      Hi,

      i have found the Solution by my self. I have update the file MySQLConnectorV1.0\ODataConnectorForMySQL\Common\inflector.php. I have add “static” keywords to the methods.

      And now it works 🙂

      cheers

      Bernard

      (0) 
          1. Marc Augustin Post author

            Just wanted to share the link to my repo again, but glad that it worked now for you.

            If the pull request would be accepted, everything would be easier….

            cheers,

            Marc

            (0) 
  3. Bernard Fricaud

    Hi,

    this version of library support only GET ? 🙁

    {

      “error”: {

      “message”: {

      “lang”: “en-US”, “value”: “This release of library support only GET (read) request, received a request with method POST”

      }

      }

    }

    Is there a posibility to change that?

    In my service provider file i have the default set…

    $config->setEntitySetAccessRule(‘*’, EntitySetRights::ALL);

    cheers

    Bernard

    (0) 

Leave a Reply