iOS Question Compare B4i 2 Sqlite Tables

RauchG

Active Member
Licensed User
Longtime User
Hello everybody,
I have 2 tables in documents and would like to align the 1st with the 2nd. My code doesn't work. What am I doing wrong?

B4X: 
    SQL1.Initialize(xui.DefaultFolder, "einkauf_tmp.db3", True)
    Dim rs As ResultSet = SQL1.ExecQuery("SELECT * FROM einkauf ORDER BY produkt COLLATE NOCASE ASC LIMIT 1000")
    Do While rs.NextRow
        Log("einkauf")

        SQL1.Initialize(xui.DefaultFolder, "einkauf.db3", True)
        Dim rs2 As ResultSet = SQL1.ExecQuery("SELECT * FROM einkauf WHERE produkt = '" & mProduktTmp & "' ORDER BY produkt COLLATE NOCASE Asc LIMIT 1000")
        Do While rs2.NextRow
            Log("einkauf_tmp")

        Loop
        rs2.Close
           
    Loop
    rs.Close
 
Sort by date Sort by votes

hatzisn

Expert
Licensed User
Longtime User
Firstly with the word align what do you mean?
 
Upvote 0

hatzisn

Expert
Licensed User
Longtime User
Do you mean replicate?
 
Upvote 0

hatzisn

Expert
Licensed User
Longtime User
Probably it is because you do not have two SQL variables but both are initialized with SQL1.Initialize. Create a second SQL variable AND move the second initialization out of outer loop.
 
Upvote 0

RauchG

Active Member
Licensed User
Longtime User
hatzisn said:
Firstly with the word align what do you mean?
Click to expand...

I translate with the google translator

I want to compare both tables. Both tables have the same structure and content. Only Boolean fields are more up-to-date in the 2nd table. The 1st table should be updated with the content of the 2nd table.
 
Upvote 0

RauchG

Active Member
Licensed User
Longtime User
hatzisn said:
Probably it is because you do not have two SQL variables but both are initialized with SQL1.Initialize. Create a second SQL variable AND move the second initialization out of outer loop.
Click to expand...

I'll try that
 
Upvote 0

RauchG

Active Member
Licensed User
Longtime User
hatzisn said:
Probably it is because you do not have two SQL variables but both are initialized with SQL1.Initialize. Create a second SQL variable AND move the second initialization out of outer loop.
Click to expand...

It does not work

B4X: 
    SQL1.Initialize(xui.DefaultFolder, "einkauf_tmp.db3", True)
    SQL2.Initialize(xui.DefaultFolder, "einkauf.db3", True)

    Dim rs As ResultSet = SQL1.ExecQuery("SELECT * FROM einkauf ORDER BY produkt COLLATE NOCASE ASC LIMIT 1000")
    Do While rs.NextRow
        Log("einkauf")

        Dim rs2 As ResultSet = SQL2.ExecQuery("SELECT * FROM einkauf WHERE produkt = '" & mProduktTmp & "' ORDER BY produkt COLLATE NOCASE Asc LIMIT 1000")
        Do While rs2.NextRow
            Log("einkauf_tmp")

        Loop
        rs.Close
           
    Loop
    rs.Close
 
Upvote 0

RauchG

Active Member
Licensed User
Longtime User

Log shows errors

B4X: 
Error calling sqlite3_step (21: out of memory) rs
 
Upvote 0

Erel

B4X founder
Staff member
Licensed User
Longtime User
You should never build queries like this. Learn how to use parameterized queries. Very simple. Watch the SQL video tutorial: https://www.b4x.com/etp.html

B4X: 
SQL1.Initialize(xui.DefaultFolder, "einkauf_tmp.db3", True)
SQL2.Initialize(xui.DefaultFolder, "einkauf.db3", True)
Dim rs1 As ResultSet = SQL1.ExecQuery2(..)
Dim rs2 As ResultSet = SQL2.ExecQuery2(..)
  Dim NoMatch As Boolean
  Do While True
    Dim b1 As Boolean = rs1.NextRow
    Dim b2 As Boolean = rs2.NextRow
    If b1 <> b2 Then
     NoMatch = True
     Exit
    End If
  Else If b1 = False Then
    Exit
  Else
    Dim value1 As SomeType = rs1.GetSomeType("Column Name")
    Dim value2 As SomeType = rs2.GetSomeType("Column Name")
    If value1 <> value2 Then
      NoMatch = True
      Exit
   End If
Loop
rs1.Close
rs2.Close
Log(NoMatch)
 
Upvote 0

RauchG

Active Member
Licensed User
Longtime User
Erel said:
You should never build queries like this. Learn how to use parameterized queries. Very simple. Watch the SQL video tutorial: https://www.b4x.com/etp.html
Click to expand...

B4X: 
SQL1.Initialize(xui.DefaultFolder, "einkauf_tmp.db3", True)
SQL2.Initialize(xui.DefaultFolder, "einkauf.db3", True)
Dim rs1 As ResultSet = SQL1.ExecQuery2(..)
Dim rs2 As ResultSet = SQL2.ExecQuery2(..)
  Dim NoMatch As Boolean
  Do While True
    Dim b1 As Boolean = rs1.NextRow
    Dim b2 As Boolean = rs2.NextRow
    If b1 <> b2 Then
     NoMatch = True
     Exit
    End If
  Else If b1 = False Then
    Exit
  Else
    Dim value1 As SomeType = rs1.GetSomeType("Column Name")
    Dim value2 As SomeType = rs2.GetSomeType("Column Name")
    If value1 <> value2 Then
      NoMatch = True
      Exit
   End If
Loop
rs1.Close
rs2.Close
Log(NoMatch)

Thank you very much ... works
RauchG
 
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Article
Android Code Snippet [B4X] B4XTable - Delete Row
Replies
2
Views
8K
Erel
  • Question
iOS Question (solved) RowID - 1 ?
Replies
6
Views
1K
anOparator
G
  • Question
iOS Question SQLITE
Replies
0
Views
984
gerredtor
G
  • Question
iOS Question Problem with ExecuteQuery2
Replies
4
Views
1K
marcick
  • Question
iOS Question SQL elimintes the previous "0"
Replies
5
Views
1K
schimanski
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…