Talking to Sql Server

Databases
1

Good morning.

A previous project, written by someone else in something else used what appears to be a free version of SqlServer.

I rewrote everything in Xojo with Sqlite. Also wrote code to import the existing data. Everything appears to be working.

The problem is that to talk to the SqlServer database one needs to set up an ODBC connection. I feel this is rather non-obvious to the casual user.

Questions:

  1. Is there a way to talk to a local copy of SqlServer without setting up an ODBC connection.
    or
  2. Is there a way to set it up in code so the user does not have to do anything.

Thank you.

-Bob Gordon

2

See the example directory where you find “MSSQLServerExample”.
Should be peace of cake.

3

If you on a Mac, ODBC is the only way to connect to SQLServer, any version. Else, the MSSQLServerExample should do you up just fine. :slight_smile:

4

With MBS SQL examples we normally put all the connection parameters in one string so you don’t need to create a data source. The string specifies all with driver, database, server, credentials and other values.

5

I’ll take a look at the example after lunch. Thanks everyone for the info.

6

After looking at the example and trying some things, I was able to connect.

In case anyone else needs to deal with this, here are a few things I discovered:

  1. Get a copy of Microsoft’s SQL Server Management Studio.
  2. Instead of “localhost” as the example shows, use the Server name as shown in Management Studio.
  3. If they are using Windows Authentication, do not supply a user name or password.

Again, thank you. Hope my notes help someone in the future.

-Bob

7

If one needs to access SQLServer from OSX, one can use libtdsodbc.dylib, which is also available here:
(freetds.dmg) http://www.monkeybreadsoftware.de/xojo/download/plugin/Libs/

Then use a copyfiles step to copy this dylib to the apps resources folder, like this you do not have to install or license any odbc driver, just distribute your app and connect to SQLServer.

I tested like this, based on MBS examples:

[code]Sub Action()

// preload libs
Dim libtdsodbc As Folderitem = _
GetFolderItem(App.ExecutableFile.Parent.Parent.Child(“Resources”).NativePath _

  • “/libtdsodbc.dylib”,FolderItem.PathTypeNative)

Dim s As New SoftDeclareMBS
Call s.LoadLibrary(libtdsodbc.UnixPathMBS)

// connect
Dim con As New SQLConnectionMBS

Try

Dim cs As String = _
"DRIVER={FREETDS};Server="+iServer.Text+";UId="+iUser.Text+";PWD=" _
+ iPass.Text+";Database="+iDatabaseName.Text+";TDS_VERSION=7.2;Port="+iPort.Text

con.SetFileOption con.kOptionLibraryODBC, libtdsodbc
con.Option("UseAPI") = "ODBC" 
con.Connect(cs,"","",SQLConnectionMBS.kODBCClient)

// add time stamp
Dim cmd As New SQLCommandMBS(con, "insert into " _
+ iTable.Text+" ("+iColumn.Text+") VALUES (SYSUTCDATETIME())")

cmd.Execute

// no commit as we have autocommit
MsgBox "Timestamp saved."

// Disconnect is optional
// autodisconnect will ocur in destructor if needed
con.Disconnect

Catch r As RuntimeException
MsgBox r.message
End Try

End Sub
[/code]