Issue with passing variable to INSERT statement in the HANA xsjs file

[sundeep_chalasani]

The below Java script is having an issue when I try to insert it through the variable but the hard coding of the value has no issues.

Code that needs to be fixed:   Passing a variable into the values of the insert is not working

var conn = $.db.getConnection();

var i = 1;

for (i = 1; i < 100; i++)

{

var pstmt = conn.prepareStatement("INSERT INTO \"SYSTEM\".\"LOOP\" VALUES (i)");

var rs = pstmt.execute();

}

conn.commit();

Code that works:  Passing a hard coded into the values has no issues

var conn = $.db.getConnection();

var i = 1;

for (i = 1; i < 100; i++)

{

var pstmt = conn.prepareStatement("INSERT INTO \"SYSTEM\".\"LOOP\" VALUES (100)");

var rs = pstmt.execute();

}

conn.commit();

Can someone let me know what I am doing wrong in the code that needs to be fixed? I don't want to hard code the values but pass the dynamic variable as a value for insert.

Thank you,

Sundeep

[lucas_oliveira · Accepted solution]

Sundeep,

You're clearly setting the value in the first query as a string and not passing the value the query expects. There's no automagic feature to get the '(i)' the way it's written and tell the DB to go ahead and insert the row.

Better do it the right way (with named parameters). Something such as:

var pstmt = conn.prepareStatement("INSERT INTO \"SYSTEM\".\"LOOP\" VALUES (?)");
pstmt.setInteger(1,i);
var rs = pstmt.execute();

Please search and check for similar examples a before posting. A very little search showed many resources, such as the one below (magically from the official documentation) :

Using the Server-Side JavaScript APIs - SAP HANA Developer Guide for SAP HANA Studio - SAP Library

OpenSAP has a neat course on native dev on HANA and they go through all that and much much more.

Software Development on SAP HANA (Delta SPS 09, Repeat) - Thomas Jung and Rich Heilman

By the way: namespace $hdb is presenting some neat features over $db. The first will replace the second with time, so get your code updated

(more info here: http://help.sap.com/saphelp_hanaplatform/helpdata/en/c6/bbca35b7734168ac585c0aef9bb527/content.htm )

BRs,

Lucas de Oliveira

[sundeep_chalasani · Accepted solution]

Thanks Lucas. I got around the code.

Magically, the code executes against the database without any errors while activating the XSJS file and when I logoff and log back into the HANA Studio, it shows me an error that variables cannot be declared within the loop when I try to activate it the second time so moved all assignments outside of the loop and it works fine now.

but the magic didn't work when using the .hdb.getConnection() is leads to a 500 - Internal server error so I will stick to .db.getConnection() for now till I go through the official documentation.

Appreciate the help.

Regards,

Sundeep

[lucas_oliveira · Accepted solution]

Well, of course you need to adapt it to your code reality. Either way, this is what it would look like:

[...]
var pstmt = conn.prepareStatement("INSERT INTO \"SYSTEM\".\"LOOPS\" VALUES (?)");


for (i = 1; i < 100; i++)
{
pstmt.setInteger(1,i);
pstmt.execute();
}
[...]

Using the hdb namespace you could have the following:

[...]
var strQuery = "INSERT INTO \"SYSTEM\".\"LOOPS\" VALUES (?)";
var conn = $.hdb.getConnection();
var i = 1;
for (i = 1; i < 100; i++) {
conn.executeUpdate(strQuery,i);
}
conn.commit();
[...]

Get familiar with the XS JS API here:  JSDoc: Index

One last thing: "LOOP" is a SQL reserved word in HANA. Not sure how (or if) you managed to use it as a table. Anyhow: don't use it.

BRs,

Lucas de Oliveira

[sundeep_chalasani · Accepted solution]

The hdb is somehow not working but as soon as I set it back to db, the code works. Do you know of any reason it might be that way?

[lucas_oliveira · Accepted solution]

Error 500 is general server side error and it does not help that much to understand what's wrong on your code.

You'll need to check what the xsengine traces are saying.

BRs,

Lucas de Oliveira

[former_member197071 · Accepted solution]

This message was moderated.