CODEWITHSUNDEEP
snowflake-cloud-data-platform
oferco4
oferco4

Reputation: 13

use database with mixed case is not working via ODBC

I have a database with mixed case, i.e testDATABASE. I run(using ODBC) the query use database ""testDATABASE";", then I run the query use schema "PUBLIC", the query fail with the error: ERROR: SQL compilation error: Object does not exist, or operation cannot be performed. Error Code: 2043 Query = use schema "PUBLIC" when I run it not via odbc but in the notebook it works fine. same queries with database that does not contain mixed case works fine.

if i run use schema "testDATABASE"."PUBLIC" it runs OK via ODBC and notebook.

is there a known issue about it? how can i run it with 2 queries in ODBCand make it work? Thanks.

Upvotes: 1

Views: 126

Answers (1)

Rich Murnane
Rich Murnane

Reputation: 2940

In your question it looks like your use database command had double double quotes, but your schema didn't, perhaps that might be the issue.

Overall Suggestions :

  1. When you make object names MiXeD-CaSe it simply makes use of the objects more difficult, so I'd recommend trying to not do mixed case if you can avoid it. You may not be able to avoid this, that's OK, it's just a suggestion.

  2. if you can't avoid it, the only time I'd use the double quotes is when the object name (in this case, the database name) has mixed case.

In your case, you should be able to run (you may have to double-double quote it in ODBC):

use database "testDATABASE";

and then this - note no double quotes needed because it's not mixed case

use schema PUBLIC;

this document illustrates how you don't need to prefix the schema with the database:

https://docs.snowflake.com/en/sql-reference/sql/use-schema.html

  1. something else I recommend to folks getting started, for each user I like to set all the default context items (role, warehouse, namespace)

    ALTER USER rich SET DEFAULT_ROLE = 'RICH_ROLE';

    ALTER USER rich SET DEFAULT_WAREHOUSE = 'RICH_WH' ;

    ALTER USER rich SET DEFAULT_NAMESPACE = 'RICH_DB.TEST_SCHEMA';

Upvotes: 1

Related Questions