Ex_Caliber
asked on
Get all the spooled files for a specific OutQ
Hello all.
I need write a program that process spooled files that are in a specific OutQ.
All I have is the OutQ name.
I need to get all the information about the spooled files(like job,job#,user, splnbr...)
I am writing a VB Program that sends CL Commands to the as400 system.
I thought of using WRKOUTQ and save the output to a file and then copy it to a pc file and then read it from the VB program.
The problem is that I don't know exactly how to do that.
Thanks for the answers.
I need write a program that process spooled files that are in a specific OutQ.
All I have is the OutQ name.
I need to get all the information about the spooled files(like job,job#,user, splnbr...)
I am writing a VB Program that sends CL Commands to the as400 system.
I thought of using WRKOUTQ and save the output to a file and then copy it to a pc file and then read it from the VB program.
The problem is that I don't know exactly how to do that.
Thanks for the answers.
ASKER
Hi Murph,
I do have CA Installation and I will look up those DLLs.
I will update you in a short while.
P.S.
maybe you know the names of the DLLs?
I do have CA Installation and I will look up those DLLs.
I will update you in a short while.
P.S.
maybe you know the names of the DLLs?
Hi Ex_Caliber,
It's even beter then i thought there are 5 controls available
1. AS/400 Operations Navigator List Control
2. AS/400 Operations Navigator Tree Control
3. Client Access Data Queue Text Box Control
4. Client Access Remote Command Button Control
5. Client Access System List Control
This are all components that are also used in the Oper. Nav., so if you never used the Oper. Nav. before try it first to see what you can do with the several options.
It's even beter then i thought there are 5 controls available
1. AS/400 Operations Navigator List Control
2. AS/400 Operations Navigator Tree Control
3. Client Access Data Queue Text Box Control
4. Client Access Remote Command Button Control
5. Client Access System List Control
This are all components that are also used in the Oper. Nav., so if you never used the Oper. Nav. before try it first to see what you can do with the several options.
ASKER
Hi Murph,
All of these controls are not dealing with OutQs and spoolFiles.
I found an activeX from the installation of CA named ActiveX_AS400 which does deal with SpoolFiles.
I am checking it now to find out if it can help me.
update soon.
All of these controls are not dealing with OutQs and spoolFiles.
I found an activeX from the installation of CA named ActiveX_AS400 which does deal with SpoolFiles.
I am checking it now to find out if it can help me.
update soon.
Hi
If you want a different angle I may have a solution that uses ADODB
1) Create a physical file that will hold the relevent infotmation
2) Call a pgm that Use an AIP to populte the file, and generate a key
3) Read the file using the returned key
I can post some code to help if you want.
Dave
If you want a different angle I may have a solution that uses ADODB
1) Create a physical file that will hold the relevent infotmation
2) Call a pgm that Use an AIP to populte the file, and generate a key
3) Read the file using the returned key
I can post some code to help if you want.
Dave
Ex_Caliber:
Try this as a starting point:
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/rzaik/rzaiksoa.htm
If you don't find what you need there, then there are alternatives that require programming on the iSeries side.
Tom
Try this as a starting point:
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/rzaik/rzaiksoa.htm
If you don't find what you need there, then there are alternatives that require programming on the iSeries side.
Tom
ASKER
Hi Dave,
About :
"1) Create a physical file that will hold the relevent infotmation"
Can you be more specifc of how to create a physical file from the results of WrkOutQ command?
About :
"1) Create a physical file that will hold the relevent infotmation"
Can you be more specifc of how to create a physical file from the results of WrkOutQ command?
Hi
I got a bit board on Friday so wrote some code. You can use it as a starting point.
I have written this for V5.2. On the F spec there is a keyword EXTFILE('DSLIBEE/OUTSPLF') This is the actual file name and saves performing the ovrdbf cl command and
Database file (outsplf)
A R OUTSPLFR
A KEY 6 COLHDG('Key')
A OUTQ 10 COLHDG('Output Queue')
A SPLF 10 COLHDG('Spool File')
A SPLFN 6 COLHDG('Spool File Number')
A JOB 10 COLHDG('Job Name')
A USER 10 COLHDG('User Id')
A NBR 6 COLHDG('Job Number')
A STS 4 COLHDG('Splf Status')
A PAGE 7 0 COLHDG('Nbr Pages')
RPGLE pgm
‚************************* ********** ********** ********** ********** ***
FOUTSPLF O E Disk EXTFILE('DSLIBEE/OUTSPLF')
‚************************* ********** ********** ********** ********** ***
‚** PSDS
D PSDS SDS
D P#JOBN 264 269
‚************************* ********** ********** ********** ********** ***
‚** Spool file status
D AStatus S 4 dim(12) ctdata
D perrcd(12)
‚************************* ********** ********** ********** ********** ***
‚** error data structure:
D ErrorDs Ds
D ErBytPrv 10i 0 Inz( %Size( ErrorDs ))
D ErBytAvl 10i 0
D ErMsgId 7a
D 1a
D Erdata 128a
‚************************* ********** ********** ********** ********** ***
‚** Spooled file information:
D OSPL0300 Ds
D spJobId 26a
D spJobNam 10a Overlay( spJobId: 1 )
D spUsrNam 10a Overlay( spJobId: *Next )
D spJobNbr 6a Overlay( spJobId: *Next )
D spSplfNam 10a
D spSplfNbr 10i 0
D spSplfSts 10i 0
D spDatOpn 7a
D spTimOpn 6a
D spSplfSch 1a
D spJobSysNam 10a
D spUsrDta 10a
D spFrmTyp 10a
D spOutQnam 10a
D spOutQlib 10a
D spAuxStgPool 10i 0
D spSplfSiz 10i 0
D spSizMtp 10i 0
D spTotPag 10i 0
D spCpyLft 10i 0
D spSplfPty 1a
D 3a
‚************************* ********** ********** ********** ********** ***
‚** Close list
D CloseLst Pr ExtPgm( 'QGY/QGYCLST' )
D ClHandle 4a Const
D ClError 1024a Options( *VarSize )
‚************************* ********** ********** ********** ********** ***
‚** Get list entry:
D GetLstEnt Pr ExtPgm( 'QGY/QGYGTLE' )
D GlRcvVar 65535a Options( *VarSize )
D GlRcvVarLen 10i 0 Const
D GlHandle 4a Const
D GlLstInf 80a
D GlNbrRcdRtn 10i 0 Const
D GlRtnRcdNbr 10i 0 Const
D GlError 1024a Options( *VarSize )
‚************************* ********** ********** ********** ********** ***
‚** Sort information:
D srSrtInf Ds
D srNbrKeys 10i 0
D srSrtInfa 12a Dim( 10 )
‚**Sort Sub definition
D srSrtInf1 Ds
D s1SrtInf
D srKeyFldOfs 10i 0 Overlay( s1SrtInf : 1 )
D srKeyFldLen 10i 0 Overlay( s1SrtInf : 5 )
D srKeyFldTyp 5i 0 Overlay( s1SrtInf : 9 )
D srSrtOrd 1a Overlay( s1SrtInf : 11 )
D srRsv 1a Overlay( s1SrtInf : 12 )
‚************************* ********** ********** ********** ********** ***
‚** Open list of jobs:
D LstSplf Pr ExtPgm( 'QGY/QGYOLSPL' )
D LsRcvVar 65535a Options( *VarSize )
D LsRcvVarLen 10i 0 Const
D LsLstInf 80a
D LsNbrRcdRtn 10i 0 Const
D LsSrtInf 1024a Const Options( *VarSize )
D LsSltInf 1024a Const Options( *VarSize )
D LsJobNam 26a Const
D LsFmtNam 8a Const
D LsError 1024a Options( *VarSize )
‚************************* ********** ********** ********** ********** ***
‚** Procedure definitionas
D fnOpenList Pr n
DfnGetList Pr n
DfnCloseList Pr n
‚************************* ********** ********** ********** ********** ***
‚** Global variables
D SplfChkDts s z
D NbrSplfs s 10i 0 Inz
D SortBinary S 5I 0 INZ(9)
D SortChar S 5I 0 INZ(4)
D SlCurRcdNbr s 10i 0 Inz( 0 )
D SlSavRcdNbr s 10i 0 Inz( 0 )
‚************************* ********** ********** ********** ********** ***
‚** Parameters
D SlOutQ Ds
D SlOutQnam 10a Overlay( SlOutQ: 1 )
D SlOutQlib 10a Overlay( SlOutQ: *Next )
‚************************* ********** ********** ********** ********** ***
‚** List information:
D SlLstInf Ds
D NbrRecs 10i 0
D RcdNbrRtn 10i 0
D LiHandle 4a
D LiRcdLen 10i 0
D LiInfSts 1a
D LiDts 13a
D LiLstSts 1a
D 1a
D LiInfLen 10i 0
D LiRcd1 10i 0
D 40a
‚************************* ********** ********** ********** ********** ***
‚**
‚************************* ********** ********** ********** ********** ***
‚*
C *Entry Plist
C Parm Key
C Parm SlOutQ
‚* Open the list
c eval *IN01=fnOPenList()
C EXSR srRollf
C EVAL *IN01=fnCloseList()
C Eval *InLr = *On
C Return
‚************************* ********** ********** ********** ********** ********** ********** *****
‚** srRollf - Ouput next page of the subfile
‚************************* ********** ********** ********** ********** ********** ********** *****
C SrRollF BEGSR
‚** Process required number of records
B01 C DO *hival
‚** No more entries?
B02 C IF SlCurRcdNbr = NbrRecs
C LEAVE
E02 C ENDIF
‚** Get entry data
C Eval SlCurRcdNbr = SlCurRcdNbr + 1
c EVAL *IN02=fnGetList()
‚** populate subfile details
C EVAL Splf = spSplfNam
C move spSplfNbr splfn
C EVAL USER = spUsrNam
C EVAL Outq = spOutQnam
C EVAL PAGE = spTotPag
C EVAL JOB = spJobNam
C EVAL USER = spUsrNam
C EVAL NBR = spJobNbr
C EVAL sts = astatus(spSplfSts)
C EVAL KEY = P#JOBN
‚** Write record
c WRITE outsplfr
‚*
E01 C EndDo
c ENDSR
‚************************* ********** ********** ********** ********** ********** ****
‚** Open List & Return Handle
‚************************* ********** ********** ********** ********** ********** ****
PfnOpenList B
DfnOpenList PI N
‚** Selection information:
D SlSltInf Ds
D srNbrUsrNam 10i 0 Inz( %Elem( srUsrEnt ))
D srUsrEnt 12a Dim( 1 )
D srUsrNam 10a Overlay( srUsrEnt: 1 )
D srUsrRsv 2a Overlay( srUsrEnt: *Next )
D Inz( *Allx'00' )
D srNbrOutQ 10i 0 Inz( %Elem( srOutQ ))
D srOutQ 20a Dim( 1 )
D srOutQNam 10a Overlay( srOutQ: 1 )
D srOutQlib 10a Overlay( srOutQ: *Next )
‚*
D srFrmTyp 10a
D srUsrDta 10a
‚*
D srNbrSplfSts 10i 0 Inz( %Elem( srSplfSts ))
D srSplfSts 12a Dim( 1 )
D srStatus 10a Overlay( srSplfSts: 1 )
D 2a Overlay( srSplfSts: *Next )
D Inz( *Allx'00' )
‚*
D srNbrDevNam 10i 0 Inz( %Elem( srDevEnt ))
D srDevEnt 12a Dim( 1 )
D srDevNam1 10a Overlay( srDevEnt: 1 )
D Inz( '*ALL' )
D 2a Overlay( srDevEnt: *Next )
D Inz( *Allx'00' )
D DoDatFrm S 7a
D DoTimFrm S 6a
C EVAL *IN01=fnCloseList()
C Eval srSplfSts(1) = '*ALL'
C Eval srUsrNam = '*ALL'
C Eval srFrmTyp = '*ALL'
C Eval srUsrDta = '*ALL'
‚*
B01 C If SlOutQnam <> '*ALL'
C Eval srOutQ(1) = SlOutQ
X01 C Else
C Eval srOutQ(1) = SlOutQnam
E01 C EndIf
‚*
C Eval DoDatFrm = '1991231'
C Eval DoTimFrm = '240000'
‚*
C Eval SplfChkDts = %Date( DoDatFrm: *CYMD0 ) +
C %Time( DoTimFrm: *HMS0 )
C Eval srNbrKeys = 1
C Eval srKeyFldOfs = 11
C Eval srKeyFldLen = 10
C Eval srKeyFldTyp = 4
C Eval srSrtOrd = '1'
C Eval srRsv = x'00'
C eval srSrtInfa(1)=srSrtInf1
C CallP LstSplf( OSPL0300
C : %size( OSPL0300 )
C : SlLstInf
C : 1
C : srSrtInf
C : SlSltInf
C : *Blanks
C : 'OSPL0300'
C : ErrorDs
C )
C RETURN LiHandle
PfnOpenList E
‚************************* ********** ********** ********** ********** ********** ****
‚** fnGeTLstEntry
‚************************* ********** ********** ********** ********** ********** ****
PfnGetList B
DfnGeTList PI N
‚***
C CallP GetLstEnt( OSPL0300
C : %Size( OSPL0300 )
C : LiHandle
C : SlLstInf
C : 1
C : SlCurRcdNbr
C : ErrorDs
C )
B01 C IF ErBytAvl<>0
C RETURN *Off
E01 C ENDIF
‚**
C RETURN *ON
PfnGetList e
‚************************* ********** ********** ********** ********** ********** ****
‚** fnCloseList
‚************************* ********** ********** ********** ********** ********** ****
PfnCloseList B
DfnCloseList PI N
‚*
C CallP CloseLst( LiHandle
C : ErrorDs
C )
C RETURN *ON
‚*
PfnCloseList e
**
RDY OPN CLO SAV WTR HLD MSGWPND PRT FIN SND DFR
========================== ========== ========== ========== ========== =====
And VB code
in VB you need the following reference addied to you project.
Microsoft ActiveX data objects Recordset x.x library
Microsoft ActiveX data Objrcts x.x Library
========================== ========== ===
Private Sub subGetOutq()
On Error GoTo DisplayError
Dim AS400Conn As New ADODB.Connection
Dim AS400Pgm As New ADODB.Command
Dim AS400rs As New ADODB.Recordset
Dim Rcds As Variant
Dim Parms As Variant
Dim sql As String
' Connect
AS400Conn.Open "Provider=IBMDA400;Data Source=as400name;User ID=userid;Password=passwor d;"
' Prepare for call & record set
Set AS400Pgm.ActiveConnection = AS400Conn
Set AS400rs.ActiveConnection = AS400Conn
AS400Pgm.CommandText = "{{call DSLIBEE/OUTSPLF(?,?)}}"
AS400Pgm.Parameters.Append AS400Pgm.CreateParameter(" KEY", adChar, adParamInputOutput, 6)
AS400Pgm.Parameters.Append AS400Pgm.CreateParameter(" OUTQLIB", adChar, adParamInput, 20)
' Set parameters
Parms = Array("Key", "QPRINT *LIBL")
' Execute - and wait for return
AS400Pgm.Execute Rcds, Parms, -1
' select records using returnwed key
sql = "select * from DSLIBEE.OUTSPLF WHERE KEY = '" & AS400Pgm.Parameters.Item(" key").Valu e & "'"
AS400rs.Open sql
Do Until AS400rs.EOF
For I = 0 To AS400rs.Fields.Count - 1
Debug.Print AS400rs.Fields(I).Value; " ; ";
Next
AS400rs.MoveNext
Loop
'Clear the file
sql = "DELETE from DSLIBEE.OUTSPLF WHERE KEY = '" & AS400Pgm.Parameters.Item(" key").Valu e & "'"
AS400Conn.Execute sql
tagExit:
' Terminate
AS400Conn.Close
Set AS400Conn = Nothing
Set AS400Pgm = Nothing
Set AS400rs = Nothing
' Error routine
Exit Sub
DisplayError:
MsgBox Err.Description, vbCritical, "Error"
Resume tagExit
End Sub
Have Fun
Dave
I got a bit board on Friday so wrote some code. You can use it as a starting point.
I have written this for V5.2. On the F spec there is a keyword EXTFILE('DSLIBEE/OUTSPLF')
Database file (outsplf)
A R OUTSPLFR
A KEY 6 COLHDG('Key')
A OUTQ 10 COLHDG('Output Queue')
A SPLF 10 COLHDG('Spool File')
A SPLFN 6 COLHDG('Spool File Number')
A JOB 10 COLHDG('Job Name')
A USER 10 COLHDG('User Id')
A NBR 6 COLHDG('Job Number')
A STS 4 COLHDG('Splf Status')
A PAGE 7 0 COLHDG('Nbr Pages')
RPGLE pgm
‚*************************
FOUTSPLF O E Disk EXTFILE('DSLIBEE/OUTSPLF')
‚*************************
‚** PSDS
D PSDS SDS
D P#JOBN 264 269
‚*************************
‚** Spool file status
D AStatus S 4 dim(12) ctdata
D perrcd(12)
‚*************************
‚** error data structure:
D ErrorDs Ds
D ErBytPrv 10i 0 Inz( %Size( ErrorDs ))
D ErBytAvl 10i 0
D ErMsgId 7a
D 1a
D Erdata 128a
‚*************************
‚** Spooled file information:
D OSPL0300 Ds
D spJobId 26a
D spJobNam 10a Overlay( spJobId: 1 )
D spUsrNam 10a Overlay( spJobId: *Next )
D spJobNbr 6a Overlay( spJobId: *Next )
D spSplfNam 10a
D spSplfNbr 10i 0
D spSplfSts 10i 0
D spDatOpn 7a
D spTimOpn 6a
D spSplfSch 1a
D spJobSysNam 10a
D spUsrDta 10a
D spFrmTyp 10a
D spOutQnam 10a
D spOutQlib 10a
D spAuxStgPool 10i 0
D spSplfSiz 10i 0
D spSizMtp 10i 0
D spTotPag 10i 0
D spCpyLft 10i 0
D spSplfPty 1a
D 3a
‚*************************
‚** Close list
D CloseLst Pr ExtPgm( 'QGY/QGYCLST' )
D ClHandle 4a Const
D ClError 1024a Options( *VarSize )
‚*************************
‚** Get list entry:
D GetLstEnt Pr ExtPgm( 'QGY/QGYGTLE' )
D GlRcvVar 65535a Options( *VarSize )
D GlRcvVarLen 10i 0 Const
D GlHandle 4a Const
D GlLstInf 80a
D GlNbrRcdRtn 10i 0 Const
D GlRtnRcdNbr 10i 0 Const
D GlError 1024a Options( *VarSize )
‚*************************
‚** Sort information:
D srSrtInf Ds
D srNbrKeys 10i 0
D srSrtInfa 12a Dim( 10 )
‚**Sort Sub definition
D srSrtInf1 Ds
D s1SrtInf
D srKeyFldOfs 10i 0 Overlay( s1SrtInf : 1 )
D srKeyFldLen 10i 0 Overlay( s1SrtInf : 5 )
D srKeyFldTyp 5i 0 Overlay( s1SrtInf : 9 )
D srSrtOrd 1a Overlay( s1SrtInf : 11 )
D srRsv 1a Overlay( s1SrtInf : 12 )
‚*************************
‚** Open list of jobs:
D LstSplf Pr ExtPgm( 'QGY/QGYOLSPL' )
D LsRcvVar 65535a Options( *VarSize )
D LsRcvVarLen 10i 0 Const
D LsLstInf 80a
D LsNbrRcdRtn 10i 0 Const
D LsSrtInf 1024a Const Options( *VarSize )
D LsSltInf 1024a Const Options( *VarSize )
D LsJobNam 26a Const
D LsFmtNam 8a Const
D LsError 1024a Options( *VarSize )
‚*************************
‚** Procedure definitionas
D fnOpenList Pr n
DfnGetList Pr n
DfnCloseList Pr n
‚*************************
‚** Global variables
D SplfChkDts s z
D NbrSplfs s 10i 0 Inz
D SortBinary S 5I 0 INZ(9)
D SortChar S 5I 0 INZ(4)
D SlCurRcdNbr s 10i 0 Inz( 0 )
D SlSavRcdNbr s 10i 0 Inz( 0 )
‚*************************
‚** Parameters
D SlOutQ Ds
D SlOutQnam 10a Overlay( SlOutQ: 1 )
D SlOutQlib 10a Overlay( SlOutQ: *Next )
‚*************************
‚** List information:
D SlLstInf Ds
D NbrRecs 10i 0
D RcdNbrRtn 10i 0
D LiHandle 4a
D LiRcdLen 10i 0
D LiInfSts 1a
D LiDts 13a
D LiLstSts 1a
D 1a
D LiInfLen 10i 0
D LiRcd1 10i 0
D 40a
‚*************************
‚**
‚*************************
‚*
C *Entry Plist
C Parm Key
C Parm SlOutQ
‚* Open the list
c eval *IN01=fnOPenList()
C EXSR srRollf
C EVAL *IN01=fnCloseList()
C Eval *InLr = *On
C Return
‚*************************
‚** srRollf - Ouput next page of the subfile
‚*************************
C SrRollF BEGSR
‚** Process required number of records
B01 C DO *hival
‚** No more entries?
B02 C IF SlCurRcdNbr = NbrRecs
C LEAVE
E02 C ENDIF
‚** Get entry data
C Eval SlCurRcdNbr = SlCurRcdNbr + 1
c EVAL *IN02=fnGetList()
‚** populate subfile details
C EVAL Splf = spSplfNam
C move spSplfNbr splfn
C EVAL USER = spUsrNam
C EVAL Outq = spOutQnam
C EVAL PAGE = spTotPag
C EVAL JOB = spJobNam
C EVAL USER = spUsrNam
C EVAL NBR = spJobNbr
C EVAL sts = astatus(spSplfSts)
C EVAL KEY = P#JOBN
‚** Write record
c WRITE outsplfr
‚*
E01 C EndDo
c ENDSR
‚*************************
‚** Open List & Return Handle
‚*************************
PfnOpenList B
DfnOpenList PI N
‚** Selection information:
D SlSltInf Ds
D srNbrUsrNam 10i 0 Inz( %Elem( srUsrEnt ))
D srUsrEnt 12a Dim( 1 )
D srUsrNam 10a Overlay( srUsrEnt: 1 )
D srUsrRsv 2a Overlay( srUsrEnt: *Next )
D Inz( *Allx'00' )
D srNbrOutQ 10i 0 Inz( %Elem( srOutQ ))
D srOutQ 20a Dim( 1 )
D srOutQNam 10a Overlay( srOutQ: 1 )
D srOutQlib 10a Overlay( srOutQ: *Next )
‚*
D srFrmTyp 10a
D srUsrDta 10a
‚*
D srNbrSplfSts 10i 0 Inz( %Elem( srSplfSts ))
D srSplfSts 12a Dim( 1 )
D srStatus 10a Overlay( srSplfSts: 1 )
D 2a Overlay( srSplfSts: *Next )
D Inz( *Allx'00' )
‚*
D srNbrDevNam 10i 0 Inz( %Elem( srDevEnt ))
D srDevEnt 12a Dim( 1 )
D srDevNam1 10a Overlay( srDevEnt: 1 )
D Inz( '*ALL' )
D 2a Overlay( srDevEnt: *Next )
D Inz( *Allx'00' )
D DoDatFrm S 7a
D DoTimFrm S 6a
C EVAL *IN01=fnCloseList()
C Eval srSplfSts(1) = '*ALL'
C Eval srUsrNam = '*ALL'
C Eval srFrmTyp = '*ALL'
C Eval srUsrDta = '*ALL'
‚*
B01 C If SlOutQnam <> '*ALL'
C Eval srOutQ(1) = SlOutQ
X01 C Else
C Eval srOutQ(1) = SlOutQnam
E01 C EndIf
‚*
C Eval DoDatFrm = '1991231'
C Eval DoTimFrm = '240000'
‚*
C Eval SplfChkDts = %Date( DoDatFrm: *CYMD0 ) +
C %Time( DoTimFrm: *HMS0 )
C Eval srNbrKeys = 1
C Eval srKeyFldOfs = 11
C Eval srKeyFldLen = 10
C Eval srKeyFldTyp = 4
C Eval srSrtOrd = '1'
C Eval srRsv = x'00'
C eval srSrtInfa(1)=srSrtInf1
C CallP LstSplf( OSPL0300
C : %size( OSPL0300 )
C : SlLstInf
C : 1
C : srSrtInf
C : SlSltInf
C : *Blanks
C : 'OSPL0300'
C : ErrorDs
C )
C RETURN LiHandle
PfnOpenList E
‚*************************
‚** fnGeTLstEntry
‚*************************
PfnGetList B
DfnGeTList PI N
‚***
C CallP GetLstEnt( OSPL0300
C : %Size( OSPL0300 )
C : LiHandle
C : SlLstInf
C : 1
C : SlCurRcdNbr
C : ErrorDs
C )
B01 C IF ErBytAvl<>0
C RETURN *Off
E01 C ENDIF
‚**
C RETURN *ON
PfnGetList e
‚*************************
‚** fnCloseList
‚*************************
PfnCloseList B
DfnCloseList PI N
‚*
C CallP CloseLst( LiHandle
C : ErrorDs
C )
C RETURN *ON
‚*
PfnCloseList e
**
RDY OPN CLO SAV WTR HLD MSGWPND PRT FIN SND DFR
==========================
And VB code
in VB you need the following reference addied to you project.
Microsoft ActiveX data objects Recordset x.x library
Microsoft ActiveX data Objrcts x.x Library
==========================
Private Sub subGetOutq()
On Error GoTo DisplayError
Dim AS400Conn As New ADODB.Connection
Dim AS400Pgm As New ADODB.Command
Dim AS400rs As New ADODB.Recordset
Dim Rcds As Variant
Dim Parms As Variant
Dim sql As String
' Connect
AS400Conn.Open "Provider=IBMDA400;Data Source=as400name;User ID=userid;Password=passwor
' Prepare for call & record set
Set AS400Pgm.ActiveConnection = AS400Conn
Set AS400rs.ActiveConnection = AS400Conn
AS400Pgm.CommandText = "{{call DSLIBEE/OUTSPLF(?,?)}}"
AS400Pgm.Parameters.Append
AS400Pgm.Parameters.Append
' Set parameters
Parms = Array("Key", "QPRINT *LIBL")
' Execute - and wait for return
AS400Pgm.Execute Rcds, Parms, -1
' select records using returnwed key
sql = "select * from DSLIBEE.OUTSPLF WHERE KEY = '" & AS400Pgm.Parameters.Item("
AS400rs.Open sql
Do Until AS400rs.EOF
For I = 0 To AS400rs.Fields.Count - 1
Debug.Print AS400rs.Fields(I).Value; " ; ";
Next
AS400rs.MoveNext
Loop
'Clear the file
sql = "DELETE from DSLIBEE.OUTSPLF WHERE KEY = '" & AS400Pgm.Parameters.Item("
AS400Conn.Execute sql
tagExit:
' Terminate
AS400Conn.Close
Set AS400Conn = Nothing
Set AS400Pgm = Nothing
Set AS400rs = Nothing
' Error routine
Exit Sub
DisplayError:
MsgBox Err.Description, vbCritical, "Error"
Resume tagExit
End Sub
Have Fun
Dave
ASKER
Hi Dave,
THANKS A LOT for the code but unfortunately I can’t write any code on the iSeries computer.
My Question was how do I use the OUTPUT parameter of the WRKOUTQ command to save the result to a Physical file.
Later on I am planning to convert the file to a pc file and then to read it as a text file.
THANKS A LOT for the code but unfortunately I can’t write any code on the iSeries computer.
My Question was how do I use the OUTPUT parameter of the WRKOUTQ command to save the result to a Physical file.
Later on I am planning to convert the file to a pc file and then to read it as a text file.
Hi
not even a simple CL?
Dave
not even a simple CL?
Dave
Hi
this is the only way I cabn think of dowing it with very litlle AS/400 involvement. If you can not craete the AS/400 file then then you will need as mixture AODB and ODBC
AS/400 physica file. This is simply needed because it is compicated to convert AS/400 ccsid 65535 in ADODB (It is easy in ODBC). Create using CCSID 37. I have caklled it zzsplfzz in library dslibee
A R SPLF
A DATA 200
The VB code can then be
Private Sub subGetOutq()
On Error GoTo DisplayError
Dim AS400Conn As New ADODB.Connection
Dim AS400Pgm As New ADODB.Command
Dim AS400rs As New ADODB.Recordset
Dim Rcds As Variant
Dim Parms As Variant
Dim sql As String
' Connect
AS400Conn.Open "Provider=IBMDA400;Data Source=AS400name or IP address;User ID=userid;Password=passwor d;"
' Prepare for call & record set
Set AS400Pgm.ActiveConnection = AS400Conn
Set AS400rs.ActiveConnection = AS400Conn
'' delete existing WRKOUTQ spool files for user ' userid should be the user in the userid on the connection string
On Error GoTo NoSpoolfile
sql = "DLTSPLF FILE(QPRTSPLQ) JOB(userid/QPRTJOB) SPLNBR(*LAST)"
Do
Call xcall(AS400Pgm, sql)
Loop
NoSpoolfileResume:
'' generate a print from wrkoutq
On Error GoTo DisplayError
sql = "WRKOUTQ OUTQ(QGPL/QPRINT) OUTPUT(*PRINT)"
Call xcall(AS400Pgm, sql)
'Copy the spool file to the output file (created above)
sql = "CPYSPLF FILE(QPRTSPLQ) TOFILE(DSLIBEE/zzsplfzz) JOB(userid/QPRTJOB) SPLNBR(*LAST)"
Call xcall(AS400Pgm, sql)
'Read all records from the output file
sql = "select * from DSLIBEE.zzsplfzz"
AS400rs.Open sql
Do Until AS400rs.EOF
For I = 0 To AS400rs.Fields.Count - 1
Debug.Print AS400rs.Fields(I).Value
Next
AS400rs.MoveNext
Loop
tagExit:
' Terminate
AS400Conn.Close
Set AS400Conn = Nothing
Set AS400Pgm = Nothing
Set AS400rs = Nothing
' Error routine
Exit Sub
DisplayError:
MsgBox Err.Description, vbCritical, "Error"
Resume tagExit
NoSpoolfile:
Resume NoSpoolfileResume:
End Sub
Sub xcall(ByRef cmd, xcmd As String)
Dim yCMD
yCMD = "CALL QSYS.QCMDEXC('" & xcmd & "', " & Format(Len(xcmd), "0000000000") & ".00000)"
cmd.CommandText = yCMD
cmd.Execute
End Sub
Please note if the datbase is a hex database then the values returned from the sql will be ?????????????????????????? ??
Dave
this is the only way I cabn think of dowing it with very litlle AS/400 involvement. If you can not craete the AS/400 file then then you will need as mixture AODB and ODBC
AS/400 physica file. This is simply needed because it is compicated to convert AS/400 ccsid 65535 in ADODB (It is easy in ODBC). Create using CCSID 37. I have caklled it zzsplfzz in library dslibee
A R SPLF
A DATA 200
The VB code can then be
Private Sub subGetOutq()
On Error GoTo DisplayError
Dim AS400Conn As New ADODB.Connection
Dim AS400Pgm As New ADODB.Command
Dim AS400rs As New ADODB.Recordset
Dim Rcds As Variant
Dim Parms As Variant
Dim sql As String
' Connect
AS400Conn.Open "Provider=IBMDA400;Data Source=AS400name or IP address;User ID=userid;Password=passwor
' Prepare for call & record set
Set AS400Pgm.ActiveConnection = AS400Conn
Set AS400rs.ActiveConnection = AS400Conn
'' delete existing WRKOUTQ spool files for user ' userid should be the user in the userid on the connection string
On Error GoTo NoSpoolfile
sql = "DLTSPLF FILE(QPRTSPLQ) JOB(userid/QPRTJOB) SPLNBR(*LAST)"
Do
Call xcall(AS400Pgm, sql)
Loop
NoSpoolfileResume:
'' generate a print from wrkoutq
On Error GoTo DisplayError
sql = "WRKOUTQ OUTQ(QGPL/QPRINT) OUTPUT(*PRINT)"
Call xcall(AS400Pgm, sql)
'Copy the spool file to the output file (created above)
sql = "CPYSPLF FILE(QPRTSPLQ) TOFILE(DSLIBEE/zzsplfzz) JOB(userid/QPRTJOB) SPLNBR(*LAST)"
Call xcall(AS400Pgm, sql)
'Read all records from the output file
sql = "select * from DSLIBEE.zzsplfzz"
AS400rs.Open sql
Do Until AS400rs.EOF
For I = 0 To AS400rs.Fields.Count - 1
Debug.Print AS400rs.Fields(I).Value
Next
AS400rs.MoveNext
Loop
tagExit:
' Terminate
AS400Conn.Close
Set AS400Conn = Nothing
Set AS400Pgm = Nothing
Set AS400rs = Nothing
' Error routine
Exit Sub
DisplayError:
MsgBox Err.Description, vbCritical, "Error"
Resume tagExit
NoSpoolfile:
Resume NoSpoolfileResume:
End Sub
Sub xcall(ByRef cmd, xcmd As String)
Dim yCMD
yCMD = "CALL QSYS.QCMDEXC('" & xcmd & "', " & Format(Len(xcmd), "0000000000") & ".00000)"
cmd.CommandText = yCMD
cmd.Execute
End Sub
Please note if the datbase is a hex database then the values returned from the sql will be ??????????????????????????
Dave
ASKER
Hi Dave.
Well, I can run simple CL commands from a remote computer but I can not run CL programs.
Much appreciation for your efforts.
I will try this code on the coming days.
Btw, can you write me the command for creating the physica file?
Well, I can run simple CL commands from a remote computer but I can not run CL programs.
Much appreciation for your efforts.
I will try this code on the coming days.
Btw, can you write me the command for creating the physica file?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hmmm... another possibility... Rather than [WRKOUTQ *PRINT], the List Spooled Files (QUSLSPL) API will create a list in a user space. Then Sort (QLGSORT) API can be called to place the listed entries into a file. (Or you could start with the Open List of Spooled Files (QGYOLSPL) API, but that might be overkill.)
These _might_ be useful if you call them as stored procedures.
In addition, although you aren't allowed to write "programs" on your AS/400, _perhaps_ you'd be allowed to upload a REXX member. With REXX, the APIs get easier. Technically, it sounds as if you already have the authority to upload and run REXX scripts although you might not have permission. It might be worth asking if you can upload a REXX script to help with your problem.
Tom
These _might_ be useful if you call them as stored procedures.
In addition, although you aren't allowed to write "programs" on your AS/400, _perhaps_ you'd be allowed to upload a REXX member. With REXX, the APIs get easier. Technically, it sounds as if you already have the authority to upload and run REXX scripts although you might not have permission. It might be worth asking if you can upload a REXX script to help with your problem.
Tom
ASKER
Thanks you Murph,tom and especialy Dave for the help.
Ex_Caliber.
Ex_Caliber.
If you have Client Access installed, then you should install the CA operations navigator (on the samr CA Install cd) !!!!!
After installing this, there are 2 objects (OCX or DLL available where you can directly access the printerQ's and spoolfiles.
I don't know exactly how, dut I saw them while I was messing around with some VBA stuff.
Regards
Murph