convert image column's bytes to int's

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

convert image column's bytes to int's

Author

Topic

daktmacfan
Starting Member

2 Posts

Posted - 2008-05-14 : 15:18:02

Hi I have an image column (Spectrum) in a Table (ParticleEDS) which is populated with an array of a bunch of INT32's (4 bytes each)

Using TSQL is there any way that I can read each 4 bytes (convert this to an INT) and return this data for a given record (based on ParticleEDSID).

I know that there are 8192 byes (2048x4 bytes) that make up the image column.

I would like the output of the query/stored procedure to be:

Value
------------
1 2342
2 2334
3 3343
.....
2048 1001

I am thinking that the way to do this would be to convert every 4 bytes into an int and create a temporaty table with an integer column which I populate with int and then run a select * on this temporary table.

Does anybody have any pointers on how I can start to do this?

daktmacfan
Starting Member

2 Posts

Posted - 2008-05-15 : 10:49:38

Here is a solution that ended up working for me...

--input for this function:
declare @ParticleEDSID bigint
set @ParticleEDSID = 1

--This temp table will be the output
create table #Output(RowID int identity(1,1), bin4 binary(4), iOutput as convert(int, bin4))

--Variables that we'll need
DECLARE @ptrval varbinary(16),
@RowNum int,
@ReadStart int,
@cmd nvarchar(255),
@Parms nvarchar(255),
@DataLen bigint

--Get the total len of the field (will be 2048 * 4)
select @DataLen = DataLength(Spectrum)
from ParticleEDS where ParticleEDSID = @ParticleEDSID

set @RowNum = 0

SELECT @ptrval = TEXTPTR(Spectrum)
FROM ParticleEDS where ParticleEDSID = @ParticleEDSID

set @parms = '@b binary(16)'

BeginLoop:

set @ReadStart = @RowNum * 4

set @cmd = 'READTEXT ParticleEDS.Spectrum @b ' + convert(nvarchar,@ReadStart) + ' 4;'

insert into #Output(bin4)

exec sp_ExecuteSQL @cmd, @Parms, @ptrval

set @RowNum = @RowNum + 1

if (@RowNum * 4) < @DataLen
goto BeginLoop

select * from #OUtput

drop table #Output

ppenn
Starting Member

1 Post

Posted - 2008-08-06 : 12:02:43

Does this really work?

I have same issue, trying to dig an int32 out of the opening 4 bytes of my image column

DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(c.codedvalues) from sde.gdb_codeddomains c, sde.gdb_domains d where c.domainid = d.id and lower(d.domainname)='port_facility'
readtext sde.gdb_codeddomains.codedvalues @ptrval 0 4

But all I get back from readtext is an address (0x1D000000), not the number I'm expecting. I can't assign readtext to anything (does your executesql actually work as you expect?) so I'm completely lost.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-06 : 12:08:40

How do you source table look like?
Can you post some proper sample data?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources