Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2009
    Posts
    8

    Unanswered: Inserting Hexadecimal in stored procedure.

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    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:

    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.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jul 2009
    Posts
    8
    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?

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  7. #7
    Join Date
    Dec 2008
    Posts
    76
    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?
    Last edited by rdutton; 07-28-09 at 10:49.
    RD

  8. #8
    Join Date
    Jul 2009
    Posts
    8
    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

  9. #9
    Join Date
    Jul 2009
    Posts
    8
    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

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Jul 2009
    Posts
    8
    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.

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •