ActiveRecord and ARGen

Joost, FWIW, originally I had a number of problems with the XOJO native SQL Server plugin (including prepared statements) and switched to Christian’s MBS SQL plugin early on. Haven’t had a problem since … and I use prepared statements a lot!

Thank you Don… I have a license for the MBS SQL plugin so now is the time to use that one instead.

Thanks or the info.

where can I download MBS SQL plugin?

It’s disappointing that MS SQL Server has so many bugs with basic functionality. Seems like every time I try it I end up mad and frustrated about the lack of progress.

Don’t know if you need this or if would help you any, but on the off chance it would, I thought I’d put a little code (simplified from a routine in one of my desktop apps) here for you to see how I use the MBS SQL plugin with prepared statements. This snippet is dealing with 3 text fields from a control array placed on a window (wnd1) and the code updates the database with the values of the text boxes.

mDB is a property of App set to SQLDatabaseMBS type

[code] // connect to db
mDB = new SQLDatabaseMBS
mDB.Option(“UseAPI”) = “OLEDB”
mDB.Option(“SQLNCLI.LIBS”) = “sqlsrv32.dll” // Library included in Windows Vista and newer


if mDB.Connect then

con = mDB.Connection

mDB.Scrollable = false  // must do this or record sets are messed up on searching them (takes forever)
mDB.AutoCommit = mDB.kAutoCommitOff

MsgBox mDB.ErrorMessage
end if

// update the db with the text box values
Dim db As SQLDatabaseMBS = App.mDB

Dim rowID As Integer
rowID = 123

Dim sql As String = _
“UPDATE defrostData " + _
" SET DefrostsPerDay = :dd, " + _
" DefTimedFSTime = :DTfs, " + _
" DefTimedTermTemp = :DTtt " + _
" WHERE DefrostID = :dID;”

dim v as Variant = db.Prepare(sql)

dim p as SQLPreparedStatementMBS = v

p.BindType(“dd”, SQLPreparedStatementMBS.kTypeLong)
p.BindType(“DTfs”, SQLPreparedStatementMBS.kTypeDouble)
p.BindType(“DTtt”, SQLPreparedStatementMBS.kTypeDouble)
p.BindType(“dID”, SQLPreparedStatementMBS.kTypeLong)

if wnd1.txf(0).Text <> “—” THEN
p.Bind(“dd”, CLong(wnd1.txf(0).Text))
p.BindType(“dd”, SQLPreparedStatementMBS.kTypeNull)
end if

if wnd1.txf(1).Text <> “—” THEN
p.Bind(“DTfs”, CDbl(wnd1.txf(1).Text))
p.BindType(“DTfs”, SQLPreparedStatementMBS.kTypeNull)
end if

if wnd1.txf(2).Text <> “—” THEN
p.Bind(“DTtt”, CDbl(wnd1.txf(2).Text))
p.BindType(“DTtt”, SQLPreparedStatementMBS.kTypeNull)
end if

p.Bind(“dID”, rowID)


If db.Error Then

// open Modal MsgBox window
taMessage = "UPDATE Model Defrost Record Set  Data (saveToDB_DFST) DB Error: " + db.ErrorMessage


db.Commit   // Commit the recordset to the database

End If[/code]

Monkeybread Software web site
link text

Direct link.
MBS Xojo (Real Studio) SQL Plugin