B4J Question SQLITE ???

ivanomonti

Well-Known Member
Licensed User
I tried all the examples but the only one that gives me results and this, I would like the operation to be done faster and maybe in the background,

B4X:
Sub query
    
    Dim query1 As String = ""
    
    If sql1.IsInitialized = False Then
        If File.Exists(File.DirApp,"..\data.db") = True Then
            sql1.InitializeSQLite(File.DirApp,"..\data.db", False)
        Else
            fx.Msgbox(MainForm,"Data not found","Alert")
        End If
    End If
    
    For i = 5267 To ListView1.Items.Size-1
        
        Label1.Text = i
        
        Dim lb As Label = ListView1.Items.Get(i)
        Dim li As List =lb.Tag
        
        Try
            query1="INSERT INTO data1 (name,phone,email,client,production,canal,log) Values (" & _
            "'" & Regex.Replace("'",li.get(1),"''") & _
            "','" & Regex.Replace("'",li.get(7),"") & _
            "','" & Regex.Replace("'",li.get(9),"") & _
            "','" & "Business - " & Regex.Replace("'",li.get(0),"''") & _
            "','" & "Altro" & _
            "','" & "4Dem" & _
            "','" & DateTime.Date(DateTime.Now) & "');"
            sql1.ExecNonQuery(query1)
            Sleep(100)
            ListView1.SelectedIndex = i
        Catch
            Log("error ecption " & i & " " & li.Get(1) )
        End Try
    
    Next
    
End Sub
 

udg

Expert
Licensed User
Since you're going to insert multiple records at once, read about transactions
 

amykonio

Member
Licensed User
I tried all the examples but the only one that gives me results and this, I would like the operation to be done faster and maybe in the background,

B4X:
            query1="INSERT INTO data1 (name,phone,email,client,production,canal,log) Values (" & _
            "'" & Regex.Replace("'",li.get(1),"''") & _
            "','" & Regex.Replace("'",li.get(7),"") & _
            "','" & Regex.Replace("'",li.get(9),"") & _
            "','" & "Business - " & Regex.Replace("'",li.get(0),"''") & _
            "','" & "Altro" & _
            "','" & "4Dem" & _
            "','" & DateTime.Date(DateTime.Now) & "');"
            sql1.ExecNonQuery(query1)
            Sleep(100)
            ListView1.SelectedIndex = i
Hi.
Why are you using sleep(100)? Why not sleep(0)?
Anyway, look at @DonManfred and other's answer...

Andreas.
 

Erel

Administrator
Staff member
Licensed User
There are many mistakes in this code:
1. Not using parameterized queries.
2. Not creating a single transaction.
3. Using Sleep to let the UI being updated instead of using the much simpler async methods.
4. Not using smart string literals.
(5. Using ListView instead of xCustomListView.)
6. Not returning after the "no data" msgbox.

Worth watching the video tutorial: https://www.b4x.com/android/forum/threads/sql-tutorial.6736/#content
 
Last edited:

ivanomonti

Well-Known Member
Licensed User
There are many mistakes in this code:
1. Not using parameterized queries.
2. Not creating a single transaction.
3. Using Sleep to let the UI being updated instead of using the much simpler async methods.
4. Not using smart string literals.
(5. Using ListView instead of xCustomListView.)
6. Not returning after the "no data" msgbox.

Worth watching the video tutorial: https://www.b4x.com/android/forum/threads/sql-tutorial.6736/#content
I started from there but it didn't give me results, tomorrow I try again to rewrite the code, maybe the problem is local sqlite.
 

ivanomonti

Well-Known Member
Licensed User
That's expected.
It will take you exactly two minutes to solve if you read the tutorial: https://www.b4x.com/android/forum/threads/b4x-sql-with-wait-for.79532/#content
ERROR as before it gives me an error saying that the name does not exist, but in reality it exists and gives it to me at the end of the process, I'm sure I'm wrong again but I don't understand where.

TABLE

B4X:
CREATE TABLE [data1](
  [name] TEXT(255),
  [phone] TEXT(255),
  [email] TEXT(255),
  [client] TEXT(255),
  [production] TEXT(255),
  [canal] TEXT(255),
  [log] TEXT(255));

