Create Datasource Externally

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

AIV 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

Download DatasourceAPI.jar & Properties file from here..
JDK version 8 (Java version 1.8)

Setup .Properties file

Download DatasourceAPI.jar & new.properties file from prerequisites
Extract content from .zip file

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

user: provide user name here

pass: provide encrypted password here. in order know how to get Encrypted password follow below steps,

3.1 Open AIV 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

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.

url: provide base URL of AIV 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

Save this changes and close this properties file

Add headers in AIV

To create datasource externally, we need to pass a header in Allow Headers property of AIV configuration.
Go to Administration > AIV Configurations and you will see AIV Configurations property in General Configuration part.
Add ep in the list of Headers available and save this changes.

Run jar file

Open command prompt or terminal and navigate to directory where both jar and properties file is placed
Run jar file by executing below command;

java -jar DatasourceAPI.jar

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

Troubleshooting Steps

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

1. java.lang.NullPointerException

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

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

3. Datasource with same name already exist

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

4. Not a Valid Database Type

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>"`

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>"`

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.

7. `"<h4>Connection Failed</h4> <br> <p>com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure\n\nThe last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.</p>"`

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>"`

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