Easysoft Blog

Wed, January 10 2018

Data Access from Raspberry Pi

If you need to access a database from Raspberry Pi, you can either use one that is available for this platform (for example, MySQL / MariaDB, PostgreSQL and MongoDB) or access one remotely. Easysoft ODBC drivers enable your Pi applications to connect to both local and remote databases. For example, you could use the SQL Server ODBC driver to connect Python on Pi to SQL Server on Windows (or in the Azure Cloud, or on Linux, if you use these platforms). The steps for this are as follows:

  1. Download the SQL Server ODBC driver for Raspberry Pi. (Registration required.)
  2. Install and license the SQL Server ODBC driver on the Raspberry Pi machine.

    For installation instructions, see the ODBC driver documentation.

    Note You need the unixODBC Driver Manager installed on your machine. The Easysoft distribution includes a version of the unixODBC Driver Manager that the Easysoft SQL Server ODBC driver has been tested with. The Easysoft driver setup program gives you the option to install unixODBC.

  3. Create an ODBC data source in /etc/odbc.ini that connects to the SQL Server database you want to access from Python. For example:
    [SQLSERVER_SAMPLE]
    Driver          = Easysoft ODBC-SQL Server
    Server          = my_machine\SQLEXPRESS
    User            = my_domain\my_user
    Password        = my_password
    # If the database you want to connect to is the default
    # for the SQL Server login, omit this attribute
    Database        = Northwind
    
  4. Use isql to test the new data source. For example:
    cd /usr/local/easysoft/unixODBC/bin
    ./isql.sh -v SQLSERVER_SAMPLE
    

    At the prompt, type "help" to display a list of tables. To exit, press return in an empty prompt line.

    If you are unable to connect, refer to this article and the SQL Server ODBC Driver Knowledge Base for assistance.

  5. Now install pyodbc, the ODBC interface that enables you to access an ODBC database from Python:
    sudo apt-get install python-pyodbc
  6. To test the installation, fetch some SQL Server data from a Python shell:
    pi@raspberrypi:~ $ python
    Python 2.7.13 (default, Jan 19 2017, 14:48:08)
    [GCC 6.3.0 20170124] on linux2
    Type "help", "copyright", "credits" or "license" for more information.
    >>> import pyodbc
    >>> cnxn = pyodbc.connect("DSN=SQLSERVER_SAMPLE")
    >>> cursor = cnxn.cursor()
    >>> cursor.tables()
    <pyodbc.Cursor object at 0x76a168a8>
    >>> rows = cursor.fetchall()
    >>> for row in rows:
    ...      print row.table_name
    ...
    sysmatrixageforget
    GEMS_DEPENDENTS_STAGING2
    GEMS_DEPENDENTS_STAGING2
    MSreplication_options
    oinsert
    spt_fallback_db
    spt_fallback_dev
    spt_fallback_usg
    spt_monitor
    >>>
    

Alternatively, if you prefer Perl:

pi@raspberrypi:~ $ sudo apt-get install libdbi-perl
	pi@raspberrypi:~ $ wget http://search.cpan.org/CPAN/authors/id/M/MJ/MJEVANS/DBD-ODBC-1.56.tar.gz
pi@raspberrypi:~ $ tar -xvf DBD-ODBC-1.56.tar.gz
pi@raspberrypi:~ $ cd DBD-ODBC-1.56
pi@raspberrypi:~ $ export DBI_DSN='dbi:ODBC:SQLSERVER_SAMPLE'
pi@raspberrypi:~ $ DBI_USER='mydb_user'
pi@raspberrypi:~ $ DBI_PASS='mydb_password'
pi@raspberrypi:~ $ perl Makefile.PL
pi@raspberrypi:~ $ make
pi@raspberrypi:~ $ make test
pi@raspberrypi:~ $ sudo make install
pi@raspberrypi:~ $ vi perl-test.pl
#!/usr/bin/perl -w

use strict;

use DBI;

my $dbh = DBI->connect( "dbi:ODBC:SQLSERVER_SAMPLE", "mydb_user", "mydb_password" , {
    RaiseError => 1
} );

my $sth = $dbh->table_info();

while ( my ( $qualifier, $owner, $name, $type, $remarks ) = 
        $sth->fetchrow_array() ) {

    foreach ($qualifier, $owner, $name, $type, $remarks) {
        $_ = '' unless defined $_;
    }
    print "$qualifier, $owner, $name, $type, $remarks \n";

}

exit;
pi@raspberrypi:~ $ perl ./perl-test.pl
master, dbo, sysmatrixageforget, SYSTEM TABLE,
master, dbo,  GEMS_DEPENDENTS_STAGING2, TABLE,
master, dbo, DBD_ODBC_LOB_TEST, TABLE,

Easysoft currently supports the ARMv71 Raspberry Pi platform, which is 32-bit. If you need one of our ODBC drivers for a 64-bit Raspberry PI (at the time of writing this means a Raspberry Pi 3 Model B running SUSE Linux), let us know by contacting our Support team, and we will attempt to build a 64-bit driver for you. An example benefit that running our drivers on a 64-bit Raspberry Pi platform is the performance increase this architecture brings when using encryption. (64-bit integers enable CPUs to handle encryption tasks with less commands.) The SQL Server ODBC driver enables you to encrypt the network connection between your Raspberry Pi and SQL Server, protecting data in transit.

Currently, Easysoft drivers enable you to access the following data stores from Raspberry Pi: