Insert in MSSQL with MBS SQL plugin - Invalid cursor state


i’m trying to insert into a mssql database using SQLCommand MBS and get this error:

24000 [FreeTDS][SQL Server]Invalid cursor state

The thing is that the record is successfully inserted in the database but I get an error.
Also, a simple select in the database works as intended.

I use this method to insert a record:

[code] dim SQL as string = "Insert into “+me.kTableName+”(CT_Num,CT_Intitule,CT_Type,CG_NumPrinc,CT_NumPayeur,N_Risque,N_CatTarif,N_CatCompta,N_Period,N_Expedition,N_Condition,CT_DateCreate,CT_Classement,bb_uuid) “+ _
" values (:ctnum,:ctintitule,:cttype,:cgnumprinc,:ctnumpayeur,:nrisque,:ncattarif,:ncatcompta,:nperiod,:nexpedition,:ncondition,:ctdatecreate,:ctclassement,:bbuuid)”

dim tDate as new Date
dim cmd as new SQLCommandMBS(me.dbSage.db,SQL)
cmd.Param(“ctnum”).setAsString(new SQLStringMBS(me.numero_compte_tier))
cmd.Param(“ctintitule”).setAsString(new SQLStringMBS(me.intitule))
cmd.Param(“cgnumprinc”).setAsString(new SQLStringMBS(me.account_id))
cmd.Param(“ctnumpayeur”).setAsString(new SQLStringMBS(me.num_payeur))
cmd.Param(“ctdatecreate”).setAsDateTime(new SQLDateTimeMBS(tDate))
cmd.Param(“bbuuid”).setAsString(new SQLStringMBS(me.classement))
catch r as SQLErrorExceptionMBS
// SAConnection::Rollback()
// can also throw an exception
// (if a network error for example),
// we will be ready

  // on error rollback changes
  if me.db<>nil then
  end if
catch x as SQLErrorExceptionMBS
  // ignore
end try

// show error message
MsgBox r.message

end try[/code]

I also tried with me.db.InsertRecord(me.kTableName,d) using a dictionnary but I get the same error anyway.

My connect method to the database:

[code]// preload libs, this helps on Linux to find them
if not me.db.isConnected then
dim tItem as XOJO.IO.FolderItem
tItem = Xojo.IO.SpecialFolder.GetResource(“libtdsodbc.dylib”)

dim libtdsodbc as FolderItem = GetFolderItem(tItem.path, FolderItem.PathTypeShell)

dim s as new SoftDeclareMBS
call s.LoadLibrary(libtdsodbc.UnixpathMBS)

// connect
// we used Microsoft SQL Server 2008 and run app on Windows to test this
dim cs as string = “DRIVER={FREETDS};Server=”+me.kHost+";UId="+me.kUser+";PWD="+me.kPassword+";Database="+me.kDatabase+";TDS_VERSION=7.2"
me.db.SetFileOption me.db.kOptionLibraryODBC, libtdsodbc
me.db.Option(“UseAPI”) = “ODBC”
me.db.Option(“AutoCache”) = “true”
me.db.Scrollable = false // disabling scrolling cursors is much faster for Microsoft SQL Server…

catch r as RuntimeException
MsgBox r.message
end try
end if



is that due to AutoCache being true?

And make sure that you do not use the same connection for more then 1 querry at a time.

Autocache set to false solves the problem, thanks.

Yes, I will check the use of differents connection if I have many queries

We have three ways to connect to MS SQL and one has that problem only.

Just wanted to help.

Appreciated. I’ll change the AutoCache to not try to cache, if there is no result.

where should i add in the code for AutoCache???

// turn on auto cache
c.Option("AutoCache") = "true"

can be set for connection to cache all RecordSets automatically locally in memory.