Skip to Content
0
Former Member
Oct 29, 2013 at 12:43 PM

Call Hana sproc with parameters from Java

464 Views

I am trying to call a Hana stored procedure from Java which accepts a string value as input and returns a string. But the call to Hana procedure step returns a null value with the below message:

WARN CallMetaDataProviderFactory:91 - HDB is not one of the databases fully supported for procedure calls -- supported are: [Apache Derby, DB2, MySQL, Microsoft SQL Server, Oracle, PostgreSQL, Sybase]

WARN CallMetaDataProviderFactory:95 - Metadata processing disabled - you must specify all parameters explicitly

JdbcTemplate:1194 - Added default SqlReturnResultSet parameter named #result-set-1

null

Questions : what is a best way to pass a string parameter and get the result from the sproc and store in a java variable?

Below is the sproc:

CREATE

PROCEDURE TEST.GETCITY(IN NAME VARCHAR(20)) LANGUAGE SQLSCRIPT AS

BEGIN

SELECT TOP 1 CITY

FROM TEST1.CUSTOMER

WHERE :NAME=CUSTOMERNAME

ORDER BY INSERT_DTTM DESC

; END;

When I execute the sproc in hana: CALL TEST.GETCITY('SAM') it returns the result as BERLIN

this command when used in Java doesnot seem to work

Below is how I call the sproc:


private DataSource dataSource;

private SimpleJdbcCall simpleJdbcCall;

private JdbcTemplate jdbcTemplate = new JdbcTemplate();


private String sprocname="TEST.GETCITY";


public DataSource getDataSource() {

return dataSource;

}


public SimpleJdbcCall getSimpleJdbcCall() {

return simpleJdbcCall; }


@Autowired

public void setDataSource(DataSource dataSource) {

this.dataSource = dataSource;

this.simpleJdbcCall = new SimpleJdbcCall(dataSource).withoutProcedureColumnMetaDataAccess()

.withProcedureName(sprocname)

.declareParameters(new SqlParameter( "NAME", Types.CHAR ));

}

@Override

public String FindCity(string Name) {

jdbcTemplate.setDataSource(this.getDataSource());

Map<String, Object> out = simpleJdbcCall.execute("SAM");

ArrayList resultList = (ArrayList) out.get( "#result-set-1" );

Map resultlMap = (Map) resultList.get( 0 );


String name = (String) resultlMap.get("CUSTOMERNAME");

return name;

}