oData not returning any Collections - Forum - DataDirect Cloud - Progress Community

oData not returning any Collections

 Forum

oData not returning any Collections

  • Hi I have been setting up a datasource to access through oDATA on DataDirect Cloud, initially I had some success and a valid oData schema was generated, but now I can not get the schema to show the tables/collections, even though it is generated when configuring the data source.

    I believe I am following all correct steps, as it is not a complex process.

    I have two tables that show in the schema correctly as below:

    {
    "schemaName": "public",
    "includedTables": [
    "example",
    "other"
    ]
    }

    But when making the request the oData definition contains no collections.

    <service xmlns="http://www.w3.org/2007/app" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:app="http://www.w3.org/2007/app"xml:base="https://service.datadirectcloud.com/api/odata/RDSPostgres/">
     <workspace>
       <atom:title>Default</atom:title>
    </workspace>
    </service>
     
    Can anyone help?
  • When you issue the service document request, what does that return?

  • Was the Schema Map editor used to generate the JSON schema string above?  If not I would recommend that the editor be used to generate JSON string.  

    To do that clear the contents of the Schema Map field in the data source setup page and then click the Edit Configuration button below the schema Map field

    This will prompt you for the schema to expose and you will be able to select the schema in the data source that you want to expose and then it will allow you to select which tables from that schema are to be included.  The editor will also check whether there is a primary key defined for the tables that are selected.  OData requires that entities have a unique identifier to access a specific row in the data source.  DataDirect Cloud will use the primary key for a table as the unique identifier.  If the table selected to be included does not have a primary key the editor will be prompt for a column in the table to use as the unique identifier when the Save & Close button on the editor is clicked.

    If the Schema Map string is being generated by hand, which again I don't recommend in most cases, there are a couple of things to note.  First the schema names, table names and column names listed in the JSON string are case sensitive.  They must match the case of the names reported by the data source.  From the screen shot below you can see that the schema and table names are reported in all upper case by the data source that was connected to.  

    The other thing that could cause the OData service not to include a table is if there is not a primary key defined for the table.  Unfortunately the SQL editor does not show primary key information.  If case and names of the table and schema are correct, I would add a primary key column for the table in the JSON string as well.  If I wanted to map the EMPLOYEE table and use the ID column as the primary key, the JSON string would look like

    {
       "schemaName":"TEST10",
       "includedTables":[
          "EMPLOYEE"
       ],
       "tableMapping":[
          {"name":"EMPLOYEE","key":["ID"]}
       ]
    }

    The Schema Editor will take into account the name case and the primary key information so if the editor is used you should not need to worry about it.

    Hope this helps. 

  • Thanks for response, Yes as above when I open the url: service.datadirectcloud.com/.../RdsPostgrSql

    I get:

    <service xmlns="www.w3.org/.../app" xmlns:atom="www.w3.org/.../Atom" xmlns:app="www.w3.org/.../app" xml:base="service.datadirectcloud.com/.../">

    <workspace>

    <atom:title>Default</atom:title>

    </workspace>

    </service>

  • Thanks for the response... Yes I am using the editor to auto generate the json schema. I can also query the table through the SQL Testing tool, but the service request document still returns no tables.

    It does all work fine when I use the schema editor to use tables from the system schemas (information_schema, pg_catalog)... but not from my regular schema. I dont see how this can be user permissions as the SQL tool has full access. I am operating on AWS/RDS Postgres database.

    Thanks

  • So I figured this out eventually... it seems that in my test database, on postgres it is perfectly legal to create varchar columns with a null character size, all columns in my table had a null character size. These are ignored by oData. Once I set a character size all was fine!

  • Would it be useful to have a troubleshooting section in the help to suggestion something like this as a cause?
    From: david.cameron@skrill.com [mailto:bounce-davidcameronskrillcom@community.progress.com]
    Sent: Tuesday, June 23, 2015 5:09 AM
    To: TU.DD.Cloud@community.progress.com
    Subject: RE: [Technical Users - DataDirect Cloud] oData not returning any Collections
     

    So I figured this out eventually... it seems that in my test database, on postgres it is perfectly legal to create varchar columns with a null character size, all columns in my table had a null character size. These are ignored by oData. Once I set a character size all was fine!

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.

  • Thanks for following up with the problem and resolution.  Looks like we need to do a better job or reporting why things were omitted from the mode.