B4J Question [SOLVED] DBUtils.ExecuteTableView -WHERE-ORDER BY

Mark Zraik

Member
Licensed User
Longtime User
Hello Everyone,

I've been working with DBUtils and was wondering how to implement the "WHERE" and 'ORDER BY" clauses for a tableview.

B4J 2.8 and Windows 8.1, Asus Laptop X750J


Code: Copy
DBUtils.ExecuteTableView(RMSQL, "SELECT [load_ref], [loadid], [pu_date], [del_date],[paid_miles],[money_offered],[pckid],[delid],[completed] FROM loadlist WHERE completed = 0 ORDER BY load_ref DESC", Null, 0, loads_tv)


Do I replace the Null with a Map, or Array that contains the WHERE and ORDER BY?

Code: Copy
DBUtils.ExecuteTableView(RMSQL, "SELECT [load_ref], [loadid], [pu_date], [del_date],[paid_miles],[money_offered],[pckid],[delid],[completed] FROM loadlist WHERE ? ORDER BY ?", ("completed = 0",  "load_ref DESC"), 0, loads_tv)

Could someone re-write this DBUtils statement to show an example?[/Code]

NOTE:
If the code sql statement stops at 'loadlist', then goes on with Null, 0, loads_tv - then the table populates, but without some conditions I am looking for.
 
Last edited:
Hello,
An Update:
The following passes syntax and does not give an error, but also doesn't return anything...Hmmmm
What am I missing?

I guess I'm looking for what the 'StringArgs()' portions of the DBUtils is used for, and what are the possible arguments?


Code: Copy
Dim str_args(2) As String
str_args(0) = ("completed<>1")
str_args(1) = ("load_ref")
DBUtils.ExecuteTableView(RMSQL, "SELECT [load_ref], [loadid], [pu_date], [del_date],[paid_miles],[money_offered],[pckid],[delid],[completed] FROM loadlist WHERE ? ORDER BY ? DESC", str_args, 0, loads_tv)

I'm still working on it.
This passes syntax, too.
Code: Copy
DBUtils.ExecuteTableView(RMSQL, "SELECT [load_ref], [loadid], [pu_date], [del_date]," _
    &"[paid_miles],[money_offered],[pckid],[delid],[completed] FROM loadlist WHERE ?", _
        Array As String("completed = Null"), 0, loads_tv)
 
Last edited:
Upvote 0
Hello,

I Fixed it!
Hopefully this can be useful to others!
And, maybe I can loose less hair in the future.
I'm going to add an 'ORDER BY' and add to the string array and see what happens.
I'll edit this post after i find another success.

Code: Copy
DBUtils.ExecuteTableView(RMSQL, "SELECT [load_ref], [loadid], [pu_date], [del_date]," _
    &"[paid_miles],[money_offered],[pckid],[delid],[completed] FROM loadlist WHERE ? <> ?", _
        Array As String("completed","Null"), 0, loads_tv)
The following passes syntax, but the ORDER BY doesn't seem to have any effect as it is written here...Inch by Inch we go:)
Trying to get a Descending order on 'load_ref'.

Code: Copy
    DBUtils.ExecuteTableView(RMSQL, "SELECT [load_ref], [loadid], [pu_date], [del_date]," _
        &"[paid_miles],[money_offered],[pckid],[delid],[completed] FROM loadlist WHERE ? <> ? ORDER BY ? DESC", _
            Array As String("completed","Null","load_ref"), 0, loads_tv)
 
Last edited:
Upvote 0
Hello,

I changed the title back from [SOLVED] due to NOT finding the correct way to force a Descending ORDER BY.
Post #3 works, but it seems that the Sort Order is ignored (But, There is no error reported), so, the syntax is OK.
Since I'm using DBUtils.ExecuteTableView, I'm thinking there might be something I'm missing on how to properly use the sub routine.
From what I know about SQL syntax, DESC should give me the descending order, but it doesn't seem to work using a tableview.
I tried using another ? and adding DESC to the array, but it threw an error

Code: Copy
Loads Table Error: java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "?": syntax error)

If anyone knows, please jump in.
Mark
 
Upvote 0
Hi Erel,

Thank you for confirming that the routine usage is OK, I'll try what you are suggesting.
And thank you for DBUtils!
Mark
 
Upvote 0
Hi Everyone,

As strange as it is, this code passes syntax and works!
I had this exact statement before, but it didn't seem to want to play.
'completed' is not needed for display, but is needed for distinction, so completed = 0 remains and the value = 0 is forced when the entry into the table occurs (It's a checkbox on the form), without forcing 0 in this case, it leaves the DB_INTEGER as Null, UNKNOWN, or some other value I can't determine or match, so I forced 0.
Notice the ORDER BY is hardcoded as well as completed = 0.

:)
Code: Copy
   DBUtils.ExecuteTableView(RMSQL, "SELECT [load_ref], [loadid], [pu_date], [del_date]," _
        &"[paid_miles],[money_offered],[pckid],[delid] FROM loadlist WHERE completed = 0 ORDER BY load_ref DESC", _
            Null, 0, loads_tv)
:)

The following passes syntax and does not throw an error, but returns an empty table :(, bummer!

:(
Code: Copy
    DBUtils.ExecuteTableView(RMSQL, "SELECT [load_ref], [loadid], [pu_date], [del_date]," _
        &"[paid_miles],[money_offered],[pckid],[delid] FROM loadlist WHERE ? = ? ORDER BY ? DESC", _
            Array As String("completed",0,"load_ref"), 0, loads_tv)
:(

I modified the 0 as an int, placed in quotes - bracketed [load_ref] or unbracketed with the same results.
I tried using the Array As String to substitute just for 'completed' and so forth to no avail. The substitution isn't playing!
I looked at all my other uses of DBUtils.ExecuteTableView and none of them use substitution through the Array As String (), and work flawlessly!

I think DBUtils is awesome!, I just needed to know how to get around this oddity where the substitution doesn't seem to work.
I'm also completely open to the idea that I'm not coding or using it correctly!

So, my conclusion at this point is - Hard code the sql statement for WHERE, ORDER BY and sort order, and it works!
Changing the Title back to [Solved]
 
Upvote 0
Have you tried changing
Code: Copy
Array As String("completed",0,"load_ref"), 0, loads_tv)
to
Code: Copy
Array("completed",0,"load_ref"), 0, loads_tv)
As it seems you are forcing the 0 to be a string instead of an Integer.
 
Upvote 0
Hi Daestrum,

No, but I'll give it a shot and post the results.
For now I have it working as is.

I do want to figure this one out, so I'll give your suggestion a try for sure :)
Mark
 
Upvote 0
Back
Top