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

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

2. Extract content from .zip file

3. 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

4. Save this changes and close this properties file

Add headers in AIV

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

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

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

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.

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.