In software development, the easier it is to test, the better the code!

And the ESP studio is nice development framework for developing and testing projects with the local cluster. Projects often use input / output database adapters, however, sometimes there are constraints around the use of those databases which can make testing more complicated.

This post explains how in less than two minutes you can create a lightweight database that will be automatically started and stopped along your ESP projects. The database engine is SQL Anywhere, which is already installed 🙂 If your SQL code is ANSI, using SQL Anywhere instead of a bigger footprint database should probably be transparent.

This tutorial is made with the ESP Studio SPS10 but it should work with SPS9, maybe even SPS8.

It consists of 5 steps:

  1. Customize the attributes of your testing database
  2. Initialize it and create the ODBC alias
  3. Optionally, configure Sybase Central for an even easier database development experience
  4. Create a table and add rows
  5. Use the new database table in an ESP project

Open a DOS console and adjust the values based on your preferences :

set dbname=miniDB

set dbpassword=Training1

set dbpath=%STREAMING_HOME%\miniDB

Then you can simply execute the following commands in the same window to create the database, register an ODBC alias, and connect your newly running SQL Anywhere db :

set pathutils=%STREAMING_HOME%\sqla\Bin64

set dbfile=%dbpath%\%dbname%.db

mkdir “%dbpath%”

cd /D “%dbpath%”


%pathutils%\dbinit -z “UTF8BIN” -zn “UCA” -dba DBA,”%dbpassword%” -t “%dbname%.dblog” “%dbname%.db”

%pathutils%\dbdsn -pe -y -cw -w sqla_%dbname% -c “DBF=%dbfile%;UID=DBA;PWD=%dbpassword%;DBN=%dbname%;ASTART=TRUE;ASTOP=TRUE;StartLine=%pathutils%\dbeng16 -c 12M -xd -zr sql -zo %dbpath%\queries.txt -zn 3 -zs 1M”

%pathutils%\dbisql -datasource sqla_%dbname%

That’s it, you should have an open connection to the new db. Press F9 to execute a query

/wp-content/uploads/2015/08/dbisql_779216.png

This new db is located in the path you’ve specified  :

/wp-content/uploads/2015/08/after_dbinit_779645.png

The miniDB is configured to log all queries in queries.TXT with 3 rotating log files of 1 MB each.

A user ODBC alias should have been created :

ODBC.PNG

Optionally, you can use Sybase Central if you’re not familiar with SQL Anywhere.

Launch it:    %STREAMING_HOME%\sqla\Bin64\scjview

Open tools / Plug-ins

SC.plugin1.png

The Plug in Menu should appear, Click on “Register”SC.plugin2.png

Then select the second option, “Register a pluging by specifying a JAR file” and browse.

Paste this to navigate to the appropriate directory : %STREAMING_HOME%\sqla\Java and select the file saplugin.jar

SC.plugin3.png

Then click next and finish and the plugin should be loaded :

SC.plugin4.png

Then make a right click on SQL Anywhere 16 and select connect

SC.connect1.png

Then choose the ODBC option

SC.connect2.png

Click on “browse” to find your ODBC alias

SC.connect3.png

And then click connect,  you can leave the fields “User ID” and “Password” blanks, the values are defined inside the alias.

You can now use Sybase Central to administer the database and create objects :

SC.create.table1.png

For the rest of this tutorial, we’ll use the table called reservations to store the bookings of the hotel franchise.

SC.create.table2.png

You can get the DDL of any object by selecting it and hitting “ctrl-c”


CREATE TABLE "DBA"."reservations" (
  "hotelid" INTEGER NOT NULL,
  "bookingid" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
  "bookingdt" TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
  "amount" FLOAT NOT NULL,
  "bookingdtstart" DATE NOT NULL,
  "bookingdtend" DATE NOT NULL,
  PRIMARY KEY ( "bookingid" ASC )
) ;

Execute the following SQL to insert 2000 bookings in 12 hotels, of a length between 1 and 7 nights


