Mobile Line Of Business

Richard Jones (MVP)

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

News

Welcome to the Mobile Line Of Business Blog

Tag Cloud


Archives

About

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.