Error calling stored procedure on mysql through a CALL Stmt.

[d.voelkerts]

Hi all,

I followed the stored procedure tutorial and adapted it to MySQL as the orginial once was intented for MS SQL I guess.

My JS Code concats an SQL statement string to pass it to the SP. The second step is to call the stored procedure in the database.

The SP called correctly as the data passed it way into the db.

But Mirth logged an 'error' instead of an 'sent' with the error message:

"org.mozilla.javascript.WrappedException: Wrapped java.sql.SQLException: Invalid column count. Cannot be less or equal to zero (210c2418-5f91-4109-a489-9e86fd9f6aa1#52)
at org.mozilla.javascript.Context.throwAsScriptRuntim eEx(Context.java:1693)"

My JS script:

8<------

dbConn.executeCachedQuery(sql);

8<------

I think the problem is that a SP in MySQL doesn&#039;t return any value. Is there an other function in the DbConnection-Class I could use to call a SP without waiting for a return value?

TIA,
Daniel

Hamm - Germany

[nshaik]

Re: Error calling stored procedure on mysql through a CALL Stmt.

After insert, update, or delete inside the stored procedure, check for error and return a value using a SELECT. For e.g., at the end of the stored procedure,

IF @@ERROR <> 0
SELECT -1
ELSE
SELECT 1

You may also refer to the following post:
[orange]
Suppress warning?

http://www.mirthproject.org/index.php?option=com_loudmouth&task=topic&id=69&Itemid=63

[/orange]

Hope it helps!

[d.voelkerts]

Re: Error calling stored procedure on mysql through a CALL Stmt.

ohh, thanks a lot. My search terms seems to be wrong. I&#039;ll convert my procedure to an function and provide an return statement.

have a nice weekend!

Regards,
Daniel
Hamm(NRW) - Germany

[comicalPenguin]

Alternative solution to this error message

For people of the future or others encountering this error message,

I encountered this exact error message (Wrapped java.sql.SQLException: Invalid column count. Cannot be less or equal to zero), but I had a different cause.

My problem was this: I had dbConn.executeCachedQuery(query); but my query was an UPDATE, not a SELECT statement. I needed to call executeUpdate instead of executeCachedQuery.
I hope this helps others.