How to insert data from SQL procedure

1380 pts.
Tags:
AS/400
SQL procedure
How can I insert data from SQL procedure. I am using cursor.

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • 9783444184
    CREATE PROCEDURE AMITS.UTXPROC ( ) LANGUAGE RPGLE SPECIFIC AMITS.UTXPROC NOT DETERMINISTIC MODIFIES SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'AMITS/TESTUTX' PARAMETER STYLE GENERAL ;
    1,380 pointsBadges:
    report
  • 9783444184
    d@LWCUS s 6 0 d@UPCUS s 6 0 d@LINVN s 8 0 d@UINVN s 8 0 d@LWORD s 8 0 d@UPORD s 8 0 DW8ORD S 8S 0 INZ(0) DW8ORD1 S 8 0 INZ(0) DW8INV S 8S 0 INZ(0) DW8INV1 S 8 0 INZ(0) DW8INVOLD S 8S 0 INZ(0) DW8CUST S 6S 0 INZ(0) DW8CUST1 S 6 0 INZ(0) DW8OTYP S 1 INZ(*BLANKS) DW2ROW S 2P 0 INZ(09) DW2COL S 2P 0 INZ(30) DW2CALL S 1A INZ('1') D*W2CUSTF S LIKE(XLWCUS) DT1RTPM S 1A INZ(*BLANKS) DT1DTFM S 1 0 INZ(*BLANKS) DT1DTE6 S 6 0 INZ(*BLANKS) DT1DTE8 S 8 0 INZ(*BLANKS) DT8PROG S 10A INZ(*BLANKS) DT8PNL S 3A INZ(*BLANKS) DW0OFF S 1A INZ(*BLANKS) DW0ON S 1A INZ(*BLANKS) DW8ERR S 1A INZ(*BLANKS) DW8ERRF S 1A INZ(*BLANKS) DXSPDTE S 6S 0 INZ(*ZEROS) DMSGFLG S 1A INZ('N') DTOP S 1 INZ(*BLANKS) DCOUNT1 S 1S 0 INZ(*ZEROS) DBOTTOM S 1 INZ(*BLANKS) DEND S 1 INZ(*BLANKS) DW8ORDUP S 8 0 DW8ORDDW S 8 0 DW8INVUP S 8 0 DW8INVDW S 8 0 DW8CUSUP S 6 0 DW8CUSDW S 6 0 c* *entry plist c* parm XLWCUS 6 c* parm XUPCUS 6 c* parm XLINVN 8 c* parm XUINVN 8 c* parm XLWORD 8 c* parm XUPORD 8 c* parm W8CUST 6 c* parm W8INV 8 c* parm W8ORD 8 c* eval XLWCUS=%trimr(XLWCUS) c* eval XUPCUS=%trimr(XUPCUS) c* eval XLINVN=%trimr(XLINVN) c* eval XUINVN=%trimr(XUINVN) c* eval XLWORD=%trimr(XLWORD) c* eval XUPORD=%trimr(XUPORD) c* move XLWCUS @LWCUS c* move XUPCUS @UPCUS c* move XLINVN @LINVN c* move XUINVN @UINVN c* move XLWORD @LWORD c* move XUPORD @UPORD c eval @LWCUS=35001 c eval @UPCUS=35015 c eval @LINVN=00000000 c eval @UINVN=99999999 c eval @LWORD=00000000 c eval @UPORD=99999999 c C/EXEC SQL C+ Set Option Commit=*none C/END-EXEC C/EXEC SQL C+ DELETE FROM amits/TESTPUTX C/END-EXEC c C/EXEC SQL C+ DECLARE S1 CURSOR FOR SELECT ILCUST, ILINVN, ILORD FROM SIL A,SIH B C+ WHERE A.ILCUST BETWEEN :@LWCUS AND :@UPCUS AND B.SICUST = A.ILCUST C+ AND A.ILINVN BETWEEN :@LINVN AND :@UINVN AND B.SIINVN = A.ILINVN C+ AND A.ILORD BETWEEN :@LWORD AND :@UPORD AND B.SIORD = A.ILORD AND C+ IHUTYP <> '9' AND A.ILINVN NOT IN (SELECT ITXINVN FROM UTXIE C WHERE C+ C.ITXINVN = A.ILINVN AND C.ILORD = A.ILORD AND C.ILLINE = A.ILLINE C+ AND C.UPDFLG ='Y') AND (A.ILWHS IN C+ (SELECT LWHS FROM IWM C ,ZCC D WHERE C.LWHS = A.ILWHS AND D.CCID = 'CC' C+ AND D.CCTABL = 'ITXFAC' AND D.CCCODE = C.WMFAC)) C+ ORDER BY A.ILINVN C/END-EXEC C* C/EXEC SQL C+ OPEN S1 C/END-EXEC C* C/EXEC SQL C+ FETCH S1 INTO :W8CUST, :W8INV, :W8ORD C/END-EXEC C dow SQLCOD = 0 C/EXEC SQL C+ insert INTO amits/TESTPUTX (Y8CUST,Y8INV,Y8ORD) c+ values(:W8CUST,:W8INV,:W8ORD) C/END-EXEC C/EXEC SQL C+ FETCH S1 INTO :W8CUST, :W8INV, :W8ORD C/END-EXEC C ENDDO C/EXEC SQL C+ CLOSE S1 C/END-EXEC c eval *inlr=*on
    1,380 pointsBadges:
    report
  • TomLiotta
    how can i insert data from sql procedure in proceduer   You insert rows with an INSERT statement.   i am using cursor.   So? What does a cursor have to do with an INSERT?   A common rule of thumb is "If you're using a cursor, you're probably doing it wrong." The meaning is that most cursors (by users who have trouble) are unnecessary. SQL usually provides set operations that can replace unneeded cursors. It's nice to know that you're using a cursor, but it doesn't help at all without knowing why it's needed and how it's used.   Tom
    125,585 pointsBadges:
    report
  • 9783444184
    okwhen i run this proc in strsql it wring fine but when i call this proc in .net environment its not working
    1,380 pointsBadges:
    report
  • TomLiotta
    when i call this proc in .net environment its not working It doesn't help to say it's "not working". Why doesn't it work? What happens? Is there an error? How do you know it's "not working"? How do you call it? How do you know it works in STRSQL?   I can't see how it could work. It does look like it could compile and run okay. But the way I read it, it should always produce zero rows.  
     AND A.ILINVN NOT IN (SELECT ITXINVN FROM UTXIE C     WHERE C.ITXINVN = A.ILINVN AND C.ILORD = A.ILORD AND C.ILLINE = A.ILLINE       AND C.UPDFLG =’Y')
      To me, that looks like it always excludes every row. You have a sub-SELECT WHERE every row matches against A.ILINVN. But the outer condition says to only process rows from SIL A WHERE ILINVN is not in the sub-SELECT.   Overall, it looks like the whole procedure doesn't need the RPG at all. All the procedure needs is the DELETE and an INSERT ... SELECT.   Also, a minor change on your cursor SELECT would be to remove the ORDER BY. It doesn't seem to have any purpose.   Tom
    125,585 pointsBadges:
    report
  • 9783444184
    hi Tom,i have solved my problem actually when i debug my program i found in .net environment has another session so that my cursor is not open because utxie table in my library but that session not taken my library...
    1,380 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following