Wednesday, June 19, 2019

Displaying a MySQL/Mariadb table on a web page using Ruby Sinatra and JS DataTables

My son was working on a project where he needed to display data from a MySQL/Mariadb table on a web page. Having no prior HTML/JS experience he had no clue how to do this.

He already had a Sinatra based API for the same project so I decided to build him a small demo using Sinatra as the backend API and "web server" and JavaScript DataTables as the front-end.

First let's take a look at a simple database with a single table:

CREATE database demo_datatables;
USE demo_datatables;
DROP TABLE employees;
  name VARCHAR(255),
  position VARCHAR(255),
  office VARCHAR(255),
  extn VARCHAR(255),
  start_date VARCHAR(255),
  salary VARCHAR(255)
GRANT ALL ON demo_datatables.* TO my_user@localhost IDENTIFIED BY "my_password";
INSERT INTO employees VALUES ('Tiger Nixon','System Architect','Edinburgh','5421','2011/04/25','$320,800'),('Garrett Winters','Accountant','Tokyo','8422','2011/07/25','$170,750'),('Ashton Cox','Junior Technical Author','San Francisco','1562','2009/01/12','$86,000');

Ok the datatypes used suck and are inappropriate, but this is not the scope here.

Next let's create our Sinatra API (api.rb):

require "sinatra"
require "sequel"
require "json"

mysql_host   = 'localhost'
mysql_user   = 'my_user'
mysql_pass   = 'my_password'
mysql_db     = 'demo_datatables'

