Result/ResultSet return from Stored Procedure Exection

[mukeshid]

Error Code, Result, ResultSet received are always NULL, for executing an procedure in Sybase.

Sybase.prototype.onSQLResponse, jsonMsg... { result: [], javaStartTime: 1482846641812, error: 'JZ0CU: getUpdateCount can only be called once after a successful call to getMoreResult, or execute methods.', msgId: 7, javaEndTime: 1482846646007 }

[rodhoward]

Stored procedures in general should work.

Perhaps your hitting a corner case in your stored procedure. The relevant code is in: https://github.com/rodhoward/node-sybase/blob/master/JavaSybaseLink/src/ExecSQLCallable.java:

boolean isRS = stmt.execute(request.sql);
while (isRS || (stmt.getUpdateCount() != -1))

I can't really see how this could happen unless the original call isn't valid but then you should get a different error message so I'd be interested in a bit more detail if that's possible so that I could re-create the issue.

Cheers Rod

[rodhoward]

As a work around it might also be worth just setting this flag in the stored procedure if your still having issues: SET NOCOUNT ON;

[mukeshid]

Hi rodhoward,

I tried using NOCOUNT parameter, but of no use. The SP looks something like this..

BEGIN TRANSACTION SaveNewBatchTrans

IF ( NULL != @EntryCreationTime )
BEGIN
  EXECUTE ('INSERT INTO BatchInfo (Field1, Field2) VALUES (@Value1, @Value2)')
  IF ( @@error != 0 )
  BEGIN
    ROLLBACK TRANSACTION
    return 102
  END 
END

COMMIT TRANSACTION

BEGIN
  SELECT MAX(Id) AS MAXID from BatchInfo
END

RETURN 0

I do not get back any ResultSet or Error return. SYBASE Version:

Adaptive Server Enterprise/15.7/EBF 24747 SMP SP135 /P/Sun_svr4/OS 5.10/ase157s p133x/3927/64-bit/FBO/Fri Jun 5 01:27:51 2015

[theneva]

For what it's worth, I'm seeing the same error. I get it by doing this:

return new Promise((resolve, reject) => {
  db.query(sql, (err, result) => {
    if (err) {
      logger.error('rejected query', {
        extras: { sql },
      }, err);

      return reject(err);
    }

    resolve(result);
  });
});

My query is real simple with no transactions or anything (this is the output from my logger):

[15:01:30.064] [ERROR] - rejected query - { sql: '\nINSERT INTO Org_Export (OrgId, Export_Code, Override_Status)\nVALUES (1807359866, \'EVERDI\', 1)\n' }

followed by output from my unhandled promise rejection:

(node:22824) UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 2): Error: JZ0CU: getUpdateCount can only be called once after a successful call to getMoreResult, or execute methods.

The query runs (the side effects take place in the database), but it feels bad to knowingly push code that fails to production.

[rodhoward]

Hi Theneva,

I have tested this locally and both stored procedures that insert and update multiple lines are working. My Sybase version is: Adaptive Server Enterprise/15.0.2/EBF 15687 ESD#5/P/x86_64/Enterprise Linux/ase1502/2528/64-bit/FBO/Tue Jun 17 14:24:43 2008

If one of you was able to get to the bottom of this I'd love to publish a new version with your changes but as I can't reproduce it I can't spend more time on it. The code in question is in the Java library: ExecSQLCallable.java

Also Theneva I don't think its necessarily safe to assume that the database will always save your updates. I have found that if you don't push through all the results sets then subsequent results / executions may not happen in the database. e.g. if you have 50 updates and you look at (getMoreResults()) the first 2 then just close the statement the database can ignore or cancel up to 48 of those updates. I'm not an expert and this behaviour is probably config driven but just a warning if your getting an error your data may not be saved.

Cheers Rod

[theneva]

Thanks for your quick reply @rodhoward!

I'm seeing the issue with Solaris 5.10 Adaptive Server Enterprise/15.5/EBF 19898 SMP ESD#5.1

@mukeshid is running on Solaris 5.10 too, maybe that's a factor.

I'll try to reproduce it (and prepare a patch if I can), and get back to you.

[mukeshid]

Hi @theneva, I may not be an expert yet in this matter. But if you wish, you can share a debug JAR to me, and I can capture the logs for you. For me, the problem happens when there are multiple SELECT or SET statements are executed in the Stored Procedure.

[theneva]

Thanks @mukeshid, I'll certainly get back to you if I can't figure it out. In the meantime, feel free to look into the issue as well—I'm not sure when I'll get time to try to fix this.

[mukeshid]

FYKI, I just recreated the JavaSybaseLink.jar JAR from my eclipse setup, Java v1.5, and did no code changes whatsoever. With newly built JAR, I am able to get the ResultSet, but with the original JAR not able to get anything.

Could this be related to Java version compatibility somehow ?

[theneva]

@mukeshid that's really interesting! Nice find.

I tried pointing my JAVA_HOME (which was 8u112) to jre7 (no change in behaviour) and then jre6 (with which my query broke with an error about unexpected characters in the json).

I'll try building the jar with other java versions too, to see if it helps out here as well. It'll have to wait until tomorrow though.

[rafriki]

I am having these exact same issues at the moment, so am watching this with interest. Annoyingly my API is written so that I wait for the resolution of the promise that executes the first stored procedure before moving onto my next query. No response = no resolution at the moment.

[rafriki]

@mukeshid any chance you could make a PR with the new JAR?

[rodhoward]

I have rebuilt the jar with no code changes and released a new version 1.0.13. I am compiling with java 1.6. Its working fine for me but that doesn't say much as it was working for me before as well.

[rafriki]

@rodhoward thanks for that - it's not made a difference to me though - gonna chat with the DBA and see if I can find out what's going on. It times out with any client I use (even RazorSQL) but I note that it's using a java driver also.

[theneva]

Hey @rodhoward sorry about disappearing completely. Your fix works for me, it's no longer failing on my end. :)