B4J Question SQLITE ???

ivanomonti

Expert
Licensed User
Longtime 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,

Code: Copy
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
 
Since you're going to insert multiple records at once, read about transactions
 
Upvote 0
Code: Copy
For i = 1 To 10000
        sql1.AddNonQueryToBatch("INSERT INTO table1 VALUES (?)", Array As Object(Rnd(0, 100000)))
  Next
  sql1.ExecNonQueryBatch("SQL")
 
Upvote 0
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,

Code: Copy
            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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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

Code: Copy
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

Code: Copy
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

Code: Copy
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
 
Upvote 0
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???
 
Upvote 0
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).

Code: Copy
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:
Code: Copy
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.
Code: Copy
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.
Code: Copy
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.
 
Upvote 0
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).

Code: Copy
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:
Code: Copy
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.
Code: Copy
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.
Code: Copy
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 :-)
 
Upvote 0
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.
 
Upvote 0
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

Code: Copy
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...
 
Upvote 0
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).
 
Upvote 0
Asume your table has 100 Fields. Which of the questionmark belongs to the field 36?
I prefer queries like

Code: Copy
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.
 
Upvote 0
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
 
Upvote 0
Back
Top