delete "DBA"."reservations";
BEGIN
   declare @i int;
   declare @j int;
   set @i=1;
   while @i<2000 LOOP 
      set @j=round(RAND()*60,0);
      insert "DBA"."reservations" (hotelid, amount, bookingdtstart, bookingdtend)
           values (round(RAND()*12,0),round(RAND()*1000, 0), dateadd(dd,@j,current date),dateadd(dd,@j+1+round(RAND()*6, 0),current date) ) ;
      set @i=@i+1;
   END LOOP;
   COMMIT;
END;

The following ESP Project is designed to maintain aggregates, for instance the number of arrivals and departures per hotel and per day.

The CCL code is :

CREATE SCHEMA RESERVATIONS_RCD (

  hotelid INTEGER,

  bookingid INTEGER ,

  bookingdt MsDate,

  amount FLOAT,

  bookingdtstart Bigdatetime,

  bookingdtend Bigdatetime

);

CREATE INPUT STREAM INPUT_RESERVATIONS SCHEMA RESERVATIONS_RCD;

CREATE OUTPUT WINDOW OUT_DAILYARRIVALS

PRIMARY KEY DEDUCED

KEEP ALL as

select res.hotelid,

       res.bookingdtstart,

       count(*) nb_arrivals

from  INPUT_RESERVATIONS res


//remove reservations that ended before yesterday

where res.bookingdtstart > datefloor( ‘DAY’,now() ) – 1 DAY

group by res.hotelid, res.bookingdtstart ;

ATTACH INPUT ADAPTER DB_RESERVATIONS TYPE db_in

TO INPUT_RESERVATIONS

GROUP RunGroup1

PROPERTIES

  query = ‘SELECT hotelid, bookingid, bookingdt, amount, bookingdtstart, bookingdtend FROM reservations where bookingid > ?ESP_COUNTER_VALUE AND bookingdtend >= dateadd(dd,-1,current DATE)’ ,

  recordCounterColumn=’bookingid’,

  recordCounterInitValue=’0′,

  service = ‘sqla_minidb’,

  pollperiod = 20 ;

ADAPTER START GROUPS RunGroup1;


Then add the connection to the new database by selecting the Data Services tab and making a right click “Add ODBC service”

ESP.ODBC1.png

Set the following connection properties :

ESP.ODBC2.png

Then finally you can run the project, open the stream view of OUT_DAILYARRIVALS.

If you re execute the loop to insert data, ESP will issue a query to poll only this incremental data and you should see new reservations in the window.

RESULTS.png

Please let me know if you run into any issue while trying to follow this tutorial.

Remi.

To report this post you need to login first.

