472,983 Members | 2,296 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,983 software developers and data experts.

Function for adding 1 to a hex column

I am trying to create a function that adds 1 to the value in a
hexadecimalcolumn. This function I want to use inside an SQL PL procedure.

I have a table with a column with char(16) for bit data. A 'select hex(col1)
from tab1' may return x'FFF59E344C59684CA17BB105677343BE'. I have tried to
create a function that takes the hexadecimal value (the in parameter is also
char(16) for bit data) and adds 1 to the input value. I have managed to add
1 to the value of the input parameter and get this:
x'FFF59E344C59684CA17BB105677343BF'. And this seems to work for any
hexadecimal value I try. But how do I convert this text string back to a
hexdecimal value so that I am able to update the column i db2? If I return
it as CHAR(35) - as a character string and try to update I get this value in
the column: x'78274646463539453334344335393638' ....

I try to do this from Command Center or a Command Window: update tab1 set
col1 = x'FFF59E344C59684CA17BB105677343BF' then it works. But why will it
not work inside a SQL PL procedure when I do: update tab1 set col1 = var1
(where var1 is CHAR(35) and contains x'FFF59E344C59684CA17BB105677343BF').


Odd Bjørn Andersen
ErgoGroup AS
Postboks 4364 Nydalen, N-0402 Oslo, Norway
Telefon +4723 14 50 00, Telefaks +4723 14 50 01
Mobilnr +47970 84597
Feb 20 '08 #1
1 2555
Go through dynamic SQL and let the SQL parser do the conversion:
E.g. in DB2 9.5 you could do something like:

txt = 'SET ? = x''' || hexstring || '''';
PREPARE stmt FROM txt;
EXECUTE stmt INTO myval;

Prior to 9.5 use a dynamic cursor definition with SELECT FROM

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 20 '08 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: KULJEET | last post by:
hi i have a table c_table with two columns no, date it contains records no n_date 1 10-AUG-03 2 21-AUG-03
by: Not Me | last post by:
Hi, I'm sure this is a common problem.. to create a single field from a whole column, where each row would be separated by a comma. I can do this for a specified table, and column.. and I've...
by: Eric Petruzzelli | last post by:
If I fill my dataset and there is no data. The dataset is still created with zero rows (all columns are there). When I add my first row using the script below, it takes over 2 seconds to add??? If...
by: Paul | last post by:
Hi I have a table that currently has 466 columns and about 700,000 records. Adding a new DEFAULT column to this table takes a long time. It it a lot faster to recreate the table with the new...
by: Chris Cobb | last post by:
I have a table that currently contains 718000 rows. I wish to add a column to the table. Adding this column anywhere other than the end of the table requires exporting data, a drop and recreate,...
by: Jim Heavey | last post by:
Trying to figure out the technique which should be used to add rows to a datagrid. I am thinking that I would want an "Add" button on the footer, but I am not quite sure how to do that. Is that...
by: Robin Thomas | last post by:
I am fairly new to ASP.NET so I think I am missing something fundamental. Anyway, quite often I am pulling data from a database, but then I need to use that data to produce more data. A simple...
by: Tony Lownds | last post by:
(Note: PEPs in the 3xxx number range are intended for Python 3000) PEP: 3107 Title: Function Annotations Version: $Revision: 53169 $ Last-Modified: $Date: 2006-12-27 20:59:16 -0800 (Wed, 27 Dec...
by: kirthika | last post by:
In a function we r finding max value of a column which is hving an id value and then incrementing the column value by adding 1 to the value . now we have to check if max value is zero (ie that...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.