How to code SQL embebed in RPGIII?

[uidamt98]

Re: How to code SQL embebed in RPGIII?

Thanks for support quickly!:-)
- When I use command "GO LICPGM" and take option 10. I look that SQL is installed.
The results as below:

LICENSED INSTALLED
PROGRAM STATUS DESCRIPTION
5722SS1 *COMPATIBLE OS/400 - LIBRARY QGPL
5722SS1 *COMPATIBLE OS/400 - LIBRARY QUSRSYS
5722SS1 *COMPATIBLE OPERATING SYSTEM/400
5722SS1 *COMPATIBLE OS/400 - EXTENDED BASE SUPPORT
5722SS1 *COMPATIBLE OS/400 - ONLINE INFORMATION
5722SS1 *COMPATIBLE OS/400 - EXTENDED BASE DIRECTORY SUPPORT
5722SS1 *COMPATIBLE OS/400 - SYSTEM/36 ENVIRONMENT
5722SS1 *COMPATIBLE OS/400 - SYSTEM/38 ENVIRONMENT
5722SS1 *COMPATIBLE OS/400 - EXAMPLE TOOLS LIBRARY
5722SS1 *COMPATIBLE OS/400 - AFP COMPATIBILITY FONTS
5722SS1 *COMPATIBLE OS/400 - *PRV CL COMPILER SUPPORT
5722SS1 *COMPATIBLE OS/400 - HOST SERVERS
5722SS1 *COMPATIBLE OS/400 - SYSTEM OPENNESS INCLUDES
5722SS1 *COMPATIBLE OS/400 - MEDIA AND STORAGE EXTENSIONS
5722SS1 *COMPATIBLE OS/400 - QSHELL
5722SS1 *COMPATIBLE OS/400 - DOMAIN NAME SYSTEM
5722SS1 *COMPATIBLE OS/400 - PORTABLE APP SOLUTIONS ENVIRONMENT
5722SS1 *COMPATIBLE OS/400 - DIGITAL CERTIFICATE MANAGER
5722AC3 *INSTALLED CRYPTO ACCESS PROVIDER 128-BIT
5722AP1 *COMPATIBLE ADVANCED DBCS PRINTER SUPPORT
5733A19 *INSTALLED 5733-A19 IBM TOOLBOX FOR AS/400 VERSION 4
5722BR1 *COMPATIBLE BACKUP RECOVERY AND MEDIA SERVICES
5722CE3 *INSTALLED CLIENT ENCRYPTION 128-BIT
5722DB1 *INSTALLED AS/400 SYSTEM/38 UTILITIES
5722DG1 *COMPATIBLE IBM HTTP SERVER
5722DG1 *COMPATIBLE TRIGGERED CACHE MANAGER
5722JC1 *COMPATIBLE TOOLBOX FOR JAVA
5722JV1 *COMPATIBLE DEVELOPER KIT FOR JAVA
5722JV1 *COMPATIBLE JAVA DEVELOPER KIT 1.3
5722PT1 *COMPATIBLE PERFORMANCE TOOLS
5722PT1 *COMPATIBLE PERFORMANCE TOOLS - MANAGER FEATURE
5722PT1 *COMPATIBLE PERFORMANCE TOOLS - AGENT FEATURE
5722QU1 *COMPATIBLE QUERY
5722ST1 *COMPATIBLE DB2 QUERY MGR AND SQL DEVKIT
5722TC1 *COMPATIBLE TCP/IP UTILITIES
5722WDS *COMPATIBLE WEBSPHERE DEVELOPMENT STUDIO FOR ISERIES
5722WDS *COMPATIBLE ISERIES TOOLS - APPLICATION DEVELOPMENT
5722WDS *COMPATIBLE ILE RPG
5722WDS *COMPATIBLE SYSTEM/36 COMPATIBLE RPG II
5722WDS *COMPATIBLE SYSTEM/38 COMPATIBLE RPG III
5722WDS *COMPATIBLE RPG/400
5722WDS *COMPATIBLE ILE RPG *PRV COMPILER
5722WDS *COMPATIBLE ILE COBOL
5722WDS *COMPATIBLE SYSTEM/36 COMPATIBLE COBOL
5722WDS *COMPATIBLE SYSTEM/38 COMPATIBLE COBOL
5722WDS *COMPATIBLE OPM COBOL
5722WDS *COMPATIBLE ILE COBOL *PRV COMPILER
5722WDS *COMPATIBLE ILE C
5722WDS *COMPATIBLE ILE C++
5722WDS *COMPATIBLE IXLC FOR C/C++
5722WDS *COMPATIBLE WEBSPHERE DEV STUDIO CLIENT - SERVER SUPPORT
5722XE1 *COMPATIBLE ISERIES ACCESS FOR WINDOWS
5722XW1 *COMPATIBLE ISERIES ACCESS FAMILY
5722XW1 *COMPATIBLE ISERIES ACCESS ENABLEMENT SUPPORT
1BPCS82 *INSTALLED BPCS V8.2.00 (c) SSA Global Technologies 2003
1BPCS82 *INSTALLED BPCS V8.2.00 (c) SSA Global Technologies 2003

