Highlighted
jlynch Absent Member.
Absent Member.
1019 views

Using API, how to retrieve a list of CRs based on a Usr_ defined field

Jump to solution

Hi All,

 

I'm looking for a way to make a call through the API to query the Starteam DB and have the query return a list of CRs that have a matching value in a Usr_ defined field.

 

For example, for each CR we have a Usr_ReleaseNumber text field defined for the CR. I would like to send a query that basically says, "Please return a list of CR's that contain Usr_ReleaseNumber equal to "1234". I'd like to see the work done on the database side, rather than have to walk a list of CR's in the folder and interrogate each one to see if it has a matching release number.

 

Is this something I can do using the ViewMemberListManager? If so, still not sure how to construct the query through the API to return just the relevant CRs.

 

Thank you!!

0 Likes
Reply
1 Solution

Accepted Solutions
Micro Focus Expert
Micro Focus Expert

RE: Using API, how to retrieve a list of CRs based on a Usr_ defined field

Jump to solution

the simplest query you can write is one using the CommandProcessor class, which encapsulates viewmemberlistmanagers, folderlistmanagers, sorting, querying, filtering etc.

a pseudo query would look like this
Server s = new Server("host", port);
Project p = s.findProject("projectname");
View v = p.findView("viewname);
CommandProcessor cp = new CommandProcessor(v);
String[][] results = cp.execute("select viewmemberid from changerequest where Usr_ReleaseNumber = "1234"");
// do something with the results
s.disconnect();

I hope this helps
anil

17 Replies
Micro Focus Expert
Micro Focus Expert

RE: Using API, how to retrieve a list of CRs based on a Usr_ defined field

Jump to solution

the simplest query you can write is one using the CommandProcessor class, which encapsulates viewmemberlistmanagers, folderlistmanagers, sorting, querying, filtering etc.

a pseudo query would look like this
Server s = new Server("host", port);
Project p = s.findProject("projectname");
View v = p.findView("viewname);
CommandProcessor cp = new CommandProcessor(v);
String[][] results = cp.execute("select viewmemberid from changerequest where Usr_ReleaseNumber = "1234"");
// do something with the results
s.disconnect();

I hope this helps
anil

jlynch Absent Member.
Absent Member.

RE: Using API, how to retrieve a list of CRs based on a Usr_ defined field

Jump to solution
Thank you Anil, it helped immensely!!
0 Likes
Reply
mzrousar Absent Member.
Absent Member.

RE: Using API, how to retrieve a list of CRs based on a Usr_ defined field

Jump to solution
Anil - if I wanted to add an additional parameter to the execute query and search on the Property "ClosedOn", would it look like this:

String[][] results = cp.execute("select viewmemberid from changerequest where Usr_ReleaseNumber = "1234" and ClosedOn >= '01/01/2019'");

What date format is accepted? Thank you for your response.
0 Likes
Reply
Micro Focus Expert
Micro Focus Expert

RE: Using API, how to retrieve a list of CRs based on a Usr_ defined field

Jump to solution

Yes, you can execute any query through the CommandProcessor which is supported by the stcmd/stcmdEx engine.
The entire syntax of the select statement is available here
community.microfocus.com/.../stql-the-select-command-a-generalized-starteam-query-generation-language
You have the flexibility to provide the date format of your choice, per the rules of the grammar (-pattern)  in the link above.
(though for what its worth, the format is validated using the java SimpleDateFormat pattern matcher)
Take care
anil

0 Likes
Reply
haqattaqq
New Member.

RE: Using API, how to retrieve a list of CRs based on a Usr_ defined field

Jump to solution
Hi,
I've also got a question about how to format dates. If I wanted to use the query to filter on Dec 31, 2018 how exactly would I do that? Can you give me an example of the syntax?

eg:
String[][] results = cp.execute("select viewmemberid from changerequest where Usr_ReleaseNumber = '1234' and ClosedOn >= '12/31/2018'");

I tried that and got an error. I'm not sure what the syntax is for formatting dates.

Thanks!
0 Likes
Reply
Micro Focus Expert
Micro Focus Expert

RE: Using API, how to retrieve a list of CRs based on a Usr_ defined field

Jump to solution

you need to use -pattern (please see the select syntax here)
community.microfocus.com/.../stql-the-select-command-a-generalized-starteam-query-generation-language
and the pattern syntax here
docs.oracle.com/.../SimpleDateFormat.html


select viewmemberid from changerequest -pattern 'M/d/y' where Usr_ReleaeNumber = '1234' and ClosedOn geq '12/31/2018' 

you can test these commands out using stcmd or stcmdex, then integrate them into your java or c# programs.

take care
anil

0 Likes
Reply
haqattaqq
New Member.

RE: Using API, how to retrieve a list of CRs based on a Usr_ defined field

Jump to solution
Hi Anil,
I tried the syntax that you suggested and got an error. I then tried a variety of different things to see if I could figure out how to query based on dates. I noticed some interesting things.

I did a very simple query: select * from changerequest where ChangeNumber = 43688
and got back about 150 different columns. The values for numeric columns were fine - they show up correctly. However all of the data fields return as "M/d/y"... and I mean literally the value "M/d/y", not a date in that format. I'm guessing that I need to specify something in the query to see the actual dates and not the date mask.

So, a few questions:
1 Can you tell me how to query to see a real date? eg how do I fix this to show me the real created time? "select CreatedTime from changerequest where ChangeNumber = 43688" right now this literally returns "M/d/y"
2 Can you tell me how to query dates? eg "select * from changerequest -pattern 'M/d/y' where CreatedTime geq '12/31/2018' " throws an error.
0 Likes
Reply
Micro Focus Expert
Micro Focus Expert

RE: Using API, how to retrieve a list of CRs based on a Usr_ defined field

Jump to solution
What version of the sdk are you using?
Run stcmdex version
If you aren’t there, vacancy you upgrade to the latest sdk build 16.0.3.56 from microfocus supportline please?
I can verify that these commands work as expected with our latest release platform builds
Please let me know
Thanks
0 Likes
Reply
haqattaqq
New Member.

RE: Using API, how to retrieve a list of CRs based on a Usr_ defined field

Jump to solution
I'm on 16.0.3.28. Can you tell me how to upgrade to the latest SDK?
0 Likes
Reply
Micro Focus Expert
Micro Focus Expert

RE: Using API, how to retrieve a list of CRs based on a Usr_ defined field

Jump to solution
Sorry, Can you please upgrade to 16.0.3.56 sdk
If you still have issues, with the release build, please open s support incident and we will work with you to resolve it.
Thanks
0 Likes
Reply
Micro Focus Expert
Micro Focus Expert

RE: Using API, how to retrieve a list of CRs based on a Usr_ defined field

Jump to solution
You need to download it from microfocus supportline
Your microfocus support or account executive can help you
0 Likes
Reply
haqattaqq
New Member.

RE: Using API, how to retrieve a list of CRs based on a Usr_ defined field

Jump to solution
I updated to the latest version of the SDK and still have the same issue with querying by date. I was able to view the real saved dates, which is good. I also noticed that when I query like this:

select * from changerequest where changeNumber > 50000 order by ChangeNumber

the first change request that is returned is 50000... so it looks like > doesn't actually work and instead is >=.

Is there any alternative way to query based on date? I'd really like to be able to return CRs since a certain date.

I ran the above query and was able to see dates correctly. Then I ran this query (I just added the -pattern 'M/d/y' 😞

select * from changerequest -pattern 'M/d/y' where changeNumber > 50000 order by ChangeNumber

...it changes the *output* of all dates to just be the string 'M/d/y'. I'm guessing that this is either a bug or that's not how to query dates.

Anyways again I tried to query dates and it threw an error. Can you please try this code on your end and verify that it really does work? Exactly as is below:
"select * from changerequest -pattern 'M/d/y' where CreatedTime geq '12/31/2018' "
0 Likes
Reply
Micro Focus Expert
Micro Focus Expert

RE: Using API, how to retrieve a list of CRs based on a Usr_ defined field

Jump to solution
ok, so, i've been running stcmdex tests against our production server. here are working queries.
you should be able to test them yourself either in stcmd or stcmmdex or through java or c#


stcmdex select all from ChangeRequest -p "user:pwd@host:port/project/view"
stcmdex select all from ChangeRequest where ChangeNumber gt 14906 -p "user:pwd@host:port/project/view" (returns 1 row)
stcmdex select all from ChangeRequest where ChangeNumber gte 14906 -p "user:pwd@host:port/project/view" (returns 2 rows)
stcmdex select all from ChangeRequest -pattern "M/d/y" where EnteredOn gte "1/11/2019"-p "user:pwd@host:port/project/view" (returns 8 rows)

(instead of all, you can return groups of indivdual property values as well)

I generally recommend using the symbolic arguments gte, gt, eq etc, rather than the numerical ones >=, >, + etc.
It's probably less relevant when going through a java or c# program, you inevitably encounter msdos expansion issues when doing so through batch scripts in a command line.

I suspect the problem(s) / error(s) you have encountered have to do with the use of single quotes ' around arguments.
If you use double quoted arguments as in the example above, this should work and produce the results you need.

If you are still facing issues, please feel free to raise an incident, we will work with you to resolve it.
Take care
anil
0 Likes
Reply
haqattaqq
New Member.

RE: Using API, how to retrieve a list of CRs based on a Usr_ defined field

Jump to solution
Thanks Anil,
You're correct that the use of single quotes was what was causing problems. This works now. Whew!
0 Likes
Reply
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.