Scripting a "CREATE TABLE" in Snowflake does not account for case sensitive table names

I'm trying to script a CREATE TABLE statement by right clicking on the table name, choosing "Script Table...", then choosing the CREATE radio button under the Scripting Type header, Options - Format SQL is checked, Statement delimiter is ';', Output Destination is SQL Commander: New Editor.

The table I'm selecting is _SW_807c1a63_b070_4ca5_a84c_a0c566698f4a_Seq_000002

(Note the use of lower case letters in the table name)

The error I'm getting is the following:

An error occurred while executing the database request for:

Short message:

An error occurred while performing the operation:

SQL compilation error:

Table '<OBFUSCATED>.PUBLIC._SW_807C1A63_B070_4CA5_A84C_A0C566698F4A_SEQ_000002' does not exist or not authorized.

Long Message:

SQL compilation error:

Table '<OBFUSCATED>.PUBLIC._SW_807C1A63_B070_4CA5_A84C_A0C566698F4A_SEQ_000002' does not exist or not authorized.

Details:

   Type: com.onseven.dbvis.l.e.r

Stack Trace:

net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:

Table '<OBFUSCATED>.PUBLIC._SW_807C1A63_B070_4CA5_A84C_A0C566698F4A_SEQ_000002' does not exist or not authorized.

   at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:139)

   at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:64)

   at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:485)

   at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:362)

   at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:502)

   at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:247)

   at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:186)

   at net.snowflake.client.core.SFStatement.execute(SFStatement.java:790)

   at net.snowflake.client.jdbc.SnowflakeStatementV1.executeInternal(SnowflakeStatementV1.java:295)

   at net.snowflake.client.jdbc.SnowflakeStatementV1.execute(SnowflakeStatementV1.java:359)

   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

   at java.lang.reflect.Method.invoke(Method.java:498)

   at com.onseven.dbvis.jb.c.c.b(Z:2186)

   at com.onseven.dbvis.jb.c.g$_b.call(Z:1369)

   at java.util.concurrent.FutureTask.run(FutureTask.java:266)

[wrapped] com.onseven.dbvis.l.e.r: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:

Table '<OBFUSCATED>.PUBLIC._SW_807C1A63_B070_4CA5_A84C_A0C566698F4A_SEQ_000002' does not exist or not authorized.

   at com.onseven.dbvis.l.e.k.qe(Z:729)

   at com.onseven.dbvis.l.e.bb.ie(Z:982)

   at com.onseven.dbvis.l.e.bb.sd(Z:2081)

   at com.onseven.dbvis.ab.c.p.f(Z:2499)

   at com.onseven.dbvis.db.d.f.l(Z:2320)

   at com.onseven.dbvis.db.d.f.d(Z:1164)

   at com.onseven.dbvis.l.e.q.c(Z:3364)

   at com.onseven.dbvis.l.e.q.qe(Z:1160)

   at com.onseven.dbvis.l.e.bb.ie(Z:982)

   at com.onseven.dbvis.l.e.bb.sd(Z:2081)

   at com.onseven.dbvis.objects.d.c.n.c(Z:2365)

   at com.onseven.dbvis.objects.d.c.n.b(Z:1811)

   at com.onseven.dbvis.objects.d.c.n$1.b(Z:346)

   at com.onseven.dbvis.l.e.b.j.qe(Z:188)

   at com.onseven.dbvis.l.e.bb.ie(Z:982)

   at com.onseven.dbvis.l.e.l.c(Z:1374)

   at com.onseven.dbvis.l.e.l.doInBackground(Z:1521)

   at javax.swing.SwingWorker$1.call(SwingWorker.java:295)

   at java.util.concurrent.FutureTask.run(FutureTask.java:266)

   at javax.swing.SwingWorker.run(SwingWorker.java:334)

   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

   at java.lang.Thread.run(Thread.java:748)

System Information:

