SNOW-1852633: connection.getSchema returns the current schema UPPERCASE

[OS-veracardoso]

Please answer these questions before submitting your issue.
In order to accurately debug the issue this information is required. Thanks!

1. What version of JDBC driver are you using?
   3.20.0

2. What operating system and processor architecture are you using?
   WSL

3. What version of Java are you using?
   21

4. What did you do?

• Create a schema with mixed case (e.g. Test_SchemaMixedCase.
• Create a new connection setting the schema to the schema created
• Execute connection.getSchema()

5. What did you expect to see?

The returned value is TEST_SCHEMAMIXEDCASE instead of Test_SchemaMixedCase

6. Can you set logging to DEBUG and collect the logs?

   https://community.snowflake.com/s/article/How-to-generate-log-file-on-Snowflake-connectors

   Before sharing any information, please be sure to review the log and remove any sensitive
   information.

[sfc-gh-sghosh]

Hello @OS-veracardoso ,

Thanks for raising the issue.
By default, any object inside double quotes will be stored in capital letters.

Example: This will be stored as TEST_SCHEMAMIXEDCASE
String schemaName_new = "Test_SchemaMixedCase";
stmt.execute("create schema "+schemaName_new);

whereas if you use below one, it will store the schema as it is Test_SchemaMixedCase
String schemaName_new = ""Test_SchemaMixedCase"";
stmt.execute("create schema "+schemaName_new);

So its an application usage issue.
Also, the connection.getSchema() will return as per the above rules.

I hope its clear now.

Regards,
Sujan

[OS-veracardoso]

Hello @OS-veracardoso ,

Thanks for raising the issue. By default, any object inside double quotes will be stored in capital letters.

Example: This will be stored as TEST_SCHEMAMIXEDCASE String schemaName_new = "Test_SchemaMixedCase"; stmt.execute("create schema "+schemaName_new);

whereas if you use below one, it will store the schema as it is Test_SchemaMixedCase String schemaName_new = ""Test_SchemaMixedCase""; stmt.execute("create schema "+schemaName_new);

So its an application usage issue. Also, the connection.getSchema() will return as per the above rules.

I hope its clear now.

Regards, Sujan

Hi Sujan,

But that behaviour can cause issues.

Imagine I want to get the current schema from the connection so I can use it to create some other connection or to build a query or something similar. Since the schema name returned is TEST_SCHEMAMIXEDCASE and the original schema name is Test_SchemaMixedCase then the driver will return a messgae sayng the schema TEST_SCHEMAMIXEDCASE is not found.

That is the issue I'm facing currently.

Take for instance the following example:

1. When I the statement CREATE SCHEMA IF NOT EXISTS "Mixed_Case_Schema"; a new schema is created with the name Mixed_Case_Schema.

2. If I run SHOW SCHEMAS I get the schema with the exact same name (Mixed_Case_Schema)

3. When I create a connection and set the schema to Mixed_Case_Schema I can execute queries without any problem

4. But then, when I execute connection.getSchema() from the previously created connection I get MIXED_CASE_SCHEMA instead of Mixed_Case_Schema`.

[sfc-gh-sghosh]

Hello @OS-veracardoso ,

1. Anything you put in double quotes will be treated as case-sensitive, snowflake will store it as it is. Please see the documentation I mentioned below.
2. Now whether you do connection.getSchema, it will return same case-sensitive schema.
3. In your scenario, at step 3, when you set the schema to Mixed_Case_Schema I can execute queries without any problem, I hope you setting via variable and thats why new connection will store it in capital letter not as case sensitive. But if you set within double quotes then it will be case-sensitive.

This is as per the design of Snowflake.
Please have a look
https://docs.snowflake.com/en/sql-reference/identifiers-syntax
https://docs.snowflake.com/en/sql-reference/identifiers-syntax#label-delimited-identifier

Regards,
Sujan