DSXchange

Posted: **Fri Feb 10, 2006 5:24 am**

Hi,

I am executing one query from Routine.
The Query is "SELECT NAME,CATEGORY FROM DS_JOBS WHERE CATEGORY LIKE ='AAB%'"

The output is shown below :

Result = Job name............ Category............

J1AaabXfm AAB\S1Lbw

1 records listed.

I am unable to capture the Category name.

I want to capture both the Job name and the category.

Posted: **Fri Feb 10, 2006 5:36 am**

What exactly is the question you have; from your captured text output the SELECT command correctly output both your jobname and category name separated by one (or more) spaces.

Posted: **Fri Feb 10, 2006 5:39 am**

I am getting the JobName and Category name in Output when executing the UV command. I want to store these 2 in 2 different variables.

How to separte these 2 fields and equate them to variables ?

Posted: **Fri Feb 10, 2006 6:29 am**

They are separated by a space in your output. Change your query order to "SELECT CATEGORY, JOB FROM DS_JOBS WHERE CATEGORY LIKE ='AAB%" and you'll see the difference.

Posted: **Fri Feb 10, 2006 6:42 am**

If you do
Call DSExecute("UV","SELECT NAME,CATEGORY FROM DS_JOBS WHERE CATEGORY LIKE 'B%'",Res,SystemReturnCode)

You can separate yours lines result with
Res2=Field(Res,@FM,nb)
where nb is your line number

Then you're able to separate into two variables :
Var1=Field(Trim(Res2)," ",1) for the NAME
Var2=Field(Trim(Res2)," ",2) for the CATEGORY

Posted: **Fri Feb 10, 2006 7:21 am**

This is my code. I am still unable to extract the values of Job name and Category.

$INCLUDE DSINCLUDE JOBCONTROL.H
0
Ans=0
JobName='J1AaabXfm'

Command ='SELECT NAME, CATEGORY FROM DS_JOBS WHERE NAME=':SQUOTE(JobName):';'
Call DSExecute('UV', Command, Output, SystemReturnCode)

NoOfMessage = DCount(Output,@FM)

for i=1 to NoOfMessage Step 1
Msg = Field(Output,@FM,i)
If Not(IsNull(Trim(Msg)))
Then

Var1=Field(Msg," ",1)
Var2=Field(Msg," ",2)

End
Next i

Ans=Var1:"***":Var2

Posted: **Fri Feb 10, 2006 7:23 am**

Try with Trim() :

Var1=Field(Trim(Msg)," ",1)
Var2=Field(Trim(Msg)," ",2)

Posted: **Fri Feb 10, 2006 7:24 am**

If you want job category:

Code: Select all

* -----------------------------------------------------------------
* GetJobCategory(JobName)
* Decription: Returns Job Category.
* Written by: Kim Duke
* Notes:
* -----------------------------------------------------------------
      Ans = ''
      RoutineName = "GetJobCategory"
      Usage = RoutineName:'(JobName)'
* -----------------------------------------------------------------
* initialize variables
* -----------------------------------------------------------------
      ErrMsg = ''
      DebugSw = @FALSE
* -----------------------------------------------------------------
      FileName = 'DS_JOBS'
      open FileName to FilePtr else
         ErrMsg = 'Can not open ':FileName:' file.'
         goto ErrRtn
      end
      DsJobsPtr = FilePtr
      TheJobName = field(JobName, '.', 1)
      read JobRec from DsJobsPtr, TheJobName else
         ErrMsg = 'No JOB: ':TheJobName
         goto ErrRtn
      end
      JobCategory = JobRec<3>
      Ans = JobCategory
      goto TheEnd
* ----------------------------------------------------------------------
ErrRtn:
      if DebugSw then print
      if DebugSw then print ErrMsg
      if DebugSw then print
      if DebugSw then Ans = ErrMsg
* ----------------------------------------------------------------------
TheEnd:
      if DebugSw then print
      if DebugSw then print
      if DebugSw then print Ans
      if DebugSw then print

Posted: **Fri Feb 10, 2006 3:16 pm**

Here's a great opportunity to use BCI functions. The environments for localuv are already set up, available through variables @HENV, @HDBC and @HSTMT. So all you need is one call to SQLExecDirect(), two calls to SQLBindCol() and one call to SQLFetch() (plus appropriate error handling) and you are there!

Code: Select all

SQLStmt = 'SELECT NAME, CATEGORY FROM DS_JOBS WHERE NAME=':SQUOTE(JobName):';' 
ErrCode = SQLExecDirect(@HSTMT, SQLStmt)
ErrCode = SQLBindCol(@HSTMT, 1, SQL.B.DEFAULT, Name)
ErrCode = SQLBindCol(@HSTMT, 2, SQL.B.DEFAULT, Category)
ErrCode = SQLFetch(@HSTMT)

ErrCode = SQLFreeEnv(@STMT, SQL.DROP)

Please check syntax for SQLBindCol() - that was done from memory!

Posted: **Sat Feb 11, 2006 2:19 am**

Thanks a lot everyone, I got it resoolved. In my query i Restricted the lenght of Job Name and Category each to 30 and then took the substrings out of the fetched record.

Thanks

DSXchange

DS_JOBS Query

DS_JOBS Query

Easily solved with just four BCI functions