Binary concatenation with string- Sybase IQ 12.6

Perhaps it would work to first convert the binary value to an int and then use inttohex()?
(I don't have IQ set up so can't test it, and not enough experience with it to be sure, but I recall doing similar things in ASE before the bintostr function was introduced).


 create table bintest
 ( FirmViewI binary(4))
go
insert into bintest values(0x0a7b9ff6)
go
select '0x'+inttohex(convert(int,FirmViewI)) from bintest
go
 

It doesnt work... :(
1> select FirmViewI from bintest
2> go
 FirmViewI
 ----------
 0x0a7b9ff6

(1 row affected)
1>  select '0x'+inttohex(convert(int,FirmViewI)) from bintest
2> go
 '0x'+inttohex(convert(integer,
 ------------------------------
 0x
{ö

(1 row affected)

Well, at least it isn't giving an error.  :-)

Lets break it down a little.   What do you get for just

select convert(int, FirmViewl) from bintest
go


If you stuff that literal value into

select '0x'+ inttohex( <value> )

do you get the same result?

Thanks for the prompt response.
Literal value substitution works. Only if it deals with the table, it doesnt work.

1> select convert(int,FirmViewI) from bintest
2> go
 FirmViewI
 -----------
   175874038
1> select '0x'+ inttohex(175874038)
2> go
 '0x'+inttohex(175874038)
 ------------------------
 0x0a7b9ff6

I tried the below yesterday

1>  select '0x'+ inttohex(hextoint("0x0a7b9ff6"))
2> go
 '0x'+inttohex(hextoint('0x0a7b
 ------------------------------
 0x0a7b9ff6

(1 row affected)

hm.  Ok, well....  looking at your original output, there some clues to what is going on:

 FirmViewI
 ----------
 0x0a7b9ff6

1>  select '0x'+inttohex(convert(int,FirmViewI)) from bintest
2> go
 '0x'+inttohex(convert(integer,
 ------------------------------
 0x
{ö


0x0a is newline
0x7b is {
0x9f is above standard ascii but on your character set I bet it is 'ö'
0xf6 has the same issue, appears to be unprintable.

Anyway, looks like what IQ is doing is converting directly from binary to character,
and I have no idea why it would be doing that.

Looks like in IQ, INTTOHEX() is expected to return binary rather than a char string containing hexidecimal.

Try turning on  the ASE_FUNCTION_BEHAVIOR  option before running the query.

Do this:

SELECT FirmView FROM bintest

and see what you get.  I believe you will get the binary value back as a hex string already.  Sybase does this because most of the time a true binary value would result in garbage at various levels so it converts naked Binary and Varbinary datatypes to Hex Strings prior for display purposes.

If if does not treat it as a string when you try and pre-pend the '0x' on the front, try
'0x' + convert(varchar,FirmView)

Regards,
Bill

Bret,

ASE_FUNCTION_BEHAVIOR  is not available in IQ 12.6.

Bill,
This produces same incorrect output.

1> select '0x'+ convert(varchar,FirmViewI)  from bintest
2> go
 '0x'+convert(varchar,bintest.F                                                                                                                              
 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 0x
{ö                                                                                                                                                          

(1 row affected)

What do you get when you just do select FirmViewI from bintest?

Bill

This is the result,

1> select FirmViewI from bintest
2> go
 FirmViewI
 ----------
 0x0a7b9ff6

(1 row affected)

Ah, isn't that exactly what you want?

Bill

I am trying to concetenate binary(FirmViewI) to a string. In my above example '0x' is a string. (The string is again concatenated with other strings and the value is written to a file.)

May be '0x' is kind of misleading...
Here is an example,
1> select '|0x'+inttohex(FirmViewI) from bintest
2> go

 -----------
 |0x0A7B9FF6

O.K., I have to ask.  If you are going to want to see something as a string, why are you using the one data type for which there are the fewest conversion and manipulation options?

What is the native data type of the source system?
How are you getting the data into IQ?
Is the hex representation actually man-readable?

Regards,
Bill

The datatype in source system is binary. It has been like that for past 10 years ( I guess)
Now, we are bringing data to IQ using Insert into location.

This type of conversion works in ASE but not in IQ.

Is the field man-readable in the source system?  How is it used there?

Your best bet may be to make the IQ field an INT data type and do the conversion at INSERT time.  That way the conversion from binary to int is taking place in the ASE source.

Regards,
Bill

As mentioned earlier, the source system field is binary in the below format.

0x0a7b9ff6

we cannot have integer type in IQ. it has to be binary.

Why?

The data type used for storage and the way you want to see it displayed are two different things.

We want to retain the datatypes as it is in ASE. It is not in my control to change the datatype. Only in one of the report, we need it in string format. We are good otherwise.

My best advice is to find the guy who has the control over the data type and explain it to him.

Spending days of time and money trying to find a kludgy work around for what was a bad data type choice in ASE now propagated to IQ is not good for anybody.

Regards,
Bill

One option I'm not seeing discussed here is to just add a new column.

Since IQ is column-based, it's very very easy to add more columns to tables. This is a useful approach when you want to preserve legacy data for legacy reasons - keep the legacy column in the legacy datatype, but add as many new ones as are useful.

This would let you convert it over to a more easily manipulated format, with a write-back to the binary format if you change things.

Separate to that, has anyone tried

select stuff(FirmViewI, 1, NULL, '|0x')  from bintest

?

It doesnt work.
1> select stuff(FirmViewI, 1, NULL, '|0x')  from bintest
2> go
 stuff(bintest.FirmViewI,1,null
 ------------------------------
 NULL
 NULL
 NULL
 NULL

We have some restrictions because of which we cannot add new column or change existing datatype.
For now, we are processing it in unix.
Thanks for the response.

Oh, interesting. The manuals srtrongly suggest STUFF should be able to deal with hex numbers.

I understand changing existing datatypes being an issue, but the "don't add new columns" is odd, especially in IQ. How will any other application even know the new column(s) exist(s)? Unless there are plenty of "SELECT *" statements, but even then you could get around that.

Anyway, yes, it sounds as though some of IQ's functions aren't quite working as advertised. Doing the work at the O/S level probably makes sense. Good luck!

Hi all,

we have upgraded to IQ 12.7 and it works.

IQ v12.6 was kind of the ugly step child and rather short lived.  Version 12.7 updated the release of ASA on the front end of IQ so it opened up additional functionality that way too.

Regards,
Bill