Category Archives: Code

ExpressJS, AngularJS, and pretty URLs

We added a new web interface to a set of internal developer APIs so our developers would no longer have to manage unruly postman collections and I kept running into problems where the catch-all express route was catching too much or other routes were no longer behaving correctly.
If you’re having problems with

$locationprovider.html5mode(true)

Here are all the steps I took to resolve our issues:

  1. Add a base tag of <base href=”/”> to your angular index file.
  2. Make sure all of your static routes are defined first in your express app.
  3. After all of your app’s routes, make sure your catch all route is absolutely last
    app.all('/*', function(req, res, next) {
      res.sendfile('index.html', { root: 'myWebRoot' });
    });
  4. That’s it.

I kept running into so many issues and they all boiled down to the static routes not being defined absolutely first. Hopefully with this post you don’t have to make the same mistake.

Possible Fix for Hosted Git (Github.com) Timeout Issues

When installing packages using your language or framework of choice, you’re often required to have git installed when the packages are hosted remotely and versioning is done using git tags or branches. This is great usually, but occasionally you’ll come across a dreaded timeout error like this:

ECMDERR Failed to execute "git ls-remote --tags --heads git://github.com/angular/bower-angular.git", exit code of #128




Additional error details:

fatal: unable to connect to github.com:

github.com[0: 192.30.252.129]: errno=Operation timed out

When this occurs, often it’s just an issue with Github handling git commands. To bypass git using git:// addresses, force them to use https:

git config --global url."https://".insteadOf git://

This isn’t a solution for everyone, especially if you have locally hosted git repositories that are only accessible via git:// , but it might resolve your Github or Bitbucket issues temporarily. To revert this change, simply run the following:

git config --global --unset url."https://".insteadOf

Connecting to an Azure SQL Database with FreeTDS (on a Mac even)

Connecting to an Azure database on your Mac can be more convenient than opening a virtual machine at times, especially when you’re working with a 128gb SSD and your Windows VM isn’t on said SSD. FreeTDS to the rescue!

While it would be nice if Microsoft would opensource the Microsoft SQL Server driver for Linux, FreeTDS seems to work for my situations just fine. In order to get FreeTDS installed and connecting to Azure, you’ll need Homebrew. After you get that sorted out, open up your favorite terminal emulator and run the following:

brew install --enable-msdblib --with-unixodbc freetds

Once this is completed and freetds is compiled, you’ll want to make some changes to your odbcinst.ini file possibly located at /usr/local/Cellar/unixodbc/2.3.2/etc/, but the best way to be certain about where your unixodbc installation assumes its files are is to run odbcinst -j from the terminal to check.

Once you have your odbcinst.ini opened in your favorite text editor, add the following:

[ODBC]
Trace = No
TraceFile = /tmp/odbc.log


[FreeTDS]
Driver = /usr/local/lib/libtdsodbc.so
UsageCount = 1

If something goes awry, you can try debugging the situation with Trace = Yes later.

At this point, you have a driver installed named FreeTDS but no data sources configured. Go back to your text editor and open up the file ~/.odbc.ini or use the SYSTEM DATA SOURCES file that was given when you ran odbcinst -j. Add the following to the file you just opened:

[AZURE]
Description = My MS SQL server on Azure
Driver = FreeTDS
Server = XXXXXXXXX.database.windows.net
Database = XXXXXXX
Port = 1433
TDS_Version = 7.2

Change the Server and Database options to match your own. Notice the TDS_VERSION, this option is key to connecting to Azure SQL Server instances since Azure doesn’t seem to accept connections for any protocol versions 7.0 or less.

At this point, you should be able to connect to your database via the isql tool:

isql -v AZURE MYUSERNAME MYPASSWORD

If something isn’t working, make sure to enable the ODBC Tracing and check that log. Also, verify you’ve added your ip to the Allowed IP Addresses section under the Configure tab once you’ve selected your SQL Database Server in the Azure management portal.

PHP 5.6 x86 (nts) Windows SQLSRV and PDO_SQLSRV Extensions

UPDATE: check out Rob’s additions and compiled extensions here. They have added patches and he’s done an excellent job keeping up to date with them. I initially compiled because there weren’t any sqlsrv extensions compiled for PHP 5.6

