Re: luasql.odbc and SQL Server 2005

[Robert Raschke <rrlua@...>]

The sys.tables table in SQL Server contains fields with pretty
SQLServer specific types: datetime and bit.  It also has nchar and
nvarchar, which I am unsure whether LuaSQL handles just now.

You could change your query to cast every field explicitly (slightly tedious).

Or you could use ADO via LuaCOM directly:

	require "luacom"

	conn = luacom.CreateObject("ADODB.Connection")

	conn:Open("Provider=sqloledb;Server=" .. server ..
			";Database=" .. name ..
			";UID=" .. user ..
			";PWD=" .. password)

	rs = conn:Execute("SELECT * FROM sys.tables")

	while not rs.EOF do
		for i = 0, rs.Fields.Count - 1 do
			local fld = rs.Fields:Item(i)
			if fld and fld.Value then
				print(fld.Name, fld.Value)
			end
		end
		rs:MoveNext()
	end
	rs:Close()

	_rs, recs = conn:Execute([[
		INSERT INTO A_TABLE (
			AN_INT_FIELD,
			A_VARCHAR_FIELD
		)
		VALUES (
			42,
			'another value'
		)]])
	print("number of records affected: " .. recs)

	conn:Close()

(Not tested just now, but copied from working code, so ...)

Robby