locked
ODATA syntax - column name RRS feed

  • Question

  • Hi,

    My XML flux is below,

    I try to filter by using the field Modifié (means modified) to permit to have only record for a specific dates. so each time I onbtain a 400 Bad request.

    So I test the following syntax

    http://sps3:40173/_vti_bin/listdata.svc/TempZIPFiles?$filter=Modifié gt DateTime'2010-01-01T00:00:00'

    http://sps3:40173/_vti_bin/listdata.svc/TempZIPFiles?$filter=Modifié ne null

    http://sps3:40173/_vti_bin/listdata.svc/TempZIPFiles?$filter=Modifié ne null

    http://sps3:40173/_vti_bin/listdata.svc/TempZIPFiles?$filter=Modifiié gt DateTime'2010-01-01T00:00:00'

    Each time I obtain 404, but if I did http://sps3:40173/_vti_bin/listdata.svc/TempZIPFiles(25)/Modifié, I obtain result:

    <?xml version="1.0" encoding="utf-8" standalone="yes" ?>
      <Modifié p1:type="Edm.DateTime" xmlns:p1="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices">2012-09-19T16:43:25</Modifié>

    So my assumption is maybe the issue is not related to Datetime but about the name.

    I try to obtain results by filter from the field 2tatDApprobation with the query http://sps3:40173/_vti_bin/listdata.svc/TempZIPFiles?$filter=ÉtatDApprobation ne null and I obtain a 404. I don't see any log in event viewer. 

    My assumption is WCF ODATA from Sharepoint missunderstood my column name, maybe my syntax is wrong.

    I took time to read http://msdn.microsoft.com/en-us/library/dd728283.aspx and http://msdn.microsoft.com/en-us/library/gg309461.aspx#BKMK_filter and http://www.odata.org/documentation/uri-conventions#TopSystemQueryOption but dont find any way.

    Do you have any point of view?

      <?xml version="1.0" encoding="utf-8" standalone="yes" ?> 
    - <entry xml:base="http://sps3:40173/_vti_bin/listdata.svc/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" m:etag="W/"3"" xmlns="http://www.w3.org/2005/Atom">
      <id>http://sps3:40173/_vti_bin/listdata.svc/TempZIPFiles(25)</id> 
      <title type="text" /> 
      <updated>2012-09-19T16:43:25+02:00</updated> 
    - <author>
      <name /> 
      </author>
      <link m:etag=""{C274462C-3AB5-4C68-958E-0F8923312727},6"" rel="edit-media" title="TempZIPFilesItem" href="TempZIPFiles(25)/$value" /> 
      <link rel="edit" title="TempZIPFilesItem" href="TempZIPFiles(25)" /> 
      <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/CrééPar" type="application/atom+xml;type=entry" title="CrééPar" href="TempZIPFiles(25)/CrééPar" /> 
      <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/ModifiéPar" type="application/atom+xml;type=entry" title="ModifiéPar" href="TempZIPFiles(25)/ModifiéPar" /> 
      <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/ExtraitVers" type="application/atom+xml;type=entry" title="ExtraitVers" href="TempZIPFiles(25)/ExtraitVers" /> 
      <category term="Microsoft.SharePoint.DataService.TempZIPFilesItem" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" /> 
      <content type="application/octetstream" src="http://sps3:40173/Temp%20%20ZIP%20Files/alex.zip" /> 
    - <m:properties xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices">
      <d:ID m:type="Edm.Int32">25</d:ID> 
      <d:IDDuTypeDeContenu>0x0101005CF79BC1F396A34B9EECA7192AD0D722</d:IDDuTypeDeContenu> 
      <d:TypeDeContenu>Document</d:TypeDeContenu> 
      <d:Créé m:type="Edm.DateTime">2012-09-19T15:25:15</d:Créé> 
      <d:CrééParId m:type="Edm.Int32">1073741823</d:CrééParId> 
      <d:Modifié m:type="Edm.DateTime">2012-09-19T16:43:25</d:Modifié> 
      <d:ModifiéParId m:type="Edm.Int32">1073741823</d:ModifiéParId> 
      <d:SourceDeLaCopie m:null="true" /> 
      <d:ÉtatDApprobation>0</d:ÉtatDApprobation> 
      <d:CheminDAccès>/Temp ZIP Files</d:CheminDAccès> 
      <d:ExtraitVersId m:type="Edm.Int32" m:null="true" /> 
      <d:Nom>alex.zip</d:Nom> 
      <d:ÉtatDuVirus>168529698</d:ÉtatDuVirus> 
      <d:VersionActuelle m:type="Edm.Boolean">true</d:VersionActuelle> 
      <d:Owshiddenversion m:type="Edm.Int32">3</d:Owshiddenversion> 
      <d:Version>1.0</d:Version> 
      <d:Titre m:null="true" /> 
      </m:properties>
      </entry>

    Best regards,

    Alexandre


    Best regards, Alexandre http://alexandrebarault.wordpress.com

    • Moved by LeoTang Thursday, September 20, 2012 2:37 AM (From:Windows Communication Foundation (WCF))
    Wednesday, September 19, 2012 3:28 PM

