Embedded databases in ESP projects
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:
- Customize the attributes of your testing database
- Initialize it and create the ODBC alias
- Optionally, configure Sybase Central for an even easier database development experience
- Create a table and add rows
- Use the new database table in an ESP project
Open a DOS console and adjust the values based on your preferences :
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 :
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
This new db is located in the path you’ve specified :
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 :
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
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
Then click next and finish and the plugin should be loaded :
Then make a right click on SQL Anywhere 16 and select connect
Then choose the ODBC option
Click on “browse” to find your ODBC alias
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 :
For the rest of this tutorial, we’ll use the table called reservations to store the bookings of the hotel franchise.
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 (
bookingid INTEGER ,
CREATE INPUT STREAM INPUT_RESERVATIONS SCHEMA RESERVATIONS_RCD;
CREATE OUTPUT WINDOW OUT_DAILYARRIVALS
PRIMARY KEY DEDUCED
KEEP ALL as
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
query = ‘SELECT hotelid, bookingid, bookingdt, amount, bookingdtstart, bookingdtend FROM reservations where bookingid > ?ESP_COUNTER_VALUE AND bookingdtend >= dateadd(dd,-1,current DATE)’ ,
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”
Set the following connection properties :
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.
Please let me know if you run into any issue while trying to follow this tutorial.