XSJS - db table select statement with input parameter

[former_member375266]

Hi All,

I'm very new to XSJS and I'm trying to learn it.. I went through the Reference guide but found it difficult to find the syntax of a select statement on the db table with input parameter and how to call this XSJS in the web browser. I'm using the below code and saved/activated it as customer.XSJS

try {
	$.response.contentType = "text/html";
	var output = "Hello, " + $.session.getUsername() + " <br><br>";
	var conn = $.db.getConnection();
var id = $.request.parameters.get("id");
var query = "SELECT  MTART FROM <SCHEMA>.MARA WHERE MATNR like ?";
var pstmt = conn.prepareStatement( query );
pstmt.setString(1, "'%"+id+"%'");
var rs = pstmt.executeQuery(); 
if (!rs.next()) {
	$.response.setBody( "Failed to retrieve data" );
	$.response.status = $.net.http.INTERNAL_SERVER_ERROR;
	}
else {
	output = output + "This is the Material Number is" + rs.getString(1);
	}
rs.close();
pstmt.close();
conn.close();
$.response.setBody(output);
}
catch (e){
	$.response.status = $.net.http.INTERNAL_SERVER_ERROR;
	$.response.setBody(e.message);
	}

and when i execute the link ( http://<SERVER-NAME>/<PACKAGE-NAME>/customer.xsjs?id=<VALUE>; ) i just get "Hello, <USERNAME>. I don't get the results for the "ELSE" part of my If statement even though there is an entry in the db.

When i modify my XSJS with no input parameter and run it, i get the output. Just the XSJS with input parameter is not giving me the results.

[thomas_jung]

If you are just learning XSJS, might I suggest the openSAP hana courses. There are basic exercises that teach such syntax in these courses.

[former_member375266]

Hi Thomas,

Thanks for the suggestion. When i searched for XSJS in OpenSAP, I find lecture on Software Development on SAP HANA (Delta SPS 09, Repeat) where it talks about the new features in XSJS,XSODATA. I'm not sure if you were discussing the basics of XSJS.

By any chance do you know which course do i need to subscribe to learn more about XSJS.

[pfefferf · Accepted solution]

Hi,

when you set the parameter for the prepared statement you do not have to use single quotes to enclose the id value including the "%" signs.

You have to do it without single quotes:

pstmt.setString(1, "%" + id + "%")

Regards,
Florian

PS: In general you should think about to use the new $.hdb DB interface instead of the legacy interface $.db.

[former_member375266 · Accepted solution]

Thanks Florian. Its working now.