How-to Guides‎ > ‎

Connecting to a Polyhedra server

This article looks at various ways by which user-coded application can connect to – and interact with – a running Polyhedra database. It contains sample application code to query the database, using various APIs and written in various languages. It is not intended to be a tutorial on Polyhedra or the various APIs or the languages involved, but instead is intended to act as a taster to allow readers to get an idea of what is involved in using the various interfaces.

(When using 3rd-party tools you will not normally have the option of rebuilding it to make direct use of Polyhedra libraries, but it is often possible to access Polyhedra databases via our ODBC, OLE or JDBC drivers. There is a separate page giving details on how some of these tools can be used with Polyhedra.)

Accessing a Polyhedra database

Before describing how Polyhedra – or, more accurately, a database being managed by an active Polyhedra database server or a fault-tolerant pair of servers – can be accessed, we shall first summarise the various APIs that are available for this purpose. These are:
  • ODBC API - This API contains all of the active query management to interface a client application to any Polyhedra server. It is based on the standard ODBC programming interface, but with extensions to better support the event-driven nature of Polyhedra.
On all platforms, ODBC client applications can be linked with the Polyhedra library directly, allowing them to make use of some Polyhedra-specific extensions to ODBC; alternatively, on Windows and selected Linux platforms the Polyhedra library is available as an ODBC driver - in this case, the client applications would be linked with the corresponding ODBC driver manager, and thus would not be able to invoke the Polyhedra-specific extensions to ODBC.
  • JDBC API – this allows the Java programmer to access a Polyhedra database. It uses the Polyhedra native protocol over TCP/IP to communicate with the Polyhedra Database Server.
  • Callback Client API - This API contains all of the active query management to interface to any Polyhedra server. This API supports active and static queries for both SQL and direct object queries. It is based on the callback model of programming (hence its name).
  • ADO.NET - For use by .NET programmers, an ADO.NET data provider for Polyhedra is available, which allows access to much of the functionality of the callback API including active queries. (NB - the ADO.NET data provider for Polyhedra is only provided to those with development licenses for Polyhedra on Windows platforms and a current support contract, and can only be used to connect to Polyhedra 8.6 and onwards.)
(In addition, there are a number of special-purpose APIs that are collectively referred to as the Polyhedra embedded API; these are documented in the 'Polyhedra on embedded systems' reference manual. These APIs can be used to 'fine-tune' the functionality of the Polyhedra database server, by providing alternative definitions of certain functions (and thus allow file-system calls to be intercepted, for example) or to provide tuning parameters.)

