How to send Array parameter to Stored Proc using DBUtils?

[Former Member]

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 :-

int[] attribute_id = new int[2];

attribute_id[0] = 0;

attribute_id[1] = 1;

result = queryRunner.query("CALL spw_GetStuff(?)", new JsonListAsStringHandler(),attribute_id);


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?

[MarkCulp · Accepted solution]

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

[VolkerBarth · Accepted solution]

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...

[Former Member · Accepted solution]

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?

[MarkCulp · Accepted solution]

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.

[VolkerBarth · Accepted solution]

Well, it is already in the documentation - somewhat hidden for SQLCODE -1599 ("SQLE_INVALID_USE_OF_COLLECTION"):

Probable cause
You tried to use an ARRAY or ROW type in an unsupported context. You cannot store these values in tables or read them directly from client interfaces such as ODBC, ESQL, and JDBC. You must extract the individual values you are interested in.

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:)