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

[Benoît Minisini]

Le 07/04/2020 à 11:27, Yeshua Rodas a écrit :
> 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. :)
> 
> 
> 
> ----[ http://gambaswiki.org/wiki/doc/netiquette ]----
> 

Only SELECT queries return a Result object. You don't have access to the 
number of inserted rows at the moment.

Regards,

-- 
Benoît Minisini