Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » scout » Parameterising LookupCalls
Parameterising LookupCalls [message #1274346] Fri, 21 March 2014 10:33 Go to next message
Dominic Hanlan is currently offline Dominic Hanlan
Messages: 98
Registered: July 2009
Member
Hi,

I have a table that uses smart fields and I've started using the lookupcall mechanism to get the members.

Now I have the situation where this smart field can be used to provide a different set of members based on differing scenarios.

Is there a way of parameterising the getConfiguredSqlSelect method ? currently I'm using a static member as a descriminator.

Regards

/Dominic
Re: Parameterising LookupCalls [message #1274441 is a reply to message #1274346] Fri, 21 March 2014 13:20 Go to previous messageGo to next message
Bertin Kiekebosch is currently offline Bertin Kiekebosch
Messages: 303
Registered: August 2011
Senior Member
Hi,

did you try to use the ExecPrepareLookup operation to set parameters on the call? That normally works to set parameters on the call and use them on the back end.

for example on the client you use :

@Override
protected void execPrepareLookup(LookupCall call) throws ProcessingException {
   call.setRec(getCategorieField().getValue());
}


on the server you use

  @Override
  protected String getConfiguredSqlSelect() {
    return "SELECT id, name FROM Test" +
        " WHERE 1=1" +
        " <key>AND id = :key</key>" +
        " <text>AND UPPER(name) LIKE UPPER(:text) + '%'</text>" +
        " AND categoryID = :rec";
  }


note the ":rec" hostvariable thats holds the value that is set on the call.
Re: Parameterising LookupCalls [message #1274466 is a reply to message #1274441] Fri, 21 March 2014 14:05 Go to previous messageGo to next message
Dominic Hanlan is currently offline Dominic Hanlan
Messages: 98
Registered: July 2009
Member
Hi,

I didn't now that existed, thanks a million .... I can clean away those static references!!!
Re: Parameterising LookupCalls [message #1276133 is a reply to message #1274466] Mon, 24 March 2014 06:05 Go to previous messageGo to next message
Jeremie Bresson is currently offline Jeremie Bresson
Messages: 698
Registered: October 2011
Senior Member
Hi,

The default members (key, text, all, rec, master) have meanings. For example "rec" is used in hierarchical lookup calls: it contains the key of the parent entry, to load the children of a parent-node.

You can add your own members:
See Lookup call > Members (check the example with "ValidityFrom" and "ValidityTo").

In the Sql Lookup Service > getConfiguredSqlSelect() property you can also use the members you have defined.

.
Re: Parameterising LookupCalls [message #1277730 is a reply to message #1276133] Wed, 26 March 2014 11:30 Go to previous messageGo to next message
marco giudici is currently offline marco giudici
Messages: 125
Registered: February 2013
Senior Member
Hi,
about this argument of parameterising lookupcalls: I have a list of value to be used as a filter, this list contains a variable number of elements that depends from user selection. In a standard sql I use "IN" statements, but here isn't possible to use for security reason.

Select col1, col2, col3
From table1
Where col3 IN (value1,value2,...,valueN)


How can I generate something like this?

Thanks in advance for your help
Re: Parameterising LookupCalls [message #1277838 is a reply to message #1277730] Wed, 26 March 2014 14:26 Go to previous messageGo to next message
Jeremie Bresson is currently offline Jeremie Bresson
Messages: 698
Registered: October 2011
Senior Member
I am not sure but I think you can use:

where col3 = :myValues


If myValues is a list or an array, the Scout SQL layer should do the expected conversion and use "IN (..)".
Re: Parameterising LookupCalls [message #1277890 is a reply to message #1277838] Wed, 26 March 2014 15:52 Go to previous messageGo to next message
marco giudici is currently offline marco giudici
Messages: 125
Registered: February 2013
Senior Member
Hi Jeremie,
I try before with array of String and after with ArrayList of String but in both cases Scout SQL Layer converts

where col3 = :myValues


in

where col3 LIKE '%[i]first value of my Array or ArrayList[/i]%'


Example:

myValue={'DE','IT','BG'}

where col3 LIKE '%DE%'


Have you any other suggestions?
Re: Parameterising LookupCalls [message #1278292 is a reply to message #1277890] Thu, 27 March 2014 06:13 Go to previous messageGo to next message
Urs Beeli is currently offline Urs Beeli
Messages: 330
Registered: October 2012
Location: Bern, Switzerland
Senior Member
Have you tried

where col3 = :{myValues}
Re: Parameterising LookupCalls [message #1278383 is a reply to message #1278292] Thu, 27 March 2014 08:56 Go to previous messageGo to next message
marco giudici is currently offline marco giudici
Messages: 125
Registered: February 2013
Senior Member
Hi Urs,
I try in this moment with your suggestion but the result is the same of my last post.

Is it possible that I make a mistake that I don't see Confused , below my code:

Client --> DesktopForm
...
        protected void execPrepareLookup(LookupCall call, ITreeNode parent) throws ProcessingException {
          if (getListField() != null) {
            if (getListField().getTable().getSelectedRowCount() > 0) {
              ArrayList<String> listId = new ArrayList<String>();
              int index = 0;
              for (ITableRow row : getListField().getTable().getSelectedRows()) {
                listId.add(row.getCell(0).toString());
                index++;
              }
              c.setMyList(listId);
              c.setListFilter(true);
            }
            else {
              c.setMyList(new ArrayList<String>());
              c.setListFilter(false);
            }
          }
          else {
            c.setMyList(new ArrayList<String>());
            c.setListFilter(false);
          }
}
...


Shared --> Lookup Calls
public class myLookupCall extends LookupCall {

  private ArrayList<String> m_MyList = new ArrayList<String>();
  private boolean m_ListFilter = false;

  public boolean isListFilter() {
    return m_ListFilter;
  }

  public void setListFilter(boolean filter) {
    m_ListFilter = filter;
  }

  public ArrayList<String> getMyList() {
    return m_MyList;
  }

  public void setMyList(ArrayList<String> myList) {
    m_MyList = myList;
  }

}


Server --> Lookcap
...
  protected String getConfiguredSqlSelect() {
    return "SELECT ID, TEXT, NULL, NULL, NULL, NULL, NULL, 0, NULL As PARENTID, 1 "
        + " FROM MyTable "
        + " WHERE ColID<>0 And ColID<>3 "
        + "      And (0 = :ListFilter OR ID = :{MyList} ) "

  }
...


This is the dump that I have:
!MESSAGE org.eclipse.scout.rt.server.services.common.jdbc.internal.exec.StatementProcessor.dump(StatementProcessor.java:809) 
SQL with binds:
SELECT    ID,
          TEXT,
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          0,
          NULL
AS        PARENTID,
          1
FROM      MyTable
WHERE     ColID <> 0
          AND ColID <> 3
          AND (0 = :CountryFilter
          OR
          COUNTRYID = :{CountryIDFilterList})
IN  :CountryFilter => ? [INTEGER 1]
IN  :{CountryIDFilterList} => ? [VARCHAR IT]


Thanks in advance for any suggestions
Re: Parameterising LookupCalls [message #1278447 is a reply to message #1278383] Thu, 27 March 2014 10:56 Go to previous messageGo to next message
Jeremie Bresson is currently offline Jeremie Bresson
Messages: 698
Registered: October 2011
Senior Member
I do not think that you need to use the curly braces because in this case you do not want a batch processing.

Tested with scout 3.9, on the mini-crm tutorial (with a Derby DB*):
    List<Long> list = Arrays.asList(1L, 2L);

    Object[][] result = SQL.select("SELECT COMPANY_NR, " +
        "       SHORT_NAME, " +
        "       NAME, " +
        "       TYPE_UID " +
        " FROM COMPANY " +
        " WHERE COMPANY_NR = :id",
        new NVPair("id", list));


Here the SQL dump (if you change the level, you can get both the "SQL with binds" and "SQL PLAIN Log"):

SQL with binds:
SELECT    COMPANY_NR,
          SHORT_NAME,
          NAME,
          TYPE_UID
FROM      COMPANY
WHERE     COMPANY_NR = :id

SQL PLAIN Log:
SELECT    COMPANY_NR,
          SHORT_NAME,
          NAME,
          TYPE_UID
FROM      COMPANY
WHERE     ((COMPANY_NR IN (1, 2)))


For me you can use the same construct in an SqlLookupService. It calls also SQL.select(..). It is possible to debug it step by step.

Can you reproduce the example on a simple case (without a LookupService)?


(*) I am sure it works also with an Oracle DB. I don't think that it is db engine specific.
Re: Parameterising LookupCalls [message #1290676 is a reply to message #1278447] Thu, 10 April 2014 10:28 Go to previous messageGo to next message
marco giudici is currently offline marco giudici
Messages: 125
Registered: February 2013
Senior Member
Hi all,
I have another doubt/request regarding the lookup parametrization:

if I have two or more parameters that is necessary put in OR condition, is it possibile do something like the code below???

SELECT ID,
          TEXT,
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          0,
          NULL
AS        PARENTID,
          1
FROM      MyTable
WHERE     ColID <> 0
          AND ColID <> 3
          AND ColText1 IS NOT NULL
          AND (ColText Like '% :P1 %' Or ColText Like '% :P2 %')


Where P1 and P2 are two different parameters set with PrepareLookUpCall method.

Thanks in advance for any evaluation and suggestions
Re: Parameterising LookupCalls [message #1291761 is a reply to message #1290676] Fri, 11 April 2014 07:37 Go to previous messageGo to next message
Jeremie Bresson is currently offline Jeremie Bresson
Messages: 698
Registered: October 2011
Senior Member
I do no see why it will not work...

You need to have some getters on your LookupCall: call.getP1() and call.getP2()...
Re: Parameterising LookupCalls [message #1291767 is a reply to message #1291761] Fri, 11 April 2014 07:43 Go to previous messageGo to next message
marco giudici is currently offline marco giudici
Messages: 125
Registered: February 2013
Senior Member
Yes, I have one getters for each parameters in my lookupcall
Re: Parameterising LookupCalls [message #1298498 is a reply to message #1278447] Wed, 16 April 2014 09:58 Go to previous messageGo to next message
marco giudici is currently offline marco giudici
Messages: 125
Registered: February 2013
Senior Member
Hi Jeremie,
I implemented your suggestion an work fine and I obtain the result desidered but I cannot find a way to integrate this development in my LookUpService.
I also tried to load my TreeBoxField with the result of SQL but also this way doesn't give me any result.

Can you have a suggestion/help to exit to this empass?

Jeremie Bresson wrote on Thu, 27 March 2014 11:56
I do not think that you need to use the curly braces because in this case you do not want a batch processing.

Tested with scout 3.9, on the mini-crm tutorial (with a Derby DB*):
    List<Long> list = Arrays.asList(1L, 2L);

    Object[][] result = SQL.select("SELECT COMPANY_NR, " +
        "       SHORT_NAME, " +
        "       NAME, " +
        "       TYPE_UID " +
        " FROM COMPANY " +
        " WHERE COMPANY_NR = :id",
        new NVPair("id", list));


For me you can use the same construct in an SqlLookupService. It calls also SQL.select(..). It is possible to debug it step by step.

Can you reproduce the example on a simple case (without a LookupService)?


Thanks in advance for any help
Re: Parameterising LookupCalls [message #1298595 is a reply to message #1298498] Wed, 16 April 2014 11:23 Go to previous messageGo to next message
Jeremie Bresson is currently offline Jeremie Bresson
Messages: 698
Registered: October 2011
Senior Member
marco giudici wrote on Wed, 16 April 2014 11:58
I implemented your suggestion an work fine and I obtain the result desidered but I cannot d fina way to integrate this development in my LookUpService.
I also tried to load my TreeBoxField with the result of SQL but also this way doesn't give me any result.


I am really sorry, but you need to provide more explanation:
- What is blocking you? (Why can't you "integrate this development in my LookUpService"?).
- What is the problem with a TreeBoxField?

and more context:
- What version of scout are you using?
- How does your DB looks like?
Re: Parameterising LookupCalls [message #1298658 is a reply to message #1298595] Wed, 16 April 2014 12:09 Go to previous messageGo to next message
marco giudici is currently offline marco giudici
Messages: 125
Registered: February 2013
Senior Member
Hi,
sorry I was not very clear.
I use Eclipse Juno 3.8 with Oracle DB and my table is a standard table with different field (integer or char).

Point 1
Query that I do is the following in my LookupService:

SELECT ID,
          TEXT,
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          0,
          NULL
AS        PARENTID,
          1
FROM      MyTable
WHERE     ColID <> 0
          AND ColID <> 3


I want to add your suggestion that it works fine if I use directly "SQL.select" function with parameter, but I don't find the way to integrate this utilization with my Lookupservice and "ExecPrepareLookUp" function.


Point 2
I implemented a method in my FormProcessService that return an Object[][], that rappresent the result of the function "SQL.select" with parameters, as you suggest.
But I cannot seem to populate my TreeBoxField with the result obtains from server side.

Now I have been a bit clearer, I give you all necessary information to try to help me.
Re: Parameterising LookupCalls [message #1298760 is a reply to message #1298658] Wed, 16 April 2014 13:35 Go to previous message
marco giudici is currently offline marco giudici
Messages: 125
Registered: February 2013
Senior Member
Hi,
I find my stupid error and correct it. Now works fine, thanks a lot for your help and explanation

[Updated on: Wed, 16 April 2014 14:15]

Report message to a moderator

Previous Topic:Logout problem SCOUT+RAP
Next Topic:Scout server running under special account
Goto Forum:
  


Current Time: Wed Sep 17 21:45:08 GMT 2014

Powered by FUDForum. Page generated in 0.04227 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software