Escape default value on CREATE TABLE

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Escape default value on CREATE TABLE

ypiel
Hi,

I try to use JPA to generate CREATE TABLE statements.All works fine but
default values of VARCHAR.

For example, I add a VARCHAR column with default value with :

DBIdentifier name = DBIdentifier.newColumn("na-me");
Column cName = table.addColumn(name);
cName.setType(Types.VARCHAR);
cName.setNotNull(true);
cName.setSize(255);
cName.setDefault("O,K");

And the generated code is:

DB => CREATE TABLE "My-schema".MaTable (id1 INTEGER NOT NULL, id2
VARCHAR(500) NOT NULL, "na-me" VARCHAR(255) DEFAULT O,K NOT NULL, âge
TINYINT DEFAULT 20, salary DECIMAL, CONSTRAINT idkeys PRIMARY KEY (id1,
id2))


'O,K' will corrupt my SQL query. How can I force the escape of default
values ?

Regards,
Yves
Reply | Threaded
Open this post in threaded view
|

Re: Escape default value on CREATE TABLE

Matthew Broadhead
i don't know your use case but i generate the tables automatically from
the java entities by setting the SynchroniseMappings property in
persistence.xml, e.g.
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
     xmlns="http://xmlns.jcp.org/xml/ns/persistence"
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence 
http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
     <persistence-unit name="myDb">
         <jta-data-source>myDb</jta-data-source>
         <class>com.example.entities.jpa.Entity1</class>
         <class>com.example.entities.jpa.Entity2</class>
         <properties>
             <property name="openjpa.jdbc.SynchronizeMappings"
                 value="buildSchema(ForeignKeys=true)" />
         </properties>
     </persistence-unit>
</persistence>

an example entity might be:
package com.example.entities.jpa;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Version;

@Entity
public class Entity1 implements Serializable {
     private static final long serialVersionUID = 1L;

     @Id
     private Long id;

     @Version
     private Long version;

     @Column(length = 255)
     private String name;

     ...getters setters
}

and you could just set the default when you initialise the Entity? like
Entity1 entity1 = new Entity1();
entity1.setName("O,K");

On 12/06/18 15:21, Yves PIEL wrote:

> Hi,
>
> I try to use JPA to generate CREATE TABLE statements.All works fine but
> default values of VARCHAR.
>
> For example, I add a VARCHAR column with default value with :
>
> DBIdentifier name = DBIdentifier.newColumn("na-me");
> Column cName = table.addColumn(name);
> cName.setType(Types.VARCHAR);
> cName.setNotNull(true);
> cName.setSize(255);
> cName.setDefault("O,K");
>
> And the generated code is:
>
> DB => CREATE TABLE "My-schema".MaTable (id1 INTEGER NOT NULL, id2
> VARCHAR(500) NOT NULL, "na-me" VARCHAR(255) DEFAULT O,K NOT NULL, âge
> TINYINT DEFAULT 20, salary DECIMAL, CONSTRAINT idkeys PRIMARY KEY (id1,
> id2))
>
>
> 'O,K' will corrupt my SQL query. How can I force the escape of default
> values ?
>
> Regards,
> Yves
>

Reply | Threaded
Open this post in threaded view
|

Re: Escape default value on CREATE TABLE

ypiel
Hello Matthew,

Thanks for your answer.

My use case is really to generate SQL queries programmatically, as my
example shows, not from java entities.

Regards,
Yves

On 13 June 2018 at 12:18, Matthew Broadhead <[hidden email]>
wrote:

