Using RowSets with MBS

Databases
1

I’m hoping someone can help me here. I’m using SQLConnectionMBS to connect to a Microsoft SQL database so I can pull some rows from a table. I’m able to connect to the database successfully. The problem is that I can’t seem to work with RowSets. I can work with SQLCommandMBS but I need a RowSet so I can cursor through the rows as needed.

Here is my code:

// Connect to MSSQL Database

Var libtdsodbc As Folderitem = GetFolderItem("libtdsodbc.dylib")
Dim s As New SoftDeclareMBS
Call s.LoadLibrary(libtdsodbc.NativePath)

Var con As New SQLConnectionMBS

Try
  con.Option("UseAPI") = "ODBC"
  Var cs As String = "DRIVER="
  cs = cs + libtdsodbc.NativePath
  cs = cs + ";Server="+Session.sqlServer+";UId="+Session.sqlUser+";PWD="+Session.sqlPassword+";Database="+Session.sqlDatabase+";TDS_VERSION=7.2;Port="+Session.sqlPort
  con.Scrollable = True
  con.Connect(cs,"","",SQLConnectionMBS.kODBCClient)
  
Catch ex As RuntimeException
  MessageBox ex.Message
  Return Nil
End Try

// Iterate through rows using cmd.FetchNext - This Works

Var cmd As New SQLCommandMBS(con,"SELECT * FROM folders ORDER BY fldName")
cmd.Execute

While cmd.FetchNext
  lbFolders.AddRow(cmd.Field("fldName").asString)
  lbFolders.RowTagAt(lbFolders.LastAddedRowIndex) = cmd.Field("id").asString
Wend

// Create RowSet and and iterate through it - This does not work

Var db As New SQLDatabaseMBS
Var rs As RowSet = con.SQLSelectAsRowSet("SELECT * FROM folders ORDER BY fldName")

For Each r As DatabaseRow In rs
  lbFolders.AddRow(r.Column("fldName").StringValue)
  lbFolders.RowTagAt(lbFolders.LastAddedRowIndex) = r.Column("id").StringValue
Next

The error I get with the For Each loop is:

S1010 [iODBC][Driver Manager]Function sequence error

Is it possible to work with rowsets using MSSQL and MBS?

2

Well, it looks like I found the problem. I was missing this line in the connection block:

con.SetFileOption con.kOptionLibraryODBC, libtdsodbc

Adding that allows me to create the RowSet now.

1 Like