But I do not know "use iseries navigator or client access to export it in proper format" as your description. Please help me use what tool to export it! I do not have experience!

- I changed type from SQLRPG -> RPGILE and compile with CRTRPGPGM and the error still happend as my description above:
M E S S A G E S U M M A R Y
* QRG5006 SEVERITY: 10 NUMBER: 6
MESSAGE . . . . : NOT ENTRY NOT N OR BLANK.
* QRG5007 SEVERITY: 10 NUMBER: 7
MESSAGE . . . . : CONDITIONING-INDICATOR ENTRY INVALID.
* QRG5008 SEVERITY: 30 NUMBER: 1
MESSAGE . . . . : THE FACTOR 1 FIELD-NAME ENTRY IS NOT VALID.
* QRG5015 SEVERITY: 30 NUMBER: 3
MESSAGE . . . . : OPERATION ENTRY BLANK. SPECIFICATION IGNORED.
* QRG5175 SEVERITY: 20 NUMBER: 3
MESSAGE . . . . : THE CONTROL-LEVEL ENTRY IS INVALID FOR A
SUBROUTINE-CALCULATION SPECIFICATION.
* QRG7031 SEVERITY: 00 NUMBER: 17
MESSAGE . . . . : THE NAME OR INDICATOR IS NOT REFERENCED.
* QRG7086 SEVERITY: 00 NUMBER: 2
MESSAGE . . . . : RPG HANDLES BLOCKING FUNCTION FOR FILE. INFDS
.....


- I compiled my embebed program by using CRTRPGPGM, as arrow483 description. I try to use "CRTRPGSQL" command, but system show error:
COMMAND CRTRPGSQL IN LIBRARY *LIBL NOT FOUND.
I seem there is not this command in my AS400 system.

Thanks for all. What libraries, or tools do I need?

[uidamt98]

Re: How to code SQL embebed in RPGIII?

By the way, I send by code to all RPG experts for easy reference.
I have only one subrountine use SQL:

C CLEAR BEGSR
C/EXEC SQL
C+ DELETE FROM ACO002
C/END-EXEC
C ENDSR

Thanks for great supporting!:-)

[jamief]

Re: How to code SQL embebed in RPGIII?

Okay first off leave the type alone......It must be SQLRPG to compile with SQL embedded.


That is a fact we all know....

We also know you have SQL installed on your box

The command to create SQLRPG is CRTSQLRPG

Try changing the type back and using the command CRTSQLRPG

Then let us know how successful you are


Jamie

[uidamt98]

Re: How to code SQL embebed in RPGIII?

Oh, wow....
When I compile with CRTSQLRPG command and my program type SQLRPG. The compiler do not show any error.
The result is very excellent!!!

I already used SQL embed in RPGIII!!!!:-)))
Thanks, thanks everyone.

[uidamt98]

Re: How to code SQL embebed in RPGIII?

I still have problem when test result.
I write this code to clear data in ACo002: (My program is very short and easy!!!)

************************************************** *************
H Y/
************************************************** *************
C/EXEC SQL
C+ DELETE FROM PNEVJUN/ACO002
C/END-EXEC
C SETON LR
C RETRN

I compiled and call this program ok. But when I query ACo002 file. There are not records in this file clear!
Do I code SQL above enough function statement? Please explain about SQL syntax to clear data in certain PF file.

Thanks in advance.

[pjk]

Re: How to code SQL embebed in RPGIII?

You are missing the "WHERE" clause on that statement. What you have there deletes every record in the file. That is the difference between SQL and using CHAINS/READS. SQL works with blocks of records (even the entire file) at one time, wher a CHAIN/READ works with a single record.

[jamief]

Re: How to code SQL embebed in RPGIII?

Patrick,

I think uidamt98 is using SQL to clear the entire work file before calling a second program to re-populate it.

I would suggest you use QCMDEXC and clear PF for this rather than SQL.


