Hi Is it possible to send an array of integers from Java to a (SqlAnywhere16) store procedure? I get the following error. Exception thrown :java.sql.SQLException: [Sybase][JDBC Driver][SQL Anywhere]Communication error Query: CALL spw_GetStuff(?) Parameters: [[0,1]] My software stack is as follows :- Java – DBUtils – JDBC – network – Stored Procedure - SQLAnywhere The call is :-
Is this possible? And if so, what am I doing wrong? Bonus question: Can I dynamically change the size of my array or must it be hardcoded? |
I know nothing about the Java's 'dbutils' package and it has been many years since I have written a Java program, but... You cannot transfer an array from the client to the server OR from the server to the client as an ARRAY (or ROW) data type. See the documentation on composite data types for a discussion of the limitations of using ARRAY and ROW types. If you know the length of the array - in your example this is 2 - you could pass each element in the array as a separate bound (i.e. '?') parameter in the statement (e.g. "call spw_GetStuff(?,?)"). Another option is to build a batch to construct the array and then call the procedure - something like: "begin declare @a array(2) of integer; set @a[[1]] = ?; set @a[[2]] = ?; call spw_GetStuff(@a); end;" HTH 1
So, according to the cited documenation, a procedure call belongs to the category "query expression that is returned to the client"? I guess that could be made a bit more verbose...
(18 Mar '15, 08:51)
Volker Barth
I would have liked a little bit better explanation in the documentation, perhaps it can be clarified so nobody else asks the same question after a while?
(18 Mar '15, 10:58)
M G
Replies hidden
Agreed. I was surprised when I was looking through the documentation that I did not find a statement that said that ARRAYs and ROWs can not be sent between client and server and that these data types can only be used in procedure logic within the server. I will be discussing this will the doc team to get this clarified in the docs.
(18 Mar '15, 11:02)
Mark Culp
Well, it is already in the documentation - somewhat hidden for SQLCODE -1599 ("SQLE_INVALID_USE_OF_COLLECTION"):
I guess I have read it in Breck's "Top 10 Cool New Features In SAP® Sybase® SQL Anywhere® 16", which might make a great add-on to the official documentation, too:)
(18 Mar '15, 11:24)
Volker Barth
|