SQL Over HTTP for Remote and Cloud Databases - Welcome to Aceql HTTP

Save coding time and eliminate tedious & costly tasks:
Access directly your SQL databases via HTTP.
From all devices. In full security.

SQL over HTTP for All

AceQL HTTP allows to connect from all devices (mobile, tablets, PCs) to your remote or Cloud SQL databases with the simplest language, that you already know: SQL.
Just develop regular SQL calls with your usual C#, Python, Java or Swift IDE. The software takes care of all protocol, communications and security aspects.
Evaluate by downloading AceQL HTTP and following our Quick Start.


AceQL HTTP is a secure framework that provides access to remote or cloud based SQL databases through REST style HTTP APIs. It can thus be used in any language that supports HTTP calls.

AceQL HTTP is FREE and Open Source for access to Open Source SQL databases (LGPL license).

Four supplied SDK clients allow encapsulation of the HTTP APIs.
Zero learning curve, zero time wasted:

C# / Xamarin Client SDK

Allows SQL calls to be encoded with a standard C# SQL API: the SDK C# SQL syntax is identical to the Microsoft SQL Server C# API.

Java Client SDK

Allows SQL calls to be encoded with standard unmodified JDBC syntax (java.sql package interfaces).

Python 2 & 3 Client SDK

Allows SQL calls to be encoded with standard unmodified DB-API 2.0 syntax (PEP 249 -- DB-API 2.0 interface).

Swift Client SDK

Allows SQL calls to be encoded with SQLite.swift like syntax.
(SDK will be released November 23, 2017).

The SDKs are licensed under Apache 2.0: you can use them in your commercial software freely and without any constraint.


AceQL

A universal SQL Over HTTP solution for MySQL, PostgreSQL, SQL Server, Oracle Database & other major DB

AceQL HTTP supports all major SQL database vendors:

  • Actian Ingres
  • IBM DB2
  • IBM Informix
  • MariaDB
  • Microsoft SQL Server
  • Microsoft Access
  • Oracle Database
  • Oracle MySQL
  • PostgreSQL
  • Sybase ASE
  • Sybase SQL Anywhere
  • Teradata Database


Diminish the quantity of
server code & Web services

Tired of writing, testing and deploying Web Services?
Of managing the server code, the client code, and finally the client-server dialog?

With AceQL HTTP you only code the client part to access the remote SQL data.

The framework takes care of all the complex aspects of the client-server dialog (communications, data parsing, error detection).

Make portability and
access to your data easy

Fed up of having to rewrite your code when migrating to new environments?

Migrate your existing C# SQL and Java JDBC application code easily and quickly to many types of desktop OS and mobile / tablet OS.

Do you have a new SQL database? It can be made available to all your applications in just a few moments.

Reduce your costs and increase
your competitiveness

With AceQL HTTP, no dual client-server programming.
No server-side recompilation or redeployment.

Writing desktop and mobile apps to access remote or cloud based SQL data is more reliable, simpler and faster with AceQL HTTP.

Your development times and maintenance costs are reduced.

These gains accelerate the time to market of your apps, giving you a competitive advantage.

Manage your data
securely and easily

AceQL HTTP is a 3 tier architecture and includes a very complete security manager:

  • You freely choose and configure your strong authentication system to protect your SQL databases.
  • You control the syntax and parameters of incoming SQL commands.
  • You trigger the actions in the event of unexpected SQL commands.

Your SQL data moves via SSL and is protected from hackers.


Desktop OS, Android, iOS: Easy to use SDKs to access your remote SQL databases over HTTP

On the client side, you have several programming language options for accessing the remote SQL databases:

OS Available Client SDK
Android                      Java
C# with Xamarin
Python
iOS Swift
C# with Xamarin
Python
Linux C# with Mono
Java
Python
macOS Swift
C# with Xamarin
Java
Python
Windows Desktop C#
Java
Python


SQL calls: MS SQL Server syntax in C# and JDBC syntax in Java

Each SDK provides a SQL syntax with a zero learning curve:

Client SDK SQL Syntax
C#                Microsoft SQL Server like syntax.
SDK class names are the equivalent of SQL Server System.Data.SqlClient namespace.
They share the same suffix name for the classes, and the same method names.
See the C# SDK XML Documentation & examples below.
Java Strict JDBC syntax with java.sql interfaces implementation.
See main class AceQLConnection & examples below.
Python Python 2 & 3 support.
Strict PEP 249 -- DB-API 2.0 syntax.
See examples below.
Swift SQLite.swift like syntax.
See examples below.

Xamarin users: deploy a unique C# code base to all targets

