iOS Question Sqlite BeginTransaction error: database is locked

[Carlos marin]

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:

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

 

[Erel]

Do you call SQL.BeginTransaction anywhere in your code? Do you later close the transaction?

Does it work if you close 'c' before calling s.ExenNonQuery?

 

[Carlos marin]

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

 

[Carlos marin]

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:

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!

 

[Erel]

it's correct like this?

Not at all.

1. Use the same SQL object in all pages.
2. Never call BeginTransaction unless you want to immediately make changes and always close it when done.