ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL Works Interactively, not in RPG.

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL Works Interactively, not in RPG.

    I am trying to run the simplest of SQL Queries.

    INSERT INTO DRTEST (DRSCNT, DRDATE, DRTIME, DRDATETIME,
    DRDESC) VALUES(3, CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP,
    'Test 1')

    It works fine from a STRSQL screen, or in a Navigator Window.

    I copy paste the exact code into RPG

    But if I run it from a RPG SQL Program nothing happens, but no error either.

    C/EXEC SQL
    C+ INSERT INTO DRTEST (DRSCNT, DRDATE, DRTIME, DRDATETIME,
    C+ DRDESC) VALUES(3, CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP,
    C+ 'Test 1')
    C/END-EXEC

    Am I missing something silly ??

  • #2
    When you say "No error" what is the value of SQLSTATE/SQLCOD after the operation? That should tell you what is wrong.

    Comment


    • #3
      I guess I do have an error. SQLCOD 7008, SQLSTATE 55019

      Looking up 7008, IBM Docs say:

      ==============
      10 A CREATE VIEW statement was issued, but the view cannot be created because a view cannot be defined on another view for which an INSTEAD OF trigger exists.

      17 A CREATE TRIGGER statement was issued, but the trigger cannot be created because an INSTEAD OF trigger cannot be defined on a view that has other views dependent on it, or the view references an accelerator-only table, or an AFTER trigger cannot be defined on a table that has a clone defined.

      18 A CREATE INDEX or ALTER INDEX statement was issued, but the index cannot be created at this time because values have not yet been materialized for the row change timestamp column for some of the rows in the table. Issue REORG on the table space containing the table and then try to create the index.

      19 A MERGE statement was issued, but the target of a MERGE statement cannot be a view that has an instead of trigger defined for it.

      20 No index expression-based index can be created after the inline length is altered until the base table is reorganized. System action

      ==============
      Last edited by MFisher; March 11, 2020, 03:50 PM.

      Comment


      • #4
        DDS:

        Code:
        A R DRFMT1
        *
        A DRSCNT    6S 0      COLHDG('Sequence Counter')
        A DRDATE    L         COLHDG('Entry Date')
        A DRTIME    T       COLHDG('Entry Time')
        A DRDATETIME   Z      COLHDG('Entry DateTime')
        A DRDESC    50A     COLHDG('Description')
        Last edited by MFisher; March 11, 2020, 03:55 PM.

        Comment


        • #5
          I'm looking up SQLSTATE 55019, and I see this:
          The tables in this topic provide descriptions of SQLSTATE codes that can be returned to applications by Db2 for i. The tables include SQLSTATE values, their meanings, and their corresponding SQLCODE values.


          likewise, SQLCODE -7008 shows this:
          These tables list SQL messages. Use these tables to find message text, cause text, recovery text, and corresponding SQLCODEs and SQLSTATEs.


          Both seem to be saying approximately the same thing, the object isn't in the appropriate state for what you're doing. Why is the message information you posted, above, so dramatically different from my lookup of these codes? Are you sure you're looking in the right place?

          Also, there should be a message in the job log (unless you've turned that off) that has more details about what happened. Is it really not there? What is your log level set to?

          Comment


          • #6
            might just be missing this at the top of the program
            PHP Code:
                         Exec Sql Set Option --Naming    = *Sys,                                                
                                               
            Commit    = *None,                                              
                                               
            SRTSEQ    = *LANGIDUNQ
            All my answers were extracted from the "Big Dummy's Guide to the As400"
            and I take no responsibility for any of them.

            www.code400.com

            Comment


            • #7
              Yes!! … Thanks !

              Originally posted by jamief View Post
              might just be missing this at the top of the program
              PHP Code:
               exec sql set option --naming = *sys,
              commit = *none,
              srtseq = *langidunq

              Comment


              • #8
                Originally posted by Scott Klement View Post
                I'm looking up SQLSTATE 55019, and I see this:
                The tables in this topic provide descriptions of SQLSTATE codes that can be returned to applications by Db2 for i. The tables include SQLSTATE values, their meanings, and their corresponding SQLCODE values.


                likewise, SQLCODE -7008 shows this:
                These tables list SQL messages. Use these tables to find message text, cause text, recovery text, and corresponding SQLCODEs and SQLSTATEs.


                Both seem to be saying approximately the same thing, the object isn't in the appropriate state for what you're doing. Why is the message information you posted, above, so dramatically different from my lookup of these codes? Are you sure you're looking in the right place?

                Also, there should be a message in the job log (unless you've turned that off) that has more details about what happened. Is it really not there? What is your log level set to?
                I was looking at this link.


                Comment


                • #9
                  That link is for IBM z/OS, not IBM i. The equivalent for IBM i 7.4 is: https://www.ibm.com/support/knowledg...sages__SQL7008

                  Comment


                  • #10
                    The problem occurs, because you are compiling your program with the option COMMIT=*CHG (Default), but your table is not registred within a journal and in this way you cannot insert, update or delete rows under commitment control.
                    You either have to change the COMMIT option to *NONE in the compile command or add an SET OPTION statement at the beginning of your C-Specs, which includes the COMMIT option (as Jamie mentioned). The SET OPTION Statement overrides Options in the compile command.
                    ... but I'd change the SET OPTION statement as follows:
                    Code:
                    Exec SQL Set Option COMMIT=*NONE, DATFMT=*ISO, CLOSQLCSR=*ENDACTGRP;
                    The date forma is very important when dealing with date fields, because the SQL precompiler generates workfields for each hostvariable you use. The date format for the date work fields is taken from the compile command (and not from the D- or H-Specs). The default for the date format in the compile command is *JOB, and mostly the JOB-Format has only a 2-digit year. If your Host-Variable is defined with an 4-digit year format (*ISO, *EUR, *USA), your program will crash as soon as the Workfield is inititalized with a date before 1940 or after 2039), for example with the default/Lowval of a 4-digit year date (0001-01-01).
                    I'd also not change the SortSequence per default.
                    Use also the CLOSQLCSR=*ENDACTGRP (Default) - Open datapath are kept open, so subsequent calls are faster.

                    If you want to retrieve the SQL Messages, you can have a look at the QSQLMSGF Message File (WRKMSGF). The message id is SQL + the absolute value of the SQLCODE, so for -7008 it is SQL7008.
                    You can also use the following SQL Statement to get the Message Text for SQLCODE -7008:
                    Code:
                    Select Message_ID, Message_Text, Message_SEcond_Level_Text
                       from Message_File_Data
                       Where Message_File = 'QSQLMSG'
                             and Message_File_Library = 'QSYS'
                             and Message_Id = 'SQL7008';
                    Birgitta

                    Comment


                    • #11
                      B.Hauser,
                      Thank you very much for the help !!

                      I don't seem to have MSGF
                      QSQLMSGF

                      If I type

                      WRKOBJ OBJ(*ALL/QSQLMSGF)
                      I get no results.

                      We're on V7 R2
                      Last edited by MFisher; March 13, 2020, 07:17 AM.

                      Comment


                      • Vectorspace
                        Vectorspace commented
                        Editing a comment
                        The message file name is QSQLMSG (no F on the end)

                    • #12
                      Originally posted by MFisher View Post
                      I don't seem to have MSGF [LEFT][COLOR=#333333][FONT=inherit]QSQLMSGF
                      Sorry, type: QSQLMSG

                      Comment

                      Working...
                      X