satya - 8/29/2020, 4:37:35 PM

Goal

1. As of 2020 document how you setup a database in aspire properties files

2. I will start with some relevant URLs where this may have been documented or related URLs

3. As the article progresses you will see the needed complete setup for setting up a database

4. Likely example is going to be using SQLServr

satya - 8/29/2020, 4:38:32 PM

You need to setup a ConnectionPool first. One of the connection pools is documented here

satya - 8/29/2020, 4:40:28 PM

Here is an example of microsoft access database properties file

satya - 8/29/2020, 4:43:10 PM

A connection pool needs to be initialized

satya - 8/29/2020, 4:43:30 PM

Here is how to use/configure initializers in Aspire

satya - 8/29/2020, 5:14:57 PM

Listing to initialize the connection pool for any JDBC database


#
# You can place these in database.properties
# it is merely a suggested name for the file.
#

#
# A connection pool implementation 
# A scheduler is required for this pool to work
# The corresponding scheduler is defined as well
#

request.AppObjects.connectionManager.className=com.ai.db.cp4.ConnectionPoolConnectionManager4
request.AppObjects.scheduler.className=com.ai.scheduler.BasicScheduler
AppObjects.scheduler.timer_interval_in_milli_secs=120000

#Not necessary to preload connections
#request.AppObjects.connectionManager.preload-datasources=reportsDB

#Use this line in aspire.properties files
#If more than one initializer, use comma separated list
Aspire.startup.initializers=AppObjects.connectionManager

satya - 8/31/2020, 10:37:37 AM

Some notes

1. Preloading connections is meaningful in a webserver. for stand alone applications no need to preload.

2. For a stand alone application one may not need a pool. You may even use a SimpleConnectionManager which merely opens and closes a connection each time.

satya - 8/31/2020, 10:38:19 AM

You may want to put this in a properties file like database.properties

although name is arbitrary

satya - 8/31/2020, 10:39:05 AM

And then include that database.properties in the aspire.properties file


application.includeFiles=aspire:\\aspire-batch\\test-csv.properties,aspire:\\aspire-batch\\database.properties

satya - 8/31/2020, 10:41:27 AM

Nature of aspire.properties

1. This is the root properties file (name is arbitrary) that includes all other properties files as includes

2. Contains main objects to bootstrap the application

3. Typically contains Factory objects like

4. Configuration

5. Logging

6. Factories

7. Initializers

8. Connection and Http Event handlers

9. Security

10. etc.

satya - 8/31/2020, 10:42:36 AM

It also varies

1. from application to application

2. far more extensive for a full blown web app

3. A very simple one for command line applications

4. Database access adds a few more entries

satya - 8/31/2020, 10:42:57 AM

I will add a reference here from github soon for some representative files

satya - 8/31/2020, 10:46:05 AM

Now back to specifying a database in the properties file


#**************************************************************************
# SQL Server database definition 
#**************************************************************************

#Database.name = forecastdb
Database.forecastdb.jdbc_driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

Database.forecastdb.connection_string=\ 
jdbc:sqlserver://localhost\\MSSQLSERVER;database=db-name

Database.forecastdb.userid=
Database.forecastdb.password=

Database.forecastdb.expirationTimeInMin=1
Database.forecastdb.minimumNumberOfConnections=0

#**************************************************************************
#********** Database aliases
#**************************************************************************
Database.alias.testdb = forecastdb

satya - 8/31/2020, 10:47:02 AM

Few notes on backslashes above

1. Make sure there are no spaces for line continuation back slashes

2. Due to a current bug in my code I had to put here that extra space

satya - 8/31/2020, 10:48:58 AM

Note on aliases

1. Aliases allow to quickly switch the target database name like "testdb" to point to different databases that are defined. like between test, stage, and prod

2. It is the alias name "testdb" that is used in subsequent property file references to the database

satya - 8/31/2020, 10:49:12 AM

So in the above definitions the database name you will refer to is "testdb"

satya - 8/31/2020, 10:52:27 AM

This is a configuration for MS SQL Server

1. This will change from sql server to Oracle etc.

2. The change is as needed by the respective jdbc driver

3. Expect a good bit of time getting the jdbc spec right

satya - 8/31/2020, 10:56:08 AM

Here are notes on setting up JDBC drivers for MS SQL Server

satya - 8/31/2020, 10:57:07 AM

Here is now how you can test the database using Aspire database reads


#**************************************************************************
#********** Test database
#**************************************************************************
request.testdatabase.classname=com.ai.db.DBRequestExecutor2
request.testdatabase.db=testdb
request.testdatabase.stmt=select f_contact_name from t_vre_contacts

satya - 8/31/2020, 10:57:25 AM

You can use any SQL statement you like to test this

satya - 8/31/2020, 11:00:47 AM

Briefly

1. com.ai.db.DBRequestExecutor2 is a "reusable part" that knows how to execute basice CRUD operations on a database based on the configuration supplied

2. Calling Java will invoke this with 2 arguments

3. Arg1: The name of the request "testdatabase"

4. Arg2: A map of key value pairs which can be used in the CRUD statements. The map have come from URL or any other context including property files themselves

5. This simple segment can get very elaborate and extensive delivering HDS (hierarchical data sets) that can be rendered as HTML, XML, JSON, Java etc.

satya - 8/31/2020, 11:02:02 AM

runTestCollection.cmd

1. There is a program called runTestCollection.cmd that can be used to test these property file database invocations

2. I will provide a github link soon

satya - 8/31/2020, 11:45:57 AM

The following files I have put them on github here


aspire.properties
database.propeerties
runTestCollection.cmd