Android Question SQL 2 SQL, Double some rows

[mw71]

hi,

i use the code below to copy Data from on to an other SQLite DB.
(e.g., merge difftent DB's into one, export with Filter in the Query....)

My problem is, that the routine some rows duplicate in the target Database. Whats wrong?
(booth db's are createt, the Query is simple, SELECT * FROM log WHERE Call<>'', no error catch)

Sub SQL_to_SQL(vonSQL As SQL, nachSQL As SQL, Query As String, quelle As String) As Map
Starter.CL.Lg("SQL_to_SQL")   

Dim cur As Cursor
Dim rMap As Map
Dim Error As String = 0

rMap.Initialize

If Query ="" Then Query = "SELECT * FROM log"

Try
    Starter.cl.lg("Query: " & Query)
    cur = vonSQL.ExecQuery(Query)
Catch
    Starter.CL.lg(LastException)
    Error=1
End Try       

If Error = 0 Then
    Try
        Dim colNames(20) As String
        Dim values(20) As String
        Dim ID As String
        
        For row = 0 To cur.RowCount - 1
            cur.Position = row
            Dim values(cur.ColumnCount) As String
            For col = 0 To cur.ColumnCount - 1
                colNames(col) = cur.GetColumnName(col)
                values(col) = cur.GetString2(col)
            Next
            Dim sb_q_1 As StringBuilder
            Dim sb_q_2 As StringBuilder
            sb_q_1.Initialize
            sb_q_2.Initialize
            For i=0 To cur.ColumnCount-1
'                sb_q_1.Append(colNames(i))
                If values(i)=Null Or values(i)="null" Then values(i) = ""

                If colNames(i).ToUpperCase="ID" Then
                    ID=values(i)
                    'sb_q_2.Append("")
                Else
                    sb_q_1.Append(colNames(i))
                    sb_q_2.Append(values(i))
                    If i<cur.ColumnCount-1 Then
                        sb_q_1.Append(",")
                        sb_q_2.Append("','")
                    End If
                End If

            Next
'save the source File/ID?
            If quelle<>""  Then
                    sb_q_1.Append(", Quelle, Quell_ID")
                    sb_q_2.Append($"','${quelle}','${ID}"$)
                    ID=""
            End If
            Dim QueryTo As String = "INSERT INTO log (" & sb_q_1 & ") VALUES ('" & sb_q_2 & "')"
            nachSQL.ExecNonQuery(QueryTo)
        Next
        cur.Close
    Catch
        Starter.CL.Lg(LastException)
        Error=2
    End Try
End If

    rMap.Put("Error",Error)
    rMap.Put("Count", row)
    'Log("SQL 2 SQL fin")
    Return rMap
End Sub

 

[RB Smissaert]

hi,

i use the code below to copy Data from on to an other SQLite DB.
(e.g., merge difftent DB's into one, export with Filter in the Query....)

My problem is, that the routine some rows duplicate in the target Database. Whats wrong?
(booth db's are createt, the Query is simple, SELECT * FROM log WHERE Call<>'', no error catch)

Sub SQL_to_SQL(vonSQL As SQL, nachSQL As SQL, Query As String, quelle As String) As Map
Starter.CL.Lg("SQL_to_SQL")  

Dim cur As Cursor
Dim rMap As Map
Dim Error As String = 0

rMap.Initialize

If Query ="" Then Query = "SELECT * FROM log"

Try
    Starter.cl.lg("Query: " & Query)
    cur = vonSQL.ExecQuery(Query)
Catch
    Starter.CL.lg(LastException)
    Error=1
End Try      

If Error = 0 Then
    Try
        Dim colNames(20) As String
        Dim values(20) As String
        Dim ID As String
       
        For row = 0 To cur.RowCount - 1
            cur.Position = row
            Dim values(cur.ColumnCount) As String
            For col = 0 To cur.ColumnCount - 1
                colNames(col) = cur.GetColumnName(col)
                values(col) = cur.GetString2(col)
            Next
            Dim sb_q_1 As StringBuilder
            Dim sb_q_2 As StringBuilder
            sb_q_1.Initialize
            sb_q_2.Initialize
            For i=0 To cur.ColumnCount-1
'                sb_q_1.Append(colNames(i))
                If values(i)=Null Or values(i)="null" Then values(i) = ""

                If colNames(i).ToUpperCase="ID" Then
                    ID=values(i)
                    'sb_q_2.Append("")
                Else
                    sb_q_1.Append(colNames(i))
                    sb_q_2.Append(values(i))
                    If i<cur.ColumnCount-1 Then
                        sb_q_1.Append(",")
                        sb_q_2.Append("','")
                    End If
                End If

            Next
'save the source File/ID?
            If quelle<>""  Then
                    sb_q_1.Append(", Quelle, Quell_ID")
                    sb_q_2.Append($"','${quelle}','${ID}"$)
                    ID=""
            End If
            Dim QueryTo As String = "INSERT INTO log (" & sb_q_1 & ") VALUES ('" & sb_q_2 & "')"
            nachSQL.ExecNonQuery(QueryTo)
        Next
        cur.Close
    Catch
        Starter.CL.Lg(LastException)
        Error=2
    End Try
End If

    rMap.Put("Error",Error)
    rMap.Put("Count", row)
    'Log("SQL 2 SQL fin")
    Return rMap
End Sub

It will be a lot simpler (and also faster) to attach the other database and then run a SQL like this:

insert into NameOfOtherDB.log select * from main.log

RBS

 

[mw71]

first, i found the "error", it was the User....

Your solution looks fine, unfortunately i have actuell 2 problems.

I need the ID (from each row) from the "main" log in a separate column in the "NameOfOtherDB" Database.

NameOfOtherDB Structure:
ID (Autoincrement), A TEXT, B TEXT, C TEXT, Quell_ID TEXT, Q TEXT

Main Structure:
ID (Autoincrement), A TEXT, B TEXT, C TEXT
the ID must Save in Quell_ID

The second is,
a Field contain in the most Cases a (define) String, but the User can also enter a Number (Frequenz). I must "Translate" to the define String and save it an Column Q.

Actuell i do this with :

l.Add(Array As String("1800","2000","160m"))
.....
For z=0 To l.Size-1
    retRange = l.Get(z)
    If (q >= retRange(0)) And (q <= retRange(1)) Then 
        found=True
        Exit
    End If
Next

If found Then
    Return retRange(2)
Else
    Return ""
End If

if you (or other) have an Idear, all is welcome.

But, for Export it's a nice Idear.