The C# Client SDK is packaged as a Portable Class Library to use in your Xamarin projects.
There is no adaptation per target required. Write a unique and shared C# code and make it run on all major desktop & mobile operating systems:

  • Android
  • iOS
  • macOS
  • Windows Desktop


Easy Access to Remote SQL Database

SQL Over HTTP Code Samples

Here are examples in cURL, C# / Xamarin (with the C# Client SDK), Java/JDBC (with the Java Client SDK), Python (with the Python Client SDK) and Swift (with the upcoming Swift Client SDK).

So we connect to the remote SQL database kawansoft_example with the identifiers (MyUsername, MySecret):

curl \
https://www.acme.com:9443/aceql/database/kawansoft_example/username/MyUsername/connect?password=MySecret

Response:

The command returns a JSON stream with a unique session identifier. We will use the session identifier to authenticate all subsequent SQL calls:

{
    "status":"OK",
    "session_id":"mn7andp2tt049iaeaskr28j9ch"
}
string server = "https://www.acme.com:9443/aceql";
string database = "kawansoft_example";

string connectionString = $"Server={server}; Database={database}";
string username = "MyUsername";
char[] password = { 'M', 'y', 'S', 'e', 'c', 'r', 'e', 't' };

AceQLConnection connection = new AceQLConnection(connectionString)
{
    Credential = new AceQLCredential(username, password)
};

// Attempt to establish a connection to the remote SQL database:
await connection.OpenAsync();

Console.WriteLine("Successfully connected to database " + database + "!");

Response:

Successfully connected to database kawansoft_example!
String server = "https://www.acme.com:9443/aceql";
String database = "kawansoft_example";
String username = "MyUsername";
char[] password = { 'M', 'y', 'S', 'e', 'c', 'r', 'e', 't' };

// Attempt to establish a connection to the remote SQL database:
Connection connection = new AceQLConnection(server, database, username,
	password);

System.out.println("Successfully connected to database " + database + "!");

Response:

Successfully connected to database kawansoft_example!
server = "https://www.acme.com:9443/aceql"
database = "kawansoft_example"
username = "MyUsername"
password = "MySecret"

# Attempt to establish a connection to the remote SQL database:
connection = aceql.connect(server, database, username, password)
print("Successfully connected to database " + database)

Response:

Successfully connected to database kawansoft_example!
let server = "https://www.acme.com:9443/aceql"
let database = "kawansoft_example"
let username = "MyUsername"
let password = ["M", "y", "S", "e", "c", "r", "e", "t"]

// Attempt to establish a connection to the remote SQL database: 
let connection = try AceQLConnection(server, database, username, password)
print("Successfully connected to database \(database)!")

Response:

Successfully connected to database kawansoft_example!

On the server side, a JDBC connection is extracted from the connection pool created by the server at startup. The connection will remain ours during the session.

Following sample shows how to insert a new customer into a remote or Cloud SQL database:

curl --data-urlencode \
 "sql=insert into customer values (1, 'Sir', 'John', 'Doe', '1 Madison Ave', 'New York', 'NY 10010', NULL)" \
https://www.acme.com:9443/aceql/session/mn7andp2tt049iaeaskr28j9ch/execute_update

Response:

{
    "status":"OK",
    "row_count":1
}
string sql = "insert into customer values (1, 'Sir', 'John', 'Doe', " +
    "'1 Madison Ave', 'New York', 'NY 10010', NULL)";

using (AceQLCommand command = new AceQLCommand(sql, connection))
{
    int rows = await command.ExecuteNonQueryAsync();
    Console.WriteLine("Rows updated: " + rows);
}

Response:

Rows updated: 1
String sql = "insert into customer values (1, 'Sir', 'John', 'Doe', "
    + "'1 Madison Ave', 'New York', 'NY 10010', NULL)";

try (Statement statement = connection.createStatement();) {
    int rows = statement.executeUpdate(sql);
    System.out.println("Rows updated: " + rows);
}

Response:

Rows updated: 1
cursor = connection.cursor()
sql = "insert into customer values (1, 'Sir', 'John', 'Doe', '1 Madison Ave', 'New York', 'NY 10010', NULL)"
cursor.execute(sql,)
print("Rows updated: " + str(cursor.rowcount))

Response:

Rows updated: 1
let sql = "insert into customer values (1, \'Sir\', \'John\', \'Doe\', " 
           + "\'1 Madison Ave\', \'New York\', \'NY 10010\', NULL)"
		   
try connection.run(sql)
print("Rows updated: \(connection.changes())")

Response:

Rows updated: 1


We view the inserted customer:

curl --data "pretty_printing=true" --data-urlencode \
 "sql=select customer_id, customer_title, lname from customer where customer_id = 1" \
https://www.acme.com:9443/aceql/session/mn7andp2tt049iaeaskr28j9ch/execute_query

