Cursor not in a valid state

[SybDBA]

Hi Gurus/Experts,

I am getting the error like this a lot many times, as given below

There was an error reading the results of the SQL statement.

The displayed results may be incorrect or incomplete.

Cursor not in a valid state

SQLCODE=-853, ODBC 3 State="24000"

Value 2147484648 out of range for destination

SQLCODE=-158, ODBC 3 State="22003"

What does it mean and whats the solutions of this ??

PFA for the more details.

Thanks & Regards

[Gisung · Accepted solution]

Hi

Msg 220 "Value %1 out of range for destination" is very generic,

it just indicates that we "supplied to or fetched from the database a value

that is out of range for the destination column or host variable."

Which IQ version do you use?

===

Gi-Sung Jang

[SybDBA · Accepted solution]

Hi Gi-sung,

Thanks for your precious time.

IQ Version : SAP IQ/16.0.0.808/141110/P/sp08.20/Sun_Sparc/OS 5.10/64bit

I understood the problem, but I need to know the cause and the solution for this kind of issues.

---

Regards

[Gisung · Accepted solution]

Hi,

It seems like a known issue.

- CR #714634 //It's in progress

  Title: SQLCODE=-158 ,Value 2147484649 out of range for destination.

These user ID values are greater than max int value of 2147483647 which user_name() function can handle.

(DBA)> select grantee from sys.systableperm where grantee > 10000

grantee

----------

2147484649

2147484649

2147484649

2147484650

2147484650

2147484650

==

Gi-Sung Jang

[SybDBA · Accepted solution]

When I suppose to get update on this CR #714634

---

Regards

[Gisung · Accepted solution]

Hi,

When I checked this CR #714634, It's still open.

So If you want to proceed it more faster, please open the Incident for this.

I think you can receive the update from SAP via Incident.

==

Gi-Sung Jang

[tayeb_hadjou]

Hi Vivek,

Here, in this case, it is expected error, not a bug.

USER_NAME function accepts INT(INTEGER) Datatype Values. INT range is [0 2147483647]

However "sysuser.user_id" Datatype is Insigned INT, thus could support values up to 4294967295.

Solution : Instead of query

select User_Name(sysuser.user_id) from sysuser ;

Use query

select user_name from sysuser ;

No need to use function User_Name. Column "sysuser.user_name" can be used.

When you need absolutedly to use USER_NAME function, make sure the parameter is in range [0-2147483647]. Eg. select  user_name(sysuser.user_id) from sysuser where user_id <=2147483647 and ....

For uper values user "sysuser.user_name" column

Regards,

Tayeb.

[tayeb_hadjou]

Another solution consists of creating an IQ function, Eg my_user_name() where you force IQ  (not Sql Anywhere, the Catalog engine) to process it.

CREATE FUNCTION my_user_name(idu UNSIGNED INT)

RETURNS varchar(200)

BEGIN 

DECLARE res varchar(200);

select user_name(idu) into res from iq_dummy;

RETURN res ;

END;

Query below will work even for high values

select my_user_name(sysuser.user_id) from sysuser where user_id >2147483648 ;

Regards,Tayeb.