After a couple weeks of development, debugging, and testing, the revised scripts have been put into production. We just came across a couple of finer points that we had to word around. I’m guessing that these may be resolved in the future by switching to TDBC.

In the previous post on this topic I mentioned that we had to use a connection string/dsn instead of a username and password. That wasn’t too bad to figure out.

Luckily, the unixodbc package comes with a command line sql utility called isql that allows us to test sql scripts outside of the application, which comes in handy.

Out of the gate it turns out that some of the queries using the ‘database’ method (as opposed to the ‘statement’ method) of doing simple queries or purging tables would lock up the connection, and all subsequent queries wouldn’t fail, but they would just return an empty set. To get around this we switched to using the ‘statement’ methods of doing the queries, which also allowed us to do some defensive programming by catching and handling the errors instead of it causing the script to fail.

database msdb "DSN=XXX-aws;UID=MSUser;PWD=MSPasswd;Encrypt=yes"
set msSql "select * from mytable where key = ? and name = ?;"
set msVals [list "searchkey" "Bob Smith"]
msdb statement insQry $msSql
if { [catch { insQry $msVals } sqlErr] } {
      # don't care about duplicate key errors
      if { [string first "Cannot insert duplicate key" $sqlErr] == -1 } {
          puts "----------------------------------------------------------"
          puts "DRIVER ERROR: $sqlErr
          puts "SQL: $msSql"
          puts "Params: "
          foreach thisParam $msVals {
            puts "   ->$thisParam"
          }
          puts "----------------------------------------------------------"
      }
}
insQry drop
msdb disconnect

Using the statment method also allows us to use prepared statement, which helps protect from sql injections attacks.

We still had one perplexing issue, where doing a query or purge of a specific table using prepared statements would lock up the connection in the same manner as using the database method mentioned above, however it couldn’t be cleared by dropping the statment. The only way to clear it was to disconnect and then reconnect the database connection. We could duplicate this problem using isql on the command line, so I think it was either a freetds issue, or possibly a database server issue. It could also be due to the database server was not in the local data center (linux server in Dallas, database server in Seattle).

Aside from those issues everything is up and running, and luckily our datasets are not so big that there is a noticable slowdown with the processing.