iOS Question Compare B4i 2 Sqlite Tables

[RauchG]

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?

    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

 

[hatzisn]

Firstly with the word align what do you mean?

 

[hatzisn]

Do you mean replicate?

 

[hatzisn]

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.

 

[RauchG]

Firstly with the word align what do you mean?

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.

 

[RauchG]

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.

I'll try that

 

[RauchG]

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.

It does not work

    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

 

[RauchG]

It does not work

    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

Log shows errors

Error calling sqlite3_step (21: out of memory) rs

 

[Erel]

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

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)

 

[RauchG]

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

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