2017-08-24

SQL "with"-Statements auf JasperServer

Möchte man in JasperReports with-Statements im Query verwenden, funktioniert das im Studio problemlos, deployed man den Report allerdings auf den Server liefert dieser beim Ausführen eine Security-Exception.

Hintergrund ist das Sicherheitskonzept des JasperServers, welches verhindern soll, dass etwa durch SQL-Injections Schaden an der Datenbank entstehen kann. Leider ist die Validator-Regel zunächst aber etwas zu restriktiv. Zum Glück kann man diese anpassen:

In der Datei

...\jasperserver\WEB-INF\classes\esapi\validation.properties

die Zeile

Validator.ValidSQL=(?is)^\\s*(select|call)\\s+[^;]+;?\\s*$

ersetzen durch

Validator.ValidSQL=(?is)^\\s*(select|call|with)\\s+[^;]+;?\\s*$ 

2017-08-10

Fixing Currency Behaviour in JDeveloper / ADF 12c

As many questions regarding Currency fields in ADF have come to me lately, let me just give you an example of how to tackle these.

It seems, that there is a common error in ADF for German users (or to any other language,where the decimal and grouping delimiter is exactly switched to the English definition).

English (US) Format: $ #,###.##

German Format #.###,##€

As you see, for germans the ',' is changing places wit the '.'

Normally, this would not be much of a deal, but in ADF there seems to be a "hard coded" interpretation somewhere.

So in a simple form, let us type in a salary of 9.999,99 which is a correct entry for the salary field in German.


In case you enter it as the hint provides (that means including the currency symbol) everything seems to work. But for many cases, users are not accustomed to entering the currency symbol.

So let us create our own converter class to fix this issue:

At first, create a java class that is implementing the javax.faces.convert.Converter Interface. This should be annotated by the FacesConverter tag to make it selectable in the UIComponents properties afterwards and removes the requirement to handle the faces-config.xml tracking.


package de.teampb.conv;

import java.math.BigDecimal;

import java.text.DecimalFormatSymbols;
import java.text.NumberFormat;

import java.util.Locale;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.faces.component.UIComponent;
import javax.faces.context.FacesContext;
import javax.faces.convert.Converter;
import javax.faces.convert.ConverterException;
import javax.faces.convert.FacesConverter;

import oracle.adf.share.logging.ADFLogger;

/**
 * Custom JSF Faces Converter to convert entries in Currency Input Texts in a correct way. Oracle ADF seems to be a bit
 * confused, if the grouping character in a Locale is '.' and the decimal delimiter is '.' (for example in Germany).
 */
@FacesConverter("de.teampb.conv.CurrencyConverter")
public class CurrencyConverter implements Converter {

    /**
     * Class logger.
     */
    private static final ADFLogger LOG = ADFLogger.createADFLogger(CurrencyConverter.class.getName());