I compiled some pdo_sqlsrv and sqlsrv extensions for php 5.6 because who the heck knows when Microsoft will do another official release.
The sqlsrv source code does need a small patch to successfully compile which usually puts a lot of people off when compiling things on their own so I figured I should make them available to the public.

Here’s sqlsrv.dll and pdo_sqlsrv.dll for Windows PHP 5.6 x86 NTS: sqlsrv_pdo_sqlsrv_dll-php5.6_nts.zip.

Enjoy!

SQL Management Studio Jr.

It’s almost inarguable that Microsoft’s IntelliSense is either the top dog, or in the top three at syntax suggestion/completion. What bothers me is that with standard Visual Studio installs (as recent as VS 2013), you don’t get the full SQL Management Studio package. Instead you have to go download SQL Management Studio Jr., with a severely reduced feature set, from Microsoft. Why should you care?

How many developers of relational database-backed applications are there in 2014 that don’t have to think about database performance or troubleshooting ever?
I hope that answer is close to zero.
Even if you’re fortunate enough to have an experienced database administrator, as a developer it would be in everyone’s best interest if you tried to make their job easier and not harder.

As a developer that’s using Microsoft’s database platform, even if you’re not necessarily using Microsoft’s languages (Python, PHP, Ruby, and Node are all supported on Windows Servers), you should be able to do some simple query plan analysis, reporting, and troubleshooting. Yet, the tools required to do these simple tasks are mysteriously left out of the “free” version of Management Studio.

Have a query that’s running long for no apparent reason? Have fun; you’re on your own – unless of course you have access to a Microsoft SQL Server license key and supporting disk image.
In order to have access to the latest SQL Server Profiler, Integration Tools, and Database Engine Tuning Advisor, you’ll either need a better MSDN account (the MSDN account included with Visual Studio didn’t include SQL Server for me) or a full-on SQL Server license.

Hell.

Luckily, my workplace has an MSDN account for us and I was able to use that. Had I been on my own, I would’ve been shelling out some serious dollars (SQL Server licensing is hilariously expensive) to have very basic tools that should be bundled with Visual Studio by default.

While some might view Visual Studio as the best IDE currently available, I still believe it has a ways to go in terms of developer friendly enhancements and decoupling from Microsoft as an avenue for revenue and instead using it as an incentive (reduced cost, free, etc.) to bring more developers to their platform. The full version of SQL Server Management Studio is a developer tool and should be included with every Visual Studio install.

Installing unixODBC 2.3.2 and higher on Ubuntu 12.04 LTS

Before we start – this tutorial assumes you’re using an Ubuntu Server, and you’re OK with removing your existing unixODBC driver manager and any problems that come with that.

OK – on to the goods.

  1. Remove any previous unixODBC packages – take note of any additional packages APT wants to remove so that you can reconfigure/reinstall/fix them later:
    $ sudo apt-get remove libodbc1 unixodbc unixodbc-dev
  2. (Optional – only necessary if you don’t use my .deb package) Get your system ready to compile software if you don’t already have make and gcc installed:
    $ sudo apt-get install build-essential

Now you have three choices – download, configure, and compile yourself, use my modified version of Microsoft’s “build_dm” script they offer with the SQL Server ODBC Driver for Linux, or use the unixodbc_2.3.2-1_amd64 Ubuntu 12.04 LTS package I built.

Personally – I’d choose the package as any other packages that depend on unixodbc or libodbc should easily install and be able to use our custom unixODBC to fulfill any package requirements.

Ubuntu deb package method:

  1. Get the package:
    $ wget http://onefinepub.com/wp-content/uploads/2014/03/unixodbc_2.3.2-1_amd64.deb
  2. Install the package:
    $ sudo dpkg -i unixodbc_2.3.2-1_amd64.deb

Automated script method:

  1. Get the automated build_dm.sh script here or use this command:
    $ wget https://raw.github.com/Andrewpk/Microsoft--SQL-Server--ODBC-Driver-1.0-for-Linux-Fixed-Install-Scripts/master/build_dm.sh
  2. Make sure it’s executable and then run it:
    $ chmod u+x build_dm.sh; sudo ./build_dm.sh --libdir=/usr/lib/x86_64-linux-gnu
  3. After it’s finished, the script will give you a /tmp/unixODBC.RANDOMNUMBERS directory which it tells you to change to, and then ‘make install’. An example of the command I ran is below – replace the XXXX’s with the exact path the script gave you upon it finishing:
    $ sudo su -c 'cd /tmp/unixODBC.XXXX.XXXX.XXXX/unixODBC-2.3.2; make install'

