Android Question SQL query

Status
Not open for further replies.
harinder

harinder

Active Member
Licensed User
Longtime 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..

B4X: 
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:
Sort by date Sort by votes
harinder

harinder

Active Member
Licensed User
Longtime 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)
 
Upvote 0
R

RichardN

Well-Known Member
Licensed User
Longtime 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:
Upvote 0
harinder

harinder

Active Member
Licensed User
Longtime User
I modified the sql query to give unique ACType using GROUP BY

B4X: 
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)
 
Upvote 0
harinder

harinder

Active Member
Licensed User
Longtime 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?
B4X: 
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]
 
Upvote 0
DonManfred

DonManfred

Expert
Licensed User
Longtime User
Initialize a new map before the loop
B4X: 
Dim dualmap as map
dualmap.initialize
For .....[
  ...
Next
duallist.Add(dualmap)
/CODE]
 
Upvote 0
harinder

harinder

Active Member
Licensed User
Longtime 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
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
harinder said:
ALTERNATIVELY, how can a Table data be used to create a database table directly?
Click to expand...
You can create a new table with the data generated from the grouping query you have. See syntax below:
B4X: 
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.
 
Upvote 0
harinder

harinder

Active Member
Licensed User
Longtime User
Thanks Mahares..this takes care of my almost-half-a-day woes..will get back to you for table comparison queries later
 
Upvote 0
harinder

harinder

Active Member
Licensed User
Longtime 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




 
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
harinder said:
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
Click to expand...

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
 
Upvote 0
harinder

harinder

Active Member
Licensed User
Longtime User
How about
B4X: 
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
 
Upvote 0
harinder

harinder

Active Member
Licensed User
Longtime User
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.ExecNonQuery(Query2)




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

Logger connected to: samsung SM-G965F
--------- beginning of main
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
Unexpected event (missing RaiseSynchronousEvents): sv_scrollchanged
Check the unfiltered logs for the full stack trace.
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = true **
** Activity (main) Create, isFirst = false **
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = false **
** Activity (edit) Create, isFirst = true **
Wrong number of values =12 col=14
** Activity (edit) Resume **
InsertMaps (first query out of 1): INSERT INTO [logbook] ([chksoffticks], [chksonticks], [dayhr], [daymin], [nitehr], [nitemin], [ACType], [REGN], [Capacity], [P1], [FO], [DATE], [DEP], [DEST], [Rmks], [instrhr], [instrmin], [INSTR], [CHKSOFF], [CHKSON], [DAY], [NITE]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
** Activity (edit) Pause, UserClosed = true **
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = false **
** Activity (edit) Create, isFirst = false **
Wrong number of values =12 col=14
** Activity (edit) Resume **
InsertMaps (first query out of 1): INSERT INTO [logbook] ([chksoffticks], [chksonticks], [dayhr], [daymin], [nitehr], [nitemin], [ACType], [REGN], [Capacity], [P1], [FO], [DATE], [DEP], [DEST], [Rmks], [instrhr], [instrmin], [INSTR], [CHKSOFF], [CHKSON], [DAY], [NITE]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
** Activity (edit) Pause, UserClosed = false **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
Unexpected event (missing RaiseSynchronousEvents): sv_scrollchanged
Check the unfiltered logs for the full stack trace.
** Activity (main) Pause, UserClosed = false **
** Activity (edit) Create, isFirst = true **
Wrong number of values =12 col=14
** Activity (edit) Resume **
InsertMaps (first query out of 1): INSERT INTO [logbook] ([chksoffticks], [chksonticks], [dayhr], [daymin], [nitehr], [nitemin], [ACType], [REGN], [Capacity], [P1], [FO], [DATE], [DEP], [DEST], [Rmks], [instrhr], [instrmin], [INSTR], [CHKSOFF], [CHKSON], [DAY], [NITE]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Error occurred on line: 384 (Edit)
android.database.sqlite.SQLiteException: unknown error (code 0): Queries can be performed using SQLiteDatabase query or rawQuery methods only.
#################################################################
Error Code : 0 (SQLITE_OK)
Caused By : unknown error (code 0): Queries can be performed using SQLiteDatabase query or rawQuery methods only.
#################################################################
at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(Native Method)
at android.database.sqlite.SQLiteConnection.executeForChangedRowCount(SQLiteConnection.java:903)
at android.database.sqlite.SQLiteSession.executeForChangedRowCount(SQLiteSession.java:754)
at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:64)
at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:2103)
at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:2032)
at anywheresoftware.b4a.sql.SQL.ExecNonQuery(SQL.java:74)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:780)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:363)
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._p1(edit.java:1088)
at harry.logbook.edit._addnew(edit.java:1080)
at harry.logbook.edit._btnaddnew_click(edit.java:1156)
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)
Click to expand...
 
Upvote 0
Status
Not open for further replies.

Similar Threads

Theera
  • Question
Android Question How to use sum() in SQL?
Replies
12
Views
976
chams
chams
rgarnett1955
  • Question
Android Question Will Wait For (SenderFilter) sql_NonQueryComplete Work In a Non-UI Program
Replies
3
Views
686
Erel
Erel
M
  • Question
Android Question Get Total(SUM) from column ?
Replies
6
Views
1K
PdeG
PdeG
rafaelcamara
  • Question
Android Question Select sum() and total() rounding
Replies
11
Views
2K
Erel
Erel
harinder
  • Question
Android Question Summing times
Replies
18
Views
3K
harinder
harinder
Top