Home » Eclipse Projects » Eclipse Scout » Parameterising LookupCalls
| | | |
Re: Parameterising LookupCalls [message #1277730 is a reply to message #1276133] |
Wed, 26 March 2014 11:30 |
|
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 #1277890 is a reply to message #1277838] |
Wed, 26 March 2014 15:52 |
|
Hi Jeremie,
I try before with array of String and after with ArrayList of String but in both cases Scout SQL Layer converts
in
where col3 LIKE '%[i]first value of my Array or ArrayList[/i]%'
Example:
myValue={'DE','IT','BG'}
Have you any other suggestions?
|
|
| |
Re: Parameterising LookupCalls [message #1278383 is a reply to message #1278292] |
Thu, 27 March 2014 08:56 |
|
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 , 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 |
Jeremie Bresson Messages: 1252 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 |
|
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 #1298498 is a reply to message #1278447] |
Wed, 16 April 2014 09:58 |
|
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:56I 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 #1298658 is a reply to message #1298595] |
Wed, 16 April 2014 12:09 |
|
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.
|
|
| |
Goto Forum:
Current Time: Thu Sep 26 16:27:53 GMT 2024
Powered by FUDForum. Page generated in 0.06101 seconds
|