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

[Gianluigi]

Il giorno mar 7 apr 2020 alle ore 16:22 T Lee Davidson <
t.lee.davidson at gmail.com> ha scritto:

> On 4/7/20 8:10 AM, Benoît Minisini wrote:
> > 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:
> >>
> [snip]>>
> >
> > Only SELECT queries return a Result object. You don't have access to the
> number of inserted rows at the moment.
> >
>
> If you need the number of rows inserted, one way might be to:
> 1. Count the number of rows in the table before the operation, then
> 2. Count the number of rows in the table after the operation, and
> 3. Return the difference.
>

Maybe trying this...
Create e new Database application and add a module named MBase in which you
paste this code:
-------------------------------------------
' Gambas module file

Public hConn As New Connection
Public $nTotalNumber As Long
Public $nNewNumber As Integer
Private $nRecords As Integer
Private $sNameDB As String = "Test.db"
Private $sPathDB As String = "/tmp"

Public Sub CheckDB() As Boolean

  If Not Exist($sPathDB) Then Return
  With hConn
    .Close()
    .Type = "sqlite3"
    .Host = $sPathDB
    .Open()
    If .Databases.Exist($sNameDB) Then .Databases.Remove($sNameDB)
    $nRecords = 99
    .Databases.Add($sNameDB)
    .Close()
    .Name = $sNameDB
    .Open()
    MakeTable()
    InserBasicRecords()
    Print ("#Database successfully created!")
    .Close()
  End With
Catch
  Message.Error(("Fatal error in ") & Error.Where & ("\nError number: ") &
Error.Code & ("\nCause: ") & Error.Text, "OK")
  CloseDB()
  Return True

End

Public Sub OpenDB()

  With hConn
    .Close()
    .Type = "sqlite3"
    .Host = $sPathDB
    .Name = $sNameDB
    .Open()
  End With
Catch
  CloseDB()
  Print Error.Text, Error.Code

End

Public Sub CloseDB()

  hConn.Close

End

Private Sub MakeTable()

  Dim hTable As Table = hConn.Tables.Add("tuser")

  hTable.Fields.Add("uskey", db.Serial)
  hTable.Fields.Add("usnam", db.String)
  hTable.Fields.Add("ussur", db.String)
  hTable.Fields.Add("usdat", db.Date)
  hTable.PrimaryKey = ["uskey"]
  hTable.Update
Catch
  CloseDB()
  Print Error.Text, Error.Code

End

Private Sub InserBasicRecords()

  Dim nCasual, i, y, m, d As Integer
  Dim sName, sSurname As String
  Dim aFirstName As String[] = ["Binah", "Hanna", "Selassie", "Okpara",
"Agustina", "Clema", "Coyan", "Eusebio", "Diego", "Alisha", "Daphne",
"Wiley", "Valentine", "Lidwina", "Roxana", "Edmund", "Hildeger",
"Adrianne", "Janette", "Quentin", "Sylvain", "Francesco", "Marco",
"Aldina", "Ornella", "Carmencita", "Galena", "Florentino", "Rodrigo",
"Benoit", "Mario"]
  Dim aLastName As String[] = ["Smith", "Johnson", "Williams", "Brown",
"Martin", "Bernard", "Dubois", "Thomas", "Robert", "Richard", "Müller",
"Schmidt", "Schneider", "Fischer", "Weber", "Meyer", "Gunnarsson",
"García", "González", "Rodríguez", "Fernández", "López", "Martínez",
"Jensen", "Nielsen", "Cohen", "Friedman", "Rossi", "Bianchi", "Minisini",
"Verdone"]
  Dim hDate As Date
  Dim hResult As Result

  Randomize
  hConn.Begin()
  For i = 0 To $nRecords
    nCasual = Rand(0, 30)
    sName = aFirstName[nCasual]
    nCasual = Rand(0, 30)
    sSurname = aLastName[nCasual]
    y = Rand(1959, 2000)
    m = Rand(1, 12)
    d = Rand(1, 28)
    hDate = Date(y, m, d)
    hResult = hConn.Create("tuser")
    hResult!usnam = sName
    hResult!ussur = sSurname
    hResult!usdat = hDate
    hResult.Update
  Next
  hConn.Commit()
  $nTotalNumber = ReturnNumber()
Catch
  CloseDB()
  Print Error.Text, Error.Code

End

Public Sub InsertNewRecords(sName As String, sSurname As String, hDate As
Date)

  Dim hResult As Result
  Dim iOldTotalNumber As Long = $nTotalNumber

  hResult = hConn.Create("tuser")
  hResult!usnam = sName
  hResult!ussur = sSurname
  hResult!usdat = hDate
  hResult.Update
  If ReturnNumber() Then
    $nTotalNumber = ReturnNumber()
    $nNewNumber += $nTotalNumber - iOldTotalNumber ' ;-) here you can also
just count the steps
  Endif

End

Private Function ReturnNumber() As Long

  Dim hResult As Result
  Dim i As Long


  hResult = hConn.Exec("SELECT MAX( uskey ) FROM tuser;")
  If hResult.Available Then
    If Not IsNull(hResult[0]) Then
      i = hResult[0]
      Return i
    Endif
  Endif
  Return Null

End
-----------------------------------------------------
This is the FMain.class code:

' Gambas class file


Public hButton As Button

Public Sub Form_Open()

  Dim hButton As New Button(Me) As "Button1"

  With hButton
    .X = 120
    .Y = 120
    .H = 100
    .W = 200
    .Text = "Click me"
  End With
  MBase.CheckDB()

End

Public Sub Button1_Click()

  ' Inserts three new records
  MBase.OpenDB()
  MBase.hConn.Begin()
  For i As Integer = 0 To 2
    MBase.InsertNewRecords("Mame_" & CStr(i + 1), "Surname_" & CStr(i + 1),
Date(Now))
  Next
  MBase.hConn.Commit()
  ' Writes record numbers
  Print "The total records are "; MBase.$nTotalNumber; ", of which new ";
MBase.$nNewNumber
Catch
  MBase.hConn.Rollback()

End
--------------------------------------

Regards
Gianluigi
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.gambas-basic.org/pipermail/user/attachments/20200407/a5300d9d/attachment-0001.html>