    /**
     * Converter method from UI Entry to data value. Takes an Input String from the UI Component and converts it to a
     * BigDecimal value for data changes.
     *
     * @param facesContext current JSF Context
     * @param uIComponent Component that has a new value
     * @param string Entered String value (may contain groupings, delimiter or currency symbol)
     * @return correctly converted BigDecimal object for the given input
     */
    @Override
    public Object getAsObject(FacesContext facesContext, UIComponent uIComponent, String string) {
        LOG.entering("CurrencyConverter", "getAsObject", new Object[] { uIComponent, string });
        BigDecimal result;

        final Locale locale = facesContext.getViewRoot().getLocale();
        LOG.finest("Locale for Conversion: " + locale.getLanguage());

        if (string != null && !string.isEmpty()) {
            LOG.finest("Parsing numeric sanity of string...");
            Pattern regex = Pattern.compile("[&:;=?@#|]|[a-zA-Z]");
            Matcher matcher = regex.matcher(string);
            if (matcher.find()) {
                NumberFormat f = NumberFormat.getCurrencyInstance(locale);
                throw new ConverterException(f.format(123456.78));
            }
            LOG.finest("...done");

            String res = string;
            DecimalFormatSymbols symbols = new DecimalFormatSymbols(locale);

            // get Locale specific grouping and decimal seperators
            char sep = symbols.getDecimalSeparator();
            LOG.finest("Decimal Separator used: " + sep);
            char grp = symbols.getGroupingSeparator();
            LOG.finest("Grouping Separator used: " + grp);

            // remove groupings
            String valueConverted = string.replace(grp, Character.MIN_VALUE);
            LOG.finest("String removed of groups:" + valueConverted);

            // change decimal seperator to "US" standards
            valueConverted = valueConverted.replace(sep, ".".charAt(0));
            LOG.finest("String with changed decimal separator:" + valueConverted);

            // throw away any non numeric stuff
            res = valueConverted.replaceAll("[^\\d.]+", "");

            LOG.finest("Expected result:" + res);
            result = new BigDecimal(res);
        } else {
            LOG.finest("Input was empty, so create a zero object");
            // this is of course project specific, can also return null etc.
            result = new BigDecimal(0);
        }
        LOG.exiting("CurrencyConverter", "getAsObject", result);
        return result;
    }

    /**
     * Converter method to create a correct currency String for a given data object.
     *
     * @param facesContext current JSF Context
     * @param uIComponent UI Component that will get the String value
     * @param object data value that shall be converted
     * @return correct String representation of data to a set Locale
     */
    @Override
    public String getAsString(FacesContext facesContext, UIComponent uIComponent, Object object) {
        LOG.entering("CurrencyConverter", "getAsString", object);

        final Locale locale = facesContext.getViewRoot().getLocale();

        LOG.finest("Locale for Conversion: " + locale.getLanguage());
        NumberFormat f = NumberFormat.getCurrencyInstance(locale);
        String res = f.format(object);

        LOG.exiting("CurrencyConverter", "getAsString", res);
        return res;
    }
}

Hint: As you can see from the source code, the converter uses the Locale's specific grouping and decimal seperators, so this converter should be working for any given locale and currency representation.

Next, add this converter to a converter component inside the UI Field that should use this converter:



That's it, we can now just enter the values in simple fashion.

If we enter the value 9999,99 once again, we will see the estimated behaviour.





This entry shows, how easy it is to create a converter to a RichUIInput component, another nice use case could be, that you enter a value with a currency and then use the converter to calculate the currency exchange rate and thus convert it to the databases default currency.

Feel free to check out an example project from the German ADF Community (a subgroup of the German Enterprise Application Development Group) github:

ADFCommunityDE/ADFCustomCurrencyConverter

Thanks for the read, cheers!

2017-03-14

TAKE Four - Amazon Echo (Alexa) mit PL/SQL



Es ist wieder soweit, TEAM lädt zum vierten TAKE (TEAM After Work Knowledge). Wer sich schon immer Oracle Datenbank-orientiert mit dem Thema "Sprachgesteuerte Anwendungen mit Amazon Echo" auseinandersetzen oder generell einen Einblick in das Erstellen von Amazon Anwendungen (Skills) erhalten wollte ist hier genau richtig. Das Beste daran? Es ist vollkommen kostenfrei und sie dürfen selbst Hand an legen.

Was erwartet Sie genauer?

Lassen Sie sich schon vorab mit diesem Video von Wolf G. Beckmann in das Thema Amazon Echo (Alexa) mit PL/SQL einführen.
 
Mit Amazon Echo steuern Sie über die Spracherkennung Alexa Ihr Smart Home und alle damit verbundenen Geräte. Amazon Echo lässt sich aber auch für das eigene Unternehmen einsetzen – speziell mit der Oracle Datenbank-Welt.
 
Bei TAKE four schreiben wir einen Skill in PL/SQL, da Amazon Echo hervorragend und direkt mit der Oracle-Datenbank zusammen arbeitet.
 
