How to customize a rest-based Service ODBC driver

By Shane Peterson,2015-05-10 17:29
63 views 0
How to customize a rest-based Service ODBC driver

    How to customize a rest-based Service ODBC driver

    REST Service can help developers in a simple uniform interface provide Service to end users.Data analysis application scenario, however, some mature data analysis tools (such as Tableau, Excel, etc.) requires the user to provide the ODBC data source, in this case, the REST Service cannot meet all the user demand for the use of the data.This article from the perspective of implementation was introduced in detail how to existing REST Service, on the basis of finishing the development of a custom ODBC driver.Article focuses on the realization of the ODBC driver principle, combining with the code details the ODBC data interaction between with REST Service, and at the end of the article introduces the principle of ODBC client calls ODBC API, as well as the actual development of the construction of the debugging environment.

    May benefit from the reader

    The current mainstream data analysis tools, such as the Tableau, Microstrategy, can only be ODBC Driver, excel to access to the underlying data source.That is to say, in the process of development and a database or data warehouse, even if we have achieved in line with the SQL standard data access interface, even provides its own JDBC driver, still there is no guarantee that the data users to effectively use our data.To this end, we need additional to customize an ODBC data source Driver.

    If your data is exactly similar mongo, Hbase such common database products, maybe you can consider to purchase some commercial products directly from, for example, Simba ODBC Driver to solve your need, but it will mean a big spending.More difficult is the source of data for you is not the mainstream, haven't any Driver can directly purchase can be applied to it, then customize a own ODBC Driver might be your best choice.Even if you are an ignorant of the ODBC Driver developers, this article will bring you more or less help.

    Our position

    In a nutshell, we use Java development team for a particular SQL engine.At the beginning of the project we have only the JDBC driver and one for the service to the web client REST Server, but we don't have an ODBC driver, so most clients do not really use our products to complete their work.

    In order to solve this problem, we designed the below solution: we use REST Server uniformly accept all from the client's request, including web client and the client using ODBC Driver.REST in the Server using the JDBC driver to access our database.Of course if your client is a Java program, you can directly visit our database through JDBC, thereby saving the steps of overhead.This photo does not show this kind of situation.

    Depth on the client side, we customize a proprietary ODBC Driver, it to the upper application provides the standard ODBC API, encapsulate all implementation logic.On the

    underlying implementation, it calls the REST of the c + + library, send the application to the SQL query encapsulated into a REST request, sent to us the REST of the Server, and after getting the results, and to comply with the ODBC specification, returned to the upper application.

    Starting from the Hello World

    For developers never contact the ODBC, understand an ODBC client behavior can help understand the custom an ODBC driver needs to implement what specific API.In the image below shows a simple ODBC client program implementation, every line of code with detailed comments explaining its behavior, read through the code, it is easy to have an intuitive understanding.In order to simplify the code, we omitted all the error checking code.All SQLXXX format function, is the ODBC API defined standards.

    We will this program is divided into five area, marked as A ~ E, respectively.A zone and B zone, in turn, initializes the three associated with ODBC handles, respectively is:

    ; The Environment handle (hEnv) : contain one or more Connection

    handle.At the same time, some of the global information are included,

    such as ODBC version of the client need, and the diagnostic

    information environment level.

    ; The Connection handle (hConn) : represents a DBMS/data source

    Connection, containing Connection level of information, such as

    Connection timeout, isolation level, and the diagnostic

    information of the Connection level.

    ; Statement handle (hStmt) : it can be seen as a specific query request,

    such as SELECT * FROM employee.

    It is worth mentioning the ODBC specification only defines the data source and how to expose the data access interface, but there is no rules, how to implement this also includes three kinds of the realization of a handle.In fact, the three types in the code handles passing

    through SQLHANDLE type, and SQLHANDLE is essentially a void * type, corresponding structure to our custom.

    ODBC provides a series of C language style for the application of API to support access to the query.Driver is different from an object-oriented language, using ODBC Driver applications need to be returned data memory areas ready ahead of schedule, from this perspective, the task of ODBC is correctly will data to meet the needs of users, moving into the memory area of the user to specify (may have some data transformation, for example, if the application needs to support Unicode, so the ODBC Driver may need to char types of source data into wchar types).Below the initialization in A region of A series of handle and variables, including 307 ~ 305 line on the stack of program opens up such as cache memory area.In fact, in the region of the E, we passed the variables x and I quote, so we can put the 308 ~ 309 lines of two numerical variables such as storage also returns the area of memory.