> i don't know your use case but i generate the tables automatically from
> the java entities by setting the SynchroniseMappings property in
> persistence.xml, e.g.
> <?xml version="1.0" encoding="UTF-8"?>
> <persistence version="2.1"
>     xmlns="http://xmlns.jcp.org/xml/ns/persistence"
>     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
>     xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
> http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
>     <persistence-unit name="myDb">
>         <jta-data-source>myDb</jta-data-source>
>         <class>com.example.entities.jpa.Entity1</class>
>         <class>com.example.entities.jpa.Entity2</class>
>         <properties>
>             <property name="openjpa.jdbc.SynchronizeMappings"
>                 value="buildSchema(ForeignKeys=true)" />
>         </properties>
>     </persistence-unit>
> </persistence>
>
> an example entity might be:
> package com.example.entities.jpa;
>
> import javax.persistence.Entity;
> import javax.persistence.Id;
> import javax.persistence.Version;
>
> @Entity
> public class Entity1 implements Serializable {
>     private static final long serialVersionUID = 1L;
>
>     @Id
>     private Long id;
>
>     @Version
>     private Long version;
>
>     @Column(length = 255)
>     private String name;
>
>     ...getters setters
> }
>
> and you could just set the default when you initialise the Entity? like
> Entity1 entity1 = new Entity1();
> entity1.setName("O,K");
>
>
> On 12/06/18 15:21, Yves PIEL wrote:
>
>> Hi,
>>
>> I try to use JPA to generate CREATE TABLE statements.All works fine but
>> default values of VARCHAR.
>>
>> For example, I add a VARCHAR column with default value with :
>>
>> DBIdentifier name = DBIdentifier.newColumn("na-me");
>> Column cName = table.addColumn(name);
>> cName.setType(Types.VARCHAR);
>> cName.setNotNull(true);
>> cName.setSize(255);
>> cName.setDefault("O,K");
>>
>> And the generated code is:
>>
>> DB => CREATE TABLE "My-schema".MaTable (id1 INTEGER NOT NULL, id2
>> VARCHAR(500) NOT NULL, "na-me" VARCHAR(255) DEFAULT O,K NOT NULL, âge
>> TINYINT DEFAULT 20, salary DECIMAL, CONSTRAINT idkeys PRIMARY KEY (id1,
>> id2))
>>
>>
>> 'O,K' will corrupt my SQL query. How can I force the escape of default
>> values ?
>>
>> Regards,
>> Yves
>>
>>
>
Reply | Threaded
Open this post in threaded view
|

Re: Escape default value on CREATE TABLE

Matthew Broadhead-2
have you tried setDefaultString?
also this might be the source code here
https://apache.googlesource.com/openjpa/+/295576ffb0080106de70cc4caf23ab38d59cf56a/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/schema/Column.java

On 13/06/18 12:51, Yves PIEL wrote:

> Hello Matthew,
>
> Thanks for your answer.
>
> My use case is really to generate SQL queries programmatically, as my
> example shows, not from java entities.
>
> Regards,
> Yves
>
> On 13 June 2018 at 12:18, Matthew Broadhead <[hidden email]>
> wrote:
>
>> i don't know your use case but i generate the tables automatically from
>> the java entities by setting the SynchroniseMappings property in
>> persistence.xml, e.g.
>> <?xml version="1.0" encoding="UTF-8"?>
>> <persistence version="2.1"
>>      xmlns="http://xmlns.jcp.org/xml/ns/persistence"
>>      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
>>      xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
>> http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
>>      <persistence-unit name="myDb">
>>          <jta-data-source>myDb</jta-data-source>
>>          <class>com.example.entities.jpa.Entity1</class>
>>          <class>com.example.entities.jpa.Entity2</class>
>>          <properties>
>>              <property name="openjpa.jdbc.SynchronizeMappings"
>>                  value="buildSchema(ForeignKeys=true)" />
>>          </properties>
>>      </persistence-unit>
>> </persistence>
>>
>> an example entity might be:
>> package com.example.entities.jpa;
>>
>> import javax.persistence.Entity;
>> import javax.persistence.Id;
>> import javax.persistence.Version;
>>
>> @Entity
>> public class Entity1 implements Serializable {
>>      private static final long serialVersionUID = 1L;
>>
>>      @Id
>>      private Long id;
>>
>>      @Version
>>      private Long version;
>>
>>      @Column(length = 255)
>>      private String name;
>>
>>      ...getters setters
>> }
>>
>> and you could just set the default when you initialise the Entity? like
>> Entity1 entity1 = new Entity1();
>> entity1.setName("O,K");
>>
>>
>> On 12/06/18 15:21, Yves PIEL wrote:
>>
>>> Hi,
>>>
>>> I try to use JPA to generate CREATE TABLE statements.All works fine but
>>> default values of VARCHAR.
>>>
>>> For example, I add a VARCHAR column with default value with :
>>>
>>> DBIdentifier name = DBIdentifier.newColumn("na-me");
>>> Column cName = table.addColumn(name);
>>> cName.setType(Types.VARCHAR);
>>> cName.setNotNull(true);
>>> cName.setSize(255);
>>> cName.setDefault("O,K");
>>>
>>> And the generated code is:
>>>
>>> DB => CREATE TABLE "My-schema".MaTable (id1 INTEGER NOT NULL, id2
>>> VARCHAR(500) NOT NULL, "na-me" VARCHAR(255) DEFAULT O,K NOT NULL, âge
>>> TINYINT DEFAULT 20, salary DECIMAL, CONSTRAINT idkeys PRIMARY KEY (id1,
>>> id2))
>>>
>>>
>>> 'O,K' will corrupt my SQL query. How can I force the escape of default
>>> values ?
>>>
>>> Regards,
>>> Yves
>>>
>>>

