How do I use the ipushpull Database Loader?

The ipushpull Database Loader lets you publish live data from your local databases to your website. It queries your database periodically, and each time the data changes your website will update automatically with no need to refresh the page.

The loader is a simple command line tool that queries your database and pushes the query results live to an ipushpull page. It runs on Microsoft Windows desktop and server and currently supports Oracle MySQL and Microsoft SQLServer databases.

Note: To make use of this loader you should be familiar with writing database queries in Structured Query Language (SQL) and using the command prompt in Windows.

 

Installation

Email the ipushpull support team to get access to the database utility.

Follow the on-screen instructions to install:

 

Usage

The ipushpull database utility is a command line tool. To use it open a command prompt in the folder chosen during installation.

Run it by entering ippdb myconfig.json:

Where myconfig.json is the configuration is a JSON file containing the database configuration and options you specify (see below). The utility will continue to run until you exit using CTRL + C or close the command prompt window.

 

Configuration File

In order to use the database utility you must create a JSON configuration file, specifying the options relevant to your installation. You can find and example configuration file (example-config.json) in the installation folder:

{
  "ipp_username": "user@domain.com",
  "ipp_password": "password",
  "ipp_folder": "MyFolder",
  "ipp_page": "MyPage",
  "ipp_formats_folder": "MyFormatsFolder",
  "ipp_formats_page": "MyFormatsPage",
  "db_connection_string": "Data Source=my.db.server;Initial Catalog=my_db; User id=username; Password=password;",
  "db_query_string": "SELECT * FROM my_table",
  "interval": 1
}

The following parameters are required:

  • ipp_username: Your ipushpull username
  • ipp_password: Your ipushpull password
  • db_connection_string: The connection string for the database you wish to connect to in either MySQL or SQLServer format.
  • db_query_string: SQL query to run against the database specified in the db_connection_string argument.
  • ipp_folder: The name of the ipushpull folder containing the page you would like to push to.
  • ipp_page: The name of the ipushpull page you wish to push the query results to.

 

The following parameters are optional:

  • interval: Time in seconds to wait between re-running the query. The default is 60 seconds. You should consider the load on your database server before setting this to a lower number, particularly if your query is complex. The minimum is 1 second.
  • ipp_formats_folder: The ipushpull folder containing the formats page (see below)
  • ipp_formats_page: The name of the ipushpull page containing the formats you want to apply to your query (see below)

 

db_connection_string

The loader works out if you are using MS SQL Server or MySQL by examining your db_connection_string. Here's a sample string for MS SQL Server:

 

{
  ...
  "db_connection_string": "Data Source=MYSERVER;Initial Catalog=reporting_db;User Id=username;Password=password;",
... }

Note that if your query string contains backslashes (e.g. if you are running more than one MS SQL Server instance) you may see an error like this:

Bad JSON escape sequence: \R. Path 'db_connection_string', line 8, position 46.

To fix this you need to change each backslash to four backslashes, e.g.

{
  ...
  "db_connection_string": "Data Source=MYSERVER\\INSTANCE01;Initial Catalog=reporting_db;User Id=username;Password=password;",
... }

Specifying formatting

Use the ipp_formats_folder and ipp_formats_page to specify an alternative ipushpull page to pull the formatting (fonts, colours, styles, etc) and apply them to your query.

The formatting of the first row of the formats page will be used for the header row of the query; the second row will be used to format odd row numbers; and the third row will be used to format even rows.

You should specify format columns for each column in your query. However, if the formats page contains fewer columns than the query, the formatting of the last column will be repeated.

 

Help and Support

Please email support@ipushpull.com for help.

Was this article helpful?
0 out of 0 found this helpful
Powered by Zendesk