iOS Question Sqlite BeginTransaction error: database is locked

Carlos marin

Active Member
Licensed User
Hi, I don't know what I'm doing wrong but every time I make a query and after the query I try to make an insert or an update to the bd it throws me the error: BeginTransaction error: database is locked. I try to close the resultset and the sql but I only get different errors. Please help
the code:
B4X:
c= s.ExecQuery("SELECT * FROM shopy") ' WHERE cod = '" & cod & "' ORDER BY nombre ASC")
    If c.NextRow  Then
        If c.GetString("id_emp") = id_emp And c.GetString("id_sede") = sede Then
            c= s.ExecQuery("SELECT * FROM shopy_l WHERE id_prod = '" & lblprod.Text  & "'")
            If c.NextRow Then
                s.ExecNonQuery("UPDATE shopy_l Set cant=cant+1 WHERE id_prod = '" & lblprod.Text & "'")
            Else
                s.ExecNonQuery2("INSERT INTO shopy_l VALUES (?, ?, ?, ?, ?, ?, ?)", Array As String (0,lblprod.Text, lblnombrepedido.Text , lblprecio2.Text ,"1",txtnotas.Text,items))
            End If
            carshopy.Show
        Else 
            c.Close
            Msgbox2("Msg","Hay productos pendientes de otro restaurante o servicio, desea limpiar el carrito?", "Carrito de compras", Array ("Si", "No"))
            Wait For Msg_Click (ButtonText As String)
            If ButtonText = "Si" Then
                s.ExecNonQuery("DELETE FROM shopy_l")
                s.ExecNonQuery("DELETE FROM shopy")
                s.ExecNonQuery2("INSERT INTO shopy VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Array As String (0,id_emp, sede , miclase.id, lbldomicilio.Text,"","","","","",miclase.contrato & "/" & typeproducto,miclase.res_nom & "/" & miclase.res_dir,miclase.res_latlng & "/" & miclase.res_tiempo))
                s.ExecNonQuery2("INSERT INTO shopy_l VALUES (?, ?, ?, ?, ?, ?, ?)", Array As String (0,lblprod.Text, lblnombrepedido.Text , lblprecio2.Text ,"1",txtnotas.Text,items))
                carshopy.Show
'                    Activity.Finish
                End If
        End If
    Else
        'si no existe ps que ingrese nuevo y lance
        s.ExecNonQuery2("INSERT INTO shopy VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Array As String (0,id_emp, sede , miclase.id, lbldomicilio.Text,"","","","","",miclase.contrato & "/" & typeproducto,miclase.res_nom & "/" & miclase.res_dir,miclase.res_latlng & "/" & miclase.res_tiempo ))
        s.ExecNonQuery2("INSERT INTO shopy_l VALUES (?, ?, ?, ?, ?, ?, ?)", Array As String (0,lblprod.Text, lblnombrepedido.Text , lblprecio2.Text ,"1",txtnotas.Text,items))
        carshopy.Show
   
    End If
c.Close
 

Attachments

Carlos marin

Active Member
Licensed User
Hi erel thanks for answering if I use s.BeginTransaction but I also get the same error. I do not understand if in b4a I do not have these problems that I am doing wrong :eek:
 

Attachments

Carlos marin

Active Member
Licensed User
Erel I did some tests and I think that is the problem that I have to close the connection on each page.
My question is: is it correct to initialize sql on each page or can I create it publicly on the main page?


Mi code:


B4X:
Main page 


Sub Process_Globals
    Public App As Application
    Public NavControl As NavigationController
    Dim xui As XUI
    Private Pg As Page
    Private s As SQL
end sub 


Private Sub Application_Start (Nav As NavigationController)


    NavControl = Nav
    Pg.Initialize("pg")
    Pg.RootPanel.Color = Colors.White
    NavControl.ShowPage(Pg)
    Pg.RootPanel.LoadLayout("inicio")
    If File.Exists(File.DirLibrary,"datos.db") = False Then
        File.Copy(File.DirAssets,"datos.db",File.DirLibrary,"datos.db")
    End If
    LocManager.Initialize("LocManager")
    s.Initialize(File.DirLibrary, "datos.db",True)
    s.BeginTransaction

end sub



   'OTHER PAGE- DETAIL
Public Sub Show
	If Pg.IsInitialized = False Then
		Pg.Initialize("pg")
		Pg.RootPanel.LoadLayout("vistaprodrest")
		Pg.RootPanel.Color = Colors.White
		'Pg.HideBackButton=True
		Pg.Title = miclase.dir
		Dim no As NativeObject = Main.NavControl
		no.GetField("navigationBar").GetField("topItem").SetField("backBarButtonItem", bb)
	End If
	s.Initialize(File.DirLibrary, "datos.db",True)
End sub 

sub btnagregar_Click 

        c= s.ExecQuery("SELECT * FROM shopy") ' WHERE cod = '" & cod & "' ORDER BY nombre ASC")
	If c.NextRow  Then
		If c.GetString("id_emp") = id_emp And c.GetString("id_sede") = sede Then
			c= s.ExecQuery("SELECT * FROM shopy_l WHERE id_prod = '" & lblprod.Text  & "'")
			If c.NextRow Then
				s.ExecNonQuery("UPDATE shopy_l Set cant=cant+1 WHERE id_prod = '" & lblprod.Text & "'")
			Else
				s.ExecNonQuery2("INSERT INTO shopy_l VALUES (?, ?, ?, ?, ?, ?, ?)", Array As String (0,lblprod.Text, lblnombrepedido.Text , lblprecio2.Text ,"1",txtnotas.Text,items))
			End If
			c.Close
			s.TransactionSuccessful
			carshopy.Show
'			Activity.Finish
		Else	
			c.Close
			Msgbox2("Msg","Hay productos pendientes de otro restaurante o servicio, desea limpiar el carrito?", "Carrito de compras", Array ("Si", "No"))
			Wait For Msg_Click (ButtonText As String)
			If ButtonText = "Si" Then
				s.ExecNonQuery("DELETE FROM shopy_l")
				s.ExecNonQuery("DELETE FROM shopy")
				s.ExecNonQuery2("INSERT INTO shopy VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Array As String (0,id_emp, sede , miclase.id, lbldomicilio.Text,"","","","","",miclase.contrato & "/" & typeproducto,miclase.res_nom & "/" & miclase.res_dir,miclase.res_latlng & "/" & miclase.res_tiempo))
				s.ExecNonQuery2("INSERT INTO shopy_l VALUES (?, ?, ?, ?, ?, ?, ?)", Array As String (0,lblprod.Text, lblnombrepedido.Text , lblprecio2.Text ,"1",txtnotas.Text,items))
				s.TransactionSuccessful
				carshopy.Show
'					Activity.Finish
				End If
		End If
	Else
		'si no existe ps que ingrese nuevo y lance
		s.ExecNonQuery2("INSERT INTO shopy VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Array As String (0,id_emp, sede , miclase.id, lbldomicilio.Text,"","","","","",miclase.contrato & "/" & typeproducto,miclase.res_nom & "/" & miclase.res_dir,miclase.res_latlng & "/" & miclase.res_tiempo ))
		s.ExecNonQuery2("INSERT INTO shopy_l VALUES (?, ?, ?, ?, ?, ?, ?)", Array As String (0,lblprod.Text, lblnombrepedido.Text , lblprecio2.Text ,"1",txtnotas.Text,items))
		s.TransactionSuccessful
		carshopy.Show
		'Activity.Finish
	End If
end sub
it's correct like this?
Thanks for the help!
 
Top