createRecords(): Invalid schema name: 7 ERROR

REST API
1

Hello,

I have a local installation of DF and I am trying to create a new record in Postgresql. I am currently trying in the API Docs section.

  1. I check the list of available tables using getTables() (http://df.carpediem.pe:80/rest/creches?names_only=true&include_schemas=true) and I get the following response:

    {
    “resource”: [
    “crechesSchema.crecheTable”,
    “crechesSchema.crecheVotes”,
    "_schema/",
    "_schema/crechesSchema.crecheTable",
    "_schema/crechesSchema.crecheVotes"
    ]
    }

I then proceed by inserting records in the “crechesSchema.crecheVotes”, table using the createRecords() function:
table_name: crechesSchema.crecheVotes

body: {
    "record": [
        { "creche_id":  1, 
          "vote": "NO_PLACES"
        }
     ]
}

but I get the following error:

{
  "error": [
    {
      "context": null,
      "message": "Failed to create records in 'crechesSchema.crecheVotes'.\nSQLSTATE[3F000]: Invalid schema name: 7 ERROR:  schema "crechesschema" does not exist",
      "code": 500
    }
  ]
}

HOWEVER, if I do the same thing with the other table, it works just fine.

Does anyone have a clue about it?

Thanks,

2

If I’m reading this correctly, your table’s name is crechesSchema.crecheVotes correct?

If so you should know that we don’t currently support using periods in object names. Most database systems use period notation to delineate hierarchies.
For example I can refer to table within another database in my query by referencing otherdatabase.othertable

So when you try insert a record, DreamFactory is trying to connect to a database called crechesSchema and a table called crecheVotes, not a table called crechesSchema.crecheVotes.

3

Hello drewpearce,

Okay. In that case, I wouldn’t expect it to work in the case of the other table named crechesSchema.crecheTable but strangely it does. I also tried with the unqualified name i.e. “crecheVotes”, but it doesn’t work either.

So what is the correct way of using Postgresql database? Using a schema is inevitable in Postgresql and the default one is called “public”. Should all the tables be in the public schema ?

On a separate note, is it possible to update a table without posting the values themselves? For example, given a table with fields Table(id, count), is it possible to update the table to have the equivalent effect of the following query:
UPDATE Table set count = count + 1 WHERE id = 1;

I know there is the updateRecord() function but it requires providing the value of the column to be updated. However, the value may have changed since accessing it.

Thanks!

4

I’m sorry I told you incorrectly re: the period notation – I was just able to successfully post records to my pgs database using this notation.

What tool are you using for these calls?

5

For the count+1 scenario I believe you would need to do this with a stored procedure.

6

Yes, it works. But not always. I am not sure what the problem is though. I am using the API Docs in the Admin interface of dreamfactory as well as through angularjs. I get the same result in both cases.

One table works but the other doesn’t. Creating records directly with SQL works of course with the same user.

If you have a suggestion for where to look for the issue, let me know.

Thanks for the input about count+1.