Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2012
    Posts
    6

    Unanswered: IDENTITY_VAL_LOCAL() returns null

    Hello,

    I do know what is causing this problem, but I can't seem to find a work around to solve it.

    As far as I can tell the problem is the select statement before the insert into statement, but I need it and it would be kind of stupid to make another stored procedure just for the select statement. Is there a way to do this without making another stored procedure? Am I maybe misunderstanding something about IDENTITY_VAL_LOCAL() or am I right and I simply can't use it in this situation.

    Code:
    CREATE OR REPLACE PROCEDURE "ADDON21C"."STP_BONUSPRAEMIE_ADD"
    (
            IN @Id Integer,
            IN @Buchungnr VARCHAR(70),
            IN @Name Varchar(30),
            IN @Vorname Varchar(30),
            IN @Namensvorsatz Varchar(20),
            IN @Geschlecht char,
            IN @Strasse Varchar(30),
            IN @Hausnummer VARCHAR(15),
            IN @Plz VARCHAR(10),
            IN @Ort Varchar(60),
            IN @Kvnr  VARCHAR(30),
            IN @PraemieExtId VARCHAR(10),
            In @Timestamp VARCHAR(20),
            IN @Versandstatus SMALLINT,
            IN @Stornogrund SMALLINT,
            IN @Paketnummer VARCHAR(50),
            IN @Auslieferer VARCHAR(70),
            IN @Gutscheincode VARCHAR(50),
            IN @Lieferverzugsdatum DATE,
            OUT @result BIGINT
    LANGUAGE SQL
    SPECIFIC SQL12080109131611
    BEGIN
    
    DECLARE KVNr_Neu VARCHAR(30);
    DECLARE Titel VARCHAR(30);
    DECLARE Zusatzwort VARCHAR(30);
    DECLARE LKZ VARCHAR(20);
    DECLARE Adresszusatz VARCHAR(60);
    DECLARE GebDat DATE;
    DECLARE Status SMALLINT;
    DECLARE v_DAK_Id VARCHAR(18);
    DECLARE v_KVNR_aufbereitet VARCHAR(16);
    DECLARE v_KVNR VARCHAR(30);
    
            SELECT "KVNR_Neu", "Titel", "Zusatzwort", "LKZ", "Adresszusatz", "GebDat" 
            INTO KVNr_Neu, Titel, Zusatzwort, LKZ, Adresszusatz, GebDat 
            FROM "NatPersPool" WHERE "KVNR"= @Kvnr FETCH FIRST 1 ROWS ONLY;
    
    IF @Id IS NULL
    THEN
            SET v_DAK_Id = Right('00000000000000' || @Buchungnr, 18);
            SET v_KVNR_aufbereitet = SUBSTRING(@KVNR, 1, 1, CODEUNITS32) || ' ' || SUBSTRING(@KVNR, 2, 3, CODEUNITS32) || ' ' || SUBSTRING(@KVNR, 5, 3, CODEUNITS32) || ' ' || SUBSTRING(@KVNR, 8, 3, CODEUNITS32);
    
            INSERT INTO "ADDON21C"."BONUSPRAEMIENVERSAND"
            ("DAK_ID", "KVNR", "EKVNR", "ID_Praemie", "Geschlecht", "Titel", "Vorname", "Nachname",
            "Vorsatzwort_Name", "Namenszusatz", "Timestamp_Erstellung", "Strasse", "Hausnummer", "Laenderkennzeichen", "PLZ", "Ort",
            "Zusatzanschrift", "Geburtsdatum", "Druckaufbereitete_KVNR", "Versandstatus")
            VALUES(v_DAK_Id, @KVNR, KVNR_Neu, @PraemieExtId, @Geschlecht, Titel, @Vorname, @Name,
            @Namensvorsatz , Zusatzwort, @Timestamp, @Strasse, @Hausnummer, LKZ, @Plz, @Ort, 
            Adresszusatz, GebDat, v_KVNR_aufbereitet, @Versandstatus);
            
            SET @result = IDENTITY_VAL_LOCAL();--
            
    ELSE
            SET v_DAK_Id = Right('00000000000000' || @Buchungnr, 18);
            SET v_KVNR = (SELECT KVNR FROM "ADDON21C"."BONUSPRAEMIENVERSAND" WHERE DAK_ID = v_DAK_Id FETCH FIRST 1 ROWS ONLY);
            
            UPDATE "ADDON21C"."BONUSPRAEMIENVERSAND"
            SET 
            "Timestamp_Export" = @Timestamp,
            "Versandstatus" = @Versandstatus,
            "Paketnummer" = @Paketnummer,
            "Auslieferer" = @Auslieferer,
            "Gutscheincode" = @Gutscheincode,
            "Lieferverzugsdatum" = @Lieferverzugsdatum
            WHERE KVNR = v_KVNR;
    
    END IF;
    END

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    IDENTITY_VAL_LOCAL() returns null
    How did you know that?

    IF @Id IS NOT NULL THEN ???
    Last edited by tonkuma; 08-20-12 at 09:07.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What is the CREATE TABLE statement of "ADDON21C"."BONUSPRAEMIENVERSAND"?

  4. #4
    Join Date
    Aug 2012
    Posts
    6
    Quote Originally Posted by tonkuma View Post
    How did you know that?

    IF @Id IS NOT NULL THEN ???
    I call this statement out of a vb.net application... Also, just to make sure: I'm talking about the insert-part not about the update part.

    Code:
    CREATE TABLE
        BONUSPRAEMIENVERSAND
        (
            Id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,        DAK_ID VARCHAR(30) NOT NULL,
            KVNR VARCHAR(30) NOT NULL,
            EKVNR VARCHAR(30) NOT NULL,
            ID_Praemie VARCHAR(30) NOT NULL,
            Geschlecht CHARACTER(1) NOT NULL,
            Titel VARCHAR(30),
            Vorname VARCHAR(60) NOT NULL,
            Nachname VARCHAR(60) NOT NULL,
            Vorsatzwort_Name VARCHAR(30),
            Namenszusatz VARCHAR(30),
            Sonderanrede VARCHAR(30),
            Strasse VARCHAR(75) NOT NULL,
            Hausnummer VARCHAR(15) NOT NULL,
            Laenderkennzeichen VARCHAR(30) NOT NULL,
            PLZ VARCHAR(10) NOT NULL,
            Ort VARCHAR(60) NOT NULL,
            Ortszusatz VARCHAR(70),
            Zusatzanschrift VARCHAR(70),
            Geburtsdatum DATE NOT NULL,
            Druckaufbereitete_KVNR VARCHAR(40) NOT NULL,
            Versandstatus SMALLINT NOT NULL,
            Stornogrund SMALLINT,
            Paketnummer BIGINT,
            Auslieferer VARCHAR(70),
            Gutscheincode VARCHAR(30),
            Timestamp_Erstellung TIMESTAMP NOT NULL,
            Timestamp_Export TIMESTAMP,
            Lieferverzugsdatum DATE,
            PRIMARY KEY (Id)

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want to ask again...
    Quote Originally Posted by tonkuma View Post
    IDENTITY_VAL_LOCAL() returns null
    How did you know that?
    How to call the procedure?
    (e.g. copy and paste the call statement)

    How did you checked OUT @result after calling the procedure?
    (I hope you to copy your actual code. not by narrative descriptions)
    Last edited by tonkuma; 08-20-12 at 09:16.

  6. #6
    Join Date
    Aug 2012
    Posts
    6
    I'm using my own framework to run stored procedures and things like that. Doesn't make much sense to copy/paste x functions/subs especially if I know that they do work.

    Anyways ... this is the main part:

    Code:
    Public Shared Function RunInsertStoredProcedure(ByVal spName As String, _
                                                                ByVal spParameter As HashSet(Of System.Data.Common.DbParameter), _
                                                                ByVal ConnectionString As String,
                                                                Optional ByVal CommandTimeOut As Integer? = Nothing) As Long
                Dim conn As DB2Connection = GetDB2Connection(ConnectionString)
                Dim returnValue As Long = Nothing
    
                Try
                    Dim comm = New DB2Command(spName, conn)
                    If Not IsNothing(CommandTimeOut) Then
                        comm.CommandTimeout = CInt(CommandTimeOut)
                    End If
                    comm.CommandType = CommandType.StoredProcedure
    
                    If Not IsNothing(spParameter) Then
                        For Each para In spParameter
                            comm.Parameters.Add(para)
                        Next
                    End If
    
                    Dim return_para As New DB2Parameter("@result", DB2Type.BigInt)
                    return_para.Direction = ParameterDirection.Output
    
                    comm.Parameters.Add(return_para)
    
                    comm.ExecuteNonQuery()
    
                    returnValue = CType(return_para.Value, Long)
                Catch
                    Throw
                Finally
                    conn.Close()
                End Try
    
                Return returnValue
            End Function
    Nothing special here .. The stored procedure must be the problem.
    Last edited by Brometheus; 08-20-12 at 11:15.

  7. #7
    Join Date
    Aug 2012
    Posts
    6
    So I tried to split up parts of the Stored procedure. Now it looks like this:

    Code:
    CREATE OR REPLACE PROCEDURE "ADDON21C"."STP_BONUSPRAEMIE_ADD"
    (
            IN @Id BIGINT,
            IN @Buchungnr VARCHAR(70),
            IN @Name Varchar(50),
            IN @Vorname Varchar(50),
            IN @Namensvorsatz Varchar(50),
            IN @Geschlecht VARCHAR(5),
            IN @Strasse Varchar(30),
            IN @Hausnummer VARCHAR(15),
            IN @Plz VARCHAR(10),
            IN @Ort Varchar(60),
            IN @Kvnr VARCHAR(50),
            IN @PraemieExtId VARCHAR(10),
            In @Timestamp Timestamp,
            IN @Versandstatus SMALLINT,
            IN @Stornogrund SMALLINT,
            IN @Paketnummer VARCHAR(50),
            IN @Auslieferer VARCHAR(70),
            IN @Gutscheincode VARCHAR(50),
            IN @Lieferverzugsdatum VARCHAR(30),
            IN @KvnrAufbereitet VARCHAR(50),
            IN @KvnrNeu VARCHAR(30),
            IN @Titel VARCHAR(30),
            In @Zusatzwort VARCHAR(70),
            IN @LKZ VARCHAR(20),
            IN @Adresszusatz VARCHAR(80),
            IN @GebDat VARCHAR(30),
            OUT @result BIGINT
    )
    LANGUAGE SQL
    SPECIFIC SQL12080109131611
    BEGIN
    DECLARE Status SMALLINT;
    DECLARE v_KVNR VARCHAR(30);
    
    IF @Id IS NULL
    THEN
            INSERT INTO "ADDON21C"."BONUSPRAEMIENVERSAND"
            ("DAK_ID", "KVNR", "EKVNR", "ID_Praemie", "Geschlecht", "Titel", "Vorname", "Nachname",
            "Vorsatzwort_Name", "Namenszusatz", "Timestamp_Erstellung", "Strasse", "Hausnummer", "Laenderkennzeichen", "PLZ", "Ort",
            "Zusatzanschrift", "Geburtsdatum", "Druckaufbereitete_KVNR", "Versandstatus")
            VALUES(@Buchungnr, @KVNR, @KvnrNeu, @PraemieExtId, @Geschlecht, @Titel, @Vorname, @Name,
            @Namensvorsatz, @Zusatzwort, @Timestamp, @Strasse, @Hausnummer, @LKZ, @Plz, @Ort, 
            @Adresszusatz, @GebDat, @KvnrAufbereitet, @Versandstatus);
            
            SET @result = IDENTITY_VAL_LOCAL();--
            
    ELSE
            SET v_KVNR = (SELECT KVNR FROM "ADDON21C"."BONUSPRAEMIENVERSAND" WHERE DAK_ID = @Buchungnr FETCH FIRST 1 ROWS ONLY);
            
            UPDATE "ADDON21C"."BONUSPRAEMIENVERSAND"
            SET 
            "Timestamp_Export" = @Timestamp,
            "Versandstatus" = @Versandstatus,
            "Paketnummer" = @Paketnummer,
            "Auslieferer" = @Auslieferer,
            "Gutscheincode" = @Gutscheincode
            "Lieferverzugsdatum" = @Lieferverzugsdatum
            WHERE KVNR = v_KVNR;
            
            SET @result = IDENTITY_VAL_LOCAL();
    END IF;
    END
    ... and I still get the same error for whatever reason. (Ungültige Konvertierung von Typ DBNull in Typ Long. - Invalid conversion from type DBNull to type Long)

    The db2 documentation is hardly helpful as the "IDENTITY_VAL_LOCAL()"- part is completely in english and with all these technical terms hard for me to understand. I try to get what the article says, but it's impossible for me to comprehend...

    I still don't get why exactly

  8. #8
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Please try this code
    Code:
            SET @result = (select x.id as result from final table 
                            (
                             INSERT INTO "ADDON21C"."BONUSPRAEMIENVERSAND"
                               ("DAK_ID",
                                "KVNR",
                                "EKVNR",
                                "ID_Praemie",
                                "Geschlecht",
                                "Titel",
                                "Vorname",
                                "Nachname",
                                "Vorsatzwort_Name",
                                "Namenszusatz",
    
                                "Timestamp_Erstellung",
                                "Strasse",
                                "Hausnummer",
                                "Laenderkennzeichen",
                                "PLZ",
                                "Ort",
                                "Zusatzanschrift",
                                "Geburtsdatum",
                                "Druckaufbereitete_KVNR",
                                "Versandstatus"
                               )
                             VALUES
                               ( v_DAK_Id,
                                 @KVNR,
                                 KVNR_Neu,
                                 @PraemieExtId,
                                 @Geschlecht,
                                 Titel,
                                 @Vorname,
                                 @Name,
                                 @Namensvorsatz ,
                                 Zusatzwort,
                                 @Timestamp, 
                                 @Strasse,
                                 @Hausnummer, 
                                 LKZ,
                                 @Plz,
                                 @Ort, 
                                 Adresszusatz,
                                 GebDat, 
                                 v_KVNR_aufbereitet,
                                 @Versandstatus
                               )
                            )  x
                          ) ;
    Should not make any difference but it saves you a database-call. The only thing to explain the NULL value is the INSERT statement going into error. Did you check if the new row actually gets inserted in the database?
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  9. #9
    Join Date
    Aug 2012
    Posts
    6
    Same result and yes it does add a row in the data table. I even set all columns to NULL to be sure that nothing else is causing this, but it doesn't work.

    I mean ... I don't even really need the Id back as an result. It doesn't matter much, but I need the vb.net class as it is for other procedures...
    So I tried to set the Id with a random number after the Insert statement and I still get the same error. Now I'm really confused ..

  10. #10
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Try to call the proc outside of the application to check if it is the stored-procedure works o.k. or not. If the procedure works o.k., you're on the wrong forum
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  11. #11
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Please post also the DB2-server *version* and *fixpack* and the operating system on which your DB2-server is running.

    The IDENTITYL_VAL_LOCAL() works correctly for me in my SQL-PL procedures (db2 v9.7 and db2 v10.1) on Linux and Windows immediately after a successful INSERT...VALUES statement.

    But maybe the lack of any error handling in your procedure is the clue. You should always have exception handlers in your stored procedures to catch and log all exceptions , otherwise a world of pain awaits you or anyone who has to maintain the code.


    As suggested by dr_te_z, you should (as a test only) call the procedure from the command-line (for example, on Windows from the db2cmd.exe /db2cwadmin.bat shell, or on Linux/Unix from the bash/ksh command line). You are more likely to see what's happening. You can also debug the procedure (with Data Studio 3.1.1 debugger), or you can add DBMS_OUTPUT.put_line(...) statements to verify your assumptions.

  12. #12
    Join Date
    Aug 2012
    Posts
    6
    Ok, I solved the problem somehow. I set Id to 0 before I use it as an IN parameter for the stored procedure and use that as a condition for my if-statement.

    Thanks for your help, guys.

    Also .. there is exception handling in DB2? Can you give me a link or something? I'm so very interested.

  13. #13
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    IBM gives sample SQL PL procedures with the DB2-server installation.
    Just look in your SQLLIB SAMPLES SQLPL directory..they show a couple of simple exception handlers (far more complex ones can be written)
    The samples are also available in the Infocenter for whatever DB2 version you are using .
    Study those samples, alongside the documentation in the Infocenter...

  14. #14
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Brometheus View Post
    Can you give me a link or something?
    Main Page - SQL PL Guide for DB2
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

Posting Permissions

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