That’s it – unixODBC was automatically configured with some options the Microsoft ODBC driver recommends and the make target “install” was executed.

Do it yourself method:

  1. Download unixODBC

    $ wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.2.tar.gz
  2. Ungzip and untar the gzipped tarball – this example uses a modern gnu tar:
    $ tar -zxvf unixODBC-2.3.2.tar.gz
  3. Change to the new directory that has been created:
    $ cd unixODBC-2.3.2
  4. Configure with any custom options you want – this is an example for Ubuntu 64-bit using the recommendations provided by the Microsoft ODBC driver for server installations (note: if you’re installing on a headless server, you may want to add “–enable-stats=no” to increase performance):
    $ ./configure --enable-gui=no --enable-drivers=no --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --libdir=/usr/lib/x86_64-linux-gnu --prefix=/usr --sysconfdir=/etc
  5. Make the install target with root privileges:
    $ sudo make install

PHP segfaults with pdo_odbc and bound parameters on 64-bit platforms

The title says it all with this post: PHP segfaults with pdo_odbc on 64-bit platforms when using a query that has bound parameters (named, indexed/placeholder, bindParam(), and bindValue() in any combination).

I’ve submitted a pull request (which fails its Travis build due to 5.5.9 failing its Travis build) to keep this in the minds of the php maintainers as it’s a pretty severe problem for people using php in a more “corporate” environment (where postgres and mariadb/mysql aren’t as pervasive).

With our millions of records stored in MS SQL and iSeries DB2 UDB databases at my current employer – this is a huge problem. We’re basically confined to 32-bit environments unless we want to pay for an additional method to connect to the iSeries (IBM DB2 Connect) and even then we’d be reliant on the MS SQL ‘sqlsrv’ php driver which I’ve found to be incredibly slow with medium-sized or larger data sets.

This hasn’t been a huge problem yet for most people using Windows since IIS’ fastcgi support seems to be only 32-bit currently, but with the way Azure has been getting pushed and adopted I would assume that a demand for 64-bit fastcgi apps on Azure will be approaching soon.

php 5.5 32-bit on Azure x64

PHP 5.5 is pre-installed as 32-bit on a Microsoft Azure 64-bit “Standard” scaled “Website”.

While bugs for this issue have been outstanding for quite some time, I’ve compiled a version of pdo_odbc as a shared extension with the patches people have agreed upon. After taking a look at the history of pdo_odbc – my shared extension may work with php versions as far back as the last stable release of the 5.3 branch and has been compiled on Ubuntu 13.10 x64 (so it should work on most 64-bit Ubuntu/Debian derivatives that have glibc 2.14+) against the php 5.5.9 stable source.

The extension is relatively simple to toss in to your php installation – but use it at your own risk. I’ll try to remember to keep it updated – but hopefully this will just get fixed upstream.

Here’s a link to the php 5.5 (5.5.9 to be precise) 64-bit patched pdo_odbc shared extension compiled on Ubuntu (Ubuntu 13.10 – but should work on most modern Ubuntu/Debian variants without any problems).

On non-Ubuntu/Debian platforms, you may get an error like the following:

"PHP Startup: Unable to load dynamic library '/usr/lib64/php/modules/pdo_odbc.so' - libodbc.so.1: cannot open shared object file: No such file or directory"

You’ll probably need to create some symlinks.

If you get an error about glibc – it’s because I’ve compiled this against glibc 2.14 initially. This is a problem for both Ubuntu 12.04 LTS and CentOS, so I’ll likely be recompiling this against glibc 2.12 in the very near future.

I have been somewhat vague with this post on purpose compared to my normal “tutorial” type posts I do, due the technical nature of the problems described here. You should not be following a tutorial or step-by-step procedure without fully understanding the steps you’re executing when it comes to hacking extensions/patches into your programming language runtime – unexpected results may occur, which is why I’ve posted my compiled extension AS-IS with a “Use at your own risk” disclaimer.