CREATE TABLE [data2](
  [address] TEXT(255),
  [city] TEXT(255),
  [zipcode] TEXT(255),
  [lat] TEXT(255),
  [long] TEXT(255),
  [relationship] TEXT(255),
  [log] TEXT(255));
ERROR

B4X:
Waiting for debugger to connect...
Program started.
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such column: name)
    at org.sqlite.DB.newSQLException(DB.java:383)
    at org.sqlite.DB.newSQLException(DB.java:387)
    at org.sqlite.DB.throwex(DB.java:374)
    at org.sqlite.NativeDB.prepare(Native Method)
    at org.sqlite.DB.prepare(DB.java:123)
    at org.sqlite.PrepStmt.<init>(PrepStmt.java:42)
    at org.sqlite.Conn.prepareStatement(Conn.java:404)
    at org.sqlite.Conn.prepareStatement(Conn.java:399)
    at org.sqlite.Conn.prepareStatement(Conn.java:383)
    at anywheresoftware.b4j.objects.SQL$2.run(SQL.java:259)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
NonQuery: false
CODICE

B4X:
Sub queryx
    
    Dim query1 As String = ""
    
    If sql1.IsInitialized = False Then
        If File.Exists(File.DirApp,"..\data.db") = True Then
            sql1.InitializeSQLite(File.DirApp,"..\data.db", False)
            sql2.InitializeSQLite(File.DirApp,"..\data.db", False)
        Else
            fx.Msgbox(MainForm,"Data not found","Alert")
        End If
    End If
    
    For i = 0 To ListView1.Items.Size-1
        
        Label1.Text = i
        
        Dim lb As Label = ListView1.Items.Get(i)
        Dim li As List =lb.Tag
        
        'verifico presenza telefono, se non è presente lo cerco nel index 8 se anche qui non presente
        'allora gerero un indice provvisorio
        
        If li.Get(7) = "" Or li.Get(7) = " " Then
            li.Set(7, li.Get(8))
            If li.Get(7) = "" Or li.Get(7) = " " Then
                DateTime.DateFormat = "ddMMyyyyhhmmss"
                li.Set(7,DateTime.Date(DateTime.Now) & "-" & Rnd(1,1000))
            End If
        End If
        
        Try
            
            query1="INSERT INTO data1 Values (name,phone,email,client,production,canal,log)"
            Dim lix As List
            lix.Initialize
            lix.Add(Regex.Replace("'",li.get(1),"''"))
            lix.Add(Regex.Replace(" ",li.get(7),""))
            lix.Add(Regex.Replace(" ",li.get(9),""))
            lix.Add("Business - " & Regex.Replace("'",li.get(0),"''"))
            lix.Add("Altro")
            lix.Add("4Dem")
            lix.Add(DateTime.Date(DateTime.Now))
            sql1.AddNonQueryToBatch(query1,lix)
            
            query1="INSERT INTO data2 Values (address,city,zipcode,lat,long,relationship,log)"
            Dim lix As List
            lix.Initialize
            lix.Add(Regex.Replace("'",li.get(2),"''"))
            lix.Add(Regex.Replace("'",li.get(4) & " " & li.Get(5),""))
            lix.Add(Regex.Replace(" ",li.get(3),""))
            lix.Add("0")
            lix.Add("0")
            lix.Add(Regex.Replace(" ",li.get(7),""))
            lix.Add(DateTime.Date(DateTime.Now))
            sql1.AddNonQueryToBatch(query1,lix)
            
        Catch
            Log("error ecption " & i & " " & li.Get(1) )
        End Try
    
    Next
    
    Dim SenderFilter As Object = sql1.ExecNonQueryBatch("SQL")
    Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
    Log("NonQuery: " & Success)
    
End Sub
 

DonManfred

Expert
Licensed User
data1 has a field name
data2 does not have such a field.

You are initializing TWO different SQL-Objects with the same )File)Name
sql1.InitializeSQLite(File.DirApp,"..\data.db", False)
sql2.InitializeSQLite(
File.DirApp,"..\data.db", False)
sure this is correct???
 

amykonio

