Re: Problem with creating batch SQL jobs.

[Joep Beckeringh <joep@xxxxxxxxxxxxxxxxxxx>]

Scott,

I think you're missing a closing bracket on the 'strJob' command. 
Apparently it has the form "GSPMODTST/EXECSQL STMT(<sql statement>)". 
You build the sql statement, but miss the closing bracket.

Joep Beckeringh



Scott Lancaster wrote:
> Hey everyone, looking for some help, i have the following code that i am
> getting an error on line 57, char 2.  
>  
>  The error is CPF0006 - errors occured in command, and the code is
> 80004005 from source IBMDA400
>  
> I am a real novice at this, and modified this code from another
> program.  
>  
> Any help would be great.   I have attached the code below:
> =========================================================================
>
> '************************************************
> '*** SCRIPT TO EXTRACT TOYOTA INVOICE FILE    ***
> '************************************************
>  
> Dim objConn
> Dim objRecIn
> Dim objRecOut
> Dim intButton
> Dim strSQL
> Dim strJob
> Dim strSQLOut
>  
> '******************************************
> '*** Procedure DoFile produces the file ***
> '******************************************
> Sub DoCreateBatchJobs()
>  
> '**********************
> '*** Set up objects ***
> '**********************
>  
> ' Open the file (strFileName)
>  
> Set objConn=WScript.CreateObject("ADODB.Connection")
> Set objRecIn=WScript.CreateObject("ADODB.Recordset")
> Set objRecOut=WScript.CreateObject("ADODB.Recordset") 
>  
> strSQL =          "SELECT mlfile, whfldi, mlnrcd "
> strSQL = strSQL & "FROM copycono.file "
> strSQL = strSQL & "JOIN copycono.field ON whfile = mlfile "
> strSQL = strSQL & "WHERE mlnrcd > 1000 AND whfldi LIKE '%BUAR%' "
> strSQL = strSQL & "ORDER BY mlnrcd DESC"
>  
> objConn.Open "File Name=AS400SQL.UDL"
> objRecIn.Open strSQL, objConn
>  
> Do While Not objRecIn.EOF
>  
>     ' Build what to submit
>  
>                                             
>  
>  
>  
>     strJob = "GSPMODTST/EXECSQL STMT('update mvxbdtacf3." &
> objRecIn("mlfile")
>     strJob = strJob & ".a  set a." & objRecIn("whfldi") & " = (select
> b.market"
>     strJob = strJob & " from scottl.pgrpitem b where a.mmitno = b.item)
> "
>     strJob = strJob & " where a.mmitno in (select c.item from "
>     strJob = strJob & "scottl.pgrpitem c) and a.mmcono = 200"
>  
>  ' Build the SBMJOB command line
>  
>     strSQLOut = "{{SBMJOB CMD(" & strJob & ") JOB(" &
> objRecIn("mlfile")
>  strSQLOut = strSQLOut & ") JOBQ(QGPL/COPYCONO) HOLD(*YES)}}"
>  
>  ' Send it on its way 
>  objRecOut.Open strSQLOut, objConn
>     objRecIn.MoveNext
>  
> Loop
>  
> Set objConn = Nothing
> Set objRecOut = Nothing
> Set objRecIn = Nothing
> MsgBox "Finished - Submitting Jobs"
> End Sub
>  
>
> '*******************************
> '*** O.K to start job ?      ***
> '*******************************
>  
> intButton = MsgBox("O.K. to create batch jobs ?",3)
> If intButton = 6 Then
>    DoCreateBatchJobs
> End If
>      
> ============================================================================
>  
> Please reply to slancaster@xxxxxxxxx
>  
>  
> Thanks
>  
> Scotty
>  
>  
>
>  
>  
> Scott Lancaster
> Systems Administrator
> Gates Australia Pty Ltd
> Ph: +613 9797 9646
> Fx:  +613 9797 9600
> Mob: 0409 744 199