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

[Mark Zraik]

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

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?

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.

 

[Mark Zraik]

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?

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.

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)

 

[Mark Zraik]

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.

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'.

    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)

 

[Mark Zraik]

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

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

If anyone knows, please jump in.
Mark

 

[Erel]

Note that it has nothing to do with ExecuteTableView.

You probably cannot use a parameter field for the ORDER BY field. Try to set the column name instead.

 

[Mark Zraik]

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

 

[Mark Zraik]

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.

   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!

    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]

 

[Daestrum]

Have you tried changing

Array As String("completed",0,"load_ref"), 0, loads_tv)

to

Array("completed",0,"load_ref"), 0, loads_tv)

As it seems you are forcing the 0 to be a string instead of an Integer.

 

[Mark Zraik]

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