Correct way to build luasql-odbc on windows platform

[saspivey98]

Hello,

I have been using this platform on linux and it has worked great. I like this package :)

I am currently testing the same software on windows, which requires a standalone lua binaries. I have been able to compile and build it, but have run into issues. This may be my lack of knowledge of the Windows platform. I have SQL Server (64-bit) ODBC driver installed and successfully created a DSN using that driver.

Dev Specs:

lua: 5.3.6
luarocks 3.9.1
windows 10

Things I have tried:

1.) using mingw64 unix-odbc binaries...

You can download the same SQL binaries using pacman -S mingw-w64-x86_64-unixodbc. Redirecting the ODBC_DIR to mingw64 binaries folder compiles succesfully in luarocks, but can not find any datasources.

2.) Using Microsoft SDK package

I assumed that because I was using the linux dev packages wouldn't nicely with Windows, I would look for sql.h through the SQL SDK and compile there. Unfortunately, there seems to be no such file. Looking at this page, it seems they call it Sqlncli.h. I changed the rockspec and pointed it to that file, which compiled correctly, but still does not find any DSNs.

Do you have any tips, guides, or recommendations to building in Windows? I looked into lua-odbc module which when running found the drivers and DSN datasource I had created, but I'd prefer to keep using this if possible.

[blumf]

You're getting several things mixed up:

1. Not sure why you'd use unixODBC on Windows, instead of Windows' actual ODBC system
2. As such, you've configured a DSN in the Windows ODBC system, and not unixODBC system (not done this on Win myself, but I assume, like under *nix systems, you have to create a file somewhere with the DSN info for unixODBC to see it, but then you'd also have to install the required DB drivers for unixODBC to use as well)
3. sql.h is the standard ODBC include needed (thank you MS, for such confusing naming!), and should be in the mingw install as standard. sqlncli.h is an SQL Server specific include, not ODBC itself.

Also, keep in mind, the 'SQL Server' driver that comes as standard with Windows is for an old version of SQL Server and may not work with newer installs. So you might have to install a newer driver

[saspivey98]

Hi, thanks for responding.

Not sure why you'd use unixODBC on Windows, instead of Windows' actual ODBC system

I would rather use MS's built-in, but just wanted to get the thing to work to see some progress - which when you work with something you know, it usually helps. I freely admit I am confused by software development environment in Windows.

*nix systems, you have to create a file somewhere with the DSN info for unixODBC to see it

I did try running the odbcinst.exe, but nothing was generated. Tried adding the drivers manually and changed the odbcinst.ini & odbc.ini files, but had no luck

should be in the mingw install as standard

I checked and there is some headers files in C:\msys64\mingw64 --> \include\. I tried referencing those luarocks install luasql-odbc ODBC_DIR="C:\msys64\mingw64", which does not build. Says: cannot find -lodbc: no such file or directory. I am not sure what it is looking for, as looking into the rockspec, it only looks for sql.h as an external dependency...

So you might have to install a newer driver

Went and downloaded ODBC Driver 18 for SQL Server, and made sure to include ODBC Driver for SQL Server SDK feature. This also generated the correct header files in Program Files (x86)\Windows Kits\10\..., but when trying to build, it specifies that it can't find kerneltypes.h

I can post some logs if that is helpful. My guess is using the included SQL headers in mingw64 is what I will eventually want - do you have any pointers to where I can look?

[blumf]

I checked and there is some headers files in C:\msys64\mingw64 --> \include. I tried referencing those luarocks install luasql-odbc ODBC_DIR="C:\msys64\mingw64", which does not build. Says: cannot find -lodbc: no such file or directory

That suggest the rockspec is wrong, it should be looking for -lodbc32 (yes, even on a 64 bit build!! MS love to screw around like that), can't remember off hand, but maybe also -lodbccp32

Went and downloaded ODBC Driver 18 for SQL Server, and made sure to include ODBC Driver for SQL Server SDK

This isn't necessary, the code only cares about the ODBC system, not any particular ODBC driver. That's the whole point of ODBC. You just need the driver for running, not building.

[saspivey98]

Okay, so changing the rockspec file to have libraries = { "odbc32" } makes it all work as intended. Thanks for your help!

Should a change be proposed for a different windows build? I'm not sure if the author is actively accepted PRs, but it seems it would make sense to add?

build = {
    platforms = { 
         windows = {} ...
         linux = {} ...
   }
}

[Arethusag]

I encountered this same problem compiling luasql on Windows. posting here what worked for me in-case anyone else encounters the same issue

lua version: 5.1.5 luarocks version: 3.9.2 Windows 10.

1) Get gcc by installing the [mingw-w64-x86_64-toolchain] (https://packages.msys2.org/groups/mingw-w64-x86_64-toolchain) using msys2. I added the mingw64 bin to path and removed the x86_64-w64- prefix from the toolchain executables (i.e x86_64-w64-mingw32-make becomes mingw32-make), so that the makefile in this repo can actually use the commands. Warning that this path modification may conflict if you have MVSC build tools installed.

2) install the microsoft odbc driver from here, this provides the odbc32.lib file that is needed for luasql.

3) download the odbc rockspec from this repo and change the rockspec so that libraries = { "odbc32" }

4) the sql.h header files are in C:\msys64\mingw64\include, so when installing luasql via luarocks, point the ODBC_DIR here.

5) Since I am using lua5.1, the full command would be: luarocks install C:\Path\to\rockspec\luasql-odbc-2.6.0-2.rockspec --lua-version=5.1 ODBC_INCDIR=c:\msys64\mingw64\include

+1 for @saspivey98's suggestion to propose a different build tool for windows

[saspivey98]

Just added a PR #156 that should fix this issue on Windows while not affecting *nix based systems.

[tomasguisasola]

Sorry for so late response.

I've commited the PR and corrected some URLs. Could you test it?

[saspivey98]

I can confirm that this now builds on Windows via luarocks. Thanks @tomasguisasola!