Android Question SQL query

Discussion in 'Android Questions' started by harinder, Aug 12, 2018.

Thread Status:
Not open for further replies.
  1. harinder

    harinder Member Licensed User

    in the following code, I intend to filter data in my table as per P1 and ACType, for which it goes through each item of a spinner-spnAC. But, I am getting an out of bound exception. Where am I going wrong? thnx..

    Code:
    Dim gal() As String
    For j= 0 To spnAC.Size -1
         gal(j) = spnAC.GetItem(j)
         
    Dim MyQuery As String
         MyQuery=
    "SELECT SUM(dayhr) ||':'|| SUM(daymin) ||':'|| SUM(nitehr) ||':'||         SUM(nitemin) ||':'|| SUM(instrhr) ||':'|| SUM(instrmin)   FROM " & _
         Main.DBTableName &
    " where capacity = 'P1' and ACType =' "& gal(j) &"' "
    NEXT
     
    Last edited: Aug 12, 2018
  2. Geezer

    Geezer Active Member Licensed User

    Post your error log
     
  3. harinder

    harinder Member Licensed User

    Error occurred on line: 906 (Table)
    java.lang.ArrayIndexOutOfBoundsException: length=0; index=0
    at java.lang.reflect.Array.set(Array.java:454)
    at anywheresoftware.b4a.shell.ArraysUtils.setElement(ArraysUtils.java:84)
    at anywheresoftware.b4a.shell.Shell.setArrayElement(Shell.java:600)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:395)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:260)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:175)
    at anywheresoftware.b4a.debug.Debug.delegate(Debug.java:259)
    at harry.logbook.edit._addnew(edit.java:879)
    at harry.logbook.edit._btnaddnew_click(edit.java:1259)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:735)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:360)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:260)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:175)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:171)
    at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:80)
    at android.view.View.performClick(View.java:6877)
    at android.widget.TextView.performClick(TextView.java:12651)
    at android.view.View$PerformClick.run(View.java:26069)
    at android.os.Handler.handleCallback(Handler.java:789)
    at android.os.Handler.dispatchMessage(Handler.java:98)
    at android.os.Looper.loop(Looper.java:164)
    at android.app.ActivityThread.main(ActivityThread.java:6938)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.Zygote$MethodAndArgsCaller.run(Zygote.java:327)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1374)
     
  4. Geezer

    Geezer Active Member Licensed User

    Code:
    ACType =' "& gal(j) &"' "
    Is that space intentional inside the ACType ?
     
  5. harinder

    harinder Member Licensed User

    i removed the space. Same result.
     
  6. Erel

    Erel Administrator Staff Member Licensed User

    1. The first step is to learn how to use parametersized queries (SQL tutorial): https://www.b4x.com/etp.html

    2.
    Code:
    Dim gal() As String
    This line creates a zero size array. You cannot get or set an element in a zero sized array.
     
  7. RichardN

    RichardN Active Member Licensed User

    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.
     
    Last edited: Aug 12, 2018
  8. harinder

    harinder Member Licensed User

    Thanks Richard..Valuable tips:)
     
  9. harinder

    harinder Member Licensed User

    I modified the sql query to give unique ACType using GROUP BY

    Code:
    Dim MyQuery As String
        MyQuery=
    "SELECT ACType, SUM(dayhr),SUM(daymin), SUM(nitehr), SUM(nitemin), SUM(instrhr), SUM(instrmin) FROM "& Main.DBTableName _
         &
    " WHERE Capacity='Dual' GROUP BY ACType"
        table1.LoadSQLiteDB(Main.SQL1,MyQuery, 
    True)
     
  10. harinder

    harinder Member Licensed User

    The last Map data is replacing the earlier data. How can I use map to store all data read off the filtered table and store it in a List?
    Code:
    Sub dual
        
    Dim MyQuery As String
        MyQuery=
    "SELECT ACType, SUM(dayhr),SUM(daymin), SUM(nitehr), SUM(nitemin), SUM(instrhr), SUM(instrmin) FROM "& Main.DBTableName _
        &
    " WHERE Capacity='Dual' GROUP BY ACType"
        table1.LoadSQLiteDB(Main.SQL1,MyQuery, 
    True)
       
        
    For i=0 To table1.Size -1
            dualmap.Put(
    "ACType",table1.GetValue(0,i))
            dualmap.Put(
    "dualDayhr",table1.GetValue(1,i))
            dualmap.Put(
    "dualDaymin",table1.GetValue(2,i))
            dualmap.Put(
    "dualNitehr",table1.GetValue(3,i))
            dualmap.Put(
    "dualNitemin",table1.GetValue(4,i))
            dualmap.Put(
    "Instrflghr",table1.GetValue(5,i))
            dualmap.Put(
    "Instrflgmin",table1.GetValue(6,i))
        
    Next
        
    Log(dualmap)
       
        duallist.Add(dualmap)
        DBUtils.InsertMaps(Main.SQL1, Main.DBTabledual, duallist)
       
    End Sub
    /CODE]
     
  11. DonManfred

    DonManfred Expert Licensed User

    Initialize a new map before the loop
    Code:
    Dim dualmap as map
    dualmap.initialize
    For .....[
      ...
    Next
    duallist.Add(dualmap)
    /CODE]
     
  12. harinder

    harinder Member Licensed User

    i think same column data cannot be stored via Map method, as the Key (column name) is the same even if value is different. So, only last Key.Value is stored.
    Is there any other way of copying Table data to a list that can be used to fill another Table,
    ALTERNATIVELY, how can a Table data be used to create a database table directly? thnx
     
  13. Mahares

    Mahares Well Known Member Licensed User

    You can create a new table with the data generated from the grouping query you have. See syntax below:
    Code:
    Dim DBTableNew As String ="MyNewTable"
        
    Dim strQuery As String ="CREATE TABLE IF NOT EXISTS " & DBTableNew & " AS SELECT ACType, SUM(dayhr),SUM(daymin), " _
        & 
    "SUM(nitehr), SUM(nitemin), SUM(instrhr), SUM(instrmin) FROM " & main.DBTableName _
        & 
    " WHERE Capacity='Dual' GROUP BY ACType"
        SQL1.ExecNonQuery(strQuery)
    You can also drop the new table and recreate it by removing the IF NOT EXISTS in the syntax.
     
    mangojack likes this.
  14. harinder

    harinder Member Licensed User

    Thanks Mahares..this takes care of my almost-half-a-day woes..will get back to you for table comparison queries later
     
  15. harinder

    harinder Member Licensed User

    Table1 data:

    ACType Dualhr P1hr Total
    A320 2 2 4
    B787 1 2 3
    B777 3 1 4

    Table2 data:

    ACType P1hr Total

    A320 5 5


    on comparing the 2 tables,what sql query will give me result:
    ACType Dualhr P1hr Total
    A320 2 7 9
    B787 1 2 3
    B777 3 1 4

    which should also factor case when Table1 is empty,so to give result:
    ACType Dualhr P1hr Total
    A320 0 5 5




     
  16. RB Smissaert

    RB Smissaert Well-Known Member Licensed User

    select T1.ACType as ACType, T1.Dualhr As Dualhr, T1.P1hr + T2.P1hr as P1hr, T1.Dualhr + T1.P1hr + T2.P1hr as Total
    from Table1 T1 inner join Table2 T2 on(T1.ACType = T2.ACType)
    union all
    select * from table1 where ACType not in(select ACType from Table2)

    Leave the empty Table1 as a nice exercise:)

    RBS
     
  17. harinder

    harinder Member Licensed User

    How about
    Code:
    Select ACType, sum(Dualhr) As Dualhr, sum(P1hr) As P1hr,sum(Total) As Total
    from
    (
    Select ACType, Dualhr, P1hr, Total from table1
        union all
        
    Select ACType, 0, P1hr, Total from table2)
        group by ACType
     
  18. harinder

    harinder Member Licensed User

    Code:
    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.ExecNonQuery(Query2)



    What might I be doing wrong here? Error code appended below:

     
  19. harinder

    harinder Member Licensed User

    oops..sorry for the bother...i need to use ExecQuery not ExecNonQuery.......
     
  20. LucaMs

    LucaMs Expert Licensed User

    This is one of your error:

    Wrong number of values =12 col=14

    The numbers of question marks and of the fields you are trying to insert are different.
     
Thread Status:
Not open for further replies.
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