PHP 5.5+ and OPCode Caching

For those of us still using PHP:

With PHP 5.5, the time has come when users no longer have to seek out one of many OPcode caching alternatives. PHP 5.5 has OPcache built right in.

Oh, but your favorite framework/library/ORM doesn’t support OPcache because it no longer has userland caching functionality? Well, now we’re in quite a predicament; PHP upgraded, new OPcode caching via OPcache, and yet this setup feels like we’ve lost something. Don’t worry, you don’t have to run a memcache server and install a memcache extension just for userland caching – there’s APCu.

APCu is a fully functioning userland caching implementation that can be overly simplified with this equation:
The old APC extension – OPcache’s OPcode caching = APCu

If you have a lot of code that does detection of the old APC extension, you can even enable the compile flag --with-apc-bc to enable full APC compatibility mode.

Now you have no excuse – upgrade to PHP 5.5+.

When to Use the Microsoft SQL Server ODBC Driver for Linux

When I originally wrote the how to, I was frustrated with Microsoft’s PDO driver being so. Incredibly.

Slow…

In my tests, I was using a table with a few hundred thousand rows. This table had up-to-date indexes that were relatively useful and fast.

As a starting point, I tested the query on  SQL Management Studio and I could return a few thousand rows in 0.1 seconds on our test server.
For the second test, I used pdo_sqlsrv (the Microsoft provided PHP driver for SQL Server) to do the same query, and it would take around 20-30 seconds.
As a final test, I accessed the database via pdo_odbc (with the installed Microsoft SQL ODBC drivers), and the same query that had just taken 20-30 seconds returned just as fast as SQL Management Studio. PDO_SQLSRV was far too slow and we needed to move to something faster.

Our results of using pdo_sqlsrv versus pdo_odbc (with the Microsoft SQL Server ODBC Driver for Linux) were pretty staggering

Our results of using pdo_sqlsrv versus pdo_odbc (with the Microsoft SQL Server Native Client 10) were pretty staggering

Here are some items (that I wish someone had told me when I was starting down this path) for you to consider when making your choice between pdo_odbc (using Microsoft SQL Server ODBC drivers), PDO_SQLSRV (Microsoft’s native SQL Server driver for php), or using the open-source freetds driver:

  • When choosing the odbc route – you lose a lot of the data type inference casting that the SQL Server driver for your programming language (such as pdo_sqlsrv) will do for you automatically, such as taking a date in the format of “03/01/2013” (March 1st, 2013) and inserting into a ‘date’ column as ‘2013-03-01’. This is no big deal if you start out knowing that these features aren’t there, but for porting code – it would’ve been nice to know right off the bat.
  • Need to connect to SQL Server older than SQL Server 2005? Forget about using ODBC (pdo_odbc in my case) method; it will continually complain and give you all sorts of fun errors that will be generally meaningless, yet when you switch to pdo_sqlsrv – it will instantly work. Awesome right? Your other choice in this situation is a freetds connection via odbc; this also has worked well for me and I will try to put up a tutorial on getting that up and running easily in the near future.
  • Do you only need to run a few queries here and there or will you be returning less than 1000 results from the database per query? You should probably just use the  driver that comes with your programming language (pdo_sqlsrv in my case) as it’ll be the most painless and the easiest to get started with.
  • Do you have a lot of code that is currently accepting strange formats for date (including slashes?) or do you have a lot of old stored procedures? You’ll probably want to go with (in order of preference): your language’s driver (pdo_sqlsrv in my case), freetds, and lastly the odbc route. Why is odbc last in this case? Because all your code, including stored procedures, will need to be modified in any places that you’re depending on implicit type casting that SQL Server or the current driver is doing for you. The odbc route also causes all input parameters to be accepted in as the C type ‘SQL_C_WCHAR’ more or less, which will only be implicitly cast to one of three SQL character types. If you go the odbc route, you’ll likely have to perform quit a few CAST(CAST(SOMETHING AS VARCHAR) AS SOMEOTHERTYPE) operations.

So – if you’ve gotten this far and still want to read the rest of my original post – here it is (also, this is the ODBC C Datatype to SQL DATA TYPE conversion chart – you’ll likely need this if you go the ODBC route).