title: Configuring a connection timeout when connecting to a MS SQL Server database from a lua script using FreeTDS and unixODBC
body: <p>I have a lua script, running on a RHEL7 host, which has been written to connect to a MS SQL Server database (using FreeTDS / unixODBC) and retrieve a value. The script generally works OK, however, if the target server is offline/unavailable, it will just hang (for approximately 6 minutes 20 seconds), before something eventually times out and an error is returned. Want I want is to get this timeout down to a couple of seconds...</p>
  
  <p>I feel like this should be simple to do, but I can't seem to figure out how to specify/enforce a connection timeout to the SQL Server. </p>
  
  <p>There is a timeout configuration in the /etc/freetds.conf (see below), but this seems to have no effect.</p>
  
  <p>The script is normally called from nginx (openresty), which is the main reason I want to keep the timeout low, however the same hanging/timeout behaviour is observed running the script independently.</p>
  
  <p>Can anyone help me solve this? I've included the script and the contents of relevant related config files below.</p>
  
  <p>UPDATE:
  I noticed something worth mentioning during further testing - if I point my test script at an unused IP address OUTSIDE of the same subnet, my timeout when attempting to do the SQL server connection is ~6 minutes 21 seconds (as previously mentioned). If I point it at an unused IP address in the same subnet though, my timeout is consistently ~9 seconds. I'm no networking expert, but this suggests the 2 timeouts I'm actually seeing here are influenced by the network/transport layer - perhaps my local network switch is simply dropping the packets to the unknown subnet?</p>
  
  <p>This doesn't solve my original problem, but thought it was worth mentioning as an update...</p>
  
  <p>getvalue.lua:</p>
  
  <pre><code>local odbc = require "odbc"
  local keyvalue = "some_value"
  local retval = ""
  
  CNN_DRV = {
          {Driver='{FreeTDS}'};
          {Server='10.10.60.100,1433'};
          {Database='databasename'};
          {Uid='sqlusername'};
          {Pwd='sqlpassword'};
  };
  
  dbassert = odbc.assert
  env,err = odbc.environment()
  local cnn, err = env:driverconnect(CNN_DRV)
  stmt,err = cnn:prepare("{?= call dbo.GetRetValForKeyValue(?)}")
  ret = stmt:vbind_param_ulong(1, ret, odbc.PARAM_OUTPUT)
  val = stmt:vbind_param_char(2, keyvalue)
  dbassert(stmt:execute())
  
  stmt:foreach(function(f1)
          if tonumber(f1)
                  then
                  retval = string.format("%d", f1)
                  else
                  retval = ''
          end
  end
  )
  
  print(retval)
  </code></pre>
  
  <p>/etc/odbcinst.ini:</p>
  
  <pre><code>[FreeTDS]
  Description = FreeTDS TDS driver (for Sybase/MS SQL)
  Driver = /usr/lib64/libtdsodbc.so.0
  Setup = /usr/lib64/libtdsS.so.2
  </code></pre>
  
  <p>/etc/freetds.conf:</p>
  
  <pre><code># Global settings are overridden by those in a database
  # server specific section
  [global]
          # TDS protocol version
  ;       tds version = 4.2
  
          # Whether to write a TDSDUMP file for diagnostic purposes
          # (setting this to /tmp is insecure on a multi-user system)
  ;       dump file = /tmp/freetds.log
  ;       debug flags = 0xffff
  
          # Command and connection timeouts
  ;       timeout = 10
  ;       connect timeout = 10
  
          # If you get out-of-memory errors, it may mean that your client
          # is trying to allocate a huge buffer for a TEXT field.
          # Try setting 'text size' to a more reasonable limit
          text size = 64512
  </code></pre>