This script demonstrates the problem:

create table stuff(SomeStuff char(10) primary key);
insert into stuff(somestuff) values ('a');
insert into stuff(somestuff) values ('b');
insert into stuff(somestuff) values ('c');
insert into stuff(somestuff) values ('d');
insert into stuff(somestuff) values ('e');
insert into stuff(somestuff) values ('f');
insert into stuff(somestuff) values ('g');
insert into stuff(somestuff) values ('h');

create function Needs2Parameters(a int, b int)
returns int
return a+b

When you run this statement in ISQL:

select somestuff, Needs2Parameters(13) from stuff order by somestuff;

You get a dialog box entitled "Interactive SQL" saying: The following error occured while fetching results: Wrong number of parameters to function 'Needs2Parameters' SQLCODE=-154, ODBC 3 State="42000".

Fair enough, except that you also get a result set looking like this:

somestuff  Needs2Parameters(13)
'b'        (NULL)
'c',       (NULL)
'd',       (NULL)
'e',       (NULL)
'f',       (NULL)
'g',       (NULL)
'h',       (NULL)

note that the first line is missing. With an ODBC client it's even odder, you don't get an error, and the first line consists of random data from a buffer (it's often recognisable as something you selected recently), then you get the remainder of the result set seen in ISQL.

If the function is a built in one, eg

select somestuff, greater(13) from stuff order by somestuff

Then you get a different dialog box, enttled "ISQL Error", Could not execute statement. Wrong number of parameters ...... In this case you get no result set.

The same behaviour is seen in v9.0.2

asked 18 Aug '10, 13:22

Justin%20Willey's gravatar image

Justin Willey
accept rate: 20%

The same thing - partial results sets + garbage - also seems to be a possibility when you get the "Null value eliminated in aggregate function" message

(18 Aug '10, 14:48) Justin Willey

With there's only the error message (SQLCODE=-154, ODBC 3 State="42000"), but no result set.

(18 Aug '10, 15:38) Reimer Pods

I get an appropriate error (not enough parameters) with

(18 Aug '10, 17:38) Mark Culp

@Mark - and no result set?

(18 Aug '10, 17:42) Justin Willey

@Justin: Yes, once the error is raised the query stops (or more correctly, doesn't even start) and there is no result set returned. I think there must have been a fix made that detected the syntactic error but I have not had the time to track down the change (yet... I will try to do this and will post an answer if I find it). Prior to the fix (presuming there was one), my theory is that the error would go undetected and the 'b' parameter would get a value of NULL. This would result in a return value of NULL since int_value+NULL equals NULL and hence the result set that you have seen.

(18 Aug '10, 20:41) Mark Culp

@Justin: Hmmm, I've been trying to figure out which change fixed this issue. There are not a lot of changes between 3931 and 3938 and didn't find anything that would account for the difference... so I tried to repro the issue on 3931 and did not see the behaviour that you saw - i.e. I got the appropriate error. Can you double check the version that you are using?

(18 Aug '10, 21:44) Mark Culp

@Mark: Hmmmmmmmm... very odd. I think there is more than one thing involved here. I've just tested another (older) build 3680, and the behaviour in isql is as you describe and ODBC gets a proper error as well,could there be something about the database? I'll try a new db in 3931.

(19 Aug '10, 13:34) Justin Willey
More comments hidden
showing 5 of 7 show all flat view

For a 10.0 server, a warning (SQL_SUCCESS_WITH_INFO) occurs in SQLFetch:

[1] [42000] [SAP][ODBC Driver][SQL Anywhere]Wrong number of parameters to function 'Needs2Parameters', result rows=8, error in row=1

In my test ODBC app I use a row status array, something like this...

SQLSetStmtAttr( HStmt, SQL_ATTR_ROW_STATUS_PTR, row_status, 0 );

You see the following results (status, column values).

Row:     1 [SQL_ROW_ERROR] [ 1]: ═══════════b [ 2]: -842150451
Row:     2 [SQL_ROW_SUCCESS] [ 1]: b [ 2]: <NULL>
Row:     3 [SQL_ROW_SUCCESS] [ 1]: c [ 2]: <NULL>
Row:     4 [SQL_ROW_SUCCESS] [ 1]: d [ 2]: <NULL>
Row:     5 [SQL_ROW_SUCCESS] [ 1]: e [ 2]: <NULL>
Row:     6 [SQL_ROW_SUCCESS] [ 1]: f [ 2]: <NULL>
Row:     7 [SQL_ROW_SUCCESS] [ 1]: g [ 2]: <NULL>
Row:     8 [SQL_ROW_SUCCESS] [ 1]: h [ 2]: <NULL>

Looks like the 10.0 server produces all rows of the rowset, with no values set for the first row, and valid column values for somestuff and SQL_NULL_DATA for Needs2Parameters(13) in the remaining rows. SQL_ROW_ERROR indicates that the columns values in the first row should be ignored (nothing was written to memory).

Interesting behavior.

For an 11.0 server, an error (SQL_ERROR) occurs in SQLFetch and no fetching is done.

For a 16.0 server, an error (SQL_ERROR) results in SQLExecDirect and no fetching is done.

permanent link

answered 06 Jan '15, 10:58

JBSchueler's gravatar image

accept rate: 19%

Your answer
toggle preview

Follow this question

By Email:

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



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:


question asked: 18 Aug '10, 13:22

question was seen: 7,445 times

last updated: 06 Jan '15, 10:58