[Gambas-user] Succesful multiple Insert return a failed Result

[Yeshua Rodas]

Hello people.

I'm having an issue working with SQLite.

I have a particular function for insert a batch of data and return the
number of affected rows:

Public Function myInsert() as Integer
' I have another function for create the connection, so
Dim conn as Connection = getMyConnection()

' I prepare a SQL with multiple INSERT, like:

Dim sQuery As String[] = []
sQuery.Push("INSERT INTO table (col1, col2, col3) VALUES (val11, val12,
val13)")
sQuery.Push("INSERT INTO table (col1, col2, col3) VALUES (val21, val22,
val23)");
sQuery.Push("INSERT INTO table (col1, col2, col3) VALUES (val31, val32,
val33)");

' Since I have already a valid and opened connection, I just execute all my
inserts in a single query call:

conn.Begin()
Dim insertResult As Result = conn.Exec(sQuery.Join(";"))
conn.Commit()

Return insertResult.Count

Catch
  conn.Rollback()
  Return -1

End

So, what I expect is that my function returns 3 and sees those 3 rows on my
table. However, what I'm getting is, on the table, the 3 inserted rows; but
on other hand, the "Catch" part is executed, returning my function -1
value.
I executed my program with a breakpoint just on "conn.Begin()" call to see
what is happening, and found that insertResult has the property Available
as False, and the Count as 0.
Also, instead of returning the 0 from insertResult, as I said, the Catch
part is executed, BUT the 3 inserted rows remain on the database.

I'm getting a bit confused with that.
Why could it be that if the Query is executed successfully, the Result is
not?

Should I reconsider to use the "Result way" via the Connection.Create()
call?

I try it.

Public Function myInsert() as Integer
' I have another function for create the connection, so
Dim conn as Connection = getMyConnection()

' I prepare a SQL with multiple INSERT, like:

Dim data as Collection[] = []
data.Push(["col1": val11, "col2": val12, "col3": val13])
data.Push(["col1": val21, "col2": val22, "col3": val23])
data.Push(["col1": val31, "col2": val32, "col3": val33])

conn.Begin()
Dim insertResult As Result = conn.Create("table")
For Each coll as Collection in data
  For Each val as Variant in coll
    insertResult[data.Key] = val
  Next
  insertResult.Update()
Next

conn.Commit()

Return insertResult.Count

Catch
  conn.Rollback()
  Return -1

End

By this approach the function returns 1. However, I think that this is
calling an INSERT CALL for each iteration. With 3 rows its OK, but what if
I have 10000 rows to insert? Can be the performance down by that way
instead of a single .Exec() call? Or doesn't matter since it is inside a
transaction?
Why the .Count property does not change?

Thank you all for your time and answers. :)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.gambas-basic.org/pipermail/user/attachments/20200407/40faea3e/attachment.html>