Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » scout » Casting query results
Casting query results [message #1053221] Fri, 03 May 2013 08:21 Go to next message
Bertin Kiekebosch is currently offline Bertin KiekeboschFriend
Messages: 321
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 Go to previous messageGo to next message
Jeremie Bresson is currently offline Jeremie BressonFriend
Messages: 987
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 Go to previous messageGo to next message
Justin B is currently offline Justin BFriend
Messages: 19
Registered: January 2015
Location: New Jersey, USA
Junior Member
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 #1599073 is a reply to message #1591768] Tue, 03 February 2015 11:03 Go to previous messageGo to next message
Matthias Zimmermann is currently offline Matthias ZimmermannFriend
Messages: 106
Registered: June 2015
Senior Member
i might not fully understand what you need to do, but the following line seems fishy:

Object[] answer = InvService.queryStockLevel(formData.getItemNr());


you probably what this to say

 Object[] answer = InvService.queryStockLevel(formData.getItemNr().getValue());


can you try this?
Re: Casting query results [message #1599303 is a reply to message #1599073] Tue, 03 February 2015 14:31 Go to previous messageGo to next message
Justin B is currently offline Justin BFriend
Messages: 19
Registered: January 2015
Location: New Jersey, USA
Junior Member
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 #1599304 is a reply to message #1599073] Tue, 03 February 2015 14:32 Go to previous messageGo to next message
Justin B is currently offline Justin BFriend
Messages: 19
Registered: January 2015
Location: New Jersey, USA
Junior Member
Any assistance on how to handle data transfer object that doesn't have a form attached so that it can be referenced elsewhere in the application for logic testing. Example: "if stock is >= 1 then do ..." etc.

[Updated on: Tue, 03 February 2015 21:34]

Report message to a moderator

Re: Casting query results [message #1602459 is a reply to message #1599303] Thu, 05 February 2015 15:27 Go to previous messageGo to next message
Jeremie Bresson is currently offline Jeremie BressonFriend
Messages: 987
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:31
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;
  }
}



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:
result[0][0]

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 Go to previous message
Justin B is currently offline Justin BFriend
Messages: 19
Registered: January 2015
Location: New Jersey, USA
Junior Member
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!
Previous Topic:Scout and REST services
Next Topic:Master Field not firing in mobile clients
Goto Forum:
  


Current Time: Thu Aug 27 19:42:37 GMT 2015

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

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