B4J Question Application runs on OK on Debug but no SQLite Queries on Standalone

[Setlodi]

Hi Friends

When I run my application in Debug, it runs perfectly. I populate numerous CLVs from SQLite Queries. Example:

Public Sub Get_Shop_Info
    Log("::::: Getting Shop Info")
    clvDistrShops.Clear
    Dim rs As ResultSet =Main.SQL.ExecQuery("SELECT * FROM DistrShop_Table")
    Do While rs.NextRow
        
        DistrShop_OwnerName  = rs.GetString("Shop_Owner_Name")
        DistrShop_OwnerSurname  = rs.GetString("Shop_Owner_Surname")
        DistrShop_OwnerIDNo  = rs.GetString("Shop_Owner_ID")
        DistrShop_OwnerTelephone  = rs.GetString("Shop_Owner_Telephone")
        DistrShop_Name  = rs.GetString("Shop_Name")
        DistrShop_RegNo  = rs.GetString("Shop_Reg_Number")
        DistrShop_Street  = rs.GetString("Shop_Address_Street")
        DistrShop_Location  = rs.GetString("Shop_Address_Township")
        DistrShop_Telephone  = rs.GetString("Shop_Phone_No")
        DistrShop_Area  = rs.GetString("Shop_Area")
        DistrShop_Section  = rs.GetString("Shop_Section")
        DistrShop_Status  = rs.GetString("Shop_Status")
        
        Log("DistrShop_OwnerIDNo = " & DistrShop_OwnerIDNo)
        Log("DistrShop_Name = " & DistrShop_Name)
        Log("DistrShop_RegNo = " & DistrShop_RegNo)
        Log("DistrShop_Street = " & DistrShop_Street)
        Log("DistrShop_Location = " & DistrShop_Location)
        Log("DistrShop_Telephone = " & DistrShop_Telephone)
        Log("DistrShop_Area = " & DistrShop_Area)
        Log("DistrShop_Section = " & DistrShop_Section)
        Log("DistrShop_Status = " & DistrShop_Status)
        Log("_____________________")
'       
        Add_Shop_Info_to_CLV
    Loop
    rs.Close
End Sub

Public Sub Add_Shop_Info_to_CLV
    
    Dim p As B4XView = xui.CreatePanel("")
    p.SetLayoutAnimated(0, 0, 0, Root.Width, 60dip)
    p.LoadLayout("Layout_View_DistrShops")
    
    lblDistrShop_Status .text = DistrShop_Status
    lblDistrShop_Area .text = DistrShop_Area
    lblDistrShop_Location .text = DistrShop_Location
    lblDistrShop_Name .text = DistrShop_Name
'    lblDistrShop_OwnerIDNoVIEW .text = DistrShop_OwnerIDNo
    lblDistrShop_OwnerNameVIEW .text = DistrShop_OwnerName
    lblDistrShop_OwnerSurnameVIEW .text = DistrShop_OwnerSurname
    lblDistrShop_OwnerTelephoneVIEW .text = DistrShop_OwnerTelephone
    lblDistrShop_RegNo .text = DistrShop_RegNo
    lblDistrShop_Section .text = DistrShop_Section
'    lblDistrShop_Street .text = DistrShop_Street
    lblDistrShop_Telephone .text = DistrShop_Telephone
                
    Dim prod_list As List
    prod_list.Initialize
    prod_list.AddAll(Array As String(DistrShop_Status, DistrShop_Area, DistrShop_Location, DistrShop_Name, DistrShop_OwnerIDNo, DistrShop_OwnerSurname, DistrShop_OwnerSurname, DistrShop_OwnerTelephone, DistrShop_RegNo, DistrShop_Section, DistrShop_Street, DistrShop_Telephone))
    clvDistrShops.Add(p,prod_list)
    
End Sub

When I run executable created by Build Standalone Package, it doesn't seem to run SQL queries as I see blank CLVs. Here are my Project Attributes:

    #MainFormWidth: 600
    #MainFormHeight: 600
    #AdditionalJar: sqlite-jdbc-3.7.2
    #AdditionalJar: mysql-connector-java-5.1.47-bin.jar
    #PackagerProperty: IncludedModules = javafx.controls
    #PackagerProperty: IncludedModules = java.sql

I'm running Windows 11 and jdk-19.0.2.

run_debug log:


Please help...

 

[Erel]

No need to do anything special for SQLite to work in standalone app.

Small test with:

Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("MainPage")
    sql.InitializeSQLite(File.DirApp, "db.db", True)
    Log(sql.ExecQuerySingleResult("SELECT sqlite_version()")) 'maybe you are actually using a different version?
    If sql.ExecQuerySingleResult2("SELECT count(*) FROM sqlite_master WHERE Type = 'table' AND name = ?", Array("table1")) = 0 Then
        sql.ExecNonQuery("CREATE TABLE table1 (col1 TEXT , col2 INTEGER, col3 INTEGER)")
        sql.ExecNonQuery2("INSERT INTO table1 VALUES(?, ?, ?)", Array("aaa", 10, 20))
    End If
    Dim aaa As String = sql.ExecQuerySingleResult("SELECT col1 FROM table1")
    Log(aaa)
    xui.MsgboxAsync(aaa, "")
End Sub

And in main module:

#AdditionalJar: sqlite-jdbc-3.7.2

Project is attached.

Tip: File.DirApp is not a good folder for databases as it can be readonly if app installed in Program Files.

 

[Setlodi]

Hi Erel
Thank you for your response. Your Project works well. I've also tested my application and it works OK if I Insert data in my database at runtime. However, for some reason, my application doesn't work because my database is strangely empty. My standalone package doesn't seem to bring the data already in the database through when compiling. I know that my database is not empty because the application works perfectly in Debug mode.

Public Sub Check_Tables
    Log("$$$$$$$$$$$$$$$$$$$ Starting Check table")
    Dim rs As ResultSet =SQL.ExecQuery("SELECT * FROM Supplier_Table")
    Do While rs.NextRow
        Log("Supplier Name  = " & rs.GetString("Supplier_Name"))
    Loop
    rs.Close
    Log("$$$$$$$$$$$$$$$$$$$ Ending Check table")
End Sub

Log in Debug mode:

Log in Standalone mode (run_debug)

 

[Erel]

Post the code that loads the db.

 

[Setlodi]

SQL.InitializeSQLite(File.DirApp, "SBA_Project.db", True)

 

[Erel]

This will try to load the db from the bin folder. It will start with an empty db unless you copy one to that folder.

 

[Setlodi]

Hi Erel. Thank you once more for your assistance. I used the following to copy the db file to the bin folder but it copies a blank db, with the right schema. Only the data is missing...

#CustomBuildAction: After Packager, %WINDIR%\System32\robocopy.exe, ..\ temp\build\bin\ SBA_Project.db

I thought maybe if I use file. Copy it might work but I'm not sure of the proper syntax:

File.Copy(File.DirApp, "SBA_Project.db", "..\ temp\build\bin\", "SBA_Project.db")

I've searched the forum but I can't find the syntax for acdcessing a folder other than DirApp/DirAssets/DirData etc

 

[Setlodi]

So just to test, I ended up copying the db file manually in windows explorer and it works!