Topic
  • 16 replies
  • Latest Post - ‏2013-02-14T01:54:35Z by SystemAdmin
SystemAdmin
SystemAdmin
24948 Posts

Pinned topic REST interface, how do you formulate the query "<field> is not null"

‏2013-01-09T02:19:47Z |
I have a Perl REST module and I wish to formulate the query "<field> is not null". I can formulate "<field> is null" but supplying '&oslc_cm.query=<field> in ' to the request URL. But how do you specify "<field> is not null"?

https://jazz.net/wiki/bin/view/Main/RcmRestCmApi refers to http://open-services.net/bin/view/Main/CmQuerySyntaxV1 for the syntax of a query. What I would like to use is something like '&oslc_cm.query=<field> in ' where '*' would mean "any character" but that doesn't work.

So then, how do you do it?
Updated on 2013-02-14T01:54:35Z at 2013-02-14T01:54:35Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    24948 Posts

    Re: REST interface, how do you formulate the query "&lt;field&gt; is not null"

    ‏2013-01-15T17:02:18Z  
    Well we've gotten a lot of views but no answers. Do I need to submit a PMR?
  • GoodGulf
    GoodGulf
    610 Posts

    Re: REST interface, how do you formulate the query "&lt;field&gt; is not null"

    ‏2013-01-17T22:17:30Z  
    Andrew,

    First off, I feel your pain. I truly do.
    You mention referencing the CmQuerySyntaxV1 document, have you looked at the V2 of this (don't know what version of CQ you're running or if it supports it)...

    https://jazz.net/wiki/bin/view/Main/CqOslcV2#Query_Capabilities

    You can access V2 by setting "OSLC-Core-Version" to "2.0" in the header...
    e.g.
    
    my $request = 
    
    new HTTP::Request(
    'PUT', $upduri, 
    
    new HTTP::Headers( 
    "Content-Type" => 
    "application/json", 
    "OSLC-Core-Version" => 
    "2.0", 
    "Authorization" => 
    "Basic ".getCredentials(), 
    "Accept" => 
    "application/json", 
    "Connection" =>
    "close"), $json_text );
    

    This links to the oslc core spec for queries...

    http://open-services.net/bin/view/Main/OSLCCoreSpecQuery?sortcol=table;table=up#oslc_where

    I was wondering if you tried using oslc.where.
    
    &oslc.where=cq:<field>=
    "" or &oslc.where!=cq:<field>=
    ""
    


    I was playing with OSLC a while back but haven't had time lately.
    Since there's not even a hint of "IS NULL" or "IS NOT NULL", possibly the = and != would work.
    It's either that or OSLC has not matured that far (which is easy to believe based on what I've seen of it so far).

    BTW
    One thing I did learn was that you can use JSON to do inserts and updates (instead rdf and xml).

    For me anyway, JSON is so much easier to use with Perl than XML.

    Anyway, hope this helps.
  • SystemAdmin
    SystemAdmin
    24948 Posts

    Re: REST interface, how do you formulate the query "&lt;field&gt; is not null"

    ‏2013-01-17T22:41:48Z  
    • GoodGulf
    • ‏2013-01-17T22:17:30Z
    Andrew,

    First off, I feel your pain. I truly do.
    You mention referencing the CmQuerySyntaxV1 document, have you looked at the V2 of this (don't know what version of CQ you're running or if it supports it)...

    https://jazz.net/wiki/bin/view/Main/CqOslcV2#Query_Capabilities

    You can access V2 by setting "OSLC-Core-Version" to "2.0" in the header...
    e.g.
    <pre class="jive-pre"> my $request = new HTTP::Request( 'PUT', $upduri, new HTTP::Headers( "Content-Type" => "application/json", "OSLC-Core-Version" => "2.0", "Authorization" => "Basic ".getCredentials(), "Accept" => "application/json", "Connection" => "close"), $json_text ); </pre>
    This links to the oslc core spec for queries...

    http://open-services.net/bin/view/Main/OSLCCoreSpecQuery?sortcol=table;table=up#oslc_where

    I was wondering if you tried using oslc.where.
    <pre class="jive-pre"> &oslc.where=cq:<field>= "" or &oslc.where!=cq:<field>= "" </pre>

    I was playing with OSLC a while back but haven't had time lately.
    Since there's not even a hint of "IS NULL" or "IS NOT NULL", possibly the = and != would work.
    It's either that or OSLC has not matured that far (which is easy to believe based on what I've seen of it so far).

    BTW
    One thing I did learn was that you can use JSON to do inserts and updates (instead rdf and xml).

    For me anyway, JSON is so much easier to use with Perl than XML.

    Anyway, hope this helps.
    I saw the V2 where clause. I'm writing a general library. There are things in V1 that are not in V2 and there are things in V2 that are not in V1. It's not clear to me that I can easily switch between the two when and if I need to. Can I? It might be the way I need to go. If so then IBM please just say so!

    I'm not sure where the "where" clause would work. I had been using V1's in operator as in 'in ' for "is null" and wanted to use 'in ' for "is not null" but apparently "" is not considered a regex. Perhaps it'll work in V2 as V2 says something about the "wildcard" being "".

    I don't understand what's up with IBM or this OSLC committee thingy. It states 'boolean_op ::= "and"' - Hello! McFly! Have you heard of "or"? How about "not" and proper boolean expressions! We've been doing such stuff for decades now - why are you so incomplete?!?

    For me JSON vs XML is a wash. Both are horrendous formats and both have Perl modules to deal with them. Personally, in a purist sense, I don't see what JavaScript (the JS in JSON) has to do with anything here so I've just done the XML. XML::Simple seems to consume any XML sent and returns me a hash that I can work with. The format of the hash... now that seems to be some sort of industrial secret but give me Perl and it's debugger and I can hack around that...

    Thanks for your response.
  • GoodGulf
    GoodGulf
    610 Posts

    Re: REST interface, how do you formulate the query "&lt;field&gt; is not null"

    ‏2013-01-18T22:21:55Z  
    I saw the V2 where clause. I'm writing a general library. There are things in V1 that are not in V2 and there are things in V2 that are not in V1. It's not clear to me that I can easily switch between the two when and if I need to. Can I? It might be the way I need to go. If so then IBM please just say so!

    I'm not sure where the "where" clause would work. I had been using V1's in operator as in 'in ' for "is null" and wanted to use 'in ' for "is not null" but apparently "" is not considered a regex. Perhaps it'll work in V2 as V2 says something about the "wildcard" being "".

    I don't understand what's up with IBM or this OSLC committee thingy. It states 'boolean_op ::= "and"' - Hello! McFly! Have you heard of "or"? How about "not" and proper boolean expressions! We've been doing such stuff for decades now - why are you so incomplete?!?

    For me JSON vs XML is a wash. Both are horrendous formats and both have Perl modules to deal with them. Personally, in a purist sense, I don't see what JavaScript (the JS in JSON) has to do with anything here so I've just done the XML. XML::Simple seems to consume any XML sent and returns me a hash that I can work with. The format of the hash... now that seems to be some sort of industrial secret but give me Perl and it's debugger and I can hack around that...

    Thanks for your response.
    As far as I know, you can easily switch between the two by indicating the "OSLC-Core-Version" => "2.0" (see below) in your HTTP::Headers.
    Not including it defaults you to 1.0.

    An ad-hoc API it is not nor does it look like it's going to be any time soon.
    The date on that V2 document is 2010.

    I tried using a boolean or in the where clause and got this...
    
    Encountered 
    " <PN_PREFIX> "or 
    "" at line 1, column 27. Was expecting: <BOOLEAN_OP> ...
    


    As to JSON vs RDF/XML, I tried wading through a bit of RDF documentation and the meaning of some of that "industrial strength". It took a while but the headache did finally go away.

    I just found JSON easier to use.
    All I needed it for was to update the notes field of a CQ record in India from the US.
    If I use the perl API/cqperl, there's a built in "feature" which causes the login to wait 15 minutes before it finally logs into a remote database.

    I'm sure someone, somewhere thinks OSLC is just great.
    I think it's got a long way to go before it supports enough functionality to make it useful.

    That's just my opinion. I'm sure there are others.
  • SystemAdmin
    SystemAdmin
    24948 Posts

    Re: REST interface, how do you formulate the query "&lt;field&gt; is not null"

    ‏2013-01-18T23:29:02Z  
    • GoodGulf
    • ‏2013-01-18T22:21:55Z
    As far as I know, you can easily switch between the two by indicating the "OSLC-Core-Version" => "2.0" (see below) in your HTTP::Headers.
    Not including it defaults you to 1.0.

    An ad-hoc API it is not nor does it look like it's going to be any time soon.
    The date on that V2 document is 2010.

    I tried using a boolean or in the where clause and got this...
    <pre class="jive-pre"> Encountered " <PN_PREFIX> "or "" at line 1, column 27. Was expecting: <BOOLEAN_OP> ... </pre>

    As to JSON vs RDF/XML, I tried wading through a bit of RDF documentation and the meaning of some of that "industrial strength". It took a while but the headache did finally go away.

    I just found JSON easier to use.
    All I needed it for was to update the notes field of a CQ record in India from the US.
    If I use the perl API/cqperl, there's a built in "feature" which causes the login to wait 15 minutes before it finally logs into a remote database.

    I'm sure someone, somewhere thinks OSLC is just great.
    I think it's got a long way to go before it supports enough functionality to make it useful.

    That's just my opinion. I'm sure there are others.
    But I'm not even using HTTP::Request - I'm using REST::Client from CPAN. It has an addHeader method to inject a new header but I don't want to do that if I need to switch back. There seems to be a way to add the header for a single call. I'll have to play around with this. Ugh, so ugly...
  • SystemAdmin
    SystemAdmin
    24948 Posts

    Re: REST interface, how do you formulate the query "&lt;field&gt; is not null"

    ‏2013-02-08T00:46:37Z  
    • GoodGulf
    • ‏2013-01-18T22:21:55Z
    As far as I know, you can easily switch between the two by indicating the "OSLC-Core-Version" => "2.0" (see below) in your HTTP::Headers.
    Not including it defaults you to 1.0.

    An ad-hoc API it is not nor does it look like it's going to be any time soon.
    The date on that V2 document is 2010.

    I tried using a boolean or in the where clause and got this...
    <pre class="jive-pre"> Encountered " <PN_PREFIX> "or "" at line 1, column 27. Was expecting: <BOOLEAN_OP> ... </pre>

    As to JSON vs RDF/XML, I tried wading through a bit of RDF documentation and the meaning of some of that "industrial strength". It took a while but the headache did finally go away.

    I just found JSON easier to use.
    All I needed it for was to update the notes field of a CQ record in India from the US.
    If I use the perl API/cqperl, there's a built in "feature" which causes the login to wait 15 minutes before it finally logs into a remote database.

    I'm sure someone, somewhere thinks OSLC is just great.
    I think it's got a long way to go before it supports enough functionality to make it useful.

    That's just my opinion. I'm sure there are others.
    I've finally gotten back around to this. I'm still having problems. At this point the question really still remains - how do you formulate the query "<field> is not null"?

    I am now using OSLC 2.0 and the where clause but I still don't know how to formulate the actual query. The documentation is of zero help really.

    URLs and the like aside for the moment, one would think that the simplest form would be "<field> is not null". We'll use <field> = Requester from now on as in "...oslc.where=Requester is not null&..." (the full URL in my case is like "/cqweb/oslc/repo/MCBU/db/MobC/record/?rcm.type=Defect&oslc.where
    =cq:Requester = "foo"&oslc_cm.properties=id&oslc_cm.pageSize=1"). Using "Requester is not null" this gives me:

    Encountered " <PN_PREFIX> "Requester "" at line 1, column 1.
    Was expecting one of:
    "*" ...
    <IDENTIFIER> ...
    <IDENTIFIER> ...
    "*" ...
    <IDENTIFIER> ...
    "*" ...

    Seeing as you used cq:<field> I tried "cq:Requester is not null" and got:

    Encountered " <PN_PREFIX> "is "" at line 1, column 14.
    Was expecting:
    <IN_OP> ...

    OK, can I use "cq:Requester in [*]"?

    Encountered " "" " "" at line 1, column 18.
    Was expecting:
    <URI_REF_ESC> ...

    Alright it didn't like the bare * so let's do "cq:Requester in ":

    Unknown error

    Ugh!

    BTW if I use cq:Requester = "foo" I get:

    Encountered " <COMPARISON_OP> "= "" at line 1, column 14.
    Was expecting:
    <IN_OP> ...

    So "where" only supports the "<IN_OP>"?!?
  • SystemAdmin
    SystemAdmin
    24948 Posts

    Re: REST interface, how do you formulate the query "&lt;field&gt; is not null"

    ‏2013-02-08T01:19:47Z  
    I've finally gotten back around to this. I'm still having problems. At this point the question really still remains - how do you formulate the query "<field> is not null"?

    I am now using OSLC 2.0 and the where clause but I still don't know how to formulate the actual query. The documentation is of zero help really.

    URLs and the like aside for the moment, one would think that the simplest form would be "<field> is not null". We'll use <field> = Requester from now on as in "...oslc.where=Requester is not null&..." (the full URL in my case is like "/cqweb/oslc/repo/MCBU/db/MobC/record/?rcm.type=Defect&oslc.where
    =cq:Requester = "foo"&oslc_cm.properties=id&oslc_cm.pageSize=1"). Using "Requester is not null" this gives me:

    Encountered " <PN_PREFIX> "Requester "" at line 1, column 1.
    Was expecting one of:
    "*" ...
    <IDENTIFIER> ...
    <IDENTIFIER> ...
    "*" ...
    <IDENTIFIER> ...
    "*" ...

    Seeing as you used cq:<field> I tried "cq:Requester is not null" and got:

    Encountered " <PN_PREFIX> "is "" at line 1, column 14.
    Was expecting:
    <IN_OP> ...

    OK, can I use "cq:Requester in [*]"?

    Encountered " "" " "" at line 1, column 18.
    Was expecting:
    <URI_REF_ESC> ...

    Alright it didn't like the bare * so let's do "cq:Requester in ":

    Unknown error

    Ugh!

    BTW if I use cq:Requester = "foo" I get:

    Encountered " <COMPARISON_OP> "= "" at line 1, column 14.
    Was expecting:
    <IN_OP> ...

    So "where" only supports the "<IN_OP>"?!?
    While we're at it - how would you do CQ_COMP_OP_LIKE?
  • GoodGulf
    GoodGulf
    610 Posts

    Re: REST interface, how do you formulate the query "&lt;field&gt; is not null"

    ‏2013-02-13T15:32:00Z  
    I've finally gotten back around to this. I'm still having problems. At this point the question really still remains - how do you formulate the query "<field> is not null"?

    I am now using OSLC 2.0 and the where clause but I still don't know how to formulate the actual query. The documentation is of zero help really.

    URLs and the like aside for the moment, one would think that the simplest form would be "<field> is not null". We'll use <field> = Requester from now on as in "...oslc.where=Requester is not null&..." (the full URL in my case is like "/cqweb/oslc/repo/MCBU/db/MobC/record/?rcm.type=Defect&oslc.where
    =cq:Requester = "foo"&oslc_cm.properties=id&oslc_cm.pageSize=1"). Using "Requester is not null" this gives me:

    Encountered " <PN_PREFIX> "Requester "" at line 1, column 1.
    Was expecting one of:
    "*" ...
    <IDENTIFIER> ...
    <IDENTIFIER> ...
    "*" ...
    <IDENTIFIER> ...
    "*" ...

    Seeing as you used cq:<field> I tried "cq:Requester is not null" and got:

    Encountered " <PN_PREFIX> "is "" at line 1, column 14.
    Was expecting:
    <IN_OP> ...

    OK, can I use "cq:Requester in [*]"?

    Encountered " "" " "" at line 1, column 18.
    Was expecting:
    <URI_REF_ESC> ...

    Alright it didn't like the bare * so let's do "cq:Requester in ":

    Unknown error

    Ugh!

    BTW if I use cq:Requester = "foo" I get:

    Encountered " <COMPARISON_OP> "= "" at line 1, column 14.
    Was expecting:
    <IN_OP> ...

    So "where" only supports the "<IN_OP>"?!?
    Andrew,

    BTW if I use cq:Requester = "foo" I get:

    Encountered " <COMPARISON_OP> "= "" at line 1, column 14.
    Was expecting:
    <IN_OP> ...

    In "=" case, try it with no spaces before and after the operator

    cq:Requester="foo"
  • SystemAdmin
    SystemAdmin
    24948 Posts

    Re: REST interface, how do you formulate the query "&lt;field&gt; is not null"

    ‏2013-02-13T15:47:58Z  
    • GoodGulf
    • ‏2013-02-13T15:32:00Z
    Andrew,

    BTW if I use cq:Requester = "foo" I get:

    Encountered " <COMPARISON_OP> "= "" at line 1, column 14.
    Was expecting:
    <IN_OP> ...

    In "=" case, try it with no spaces before and after the operator

    cq:Requester="foo"
    Is the expression evaluater really that bad?

    I'm still trying to get "is not null"...
  • GoodGulf
    GoodGulf
    610 Posts

    Re: REST interface, how do you formulate the query "&lt;field&gt; is not null"

    ‏2013-02-13T15:59:36Z  
    While we're at it - how would you do CQ_COMP_OP_LIKE?
    There's no "OR", "IS NULL", "IS NOT NULL" and you're asking about "LIKE"? Seriously? ;-)

    The funny thing is, it looks like it's supported (just not by where).

    If you look in the oslc:service section of the database catalog, there's a section called "oslc:selectionDialog".
    Bring up the url in the oslc:dialog of the first entry in a web browser.
    The last argument in the url should have "&restrictType=false".

    It will bring up a dialog that allows you to search record types by unique key value.
    If you select "Defect" and type in 1 or 2 digits, it will return every record with an id that contains those digits.
    So LIKE is definitely there.

    Here's what I want to know about that dialog.
    How does it know what the unique key field(s) are for a given record type?
    I have been searching for where they stashed that little tidbit forever.
  • GoodGulf
    GoodGulf
    610 Posts

    Re: REST interface, how do you formulate the query "&lt;field&gt; is not null"

    ‏2013-02-13T16:00:32Z  
    Is the expression evaluater really that bad?

    I'm still trying to get "is not null"...
    Oh you bet it is.
    You can't even use single quotes around filter values.
    They HAVE to be double quotes.
  • SystemAdmin
    SystemAdmin
    24948 Posts

    Re: REST interface, how do you formulate the query "&lt;field&gt; is not null"

    ‏2013-02-13T16:17:45Z  
    • GoodGulf
    • ‏2013-02-13T16:00:32Z
    Oh you bet it is.
    You can't even use single quotes around filter values.
    They HAVE to be double quotes.
    The should have used somebody with more than 1-3 years experience to write the sucker. Very unprofessional indeed.
  • SystemAdmin
    SystemAdmin
    24948 Posts

    Re: REST interface, how do you formulate the query "&lt;field&gt; is not null"

    ‏2013-02-13T16:25:24Z  
    • GoodGulf
    • ‏2013-02-13T15:59:36Z
    There's no "OR", "IS NULL", "IS NOT NULL" and you're asking about "LIKE"? Seriously? ;-)

    The funny thing is, it looks like it's supported (just not by where).

    If you look in the oslc:service section of the database catalog, there's a section called "oslc:selectionDialog".
    Bring up the url in the oslc:dialog of the first entry in a web browser.
    The last argument in the url should have "&restrictType=false".

    It will bring up a dialog that allows you to search record types by unique key value.
    If you select "Defect" and type in 1 or 2 digits, it will return every record with an id that contains those digits.
    So LIKE is definitely there.

    Here's what I want to know about that dialog.
    How does it know what the unique key field(s) are for a given record type?
    I have been searching for where they stashed that little tidbit forever.
    Re: dialogs like selectionDialog: I'm a library - there will be no dialogboxs popping up in the middle of my caller's script.

    I'm not in front of the documentation right now but I thought that stuff was more related to full text search. We have full text search turned on but not all of my clients or users will necessarily have full text search enabled.

    Perhaps full text search is not required to be enabled and there is some way to supply the search phrase programmatically, I'll have to look at this later. However IMHO this interface is extremely difficult to use, badly documented and badly designed if simple things like "like" are so convoluted to use. Indeed the absence of "OR", "IS NULL" and "IS NOT NULL" speaks to the immaturity of this protocol/access method, it's incompleteness, etc. And the odd way you need to "incant" to get it to do things is horrible.

    Look I realize that OSLC (or whatever) is one spec and it wasn't necessarily written to fit Clearquest like a glove, however I would also say that this "obviously not fitting very well" OSLC might perhaps not be the proper protocol to hang one's hat on...

    ... Back to the trenches...
  • SystemAdmin
    SystemAdmin
    24948 Posts

    Re: REST interface, how do you formulate the query "&lt;field&gt; is not null"

    ‏2013-02-13T16:25:38Z  
    • GoodGulf
    • ‏2013-02-13T15:59:36Z
    There's no "OR", "IS NULL", "IS NOT NULL" and you're asking about "LIKE"? Seriously? ;-)

    The funny thing is, it looks like it's supported (just not by where).

    If you look in the oslc:service section of the database catalog, there's a section called "oslc:selectionDialog".
    Bring up the url in the oslc:dialog of the first entry in a web browser.
    The last argument in the url should have "&restrictType=false".

    It will bring up a dialog that allows you to search record types by unique key value.
    If you select "Defect" and type in 1 or 2 digits, it will return every record with an id that contains those digits.
    So LIKE is definitely there.

    Here's what I want to know about that dialog.
    How does it know what the unique key field(s) are for a given record type?
    I have been searching for where they stashed that little tidbit forever.
    Re: dialogs like selectionDialog: I'm a library - there will be no dialogboxs popping up in the middle of my caller's script.

    I'm not in front of the documentation right now but I thought that stuff was more related to full text search. We have full text search turned on but not all of my clients or users will necessarily have full text search enabled.

    Perhaps full text search is not required to be enabled and there is some way to supply the search phrase programmatically, I'll have to look at this later. However IMHO this interface is extremely difficult to use, badly documented and badly designed if simple things like "like" are so convoluted to use. Indeed the absence of "OR", "IS NULL" and "IS NOT NULL" speaks to the immaturity of this protocol/access method, it's incompleteness, etc. And the odd way you need to "incant" to get it to do things is horrible.

    Look I realize that OSLC (or whatever) is one spec and it wasn't necessarily written to fit Clearquest like a glove, however I would also say that this "obviously not fitting very well" OSLC might perhaps not be the proper protocol to hang one's hat on...

    ... Back to the trenches...
  • GoodGulf
    GoodGulf
    610 Posts

    Re: REST interface, how do you formulate the query "&lt;field&gt; is not null"

    ‏2013-02-13T16:31:35Z  
    The should have used somebody with more than 1-3 years experience to write the sucker. Very unprofessional indeed.
    Don't know if you saw this one, it was up the chain a bit...
    It was in response to your "LIKE" question...

    There's no "OR", "IS NULL", "IS NOT NULL" and you're asking about "LIKE"? Seriously? ;-)

    The funny thing is, it looks like it's supported (just not by where).

    If you look in the oslc:service section of the database catalog, there's a section called "oslc:selectionDialog".
    Bring up the url in the oslc:dialog of the first entry in a web browser.
    The last argument in the url should have "&restrictType=false".

    It will bring up a dialog that allows you to search record types by unique key value.
    If you select "Defect" and type in 1 or 2 digits, it will return every record with an id that contains those digits.
    So LIKE is definitely there.

    Here's what I want to know about that dialog.
    How does it know what the unique key field(s) are for a given record type?
    I have been searching for where they stashed that little tidbit forever.
  • SystemAdmin
    SystemAdmin
    24948 Posts

    Re: REST interface, how do you formulate the query "&lt;field&gt; is not null"

    ‏2013-02-14T01:54:35Z  
    • GoodGulf
    • ‏2013-02-13T15:59:36Z
    There's no "OR", "IS NULL", "IS NOT NULL" and you're asking about "LIKE"? Seriously? ;-)

    The funny thing is, it looks like it's supported (just not by where).

    If you look in the oslc:service section of the database catalog, there's a section called "oslc:selectionDialog".
    Bring up the url in the oslc:dialog of the first entry in a web browser.
    The last argument in the url should have "&restrictType=false".

    It will bring up a dialog that allows you to search record types by unique key value.
    If you select "Defect" and type in 1 or 2 digits, it will return every record with an id that contains those digits.
    So LIKE is definitely there.

    Here's what I want to know about that dialog.
    How does it know what the unique key field(s) are for a given record type?
    I have been searching for where they stashed that little tidbit forever.
    It seems like this selectionDialog junk has to do with web browsers and prompting the user interactively. This does not provide any kind of "like" functionality to a script running in cron that wishes to process say 'all defects where Project like "%new%"' which are the kinds of requests that my library is intended on servicing...