All replies

  • Hi,

    The correct syntax to filter based on datetime values is something like this

    http://localhost:800/_vti_bin/ListData.svc/Tasks?$filter=Modified gt DateTime'2012-09-18'

    Are you sure you are not making a mistake in the field name? From the above content, i can see that the name of the field is Modifie

    but one of the urls that you have pasted above (the correct one in my opinion) uses a different name for the field (Modifiie)

    http://sps3:40173/_vti_bin/listdata.svc/TempZIPFiles?$filter=Modifiié gt DateTime'2010-01-01T00:00:00'


    Please "Mark as Answer" if a post has answered your question or "Vote as Helpful" if it was helpful in some way. Here's why

    Wednesday, September 19, 2012 3:46 PM
  • Hi,

    Thank you for your reply.

    Effectively I did a mistake, It's effectively http://sps3:40173/_vti_bin/listdata.svc/TempZIPFiles?$filter=Modifié gt DateTime'2010-01-01T00:00:00' and it doesn't work. I am sure I didn't do any mistake about the name.

    If I use http://sps3:40173/_vti_bin/listdata.svc/TempZIPFiles?$filter=Modifié gt DateTime'2010-01-01T00:00:00' => error 400 and if I use http://sps3:40173/_vti_bin/listdata.svc/TempZIPFiles(25)/Modifié I obtain 

    <?xml version="1.0" encoding="utf-8" standalone="yes" ?>
     <Modifié p1:type="Edm.DateTime" xmlns:p1="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices">2012-09-19T16:43:25</Modifié>

    Best regards,

    Alexandre



    Best regards, Alexandre http://alexandrebarault.wordpress.com

    Wednesday, September 19, 2012 3:53 PM
  • Hi,

    Note that the literals in the filter are case sensitive. So it's not DateTime'...' but datetime'...'. As shown here: http://www.odata.org/documentation/overview#AbstractTypeSystem

    I'm not sure about SharePoint, but the error response might contain more information as to why it failed.

    Thanks,


    Vitek Karas [MSFT]

    Thursday, September 20, 2012 7:11 AM
    Moderator
  • Hi,

    Thank you Vitek.

    I found the right way, finally you need to use UTF8 encoded for URL.

    So my Url is coming as :

    http://sps3:40173/_vti_bin/listdata.svc/TempZIPFiles?$filter=Modifi%C3%A9%20gt%20datetime'2012-09-20T01:01:01'

    http://www.url-encode-decode.com/

    I notice another issue, I add a new file right now.

    So I have 3 files with the modified date from yesterday and one from now.

    I notice if I put http://sps3:40173/_vti_bin/listdata.svc/TempZIPFiles?$filter=Modifi%C3%A9%20gt%20datetime'2012-09-19T00:00:00'

    I obtain 4 files : Correct

    I notice if I put http://sps3:40173/_vti_bin/listdata.svc/TempZIPFiles?$filter=Modifi%C3%A9%20gt%20datetime'2012-09-20T00:00:00'

    I obtain 0 files : Not Correct, I am expecting to obtain one file.

    Do you have any idea?

    Best regards,

    Alexandre


    Best regards, Alexandre http://alexandrebarault.wordpress.com

    • Marked as answer by alexandreb Thursday, September 20, 2012 12:45 PM
    • Unmarked as answer by alexandreb Thursday, September 20, 2012 3:18 PM
    Thursday, September 20, 2012 11:46 AM
  • Sorry - no idea.

    You could try to run the first query which returns the 4 files and see what are the values for the Modifiee property for all of them.

    Thanks,


    Vitek Karas [MSFT]

    Thursday, September 20, 2012 12:16 PM
    Moderator
  • It's a solution, nevertheless I wouldn't get all datas and filters later, it's not really efficient.

    And my assumption is the datetime isn't fully supported by ODATA/Sharepoint.

    I need to find another ways.

    Best regards,

    Alexandre


    Best regards, Alexandre http://alexandrebarault.wordpress.com

    Thursday, September 20, 2012 12:45 PM
  • I didn't mean that as a solution, I meant it as a way to debug the problem.

    It's just a way to validate that the expectation of getting 1 result back is actually valid. Note that you specify "gt" so if the 4th file is added with the date exactly equal to the one you're comparing against, it will not be returned.

    Thanks,


    Vitek Karas [MSFT]

    Thursday, September 20, 2012 1:08 PM
    Moderator
  • Hi,

    The modified date from the files are :

    • 19/09/2012 16:43
    • 20/09/2012 13:26
    • 19/09/2012 16:43
    • 20/09/2012 13:43

    http://sps3:40173/_vti_bin/listdata.svc/TempZIPFiles?$filter=Modifi%C3%A9%20gt%20datetime'2012-09-19T00:00:00'

    >> return Two files :

    • 20/09/2012 13:26
    • 20/09/2012 13:43

    >>http://sps3:40173/_vti_bin/listdata.svc/TempZIPFiles?$filter=Modifi%C3%A9%20gt%20datetime'2012-09-20T00:00:00'

    return 0 File

    >>http://sps3:40173/_vti_bin/listdata.svc/TempZIPFiles?$filter=Modifi%C3%A9%20gt%20datetime'2012-09-18T00:00:00'

    return 4 files

    I change by Greater than or equal

    >>http://sps3:40173/_vti_bin/listdata.svc/TempZIPFiles?$filter=Modifi%C3%A9%20ge%20datetime'2012-09-20T13:40:00'

    return 2 files

    • 20/09/2012 13:26
    • 20/09/2012 13:43

    Time from the server during the testing 20/09/2012 17H32

    My assumption is the time isn't threated by sharepoint/odata

    Best regards,

    Alexandre


    Best regards, Alexandre http://alexandrebarault.wordpress.com

    Thursday, September 20, 2012 3:37 PM
  • This really does look weird. I would suggest asking on a SharePoint forum. For example http://social.msdn.microsoft.com/Forums/en-US/sharepoint2010programming/threads

    This feels like a problem with time 00:00:00 - maybe try 00:00:01 (just for the kicks). It would be weird anyway.

    Thanks,


    Vitek Karas [MSFT]

    Thursday, September 20, 2012 3:48 PM
    Moderator