DSN-less access to Microsoft SQL Server from Raspberry Pi Raspbian Stretch

Accessing Microsoft SQL Server (including SQL Server Express and Azure SQL Database) from Linux isn't that straightforward.

Microsoft does provide free connection library for Linux to access to SQL Server, but the library will not work for Raspbian OS in Raspberry Pi due to conflicting type definition with other essential library in the system.

Below is the easiest way I found out to access SQL Server from Raspberry Pi.

You need to enable and allow TCP/IP access to the SQL Server.

The SQL client to use is FreeTDS through unixODBC.

First of all, make sure the required FreeTDS and unixODBC components are installed in the Raspberry Pi:

sudo apt install freetds-bin freetds-dev tdsodbc unixodbc unixodbc-bin unixodbc-dev

If you are using Python to access the SQL Server, also need to install the following components:

sudo apt install python-pymssql python-pyodbc python-sqlalchemy

If you are using PHP to access the SQL Server, then need to install the following components:

sudo apt install php7.0-odbc

Since we are going to use the DSN-less method to access to the SQL Server, it is not necessary to configure the DSN settings. However, there is still minimal configuration needs to be made.

sudo nano /etc/odbc.ini

[FreeTDS]
Description = SQL Server
Driver = FreeTDS
Trace = No
TDS_Version = 7.0

For the TDS Version, if you are unsure about the version of your SQL Server, then set the TDS Version to 7.0.

You can set the TDS Version according to the following:

• TDS Ver. 7.0 for SQL Server 7.0
• TDS Ver. 7.1 for SQL Server 2000
• TDS Ver. 7.2 for SQL Server 2005
• TDS Ver. 7.3 for SQL Server 2008
• TDS Ver. 7.4 for SQL Server 2012, 2014, 2016 or 2017

Lower TDS version has more compatibility but less feature in SQL command and less supported data types.

Another file you need to edit is odbcinst.ini.

sudo nano /etc/odbcinst.ini

[FreeTDS]
Description = FreeTDS unixODBC Driver
Driver = /usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so
Setup =  /usr/lib/arm-linux-gnueabihf/odbc/libtdsS.so
fileusage=1
dontdlclose=1
UsageCount=1

Now you should be ready to access to your SQL Server from this Raspberry Pi.

Python test script:

import pyodbc

server = 'your_SQL_Server_IP_address'
database = 'your_database'
user = 'database_user'
password = 'database_user_password'
query = 'SELECT * FROM INFORMATION_SCHEMA.TABLES;'   # your SQL query
conn = pyodbc.connect('DRIVER={FreeTDS};SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+user+';PWD='+password+';')
cursor = conn.cursor()
cursor.execute(query)
for row in cursor.fetchall():
        print row

PHP test script:

$server = 'your_SQL_Server_IP_address';
$database = 'your_database';
$user = 'database_user';
$password = 'database_user_password';
$query = 'SELECT * FROM INFORMATION_SCHEMA.TABLES;';   // your SQL query

if ($db = odbc_connect('DRIVER={FreeTDS};SERVER='.$server.';PORT=1433;DATABASE='.$database.';',$user,$password)) {
        $res = odbc_exec($db, $query);
        while( $row = odbc_fetch_array($res) ) {
                print_r($row);
        }
        odbc_close($db);
}

dnGrep - Free GUI "grep" and "sed" tool for Windows

If you are familiar with UNIX/Linux operating system, you must be using a lot of "grep" command to search for string inside text files, and "sed" command to find and replace string in the text files.

There are identical command in Windows such as "find", "findstr" or even the PowerShell "Select-String", but they are just not as easy to use as "grep" in UNIX/Linux.

There are also GUI tools for Windows which aim to perform the "grep" and "sed" function, majority of the good ones need you to pay for it, except dnGrep which is licensed under GNU GPL v2 and is therefore free for use.

dnGrep is indeed a very powerful find and/or replace tool for Windows. It can not only search for text or keywords inside the target file(s), but also able to perform a text replace action in target text file(s).

Beside normal text files, it is also able to search inside MS Word documents and PDF files. It can also search inside archived file.

You can perform your search by plain text input, or XPath query language, or regular expression (regex), or even phonetic.

You can also specify to search for files in a folder (and sub-folders) within a particular date range in which they are created or last modified.

The search result is displayed with yellow background highlight, together with the line number in the target document. Double click on the search result will open up the target document for further action.

The beauty of its replace function is that, the replace action can be undone.

You can also create "bookmarks" to store frequently used search keywords and conditions.

dnGrep is lightweight, fast in action, powerful and free for use. It is an essential 3-party tool in your Windows computers.

Click here to go to the download page of dnGrep.

My UGREEN LP144 gravity handphone holder for car CD/DVD slot

Handphone holder (a.k.a. phone cradle) is a very handy and useful gadget when we want to hold our phone in our car at eye level.

One of the common use case is when we use our handphone for navigation on Google Map, or Waze, or some other similar navigation apps.

There are generally 5 types of handphone holders available in the market:

• CD/DVD player slot holding type
• Air-cond vent fin clipping type
• Suction mount type which attached onto the windscreen
• Standing type putting on top of the car front panel
• Rearview mirror attaching type

I would recommend the first 2 types, because they can take advantage of your car aircond blowing to cool down your phone. If you attach your phone onto the windscreen or put it on top of the car front panel, the sunlight will heat it up to a high temperature, which is unfavourable to the phone and its Li-ion battery.

Depends on the design of your car front panel, you can choose the more popular air-cond vent fin clipping type, or the CD/DVD player slot holding type.

My UGREEN LP144  gravity handphone holder of car CD/DVD slot holding type costs at RM20++ and has a simple packaging.

This is how it looks like (front and back).

 

As you can see from the picture, it has 3 rubber pads to be inserted into the car CD/DVD slot to hold it firmly. The big button at the back of the center rubber pad is used to align the rubber pads for insertion and removal.

This handphone holder can still be used even when there is CD/DVD inside the player. It would not affect the CD/DVD playing.

I have chosen it because of its special 3 arms design, whereby when the lower arm is pulled down, the left and right arms will contract. When you slot in your phone, the weight of the phone will pull down the lower arm, forming a strong grip by the side arms.

As such, it can hold your phone firmly, and you don't need to be worried about the phone fall off the holder when you are driving on bumpy road.

This holder can fit for handphones with width up to about 8.3cm. If the phone is too large (for example, the Huawei Mate 20 X with width of 8.54cm), then might not be able to fit in.

For my car, the phone is sitting at the position just nice to be cooled by the air-cond behind it.

The only thing I dislike about this product is that, it will produce rattling sound when you are driving on uneven road, and there is no phone put on it. This is caused by its plastic arms knocking onto the center part and producing the noise. Anyhow, this problem does not exist when there is a phone put on it.

If the manufacturer can enhance its design by putting a layer of thin soft sponge at the knocking points which produces the noise, then it will be a perfect product.