Problem updating table using XSJS

[Former Member]

Hello,

I'm trying to update a table using xsjs using the following code:

var query = 'UPDATE "ABC"."TABLENAME" SET SOMEFIELD = ?';
var pstmt = conn.prepareStatement(query);
pstmt.setString(1, 'test');
pstmt.executeUpdate();
conn.commit();

This unfortunately doesn't work.

I've tried to debug this, and when executing line 4 it crashes.

2 questions:

- Is there an obvisous mistake?

- In the debugger view where can I see what's going wrong? Debugging without error messages is not so helpfull...

FYI: When I do a INSERT instead of an UPDATE it works fine.

Thanks a lot for any help!

[thomas_jung]

> Is there an obvisous mistake?

Your SQL is invalid.  For an UPDATE command you need a WHERE condition. The system needs to know what existing record you want to update.  Do you perhaps need to use an UPSERT instead?

As far as error handling, do you have a try catch block around this code to catch the exception and report it?  You can also turn on developer mode in XS to return more detailed but less friendly errors in the HTTP Response.

[Former Member]

Ben,

have you tried 04. pstmt.execute() ? Assuming that you have verified your UPDATE permissions.

Wrap it in try/catch as suggested by Thomas Jung to get slightly more detailed error message

try{

...

}catch(e) {

// see e.toString()

}

HTH

[shahid]

this code works great...

  var s = $.request.parameters.get('s');

          var p = $.request.parameters.get('p');

          var x = $.request.parameters.get('x'); 

          var y = $.request.parameters.get('y'); 

    var output = {}; 

          output.data = []; 

          var query =

              'update "WRK_SCH"."abc.session.data::updatetab" set "x" = ?, "y" = ? where "s" = ? and "p" = ?';

          var conn = $.db.getConnection();

          var cstmt = conn.prepareCall(query);

          cstmt.setString(3,s);

          cstmt.setString(1,x);

          cstmt.setString(2,y);

          cstmt.setString(4,p);

    cstmt.execute();

          conn.commit(); 

          var record = []; 

          record.push(s);

          record.push(x); 

          record.push(y); 

          record.push(p);

          output.data.push(record); 

          conn.close(); 

          $.response.contentType = 'text/json';

          $.response.setBody(JSON.stringify(output)); 

}

[former_member664593]

Hi shahidmohammed.syed.

When I try to make an update I get this error.

{"error":{"code":"500","message":{"lang":"en-US","value":"Internal Server Error"},"innererror":{"code":7,"message":"feature not supported: update statement for volatile table: line 1 col 8 (at pos 7)","sqlState":"HY000"}}}

Can you help me?

Thanks in advance.