Inserting Hexadecimal in stored procedure.

raks81 · #1 (**permalink**) 07-27-09, 09:53

I need to run this SQL in a stored procedure:

Code:

INSERT INTO SCHEMA.TABLE
(POLN,GPVN,TACC,TANA,PRKY)                                  
VALUES                                                      
(222444,                                                    
  5,                                                         
  'I',                                                       
  'PRAARCTB',                                                
  X'000364EC0001C8C3C1D9C5D7C1D9E3C9C3C9D7C1D5E3404040F1F1');

The key here is the last column which I am inserting as a hexadecimal. The above runs fine when executed as SQL from Command Center. I need to run this in an SP. First I tried using regular insert, but this inserts the actual string -> X'234AB23..'

Code:

SET IN_DELTA_TABLE_PRKY = 'X'''||IN_DELTA_TABLE_PRKY||'''';
INSERT INTO PLAY.DELTACTB (POLN, GPVN, TACC, TANA, PRKY)
VALUES (IN_POLICY_NUM, GROUP_POLICY_VERSION, 'I', 'PRAARCTB', IN_DELTA_TABLE_PRKY);

Changed the SQL to dynamic,

Code:

SET V_SQL = 'INSERT INTO PLAY.DELTACTB (POLN, GPVN, TACC, TANA, PRKY) VALUES (?,?,?,?,X''?'')';
PREPARE S_SQL FROM V_SQL;
EXECUTE S_SQL using IN_POLICY_NUM, GROUP_POLICY_VERSION, TACC_CODE, TABLE_NAME_1, IN_DELTA_TABLE_PRKY;

This throws this exception:
A database manager error occurred.[IBM][CLI Driver][DB2/AIX64] SQL0105N The string constant beginning with "X'?'" is not valid. SQLSTATE=42604

Any ideas on how I would run the SQL in the SP? Any help would be appreciated.

Thanks
Raks

ARWinner · #2 (**permalink**) 07-27-09, 10:12

Two questions: What is the data type of the column you want to insert a hex value for? Where does the value for the column come from? (Is is passed to the SP?)

Andy

Lenny77 · #3 (**permalink**) 07-27-09, 11:06

Quote:

SQLCODE = -105: INVALID STRING

Explanation: The statement contains an invalid string. It is neither a
character string nor a graphic string.

System Action: The statement cannot be executed.

Programmer Response: Specify the correct format of the string. Check for
a graphic string, paired delimiters, the character G or N, and an even
number of bytes within the string.

SQLSTATE: 42604

But it could be also:

Quote:

SQLCODE = -103 literal IS AN INVALID NUMERIC LITERAL

Explanation: The indicated 'literal' begins with a digit, but is not a
valid integer, decimal, or float literal.

System Action: The statement cannot be executed.

Programmer Response: Correct the invalid literal.

SQLSTATE: 42604

In my understaning of the problem - the second one was happen with you.

Lenny K.

stolze · #4 (**permalink**) 07-27-09, 13:37

Just prepending a 'x' in front of a parameter marker doesn't work because the actual value is defined by the host variable referring to the parameter marker. So if you want to have binary data handled that way, use a host variable that has a binary data type, e.g. (VAR)CHAR FOR BIT DATA or BLOB. Alternatively, cast the string in the host variable to one of those data types.

raks81 · #5 (**permalink**) 07-28-09, 09:30

The column in the DB is VARCHAR and the values is being passed from the application. (Although now we are open to moving the logic to SP if needed)

Here is the SP stripped of all other logic:

Code:

CREATE PROCEDURE PLAY.INSERT_BENEFIT_RATE2 (IN IN_POLICY_NUM INTEGER,
                                            IN HEX_IN_POLICY_NUM CHAR(100),
                                            IN IN_DELTA_TABLE_PRKY CHAR(254)
                                           )

P1: BEGIN ATOMIC
--VARIABLE DECLARATIONS
DECLARE GROUP_POLICY_VERSION SMALLINT DEFAULT 1;
DECLARE TACC_CODE CHARACTER DEFAULT 'I';
DECLARE TABLE_NAME_1 VARCHAR(8) DEFAULT 'PRAARCTA';
DECLARE V_SQL VARCHAR(100);

--Temporarily hard code this to a hex number and run the insert. This works fine!!
--But 000364EC01 when passed from application does not store correctly.
SET HEX_IN_POLICY_NUM = x'000364EC01';

SET V_SQL = 'INSERT INTO PLAY.DELTACTB (POLN, GPVN, TACC, TANA, PRKY) VALUES (?,?,?,?,?)';
PREPARE S_SQL FROM V_SQL;
EXECUTE S_SQL using IN_POLICY_NUM, GROUP_POLICY_VERSION, TACC_CODE, TABLE_NAME_1, HEX_IN_POLICY_NUM;

END P1

For e.g. If I pass 000364EC01 as the value of HEX_IN_POLICY_NUM parameter from application, it does not store it as expected. But if I hard code it as x'000364EC01' in the SP (as I have done in the code above), it stores it in the DB as expected. Having HEX_IN_POLICY_NUM as CHAR does not seem to be helping and the INSERT statement inserts it as string instead.

So I guess my question is how would I tell the INSERT that HEX_IN_POLICY_NUM needs to be treated as hex and not String?

ARWinner · #6 (**permalink**) 07-28-09, 09:43

Either the caller needs to pass the value as a hex value, or the SP will have to convert it to one.

Is the SP being invoked like this:

call PLAY.INSERT_BENEFIT_RATE2 (1,'000364EC01','')

or this:
call PLAY.INSERT_BENEFIT_RATE2 (1,x'000364EC01','')

If it is the first method, then the SP has to convert the value. If it is the second, then the SP should have to do nothing with it but pass it on along to the insert statement.

Andy

rdutton · #7 (**permalink**) 07-28-09, 09:45

foo character(50) for Bit Data will give you a binary variable that is capable of holding 100 characters of hex string data.
set foo = x'00ABCDE1'; But is your table column defined for hex info?

raks81 · #8 (**permalink**) 07-28-09, 09:55

Its being called like this:

Quote:

Originally Posted by ARWinner

call PLAY.INSERT_BENEFIT_RATE2 (1,'000364EC01','')

Quote:

Originally Posted by ARWinner

If it is the first method, then the SP has to convert the value.

So I am obviously not good at writing SP

, so please dont hate me for what I am about to ask-
I am receiving the passed string '000364EC01' as a CHAR array in the SP, now as I mentioned I cannot pass it to the INSERT as it is. How would I convert this string it to hex?

Thanks
Raks

raks81 · #9 (**permalink**) 07-28-09, 10:03

Quote:

Originally Posted by rdutton

foo character(50) for Bit Data will give you a binary variable that is capable of holding 100 characters of hex string data.
set foo = x'00ABCDE1'; But is your table column defined for hex info?

The column should be defined for hex info cos it is accepting HEX when it is hardcoded.

I changed the code to something like this, but it is still inserting it as a string--

Code:

...
DECLARE BIT_DATAA character(50) for Bit Data;
...
SET  BIT_DATAA = HEX_IN_POLICY_NUM;  
...

Thanks
Raks

n_i · #10 (**permalink**) 07-28-09, 10:10

Obviously you don't understand the difference between values of different data types and their representation to humans, hexadecimal or not. I think this is the solution you are looking for, but I would read on some computer science basics if I were you...

Code:

SET V_SQL = 'INSERT INTO PLAY.DELTACTB (POLN, GPVN, TACC, TANA, PRKY) VALUES (?,?,?,?,x''' ||HEX_IN_POLICY_NUM||''')';
PREPARE S_SQL FROM V_SQL;
EXECUTE S_SQL using IN_POLICY_NUM, GROUP_POLICY_VERSION, TACC_CODE, TABLE_NAME_1;

You may need to trim blanks off HEX_IN_POLICY_NUM because the values don't look like they take all of the 100 characters you have defined.

raks81 · #11 (**permalink**) 07-28-09, 12:04

Changing the IN param to FOR BIT DATA seemed to have fixed the issue partially. Now when I invoke the SP from command center and pass the value it seems to be inserting fine.

Code:

CREATE PROCEDURE PLAY.INSERT_BENEFIT_RATE2
 (IN IN_POLICY_NUM INTEGER,
                                            IN HEX_IN_POLICY_NUM character(50) for Bit Data,
                                            IN IN_DELTA_TABLE_PRKY CHAR(254)
                                           )

However when I call it from the application (Java client using JDBC) its inserting it as string. I guess I would have to take this to a different forum now. Thank you all for your help.

stolze · #12 (**permalink**) 07-28-09, 17:28

A string (CHAR or VARCHAR) is something different than binary data (CHAR FOR BIT DATA or VARCHAR FOR BIT DATA). Passing strings into a variable with binary data type stores the string as binary data, i.e. including '\0' characters etc. What you want to do here appears to be something different: you have a string and you want to interpret 2 characters in the string as hexadecimal value for a single byte. Is that about right?

Internet Services

Web Development

Digital Marketing

Consumer Tech