Android Question sqlite do not save .. and error ....why?

[fifiddu70]

hello, i have one app with SQL lite database, in this app i use this class: CameraExClass, FileProvider, ExternalStorage, QrGenerator,
if save one element in edittext with this metod:

 dbsql.ExecNonQuery2("INSERT INTO field1 VALUES (?)", Array As String(txtcaricaporodotto.Text & " - " & "Euro" & " " & txtcaricoeuro.Text))

, i recevie this error

main_btnaddcliente_click (java line: 1124)
java.lang.NullPointerException: Attempt to invoke virtual method 'android.database.sqlite.SQLiteStatement android.database.sqlite.SQLiteDatabase.compileStatement(java.lang.String)' on a null object reference
at anywheresoftware.b4a.sql.SQL.ExecNonQuery2(SQL.java:85)
at com.partannasoftware.LePre2020.main._btnaddcliente_click(main.java:1124)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:213)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:197)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:193)
at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:80)
at android.view.View.performClick(View.java:7870)
at android.widget.TextView.performClick(TextView.java:14970)
at android.view.View.performClickInternal(View.java:7839)
at android.view.View.access$3600(View.java:886)
at android.view.View$PerformClick.run(View.java:29363)
at android.os.Handler.handleCallback(Handler.java:883)
at android.os.Handler.dispatchMessage(Handler.java:100)
at android.os.Looper.loop(Looper.java:237)
at android.app.ActivityThread.main(ActivityThread.java:7948)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1075)

this is the button complete for saving the element in database:

Sub btnpiu_Click
    If txtcaricoeuro.Text="" Then
        
        MsgboxAsync("inset element before save","ALERT !!")
        
    Else
        If txtcaricoeuro.Text.Contains(",") = True Then
            txtcaricoeuro.Text.Replace(",",".")
        End If
    
        If txtcaricaporodotto.Text="" Then
            
            MsgboxAsync("insert element before save","ALERT !!")
        Else
            dbsql.ExecNonQuery2("INSERT INTO field1 VALUES (?)", Array As String(txtcaricaporodotto.Text & " - " & "Euro" & " " & txtcaricoeuro.Text)) ' when in debug in this line i have the error
            spm1.Add (txtcaricaporodotto.Text & " " & "Euro" & " " & txtcaricoeuro.Text) '
            txtcaricoeuro.Text="" '
            dbcursor.Close
            MsgboxAsync("Element added","DONE")
            txtcaricaporodotto.Text=""
            
        End If
            
            
    End If
    
    
    ps1.HideKeyboard(Activity)
    txtcaricoeuro.Text=0
End Sub

 

[LucaMs]

Add:

Else
Log(dbsql.IsInitialized)
dbsql.ExecNonQuery2("IN...

 

[fifiddu70]

Add:

yes is true

 

[LucaMs]

btnaddcliente_click

AddCliente but the query does other; are you sure that's the line?

 

[Mahares]

INSERT INTO field1 VALUES (?)

aren't you missing the table name

 

[fifiddu70]

ok the SQL variable are declared in global and receive the old error, now receive the new error after change variable sql in process global
java.lang.RuntimeException: Object should first be initialized (Cursor).
this is little part of code for see variable sql and cursor:

#Region  Project Attributes
    #ApplicationLabel: LePre2020
    #VersionCode: 1
    #VersionName: 1.0
    'SupportedOrientations possible values: unspecified, landscape or portrait.
    #SupportedOrientations: portrait
    #CanInstallToExternalStorage: True
    #BridgeLogger: True
    #AdditionalJar: com.google.android.gms:play-services-vision
#End Region


#Region  Activity Attributes
    #FullScreen: False
    #IncludeTitle: True
   
#End Region

Sub Process_Globals
   
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Dim SQL2 As SQL ' the variable sql'
    Dim ipstampantet As TextWriter
    Dim ipstampanter As TextReader
    Dim portastampantet As TextWriter
    Dim portastampanter As TextReader
    Dim Server As ServerSocket
    Dim wifi1 As WifiManager
    Dim wifi2 As WiFiConnect
    Dim CltSock As Socket
    Dim AStreams As AsyncStreams
    Dim connected As Boolean
    Private voice  As VoiceRecognition
    Private frontCamera As Boolean = False
    Private detector As JavaObject
    Private SearchForBarcodes As Boolean
    Private LastPreview As Long
    Private IntervalBetweenPreviewsMs As Int = 3000
    Dim timerfront As Timer
    Dim restart As Timer
    Dim mp As MediaPlayer
    Private RP As RuntimePermissions
   
   
   
   

   
End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.
   
    Dim cursor2 As Cursor ' the variable cursor
    Dim v_data As Long
    v_data = DateTime.now
    Dim writer As TextWriter
    Dim reader As TextReader
    Dim now As Long
    now=DateTime.now
    Dim ps1 As Phone
    Dim lst1 As List
    Dim id As Int
    Dim val, TotalVal As Double  
    Dim pk As Phone
    Dim awakw As PhoneWakeState
    Private pnlbase As Panel
    Private Panel1 As Panel
    Private camEx As CameraExClass
    Private pnlDrawing As Panel
    Private cvs As B4XCanvas
    Private imsqrcode As ImageView
    Private txtqrcode As EditText
    Private pnlleggiprivacy As Panel
    Private btnindietro As Button
    Private txtprivacy As EditText
    Private btnChangeCamera As Button
    Dim now As Long
    now=DateTime.now
    Private pnlreader As Panel
    Private txtreader As EditText
    Private btnelimina As Button
    Private btninvia As Button
    Private btnchiudlista As Button
    Private pnlqrcode As Panel
    Private ImageView2 As B4XView
    Private ImageView1 As B4XView
    Private btnbackqrcode As Button
    Private txtscriviqrcode As EditText
    Private imgbocca As ImageView
    Private imggomma As ImageView
    Private imgshare As ImageView
    Private imgcrea As ImageView
    Private imgeliminaqr As ImageView
    Private pnlwifi As Panel
    Private lblporta As Label
    Private txtport As EditText
    Private lblipserver As Label
    Private txtipserver As EditText
    Private btnsalvaport As Button
    Private btnsaveipserver As Button
    Private btn_client As Button
    Private btntrasmetti As Button
    Dim linecod As String
    Private btnstampa As Button
    Private pnllavoro As Panel
    Private speuro As Spinner
    Private lsw1 As ListView
    Private txtclienten As EditText
    Private txtnome As EditText
    Private lblprodotto As Label
    Private btnadd As Button
    Private lbltipo As Label
    Private lblmanualmente As Label
    Private txttipo As EditText
    Private lbleuro As Label
    Private lbleurotxt As Label
    Private lbltoteuro As Label
    Private txteuro As EditText
    Private txttotaleeuro As EditText
    Private btnpiu As Button
    Private btnstampal As Button
    Private txtordineinvisibile As EditText
    Private txtlistview As EditText
    Private txtmn1 As EditText
    Private spm1 As Spinner
    Private spquantita As Spinner
    Private txtmn1noeuro As EditText
    Private spuscite As Spinner
    Private txteuroparziale As EditText
    Private lblcaricoprodotti As Label
    Private spclienti As Spinner
    Private txtcaricaporodotto As EditText
    Private txtcaricoeuro As EditText
    Private pnlaggmod As Panel
    Private btnaddclprod As Button
    Private btnup1 As Button
    Private lblmodificaprodotto As Label
    Private txtcliente As EditText
    Private btnaddcliente As Button
    Private lbladdcliente As Label
    Private btnchiudimodifica As Button
    Private txteuroinvisible As EditText
End Sub

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
   
    If FirstTime Then
        CreateDetector (Array("ALL_FORMATS"))
        Server.Initialize(9093, "Server")
        Server.Listen
        Log("MyIp = " & Server.GetMyIP)
    End If
   
        mp.Initialize
    Activity.LoadLayout("LePre2020")
       
    If File.Exists(File.DirRootExternal, "data.db") = False Then
        File.Copy(File.DirAssets, "data.db", File.DirRootExternal, "data.db")
    Else
       
        cursor2.IsInitialized
    SQL2.Initialize(File.DirRootExternal, "data.db", True)
    End If
   
    'File.Delete(File.DirRootExternal,"data.db")

i have sql library version 1.50, is correct?

i try change name at the cursor and the sql variables but not resolve.

Sub btnpiu_Click
    If txtcaricoeuro.Text="" Then
        
        MsgboxAsync("","ALERT !!")
        
    Else
        If txtcaricoeuro.Text.Contains(",") = True Then
            txtcaricoeuro.Text.Replace(",",".")
        End If
    
        If txtcaricaporodotto.Text="" Then
            
            MsgboxAsync("","ALERT !!")
        Else
            SQL2.ExecNonQuery2("INSERT INTO field1 VALUES (?)", Array As String(txtcaricaporodotto.Text & " - " & "Euro" & " " & txtcaricoeuro.Text))
            spm1.Add (txtcaricaporodotto.Text & " " & "Euro" & " " & txtcaricoeuro.Text)
            txtcaricoeuro.Text=""
            cursor2.Close
            txtcaricaporodotto.Text=""
            
        End If
            
            
    End If
    
    
    ps1.HideKeyboard(Activity)
    txtcaricoeuro.Text=0
End Sub

 

[fifiddu70]

 

[fifiddu70]

this is the database maked with sqlite database browser

 

[OliverA]

ExecNonQuery2 does not use a cursor. So why use a cursor? The only methods that return a cursor are ExecQuery, ExecQuery2 and ExecQueryAsync. None of these are used in your posted code.

 

[fifiddu70]

ExecNonQuery2 does not use a cursor. So why use a cursor? The only methods that return a cursor are ExecQuery, ExecQuery2 and ExecQueryAsync. None of these are used in your posted code.

OliverA i have other app with this metod and ok, please send your mail, i sent my project

 

[mangojack]

As @LucaMs alluded to in post #4 ... the original error is raised in btnAddClient_Click

at com.partannasoftware.LePre2020.main._btnaddcliente_click(main.java:1124)

Yet you have posted code for the Sub btnpiu_Click (posts #1 & 6)

Have you checked this ?

 

[fifiddu70]

As @LucaMs alluded to in post #4 ... the original error is raised in btnAddClient_Click

at com.partannasoftware.LePre2020.main._btnaddcliente_click(main.java:1124)

Yet you have posted code for the Sub btnpiu_Click (posts #1 & 6)

Have you checked this ?

yes mangojack, i have two buttons for save the field, btnaddcliente and btnpiu i try in first post with btnaddcliente, i try with btnpiu but this error is in all buttons. sorry for posted other button after first post, if delete cursor after save in button the saved work but after restart app and recall the field the element not saved.

 

[Erel]

I don't understand this code:

    If File.Exists(File.DirRootExternal, "data.db") = False Then
        File.Copy(File.DirAssets, "data.db", File.DirRootExternal, "data.db")
    Else
       
        cursor2.IsInitialized
    SQL2.Initialize(File.DirRootExternal, "data.db", True)
    End If

1. Seems like you are not initializing the SQL object if the database already exists.
2. Why use File.DirRootExternal? Use File.DirInternal.
3. Why call cursor2.IsInitialized? Why the cursor is a global variable?
4. Why is this code not in the starter service? Why should it be tied to the activity life cycle? (B4XPages makes all of these issues trivial).

 

[LucaMs]

i have sql library version 1.50, is correct?

Yes.

https://www.b4x.com/android/help/sql.html#cursor

 

[fifiddu70]

Solved the problem, thanks also to your help, I made the following corrections: I moved the cursor variable to globals, deleted the two cursor.close in their respective save buttons, I deleted cursor2.IsInitialized to check if its initialization was true, and I moved the whole database to file.dirinternal everything as said by Erel, and also by my friend OliverA, everything seems to work perfectly, but I wanted to say that I have many programs made with the same system in fact it was a copy and paste the code save the data that I used and still use in another app, only here it doesn't seem to give the same results, yet in my previous apps I use the cursor.close neo save buttons without ever having had any problems, will it be a mystery? anyway I want to thank you all for giving me some help, now I can complete my project by completing it with the code still to be inserted.

 

[LucaMs]

but I wanted to say that I have many programs made with the same system in fact it was a copy and paste the code...

Forget them; now your project works


For your next project... I think it will be useful to create a code module (or a class) to manage each database access, with routines for initialization (although DBUtils is fine for this too) and all the read/write routines.