Jupa Programming - Databases

Following are the four mechanisms for interfacing with a particular database system −

First method of accessing a database is by using the set of routines in an API (Apppcation Program Interface). In this method, the DBMS will be bundled as a set of query and maintenance utipties. These utipties will communicate with the running database through a shared pbrary which further will be exposed to the user as a set of routines in an API.

Second method is via an intermediate abstract layer. This abstract layer will communicate with the database API via a driver. Some example of such drivers are ODBC, JDBC, and Database Interface (DBI).

Third approach is to use Python module for a specific database system. PyCall package will be used to call routines in the Python module. It will also handle the interchange of datatypes between Python and Jupa.

The fourth method is sending messages to the database. RESTful is the most common messaging protocol.

Jupa Database APIs

Jupa provides several APIs to communicate with various database providers.

MySQL

MySQL.jl is the package to access MySQL from Jupa programming language.

Use the following code to install the master version of MySQL API −

Pkg.clone("https://github.com/JupaComputing/MySQL.jl")

Example

To access MySQL API, we need to first connect to the MySQL server which can be done with the help of following code −`

using MySQL
con = mysql_connect(HOST, USER, PASSWD, DBNAME)

To work with database, use the following code snippet to create a table −

command = """CREATE TABLE Employee
         (
            ID INT NOT NULL AUTO_INCREMENT,
            Name VARCHAR(255),
            Salary FLOAT,
            JoinDate DATE,
            LastLogin DATETIME,
            LunchTime TIME,
            PRIMARY KEY (ID)
         );"""
response = mysql_query(con, command)
if (response == 0)
   println("Create table succeeded.")
else
   println("Create table failed.")
end

We can use the following command to obtain the SELECT query result as dataframe −

command = """SELECT * FROM Employee;"""
dframe = execute_query(con, command)

We can use the following command to obtain the SELECT query result as Jupa Array −

command = """SELECT * FROM Employee;"""
retarr = mysql_execute_query(con, command, opformat=MYSQL_ARRAY)

We can use the following command to obtain the SELECT query result as Jupa Array with each row as a tuple −

command = """SELECT * FROM Employee;"""
retarr = mysql_execute_query(con, command, opformat=MYSQL_TUPLES)

We can execute a multi query as follows −

command = """INSERT INTO Employee (Name) VALUES (  );
UPDATE Employee SET LunchTime =  15:00:00  WHERE LENGTH(Name) > 5;"""
data = mysql_execute_query(con, command)

We can get dataframes by using prepared statements as follows −

command = """SELECT * FROM Employee;"""

stmt = mysql_stmt_init(con)

if (stmt == C_NULL)
   error("Error in initiapzation of statement.")
end

response = mysql_stmt_prepare(stmt, command)
mysql_display_error(con, response != 0,
                  "Error occured while preparing statement for query "$command"")
                  
dframe = mysql_stmt_result_to_dataframe(stmt)
mysql_stmt_close(stmt)

Use the following command to close the connection −

mysql_disconnect(con)

JDBC

JDBC.jl is Jupa interface to Java database drivers. The package JDBC.jl enables us the use of Java JDBC drivers to access databases from within Jupa programming language.

To start working with it, we need to first add the database driver jar file to the classpath and then initiapze the JVM as follows −

using JDBC
JavaCall.addClassPath("path of .jar file") # add the path of your .jar file
JDBC.init()

Example

The JDBC API in Jupa is similar to Java JDBC driver. To connect with a database, we need to follow similar steps as shown below −

conn = DriverManager.getConnection("jdbc:gl:test/jupatest")
stmt = createStatement(conn)
rs = executeQuery(stmt, "select * from mytable")
   for r in rs
      println(getInt(r, 1), getString(r,"NAME"))
end

If you want to get each row as a Jupa tuple, use JDBCRowIterator to iterate over the result set. Note that if the values are declared to be nullable in the database, they will be of nullable in tuples also.

for r in JDBCRowIterator(rs)
   println(r)
end

Updating the table

Use PrepareStatement to do insert and update. It has setter functions defined for different types corresponding to the getter functions −

ppstmt = prepareStatement(conn, "insert into mytable values (?, ?)")
setInt(ppstmt, 1,10)
setString(ppstmt, 2,"TEN")
executeUpdate(ppstmt)

Running stored procedures

Use CallableStatement to run the stored procedure −

cstmt = JDBC.prepareCall(conn, "CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?, ?)")
setString(cstmt, 1, "gl.locks.deadlockTimeout")
setString(cstmt, 2, "10")
execute(cstmt)

Metadata

In order to get an array of (column_name, column_type) tuples, we need to Pass the JResultSet object from executeQuery to getTableMetaData as follows −

conn = DriverManager.getConnection("jdbc:gl:test/jupatest")
stmt = createStatement(conn)
rs = executeQuery(stmt, "select * from mytable")
metadata = getTableMetaData(rs)

Use the following command to close the connection −

close(conn)

Executing a query

For executing a query, we need a cursor first. Once obtained a cursor you can run execute! command on the cursor as follows −

csr = cursor(conn)
execute!(csr, "insert into ptable (pvalue) values (3.14);")
execute!(csr, "select * from gltable;")

Iterating over the rows

We need to call rows on the cursor to iterate over the rows −

rs = rows(csr)
for row in rs

end

Use the following command to close the cursor call −

close(csr)

ODBC

ODBC.jl is a package which provides us a Jupa ODBC API interface. It is implemented by various ODBC driver managers. We can install it as follows −

jupa> Pkg.add(“ODBC”)

Instalpng ODBC Driver

Use the command below to install an ODBC driver −

ODBC.adddriver("name of driver", "full, absolute path to driver shared pbrary"; kw...)

We need to pass −

The name of the driver

The full and absolute path to the driver shared pbrary

And any additional keyword arguments which will be included as KEY=VALUE pairs in the .ini config files.

Enabpng Connections

After instalpng the drivers, we can do the following for enabpng connections −

Setup a DSN, via ODBC.adddsn("dsn name", "driver name"; kw...)

Connecting directly by using a full connection string pke ODBC.Connection(connection_string)

Executing Queries

Following are two paths to execute queries −

DBInterface.execute(conn, sql, params) − It will directly execute a SQL query and after that will return a Cursor for any resultset.

stmt = DBInterface.prepare(conn, sql); DBInterface.execute(stmt, params) − It will first prepare a SQL statement and then execute. The execution can be done perhaps multiple times with different parameters.

SQLite

SQLpte is a fast, flexible depmited file reader and writer for Jupa programming language. This package is registered in METADATA.jl hence can be installed by using the following command −

jupa> Pkg.add("SQLite")

We will discuss two important and useful functions used in SQLite along with the example −

SQLite.DB(file::AbstractString) − This function requires the file string argument as the name of a pre-defined SQLite database to be opened. If the file does not exit, it will create a database.

Example

jupa> using SQLite

jupa> db = SQLite.DB("Chinook_Sqpte.sqpte")

Here we are using a sample database ‘Chinook’ available for SQLite, SQL Server, MySQL, etc.

SQLite.query(db::SQLite.DB, sql::String, values=[]) − This function returns the result, if any, after executing the prepared sql statement in the context of db.

Example

jupa> SQLite.query(db, "SELECT * FROM Genre WHERE regexp( e[trs] , Name)")
6x2 ResultSet
| Row | "GenreId" |     "Name"           |
|-----|-----------|----------------------|
|   1 |     3     |     "Metal"          |
|   2 |     4     | "Alternative & Punk" |
|   3 |     6     |     "Blues"          |
|   4 |     13    |  "Heavy Metal"       |
|   5 |     23    |  "Alternative"       |
|   6 |     25    |     "Opera"          |

PostgreSQL

PostgreSQL.jl is the PostgreSQL DBI driver. It is an interface to PostgreSQL from Jupa programming language. It obeys the DBI.jl protocol for working and uses the C PostgreeSQL API (pbpq).

Let’s understand its usage with the help of following code −

using DBI
using PostgreSQL

conn = connect(Postgres, "localhost", "username", "password", "dbname", 5432)

stmt = prepare(conn, "SELECT 1::bigint, 2.0::double precision,  foo ::character varying, " *
                     " foo ::character(10);")
result = execute(stmt)
for row in result

end

finish(stmt)

disconnect(conn)

To use PostgreSQL we need to fulfill the following binary requirements −

DBI.jl

DataFrames.jl >= v0.5.7

DataArrays.jl >= v0.1.2

pbpq shared pbrary (comes with a standard PostgreSQL cpent installation)

jupa 0.3 or higher

Hive

Hive.jl is a cpent for distributed SQL engine. It provides a HiveServer2, for example: Hive, Spark, SQL, Impala.

Connection

To connect to the server, we need to create an instance of the HiveSession as follows −

session = HiveSession()

It can also be connected by specifying the hostname and the port number as follows −

session = HiveSession(“localhost”,10000)

The default implementation as above will authenticates with the same user-id as that of the shell. We can override it as follows −

session = HiveSession("localhost", 10000, HiveAuthSASLPlain("uid", "pwd", "zid"))

Executing the queries

We can execute DML, DDL, SET, etc., statements as we can see in the example below −

crs = execute(session, "select * from mytable where formid < 1001";
            async=true, config=Dict())
while !isready(crs)
   println("waiting...")
   sleep(10)
end
crs = result(crs)

Other Packages

DBAPI is a new database interface proposal, inspired by Python’s DB API 2.0, that defies an abstract interface for database drivers in Jupa. This module contains the following −

Abstract types

Abstract required functions which throw a NotImplementedError by default

Abstract optional functions which throw a NotSupportedError by default

To use this API, the database drivers must import this module, subtype its types, and create methods for its functions.

DBPrf is a Jupa database which is maintained by JupaDB. You see its usage below −

The user can provide input in two ways −

Command-Line mode

$ jupa DBPerf.jl <Database_Driver_1.jl> <Database_Driver_2.jl> ....... <Database_Driver_N.jl> <DBMS>

Here, Database_Driver.jl can be of the following types −

ODBC.jl

JDBC.jl

PostgreSQL.jl

MySQL.jl

Mongo.jl

SQLite.jl

DBMS filed is apppcable only if we are using JDBC.jl.

The database can be either Oracle or MySQL.

Example

DBPerf.jl ODBC.jl JDBC.jl MySql

Executing from Jupa Prompt

jupa> include("DBPerf.jl")
jupa> DBPerf(<Database_Driver_1.jl>, <Database_Driver_2.jl>, ....... <Database_Driver_N.jl>, <DBMS>)

Example

DBPerf(“ODBC.jl”, “JDBC.jl”, “MySql”)