quote rendering question

[Manuel Rossetti]

Hello, 

I am using JOOQ to make a tutorial for my students.  I see the following rendering behavior:

import static org.jooq.impl.DSL.*; // this import allows for the fluent use of the DSL class
import static org.jooq.impl.SQLDataType.*; // this import is so that you don't have to type SQLDataType.INTEGER etc

// this statement builds a step in a SQL build process that defines a table creation
CreateTableConstraintStep step = createTable("Supplier")
.column("SNUM", INTEGER)
.column("SNAME", VARCHAR(16).nullable(false))
.column("STATUS", INTEGER.nullable(false))
.column("CITY", VARCHAR(20).nullable(false))
.constraints(
primaryKey("SNUM"),
unique("SNAME"));
// the statement can be printed, etc. But, more usefully it can be executed within a context.
System.out.println(step.toString());
String sql = step.getSQL();
System.out.println(sql);
System.out.println();

CreateTableConstraintStep step1 = createTable(table("Supplier"))
.column(field("SNUM", INTEGER))
.column(field("SNAME", VARCHAR(16).nullable(false)))
.column(field("STATUS", INTEGER.nullable(false)))
.column(field("CITY", VARCHAR(20).nullable(false)))
.constraints(primaryKey(field("SNUM")),
unique(field("SNAME")));

System.out.println(step1.toString());
String sql1 = step1.getSQL();
System.out.println(sql1);
System.out.println();

These produce this output:

create table "Supplier" (

  "SNUM" integer null,

  "SNAME" varchar(16) not null,

  "STATUS" integer not null,

  "CITY" varchar(20) not null,

  primary key ("SNUM"),

  unique ("SNAME")

)

create table "Supplier" ("SNUM" integer null, "SNAME" varchar(16) not null, "STATUS" integer not null, "CITY" varchar(20) not null, primary key ("SNUM"), unique ("SNAME"))

create table Supplier (

  SNUM integer null,

  SNAME varchar(16) not null,

  STATUS integer not null,

  CITY varchar(20) not null,

  primary key (SNUM),

  unique (SNAME)

)

create table Supplier (SNUM integer null, SNAME varchar(16) not null, STATUS integer not null, CITY varchar(20) not null, primary key (SNUM), unique (SNAME))

So, I am wondering why the the resulting strings are rendered with and without quotes.

Unless I am missing something, I thought that they should be rendered the same.  If they are supposed to be different by design, which SQL building approach is more recommended?

[Rob Sargent]

What dialect are you using (target RDBMS)?

[Manuel Rossetti]

The code that I showed was based entirely on the DSL class, which does not require the specification of the SQL dialect.   With that said, I am using Apache Derby.  

I found the issue after creating the table using the first syntax option:

CreateTableConstraintStep step = createTable("Supplier")
.column("SNUM", INTEGER)
.column("SNAME", VARCHAR(16).nullable(false))
.column("STATUS", INTEGER.nullable(false))
.column("CITY", VARCHAR(20).nullable(false))
.constraints(
primaryKey("SNUM"),
unique("SNAME"));

And then trying to execute an insertInto() statement:

myDSL.insertInto(table("Supplier"), field("SNUM"), field("SNAME"), field("STATUS"), field("CITY"))
.values(1, "Smith", 20, "London")
.values(2, "Jones", 10, "Paris")
.values(3, "Blake", 30, "Paris")
.values(4, "Clark", 20, "London")
.values(5, "Adams", 30, "Athens").execute();

The insertInto() does not have the option of allowing String specification of the table and fields. Thus, I had to use the 

table() and field() methods of the DSL.  The insert statement is rendered without quotes and the create table statement was

rendered with quotes.  Thus, the table and field names are not found.

[Manuel Rossetti]

To make matters more interesting, I tried using DSL.name():

CreateTableConstraintStep step2 = createTable(table(name("Supplier")))
.column(field(name("SNUM"), INTEGER))
.column(field(name("SNAME"), VARCHAR(16).nullable(false)))
.column(field(name("STATUS"), INTEGER.nullable(false)))
.column(field(name("CITY"), VARCHAR(20).nullable(false)))
.constraints(primaryKey(field(name("SNUM"))),
unique(field(name("SNAME"))));

System.out.println(step2.toString());
String sql2 = step2.getSQL();
System.out.println(sql2);
System.out.println();

This results in quoted identifies, like in my first example:

create table "Supplier" (

  "SNUM" integer null,

  "SNAME" varchar(16) not null,

  "STATUS" integer not null,

  "CITY" varchar(20) not null,

  primary key ("SNUM"),

  unique ("SNAME")

)

create table "Supplier" ("SNUM" integer null, "SNAME" varchar(16) not null, "STATUS" integer not null, "CITY" varchar(20) not null, primary key ("SNUM"), unique ("SNAME"))

Thus, this approach:

CreateTableConstraintStep step1 = createTable(table("Supplier"))
.column(field("SNUM", INTEGER))
.column(field("SNAME", VARCHAR(16).nullable(false)))
.column(field("STATUS", INTEGER.nullable(false)))
.column(field("CITY", VARCHAR(20).nullable(false)))
.constraints(primaryKey(field("SNUM")),
unique(field("SNAME")));

Appears to be using the quoted string values as direct SQL and they are thus not being rendered through JOOQ's assumed default quoting mechanism. That isn't what I expected to happen, but it seems like that is what is going on.

[Lukas Eder]

Hi Manuel.

  "What's in a name" -- William Shakespeare

Maybe, this helps explain what you've observed?

- https://blog.jooq.org/2020/04/03/whats-a-string-in-the-jooq-api/

- https://www.jooq.org/doc/latest/manual/sql-building/names/

- https://www.jooq.org/doc/latest/manual/sql-building/dsl-context/custom-settings/settings-name-style/

You can call DSL.unquotedName() to make names explicitly unquoted.

If they are supposed to be different by design, which SQL building approach is more recommended?

It is always recommended to create explicit identifiers using DSL.name(), DSL.quotedName(), DSL.unquotedName() instead.

Thanks,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/7ec4d569-f25c-465f-81c5-483d82a73025n%40googlegroups.com.

[Manuel Rossetti]

Hey Lukas,

Thanks for the links. I was not aware of the one from your blog.  I'm pretty sure that I understand what is going on now.  I have finished a draft of a short tutorial for my students. You can see it here.  If you ever have time to look it over, I would appreciate any feedback, especially if I got something wrong about jooq.

You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/Q6vmm9k0EAs/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO42jKMj4Ak2V_g06%3DM8oFYcn4nL2T6Fa2gBpMOvedka0g%40mail.gmail.com.

[Lukas Eder]

Hi Manuel,

Thanks a lot for that link. I'll be very happy to look into it. What's the best way to contact you for any feedback? 

Cheers,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAMbxvHmmY%3DFPPQyuMUqrisOqoFow97UQymz-ieszUShn4N-3cA%40mail.gmail.com.