Use of LIKE clause in sql prepared statement

Suppose you have a where condition with like clause:

AND ( UPPER(C.ndg) LIKE UPPER('%test%') OR UPPER(C.ndg_name) LIKE UPPER('%test%') )

on Java you should write something like that:

AND ( UPPER(C.ndg) LIKE UPPER('%?%') OR UPPER(C.ndg_name) LIKE UPPER('%?%') )

but, when you fill the paramter with SimpleJdbcTemplate(), this exception will be raised:

PreparedStatementCallback; SQL []; The column index is out of range: 1, number of columns: 0.; nested exception is org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.

This happens because with prepared statement you need to remove the ‘ but, now, how can I write the condition with ‘%’ char?

Simple, you need to sorround the paramter with ‘%’ like that:

sqlString += " and ( UPPER(C.ndg) like UPPER(?) or UPPER(C.ndg_name) like UPPER(?) ) ";
 
getJdbcTemplate().query(sqlString, new Object[] {"%" + searchForm.getNdg() + "%", "%" + searchForm.getNdg() + "%"}, new ViewCGRowMapper());
Tagged , ,

ClickOnce fails: Value does not fall within the expected range.

I finally found out the “Value does not fall within the expected range” problem with a ClickOnce install application. This was the error log:

ERROR SUMMARY
 Below is a summary of the errors, details of these errors are listed later in the log.
 * Activation of http://srvweb.bccpojana.bccvicentino.it/BCCAlert/BCCAlert.application resulted in exception. Following failure messages were detected:
  + Value does not fall within the expected range.

This problem was related to a user profile corruption and recreating it solve the problem but it’s not always possible recreate the user so, to fix that error, just execute this:

rundll32 %windir%\system32\dfshim.dll CleanOnlineAppCache
Tagged ,

Welcome back! Any room for one more?

A distanza di 4 anni dall’ultimo post sul kernelplus, torno a scrivere qualcosa anche se questa volta sul mio primo blog personale. Vorrei utilizzare questo canale prima di tutto come mezzo di comunicazione per le mie attività professionali e politiche, per raccontarvi qualcosa si di me e di cosa stiamo facendo (spero di utile) nel mio paesino,con la speranza nel cuore cuore di non debordare nella tracotante ignoranza del nordest (perché, caro Lei, c’è davvero così tanta cultura nelle altre parti d’Italia? Siamo ignoranti solo perché invece di voler far la pausa caffè preferiamo stare al lavoro?).

Query Alfresco Web script using REST

With Spring 3 querying a REST-based service it’s easy. For first, you need to authenticate on Alfresco repository using an Alfresco Ticket instead of an explicit user name and password. A ticket represents a pre-authenticated user who has already performed the login process. To log in, the url is:

http://localhost:8081/alfresco/service/api/login?u=admin&pw=admin

Assuming the userid and password are correct, Alfresco will respond back with this simple XML:

<!--?xml version="1.0" encoding="UTF-8"?-->
TICKET_18d49dbd5de400c3fa1254b46e46ac51fbd0934e

Using Spring and Java you should write something like that:

RestTemplate restTemplate = new RestTemplate();
Map params = new HashMap();
params.put("user", login);
params.put("password", password);
String url = "http://localhost:8081/alfresco/service/api/login?u={user}&amp;pw={password}";
Source result = restTemplate.getForObject(url, Source.class, params);
XPathOperations xpath = new Jaxp13XPathTemplate();			
this.authenticationTicket = xpath.evaluateAsString("//ticket", result);

The RestTemplate was introduced in Spring 3 to give REST access the same support as JDBC, passing a Map of keywords and values. It serves as a wrapper to hide all the details in accessing REST resources, including opening and closing connection objects and converting responses to Java objects. Source class is the return type.

Once authenticated, we are ready to call Web script to query Alfresco repository:

http://localhost:8081/alfresco/service/kipcast/search/brand.xml?q={keyword}&alf_ticket={ticket}

This web script returns an XML like that:

<brands>
	<brand>
		<nodeRef>a8a4c38d-67fb-475b-84cb-9cf912dfac58</nodeRef>
		<name>Name1</name>
		<supplierName>
			Company1
		</supplierName>					
		<relatedCommonName>
			RelatedCommonName1
		</relatedCommonName>
	</brand>
