| Author |
Topic |
|
banersa
Starting Member
8 Posts |
 Posted - 2011-09-16 : 12:23:40
|
| One query, we have data type binary (16), but in table it is storing the data like ‘0x00000000000000000000000000000000’And total length is coming as 34. But in Extract (i.e. output to text file) through SQLCMD command it is taking The value like these which consists of value ‘0x0000000000000000’And length is coming as 18. Any reason for the difference why it is coming as 18 in extract (text file) and 34 while storing the data in table and 16 is defined as binary (16) (sql-2005). Any reason of it. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-16 : 12:38:54
|
| 1. The lengths for the samples you're showing are 32 and 16, not 34 and 18.2. Use datalength when checking length of binary data.3. 0x0 and 0x0000000000000000 are exactly the same value.4. Seems that either you're storing strings, or casting on the way out.5. Remember that it takes two bits to store each value.Storing a 0 in a binary(16) will always be 0x00000000000000000000000000000000 |
 |
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-09-17 : 00:22:42
|
quote:
1. The lengths for the samples you're showing are 32 and 16, not 34 and 18.
Hey Russell, I'm confused. I"m not all that savvy with bit, but when I run:[CODE]select datalength('0x00000000000000000000000000000000'),datalength('0x0000000000000000') ----------- -----------34 18(1 row(s) affected)[/CODE]It shows 18 for the second value. Did I miss something? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-17 : 09:51:07
|
Take away the single quotes. What you have there is character data, not binary.select datalength(0x00000000000000000000000000000000),datalength(0x0000000000000000) ----------- -----------16 8 |
|
|
|
banersa
Starting Member
8 Posts |
Posted - 2011-09-19 : 10:43:12
|
| Thank you Rusell for clarification of doubts, can you please also calrify , while doing the extraction in text file from table through SQLCMD command the out put in text file is coming as 18 length and not 16, as it has been specified as binary(16) in schema, Any specific reason for it. |
|
|
|
banersa
Starting Member
8 Posts |
Posted - 2011-09-19 : 11:37:14
|
| Just to clarify what is extraction is output to text file from table rows. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-19 : 17:01:44
|
| A text file is just text, so the leading 0x is being counted as part of the data. 0x is just a prefix to say "What follows is binary" but it isn't part of the data itself. |
|
|
|
banersa
Starting Member
8 Posts |
Posted - 2011-09-20 : 10:50:31
|
| If we specify the binary (20) in table, it is also 0X is there in table rows as well as in text file. But due to 0X the length increases to 22 in text file, is that reason will hold good for these. Is that means 0x prefix to say "What follows is binary" but it isn't part of the data itself ( is that holds true for data in table rows and as well as in text file).Kindly suggest. |
|
|
|
banersa
Starting Member
8 Posts |
Posted - 2011-09-20 : 10:55:53
|
| but the data coming in text file is '0x00000000000000000000' which is 22 |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-20 : 11:25:05
|
| You need to ignore the 0x and then divide by 2.EVERYTHING in a text file is text. You'll need to understand binary data type and adjust for it if you're reading it from a text file. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-20 : 11:29:42
|
| Have a look at this |
|
|
|
banersa
Starting Member
8 Posts |
Posted - 2011-09-21 : 06:21:42
|
| Thanks Rusell for all clarification |
|
|
|
|
Binary Data type