Android Question SQL 2 SQL, Double some rows

Discussion in 'Android Questions' started by mw71, Jul 1, 2019.

  1. mw71

    mw71 Active Member Licensed User

    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)

    Code:
    Sub SQL_to_SQL(vonSQL As SQL, nachSQL As SQL, Query As String, quelle As StringAs 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(20As String
            
    Dim values(20As 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
     
  2. RB Smissaert

    RB Smissaert Well-Known Member Licensed User

    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
     
    OliverA likes this.
  3. mw71

    mw71 Active Member Licensed User

    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 :

    Code:
    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.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice