C/C++ SQL CLI UPDATE problem - IBM AS400

This is a discussion on C/C++ SQL CLI UPDATE problem - IBM AS400 ; No matter what I do to try and tweak this code, it's always failing on me. If I enter the statement directly into STRSQL it works fine. The error I'm getting back from SQLExecDirect() is : sqlRet=-1 NativeError=-7008 SqlState=55019 Message=DFPRAUTP ...

+ Reply to Thread
Results 1 to 7 of 7

Thread: C/C++ SQL CLI UPDATE problem

  1. C/C++ SQL CLI UPDATE problem

    No matter what I do to try and tweak this code, it's always failing on
    me. If I enter the statement directly into STRSQL it works fine. The
    error I'm getting back from SQLExecDirect() is :

    sqlRet=-1 NativeError=-7008 SqlState=55019 Message=DFPRAUTP in
    ICCTEST01P not valid for operation.

    The SqlState returned by SQLGetDiagRec() isn't listed on
    http://publib.boulder.ibm.com/infoce...d/r0000588.htm

    The string that is being built by my routine works out to this:
    UPDATE ICCTEST01P.DFPRAUTP SET PASTATION = 'TN0001' WHERE PASCODE =
    99123 AND PAPREAUTH = 654321

    I can post more information including code if someone can point me in
    the right direction. As always, thanks in advance.

    Kelly Beard

  2. Re: C/C++ SQL CLI UPDATE problem

    On Aug 12, 5:16*pm, "Mr. K.V.B.L." wrote:
    > No matter what I do to try and tweak this code, it's always failing on
    > me. *If I enter the statement directly into STRSQL it works fine. *The
    > error I'm getting back from SQLExecDirect() is :
    >
    > sqlRet=-1 NativeError=-7008 SqlState=55019 Message=DFPRAUTP in
    > ICCTEST01P not valid for operation.
    >
    > The SqlState returned by SQLGetDiagRec() isn't listed onhttp://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/c...
    >
    > The string that is being built by my routine works out to this:
    > UPDATE ICCTEST01P.DFPRAUTP SET PASTATION = 'TN0001' WHERE PASCODE =
    > 99123 AND PAPREAUTH = 654321
    >
    > I can post more information including code if someone can point me in
    > the right direction. *As always, thanks in advance.
    >
    > Kelly Beard


    My initial searches on this problem led to more 'greek' than useful
    information until I found this:

    http://forums.systeminetwork.com/isn...p?t-38771.html

    So I guess I need to turn on journaling for the DFPRAUTP file? I dont
    know why a straight STRSQL statement would work and not
    SQLExecDirect() though.

  3. Re: C/C++ SQL CLI UPDATE problem

    On Aug 12, 5:32*pm, "Mr. K.V.B.L." wrote:
    > On Aug 12, 5:16*pm, "Mr. K.V.B.L." wrote:
    >
    >
    >
    > > No matter what I do to try and tweak this code, it's always failing on
    > > me. *If I enter the statement directly into STRSQL it works fine. *The
    > > error I'm getting back from SQLExecDirect() is :

    >
    > > sqlRet=-1 NativeError=-7008 SqlState=55019 Message=DFPRAUTP in
    > > ICCTEST01P not valid for operation.

    >
    > > The SqlState returned by SQLGetDiagRec() isn't listed onhttp://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/c...

    >
    > > The string that is being built by my routine works out to this:
    > > UPDATE ICCTEST01P.DFPRAUTP SET PASTATION = 'TN0001' WHERE PASCODE =
    > > 99123 AND PAPREAUTH = 654321

    >
    > > I can post more information including code if someone can point me in
    > > the right direction. *As always, thanks in advance.

    >
    > > Kelly Beard

    >
    > My initial searches on this problem led to more 'greek' than useful
    > information until I found this:
    >
    > http://forums.systeminetwork.com/isn...dex.php?t-3877...
    >
    > So I guess I need to turn on journaling for the DFPRAUTP file? *I dont
    > know why a straight STRSQL statement would work and not
    > SQLExecDirect() though.


    I'm learning some about this, but I thought I would just add that I'm
    using CRTCPPMOD & CRTPGM to build my PGM object. An RPG guy here
    showed me some create options for a CRTSQLRPGI command but I don't see
    those options just yet in CRTCPPMOD or CRTPGM.

  4. Re: C/C++ SQL CLI UPDATE problem

    Mr. K.V.B.L. wrote:
    > On Aug 12, 5:32 pm, "Mr. K.V.B.L." wrote:
    >> On Aug 12, 5:16 pm, "Mr. K.V.B.L." wrote:
    >>
    >>
    >>
    >>> No matter what I do to try and tweak this code, it's always failing on
    >>> me. If I enter the statement directly into STRSQL it works fine. The
    >>> error I'm getting back from SQLExecDirect() is :
    >>> sqlRet=-1 NativeError=-7008 SqlState=55019 Message=DFPRAUTP in
    >>> ICCTEST01P not valid for operation.
    >>> The SqlState returned by SQLGetDiagRec() isn't listed onhttp://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/c...
    >>> The string that is being built by my routine works out to this:
    >>> UPDATE ICCTEST01P.DFPRAUTP SET PASTATION = 'TN0001' WHERE PASCODE =
    >>> 99123 AND PAPREAUTH = 654321
    >>> I can post more information including code if someone can point me in
    >>> the right direction. As always, thanks in advance.
    >>> Kelly Beard

    >> My initial searches on this problem led to more 'greek' than useful
    >> information until I found this:
    >>
    >> http://forums.systeminetwork.com/isn...dex.php?t-3877...
    >>
    >> So I guess I need to turn on journaling for the DFPRAUTP file? I dont
    >> know why a straight STRSQL statement would work and not
    >> SQLExecDirect() though.

    >
    > I'm learning some about this, but I thought I would just add that I'm
    > using CRTCPPMOD & CRTPGM to build my PGM object. An RPG guy here
    > showed me some create options for a CRTSQLRPGI command but I don't see
    > those options just yet in CRTCPPMOD or CRTPGM.


    Journaling is required if commitment control is used - ie anything other
    than COMMIT(*NONE) - you can search the newsgroup for SQL7008 and
    probably find more details. Most likely when in STRSQL you were using
    *NONE - try F13->option 1 and check the value of "Commitment control" there.

    If you don't need commitment control, the connection attribute can be
    set to *NONE using SQL_ATTR_COMMIT :
    http://publib.boulder.ibm.com/infoce...adpfnsconx.htm

    If you are coding CLI then you're likely not also using embedded SQL, so
    commands like CRTSQLxxx would not apply. However most (all?) of the
    attributes can be set as above.

    It's worth noting that when using SQL to create a schema (aka
    collection), the i5/OS *LIB object that's created also contains journal
    objects, so tables created thereafter are automatically journaled. But
    some *FILE objects historically were maintained using native I/O (eg
    RPG) and did not use journaling or commitment control. And if no
    transaction support is needed with SQL, *NONE can be used to manage
    files not journaled.

    --
    Karl Hanson

  5. Re: C/C++ SQL CLI UPDATE problem

    As a temporary measure, I found that appending the text "WITH NONE" to
    the end of my UPDATE statement allowed the code to work. So...

    UPDATE ICCTEST01P.DFPRAUTP SET PASTATION = 'TN0001' WHERE PASCODE =
    99123 AND PAPREAUTH = 654321 WITH NONE

    However I'll investigate using SQLSetConnectAttr() instead. Thanks
    for all the help Karl.

    On Aug 12, 8:21*pm, Karl Hanson wrote:
    > Mr. K.V.B.L. wrote:
    > > On Aug 12, 5:32 pm, "Mr. K.V.B.L." wrote:
    > >> On Aug 12, 5:16 pm, "Mr. K.V.B.L." wrote:

    >
    > Journaling is required if commitment control is used - ie anything other
    > than COMMIT(*NONE) - you can search the newsgroup for SQL7008 and
    > probably find more details. *Most likely when in STRSQL you were using
    > *NONE - try F13->option 1 and check the value of "Commitment control" there.
    >
    > If you don't need commitment control, the connection attribute can be
    > set to *NONE using SQL_ATTR_COMMIT :http://publib.boulder.ibm.com/infoce...ndex.jsp?topic...
    >
    > If you are coding CLI then you're likely not also using embedded SQL, so
    > commands like CRTSQLxxx would not apply. However most (all?) of the
    > attributes can be set as above.
    >
    > It's worth noting that when using SQL to create a schema (aka
    > collection), the i5/OS *LIB object that's created also contains journal
    > objects, so tables created thereafter are automatically journaled. But
    > some *FILE objects historically were maintained using native I/O (eg
    > RPG) and did not use journaling or commitment control. And if no
    > transaction support is needed with SQL, *NONE can be used to manage
    > files not journaled.
    >
    > --
    > Karl Hanson



  6. Re: C/C++ SQL CLI UPDATE problem

    Mr. K.V.B.L. wrote:
    > As a temporary measure, I found that appending the text "WITH NONE" to
    > the end of my UPDATE statement allowed the code to work. So...
    >
    > UPDATE ICCTEST01P.DFPRAUTP SET PASTATION = 'TN0001' WHERE PASCODE =
    > 99123 AND PAPREAUTH = 654321 WITH NONE
    >
    > However I'll investigate using SQLSetConnectAttr() instead. Thanks
    > for all the help Karl.
    >
    > On Aug 12, 8:21 pm, Karl Hanson wrote:
    >> Mr. K.V.B.L. wrote:
    >>> On Aug 12, 5:32 pm, "Mr. K.V.B.L." wrote:
    >>>> On Aug 12, 5:16 pm, "Mr. K.V.B.L." wrote:

    >> Journaling is required if commitment control is used - ie anything other
    >> than COMMIT(*NONE) - you can search the newsgroup for SQL7008 and
    >> probably find more details. Most likely when in STRSQL you were using
    >> *NONE - try F13->option 1 and check the value of "Commitment control" there.
    >>
    >> If you don't need commitment control, the connection attribute can be
    >> set to *NONE using SQL_ATTR_COMMIT :http://publib.boulder.ibm.com/infoce...ndex.jsp?topic...
    >>
    >> If you are coding CLI then you're likely not also using embedded SQL, so
    >> commands like CRTSQLxxx would not apply. However most (all?) of the
    >> attributes can be set as above.
    >>
    >> It's worth noting that when using SQL to create a schema (aka
    >> collection), the i5/OS *LIB object that's created also contains journal
    >> objects, so tables created thereafter are automatically journaled. But
    >> some *FILE objects historically were maintained using native I/O (eg
    >> RPG) and did not use journaling or commitment control. And if no
    >> transaction support is needed with SQL, *NONE can be used to manage
    >> files not journaled.
    >>

    >


    Yes the statement isolation-clause overrides the default (in this case
    connection) isolation level.
    http://publib.boulder.ibm.com/infoce...afzmstisol.htm

    Also fyi - in the job where you test your program, if you can first get
    the job into debug mode, more detailed info may exist in job log
    messages. SQL7008 is a good example, where in this case reason code 3
    explains the journaling requirement. (SQL7008 may have been present even
    w/o being in debug mode, but more SQL "informational" messages are sent
    when in debug mode, that can help with functional or performance
    issues). Another useful tool is Database Monitor:
    http://publib.boulder.ibm.com/infoce...onexamples.htm

    ===> dspmsgd sql7008 qsqlmsg

    Message ID . . . . . . . . . : SQL7008

    Message file . . . . . . . . : QSQLMSG

    Library . . . . . . . . . : QSYS



    Message . . . . : &1 in &2 not valid for operation.

    Cause . . . . . : The reason code is &3. Reason codes are:

    1 -- &1 has no members.

    2 -- &1 has been saved with storage free.

    3 -- &1 not journaled, no authority to the journal, or the journal
    state
    ...

    --
    Karl Hanson

  7. Re: C/C++ SQL CLI UPDATE problem

    There should be a CLI connection attribute that can be specified to change the
    Commit Level to *NONE which then disables the need for journaling.

    Mr. K.V.B.L. wrote:
    > On Aug 12, 5:32 pm, "Mr. K.V.B.L." wrote:
    >> On Aug 12, 5:16 pm, "Mr. K.V.B.L." wrote:
    >>
    >>
    >>
    >>> No matter what I do to try and tweak this code, it's always failing on
    >>> me. If I enter the statement directly into STRSQL it works fine. The
    >>> error I'm getting back from SQLExecDirect() is :
    >>> sqlRet=-1 NativeError=-7008 SqlState=55019 Message=DFPRAUTP in
    >>> ICCTEST01P not valid for operation.
    >>> The SqlState returned by SQLGetDiagRec() isn't listed onhttp://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/c...
    >>> The string that is being built by my routine works out to this:
    >>> UPDATE ICCTEST01P.DFPRAUTP SET PASTATION = 'TN0001' WHERE PASCODE =
    >>> 99123 AND PAPREAUTH = 654321
    >>> I can post more information including code if someone can point me in
    >>> the right direction. As always, thanks in advance.
    >>> Kelly Beard

    >> My initial searches on this problem led to more 'greek' than useful
    >> information until I found this:
    >>
    >> http://forums.systeminetwork.com/isn...dex.php?t-3877...
    >>
    >> So I guess I need to turn on journaling for the DFPRAUTP file? I dont
    >> know why a straight STRSQL statement would work and not
    >> SQLExecDirect() though.

    >
    > I'm learning some about this, but I thought I would just add that I'm
    > using CRTCPPMOD & CRTPGM to build my PGM object. An RPG guy here
    > showed me some create options for a CRTSQLRPGI command but I don't see
    > those options just yet in CRTCPPMOD or CRTPGM.


    --
    Kent Milligan
    ISV Enablement - System i
    kmill@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
    >>> ibm.com/iseries/db2

    (opinions stated are not necessarily those of my employer)

+ Reply to Thread