How to access RDO recordset field with array of 8 bytes ?

[andrewd27]

I have a column that stores numbers as 8 byte binary hex numbers eg. 0x0040000000000009
My problem is when I get this column in an ADO recordset I cannot access the value to show in an ASP page as a meaningfull number. The ADO recordset stores it as an array of bits which I don't know how to handle ?

question 1. Can I access this value in the ADO rs in a meaningfull way ?

question 2. Is there a way to convert this value into an 8 byte integer ? I used CONVERT(int, 0x0040000000000009) but this returned 9 the same value when I tried CONVERT(int, 0x0000000000000009) so cleary it is truncating to 4 bytes !!

 

[shaddow]

If u are using Binarry fields u must handle the way u want to deal with this field...U cannot convert it to an number maybe to an Currency field or float or real field but then u store the zecimal places... ________
George, M

 

[andrewd27]

I have not set up this binary field but need to use it in a dropdown list for users to choose from.
All binary fields are ultimately just numbers but this is just a very long one (8 bytes)
SQL won't allow conversion to float and although money works since its 8 bytes, the conversion back to binary fails (gives a different HEX) I think because of overflow.

 

[shaddow]

U could use an varchar 8 field... ________
George, M

 

[andrewd27]

Tried it but still the conversion back was not correct..
SQL Help says binary field conversions to varchar,nchar, nvarchar are truncated.
It seems incredible that there is no long datatype in SQL as in C++ since this would hold the number.
SQL Help says binary conversions to float and real not allowed either.

Thanks for suggestions...
I thought you were onto something with the currency but
still did not store it accurately.

Still stuck !!

 

[shaddow]

Or Try to use a BIGINT field...
I have SQL server 2000 and i try to create a field type BIGINT and it is an 8 bytes long field...

________
George, M

 

[andrewd27]

thanks but unfortunately the web server is running SQL7 so BIGINT is not available.

I am trying to tackle it from the ADO angle using GetChunk to get the binary recordset field value but keep getting an exception "ADODB.Field operation not allowed" although I am using an ASP code example from MSDN ??

Any clues on GetChunk method on ADO value that comes from a SQL7 binary(8) column ??

 

[shaddow]

Nope, but why dont you store this hex values in an string value? ________
George, M

 

[Swany]

So ADO treats it as an array with a ubound of 64 (one indice for each bit?)

This will seem a little weird, but it should work.
note, I just typed this into here and didn't test it
so there will be bugs. you should be able to get it
working though
----------------------------------------------------

type struct1
  byte1 as byte
  byte2 as byte
  byte3 as byte
  byte4 as byte
  byte5 as byte
  byte6 as byte
  byte7 as byte
  byte8 as byte
end type

type struct2
  combined_val as currency
end type

Function SetBit(InByte As Byte, Bit As Byte) As Byte
  SetBit = InByte Or (2 ^ Bit)  
End Function

function slam_value(bitArray) as currency
  dim split_bytes as struct1
  dim composite as struct2

  'stuff the bits into the bytes
  setBits(split_bytes.byte1, 0 , bitArray)
  setBits(split_bytes.byte2, 1 , bitArray)
  setBits(split_bytes.byte3, 2 , bitArray)
  setBits(split_bytes.byte4, 3 , bitArray)
  setBits(split_bytes.byte5, 4 , bitArray)
  setBits(split_bytes.byte6, 5 , bitArray)
  setBits(split_bytes.byte7, 6 , bitArray)
  setBits(split_bytes.byte8, 7 , bitArray)

  'This should stuff all 8 bytes into the currency value
  'in the composite structure
  LSET composite = split_bytes
  
  'return the value
  slam_value = composite.combined_val
  
end function

sub setBits(onByte, cur_byte, bitArray)
    for n = 0 to 7
      if bitArray( (cur_byte * 7)+ n) = 1 then
        setbit(onByte, n+1) 
      end if
    next
end sub

.
.. Eat, think and be merry .
... ....................... .

 

[Swany]

Oh, that will only work in VB (you will need to make a component if you want to use it in ASP) because VBscript doesn't support TYPE, LSET or strongly typed variables.

.
.. Eat, think and be merry .
... ....................... .

 

[Swany]

Another thought just crossed my mind.

While the currency value will store the value properly, if you use all 64 bits, if you try to display the value, vb/vbscript are going to display it as a negative value.

You are probably better off making a VC component that uses an unsigned 64bit int, then sprintf that into a char* and return that to vb/vbscript as a string.

Then you simply have to set the bits in the uint64 and return the string representation of the value.
.
.. Eat, think and be merry .
... ....................... .