Microsoft SQL Server connectivity
Sometimes you need to connect to Microsoft SQL Server and you know it's not as easy as with MySQL or PostgreSQL. SQL Server is a bit different which makes trouble and things more difficult.
Windows
On Windows we can just connect with "WIN-TEST\SQLEXPRESS@test" here and use Windows authentication. WIN-TEST is the name of the windows machine, SQLEXPRESS the name of the instance of the Microsoft SQL Server. After the @ comes with name of the database.
If we disable Windows authentication and enable login via name and password, define a new login "test" with password "hello123" and than we can just connect on Windows using the following code:
dim con as new SQLDatabaseMBS
con.Option("UseAPI") = "ODBC"
con.DatabaseName = "SQLServer:WIN-HO9KHI2N15T\SQLEXPRESS@test"
con.UserName = "test"
con.Password = "hello123"
con.Scrollable = false // disabling scrolling cursors is much faster for Microsoft SQL Server...
if con.Connect then
Msgbox "Connected"
Else
Msgbox con.ErrorMessage
end if
This does work on Windows PC. The same connection also works with TCP/IP as "tcp:192.168.2.123\SQLEXPRESS,1433@test". As you see we use IP here and the port 1433 separated by a comma. For this to work, the server must explicitly be configured to allow TCP/IP connections and to listen on the IPs of the server with the given port. This works well from a second PC.
MacOS
On MacOS, we can use the freetds library. On our website, we have a copy for you to download (http://www.monkeybreadsoftware.de/xojo/download/plugin/Libs/). We can use the ODBC connection type and the libtdsodbc.dylib library file. This library file allows us to make a ODBC style connection directly to the Microsoft SQL Server giving the port, IP and database name:
dim con as new SQLDatabaseMBS
con.Option(con.kOptionLibraryODBC) = "/Users/cs/Desktop/libtdsodbc.dylib"
con.DatabaseName = "ODBC:DRIVER={FREETDS" + path + "};Server=192.168.2.123;Database=test;TDS_VERSION=7.2;Port=1433"
con.UserName = "test"
con.Password = "hello123"
if con.Connect then
Msgbox "Connected"
Else
Msgbox con.ErrorMessage
end if
Linux
To connect on Linux, we need to install tds for ODBC, e.g. with the command "sudo apt-get install tdsodbc".
dim con as new SQLDatabaseMBS
con.DatabaseName = "ODBC:DRIVER={FreeTDS};Server=192.168.2.123;Database=test;TDS_VERSION=7.2;Port=1433"
con.UserName = "test"
con.Password = "hello123"
If you have no odbcinst.ini file in /etc, you can specify the .so library directly in the connection string instead of {FreeTDS}, you would specify libtdsodbc.so directly.
If everything works fine, you get your connection and all texts send/received. There is a problem that freetds can be compiled for UTF16 or UTF32 and if it doesn't match your iODBC compilation setting, it will fail to pass any text.