AERGO JDBC Release

Jae Nam
Jae Nam
Feb 6 · 5 min read

AERGO JDBC (Java Database Connectivity) allows database administrators and Java developers to easily view and manipulate data on the blockchain, just like they would using a regular database. This article will cover what you need to know about JDBC and why we have it implemented.

Introduction

One of the major bottlenecks in achieving mass adoption of blockchain technology is due to the unfamiliarity of development environments for programmers. In order to address this problem, AERGO has been built to support the SQL language for smart contracts because it is a widely used programming language in a relational database management system (RDMS). There are at least 7 million developers worldwide that are familiar with programming in SQL.

We at AERGO believe that significant productivity can be achieved through SQL as the blockchain application development logic is very familiar for new blockchain developers.

The client application in essence, constantly repeats the process of creating, signing and sending transactions.

This is how you can use SQL as a smart contract in AERGO Enterprise/ SQLnet:

Developers who are already familiar with using SQL may be reluctant to learn Lua and various blockchain APIs separately. We understand this concern and tackle this issue by asking

How can one access our database at the most basic level?

The answer is via JDBC (Java Database Connectivity).

Henceforth, we developed AERGO JDBC — which allows users to use the blockchain the same way as one would with a regular database; and with the added advantage of improving security and reducing the cost of interacting with the blockchain. It allows database administrators and Java developers to easily view and manipulate data on the blockchain.

AERGO JDBC Configuration

AERGO JDBC consists of a Contract and a JDBC (Java Database Connectivity) package.

If you distribute the Lua contract (db.x.lua) shown below to the AERGO blockchain, the DB contract (Address B) becomes a part of the database (DB).

Once a user uses SQL which is part of the DB, AERGO JDBC automatically links the user with the DB contract to call ‘Transaction(A→B), Query(B)’ on the AERGO blockchain.

DB Lua Contract

How to use AERGO JDBC

Data Definition Language (DDL) and Data Manipulation Language (DML) are executed through blockchain transactions.

SELECT is executed through a query, and the result is received in JSON format and set in the resultSet by its fetch size.

Using a URL like below:

URL jdbc:aergo:<aergo node ip>:<port>@<jdbc contract address>

The following property needs to be registered to execute the Transaction

User : User’s encrypted private key

Password : password for encrypted private key

Constraints

  • The amount of data that can be retrieved from the Query at once is limited to the Remote Procedure Call(RPC) max size of (4MB): If the amount of data to be imported exceeds the max size limit, the fetchsize should be adjusted with resultSet.setFetchSize (default: 10 row)
  • The DB supported by AERGO is SQLite, so only SQL and DDL supported by SQLite are acceptable.

Features NOT Supported

  • Transaction Commits / Rollbacks : The transactions auto commits
  • Scrollable Cursor: Not supported
  • CallableStatement: Not supported

Integration with Squirrel SQL client

The Squirrel SQL client is an open source DB tool that supports JDBC for multiple databases. Furthermore, it is integrated with AERGO JDBC due to its frequent usage. Click here to download it.

JDBC Driver registration

AERGO JDBC needs to be registered in order to integrate it with the AERGO blockchain. In the Drivers tab, click the ‘+icon’ and register following the example below.

Name : aergojdbc

Example URL: jdbc:aergo:<ip:port>@<address>

Class Name : org.aergojdbc.JDBC

Add Extra Class Path: aergo-jdbc-1.0.jar

Alias registration

You can also create and connect an alias using the registered JDBC Driver.

To create an alias,

1.Click the + icon on the aliases tab

2. Select driver as the added aergojdbc

3. Fill in the alias name, URL, username, and password as follows. (User Name is encrypted private key, password is password for private key)

Korean language issue

If the Korean language is broken in Squirrel SQL, -Dfile.encoding=UTF-8 needs to be added to the last line of scripts (sh or bat).

  • Window bat file :start “SQuirreL SQL Client” /B “%LOCAL_JAVA%” -Dfile.encoding=UTF-8
  • Linux sh file: “$JAVACMD” -cp “$CP” -Dfile.encoding=UTF-8

Test method

You can download AERGO JDBC and contract file in the link below

In order to test SQL and JDBC, the blockchain is made public as part of AERGO enterprise.

  • URL : alpha1.aergo.io:7845
  • Constraints: contract DB size is limited to 20mb

Please click the video to understand the Aergo 1.3.0 Aergo JDBC integration process.

Look out for AERGO JDBC 2.0 where there will be a new feature including table permission management and more. To be released soon.

Aergo blog

Enterprise-ready, open-source hybrid blockchain and easy-to-use serverless cloud platform for building practical decentralized apps and business solutions.

    Jae Nam

    Written by

    Jae Nam

    Director of Business Development EMEA at Blocko

    Aergo blog

    Enterprise-ready, open-source hybrid blockchain and easy-to-use serverless cloud platform for building practical decentralized apps and business solutions.

    Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
    Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
    Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade