Home » Eclipse Projects » Eclipse Scout » Casting query results
| Casting query results [message #1053221] |
Fri, 03 May 2013 08:21  |
 Bertin Kiekebosch
Messages: 330
Registered: August 2011 |
Senior Member |
|
|
Hi,
I have a form witfh some fields and a table on it. Normally to load the data in the processing Service I use the selectInto method to load the fields and the table data.
so to load the formData I have two select into calls
SERVICE.selectInto("the sql", formData);
SERVICE.selectInto("the sql", formData.getTable());
Now I have a more complex case where I have to process the table data myself before I can add it to the formData so I use a 'normal' select to get the table data.
Object[][] tempResults = SERVICE.select("the sql");
Then I process the data which means changing some values, removing records from it etc.
In the end I add the processed records 1 by 1 to the formData with:
formData.getTable().addRow(newRow);
The problem with this is that the automatic casting (database types to column types) that Scout does for me is not executed. I solved this by doing it myself for every field like:
tempResults[iRow, iColumn] = TypeCastUtility.castValue(tempResults[iRow, iColumn] , Boolean.class);
The question is, is there a better way to do this? In the way I did it, it works but it is error-prone. If I add a column, change its type etc, change the order of columns etc. I always have to remember to adapt casting.
Regards Bertin
|
|
|
| Re: Casting query results [message #1053244 is a reply to message #1053221] |
Fri, 03 May 2013 11:15  |
Jeremie Bresson
Messages: 1252
Registered: October 2011 |
Senior Member |
|
|
The SDK should generate setter methods on the AbstractTableFieldData contained in your FormData.
This way, in your for each loop, you can do:
//table is somthing like "formData.getTable()" you can declare it outside of your loop.
int i = table.addRow(ITableHolder.STATUS_NON_CHANGED);
table.setSummary(i, "summary");
table.setStatus(i, "my Status");
If you send the FormData back and forth between client and server, you will probably also need:
* table.clearRows() before your loop (corresponding to AbstractTableFieldData#clearRows())
* table.setValueSet(true) after your loop (corresponding to AbstractTableFieldData#setValueSet(boolean)). This will indicate that you have set values in the TableFieldData. I think it is an obligation to tell "a table with no rows". Otherwise the form will ignore your TableFieldData when you do importFormData(..) in the client.
Let me know if it works for you.
[Updated on: Fri, 03 May 2013 11:16] Report message to a moderator |
|
|
| Re: Casting query results [message #1591768 is a reply to message #1053244] |
Thu, 29 January 2015 18:36 |
|
Is there a way to use the SQL.select service to store the values to use in another method. Example: I have created an InventoryService that I will make 'SERVICES.getService' calls to to retrieve current stock levels, item price lists, increment and decrements stock levels, among other things. I want to call to the service which polls the DB and then returns some value to be used in another external method inside the Form Service.
Is this possible?? The SQL.select service requires a return type of Object[] or Object[][].
The existing code I have works but returns the wrong type in the field. I think I may just have not Cast the value properly. See below
//Get Current Inventory Level
//TODO InventoryManager call to inventory level
IInventoryManagerService InvService = SERVICES.getService(IInventoryManagerService.class);
Object[] answer = InvService.queryStockLevel(formData.getItemNr());
if (answer[0] != null && answer.length > 0) {
String qty = String.valueOf(answer[0]);
formData.getCurrentInStock().setValue(qty);
}
else {
formData.getCurrentInStock().setValue("None");
}
This returns "[Ljava.lang.Object;@6421d3cb" and what I want to see is a AbstractLabelField with "1234" for example.
The Service interface is below:
public interface IInventoryManagerService extends IService {
int purchase(Long pId, Long pHowMany) throws ProcessingException;
int purchaseOffBackorder(Long pId, Long pHowMany) throws ProcessingException;
int backorder(Long pId, Long pHowMany) throws ProcessingException;
int setStockLevel(Long pId, Long pNumber) throws ProcessingException;
int setBackorderLevel(Long pId, Long pNumber) throws ProcessingException;
int increaseStockLevel(Long pId, Long pNumber) throws ProcessingException;
int decreaseStockLevel(Long pId, Long pNumber) throws ProcessingException;
int increaseBackorderLevel(Long pId, Long pNumber) throws ProcessingException;
int decreaseBackorderLevel(Long pId, Long pNumber) throws ProcessingException;
int queryAvailabilityStatus(Long pId) throws ProcessingException;
Object[] queryStockLevel(Long pId) throws ProcessingException;
long queryBackorderLevel(Long pId) throws ProcessingException;
long queryStockThreshold(Long pId) throws ProcessingException;
int inventoryWasUpdated(List pItemsIds) throws ProcessingException; |
|
| |
| Re: Casting query results [message #1599303 is a reply to message #1599073] |
Tue, 03 February 2015 14:31 |
|
Thanks for your attention Matthias. I have done it with the first and the Console reports that the method does pass the value of "ItemNr" without ".getValue" The problem is I want to use a separate service to retrieve the value from the database to fill in the form. This means I am exporting ItemFormData to ItemFormService to load the fields from database but I want to then send the form field "CurrentInStock" to a separate service to be filled in and then return the form back to the ItemFormService to complete the loop and load the form for the user to see. Is there a better method to accomplish this? I will attach some code below of what I have now.
Here is the ItemFormService with load method:
public class ItemFormService extends AbstractService implements IItemFormService {
@Override
public ItemFormData load(ItemFormData formData) throws ProcessingException {
if (!ACCESS.check(new ReadItemPermission())) {
throw new VetoException(TEXTS.get("AuthorizationFailed"));
}
SQL.selectInto("" +
"SELECT cat_num," +
" description," +
" uom," +
" type," +
" net_weight," +
" gross_weight," +
" hazardous_material," +
" target_inv_level," +
" min_production_qty," +
" prod_status_id," +
" unit_price " +
"FROM products " +
"WHERE product_id = :itemNr " +
"INTO :catalogueCode," +
" :description," +
" :uOM," +
" :type," +
" :netWeight," +
" :grossWeight," +
" :hazardous," +
" :targetLevel," +
" :minimumProduction," +
" :productStatus," +
" :currentPrice "
, formData);
//Get Current Inventory Level
IInventoryManagerService InvService = SERVICES.getService(IInventoryManagerService.class);
CurrentInStock answer = InvService.queryStockLevel(formData.getItemNr(), formData.getCurrentInStock());
formData.getCurrentInStock().setValue(answer.getValue());
//Set to zero for now
formData.getLastPrice().setValue("0.00");
return formData;
}
}
Then the InventoryManagerService queryStockLevel method:
public class InventoryManagerService extends AbstractService implements IInventoryManagerService {
@Override
public CurrentInStock queryStockLevel(Long pId, CurrentInStock formData) throws ProcessingException {
SQL.select("SELECT " +
" (SELECT SUM(quantity) FROM fabricator_db.inv_transactions WHERE inv_trans_type_id = 1 AND product_id = :prodId)" +
" - (SELECT SUM(quantity) FROM fabricator_db.inv_transactions WHERE inv_trans_type_id = 2 AND product_id = :prodId)"
, new NVPair("prodId", pId)
, formData);
return formData;
}
}
I do not get any errors reporting but the Value on the form Current Stock Level is blank. And when I check the console it doesn't report an output from the sql statement in InventoryManagerService. Here is the output:
!ENTRY org.eclipse.scout.rt.server 1 0 2015-02-03 09:12:31.434
!MESSAGE org.eclipse.scout.rt.server.services.common.jdbc.internal.exec.StatementProcessor.dump(StatementProcessor.java:847)
SQL with binds:
SELECT CAT_NUM,
DESCRIPTION,
UOM,
TYPE,
NET_WEIGHT,
GROSS_WEIGHT,
HAZARDOUS_MATERIAL,
TARGET_INV_LEVEL,
MIN_PRODUCTION_QTY,
PROD_STATUS_ID,
UNIT_PRICE
FROM PRODUCTS
WHERE PRODUCT_ID = :itemNr
INTO :catalogueCode,
:description,
:uOM,
:type,
:netWeight,
:grossWeight,
:hazardous,
:targetLevel,
:minimumProduction,
:productStatus,
:currentPrice
IN :itemNr => ? [BIGINT 3]
OUT :catalogueCode => ? [String]
OUT :description => ? [String]
OUT :uOM => ? [String]
OUT :type => ? [String]
OUT :netWeight => ? [Long]
OUT :grossWeight => ? [Long]
OUT :hazardous => ? [Boolean]
OUT :targetLevel => ? [Long]
OUT :minimumProduction => ? [Long]
OUT :productStatus => ? [Long]
OUT :currentPrice => ? [String]
!ENTRY org.eclipse.scout.commons 1 0 2015-02-03 09:12:31.444
!MESSAGE org.eclipse.scout.commons.ConfigIniUtility.getProperties(ConfigIniUtility.java:94) Properties for fabricator.server.core.services.InventoryManagerService
!ENTRY org.eclipse.scout.rt.server 1 0 2015-02-03 09:12:31.454
!MESSAGE org.eclipse.scout.rt.server.services.common.jdbc.internal.exec.StatementProcessor.dump(StatementProcessor.java:847)
SQL with binds:
SELECT (
SELECT SUM(QUANTITY)
FROM FABRICATOR_DB.INV_TRANSACTIONS
WHERE INV_TRANS_TYPE_ID = 1
AND PRODUCT_ID = :prodId
) - (
SELECT SUM(QUANTITY)
FROM FABRICATOR_DB.INV_TRANSACTIONS
WHERE INV_TRANS_TYPE_ID = 2
AND PRODUCT_ID = :prodId
)
IN :prodId => ? [BIGINT 3]
IN :prodId => ? [BIGINT 3]
|
|
| |
| Re: Casting query results [message #1602459 is a reply to message #1599303] |
Thu, 05 February 2015 15:27 |
Jeremie Bresson
Messages: 1252
Registered: October 2011 |
Senior Member |
|
|
I think you are mixing
* SQL.select()
* SQL.selectInto()
select returns a 2D Object array (=> Object [][]). You need to read this array to get the value. In this case you will need to cast the result from Object to the jave Type you need (String, Integer, Boolean, Date or something else).
selectInto is a convenience function where the result set returned by the database engine will be mapped to the OUT binds.
-------
Justin B wrote on Tue, 03 February 2015 15:31Then the InventoryManagerService queryStockLevel method:
public class InventoryManagerService extends AbstractService implements IInventoryManagerService {
@Override
public CurrentInStock queryStockLevel(Long pId, CurrentInStock formData) throws ProcessingException {
SQL.select("SELECT " +
" (SELECT SUM(quantity) FROM fabricator_db.inv_transactions WHERE inv_trans_type_id = 1 AND product_id = :prodId)" +
" - (SELECT SUM(quantity) FROM fabricator_db.inv_transactions WHERE inv_trans_type_id = 2 AND product_id = :prodId)"
, new NVPair("prodId", pId)
, formData);
return formData;
}
}
In this case, I think that you want to put your result somewhere.
Solution 1/
You have used select, you need to assign the return value to a new variable and to read it. I will be something like this:
Object[][] result = SQL.select("SELECT " +
" (SELECT SUM(quantity) FROM fabricator_db.inv_transactions WHERE inv_trans_type_id = 1 AND product_id = :prodId)" +
" - (SELECT SUM(quantity) FROM fabricator_db.inv_transactions WHERE inv_trans_type_id = 2 AND product_id = :prodId)"
, new NVPair("prodId", pId));
I have removed the formData, because I do not think that you read something from the formData.
I have discussed input/output binds and the relation between sql binds and java method in this thread.
And then your value is here:
You need to handle the no result case (with a if and a check on the length). And you need to cast it to a number.
Solution 2/
You can use selectInto. In this case you will need to assign the value to something.
Assuming that you have a StockLevel value holder in your formData. You have a method:
formData.getStockLevel().setValue(Integer)
or a method:
formData.setStockLevel(Integer)
You can write something like:
SQL.selectInto("SELECT " +
" (SELECT SUM(quantity) FROM fabricator_db.inv_transactions WHERE inv_trans_type_id = 1 AND product_id = :prodId)" +
" - (SELECT SUM(quantity) FROM fabricator_db.inv_transactions WHERE inv_trans_type_id = 2 AND product_id = :prodId)" +
" INTO :stockLevel" +
, new NVPair("prodId", pId)
, formData);
-------
A last point: I should explain the value holder principle.
If you want to define an OUT binding that does not require a formData or a bean (If you do not have a Java object with a setter method where the value can be put). You can use a value holder (a class extending org.eclipse.scout.commons.holders.IHolder like StringHolder, IntegerHolder or something else depending on the type you need).
The code will look like this:
IntegerHolder h = new IntegerHolder();
SQL.selectInto("SELECT " +
" (SELECT SUM(quantity) FROM fabricator_db.inv_transactions WHERE inv_trans_type_id = 1 AND product_id = :prodId)" +
" - (SELECT SUM(quantity) FROM fabricator_db.inv_transactions WHERE inv_trans_type_id = 2 AND product_id = :prodId)" +
" INTO :result" +
, new NVPair("prodId", pId)
, new NVPair("result", h));
//read the value from the holder:
Integer r = h.getValue();
// ...
I hope this helps.
.
|
|
|
| Re: Casting query results [message #1613247 is a reply to message #1602459] |
Thu, 12 February 2015 14:19 |
|
|
Brilliant. I was confusing SQL.select and SQL.selectInto. AND That last part about the holder is exactly the information I needed about handling data returns without formData binding. Cheers!
|
|
|
Goto Forum:
Current Time: Fri Apr 19 22:08:50 GMT 2024
Powered by FUDForum. Page generated in 0.03539 seconds |
] 
Search
Help
Register
Login
Home
