SQLPad with Actian X/Vector/Ingres

README.md

SQLPad with Actian X/Vector/Ingres

https://github.com/rickbergfalk/sqlpad/ is a web app for writing and running SQL queries and visualizing the results. It works well with Actian X/Vector/Ingres and is straightfoward to deploy.

Over view of Steps

These instructions were originally written for a version from 2018 https://github.com/clach04/sqlpad/tree/unixodbc_squashed/server/drivers - they have been tested with headrevs code from https://github.com/rickbergfalk/sqlpad/ with version 3.0.x 2019-09-05.

1. Install Actian client, the ODBC driver is the required component.

2. Install Linux operating system packages needed to run UnixODBC and Node.js

   sudo apt-get install g++ make python-dev nodejs-dev node-gyp unixodbc-dev  # Debian/Ubuntu
   sudo yum install make node-gyp unixODBC-devel python2-devel nodejs-devel  # CentOS / RedHat
   npm install node-gyp
   npm install odbc  # from https://github.com/wankdanker/node-odbc/ - note has compile warnings
   

3. Install SQLPad, see the readme for SQLPad for deployment instructions.

4. Configure SQLPad environment, sample shell script for running sqlpad with environment setup:

   #!/bin/bash
   . .profile  # ensure nvm in path, for specific versions of npm
   nvm --version  # use nvm to show the version of npm in use
   
   # set variables for Actian ODBC config
   . /home/ingres/.ingIIsh  # setup path for Actian client
   export ODBCSYSINI=$II_SYSTEM/ingres/files
   export ODBCINI=$II_SYSTEM/ingres/files/odbc.ini  # NOTE only needed if using a DSN
   export II_ODBC_WCHAR_SIZE=2  # Most Linux distros build UnixODBC with non-default build options
   
   cd /location/of/sqlpad
   npm start
   

5. Configure SQLPad users and database connection.

   • Add a connection in SQLPad, using the "unixODBC" driver.
   • For Database sql to lookup schema enter SELECT table_owner, table_name, column_name, column_datatype FROM iicolumns WHERE table_owner != '$ingres' ORDER BY table_owner, table_name, column_sequence
   • For the "ODBC connection string", copy/paste from cloud provisioning admin page. Ensure "Database Username" and "Database Password" are empty.

NOTE schema lookup SQL can be left blank if INFORMATION_SCHEMA has been installed (specifically INFORMATION_SCHEMA.columns is present).