Product: DbVisualizer Pro 11.0.5 [Build #3113]

OS: Linux

OS Version: 4.15.0-122-generic

OS Arch: amd64

Java Version: 1.8.0_201

Java VM: Java HotSpot(TM) 64-Bit Server VM

Java Vendor: Oracle Corporation

Java Home: /usr/lib/jvm/java-8-oracle/jre

DbVis Home: /opt/dbvis_11.0.5

User Home: /home/<USER>

PrefsDir: /home/<USER>/.dbvis

SessionId: 994

BindDir: /home/<USER>/.dbvis/[0]

Hi Ulf.  

Yes, I just tried the scripting a create table with the delimited identifiers turned on  (in Beta 120.3119) with the same results.  (Does this count as reported bug for beta 120? <wink>)

An error occurred while performing the operation:

SQL compilation error:

Table '<OBFUSCATED>.PUBLIC._SW_0048FFAC_4764_4465_A045_71E398B418BC_SEQ_000001' does not exist or not authorized.

Table name is actually _SW_0048ffac_4764_4465_a045_71e398b418bc_Seq_000001

I did find a section under the database drivers (tool properties) where you can specify situations where delimited identifiers should be used.  One of the options was for scripting.  I enabled the scripting checkbox (indicating delimited identifiers should be used when scripting), disconnected from snowflake, then reconnected, attempt to script a CREATE table for a table with lowercase characters in the name and I got the same result, "<uppercased table name> does not exist or not authorized"

There is no SQL generated by the Script action as it errors before producing the SQL.

Here's how to replicate

DDL:

-- Create table with case sensitive table name
CREATE TABLE 
    "POC_EXAMPLE_DB"."PUBLIC"."00_Customer_Test"
    ( 
        contact_id   INTEGER 
        , star_id    VARCHAR(50) 
        , first_name VARCHAR(50) 
        , last_name  VARCHAR(50) 
        , state      VARCHAR(20) 
        , zip        VARCHAR(12) 
    );

-- Populate table with case sensitive table name
INSERT INTO "00_Customer_Test" (CONTACT_ID, STAR_ID, FIRST_NAME, LAST_NAME, STATE, ZIP) VALUES (1, 'a1b2c3d4-e5f6-abcd-1234-000deadbeef0', 'Alan', 'Alda', 'ME', '00001');
INSERT INTO "00_Customer_Test" (CONTACT_ID, STAR_ID, FIRST_NAME, LAST_NAME, STATE, ZIP) VALUES (2, 'a1b2c3d4-e5f6-abcd-1234-111deadbeef1', 'Bob', 'Barker', 'NH', '11112');
INSERT INTO "00_Customer_Test" (CONTACT_ID, STAR_ID, FIRST_NAME, LAST_NAME, STATE, ZIP) VALUES (3, 'a1b2c3d4-e5f6-abcd-1234-222deadbeef2', 'Charlie', 'Chaplin', 'VT', '22223');
INSERT INTO "00_Customer_Test" (CONTACT_ID, STAR_ID, FIRST_NAME, LAST_NAME, STATE, ZIP) VALUES (4, 'a1b2c3d4-e5f6-abcd-1234-333deadbeef3', 'Danny', 'Devito', 'RI', '33334');
INSERT INTO "00_Customer_Test" (CONTACT_ID, STAR_ID, FIRST_NAME, LAST_NAME, STATE, ZIP) VALUES (5, 'a1b2c3d4-e5f6-abcd-1234-444deadbeef4', 'Emilio', 'Estevez', 'MA', '44445');
INSERT INTO "00_Customer_Test" (CONTACT_ID, STAR_ID, FIRST_NAME, LAST_NAME, STATE, ZIP) VALUES (6, 'a1b2c3d4-e5f6-abcd-1234-555deadbeef5', 'Farrah', 'Fawcett', 'CT', '55556');

1.  Execute above statements to create and populate the table

2. Right click on table name, choose Script Table, set options to following image

Hope this helps.

-- Tom

It appears the non-quoting of table names is pervasive for Snowflake.

Here is the result of trying to create a new table with a lower case table name (because the lowercase table name is not quoted in the CREATE TABLE statement, this will result in an upper case table name in Snowflake).

My t-shirt size is XXL