Oh!!! and forget the use of %trim and the "~" i was going thru a phase

     *                                                     
    D CmdString       s            256    inz(*blanks)     
    D CmdLength       s             15  5 inz(0)           
      *                                                                            
      * Clear Physical File Member                                                 
      *                                                                            
     C                   eval      CmdString = %trim('~CLRPFM') +                  
     C                             %trim('~FILE(QTEMP/CVTSRCPF)') +                
     C                             %trim('~MBR(CVTSRCPFF)')                        
     C                   eval      CmdString = %xlate('~' : ' ' : CmdString)       
     C                   eval      CmdLength = %size(CmdString)                    
     C                   call      'QCMDEXC'                            99         
     C                   parm                    CmdString                         
     C                   parm                    CmdLength                         
      *

As to you problem uidamt98 Im not sure what is happening could you re-phrase the problem.

Thanks
Jamie

[pjk]

Re: How to code SQL embebed in RPGIII?

But when I query ACo002 file. There are not records in this file clear!

I must have misunderstood... I guess I got the impression uidamt98 wanted only some of the data to be cleared, not all. ^^;

Yes, a clear would be preferable, since deleted records still take up space in storage until a RGZFM is done. So for performance reasons alone a CLRPFM would be better.

[uidamt98]

Re: How to code SQL embebed in RPGIII?

I mean I want to clear all data in ACo002 PF file. I can implement by using "CLRPFM" in my code.
This is my code implement successfully:

C****************************************
C CPY004 BEGSR
C MOVEAWLNAME CL2,13
C MOVEAWFNAME CL2,24
C*
C CL2,22 IFEQ *BLANK
C MOVE *BLANK WLCATF 21
C WLNAME CAT '/':0 WLCATF
C WLCATF CAT WFNAME:0 WLCATF
C MOVEAWLCATF CL2,13
C ENDIF
C Z-ADD34 LNG 155
C ENDIF
C Z-ADD34 LNG 155
C CALL 'QCMDEXC'
C PARM CL2
C PARM LNG
C ENDSR
C****************************************

But I do not know how to use SQL in this case. I use this technics because I can implement other SQL statements later. (it is very important!)
I read and implement SQL by the code of Mr jamief description:

*******************************
*Program Name : VFNDOBJ
*Author : Victor Voilevitch
.......

it now not clears any records (although I want to clear all records!! :-( )


P.S: I use RPGIII so I can not implement as "EVAL", "%SIZE", "%XLATE", "%TRIM" command as RPGIV.

Do you have any solution to implement for SQL technics using?
Thanks for all supporting!

[jamief]

Re: How to code SQL embebed in RPGIII?

I mean I want to clear all data in ACo002 PF file. I can implement by using "CLRPFM" in my code.
This is my code implement successfully:

C****************************************
C CPY004 BEGSR
C MOVEAWLNAME CL2,13
C MOVEAWFNAME CL2,24
C*
C CL2,22 IFEQ *BLANK
C MOVE *BLANK WLCATF 21
C WLNAME CAT '/':0 WLCATF
C WLCATF CAT WFNAME:0 WLCATF
C MOVEAWLCATF CL2,13
C ENDIF
C Z-ADD34 LNG 155
C ENDIF
C Z-ADD34 LNG 155
C CALL 'QCMDEXC'
C PARM CL2
C PARM LNG
C ENDSR
C****************************************

But I do not know how to use SQL in this case. I use this technics because I can implement other SQL statements later. (it is very important!)
I read and implement SQL by the code of Mr jamief description:

*******************************
*Program Name : VFNDOBJ
*Author : Victor Voilevitch
.......

it now not clears any records (although I want to clear all records!! :-( )


P.S: I use RPGIII so I can not implement as "EVAL", "%SIZE", "%XLATE", "%TRIM" command as RPGIV.

Do you have any solution to implement for SQL technics using?
Thanks for all supporting!

I have always found (cause Im not as good with SQL as Patrick)
That if from command line you key in STRSQL then from the sql command line you test your statement first. That way you eliminate any possible coding errors from the SQLRPG.

Please test your statement in this manner.

The way you have it coded will work as you think it should--that statement without a select clause will delete ALL records in the file.

In SQLRPGLE we have to worry about the commit option on the compile.....
You may want to look into this.......Authority is also important you should make sure
you have authority to update the file.

Take care
Jamie

[uidamt98]

Re: How to code SQL embebed in RPGIII?

To everyone,
I can delete all records in PF file. In my code, I must add some commands as below:

************************************
C/EXEC SQL
C+ SET OPTION COMMIT = *NONE
C/END-EXEC
*
C/EXEC SQL
C+ DELETE FROM ACO002
C/END-EXEC

C SETON LR
C RETRN

***********************************

This program now clear all records in ACo002 file. (I must use "SET OPTION COMMIT=*NONE" !?!)
Anyway, I implemented the embeded SQL in RPGIII.

Thanks for all supporting and good luck!