Android Question SQL query

harinder said:

a basic question here..how can I concatenate SQL query, as it goes right into eternity

Click to expand...

Dim Query2 As String=$"
    SELECT ACType,sum(DualDay), sum(DualNite),sum(P1Day), sum(P1Nite),
    sum( P2Day),sum( P2Nite) from (select * from  ${main.DBTableprior}
    UNION ALL SELECT ACType,0,0,0,0,0,0,0,0,0,0,0,0,P1Dayhr,P1Daymin,P1Nitehr,
    P1Nitemin,0,0,0,0,InstrFlghr,InstrFlgmin,0,0 from ${main.DBTablepee1})
    GROUP BY ACType"$

    Main.SQL1.ExecQuery(Query2)

Mahares said:

The easiest way is is to use smart string literals to construct your statement. I have taken your SQL statement in your post #18 and converted it to smart string literal:

B4X:

Dim Query2 As String=$"
    SELECT ACType,sum(DualDay), sum(DualNite),sum(P1Day), sum(P1Nite),
    sum( P2Day),sum( P2Nite) from (select * from  ${main.DBTableprior}
    UNION ALL SELECT ACType,0,0,0,0,0,0,0,0,0,0,0,0,P1Dayhr,P1Daymin,P1Nitehr,
    P1Nitemin,0,0,0,0,InstrFlghr,InstrFlgmin,0,0 from ${main.DBTablepee1})
    GROUP BY ACType"$

    Main.SQL1.ExecQuery(Query2)

Click to expand...

Dim Query1 As String= "UPDATE prior Set ACType=?,Dualday=?,Dualnite=?,P1day=?,P1nite=?,P2day=?,P2nite=?,Instrflg=?,TypeTotal=?,DualDayhr=?,Dualdaymin=?,Dualnitehr=?,Dualnitemin=?,P1dayhr=?,P1daymin=?,P1nitehr=?,P1nitemin=?,P2dayhr=?,P2daymin=?,P2nitehr=?,P2nitemin=?,Instrflghr=?,Instrflgmin=? WHERE ACType = "&bb

harinder said:

How can I concatenate this code:

Click to expand...

Dim bb As String = "harinder"
   Dim Query1 As String= "UPDATE prior Set ACType=?,Dualday=?,Dualnite=?,P1day=?,P1nite=?," _
    & "P2day=?,P2nite=?,Instrflg=?,TypeTotal=?,DualDayhr=?,Dualdaymin=?,Dualnitehr=?," _
    & "Dualnitemin=?,P1dayhr=?,P1daymin=?,P1nitehr=?,P1nitemin=?,P2dayhr=?,P2daymin=?," _
    & "P2nitehr=?,P2nitemin=?,Instrflghr=?,Instrflgmin=? WHERE ACType = " & bb

Dim Query1 As String= $"UPDATE prior Set ACType=?,Dualday=?,Dualnite=?,P1day=?,P1nite=?,
    P2day=?,P2nite=?,Instrflg=?,TypeTotal=?,DualDayhr=?,Dualdaymin=?,Dualnitehr=?,
    Dualnitemin=?,P1dayhr=?,P1daymin=?,P1nitehr=?,P1nitemin=?,P2dayhr=?,P2daymin=?,
    P2nitehr=?,P2nitemin=?,Instrflghr=?,Instrflgmin=? WHERE ACType = ${bb} "$

harinder said:

Additionally, is it possible to replace entire row in a db from a list using sq

Click to expand...

 Dim MyList As List
    MyList.Initialize
    MyList=File.ReadList(File.DirInternal,"mydta.txt")  'folder and file name where the list is saved
    For Each s As String In MyList   'iterate through the list
        Dim strArray() As String =Regex.Split(",",s)  'assuming all list items are separated by commas
        SQL1.ExecNonQuery2(Query1,strArray)    'query1 is one of the above sql statements
    Next

RichardN said:

Harinder,

Just an observation... I wrote my first logbook program nearly 25 years ago and have written several others since. If you try to store the various times P1/P2/P1(u/s) day/night separately in Sqlite as hours and minutes then the SQL becomes unbelievably complicated as you are finding out! Likewise avoid storing values simply as decimal hours.... you will run into rounding errors when you try and convert back to hh:mm.

Consider storing each category as integer minutes, it becomes so much simpler. OK you have to error trap user input and convert minutes to hh:mm when you present it but single values are infinitely simpler to handle.

Click to expand...