SQLDatabaseMBS (for MSSQL Server) issues

Add-Ons
1

Hello everyone,
I started using the SQLDatabaseMBS class to connect to MSSQL Server and I found 3 issues, 1 of which is really a show stopper for me.

  1. The most important for me is that the db.SQLExecute(“BEGIN TRANSACTION”) produces an error. I tried everything, including setting off the new db. Scrollable property, I tried setting db.AutoCommit property but nothing works. The only thing I found that works is if I set the cn.AutoCommit=0 that is set the AutoCommit property to a SQLConnectionMBS class but in this case I need to use the SQLConnectionMBS Methods and not the SQLDatabaseMBS methods which is a show stopper for me, as it would need to modify a lot of code.
    Does anybody know of a way to use the SQLDatabaseMBS methods in a transaction?

  2. The rs.Update does nothing (where rs is a RecordSet that was created by a db.SQLSelect("…") where db is a SQLDatabaseMBS). I haven’t tried the statements…

  3. In case I have a autoincrement identity (primary key) column in a table, the command SELECT SCOPE_IDENTITY() that gives back the last inserted id does nothing. At this point the work around could be that I could start a transaction and perform the insert via an SQLExecute method of a SQLConnectionMBS and then (while in that transaction) read the max(Id). That is some work but I could live with it…
    However, if someone knows another way that it might work I would greatly appreciate.

2

Well, we clearly state in our plugins that we do not support edit/update commands on recordset.
For the others I would need to try myself here.

3

Sorry Christian, I didn’t notice, however this is not a real problem for me. The transaction issue is the most important…

4 
db.SQLExecute "INSERT INTO TestTable (vorname, nachname) VALUES ('Hello', 'World')"
dim r as RecordSet = db.SQLSelect("SELECT @@IDENTITY")
MsgBox r.IdxField(1).StringValue

this also works for me here.
Problem seems to be that the sql library automatically wraps command in transaction, so BEGIN is not allowed as it will be put between begin/end anyway!?

5

Sorry, no idea why my queries here are wrapped in stored procedures.

6

Christian, the main issue for me is that the transaction is not working. If I can make a transaction with SQLDatabaseMBS then I can live with the other(s). Is it possible to have a solution for the transaction problem?

7

do problems go away if you use
db.Option(“UseAPI”) = “OLEDB”

before connecting? Seems like ODBC always wants to run in auto commit mode, but transactions work here with OLEDB.

8

Thank you Christian, I will check it out and let you know what happens.

9

[quote]do problems go away if you use
db.Option(“UseAPI”) = “OLEDB”

before connecting? Seems like ODBC always wants to run in auto commit mode, but transactions work here with OLED.[/quote]

Great Christian, now the transaction works as it should…
Thank you very much!