Mobile Line Of Business

Richard Jones (MVP)

  Home  |   Contact  |   Syndication    |   Login
  200 Posts | 0 Stories | 36 Comments | 0 Trackbacks

News

Welcome to the Mobile Line Of Business Blog

Tag Cloud


Archives

About

Wednesday, March 20, 2019 #

So with thanks to they guys at IBM (credited on the GitHub Page).

I’ve started work on an ODBC SwiftKuery database driver.       https://github.com/richardpauljones/SwiftKueryODBCSQL

Your contributions welcome.

I’ve basically been step, by step copying the PostgresSQL implementation,

https://github.com/IBM-Swift/Swift-Kuery-PostgreSQL

This is a work in progress.    I have basic C.R.U.D working,  but a fair way from this being a complete thing.   

SELECT * FROM "tableAliasOSX" AS "new"

a                                   b                                   

apple                               10                                  

apricot                             3                                   

banana                              17                                  

apple                               17                                  

banana                              -7                                  

banana                              27  



Feel free to lend a hand.

Saturday, February 23, 2019 #

So disclaimer,     this isn’t finished by any standard,   but I wanted to document how far I’ve got so far.

 

Goal

To be able to connect http://vapor.codes to an ODBC data source.      Primarily I wanted to be able to make use of the Fluent ORM   to query Microsoft SQL Server.

I work mostly in the enterprise space where Microsoft SQL Server is everywhere.     How nice would it be to be able to build in Swift with all the advantages that the language offers.

I know, I know there are lots of other ways to crack this nut.      I live in a world of Visual Studio C# and Lync.    so I’m well aware that other technology stacks exist for this purpose;  but whats the fun in all that.     Native Swift was where I needed/wanted to go.

 

The Beginning

So working on  a Mac,    my first step was to just get a Mac talking to SQL server.   The Swift stuff could follow later with a loftier goal of also making this work under Linux too.

So starting with the basics    Microsoft provide a set of cross platform tools for connecting from a Mac/Linux to SQL server via ODBC.

I followed this guide -

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools?view=sql-server-2017

As the brew install progressed,   I could see onscreen that what was being installed was a set of utilities called UNIXODBC

Once installed,   I ran - 

odbcinst -j

This reports back where you need to setup ODBC datasources etc.   This all at this point felt a bit old school.

unixODBC 2.3.7
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/richardj/.odbc.ini

 

Onwards….

I found you can just edit your odbc.ini and add your database connection.

Or you can invoke all the connection stuff using the SQLCMD tool.

Like this...

sqlcmd -S tcp:SERVERIP,1433 -d “DATABASENAME" -UOURUSER -POURPASSWORD -Q “SELECT * FROM TABLE "

 

This works.     You can now run queries from the Mac.        I tried this under Linux too.     It works as you would expect.

Just to show you what this looks like,    I ran the following query 

SET NOCOUNT ON; SELECT Getdate();

Output produced is - 

-----------------------
2019-02-23 12:36:35.223

 

I then spent too long trying to write wrapper code around SQLCMD trying to parse the results back but this all seemed a bit pointless slow and maybe not appropriate for long term and scalable solution.

 

Progress...

At this point,   I thought I wonder how UNIXODBC works?    Could this be the answer to getting Swift talking to ODBC data sources,  and onwards to MS SQL Server?

 

IBM To The Rescue

So much Googling later.   I found this - 

https://github.com/IBM-DTeam/swift-for-db2

IBM had already written for Swift 3.0   a wrapper around UNIXODBC for talking to DB2 via ODBC.

I seemed that IBM had gone in another direction and weren’t mainitaing any further,  but this looked promising 

 

So how hard could this be, to get the IBM Swift package for DB2 to run in a Swift 4.x environment and talk to any ODBC data source???

 

Getting it to run.

Now this stumped me.  I wanted to create a Swift sample using the package manager that will pull down the IBM package   I really struggled to get this to work.

