Unexpected behavior from longlong when querying MS SQL Server 2000

Len Binns

unread,

Mar 29, 2005, 11:42:23 AM3/29/05

to

I'm using PowerBuilder Version 9.0 Build 6066 to select a bigint from a SQL
Server 2000 database, and the value returned by my query appears to be
garbage. Here's how to reproduce:

-- SQL Server 200 Code
-- Create a table with a bigint in it
--
create table mybigint (i int, b bigint)
insert into mybigint (i, b) values (1, 1234567890)

// PowerBuilder Code Snippet
// Attempt to retrieve the bigint into a longlong
//
longlong b

SELECT b into :b from mybigint where i = 1 using sqlca ;

messagebox("b = ", string(b))

--> What appears in the messagebox is the number 4050765991979987505
Yet, when I use a decimal to do the select, and then assign the decimal to a
longlong, it works fine...

// PowerBuilder Code - using a decimal as a temporary variable
//
decimal d
longlong b

SELECT b into :d from mybigint where i = 1 using sqlca ;

b = d

messagebox("b = ", string(b))

--> Now I get the number 1234567890

Any ideas?

I'm hoping NOT to move to the latest build of PowerBuilder, as that will
significantly increase our test cycle (whenever we change versions of either
PowerBuilder or our database, we go through a full blown test cycle, as
opposed to testing those componants that have changed).

Thanks,
Len

Len Binns

unread,

Mar 29, 2005, 11:45:41 AM3/29/05

to

Forgot to mention...

I'm using an ODBC interface to SQL Server, version 2000.81.9042.00

"Len Binns" <bi...@buffalo.nospam.edu> wrote in message
news:4249856f$1@forums-1-dub...

Len Binns

unread,

Mar 29, 2005, 12:15:34 PM3/29/05

to

Should have tried this before bothering the group...

I updated the SQL 2000 driver (was version 2000.81.9042.00) to a later
version (version 2000.85.1022.00).

It's just slightly newer than the one I was using.

That corrected the problem, so it appears to be a driver issue.

"Len Binns" <bi...@buffalo.nospam.edu> wrote in message

news:42498635$1@forums-1-dub...

Len Binns

unread,

Mar 29, 2005, 12:23:51 PM3/29/05

to

Sorry, ran the wrong snippet against the new driver (I re-ran the
work-around)

The following snippet still produces garbage, and I've now found/downloaded
the newest driver.

// PowerBuilder Code Snippet
// Attempt to retrieve the bigint into a longlong
//
longlong b

SELECT b into :b from mybigint where i = 1 using sqlca ;

messagebox("b = ", string(b))

"Len Binns" <bi...@buffalo.nospam.edu> wrote in message

news:42498d36$1@forums-1-dub...

Scott Morris

unread,

Mar 29, 2005, 12:58:17 PM3/29/05

to

<snip>
It appears you have found a bug - the proper way to report it is via the
sybase website. BTW, same problem using PB9 6533 and oledb but retrieval
via datawindow appears to work. The best we can offer are workarounds. Is
that what you are looking for?

Len Binns

unread,

Mar 29, 2005, 1:20:56 PM3/29/05

to

I was looking for confirmation that it was indeed a bug, and not something I
was doing wrong (its the first time I tried using a longlong).

Thanks for confirming it is indeed a problem, I'll submit it to Sybase.

As for workarounds, it looks like I have two:

1) Using a decimal as an intermediate storage container, and
2) Using a datawindow

Thanks for the tip on the latter. Between these two workarounds, I should
be able to proceed. If you think of any others, or more importantly if you
see a reason I shouldn't use a decimal as an intermediate value, please let
me know.

Thanks again for your response.

-Len

"Scott Morris" <bo...@bogus.com> wrote in message
news:42499739$1@forums-1-dub...

Scott Morris

unread,

Mar 29, 2005, 2:07:55 PM3/29/05

to

RPC might work as well - since you're only selecting a single value. You
might also try one of the dynamic sql formats (format 3) but the RPC route
would be preferable.

"Len Binns" <bi...@buffalo.nospam.edu> wrote in message

news:42499c88$1@forums-1-dub...

Len Binns

unread,

Mar 29, 2005, 2:30:12 PM3/29/05

to

I'm actually using a cursor to go through a number of records.

I came up with this "dumbed down" example to help illustrate the problem in
its simplest form and make it easy for someone to reproduce.

"Scott Morris" <bo...@bogus.com> wrote in message

news:4249a78b@forums-1-dub...

Mike Kruchten

unread,

Mar 30, 2005, 10:24:02 AM3/30/05

to

I found this in the Powerbuilder Help under PowerScript Enhancements. I
don't have PB10 installed to check if it's enabled there, but it looks like
LongLong is not all that useful just yet.
------------------------------------
PowerBuilder provides a LongLong datatype that maps to the 64-bit integer
types used in C++, Java, and XML. This datatype can be used with EAServer,
EJB clients, SOAP clients for Web services, and PBNI applications. It
currently cannot be used with database drivers. The range of the datatype is
from -9223372036854775808 to 9223372036854775807.

Mike Kruchten

"Len Binns" <bi...@buffalo.nospam.edu> wrote in message

news:4249acc4$1@forums-1-dub...

Jim O'Neil [Sybase]

unread,

Mar 30, 2005, 3:31:39 PM3/30/05

to

unfortunately the db drivers aren't coded to take advantage of the
extended datatype.

On 30 Mar 2005 07:24:02 -0800, "Mike Kruchten" <m...@fsisolutions.com>
wrote:

Mike Kruchten

unread,

Mar 30, 2005, 5:58:11 PM3/30/05

to

Jim,
Any idea when this might change? The LongLong type could be useful to me but
not if I can't use it in my DB design.

Mike Kruchten

"Jim O'Neil [Sybase]" <joneil_at_sybase_dot_com> wrote in message
news:g43m41tmqha8ugr84...@4ax.com...

M. Searer

unread,

Mar 31, 2005, 4:55:14 PM3/31/05

to

As a workaround - doesn't a decimal{0} give you the same thing - it is 18
digits signed to the left of the decimal place?

"Mike Kruchten" <m...@fsisolutions.com> wrote in message
news:424b2eff$1@forums-2-dub...