This returns the JSON stream:

{
    "status":"OK",
    "query_rows":[
        {
            "row_1":[
                {
                    "customer_id":1
                },
                {
                    "customer_title":"Sir "
                },
                {
                    "lname":"Doe"
                }
            ]
        }
    ],
    "row_count":1
}
string sql = "select customer_id, customer_title, lname from customer where customer_id = 1";

using (AceQLCommand command = new AceQLCommand(sql, connection))
using (AceQLDataReader dataReader = await command.ExecuteReaderAsync())
{
    while (dataReader.Read())
    {
        Console.WriteLine();
        int i = 0;
        Console.WriteLine("customer_id   : " + dataReader.GetValue(i++));
        Console.WriteLine("customer_title: " + dataReader.GetValue(i++));
        Console.WriteLine("lname         : " + dataReader.GetValue(i++));
    }
} 

Which returns:

customer_id   : 1
customer_title: Sir
lname         : Doe
String sql = "select customer_id, customer_title, lname from customer where customer_id = 1";

try (Statement statement = connection.createStatement();
    ResultSet rs = statement.executeQuery(sql);) {
    while (rs.next()) {

    System.out.println();
    int i = 1;
    System.out.println("customer_id   : " + rs.getInt(i++));
    System.out.println("customer_title: " + rs.getString(i++));
    System.out.println("lname         : " + rs.getString(i++));
    }
}

Which returns:

customer_id   : 1
customer_title: Sir
lname         : Doe
with closing(connection.cursor()) as cursor:
    sql = "select customer_id, customer_title, lname from customer where customer_id = 1"
    cursor.execute(sql)

    rows = cursor.fetchall()

    for row in rows:
        print("customer_id   : " + str(row[0]))
        print("customer_title: " + row[1])
        print("lname         : " + row[2])

Which returns:

customer_id   : 1
customer_title: Sir
lname         : Doe
let sql = "select customer_id, customer_title, lname from customer where customer_id = 1"
let stmt = try connection.run(sql)

for row in stmt {
    print("customer_id   : \(row[1])") 
    print("customer_title: \(row[2])")
    print("lname         : \(row[3])")
}

Which returns:

customer_id   : 1
customer_title: Sir
lname         : Doe


The customer's first name is wrong, it's Jim and not John. Let's change it with a Prepared Statement:

curl --data "prepared_statement=true" \
--data "param_type_1=VARCHAR&param_value_1=Jim" \
--data "param_type_2=INTEGER&param_value_2=1" \
--data-urlencode "sql=update customer set fname = ? where customer_id = ?" \
https://www.acme.com:9443/aceql/session/mn7andp2tt049iaeaskr28j9ch/execute_update

Which returns:

{
    "status":"OK",
    "row_count":1
}
string sql = "update customer set fname = @fname where customer_id = @customer_id";

using (AceQLCommand command = new AceQLCommand(sql, connection))
{
    command.Prepare();
    command.Parameters.AddWithValue("@customer_id", 1);
    command.Parameters.AddWithValue("@fname", "Jim");

    int rows = await command.ExecuteNonQueryAsync();
    Console.WriteLine("Rows updated: " + rows);
}

Which returns:

Rows updated: 1
String sql = "update customer set fname = ? where customer_id = ?";

try (PreparedStatement prepStatement = connection.prepareStatement(sql);) {
    prepStatement.setString(1, "Jim");
    prepStatement.setInt(2, 1);

    int rows = prepStatement.executeUpdate();
    System.out.println("Rows updated: " + rows);
}

Which returns:

Rows updated: 1
cursor = connection.cursor()
sql = "update customer set fname = ? where customer_id = ?"
params = ("Jim", 1)
cursor.execute(sql, params)
print("Rows updated: " + str(cursor.rowcount))

Which returns:

Rows updated: 1
let  sql = "update customer set fname = ? where customer_id = ?"

let  stmt = try connection.prepare(sql)
try stmt.run("Jim", 1)
print("Rows updated: \(connection.changes())")

Which returns:

Rows updated: 1


And we end with a clean close of our session:

curl https://www.acme.com:9443/aceql/session/mn7andp2tt049iaeaskr28j9ch/disconnect

Which returns:

{                
    "status":"OK"
}                
await connection.CloseAsync();
connection.close();
connection.close()
connection.close()

On the server side, the authentication info is purged and the JDBC connection is released in the pool.


You can now run the Quick Start to quickly test the software.
And you can also check out the documentation to learn how to use all AceQL HTTP options:

  • Error and SQL Exceptions handling.
  • Proxy setting.
  • Timeout setting.
  • SQL Transaction management.
  • Insert BLOB into a remote SQL database.
  • Reading BLOB from a remote SQL database