RE: Integer type on SQL Server vs. AS400 Database

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

Carel,

B-binary fields are not I-integers fields when dealing with internal
RPGLE program variables.

However, in DDS, the B-binary fields are indeed I-integer fields.

If you do a test, the DDS b-binary fields show up in RPGLE as I-Integer
fields and no b-binary fields.

Confusing I know. 

HTH,


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Carel Teijgeler
Sent: Thursday, November 17, 2005 3:00 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Integer type on SQL Server vs. AS400 Database

Vern,

I should do some testing as well, with reference to another thread I
claimed I had difficulties filling an DDS based file, using SQL/400 or
Qurey/400.

My assumption is based on the arguments that B fields (binary) are not I
fields (integers).

When uploading data with ODBC from a PC to the iSeries I instructed them
to pass numeric values as NUMERIC (= Zoned), instead of DECIMAL (=
packed) or INTEGER, as I am not certain if ODBC will do a correct
translation/data mapping.

And I do not think it has to do with "all 9s in a 4 byte integer", as an
integer has a limited range of values (-32k to +32k)

Perhaps others can shed some light, as well.

Regards,
Carel Teijgeler 
 

*********** REPLY SEPARATOR  ***********

On 16-11-05 at 20:48 Vernon Hamberg wrote:

>Carel, I was testing this stuff this morning, and I think I used DDS 
>to create fields of type B - binary. B 9 0 ended up as a 4-byte 
>integer, and it even accepted a 10-digit number using an SQL INSERT, 
>IIRC. B 10 0 resulted in an 8-byte integer. To me, this is all very 
>odd. Probably non-SQL interfaces would fail to write a 10-digit 
>number to a B 9 0 - maybe it is because you cannot put all 9's in a 
>4-byte integer - the reasons why are usually a futile exercise to
>ascertain.
>
>Interesting
>
>At 11:43 AM 11/16/2005, you wrote:
>
>>Ricky,
>>
>>Have you looked at the file with DSPPFM and display hex with F10?
>>
>>Perhaps that tells you more.
>>
>>I assume the file has been created with SQL. DDS based files do not 
>>support integer data types (, yet).
>>*********** REPLY SEPARATOR  ***********
>>
>>On 15-11-05 at 12:43 Ricky Thompson wrote:
>>
>> >I created a DB on the ISeries to match a MS SQL Server database so
that we
>> >could create a DTS package to transfer data between the two systems.
The
>> >problem is the SQL Server number in a integer field is 10 long but
when we
>> >transfer it to the AS/400 it drops the first position.  If you do a
RUNQRY
>> >on the file it replaces the integer value with ******. I thought an
integer
>> >was from -2147483648 to +2147483647.