Skip to main content

Create Datasource Externally

User create datasource in OPNBI to get the data from remote database. User can create different datasource from datasource section of OPNBI if user has administration rights. This is one way of creating datasource where user need to login to OPNBI and create datasource from GUI. In other way, user can also create datasource externally using REST request.

OPNBI has created a sample project to create datasource externally using REST request. you can find executable jar file of this project from prerequisites section.

Prerequisites​

info

Before proceeding in next step, please check you have java version 8 installed in your machine.

In Windows, open cmd and execute this command: java -version and it will show you installed java version.

In linux, execute same command in terminal and it will show you installed java version.

Download and install java if not installed. for windows Download JDK from here. in case of linux, run this command to install java : sudo apt-get install openjdk-8-jdk

If you are facing any erorr installing java run this command: sudo apt-get update and then execute this command sudo apt-get install openjdk-8-jdk

once java installed check java version by executing this command : java -version

Setup .Properties file​

  1. Download DatasourceAPI.jar & new.properties file from prerequisites

  2. Extract content from .zip file

info

Make sure both DatasourceAPI.jar & new.properties files are in same folder

  1. Open new.properties file in any text editor, you will see following properties in this file as below,

    Docusaurus Slash Introduction

  • user: provide user name here
info

Make sure user name provide has administrator rights

  • pass: provide encrypted password here. To know how to get Encrypted password follow below steps,
info

3.1 Open OPNBI in browser and keep network tab open in devloper tools

3.2 Provide user name and password on login page and click on submit button.

3.3 Once login successful, switch to network tab and you will see authenticate request is called

Docusaurus Slash Introduction

3.4 Click on this request and click go to Payload tab and you will see password property. that is your encrypted password, copy this password and paste this in new.properties file.

Docusaurus Slash Introduction

  • url: provide base URL of OPNBI here

  • datasourcename: provide name of datasource you are creating

  • databasetype: provide database type. i.e. mysql or mssql or postgresql

  • databasedriver: provide database driver class name. 

    com.mysql.jdbc.Driver : for MySQL 

    org.postgresql.Driver : for postgresql 

    com.microsoft.sqlserver.jdbc.SQLServerDriver : for SQL Server

  • datasourceurl: provide database connection URL

    jdbc:mysql://<hostname>:<port>/<dbname> : for MySQL 

    jdbc:postgresql://<hostname>:<port>/<dbname> : for postgresql 

    jdbc:sqlserver://<hostname>:<port>;databaseName=<dbname> : for SQL Server

  • databaseusername: provide database username

  • databasepassword: provide database password

  1. Save this changes and close this properties file

Add headers in OPNBI​

  1. To create datasource externally, we need to pass a header in Allow Headers property of OPNBI configuration.

  2. Go to Administration > OPNBI Configurations and you will see OPNBI Configurations property in General Configuration part.

  3. Add ep in the list of Headers available and save this changes.

    Docusaurus Slash Introduction

Run jar file​

  1. Open command prompt or terminal and navigate to directory where both jar and properties file is placed

  2. Run jar file by executing below command;

    java -jar DatasourceAPI.jar

  3. You will see a message Datasource created with ID- datasource_ID_number in console if all details provided in properties file are correct.

    Docusaurus Slash Introduction

    info

    If you are not getting success message and getting any error please go through troubleshooting steps provided below

Troubleshooting Steps​

  • List of possible erros you might get while executing jar file are listed below;

    1. java.lang.NullPointerException​

    Docusaurus Slash Introduction

  • Solution: This Might observed if your user name or password provided in properties file is incorrect. please check credentials once and try again.

    2. Exception in thread "main" java.lang.NullPointerException​

    Docusaurus Slash Introduction

  • Solution: This might observed due to incorrect url provided in properties file

    3. Datasource with same name already exist​

    Docusaurus Slash Introduction

  • Solution: This might observed if datasource with same name exist in OPNBI. try to change name of datasource you are creating in .properties file and try again.

    4. Not a Valid Database Type​

    Docusaurus Slash Introduction

  • Solution: This might observed if entered datatype is not valid. you need to provide database type from 3 options provided.

    5. "<h4>Class not found</h4> <br> <p>java.lang.ClassNotFoundException: com.mysql.jdbc.Driver1</p>"​

    Docusaurus Slash Introduction

  • Solution: This might observed if entered datatype is not valid. you need to provide database type from 3 options provided.

    6. "<h4>Connection Failed</h4> <br> <p>com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database 'training123'</p>"​

    Docusaurus Slash Introduction

  • Solution: This error might observed if database name you have provided in database URL is incorrect or database name you have entered is not available. check your database and provide a valid database name to connect in properties file and try again.

    Docusaurus Slash Introduction

  • Solution: This error might observed if there is any issue in database connection URL. check database port or URL and try again

    8. "<h4>Connection Failed</h4> <br> <p>java.sql.SQLException: Access denied for user 'root1'@'localhost' (using password: YES)</p>"​

    Docusaurus Slash Introduction

  • Solution: This error might observed if either user name or password provided is incorrect. check credentials and try again.