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
mDB.DatabaseName=“SQLServer:192.173.122.225,4120@xyz_lyttle”
mDB.UserName=“xyz_lyttle”
mDB.Password=“T@sk$Conc3pts”
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
else
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))
ELSE
p.BindType(“dd”, SQLPreparedStatementMBS.kTypeNull)
p.Bind(“dd”,Nil)
end if
if wnd1.txf(1).Text <> “—” THEN
p.Bind(“DTfs”, CDbl(wnd1.txf(1).Text))
ELSE
p.BindType(“DTfs”, SQLPreparedStatementMBS.kTypeNull)
p.Bind(“DTfs”,Nil)
end if
if wnd1.txf(2).Text <> “—” THEN
p.Bind(“DTtt”, CDbl(wnd1.txf(2).Text))
ELSE
p.BindType(“DTtt”, SQLPreparedStatementMBS.kTypeNull)
p.Bind(“DTtt”,Nil)
end if
p.Bind(“dID”, rowID)
p.SQLExecute
If db.Error Then
// open Modal MsgBox window
taMessage = "UPDATE Model Defrost Record Set Data (saveToDB_DFST) DB Error: " + db.ErrorMessage
wndModalMsgBox.ShowModal
else
db.Commit // Commit the recordset to the database
End If[/code]