The following sections contain some code fragments; in each case, unless otherwise stated it is assumed that the Polyhedra database server has already been started on the local machine, is listening for client connections on TCP/IP port 8001, and is running the standard example database that is included in the release kits (and described in section 2.3 of the evaluation guide accompanying the release kits. In particular, this means there is a populated table with the following definition:

create table currency
( code large varchar primary key
, country large varchar
, name large varchar
, usdollar real
);

In addition, some examples assume that the Polyhedra ODBC driver has been installed, and the local driver manager has been configured so that the DSN 8001 has been set up to access a local Polyhedra server that is listening on port 8001. Some information on installing and configuring the Polyhedra ODBC driver on various platforms can be found in Appendix A, below. By adapting the DSN, the examples can be configured to connect to Polyhedra servers operating on different ports, and to servers running on different machines: it is the job of the Polyhedra libraries and drivers to care care of issues such as differences in endianism - also, as Polyhedra is fully client-server, 32-bit client applications can talk to 64-bit versions of Polyhedra, and vice versa.

While we have tried to make sure the code presented here is correct, in some cases the error handling has been omitted for simplicity and no warranties are offered. In the case of any conflict between what is presented here and what is in the Polyhedra reference manuals, the latter prevail! With those provisos, feel free to use the code samples in your Polyhedra-based applications.

Polyhedra from C or C++

The C/C++ programmer has the greatest choice of ways of accessing Polyhedra. In practice, though, the choice comes down to one of four: the callback API, direct use of the Polyhedra ODBC API, the use of an ODBC driver manager in conjunction with a Polyhedra ODBC driver, and the use of the ADO.NET data provider for Polyhedra. The advantages and disadvantages of each are summarised in the table below:

API Availability Advantages Disadvantages

Callback API

All Polyhedra platforms
(not available for Polyhedra Lite)
  • Uses callback model of program, handles Polyhedra events in a natural manner
  • Can use less RAM client-side than the ODBC-based solutions
  • Supports SQL prepare and execute semantics, so can be very efficient
  • Uses callback model of programming, which can be difficult to adopt and can lead to code that is difficult to debug and maintain
  • Proprietary

Direct use of Polyhedra ODBC 3.5 libraries

All Polyhedra platforms
  • Standards-based, so adoption costs are lower
  • Supports SQL prepare and execute semantics, so has highest efficiency
  • Has extensions allowing it to be used in an event-driven fashion, and to tune behaviour
  • Holds a copy of the result set for a query client-side, so can use more space than an application using the Callback AP
  • Client applications cannot talk via ODBC to databases held by a different DBMS
  • Does not support ODBC2 calls that were removed in ODBC3 spec

Using Polyhedra ODBC 3.5 driver via a driver manager
  • Windows
  • selected Linux platforms
  • Standards-based, so adoption costs are lower
  • Supports SQL prepare and execute semantics, so has high efficiency
  • Client applications can simultaneously connect to databases held by a different DBMS
  • Driver managers will map ODBC2 calls to equivalent ODBC3 calls
  • Holds a copy of the result set for a query client-side, so can use more space than an application using the Callback API
  • Not as efficient as using the Polyhedra ODBC library directly, as all calls indirect via the driver manager

Using the ADO.NET data provider for Polyhedra

Windows only

(not available for Polyhedra Lite)
    • The natural interface to use for those developing .NET applications, especially  when wanting to use other .NET assemblies
    •  the data provider can be register for use by the DbProviderFactory, making it easy to develop .NET applications that interact with Polyhedra database(s).
    • Not as efficient as using the callback API directly
    • lack some functionality that is available via the callback API

    In general, Enea recommend direct use of the Polyhedra ODBC libraries as it is easier to use than the callback API, and, when compared to the ODBC driver option, it is more efficient and also allows access to Polyhedra-specific extensions. For ultimate performance, though, the proprietary callback API is best; the disadvantage is that the callback model of programming is less intuitive and can be harder to maintain.

    The following program uses the ODBC API to query the currency table in the database whose DSN is "8001":

    /* On Windows we need to include windows.h */
    #if defined(WIN32)
    #include <windows.h>
    #endif

    /* Include standard header files */
    #include <stdlib.h>
    #include <stdio.h>

    /* Include the ODBC header files */
    #include <sql.h>
    #if !defined(ODBC_STD)
    #include <sqlext.h>
    #endif

    // Error-check function, which will called after most of the ODBC
    // function calls;
    if the ODBC function call failed, this function will
    // generate a brief error report and
    then kill the program. A more complete
    // version of this function, which uses ODBC calls to retrieve more
    // information about the reason for the failure, can be found in the ODBC
    // examples in the Polyhedra release kits.


    void check_success (int ret, SQLSMALLINT handleType,
                        SQLHANDLE handle,const char * errorMsg)

    {
       if (ret != SQL_SUCCESS)
       {
          fprintf(stderr, "%s\n", errorMsg);
          exit(1);
       }
    }

    // main function

    int main ()
    {
       SQLRETURN ret;
       SQLHENV henv;
       SQLHDBC hdbc;
       SQLHSTMT hstmt;

       /* Allocate an environment handle */

       ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
       check_success(ret, SQL_HANDLE_ENV, henv,
                     "Failed to allocate environment handle");


       #if !defined(ODBC_STD)
       /* Set the ODBC version used */
       ret = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                           (SQLPOINTER)SQL_OV_ODBC3, 0)
    ;

       check_success(ret, SQL_HANDLE_ENV, henv,
                     "Failed to set ODBC version");

       #endif

       /* Connect to the database */
       ret = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
       check_success(ret, SQL_HANDLE_ENV, henv,
                     "Failed to allocate connection handle");

       ret = SQLConnect(hdbc, (SQLCHAR *)"8001", SQL_NTS, 0, SQL_NTS, 0, SQL_NTS);
       check_success(ret, SQL_HANDLE_DBC, hdbc,
                     "Failed to connect to the database");


       /* Execute the query */
       ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
       check_success(ret, SQL_HANDLE_DBC, hdbc,
                     "Failed to allocate a statement handle");

       ret = SQLExecDirect(hstmt,
                           (SQLCHAR *)"select code, usdollar from currency",
                           SQL_NTS)
    ;

       check_success(ret, SQL_HANDLE_STMT, hstmt, "Failed to perform query");

       /* Display the result set by fetching a row at a time */
       while ((ret = SQLFetch(hstmt)) == SQL_SUCCESS)
       {
          Char code[4];
          Double usdollar;
          SQLLEN length;

          ret = SQLGetData(hstmt, 1, SQL_C_CHAR, &code, sizeof(code), &length);
          check_success(ret, SQL_HANDLE_STMT, hstmt,
                        "Failed to get value of code column");

          ret = SQLGetData(hstmt, 4, SQL_C_DOUBLE,
                           &usdollar, sizeof(double), &length)
    ;

          check_success(ret, SQL_HANDLE_STMT, hstmt,
                        "Failed to get value of usdollar column");


          printf("Currency code: %s: 1 US Dollar buys %0.2lf.\n", code, usdollar);
       }

       /* tidy up (ignoring any error responses) */
       ret = SQLCloseCursor(hstmt);
       ret = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
       ret = SQLDisconnect(hdbc);
       ret = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
       ret = SQLFreeHandle(SQL_HANDLE_ENV, henv);

       return 0;
    }


    The above code is a slightly-simplified version of the 'query' example supplied in source-code form in Polyhedra release kits, with the API calls in bold. As it does not use any Polyhedra-specific extensions to ODBC, the above program can either be linked directly with the Polyhedra ODBC libraries (using the platform-specific instructions in the Polyhedra release kits) or (for selected platforms) it can be linked with an ODBC driver manager and then used in conjunction with a Polyhedra ODBC driver (as described later); however, unless you are wanting the ability to use the same program to access a non-Polyhedra database, we usually recommend dirtectly linking to our libraries for performance reasons.

    For comparison, here is an equivalent program using Polyhedra’s proprietary callback API:

    #include <stdio.h>

    #include <appapi.h>
    #include <timerapi.h>
    #include <clntapi.h>
    #include <queryapi.h>
    #include <transapi.h>


    void Print_Error(int err,const char *location)
    {
        fprintf(stderr,"Error in %s: code %d\n", location, err);
    }

    // Define a class to keep track of what is happening: we will have just one
    // of these.
    It defines the various (static) functions that the callback api
    // will be calling
    to signal that it has done its work.


    class StaticQuery
    {
        ClientAPI* Client; // Client instance
        QueryAPI* Query;   // Query instance


    public:
        AppAPI* App;

        StaticQuery ()
        {
           Client = 0;
        };

        ~StaticQuery () {};
        void Finish();

        void Report_and_Finish (int err, const char *location)
        {
           Print_Error(err, location)
           Finish();
        }

        // ***** Define the various callback methods. *****

        // Activate is called when the scheduler is started. It calls StartConnect,
        // and CB_Connect is called when the the connection to the database is made.
        // the query is started inside CB_Connect. CB_Row is called once for each row
        // returned by the query. CB_QueryDone is called when all rows have been returned,
        // and it closes the connection.
       
    // CB_DeleteDone is called once the connection is closed.


        static const int Activate       (void *userdata);
        static const void CB_Connect    (void *userdata);
        static const void CB_Row        (void *userdata);
        static const void CB_QueryDone  (void *userdata);
        static const void CB_DeleteDone (void *userdata);
    };

    const int StaticQuery::Activate (void *userdata)

    // Called by the client API scheduler once it has been granted control.
    // Returns 0 to prevent the scheduler calling it again.
    // The argument is cast back to an StaticQuery pointer, and a connection request is set up.
    {
        StaticQuery *context = (StaticQuery *)userdata;
        context->Client = ClientAPI::StartConnect ( context->App,
                                                  "8001",
                                                  StaticQuery::CB_Connect,
                                                  context);
        return 0; // don't call me again.
    }

    const void StaticQuery::CB_Connect(void *userdata)
    // Called when a connection to the database has been established (or the attempt failed).
    // If the connection worked, we just launch the query, telling it which functions are
    // to be called back later.
    {
        StaticQuery *context = (StaticQuery *) userdata;
        res = ClientAPI::GetError (context->Client);

        if (res != 0)
            context->Report_and_Finish (res, "connect to database");
        else
           context->Query = QueryAPI::StartQuery ( context->Client,
                                               "select code, usdollar from currency",
                                               StaticQuery::CB_Row,
                                               StaticQuery::CB_QueryDone,
                                               context);
    }

    const void StaticQuery::CB_Row(void *userdata)
    // This is called once for each row returned by the query.
    // Each column is accessed using the CopyColumn function.
    {
        StaticQuery *context = (StaticQuery*) userdata;
        res = QueryAPI::GetError(context->Query);

        // Variables to hold column values
        char code[4]; // Currency code
        double d_val; // US dollar value

        if (res != 0)
           context->Report_and_Finish (res, "retrieving result set");
        if (QueryAPI::CopyColumn( context->Query, "code", code, 4) == 0)
             code[0]='\0';
        if (QueryAPI::CopyColumn(context->Query, "usdollar", &d_val, sizeof(double)) == 0)
             d_val=0;
        printf("Currency code: %s: 1 US Dollar buys %0.2lf.\n", code, d_val);
    }

    const void StaticQuery::CB_QueryDone(void *userdata)
    // This callback is called when the query has completed (or failed).
    // Either way, it is time to disconnect, using our Finish method
    {
        StaticQuery *context = (StaticQuery*) userdata;
        int res = QueryAPI::GetError(context->Query);
        if (res != 0)
               context->Report_and_Finish (res, "query done");
        else
               context->Finish();
    }

    void StaticQuery::Finish()
    // called to disconnect the client, if we are connected – if not, just jump ahead
    {
        if (Client)
               ClientAPI::DeleteClient(Client, StaticQuery::CB_DeleteDone, this);
        else
               StaticQuery::CB_DeleteDone(this);
    }

    const void StaticQuery::CB_DeleteDone(void *userdata)
    // This callback is called when the client has been deleted, or Finish
    //
    was called with no client connection set up. As this is the end of 
    //
    this examplethe AppAPI::Stop method is called.
    {
        StaticQuery *context = (StaticQuery*) userdata;
        AppAPI::Stop(context->App);
    }

    extern "C" int main ()
    // Main - this is the entry point into the program.
    {
        int res;
        // initialise the client api:
        if (res = AppAPI::Init())
        {
         Print_Error(res,"initialisation of API");
              return 10;
        }
        // create our monitoring object:
        StaticQuery* context = new StaticQuery();
        // create an AppAPI object for use by the inbuilt scheduler in the Polyhedra library
        context->App = AppAPI::Create();

        // pass control to the Polyhedra Callback API, telling it the first function to call.
        AppAPI::Start(context->App, StaticQuery::Activate, context);

        // control has been returned: tidy up and quit.
        AppAPI::Delete(context->App);
        delete context;
        AppAPI::Tidy();
        return 0;
    }

    As with the ODBC example, the above is a simplified version of an example that is supplied in source-code form in the Polyhedra release kits.

    Polyhedra from Java

    Java programmers can use of the Polyhedra JDBC library to access Polyhedra databases. This is a 'type-4' library, written purely in Java, and so can be used on any machine with a Java engine that implements JDBC (which is all the standard Java engines).

    The release kit also contains a second set of class definitions, for use with Java engines such as CDLC that do not support JDBC. This library is also written entirely in Java, and has equivalent functionality to the Polyhedra JDBC library. Here is a small java program that uses JDBC to query the currency table; as there is no need to set up 'handles' before invoking SQL, and as error handling is done by throwing exceptions, the code is much shorter than the equivalent ODBC-coded example:

    import java.sql.* ;

    public class query
    {
        public static void main(String[] argv)
        throws SQLException, ClassNotFoundException, InstantiationException,
               IllegalAccessException, InterruptedException
       {
           // load Polyhedra JDBC Driver; this automatically
           // registers it with JDBC Driver Manager

           // (note: the newInstance() is required for Visual J++)
           Class.forName("com.polyhedra.jdbc.JdbcDriver").newInstance() ;

           java.util.Properties prop = new java.util.Properties() ;
           String url = "jdbc:polyhedra://:8001" ;

           // perform connection
           Connection conn = DriverManager.getConnection(url, prop) ;

           // get result set
           String sql = "select code,usdollar from currency" ;
           Statement statement = conn.createStatement () ;
           ResultSet resultSet = statement.executeQuery (sql) ;

           // print result set
           for (int row = 0 ; resultSet.next() ; row++)
              System.out.println ( "Currency code: " + resultSet.getString (1) +
                                  
    ": 1 US Dollar buys " + resultSet.getString (2) ) ;

           // close jdbc objects
           resultSet.close() ;
           statement.close() ;
           conn.close() ;
        }
    }


    The Polyhedra JDBC library supports the Driver, Connection, Statement, PreparedStatement, ResultSet, ResultSetMetaData and DatabaseMetaData interfaces defined in JDBC 2.0, with some limitations. It allows both scrollable and updateable result sets, and interprets TYPE_SCROLL_SENSITIVE as a request for an active query. Although the library does not provide an event mechanism that would allow a program to wait for an incoming update message, one can periodically iterate over the result set and use the use the rowInserted(), rowUpdated() and rowDeleted() methods of the ResultSet class to discover which rows were recently altered. Testing the result set in this way does not involve a round trip to the server; instead, when the cursor is repositioned at the start of the result set, the Polyhedra library checks to see whether an update message has been received from the server. (The Polyhedra release kits contain the source code for a Java application that handles an active query in this way; as with all example code supplied with Polyhedra or made available at the developer site, you are free to adapt this example for your own Polyhedra-based application.)

    Aside: Polyhedra on Android

    The Polyhedra JDBC library can be used on Android platforms; however, there are a few points you will have to note. Assuming you are using Eclipse+ADT as your IDE for developing Android applications, then:
    • You will have to change the extension of the Polyhedra JDBC library file from .zip to .jar. (You can compile code against the .zip but it will not be included in the Android package file unless the extension is .jar.)

    • You will have to include the JDBC in your project - either by referencing where it is on your file system, or by dragging and dropping it into your project. You then add it to the build path either via project->properties->java build path->Libraries->add external jars for the former, or ...->add jars for the latter.

    • You will have 'export' the driver to the android package to ensure it is included in it. Go to the Order and Export tab and check its box there.

    • To give the JDBC permission to use TCP/IP networking you need to open the AndroidManifest.xml file and click on the permissions tab. Click on add->uses permission and select android.permission.INTERNET from the drop-down menu.
    • Calls to the JDBC can't be done in the UI thread (you will get an exception). Android has the useful AsyncTask class which packages creating new threads to perform tasks in the background, with functions to access the UI that then run in the foreground UI.

    Polyhedra from PHP

    PHP was primarily designed as a language for embedding in HTML pages, for execution server-side. When the server reads the page, if it encounters the tags denoting a piece of PHP code, the code fragment would be executed and the output would be substituted in the HTML stream in place of the PHP code. As a result, the page supplied by the browser in response to a request is dynamic, and can hold, for example, personalised content.

    Depending on how it has been built, the PHP engine will provide functions for querying and updating databases. As well as specific functions for talking to certain database products, there is a more generic set of functions for talking to databases via ODBC. Thus, if the PHP installation on a particular machine has been built to work with an ODBC driver manager, then you can call PHP functions such as odbc_connect(), odbc_exec() and odbc_fetch_row() as illustrated by the following PHP program:

    <?php
    $database = "8001";
    $conn = odbc_connect ("$database", "", ""); // (args 2 and 3 are name, passwd)
    if ($conn)
    {
       $result = odbc_exec ($conn, "select code, usdollar value from currency");
       echo "<table cellpadding=\"2\" cellspacing=\"2\">\n<tr bgcolor=\"steelblue\"> ";

       // print field names
       $colCt = odbc_num_fields ($result);
       for ($j=1; $j<=$colCt; $j++)
         echo "<th> " . odbc_field_name ($result, $j) . " </th>";
       echo "</tr>\n";

       // scan the result set
       while (odbc_fetch_row ($result))
       {
         echo "<tr bgcolor=\"lightskyblue\">";
         for ($i=1; $i<=$colCt; $i++)
         echo "<td> " . odbc_result ($result,$i) . " </td>";
         echo "</tr>\n";
       }
       echo "</table>\n";
       odbc_close ($conn);
    }
    else echo "\nERROR: could not connect to database \"" . $database . "\".\n\n";
    ?>

    Assuming the ODBC driver has been told that 8001 is a DSN associated with a locally-installed Polyhedra ODBC driver, with a suitable service name, then – when using the standard Polyhedra currencies database – the result of running the above PHP program will be an HTML fragment that defines a table:


    <table cellpadding="2" cellspacing="2">

    <tr bgcolor="steelblue"> <th> code </th><th> value </th></tr>
    <tr bgcolor="lightskyblue"><td> GBP </td><td> 0.67 </td></tr>
    <tr bgcolor="lightskyblue"><td> EUR </td><td> 0.81 </td></tr>
    <tr bgcolor="lightskyblue"><td> CHF </td><td> 1.67 </td></tr>
    <tr bgcolor="lightskyblue"><td> CAD </td><td> 1.49 </td></tr>
    <tr bgcolor="lightskyblue"><td> AUD </td><td> 1.72 </td></tr>
    <tr bgcolor="lightskyblue"><td> JPY </td><td> 109.5 </td></tr>
    </table>

    code value
    GBP 0.67
    EUR 0.81
    CHF 1.67
    CAD 1.49
    AUD 1.72
    JPY 109.5

    (For more information about the PHP’s ODBC functions, see www.php.net/manual/en/book.uodbc.php.)

    Aside – building PHP with UnixODBC

    While most PHP packages for Linux come with support for UnixODBC, on some platforms it is necessary to rebuild PHP to achieve this. There are some instructions on http://www.unixodbc.org/doc/php3.html that can assist with this, but much of this is to do with Apache; if you are just wanting PHP, the following should suffice:
    • Create a file in /usr/local/include called odbc.h containing the following three lines:
    #include <sql.h>
    #include <sqlext.h>
    #include <odbcinst.h>
    • Download a copy of the PHP source code, unpack it, and move to the PHP directory.
    • Define the following environment variables:
    CFLAGS="-I/usr/local/include"
    LDFLAGS=
    CUSTOM_ODBC_LIBS="-L/usr/local/lib -lodbc"
    • Configure PHP with -with-custom-odbc=/usr/local, plus any other local configuration options you need:
    ./configure –with-custom-odbc=/usr/local
    • Finally, run...
    make

    Please note that as described in Appendix A, unixODBC comes with its own copies of sql.h, sqlext.h, sqltypes.h and sqlucode.h that are normally automatically installed in /usr/include; do not install the Polyhedra versions in a place such as /usr/local/include that would mean they would be used in place of the unixODBC versions when compiling PHP.

    Polyhedra from Python

    Python is a computer language for which interpreters exist for a large number of platforms. For Windows, you can obtain installation kits from www.python.org; you can also download source code, and installation kits for Linux – though many Linux distributions include it by default.

    At present there is no Polyhedra-specific module for connecting to a Polyhedra database. However, on Windows and many Linux platforms, Enea make available an ODBC driver, and this can be used in conjunction with the free third-party pyodbc module to allow Python programs to interact with a Polyhedra database. So if the file query.py contained the following…

    import pyodbc
    cnxn = pyodbc.connect('DSN=8001')
    cursor = cnxn.cursor()
    for row in cursor.execute("select code, usdollar from currency"):
       
    print ('Currency code', row.code, '- 1 US Dollar buys', row.usdollar)

    … then the program could be invoked under the Python3 interpreter using the command python query.pi, and (depending on the contents of the database!) the output could look like…

    Currency code GBP - 1 US Dollar buys 0.67
    Currency code EUR - 1 US Dollar buys 0.81
    Currency code CHF - 1 US Dollar buys 1.67
    Currency code CAD - 1 US Dollar buys 1.49
    Currency code AUD - 1 US Dollar buys 1.72
    Currency code JPY - 1 US Dollar buys 109.5

    A second Python example

    This second example uses both pyodbc (to speak to Polyhedra) and also pygame; the latter is used to control a screen. The application connects to the 'traffic lights' database used in a Polyhedra video demonstrating fault tolerance, and shows a graphic representation of the expected state of some traffic lights. In the version given below, it assumes it is running on a Raspberry Pi fitted with an Adafruit PiTFT 2.8" screen, and it treats one of the buttons on the device as a signal to exit the application. It has been tested running against a local Polyhedra server and also against a fault-tolerant database service running on a pair of Beaglebone Black computers.

    import pygame, sys
    from pygame.locals import *
    from time import sleep
    import pyodbc
    import argparse

    # ----------------------------------------------------------------------------
    # define a class to manage an individual light within a standard
    # red/amber/green traffic light; the light will be told its position, and
    # periodically the check function will be called with information about a
    # row in the light table in the database. The check function should return
    # true only if the the row is 'mine', and the value differs from what has
    # been told (in which case the object draws a circle of the appropriate colour
    # at the right position, but does not refresh the screen).
    # ----------------------------------------------------------------------------

    class light:
          def __init__(self, id, colour):
              self.id       = id
              self.colour   = colour
              self.state    = True
              self.position = []

          def check(self, id, state):
              if id != self.id:
                 return False
              elif state == self.state:
                 return False
              else:
                 self.state = state
                 self.draw()
                 return True
                
          def draw(self):
              if self.state:
                  pygame.draw.circle(window, self.colour, self.position,
                                     int(lightsize*0.45), 0)

              else:
                  pygame.draw.circle(window, (10,10,10), self.position, int(lightsize*0.45), 0)

    # ---------------------------------------------------------------------------
    # define a class to manage a traffic light box; it will have three associated
    # objects representing the individual lights within the traffic light.
    # ---------------------------------------------------------------------------

    class trafficlight:
          def __init__(self, id, position, red, amber, green, no):
              self.id = id
              self.position = position
              self.red      = light (red,   (255,  0,0))
              self.amber    = light (amber, (255,200,0))
              self.green    = light (green, (  0,255,0))
              self.no       = no


          def check(self, id, state):
              return self.red.check (id, state) or self.amber.check (id, state) or self.green.check (id, state)

          def draw(self, window, ct):
              #
              # position myself in the window, and draw. I am <no> of <ct>
              #
              w = int(size[0]/ct)
              self.box = Rect(int((self.no-1)*w+(w-lightsize)/2),
                              int((size[1]-3*lightsize) / 2),
                              lightsize, lightsize*3)

              self.red.position   = (int(self.box.left+lightsize/2),
                                     int(self.box.top+lightsize/2))

              self.amber.position = (int(self.box.left+lightsize/2),
                                     int(self.box.top+(3*lightsize)/2))

              self.green.position = (int(self.box.left+lightsize/2),
                                     int(self.box.top+(5*lightsize)/2))

              window.fill(black, self.box)
              self.red.draw()
              self.amber.draw()
              self.green.draw()

    # ----------------------------------------------------------------------------
    # initial steps
    # ----------------------------------------------------------------------------

    # check the arguments and connect to the database

    parser = argparse.ArgumentParser(description='Display traffic lights')
    parser.add_argument('DSN', nargs='?', default="8001", help='DSN of database to query')
    args = parser.parse_args()
    cnxn = pyodbc.connect('DSN=' + args.DSN)

    pygame.init()

    # query the trafficlight table, and create some matching trafficlight objects.

    cursor = cnxn.cursor()
    ct     = 0;
    lights = []
    for row in cursor.execute("select id, position,red, amber,green from trafficlight"):
        ct = ct+1
        lights.append (trafficlight (row.id, row.position, row.red, row.amber, row.green, ct))

    # Draw background and hide the mouse

    black  = (0,0,0)
    cream  = (254,255,250)
    size   = (320,240)
    window = pygame.display.set_mode(size)
    window.fill(cream)
    pygame.mouse.set_visible (False)

    # draw the boxes, all lights on (as their default state is True)

    lightsize = min (0.3*size[1], 0.9*size[0]/ct)
    for lightbox in lights:
        lightbox.draw(window,ct)
    pygame.display.update()

    # ----------------------------------------------------------------------------
    # main loop
    # ----------------------------------------------------------------------------

    while True:
        altered = False
        for row in cursor.execute("select id, value from light"):
            for lightbox in lights:
                if lightbox.check(row.id, row.value):
                   altered = True
        if altered:
            pygame.display.update()
        sleep (0.05)


    (and here is a photo of the application in action!)

    Aside: installing pyodbc on Windows

    Pyodbc is a free 3rd-party module for Python. At time of writing (April 2013), the official home or pyodbc was http://code.google.com/p/pyodbc/, and from there you can download various versions, to match the version of Python you have installed – though not all combinations are possible, and you will also need to be careful that everything is 32-bit or everything 64-bit; in practice, we suggest you use the 32-bit Polyhedra ODBC driver, Python 2.7, and a 32-bit version of pyodbc that is built for use with Python 2.7. (If you want to experiment with different builds of pyodbc, you can download the source and compile it for yourself, or you could try downloading – at your own risk – a pre-built kit from http://www.lfd.uci.edu/~gohlke/pythonlibs/#pyodbc)

    Aside: pyodbc on Linux

    Assuming you have already installed unixODBC and its development tools, it is possible to download the source code for pyodbc and compile it for yourself. However, it is already supported on various distributions, even though it does not get installed by default. On the Raspberry Pi, for example, you can just issue the following command

    sudo apt-get python-pyodbc

    This installs pyodbc for use with Python 2, which is what is run if you issue the command python on this platform. If you want to use Python3 rather than Python2, then you will need to rebuild pyodbc, which on this platform is relatively straightforward:

    sudo apt-get install python3-dev unixodbc-dev git
    git clone https://code.google.com/p/pyodbc/
    cd pyodbc
    python3 setup.py build
    sudo python3 setup.py install

    (Please note that as described in Appendix A, unixODBC comes with its own copies of sql.h, sqlext.h, sqltypes.h and sqlucode.h that are normally automatically installed in /usr/include; do not install the Polyhedra versions in a place such as /usr/local/include that would mean they would be used in place of the unixODBC versions when compiling pyodbc.)

    (When both Python2 and Python3 are installed on a machine along with the corresponding pyodbc modules, it becomes readily apparent that the two versions of Python can produce different results even on simple programs:

    pi@raspberrypi ~ $ python query.py
    ('Currency code', 'GBP', '- 1 US Dollar buys', 0.67)
    ('Currency code', 'EUR', '- 1 US Dollar buys', 0.81)
    ('Currency code', 'CHF', '- 1 US Dollar buys', 1.67)
    ('Currency code', 'CAD', '- 1 US Dollar buys', 1.49)
    ('Currency code', 'AUD', '- 1 US Dollar buys', 1.72)
    ('Currency code', 'JPY', '- 1 US Dollar buys', 109.5)
    pi@raspberrypi ~ $ python3 query.py
    Currency code GBP - 1 US Dollar buys 0.67
    Currency code EUR - 1 US Dollar buys 0.81
    Currency code CHF - 1 US Dollar buys 1.67
    Currency code CAD - 1 US Dollar buys 1.49
    Currency code AUD - 1 US Dollar buys 1.72
    Currency code JPY - 1 US Dollar buys 109.5
    pi@raspberrypi ~ $

    In python2, print was a statement, but in python3 it was altered to be a function – this reduced the need for lots of special syntax, but as you can see it broke backwards compatibility.)

    Polyhedra from .NET

    Using the ADO.NET data provider for Polyhedra

    One of the headline features of Polyhedra 9.0 was the introduction of a new module, an ADO.NET data provider that can take advantage of Polyhedra-specific features such as active queries. The following C# program shows how this new module can be used to query the currency table:

    using Polyhedra.Data.PolyClient;
    using System;
    using System.Data;
    using System.Data.Common;

    namespace Polyhedra.Data.PolySample
    {
      public class PolyQuery
      {
        public static void Main()
        {
          PolyQuery polyQuery = new PolyQuery();
          polyQuery.Run();
        }

        private void Run()
        {
          // Build a connection string and connect to the database
          PolyConnectionStringBuilder builder =
                            new PolyConnectionStringBuilder();

          builder.Service = "8001";
          PolyConnection connection =
                            new PolyConnection(builder.ConnectionString);

          connection.Open();
          PolyCommand command =
                   new PolyCommand("select code, usdollar from currency",
                                   connection)
    ;

          PolyDataReader reader = command.ExecuteReader();

          while (reader.Read())
          {
            Console.WriteLine("Currency code: {0}: "
                              "1 US Dollar buys {1:F2}.",

                              reader["code"], reader["usdollar"]);

          }
          reader.Close();
          connection.Close();
        }
      }
    }

    A more complete version of the above, including some error checking and the ability to specify via the command line the DSN of the server to which to connect, can be found in source code form in the Polyhedra release kits. There are a number of other C# examples in the release kits, including two that illustrate the use of active queries; one of these can be seen in action in a webcast.

    The reference manual for the ADO.NET data provider for Polyhedra describes how the Polyhedra ADO.NET Data Provider assembly can be installed into the Global Assembly Cache (GAC) if you want the assembly to be shared by several applications, or if you want to restrict permissions using the PolyPermssion class. The manual also described how the data provider can be registered so that it can be used with the DbProviderFactory class.

    One limitation of the data provider is that it cannot be used to connect to database servers using a version of Polyhedra earlier than release 8.6 (which is when support for 64-bit integer types was introduced). If you need to be able to connect to older versions of the database server from a C# program, you will have to adopt the approach outlined in the next subsection.

    Using the .NET Framework Data Provider for ODBC

    Prior to the release of Polyhedra 9.0 there was not a specific .NET data provider for Polyhedra, but if the Polyhedra ODBC driver for Windows has been installed on a machine then a .NET program could access Polyhedra via Microsoft’s ".NET Framework Data Provider for ODBC". You can consider this a wrapper around the standard Windows ODBC driver manager to make it accessible to the .NET programmer, so you would have needed to install the Polyhedra ODBC driver on each machine on which you wanted to run your .NET program (even if the database being accessed is on a different machine) and configure a DSN. Here is a sample C# program that uses this approach (again omitting some of the checking code in the previous example for brevity):

    using System;
    using System.Data;
    using System.Data.Odbc;

    class Sample
    {
      public static void Main()
      {
        OdbcConnection nwindConn = new OdbcConnection("DSN=8001;");
        OdbcCommand catCMD =
                  new OdbcCommand("select code, usdollar from currency",
    nwindConn);
        nwindConn.Open();
        OdbcDataReader myReader = catCMD.ExecuteReader();
        while (myReader.Read())
        {
          Console.WriteLine("Currency code {0} - 1 US Dollar buys {1}",
                            myReader.GetString(0), myReader.GetString(1));

        }
        myReader.Close();
        nwindConn.Close();
      }
    }

    As the .NET Framework Data provider for ODBC does not expose the full functionality of ODBC (let alone the Polyhedra-specific extensions you can access if linking your C/C++ program directly with the Polyhedra ODBC library) the .NET programmer cannot make use of some Polyhedra functionality if adopting this approach: in particular, active queries are not supported.



    Appendix A: Installing and configuring the Polyhedra ODBC driver

    For each platform on which Polyhedra is supported, there is an ODBC library that can be linked directly into client applications that want to use ODBC to talk to Polyhedra servers. However, in some circumstances people might prefer to use the library less directly, via an ODBC driver manager. While this has disadvantages (it is less efficient and makes it impossible to take advantage of any Polyhedra-specific extensions to ODBC), it can be more convenient – especially if you are not in a position to relink the client applications to use the Polyhedra ODBC library directly.

    The Windows ODBC drivers for Polyhedra

    Enea has two ODBC drivers for Windows: a 32-bit version and a 64-bit version. As one might expect, the 64-bit version can only be installed on 64-bit versions of Windows, and can only be used by 64-bit applications. By contrast, the 32-bit driver can be installed on both 32-bit and 64-bit versions of Windows, and is for use with 32-bit applications. As 32-bit Windows applications predominate, most people will find they need the 32-bit version of the Polyhedra ODBC driver even on 64-bit platforms. As present, you cannot install both versions on the same machine. Please note that the 64-bit Polyhedra ODBC driver is only available to those who have purchased licences for the full, commercial versions of Polyhedra; for Polyhedra Lite, we only make available a 32-bit version.

    The Polyhedra ODBC driver release kits for Windows each consist of a single ZIP file. Uncompress this, and in the case of Polyhedra 8.7 you will find you have a directory with the name poly8.7/odbc, containing 3 files: the driver (called polyod32.dll, even if it is the 64-bit version), a set-up file and a help file. For Polyhedra 8.8 and onwards, though, a different convention was adopted: the odbc directory contains just the readme file, and the driver, help file and set-up program are located in the directory win32\i386\bin (for the 32-bit driver) or win64\x86_64\bin (for the 64-bit driver). The reason for the change was to simplify administration for those who want to unpack both installation kits on the same machine.

    To complete the driver installation, run the supplied set-up program, and (once you click on 'continue' to confirm the action) it will install the driver and help file in the standard place for, replacing any previously-installed versions. (The installation location will vary, depending on the version of  Windows you are using.) The ODBC data source administrator program will be started automatically: you can use this to set up an association between a 'data source name' (DSN) and a Polyhedra server. You do this by clicking on the Add… button, scrolling down to select the Polyhedra driver. When you click Finish, the ODBC Polyhedra Setup dialogue box will allow you to define the DSN (poly-8001, say, or just 8001), a flag (to say whether the connection is to be fault-tolerant), and three other fields:
    • Description
    The value can be left blank, or set to whatever you want; the actual value is not used by the driver manager or by the Polyhedra driver.
    • Data Service
    This identifies the Polyhedra database to which you want to connect. If the Polyhedra server will be running on the machine on which you have installed the driver, this could just be the data service on which the server is listening for connections: for all the standard examples supplied by Polyhedra, this will be port 8001. If the server is on a different machine, you have to identify both the machines (by its IP address or name) and the port number, for example

    192.168.1.101:8001

    For fault-tolerant connections to a pair of servers offering a fault-tolerant service, give a comma-separated list (with no spaces), for example

    192.168.1.101:8001,192.168.1.102:8001.
    • Environment
    This is another optional field: if you give a value, it will be passed across to the Polyhedra server when the connection is established – and if data connection monitoring has been enabled in the server, the value will be stored in the appropriate field of the DataConnection record for the new connection.

    Note that the Polyhedra database servers do not have to be running at the time when you define DSNs! Once you have defined all the DSNs you need click the OK button on the ODBC Data Source Administrator window, and set-up is complete. (If you need to add a new DSN at a later stage, or want to alter the definition of an existing DSN that you have set up, use the Data Sources (ODBC) item in the Administrative Tools section of the Windows Control Panel.)

    The main body of this technical note assumes you have set up an ODBC DSN called 8001 to point at a local Polyhedra database server that is listening on port 8001.

    The Linux ODBC drivers for Polyhedra

    There are ODBC drivers for certain Linux platforms supported by Polyhedra – for example, there is a driver for Linux running in 32-bit mode on x86-based machines and compatibles, and there is a 32-bit driver for Linux running on the Raspberry Pi (a single-board microcomputer developed for a UK-based educational charity, see www.raspberrypi.org). In each case, the driver is designed for use with the unixODBC driver manager, and it is up to you to install this on the machine if it does not form part of the standard distribution. On a Raspberry Pi machine running the standard Raspbian distribution, you can install unixODBC (plus the headers etc to allow you to write your own programs that use the ODBC API) by the following command:

    sudo apt-get install unixodbc unixodbc-dev

    Like most Linux services, unixODBC uses text files to store its configuration, and these are simple enough to be edited by hand. If you are the only person wanting to use the Polyhedra driver, the simplest thing to do is first to unpack the driver kit into your home directory – in the case of Polyhedra 8.7 it will appear in a subdirectory called poly8.7/odbc; the naming convention may differ in future releases of Polyhedra, in which case the new convention will be described in the 'upgrading' section of the release notes. Next, set up a file called .odbc.ini in your home directory containing the following:

    [8001]
    Description = Demo Polyhedra DSN
    Driver = /home/pat/poly8.7/odbc/libpolyod32.so
    service = 8001

    Substitute your own user name for pat in the above! This sets up a DSN called 8001 which connects to a local Polyhedra database server that is listening for connections on port 8001. (The DSNdoes not have to be the same as the port number!) If the server is on a different machine, you have to identify both the machines (by its IP address or name) and the port number, for example 192.168.1.101:8001.

    If you want to have more than one DSN set up, just add them to the end. For example, if you want to have a DSN that uses an earlier version of the Polyhedra driver for testing purposes, your .odbc.ini file could look like the following:

    [8001]
    Description = Demo Polyhedra DSN
    Driver = /home/pat/poly8.7/odbc/libpolyod32.so
    Service = 8001

    [poly86]
    Description = Polyhedra 8.6
    Driver = /home/pat/poly8.6/odbc/libpolyod32.so
    Service = 8001

    By choosing whether to use the DSN 8001 or poly86, you specify which version of the Polyhedra ODBC driver will be picked up; in either case, though, you are connected to the same database (as the value for the service parameter is the same for both). In addition to Service, the other Polyhedra-specific parameters you can define are
    • ft_enable
    set this to yes if you want a fault-tolerant connection – in which case the service parameter would be a comma-separated list of the servers that will be hosting the database,
    • environment
    This is an optional field: if you give a value, it will be passed across to the Polyhedra server when the connection is established – and if data connection monitoring has been enabled in the server, the value will be stored in the appropriate field of the DataConnection record for the new connection.

    UnixODBC also checks for user and password parameters, and if present will use the supplied values when opening the connection. (In addition, unixODBC can be configured to trace ODBC function calls, by setting trace to yes, and tracefile to point at where you want the output sent – but this is invisible to the driver.)

    If many people are wanting to use the Polyhedra driver, it would be more sensible to copy the driver file across to, say /usr/local/lib, and put the DSN definitions in the main odbc.ini file (which will normally be found in the /etc directory. Any definitions in a user’s ~/.odbc.ini file supplements definitions defined in the central file. In fact, it is possible to split of the driver definitions to a separate file called odbcinst.ini, and replace the driver lines in the odbc.ini files with references to definitions in the odbcinst.ini file. Thus with the following definitions
    • /etc/odbcinst.ini
    [polyhedra]
    Description = Polyhedra ODBC driver
    Driver = /usr/local/lib/libpolyod32.so

    [polyhedra with logging]
    Description = Polyhedra ODBC driver, with tracing
    Driver = /usr/local/lib/libpolyod32.so
    Trace = yes
    TraceFile = /tmp/polyodbc.log
    • /etc/odbc.ini
    [8001]
    Description = Polyhedra connection to 8001
    Driver = polyhedra
    service = 8001

    [8001L]
    Description = Polyhedra connection to 8001 with logging enabled
    Driver = polyhedra with logging
    service = 8001
    • ~/.odbc.ini
    [poly86]
    Description = Polyhedra 8.6
    Driver = /home/pat/poly8.6/odbc/libpolyod32.so
    service = 8001

    … anyone can use the DSNs 8001 and 8001L, but the user with the ~/.odbc.ini set up as above can also use poly86 to make use of the indicated driver.

    An aside on SQL header files

    Please note that unixODBC comes with its own copies of sql.h, sqlext.h, sqltypes.h and sqlucode.h, which are normally installed in /usr/include/. The Polyhedra release kits also come with versions of these files, which are sufficiently compatible that Polyhedra client applications can be compiled with either version. However, they do not contain, for example, definitions of ODBC2 functions that are not implemented by Polyhedra, so when compiling applications that have been specifically written for use with unixODBC, you should ensure that they are compiled with the unixODBC versions of these header files. Thus, if unixODBC is installed on a machine, do not put the Polyhedra versions in a place such as /usr/local/include. By contrast, the file sqlpoly.h is only of use to applications that want to make direct use of the Polyhedra ODBC library, and so it does no harm to install this in a standard place.