# Initialize out database connector and table dataset
db ||= Sequel.connect(
  adapter: 'mysql2',
  host: mysql_host,
  username: mysql_user,
  password: mysql_pass,
  database: mysql_db,
  max_connections: 10
table = db[:employees]

# Data from MySQL
get '/data' do
  a = { |h| h.values }
  { data: a }.to_json

# Page web root (this must be the last method as out pages data is in /)
get '/:name' do
  if File.file?(params['name'])
    send_file params['name']
    "File #{params['name']} does not exists"

As you can see we have a /data endpoint that will fetch the entire content of the table, format it as an array compatible with DataTables.

We also have a catch all endpoint on / that is used as a poor-man web server. It could be made much better but for this demo it makes the point. This endpoint will simply take any file passed as endpoint and if they exist send them over.

We can start this simply by running ruby api.rb. The output should be similar to this:

[root@monitor1 ~]# ruby api.rb
[2019-06-19 19:38:07] INFO  WEBrick 1.4.2
[2019-06-19 19:38:07] INFO  ruby 2.5.1 (2018-03-29) [x86_64-linux]
== Sinatra (v2.0.5) has taken the stage on 4567 for development with backup from WEBrick
[2019-06-19 19:38:07] INFO  WEBrick::HTTPServer#start: pid=19804 port=4567

So our server is running on port 4756. We can easily test out API by running curl -s http://localhost:4567/data:

[root@monitor1 ~]# curl -s http://localhost:4567/data
{"data":[["Tiger Nixon","System Architect","Edinburgh","5421","2011/04/25","$320,800"],["Garrett Winters","Accountant","Tokyo","8422","2011/07/25","$170,750"],["Ashton Cox","Junior Technical Author","San Francisco","1562","2009/01/12","$86,000"]]}6,000"}]}

We see that out endpoint to get the data works.

Let's now create out base HTML file (index.html):

<!DOCTYPE html>

  <meta charset="utf-8">
  <meta name="author" content="">
  <meta name="description" content="">
  <meta name="viewport" content="width=device-width, initial-scale=1">

  <link href="" rel="stylesheet">


  <table id="example" class="display" style="width:100%">
        <th>Start date</th>

  <script src=""></script>
  <script src=""></script>
  <script src="script.js"></script>


It's a simple HTML that has the skeleton for a table and fetches the required dependancies from global CDN sources.

And our small Javascript snippet (script.js):

$(document).ready(function () {
    "ajax": "/data"

Again very simple DataTables usage where we will fetch out data from the /data endpoint and have DataTables render in in the table with ID #table.

And let's look at the result:

Not bad for some ~70 lines of code.

Note that this is by no mean secure, failures trap would have to be added and scaleability would have to be evaluated, but it's simple as hell and I hope a good starting point.

Friday, May 31, 2019

Exposition des finissants en génie électrique du Collège Lionel-Groulx - 2019

Et oui encore une fois cette année j'ai assisté aux présentations des projets des finissants en génie électrique du Collège Lionel-Groulx.

Ayant été finissant a ce même collège en 1993 c'est toujours un grand plaisir de revoir certains de mes professeurs et membre du personnel qui sont encore présents.

Moi et mon coéquipier Eric Jarry lors de la presentation de projet en 1993

Cette année a eu droit à 16 projets des élèves en profils Systèmes Ordinees et  en Télécommunications:

Voici quelques photos et vidéo prisent pendant l'événement:

Kasparov 2.0 par Jean-Chrystophe Rasine et Huillaume Marcoux-Legault

CNC Laser par Sergio-Victor Jara et Marc-Antoine Schanck

Revolution par Alexandre Charrier et Matthieu Mafran

Pinball sans fil par Eric Girard et Delmar Valesquez

Bravo particulier a  Guillaume Génier (projet de crayon capteur) et Nicolas Roy (projet Auto-Topo) qui se sont mériter une bourse offert par Hydro-Quebec:

L'utilisation des Arduino et RaspberryPi sont omnipresent dans les projets ainsi que l'utilisation de l'open source tant logiciel que matériel. Il faut plus d'initiative comme celle-ci et une plus grande participation des industries afin d'encourager la relève.

Petit conseil aux équipes de l'an prochain:

  • Planifier chaque étape de vos projets, assurez-vous d'avoir du temps pour chacune de ces étapes. N'ayez pas peur de mettre certains éléments comme non-necessaire et "nice To havent" afin de pouvoir avoir un projet complet à presenter. 
  • Passez moins de temps à faire de la mécanique et plus de temps a l'élaboration, la planification et l'implementation logiciel et électronique. 
  • Mettez vos projets sur Gitlab et faites en la publicité sur les réseaux sociaux (Facebook, Twitter....)

Thursday, May 30, 2019

RFID activated bird feeder

Over the past weeks I have been working on a project for the Geography and Environmental Systems Department at the University of Maryland.

The idea is to study feeding habits of birds. Birds are equipped with very small RFID tags on their legs and the feeders will only open when they stand on the perch of the said feeder.

All in all not a very complex project code wise, more of a mechanical challenge. Also the feeders mush be autonomous (aka battery operated) and must record the time/date and RFID tag ID when birds come to feed on an SD card.

I started with a proof of concept showing my contact that it could be done. First activating the servo via an RFID tag:

And then activating a "door" with that servo:

One of the challenge was that their RFID tags were 125kHz tags so the choice of RFID reader was more limited. 

Bird leg RFIS tag

Turned our there were 2 models: RDM630 and RDM6300. The major difference between the 2 modules is that the RDM630 is superior in functionality and little more expensive, but the RDM6300 will read the same tag continuously if it's put near the reader for a long time.

So my choice was the RSM630 at first but turned out it did not work as expected for this application. We needed to know if the tag was present so that we kept the seed door open for the bird to feed.

So I ordered some RDM6300 from Robotshop and in conjunction with using interrupt on the Arduino it worked perfectly.

There was no budget or time to make an Aduino shield PCB to hold the RFID reader, RTC and SD module so I did a protoboard assembly:

Now that I had the code working as I wanted and the electronics assembly done I needed to work on the actual feeder assembly. A few hours of Sketchup and I came up with this design:

It allowed for a removable side panel to access the electronics and retrieve the SD card as well as a removable top to insert the seed bag and delivery system (which the final customer would be taking care of).

Because MDF is easy to work with I decided to use it for this build along with some 3D printed parts (all the parts you see in gray in the above picture are 3D printed). Here are some pictures of the build:

I really suck at the painting process. But after final test I shipped it to the customer and here was their comment when they received it:

So I guess I did not do too bad on that job. It was fun and I learned new things.

Wednesday, April 17, 2019

Integer to it's binary string representation in C

My oldest son is studying mathematics and programming in college and wrote me asking for a way to convert an integer to it's binary representation in C. My C string handling is a bit rusty, but I accepted the challenge. This was the result, maybe it can be useful for someone.

#include <stdio.h>
#include <string.h>
#include <stdlib.h>

const char *int_to_binary_string(int);

#define BITS_REQUIRED (sizeof(value_to_convert) * 8)

const char * int_to_binary_string(int value_to_convert) {
  char *result_string = malloc(BITS_REQUIRED + 1);

  for (int bit_counter = BITS_REQUIRED - 1; bit_counter >= 0; bit_counter--) {
    int current_bit = value_to_convert >> bit_counter;
    strcat(result_stringcurrent_bit & 1 ? "1" : "0");

  return strchr(result_string, '1');

int main(void) {
  int value_to_convert = 55;
  printf("%i in binary is %s\n", c, int_to_binary_string(value_to_convert));

And the output:

{21:32}[2.5.0]~/Desktop ➭ gcc test.c
{21:39}[2.5.0]~/Desktop ➭ ./a.out   
55 in binary is 110111

Wednesday, March 6, 2019

Potentiometer value codes

Just received a potentiometer kit I ordered from Ebay. Off course it all comes in unlabeled small ziplock bags:

But as with most electronic component, potentiometers have small marking or value code on them:

The following is a table that will help you identify the actual value of the said potentiometers:

Code Value
100 10 Ω
200 20 Ω
500 50 Ω
101 100 Ω
201 200 Ω
501 500 Ω
102 1 kΩ
202 2 kΩ
502 5 kΩ
103 10 kΩ
203 20 kΩ
253 25 kΩ
503 50kΩ
104 100 kΩ
204 200 kΩ
254 250 kΩ
504 500 kΩ
105 1 MΩ
205 2 MΩ
505 5 MΩ

Wednesday, January 2, 2019

Electronic devices + password + death = pain

A few months ago my wife's mother died. Being the "teksavvy" guy I had to deal with the disconnection of all-the-things electronics, gather devices that needed to be returned to the ISP,...

Good thing she only had a Windows 10 laptop, no cell phone, no tablet.

But my wife wanted to access what was on the laptop before we wiped it and re-purposed it to a family member.

Only thing was that the person (not me) who set it up for her had configured it with a Microsoft LiveID account. We had a bunch of hand written note with passwords, account ID but could not find a password or PIN that worked to login into her Windows profile.

I had done Windows password reset in the past using a Live CD but it was in the Windows NT era...

The first ISO I used was Hiren’s BootCD PE (I'll spare the details that the laptop is UEFI and does not have a built-in CD/DVD drive and I could not get it to boot on USB). Unfortunately it was not able to reset or remove LiveID account password. But I was able to clear the local Administrator account password and this gaining access to the machine. If all fails we could always at least recuperate the files from there. But I wanted full account access.

After a few Google search I found PC Unlocker. Being a FLOSS advocate I tend to dismiss closed source commercial software. But I did not have much more time to spend on this. So I bought a copy for 32.7$CAD and give it a spin.

Sure enough I was able to remove the user account password. I then rebooted, Windows in all it's glory decided to do a HDD scan and repair (why I don't know), but I was then able to login the account without any passwords.

I would have preferred an open-source free utility, but when time is limited it's sometimes worth it to spend 30$ instead of fiddling for hours.

If you know of any FLOSS tool that can reset/clear LiveID account password on Windows let me know !

As a side note, having many electronic devices, accounts and passwords I'll have to evaluate how to deal with this to ease the way for my relatives when (if I ever lol) pass away.

Displaying a MySQL/Mariadb table on a web page using Ruby Sinatra and JS DataTables

My son was working on a project where he needed to display data from a MySQL/Mariadb table on a web page. Having no prior HTML/JS experience...