Parameterized insertion of data in HANA through JavaScript

[Former Member]

Hi there,

I created a little JavaScript application, that calculates different solutions to an optimization problem. At the end of each run I would like to store the solutions in a table on a HANA instance.

So far I tried to apply the code according to this example:

My code with sample values:

var conn = $.db.getConnection();

var st = conn.prepareStatement("INSERT INTO \"SCHEMA\".\"testTable\" values(131,'E')");

st.execute(); 

conn.commit();

conn.close();

Standing alone in its own .xsjs file this code snippet performs perfectly well and the values are added to the table.

However, it doesn't work with parameterized insertion:

var st = conn.prepareStatement("INSERT INTO \"SCHEMA\".\"testTable\" values(?,?)");

st.setString(1,id);

st.setString(2,val1);

Do you have any ideas how I could include a parameterized insertion in the application to write e.g. the values stored in an array to the table?

I hope u can help me and many thanks in advance,

Daniel

[thomas_jung · Accepted solution]

Is that the complete code?  Do you still have the connection commit in the second example?  Are you getting any error or just no data?  Some more information would be very helpful.

[Former Member · Accepted solution]

That's just the code to test the connection to the database since HANA is new to me and I want to find out how everything works. Later on the goal is to write and retrieve data automatically to the database within a JavaScript application.

The second example has as well the connection commit, I just shortened it in the post:

var conn = $.db.getConnection();

var st = conn.prepareStatement("INSERT INTO \"SCHEMA\".\"testTable\" values(?,?)");

st.setString(1,id);

st.setString(2,val1);

st.execute(); 

conn.commit();

conn.close();

Trying to run it I get an 'Error 500 - Internal server error'.

[former_member185132 · Accepted solution]

Hi,

Wrap it in a try/catch block like this (modified parts bolded):

result = "";
try{
     var conn = $.db.getConnection();
     var st = conn.prepareStatement("INSERT INTO \"SCHEMA\".\"testTable\" values(?,?)");
     st.setString(1,id);
     st.setString(2,val1);
     st.execute(); 
     conn.commit();
     conn.close();

     result += "data updated";
     $.response.status = $.net.http.OK;
}catch(e){
     result += e.name + ": " + e.message+"\n";
     $.response.status = $.net.http.FORBIDDEN;


}

Then, once the XSJS gets called and errors out, a look at the JS console in your browser will give you the exact error message and give an idea of what's actually happening.

[thomas_jung · Accepted solution]

The other option is to activate developer mode (xsengine.ini -> httpserver -> developer_mode = true). Then instead of "friendly" error messages like the 500 internal error, you will receive detailed error screens.

Also are both your columns really String data types?  You inserted 131 in your first example. Is the field perhaps numeric?  You must match the data type of the target field with the type in the setter. 

[Former Member · Accepted solution]

Finally it works, as well within the application!!

Thanks Thomas, of course I mixed up the data types - thought I had changed them in the table.

Many thanks for both replies and your quick help!!