Member
Licensed User
The error I can see is in the insert statement.
Look at the following code which is the sample provided to AddNonQueryToBatch (you can see it while hovering mouse on it).

B4X:
For i = 1 To 1000
   sql.AddNonQueryToBatch("INSERT INTO table1 VALUES (?)", Array(Rnd(0, 100000)))
Next
Dim SenderFilter As Object = sql.ExecNonQueryBatch("SQL")
Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
Log("NonQuery: " & Success)
You are writing:
B4X:
query1="INSERT INTO data1 Values (name,phone,email,client,production,canal,log)"
This is wrong. After values you need a "?" for each field and not the field name.
B4X:
query1="INSERT INTO data1 Values (?,?,?,?,?,?,?)"
The ? symbols will be replaced by the command itself with items in your list...

Haven't tested your code, but based on the message you are getting your insert statement is wrong.
Also I do prefer to use more readable statements.
B4X:
query1="INSERT INTO data1 (name,phone,email,client,production,canal,log) Values (?,?,?,?,?,?,?)"
Finally you are making to much effort with Regex.Replace to add quotes for some variables etc. You don't need that.

That way you will read easier your code in the future...

Andreas.
 

ivanomonti

Well-Known Member
Licensed User
The error I can see is in the insert statement.
Look at the following code which is the sample provided to AddNonQueryToBatch (you can see it while hovering mouse on it).

B4X:
For i = 1 To 1000
   sql.AddNonQueryToBatch("INSERT INTO table1 VALUES (?)", Array(Rnd(0, 100000)))
Next
Dim SenderFilter As Object = sql.ExecNonQueryBatch("SQL")
Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
Log("NonQuery: " & Success)
You are writing:
B4X:
query1="INSERT INTO data1 Values (name,phone,email,client,production,canal,log)"
This is wrong. After values you need a "?" for each field and not the field name.
B4X:
query1="INSERT INTO data1 Values (?,?,?,?,?,?,?)"
The ? symbols will be replaced by the command itself with items in your list...

Haven't tested your code, but based on the message you are getting your insert statement is wrong.
Also I do prefer to use more readable statements.
B4X:
query1="INSERT INTO data1 (name,phone,email,client,production,canal,log) Values (?,?,?,?,?,?,?)"
Finally you are making to much effort with Regex.Replace to add quotes for some variables etc. You don't need that.

That way you will read easier your code in the future...

Andreas.
Perfect, my error, yes it worked without problems, but in the example I found it indicated the fields I have smoked in my brain :)
 

amykonio

Member
Licensed User
Another point: be sure to not use keywords to your tables (table name and column names).
I'm not really using sqlite so I cannot say if the word log is a keyword or not...

Andreas.
 

DonManfred

Expert
Licensed User
Also I do prefer to use more readable statements
Asume your table has 100 Fields. Which of the questionmark belongs to the field 36?
I prefer queries like

B4X:
insert into table SET name=?,street=?;
together with the correct amount of values in the parameters.

I personally find this more intuitive. Even if one needs to change the parameters later...
 

amykonio

Member
Licensed User
Perfect, my error, yes it worked without problems, but in the example I found it indicated the fields I have smoked in my brain :)
B4X has great manuals... And great support inside the IDE to help developers.
I always have the documentation in a usb stick (many times I work without internet access).
 

amykonio

Member
Licensed User
Asume your table has 100 Fields. Which of the questionmark belongs to the field 36?
I prefer queries like

B4X:
insert into table SET name=?,street=?;
together with the correct amount of values in the parameters.
The 35 item in my list.
I see your point. I personally use the way I indicated.
I use insert into with values to generate statements directly from the database or excel.
Also I use many tools who save data in sql commands using insert into with values.
But the most important point is if the syntax is supported... And I use databases where insert into with set is not supported.

Andreas.
 

DonManfred

Expert
Licensed User
The 35 item in my list.
Sure it is :)

I just want to mention that "I" find it more comfortable to search for the Fieldname in a long list than to count the questionmarks.
Everyone should use the technique which he is more comfortable with ;-)

I must say that i do use only two kind of databases.

MySQL (preferably) and SQLite (on Android). Both support SET field=value
 
Top