innovaphone Apps SDK Developer Forum

Registered 6 years 310 days

Wim 4127 Thursday, 28 May 2020, 05:55 PM

Database queries fail if table name contains capitals

When creating a table that has one or more capitals in it, the select statement fails because it checks on a table name that is all lowercase. It only works when working with table names containing only lowercase letters. Please fix.

05-28 15:49:25.744 Snom Provisioning@snom.com App instance constructed 05-28 15:49:25.771 Snom Provisioning@snom.com PostgreSQLDatabase(001032b8,0010316c)::DatabaseConnectComplete 05-28 15:49:25.771 Snom Provisioning@snom.com PostgreSQLDatabase(001032b8,0010316c)::Connected connected:1 05-28 15:49:25.771 Snom Provisioning@snom.com PostgreSQLDatabase(001032b8,0010316c)::ExecSQLV user:0010c844 flags:00000001 sqlCmd:CREATE TABLE IF NOT EXISTS "TableNameWithCapitals" (); 05-28 15:49:25.790 Snom Provisioning@snom.com PostgreSQLDatabase(001032b8,0010c844)::DatabaseExecSQLResult dataset: 0010d0b0 05-28 15:49:25.790 Snom Provisioning@snom.com PostgreSQLDatabase(001032b8,0010316c)::ExecSQLV user:0010c844 flags:00000001 sqlCmd:SELECT column_name FROM information_schema.columns WHERE table_name='TableNameWithCapitals'; 05-28 15:49:25.888 Snom Provisioning@snom.com PostgreSQLDatabase(001032b8,0010c844)::DatabaseExecSQLResult dataset: 0010c308 05-28 15:49:25.888 Snom Provisioning@snom.com PostgreSQLDatabase(001032b8,0010316c)::ExecSQLV user:0010c844 flags:00000001 sqlCmd:ALTER TABLE "TableNameWithCapitals" ADD COLUMN "name" VARCHAR(100) UNIQUE NOT NULL, ADD COLUMN "value" VARCHAR(500) 05-28 15:49:25.949 Snom Provisioning@snom.com PostgreSQLDatabase(001032b8,0010c844)::DatabaseExecSQLResult dataset: 0010d0b0 05-28 15:49:25.949 Snom Provisioning@snom.com PostgreSQLDatabase(001032b8,0010316c)::ExecSQLV user:00103374 flags:00000001 sqlCmd:SELECT name, value FROM TableNameWithCapitals 05-28 15:49:25.953 Snom Provisioning@snom.com PostgreSQLDatabase(001032b8,0010316c)::LastCommandFailed (42P01):ERROR: relation "tablenamewithcapitals" does not exist LINE 1: SELECT name, value FROM TableNameWithCapitals ^ 05-28 15:49:25.953 Snom Provisioning@snom.com PostgreSQLDatabase(001032b8,00103374)::DatabaseError error: DB_ERR_SQL_COMMAND_FAILED

Registered 6 years 310 days

Wim 4127 Thursday, 28 May 2020, 05:56 PM

Re: Database queries fail if table name contains capitals

Hi, additionally, when editing a post on this forum, the preformatted text loses it carriage returns, therefore the logging is all after each other and no longer line by line.

Moderator

Registered 15 years 179 days

Daniel Deterding (innovaphone) Friday, 29 May 2020, 07:00 AM

Re: Database queries fail if table name contains capitals

Hi Wim,

please post traces as txt attachment, not inside your post itself.

We can't fix your issue, as this is a database server side implementation and not an issue of our driver.

You can always login with SSH (first with admin/ipapps and then su root with the password iplinux) and connect directly to your database with an official postgresql tool:

psql -d yourdatabasename

https://www.postgresql.org/docs/11/app-psql.html

Then you can enter your queries manually to check the syntax etc. which is often much faster than programming inside the app until the query works.

You'll then see, that you have to wrap "strange" table names in double quotes:

SELECT * FROM "TableNameWithUpperCaseLetters";

I don't think there is real convention about naming tables, but in examples of the official postgresql documentation, you'll just see lowercase names with underscores: table_name_without_upper_case_letters ...

Like this, you don't need to care about quoting table names.

Greetings,

Daniel

Registered 6 years 310 days

Wim 4127 Friday, 29 May 2020, 01:18 PM

Re: Database queries fail if table name contains capitals

Hi Daniel,

that might well be the PostgreSQL default behavior, but, still there is some inconsistency then in the behavior of the SDK versus/in combination with postgresql.

If I create a table with table name that has capitals in psql, it will lowercase them and create the table. See the below output of the psql command line. Hence, if this is consistentently applied, a "select * from TestWithCapitals" succeeds, because it will lowercase it again and thus return the expected result. I would have not encountered this issue then at all.

It is apparantly the code behind TaskPostgreSQLInitTable that executes the command in such a way that it preserves capitalization, causing this mixture of capitalized and lowercase. I guess to be consistent, the code of the TaskPostgreSQLInitTable should also result in a table with lowercase name, unless the name is explicitly in double quotes.

-------------- CODE FROM PSQL VIA PUTTY ------------

snomprovisioningapp=> CREATE TABLE TestWithCapitals (ID int);

CREATE TABLE
snomprovisioningapp=> select table_name from information_schema.tables where table_schema='public'
;
 table_name
------------------
 testwithcapitals
(1 row)

Moderator

Registered 15 years 179 days

Daniel Deterding (innovaphone) Friday, 29 May 2020, 02:24 PM

Re: Database queries fail if table name contains capitals

It's an interesting behavior of PostgreSQL to auto lowercase tablenames in queries, but only, if they are not wrapped inside double quotes ...

The TaskPostgreSQLInitTable wraps the table name in the CREATE statement in double quotes, which is correct, as some table names won't work otherwise at all (with spaces for example).

I'll add a comment about this to the documentation.

Edit: hmm, maybe quoting the name yourself inside the table name would be really an option. But for now I'll leave it with the comment in the docs.

Greetings,

Daniel

Registered 6 years 310 days

Wim 4127 Monday, 1 June 2020, 04:02 PM

Re: Database queries fail if table name contains capitals

as some table names won't work otherwise at all

--> That statement is equally true if you do a select statement or anything else... but that would mean innovaphone has to parse the SQL statement which is hard and unnecessary since you can leave it up to the user... but then you have to be consistent and also leave it up to the user, also in the create.

Moderator

Registered 15 years 179 days

Daniel Deterding (innovaphone) Tuesday, 2 June 2020, 07:31 AM

Re: Database queries fail if table name contains capitals

Yes, I agree, that it would be better to let the user decide to escape or not escape the table name.

But changing this now could break already existing code, so I'll leave it with the comment in the SDK doc.

Greetings,

Daniel

You are here