Here’s what you need todo at least to get this to compile.     I made a new swift package called HelloODBC.   My steps are as follows -

mkdir HelloODBC

cd HelloODBC

swift package init --type executable

 

Edit packages.swift and make it look like mine - 

 

// swift-tools-version:4.2
// The swift-tools-version declares the minimum version of Swift required to build this package.

import PackageDescription

let package = Package(
name: "HelloODBC",
dependencies: [
.package(url: "https://github.com/IBM-DTeam/swift-for-db2.git", from: "1.0.0"),
// Dependencies declare other packages that this package depends on.
// .package(url: /* package url */, from: "1.0.0"),
],
targets: [
// Targets are the basic building blocks of a package. A target can define a module or a test suite.
// Targets can depend on other targets in this package, and on products in packages which this package depends on.
.target(
name: "HelloODBC",
dependencies: ["IBMDB"])
]
)

 

cd Sources 

Edit main.swift

 

import Foundation
import IBMDB

let db = IBMDB()
//let connString = "DRIVER=ODBC Driver 17 for SQL Server;DATABASE=OURDATABASE;UID=OURUSER;PWD=OURPASSWORD;HOSTNAME=OURHOST;PORT=1433"

 

db.connect(info: connString) { (error, connection) -> Void in
if error != nil {
print(error)
} else {
print("Connected to the database!")

}
}

 

cd ..

Now to make it compile you need to type this - 

swift build -Xlinker -L/usr/local/lib/

To run

.build/debug/HelloODBC

 

So this is where the disappointment maybe lies,   but the mission continues.   

 

I get back -

 

Optional([IBMDB.DBError(native: Optional(-30081), state: Optional("08001"), description: Optional("[IBM][CLI Driver] SQL30081N  A communication error has been detected. Communication protocol being used: \"TCP/IP\".  Communication API being used: \"SOCKETS\".  Location where the error was detected: \"172.20.1.12\".  Communication function detecting the error"))])

 

 

So it looks like maybe somewhere in the IBM Code  its hard coded to use their ODBC DB2 driver,  rather than let me specifically the SQL Server one.

 

Conclusion

So this is where I’m currently at.     Have you have any advice on how to proceed please let me know.

I’ll report back on any progress.

 

 

 

 

 

 

 


Monday, October 15, 2018 #

So some great geek stuff with a customer of mine (Family Farms).   We needed to integrate combine-harvester and tipper trucks on our iPad app.  to record weights as combine tips to nearby truck.   This was our saviour a little $50 bluetooth LE dongle.

 

Dongle

 

BLE, allows devices to just find each other (romantic I know). Unlike normal Bluetooth like you use say your handsfree kit; you don’t need to pair a BLE device. You simply say find any devices in range that offer a set of services, i.e. can transfer data. iOS and Android devices have had BLE capability for about 3 years now.

NewImage

So why is all this exciting. This means a harvester can tip their harvest into a tipper truck which is equipped with a weigh cell connected to a dongle like this. As the combine drives by and tips, the iPad app. with the combine driver detects the BLE dongle and reads the tipped weight.

So we were able to prove that this can indeed be done. We have a scale simulator up and running in Harston. As POC addition to the FFG farming app. we can connect over BLE and get a reading from the scale simulator.

The advantage of using BLE is that you just create a temporary network in-field for just a few seconds between combine and tipper truck, you don’t need any other kind of WIFI or cellular network access.

 Picture1


Monday, May 7, 2018 #

So, this has taken a while

Only tonights install of Visual Studio Code update (aka after Build Keynote???)...

 

I can finally run SQL Server queries from Visual Studio Code on my Mac.

 

I followed this guide to install/re-install

 

https://sqldbawithabeard.com/2017/01/05/running-sql-queries-with-visual-studio-code/

 

I then right clicked on my query to select Execute,  the keyboard shortcut didn’t seem to work.

 

Result - some data from SQL server !



Tuesday, May 1, 2018 #

Today, I made an interesting discovery.