Reply | Threaded
Open this post in threaded view
|

Re: Escape default value on CREATE TABLE

ypiel
Thank you Matthew,

I know how to set default value as you can see in my example
cName.setDefault("O,K");
but it is not escape : "na-me" VARCHAR(255) DEFAULT O,K NOT NULL,
Should be : "na-me" VARCHAR(255) DEFAULT "O,K" NOT NULL,
isn't it ?

Regards

On 13 June 2018 at 19:39, Matthew Broadhead <
[hidden email]> wrote:

> have you tried setDefaultString?
> also this might be the source code here https://apache.googlesource.co
> m/openjpa/+/295576ffb0080106de70cc4caf23ab38d59cf56a/
> openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/schema/Column.java
>
>
> On 13/06/18 12:51, Yves PIEL wrote:
>
>> Hello Matthew,
>>
>> Thanks for your answer.
>>
>> My use case is really to generate SQL queries programmatically, as my
>> example shows, not from java entities.
>>
>> Regards,
>> Yves
>>
>> On 13 June 2018 at 12:18, Matthew Broadhead <
>> [hidden email]>
>> wrote:
>>
>> i don't know your use case but i generate the tables automatically from
>>> the java entities by setting the SynchroniseMappings property in
>>> persistence.xml, e.g.
>>> <?xml version="1.0" encoding="UTF-8"?>
>>> <persistence version="2.1"
>>>      xmlns="http://xmlns.jcp.org/xml/ns/persistence"
>>>      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
>>>      xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
>>> http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
>>>      <persistence-unit name="myDb">
>>>          <jta-data-source>myDb</jta-data-source>
>>>          <class>com.example.entities.jpa.Entity1</class>
>>>          <class>com.example.entities.jpa.Entity2</class>
>>>          <properties>
>>>              <property name="openjpa.jdbc.SynchronizeMappings"
>>>                  value="buildSchema(ForeignKeys=true)" />
>>>          </properties>
>>>      </persistence-unit>
>>> </persistence>
>>>
>>> an example entity might be:
>>> package com.example.entities.jpa;
>>>
>>> import javax.persistence.Entity;
>>> import javax.persistence.Id;
>>> import javax.persistence.Version;
>>>
>>> @Entity
>>> public class Entity1 implements Serializable {
>>>      private static final long serialVersionUID = 1L;
>>>
>>>      @Id
>>>      private Long id;
>>>
>>>      @Version
>>>      private Long version;
>>>
>>>      @Column(length = 255)
>>>      private String name;
>>>
>>>      ...getters setters
>>> }
>>>
>>> and you could just set the default when you initialise the Entity? like
>>> Entity1 entity1 = new Entity1();
>>> entity1.setName("O,K");
>>>
>>>
>>> On 12/06/18 15:21, Yves PIEL wrote:
>>>
>>> Hi,
>>>>
>>>> I try to use JPA to generate CREATE TABLE statements.All works fine but
>>>> default values of VARCHAR.
>>>>
>>>> For example, I add a VARCHAR column with default value with :
>>>>
>>>> DBIdentifier name = DBIdentifier.newColumn("na-me");
>>>> Column cName = table.addColumn(name);
>>>> cName.setType(Types.VARCHAR);
>>>> cName.setNotNull(true);
>>>> cName.setSize(255);
>>>> cName.setDefault("O,K");
>>>>
>>>> And the generated code is:
>>>>
>>>> DB => CREATE TABLE "My-schema".MaTable (id1 INTEGER NOT NULL, id2
>>>> VARCHAR(500) NOT NULL, "na-me" VARCHAR(255) DEFAULT O,K NOT NULL, âge
>>>> TINYINT DEFAULT 20, salary DECIMAL, CONSTRAINT idkeys PRIMARY KEY (id1,
>>>> id2))
>>>>
>>>>
>>>> 'O,K' will corrupt my SQL query. How can I force the escape of default
>>>> values ?
>>>>
>>>> Regards,
>>>> Yves
>>>>
>>>>
>>>>
>
Reply | Threaded
Open this post in threaded view
|

Re: Escape default value on CREATE TABLE

Matthew Broadhead-2
did you try cName.setDefaultString("O,K");
?

On 14/06/18 09:29, Yves PIEL wrote:

> Thank you Matthew,
>
> I know how to set default value as you can see in my example
> cName.setDefault("O,K");
> but it is not escape : "na-me" VARCHAR(255) DEFAULT O,K NOT NULL,
> Should be : "na-me" VARCHAR(255) DEFAULT "O,K" NOT NULL,
> isn't it ?
>
> Regards
>
> On 13 June 2018 at 19:39, Matthew Broadhead <
> [hidden email]> wrote:
>
>> have you tried setDefaultString?
>> also this might be the source code here https://apache.googlesource.co
>> m/openjpa/+/295576ffb0080106de70cc4caf23ab38d59cf56a/
>> openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/schema/Column.java
>>
>>
>> On 13/06/18 12:51, Yves PIEL wrote:
>>
>>> Hello Matthew,
>>>
>>> Thanks for your answer.
>>>
>>> My use case is really to generate SQL queries programmatically, as my
>>> example shows, not from java entities.
>>>
>>> Regards,
>>> Yves
>>>
>>> On 13 June 2018 at 12:18, Matthew Broadhead <
>>> [hidden email]>
>>> wrote:
>>>
>>> i don't know your use case but i generate the tables automatically from
>>>> the java entities by setting the SynchroniseMappings property in
>>>> persistence.xml, e.g.
>>>> <?xml version="1.0" encoding="UTF-8"?>
>>>> <persistence version="2.1"
>>>>       xmlns="http://xmlns.jcp.org/xml/ns/persistence"
>>>>       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
>>>>       xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
>>>> http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
>>>>       <persistence-unit name="myDb">
>>>>           <jta-data-source>myDb</jta-data-source>
>>>>           <class>com.example.entities.jpa.Entity1</class>
>>>>           <class>com.example.entities.jpa.Entity2</class>
>>>>           <properties>
>>>>               <property name="openjpa.jdbc.SynchronizeMappings"
>>>>                   value="buildSchema(ForeignKeys=true)" />
>>>>           </properties>
>>>>       </persistence-unit>
>>>> </persistence>
>>>>
>>>> an example entity might be:
>>>> package com.example.entities.jpa;
>>>>
>>>> import javax.persistence.Entity;
>>>> import javax.persistence.Id;
>>>> import javax.persistence.Version;
>>>>
>>>> @Entity
>>>> public class Entity1 implements Serializable {
>>>>       private static final long serialVersionUID = 1L;
>>>>
>>>>       @Id
>>>>       private Long id;
>>>>
>>>>       @Version
>>>>       private Long version;
>>>>
>>>>       @Column(length = 255)
>>>>       private String name;
>>>>
>>>>       ...getters setters
>>>> }
>>>>
>>>> and you could just set the default when you initialise the Entity? like
>>>> Entity1 entity1 = new Entity1();
>>>> entity1.setName("O,K");
>>>>
>>>>
>>>> On 12/06/18 15:21, Yves PIEL wrote:
>>>>
>>>> Hi,
>>>>> I try to use JPA to generate CREATE TABLE statements.All works fine but
>>>>> default values of VARCHAR.
>>>>>
>>>>> For example, I add a VARCHAR column with default value with :
>>>>>
>>>>> DBIdentifier name = DBIdentifier.newColumn("na-me");
>>>>> Column cName = table.addColumn(name);
>>>>> cName.setType(Types.VARCHAR);
>>>>> cName.setNotNull(true);
>>>>> cName.setSize(255);
>>>>> cName.setDefault("O,K");
>>>>>
>>>>> And the generated code is:
>>>>>
>>>>> DB => CREATE TABLE "My-schema".MaTable (id1 INTEGER NOT NULL, id2
>>>>> VARCHAR(500) NOT NULL, "na-me" VARCHAR(255) DEFAULT O,K NOT NULL, âge
>>>>> TINYINT DEFAULT 20, salary DECIMAL, CONSTRAINT idkeys PRIMARY KEY (id1,
>>>>> id2))
>>>>>
>>>>>
>>>>> 'O,K' will corrupt my SQL query. How can I force the escape of default
>>>>> values ?
>>>>>
>>>>> Regards,
>>>>> Yves
>>>>>
>>>>>
>>>>>

Reply | Threaded
Open this post in threaded view
|

Re: Escape default value on CREATE TABLE

Matthew Broadhead-2
this says you have to hack column definition
https://stackoverflow.com/questions/197045/setting-default-values-for-columns-in-jpa

maybe you could try surrounding with single quotes, like
cName.setDefault("'O,K'");

On 14/06/18 10:34, Matthew Broadhead wrote:

> did you try cName.setDefaultString("O,K");
> ?
>
> On 14/06/18 09:29, Yves PIEL wrote:
>> Thank you Matthew,
>>
>> I know how to set default value as you can see in my example
>> cName.setDefault("O,K");
>> but it is not escape : "na-me" VARCHAR(255) DEFAULT O,K NOT NULL,
>> Should be : "na-me" VARCHAR(255) DEFAULT "O,K" NOT NULL,
>> isn't it ?
>>
>> Regards
>>
>> On 13 June 2018 at 19:39, Matthew Broadhead <
>> [hidden email]> wrote:
>>
>>> have you tried setDefaultString?
>>> also this might be the source code here https://apache.googlesource.co
>>> m/openjpa/+/295576ffb0080106de70cc4caf23ab38d59cf56a/
>>> openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/schema/Column.java
>>>
>>>
>>> On 13/06/18 12:51, Yves PIEL wrote:
>>>
>>>> Hello Matthew,
>>>>
>>>> Thanks for your answer.
>>>>
>>>> My use case is really to generate SQL queries programmatically, as my
>>>> example shows, not from java entities.
>>>>
>>>> Regards,
>>>> Yves
>>>>
>>>> On 13 June 2018 at 12:18, Matthew Broadhead <
>>>> [hidden email]>
>>>> wrote:
>>>>
>>>> i don't know your use case but i generate the tables automatically
>>>> from
>>>>> the java entities by setting the SynchroniseMappings property in
>>>>> persistence.xml, e.g.
>>>>> <?xml version="1.0" encoding="UTF-8"?>
>>>>> <persistence version="2.1"
>>>>>       xmlns="http://xmlns.jcp.org/xml/ns/persistence"
>>>>> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
>>>>> xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
>>>>> http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
>>>>>       <persistence-unit name="myDb">
>>>>> <jta-data-source>myDb</jta-data-source>
>>>>> <class>com.example.entities.jpa.Entity1</class>
>>>>> <class>com.example.entities.jpa.Entity2</class>
>>>>>           <properties>
>>>>>               <property name="openjpa.jdbc.SynchronizeMappings"
>>>>>                   value="buildSchema(ForeignKeys=true)" />
>>>>>           </properties>
>>>>>       </persistence-unit>
>>>>> </persistence>
>>>>>
>>>>> an example entity might be:
>>>>> package com.example.entities.jpa;
>>>>>
>>>>> import javax.persistence.Entity;
>>>>> import javax.persistence.Id;
>>>>> import javax.persistence.Version;
>>>>>
>>>>> @Entity
>>>>> public class Entity1 implements Serializable {
>>>>>       private static final long serialVersionUID = 1L;
>>>>>
>>>>>       @Id
>>>>>       private Long id;
>>>>>
>>>>>       @Version
>>>>>       private Long version;
>>>>>
>>>>>       @Column(length = 255)
>>>>>       private String name;
>>>>>
>>>>>       ...getters setters
>>>>> }
>>>>>
>>>>> and you could just set the default when you initialise the Entity?
>>>>> like
>>>>> Entity1 entity1 = new Entity1();
>>>>> entity1.setName("O,K");
>>>>>
>>>>>
>>>>> On 12/06/18 15:21, Yves PIEL wrote:
>>>>>
>>>>> Hi,
>>>>>> I try to use JPA to generate CREATE TABLE statements.All works
>>>>>> fine but
>>>>>> default values of VARCHAR.
>>>>>>
>>>>>> For example, I add a VARCHAR column with default value with :
>>>>>>
>>>>>> DBIdentifier name = DBIdentifier.newColumn("na-me");
>>>>>> Column cName = table.addColumn(name);
>>>>>> cName.setType(Types.VARCHAR);
>>>>>> cName.setNotNull(true);
>>>>>> cName.setSize(255);
>>>>>> cName.setDefault("O,K");
>>>>>>
>>>>>> And the generated code is:
>>>>>>
>>>>>> DB => CREATE TABLE "My-schema".MaTable (id1 INTEGER NOT NULL, id2
>>>>>> VARCHAR(500) NOT NULL, "na-me" VARCHAR(255) DEFAULT O,K NOT NULL,
>>>>>> âge
>>>>>> TINYINT DEFAULT 20, salary DECIMAL, CONSTRAINT idkeys PRIMARY KEY
>>>>>> (id1,
>>>>>> id2))
>>>>>>
>>>>>>
>>>>>> 'O,K' will corrupt my SQL query. How can I force the escape of
>>>>>> default
>>>>>> values ?
>>>>>>
>>>>>> Regards,
>>>>>> Yves
>>>>>>
>>>>>>
>>>>>>
>