Lassen Sie sich die Chance für Ihr Business nicht entgehen: Zur Agenda und Anmeldung
 
TAKE four – „Amazon Echo (Alexa) mit PL/SQL"
Dienstag, 28.03.17 bei TEAM in Paderborn

Beginn: 17:00 Uhr | Ende: 19:30 Uhr
Bitte bringen Sie zur Veranstaltung Ihren eigenen Laptop mit.
 
Wir freuen uns auf Ihr Kommen zu TAKE four!

2017-02-28

Delaying Attribute Validations in ADF for LOV-based fields until commit

I think many ADF developers have encountered the unlazy validation of JSF components during their UI implementations. Especially when creating a new record, the auto submit / partial trigger reactions are quite unpleasant for users. Take this for example:


In this, the user inserted the fields in tab-order. After leaving the field salary (doing a auto submit, triggering the job field), the job validator executes and marks a red border to inform the user of the issue. As the user has never inserted the field up to this point, this is not very nice. One valid change is to remove the mandatory flag from the attribute and delegate the validation towards entity level. But then, you will lose the "red border at attribute" response and get a FacesMessage leaving the user in question, which attribute to change to solve the invalid entity.

Andrejus Baranovskis has written a nice article, how to solve this issue when encountering attributes on the same entity, the validation is occuring on:

http://andrejusb.blogspot.de/2017/02/setting-invalid-fields-for-ui-in-adf-bc.html

So let us extend the usecase a bit.

Issue:

For many List of Value components, users do not want to see the key (for example JobId) in the input text, but the looked up value (JobTitle in this case).



To implement this, there are many options to take. To combine the lov requirement and the delayed attribute validation, let me show you one implementation, that works and is quite declarative.

Define the following use cases:

  • Users want to be able to change the Job of an employee using a list of values showing the title, not the id of the job
  • When changing the salary of an employee, the job will be nullified
  • An empty job leads to a validation, but this should only occur on save button or navigation

The second use case defines the usage of partial triggers / auto submit on JSF side later on.

Solution:

To start, we need the base Entity (Employees), a View Object on that entitity (EmployeesView) and a Lookup View Object (JobsLookupView).


Next create a Transient Attribute on Employees Entity, representing the JobName. This will be the target Attribute for the delayed validation and is the base attribute for the lov.

This Attribute uses an expression referring a ViewAccessor on JobsLookupView:




Expression:

JobId != null ? JobsLookupView.getAllRowsInRange().find{it['jobId']==JobId}['jobTitle'] : null

Next, we will add the entity validation (referring Andrejus' blog entry):


(opt. add failure message)

Add dependencies on Entity attributes:




Additionally remove the mandatory flag from the JobId Attribute (to allow entity validation to do the not null check)

Add LOV to EmployeesView JobName Attribute:


(rem: We can use the Employee-level view accessor in this case because we do not use dynamic query components in this example, so no additional view object instances needed).

Create UI

Finally just create a simple Form Layout on a page, removing the JobId Attribute and ensuring the InputListOfValues Component for JobName.




Then update the following attributes for Salary resp. JobName Components:



Additionally, drag the CreateInsert Operation onto the page, to create a new record.

When running the app, and creating a new employee again tabbing through the form, we will see, there is no validation when tabbing out of the salary field (because the showRequired=true instead of mandatory=true).



When commit or navigation is triggered, the entity validation will fire, but because of the special validation attribute, we will see this on the single attribute instead of a FacesMessage.


This is especially helpful, if you are editing data inside a popup.


TL;DR / Result:

Please feel free to checkout a demo application implementing the shown at the german ADFCommunity github repository

https://github.com/ADFCommunityDE/DelayedAttributeValidationOnLookup.git

Any questions regarding this topic or want to see a feature/idea implemented in ADF? Just send me a message ;)

mail:     mke@team-pb.de
twitter:  @MarkusKlenke

Cheers!