B4J Question SQLITE ???

ivanomonti · Sep 1, 2019

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 · Sep 1, 2019

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

DonManfred · Sep 1, 2019

B4X:

For i = 1 To 10000
        sql1.AddNonQueryToBatch("INSERT INTO table1 VALUES (?)", Array As Object(Rnd(0, 100000)))
  Next
  sql1.ExecNonQueryBatch("SQL")

KMatle · Sep 2, 2019

See my example, too: https://www.b4x.com/android/forum/threads/b4x-sqlite-with-6-million-rows.91563/

It inserts 6 Million rows quite fast...

Erel · Sep 2, 2019

[B4X] SQL with Wait For

amykonio · Sep 2, 2019

ivanomonti said:

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 · Sep 2, 2019

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

ivanomonti · Sep 2, 2019

DonManfred said:

B4X:

For i = 1 To 10000
        sql1.AddNonQueryToBatch("INSERT INTO table1 VALUES (?)", Array As Object(Rnd(0, 100000)))
  Next
  sql1.ExecNonQueryBatch("SQL")

used but gave no results :-(

ivanomonti · Sep 2, 2019

Erel said:
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 · Sep 2, 2019

KMatle said:
See my example, too: https://www.b4x.com/android/forum/threads/b4x-sqlite-with-6-million-rows.91563/

It inserts 6 Million rows quite fast...

I'll try to look at your example.

Erel · Sep 3, 2019

ivanomonti said:
used but gave no results

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

ivanomonti · Sep 3, 2019

Erel said:
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 · Sep 3, 2019

ivanomonti said:
TABLE

data1 has a field name
data2 does not have such a field.

You are initializing TWO different SQL-Objects with the same )File)Name

ivanomonti said:
sql1.InitializeSQLite(File.DirApp,"..\data.db", False)
sql2.InitializeSQLite(File.DirApp,"..\data.db", False)

sure this is correct???

amykonio · Sep 3, 2019

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 · Sep 3, 2019

amykonio said:
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 · Sep 3, 2019

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 · Sep 3, 2019

amykonio said:
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 · Sep 3, 2019

ivanomonti said:
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 · Sep 3, 2019

DonManfred said:
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 · Sep 3, 2019

amykonio said:
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

B4J Question SQLITE ???

Expert

Expert

Expert

Expert

B4X founder

Active Member

B4X founder

Expert

Expert

Expert

B4X founder

Expert

Expert

Active Member

Expert

Active Member

Expert

Active Member

Active Member

Expert

Similar Threads