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

Discussion in 'B4J Questions' started by Mark Zraik, Mar 15, 2015.

  1. Mark Zraik

    Mark Zraik Member Licensed 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:
    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"Null0, loads_tv)

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

    Code:
    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: Mar 16, 2015
  2. Mark Zraik

    Mark Zraik Member Licensed User

    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:
    Dim str_args(2As 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:
    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: Mar 15, 2015
  3. Mark Zraik

    Mark Zraik Member Licensed User

    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:
    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:
    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: Mar 15, 2015
  4. Mark Zraik

    Mark Zraik Member Licensed User

    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:
    Loads Table Error: java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "?": syntax error)
    If anyone knows, please jump in.
    Mark
     
  5. Erel

    Erel Administrator Staff Member Licensed User

    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.
     
  6. Mark Zraik

    Mark Zraik Member Licensed User

    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
     
  7. Mark Zraik

    Mark Zraik Member Licensed User

    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:
    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", _
                
    Null0, loads_tv)
    :)

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

    :(
    Code:
    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]
     
  8. Daestrum

    Daestrum Well-Known Member Licensed User

    Have you tried changing
    Code:
    Array As String("completed",0,"load_ref"), 0, loads_tv)
    to
    Code:
    Array("completed",0,"load_ref"), 0, loads_tv)
    As it seems you are forcing the 0 to be a string instead of an Integer.
     
  9. Mark Zraik

    Mark Zraik Member Licensed User

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