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?

asked 17 Mar '15, 10:56

M%20G's gravatar image

M G
629253044
accept rate: 42%

edited 17 Mar '15, 11:15


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

permanent link

answered 18 Mar '15, 07:53

Mark%20Culp's gravatar image

Mark Culp
23.2k9132273
accept rate: 40%

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

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

(18 Mar '15, 11:24) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×204
×106
×65

question asked: 17 Mar '15, 10:56

question was seen: 1,455 times

last updated: 18 Mar '15, 11:24