I’m synchronising data to Microsoft NAV, from an iOS app, using oData.

My iOS app,  makes JSON requests to pull/push records in and out of NAV.

Todays discovery was don’t send float numbers in scientific format,  i.e 1e6,   send them as fully expanded numbers,  like 1000000.        Only when numbers get big did I notice this issue. 


Friday, July 7, 2017 #

So I’ve added a Lego Case To My Music Streamer 

 

IMG 0356

 

Its working great.   Our boys can wander in with their tablets/phones and just wirelessly play what they like.    Music should just be like this.   Accessible and easy sounds awesome too.

 

Sum of parts.   Raspberry Pi Zero W - Hifi-Berry DAC Board - Some Lego - Cambridge Audio Amp and Speakers.    + Great Kids.


Monday, May 8, 2017 #

So I’ve been wanting to update my media streamer for a while.

In our kitchen  I have an amp and speakers,  and I have a Raspberry Pi One acting as a media streamer,  primarily for use with Air Play.

IMG 0246

 

It sounds truly amazing.    I did the build for around £30.     

 

Here is my build list -

 

Software - http://Volumio.org

Hardware 

Pi Zero W - Board only

https://shop.pimoroni.com/products/raspberry-pi-zero-w

 

Hammer on - Header Board - this is genius

https://shop.pimoroni.com/products/gpio-hammer-header

 

DAC Board - Gives the Pi Decent Sound capability

https://www.hifiberry.com/shop/boards/hifiberry-dac-zero/

 

Metal stand-offs - These just look nice

https://www.hifiberry.com/shop/accessories/4-standoffs-m2-5x12mm-steel/

 

 

Parts I already had - 

8 Gb Micro SD Card

Power Supply - Used an old phone charger

Micro USB Cable

RCA Cable

Amp + Speakers

 

 

 


Sunday, November 27, 2016 #

This weekend…..

 

I’ve realised the truth that Docker is the future.   Please take a few moments to inhale docker.com

 

I’ve built a MS SQL 2016, in Docker, connected to from another Container running Swift.  


Friday, October 21, 2016 #

So excited.   I've done it.   First Amazon Echo Skill, all up and running.   Will report back with full implementation details.   Super easy and really great result.


Wednesday, July 6, 2016 #

So I have gotten myself another Raspberry Pi 1 Model B. (from eBay with case, power supply and 8GB SD for under £20).

I’ve plugged in an Edmiax WIFI USB Card.   Plus the star purchase a £4 USB Conexant modem.   This  is a good news, as this modem can decode UK caller ID signals.

NewImage

 

 So the goal is to broadcast who’s calling my home phone line and send who’s calling to my desktop Mac and various iOS devices.

The Pi, side of things took the longest.    I decided to use the common established and old (all good things for reliability) NCID package.   Getting this onto the Pi took the longest.

I ended up having to compile the source code, and then struggled to get the thing to start automatically.

In the end this was the best guide I found -

https://github.com/Phiplex/ncidhitta

I used the 1.4 version of NCID.   

Once up and running, I used another terminal window to telnet to my Pi on port 3333 to check that when my home phone rings the Pi was decoding (via NCID) the incoming caller ID information.

 You get something back like the following - 

NewImage

 

 

So good news,  phone rings the telnet window updates, with who’s calling.

 

The next step was to notify all Macs and iOS devices.     I followed the guide here to ‘borrow’ how they collected output from NCID for their own purposes...

https://jimtech.wordpress.com/2012/04/09/getting-ncid-working-on-every-device-possible/

 

My modification was to change the output command to curl a web request adapted to my own web server.

 

As discussed previously I have managed to get Safari and iOS push notifications working, so I utilised that and a database of device tokens and a database of phone numbers to names, to send and Apple Push Notification.

 

Net result,  phone rings.   All my devices go PING  and instantly display who’s calling -

 

NewImage

 

Click the notification and you can see/edit  (in Safari) the name associated to the number and see last calls -

NewImage

In iOS, it works similarly.