When ogr_fdw came out, I was very excited to try ogr_fdw out on windows. To start with I used the default GDAL that we package with PostGIS, which is built under mingw-w64 (both 32-bit and 64-bit versions). Then I thought imagine how much more I can get by compiling with more drivers. A big one on my list was ODBC. Sadly this did not work out of the box under mingw-w64.
For windows folks who want to try out the ogr_fdw, I have binaries for PostgreSQL 9.4 FDWs which includes ogr_fdw and some of my other favorites. I do have the 9.3s as well, but haven't written up an article about them until I test them on my production instances. The links to those are the same as 9.4, just replace 94 with 93 in the download link. These include ODBC support which should allow you to query a lot of ODBC datasources.
Normally when I try to compile something that depends on ODBC using mingw, the configure scripts or make files are setup to assume you are using UnixODBC (which works but seems to use a different ODBC Manager and drags in all these extra dependencies). The way I normally fix this to use the native ODBC support is to replace all references to -lodbc in the configure script with -lodbc32. Yes even the windows 64-bit ODBC is called odbc32 but put in the system32 folder for 64-bit and SysWow64 for 32-bit. I imagine Microsoft came up with this confusing convention for backward compatibility. So I did that for GDAL, and to my horror that did not work.
What was the problem? GDAL had this extra dependency on odbcinst library. Although the odbcinst header is packaged with mingw-w64, there is no associated library. Searched thru my windows/system32 folder and my mingw-w64 libodbc*.a files and couldn't find such a thing. So I removed this thing from the configure script, and GDAL at least got past configure and happily started compiling, and then failed when linking. So I searched for the function it couldn't find, and discovered this was in a library called odbccp32. So to compile with odbc support, as noted in my ogr_fdw_build gist
- edit the configure script in gdal source tree and replace all references to -lodbc with -lodbc32, and -lodbcinst with -lodbccp32
- Then add in your configure
--with-odbc=/mingw/${MINGHOST} where MINGHOST is set to x86_64-w64-mingw32 for the 64-bit mingw-w64 chain and i686-w64-mingw32 for the 32-bit mingw-w64 chain.
With this I was able to build libgdal with native windows ODBC support that just depends on the windows packaged odbc system dlls (so no extra dependencies). I tested by querying one of my clients SQL Server databases via ogr_fdw. One issue I ran into is it didn't handle SQL Server datetime right and kept on giving error ERROR: timestamp(62258384) precision must be between 0 and 6. So I had to change the ogr_fdw_info generated definition to bring in datetimes as varchar instead of timestamp and then just cast it to timestamp as part of my PostgreSQL query. This may be an idiosyncracy with how lengths have changed in windows ODBC that I have to patch or something with the ogr_fdw. I haven't tried the MSSpatial driver, but that suddenly showed up as an option after compiling with ODBC support. Also had some issues with UTF encoding which I was able to work thru by stripping high-byte characters in my PostgreSQL query.
One of my plans coming PostGIS 2.2 is to package a GDAL with more drivers - next on my list being SQLite family. I also need to figure out a way to have PostGIS gdal be swappable with the Visual C++ built ones. I can do that with for example curl and libxml, but the VC gdal last I looked seemed to expose weird named symbols so never hooks up. If I can get this going, then people can just swap out the GDAL we package with PostGIS with their own to get proprietary drivers like MrSID and so forth. Why don't I just try to build with it with VC++? Because then I've got to ship a VC++ runtime if I don't build with the same one EDB is using, and they use different flavors for each version of PostgreSQL. I also feel uncomfortable around Visual Studio for anything other than web development.