</brands>

The above response needs some configuration to marshall objects to XML. All we need to do is define it in our application context and, for that to work fine, you need to tell the marshaller which annotated classes you have:

<bean id="restTemplate" class="org.springframework.web.client.RestTemplate">
    <property name="messageConverters">
        <list>
            <bean id="messageConverter" class="org.springframework.http.converter.xml.MarshallingHttpMessageConverter">
                <property name="marshaller" ref="marshaller" />
                <property name="unmarshaller" ref="marshaller" />
            </bean>
        </list>
    </property>        
</bean>
 
 
<bean name="marshaller" class="org.springframework.oxm.xstream.XStreamMarshaller">
    <property name="autodetectAnnotations" value="true"/>
    <property name="annotatedClasses">
        <array>
            <value>com.kipcast.dataModel.drugs.bean.Brands</value>
            <value>com.kipcast.dataModel.drugs.bean.Brand</value>
        </array>
    </property>
</bean>

At this point we can simply instantiate a RestTemplate object by calling the getBean method on the application context. This is the Java code to do that:

ApplicationContext applicationContext = new ClassPathXmlApplicationContext("alfresco-context.xml");
RestTemplate restTemplate = applicationContext.getBean("restTemplate", RestTemplate.class);	
 
Map<String, String> params = new HashMap<String, String>();
params.put("ticket", this.authenticationTicket);	
params.put("keyword", key);	
String url = "http://localhost:8081/alfresco/service/kipcast/search/brand.xml?q={keyword}&alf_ticket={ticket}";
Brands brandList = (Brands) restTemplate.getForObject(url, Brands.class, params);

Last thing is modify the two beans Brands and Brand putting the proper annotations to both classes:

@XStreamAlias("brands")
public class Brands {
 
	@XStreamImplicit
	List<Brand> brandList = new ArrayList<Brand>(); 
 
    public List<Brand> getBrands() {
        return brandList;
    }
 
	public void setBrands(List<Brand> brandList) {
	    this.brandList = brandList;
	}
 
	public void addBrands(Brand brand) {
	    this.brandList.add(brand);
	}
 
}
 
@XStreamAlias("brand")
public class Brand {
 
	private String nodeRef;
	private String name;
	private String relatedCommonName;
	private String supplierName;
 
        // Getter and setter methods
}

For more details please take a look to thekspace.com, tedwise.com and springsource.com.

Tagged , ,

MacOS Tips and Tricks

Rebuild the Spotlight index manually :

sudo mdutil -E /

Insert a blank space on Dock:

defaults write com.apple.dock persistent-apps -array-add '{"tile-type"="spacer-tile";}'
killall Dock

Show all files (also hidden):

defaults write com.apple.finder AppleShowAllFiles TRUE
killall Finder
Tagged

Run Postgres 9.1 on MacOS Lion as Daemon

To start a new Postgres version as Daemon you need to follow these commands. For first, unload current version (if available):

sudo launchctl unload /Library/LaunchDaemons/org.postgresql.dbms.plist

Edit the org.postgresql.dbms.plist (if not available, create a new one):

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" 
"http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
        <key>Label</key>
        <string>org.postgresql.postgres</string>
        <key>ProgramArguments</key>
        <array>
                <string>/Library/PostgreSQL/9.1/bin/postmaster</string>
                <string>-D</string>
                <string>/Library/PostgreSQL/9.1/data</string>
        </array>
        <key>RunAtLoad</key>
        <true/>
        <key>UserName</key>
        <string>_postgres</string>
</dict>
</plist>

Load new configuration:

sudo launchctl load /Library/LaunchDaemons/org.postgresql.dbms.plist

Before reboot your Mac you need to check if the folder data has the right permission:

sudo su - postgres
chmod 700 /Library/PostgreSQL/9.1/data

Reboot your Mac.

To manually start (and stop Postgres on MacOS), login as postgres and:

pg_ctl -D /Library/PostgreSQL/9.1/data -l logfile start
pg_ctl -D /Library/PostgreSQL/9.1/data -l logfile stop
Tagged ,