11 Comments

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

  1. Jeff Wootton

    Remi – thanks for sharing this!  I’m sure people will find it useful.

    The only comment I have is just a “health warning” on the output window OUT_DAILYARRIVALS: while this will be fine for testing, if you were going to actually use it, you would need to add a KEEP policy or other mechanism to maintain the size, otherwise it would grow unbounded as reservations continue to accumulate over time. Maybe a filter to only look at reservations in the future and not past ones?

    (0) 
    1. Remi ASTIER Post author

      You’re absolutely right.

      I also need to maintain the vacancy information per day. If a reservation last 4 nights, a single event needs to increment 4 counters… Can you think of a way around using a flex operator ?

      (0) 
      1. Jeff Wootton

        I can’t think of a way to do that without a flex, but it seems easy enough with a flex and then an aggregation:  just use a flex to decompose each reservation event into a set of nightly booking events (one for each day) and then aggregate by hotelid/day to count the bookings per hotel per night.  Like this:

        CREATE SCHEMA RESERVATIONS_RCD

        (   hotelid INTEGER,   bookingid INTEGER ,   bookingdt msdate,   amount FLOAT,   bookingdtstart msdate,   bookingdtend msdate );

        CREATE INPUT STREAM INPUT_RESERVATIONS SCHEMA RESERVATIONS_RCD;

        CREATE FLEX DAILY_BOOKINGS

        IN INPUT_RESERVATIONS

        OUT OUTPUT STREAM DAILY_BOOKINGS

        SCHEMA ( hotelid INTEGER , date msdate, bookingid INTEGER )

        BEGIN

          DECLARE   msdate RezDate;

          END;

          ON INPUT_RESERVATIONS { 

              RezDate := datefloor(‘DAY’, INPUT_RESERVATIONS.bookingdtstart);  

              WHILE (RezDate < datefloor(‘DAY’, INPUT_RESERVATIONS.bookingdtend))\

              {  OUTPUT [hotelid = INPUT_RESERVATIONS.hotelid; date = RezDate; bookingid = INPUT_RESERVATIONS.bookingid;];   RezDate := RezDate + 1 DAY;   }

           } ;

        END;

        CREATE OUTPUT WINDOW DAILY_OCCUPANCY

        PRIMARY KEY DEDUCED

        AS SELECT

          DAILY_BOOKINGS.hotelid hotelid ,

          DAILY_BOOKINGS.date date ,

          count ( DAILY_BOOKINGS.bookingid ) roomsbooked

        FROM DAILY_BOOKINGS

        GROUP BY DAILY_BOOKINGS.hotelid , DAILY_BOOKINGS.date ;

        Note that I did run into a wonky issue that seems to be a time zone issue.  Need to look closer at that. Also note that I changed the type of the booking date columns to msdate, since the datefloor fn doesn’t seem to like seconddate, and it was easier to change than lookup the conversion funcion 🙂

        (0) 
  2. Jagadees Arumugam

    Thank you for sharing this.

    I have question…. I have my ESP studio client in local system. When checking to Data service, it is pointing to Localhost. Instead want to have data services that in my ESP server. It is not allowing me to reference the ESP server.

    (0) 
    1. Remi ASTIER Post author

      Hi,

      This post is about using an embedded database.

      I don’t understand your question.

      Do you want Data Service to interact with the embedded database ?

      Do you want Data Service to interact with ESP ?

      (0) 
        1. Remi ASTIER Post author

          If your topology is :

               ESP -> Data Services -> HANA / other database

          Then you could probably change it to :

               ESP -> HANA / other database

          Do you want to output a stream to data services ? Please clarify your question.

          (0) 
          1. Elangovan Subbiah

            Looks like Jagadees wants to create a ODBC Data Source to insert data from an output stream / window into HANA.

            He is not mentioning Data Service as a product but just as a connection…

            If the above is right Jagadees – create a System DSN using ODBC Data Source (Administrative Tools in windows) or configure ODBC.ini in linux and then use that in the configuration from ESP Studio.

            (0) 
            1. Jagadees Arumugam

              Thank you, Elangovan and Remi.

              I could create ODBC Data service connecting HANA Database through ESP studio in ESP Server in separate server. But from the local machine, where I have working client ESP Studio, how would I have access to HANA database ? As Remi pointed, Is it possible to have topology ESP -> HANA DB ? Else can I have data service in local machine to connect HANA database ? If so, I’m restricted with Data services to Localhost referring to $ESP_HOME$/bin/service.xml, which has only discovery but no option of adding ODBC/JBDC/OCS.

              (0) 
              1. Jeff Wootton

                ESP has full support for connections to HANA, both to write to HANA DB and also to read from (query) HANA DB.  The ESP-to-HANA connection uses ODBC, so the HANA ODBC client must be installed on the machine where the ESP server is running. If you are running locally from ESP Studio, then the HANA ODBC client needs to be on the studio host.  If  you are using ESP Studio to deploy on a remote ESP server, then the HANA ODBC client needs to be on the ESP server host.

                You shoudl be running the latest version of ESP, and you should not be using the service.xml file.  This was replaced in SP08 (we are now on SP10!) with Data Services that are defined in the ESP cluster configuration (stored in a database).

                To write to HANA, use the HANA output adapter rather than the generice ODBC or JDBC adapter.

                You may want to consult the ESP Hands-on Tutorial or this video tutorial: https://www.youtube.com/watch?v=g5JAmwDUe7Q&list=PLkzo92owKnVz6jyluKi9S7WRKMIqLvM2w&index=7

                One final question:  since you are clearly using ESP with HANA, is there a reason you are not using HANA smart data streaming?

                (0) 
                1. Jagadees Arumugam

                  Hi Remi,

                    I’m installing ESP studio (SP09) to access cluster in  ESP server (in different server machine). what are required components to make it successful ? I’m facing challenge in turns of failed login to ESP server (in Run test perspective).

                  (0) 

Leave a Reply