Professional Web Applications Themes

Actually passing NULLvalues to UDF - IBM DB2

I create a very basic java UDF. SQL creation statement is CREATE FUNCTION TestWithNULL( Arg1 VARCHAR(255),Arg2 VARCHAR(255) ) RETURNS INTEGER EXTERNAL NAME 'KxenUDF!TestWithNull' LANGUAGE JAVA PARAMETER STYLE DB2GENERAL DETERMINISTIC FENCED NULL CALL NO SQL NO EXTERNAL ACTION SCRATCHPAD NO FINAL CALL ALLOW PARALLEL NO DBINFO The code of java method TestWithNull manages null values in parameters When I call SELECT TestWithNull( NULL,NULL ) From ATable I get SQL0206N "NULL" is not authorized in this context. SQLSTATE=42703 Here is my guess NULL is a very special value which has no type. So matching the typed to find the correct UDF to ...

  1. #1

    Default Actually passing NULLvalues to UDF

    I create a very basic java UDF. SQL creation statement is

    CREATE FUNCTION TestWithNULL( Arg1 VARCHAR(255),Arg2 VARCHAR(255) )
    RETURNS INTEGER
    EXTERNAL NAME 'KxenUDF!TestWithNull'
    LANGUAGE JAVA
    PARAMETER STYLE DB2GENERAL
    DETERMINISTIC FENCED
    NULL CALL
    NO SQL
    NO EXTERNAL ACTION
    SCRATCHPAD
    NO FINAL CALL
    ALLOW PARALLEL
    NO DBINFO

    The code of java method TestWithNull manages null values in parameters

    When I call
    SELECT TestWithNull( NULL,NULL ) From ATable
    I get
    SQL0206N "NULL" is not authorized in this context. SQLSTATE=42703

    Here is my guess
    NULL is a very special value which has no type. So matching the typed to
    find the correct UDF to call is not possible

    SELECT TestWithNull( CAST( NULL AS VARCHAR(255)),CAST( NULL AS
    VARCHAR(255)) ) From ATable
    is working.
    Looks like I have made the right guess ...

    Now, a real UDF in our case is generated code for a UDF with a LOT of
    parameters
    Typical usage should be
    SELECT RealUDF( Field1,FielD,Field3,...,Field30,.... ) From
    ATableWithNULLValues

    If the table contains some null values (and it is always the case), the
    select stops at the first one.
    So the correct call should be :
    SELECT RealUDF( CAST( Field1 AS TypeOfField1),CAST( Field2 AS
    TypeOfField2),CAST( Field2 AS TypeOfField2),...,CAST( Field30 AS
    TypeOfField30),.... ) From ATableWithNULLValues

    It looks me very heavy to use !!!
    Am I wrong somewhere ? Is there a tricky switch in DB2 ? Has someone
    already have this pbm ?


    Thx


    Alain Charroux
    ---------------
    KXEN tel: +33 (0)1 41 44 88 42
    25 Quai de Gallieni fax: +33 (0)1 41 44 88 40
    Suresnes - FRANCE

    Soir d'automne
    un corbeau passe
    sans un cri

    Autumnal evening
    A crow flies by
    Without a cry



    Alain Guest

  2. #2

    Default Re: Actually passing NULLvalues to UDF

    Alain Charroux <com> wrote:
     

    You are correct that NULL, as written above, is untyped and a CAST is
    required to give it a type so that DB2's function resolution can find the
    correct function.
     

    This is not correct. If you have a query as you have written, the data
    types associated with the columns "field1", ... are used for the function
    resolution.
     

    That's not necessary as the types of the columns are already known.

    The cast of function parameters is needed in two cases:
    (1) if you use parameter markers in prepared statements
    (2) if you put a "NULL" in the statement and DB2 cannot derive the type for
    that parameter in any other way (CASE expressions come to mind).

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Guest

  3. #3

    Default Re: Actually passing NULLvalues to UDF

    Gosh !!

    OK You are right. The pbm occurs only when I explicitely pass the NULL
    'constant' in a parameter.
    Thx !!


    --
    Alain Charroux
    ---------------
    KXEN tel: +33 (0)1 41 44 88 42
    25 Quai de Gallieni fax: +33 (0)1 41 44 88 40
    Suresnes - FRANCE

    Soir d'automne
    un corbeau passe
    sans un cri

    Autumnal evening
    A crow flies by
    Without a cry

    "Knut Stolze" <ibm.com> wrote in message
    news:bks8i2$2o4$rz.uni-jena.de... 
    >
    > You are correct that NULL, as written above, is untyped and a CAST is
    > required to give it a type so that DB2's function resolution can find the
    > correct function.

    >
    > This is not correct. If you have a query as you have written, the data
    > types associated with the columns "field1", ... are used for the function
    > resolution.

    >
    > That's not necessary as the types of the columns are already known.
    >
    > The cast of function parameters is needed in two cases:
    > (1) if you use parameter markers in prepared statements
    > (2) if you put a "NULL" in the statement and DB2 cannot derive the type[/ref]
    for 


    Alain Guest

Similar Threads

  1. passing URL
    By quiero mas in forum Coldfusion Database Access
    Replies: 1
    Last Post: February 10th, 03:06 AM
  2. Passing a Value from Asp.net
    By khussan in forum Macromedia Flash Flashcom
    Replies: 3
    Last Post: August 24th, 09:16 AM
  3. Passing a value to VB6
    By Freeserve in forum ASP
    Replies: 17
    Last Post: October 2nd, 02:09 PM
  4. Replies: 1
    Last Post: July 14th, 08:05 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139