Error while inserting record from XSJS

[Former Member]

Hi,

I have created table using the following definition.

When I insert record from SQL console it is working perfectly.

But when I do the same from XSJS it throws the error as

found the following errors:

===========================

InternalError: dberror(PreparedStatement.execute): 598 - failed to execute the external statement: failed to execute at remote: peer=127.0.0.1:30103: unhandled exception (line 14 position 1 in /tryouts/monissha/projects/poc/services/demo.xsjs)

The xsjs code is as follows,

var conn;

var pstmt;

var rs;

conn = $.db.getConnection();

pstmt = conn.prepareStatement('INSERT INTO  "SAPHANA_ROI"."Scenario" VALUES ("SAPHANA_ROI"."ScenarioId".NEXTVAL, ?, ?, ?)');

pstmt.setString(1, $.session.getUsername());

pstmt.setString(2, '');

pstmt.setString(3, '');

pstmt.execute();

conn.commit();

$.response.setBody(0);

$.response.contentType = "text/html";

pstmt.close();

conn.close();

Help me out in resolving this issue.

Thanks & Regards,

Monissha

[sreehari_vpillai]

Hi Monissha,

First of all, surround these statement with a try catch block and get the exact exception message.

Instead of pstmt.execute(), try pstmt.executeUpdate();

Sree

[Former Member]

Hi Sree,

I did the changes what you have mentioned I still get the following error.

dberror(PreparedStatement.executeUpdate): 598 - failed to execute the external statement: failed to execute at remote: peer=127.0.0.1:30103: unhandled exception

Regards,

Monissha

[sreehari_vpillai]

"SAPHANA_ROI"."ScenarioId".NEXTVAL ==> can you try replacing this with a whole number and try ? we can confirm its not the problem with sequence.

[Former Member]

Sree,

I have replaced the sequence with the whole number still i got the same error. But when I changed the prepare statement like the following it worked.

pstmt = conn.prepareStatement('INSERT INTO  "SAPHANA_ROI"."Scenario" VALUES ("SAPHANA_ROI"."ScenarioId".NEXTVAL, \'\', \'\', \'\')');

i.e., I removed the setstring statements instead gave the null values directly there.

Can you please explain?

But I need to use the setstring method in my application since I would be getting the values from the user.

Thanks,

Monissha

[sreehari_vpillai]

Column "ScenarioData" is not string and is Text . So , use pstmt.setText(), instead of setString() method(for 3rd column setter alone). This would be the problem. I didn't notice it before . We blamed the sequence

Sreehari

[Former Member]

It worked out You know setstring for text was working perfectly fine till SP08. Thatsy I didnt give much importance to that earlier. We have upgraded to SP09 recently after that only I started getting this error.

Then might this be the problem after upgrade ?

Thanks

Monissha

[sreehari_vpillai]

If it was working in SP08, and not working in SP09; that means, SAP corrected a bug in SP08

Close the thread . Great that it worked !

[thomas_jung]

If you are on SPS09, then I might suggest using $.hdb library instead of $.db anyway.  The new database library doesn't use type specific setters/getters and would have avoided this problem completely because of it.

[Former Member]

Thank you Sreehari for your prompt replies and suggestions!

[Former Member]

Hi Thomas Jung,

Thank you!

Could you please give me the syntax for 'setters/getters' in this case??

Thanks & Regards,

Monissha

[sreehari_vpillai]

Monnissha,

Check the blog by Thomas.

http://scn.sap.com/community/developer-center/hana/blog/2014/12/02/sap-hana-sps-09-new-developer-fea...

Sree

[thomas_jung]

You missed the point of what I was saying. You are already using the setters/getters - as all pre-SPS09 DB XSJS code would be.  You don't need a sample for that. I was saying that in SPS09 you don't need the setters/getters at all with the new $.hdb database interface.  For examples of that refer to the blog link already provided by Sreehari in the comments or have a look at the online help.

[Former Member]

Yes Thomas, I got your point after reading your blog!

Thank you!