[PostgreSql] String is too long to insert.

[Pay Liu]

Hi all, 

The size of my string column  is too small, and I would like to truncation my too long string to be fit the size of column.

I know how to do it in SQL, as below doc: http://www.postgresql.org/docs/9.1/interactive/datatype-character.html

But I can't find how to do in JOOQ. 

Very thanks!

-Pay

INSERT INTO test2 VALUES ('too long');
ERROR:  value too long for type character varying(5)
INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation

[Lukas Eder]

Hello Pay,

Length, Precision and Scale in cast expressions are only going to be
supported in jOOQ 3.0. For now, I suggest you use the substring()
function:
http://www.jooq.org/javadoc/latest/org/jooq/impl/Factory.html#substring(org.jooq.Field,
int, int)

Here are some other Postgres String functions, many of which are
supported by jOOQ in some way:
http://www.postgresql.org/docs/9.2/static/functions-string.html

Of course, if you want to explicitly use casting, you can write your
own plain SQL for that:

create.insertInto(TEST2)
.values(Factory.field("?::varchar(5)", String.class, "too long"))
.execute();

Hope this helps

Cheers
Lukas

2013/1/8 Pay Liu <pay...@gmail.com>:

[Pay Liu]

Hello Lukas, :)

I need to set many field, so in my case it's inconvenient to use plain SQL.

As your suggestion, I'll use substring as below. thanks!

String fieldText = String.format("'%s'", text);  

create.insertInto(TEST2) 
      .set(TEST2.B, Factory.substring(Factory.field(fieldText, String.class), 1, 5)) 
      .set(...other....)
      .execute(); 

BTW, I find the number 5 is a magic number. I try to get the column size from my JOOQ java code, but I can't. 

Do I miss or there is no static variable from code generator?

ex: TEST2.B.getSize() ??

Thanks, Lukas.

-Pay

[Lukas Eder]

> String fieldText = String.format("'%s'", text);

Beware of SQL injection (and syntax errors) there!
check this out:

text = " '; DROP DATABASE my_database; --";

There is an explicit warning about SQL injection in jOOQ's plain SQL
methods' Javadoc. E.g.
http://www.jooq.org/javadoc/latest/org/jooq/impl/Factory.html#field(java.lang.String)

Either, you properly escape your text yourself, or you let jOOQ do it
using Factory.inline()

> BTW, I find the number 5 is a magic number. I try to get the column size
> from my JOOQ java code, but I can't.

As I said, column length, precision and scale will only be available
in jOOQ 3.0. In order to introduce them, I had to (slightly)
incompatibly change the DataType implementations. That's why I waited
for a major release.

Cheers
Lukas

[Pay Liu]

oh oh～ I know now, Lukas :)

And thanks for your hint, I need to avoid that SQL injection. 

Have a goody ~

- Pay

[Pay Liu]

Factory.inline is ok to me, 

thanks Lukas!

// String fieldText = String.format("'%s'", text); 

create.insertInto(TEST2) 
      .set(TEST2.B, Factory.substring(Factory.inline(text, String.class), 1, 5)) 
      .set(...other....)
      .execute(); 

[Lukas Eder]

> Factory.inline is ok to me,
> thanks Lukas!

Glad it worked out.

Cheers
Lukas