JsonSqlBridge

JsonSqlBridge is a project I created when I started developing Android applications that had the need to fetch data from a remote MySQL database.

Android has built-in support for SQLite databases which is very useful if you only need local data on the device. If you have the need for accessing data from external databases, one option is to use web-services which provide this data in a format that is readable by the android app. The preferred format in the android world is JSON (JavaScript Object Notation) which is a very lightweight format for data exchanging.

When I started making android apps, I had the need to have a webservice fetching data from the MySQL server and return it in JSON format. I could have just install Apache+PHP webserver and create a simple PHP script for this but I wanted a much easier aproach for people who want to focus on the client apps first.
This project is a modified version of my original one to allow running full SQL queries instead of just some predefined methods.

Screenshot

Download

JsonSqlBridge (4.21 MB)

What is it?

It is an HTTP server which accepts queries in SQL format, executes them on the SQL server and returns the result in Json format to the HTTP client.

It only works on Windows and you can run it as standalone application or as a Windows Service (check the batch files).

It supports caching of requests. You define the cache time to live in settings.ini.

It supports basic HTTP authentication. You define the username and password in settings.ini.

SQL Servers supported :

  • MySQL 3.20 – 5.0
  • PostgreSQL 6.5 – 8.1
  • Firebird 1.0 – 2.0
  • Interbase 5.0 – 7.5
  • Microsoft SQL Server 7, 2000
  • Sybase ASE 12.0, 12.5
  • Oracle 9i
  • SQLite 2.8, 3.5
  • ADO Connections

How to use?

– Download and extract the contents of the file.

– Edit “settings.ini” and configure the server settings and the SQL connection.

– Run “JsonSqlBridgeApp.exe”.

– Test the server with the browser using the IP:PORT url. (ex: http://127.0.0.1:8081?id=myquery&query=select * from table)

– For each request it is required to send 2 parameters :

  • id is an identifier for the query, this is useful if your run multiple queries at the same time and need to identify the results. The returned JSON array and cache will be identified by this ID.
  • query is the SQL query that will be executed on the database and the results returned as a JSON array.

– You can use both GET and POST methods but POST is preferred.

Protocols and Libraries?

In order to sucessfully connect to the SQL server, you need to setup your connection to the correct protocol and you need the corresponding libraries.

To configure the protocol, you must edit the SqlProtocol key inside settings.ini.

Possible protocols are (according to the compiled ZeosLib) :

ado, mssql, sybase, mysql, mysql-4.1, mysql-5, mysqld-4.1, mysqld-5, postgresql, postgresql-7, postgresql-8, postgresql-9, interbase-6, firebird-1.0, firebird-1.5, firebird-2.0, firebird-2.1, firebird-2.5, firebirdd-1.5, firebirdd-2.0, firebirdd-2.1, firebirdd-2.5, sqlite, sqlite-3, oracle, oracle-9i, ASA7, ASA8, ASA9, ASA12

To use a protocol you must have the correct libraries on your system or the same folder as JsonSqlBridge, or else you will get an error like this (it´s for MS SQL) :
None of the dynamic libraries can be found or is not loadable: ntwdblib.dll !
Use TZConnection.LibraryLocation if the location is invalid.

By default, the server is configured for MySQL and the library is included.
For other protocols, you must get the correct libraries.

 

 

One Response to JsonSqlBridge

  1. Pavel says:

    Thank you. Useful thing for mobile development ! But it has some problems:
    First – with char encoding. I tried to run it with Firebird 2.5 server. Database with utf-8 charset and russian chars. Application returns “?” Instead chars. Is there any parameters in the settings.ini to resolve problem with char coding ?
    And second problem – return nothing (no name, no value) when value is null. Android json causes an error in that case.

Leave a Reply

Your email address will not be published. Required fields are marked *


× one = 9

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre lang="" line="" escaped="" cssfile="">