Hi again,
JOOQ version; 3.10.1
I now have my JOOQ backed Entity Store extension in Apache Polygene passing
our test suites for Postgresql, SQLite, H2 and Derby. That is pretty cool
for us.
BUT MySQL/MariaDb doesn't pass and it is down to the use of CHAR instead of
VARCHAR for String fields.
I promised a complete test case;
start Docker instance of MySQL;
* docker run \*
* -d \*
* --name mariadb \ -e MYSQL_ROOT_PASSWORD=testing \ -e
MYSQL_DATABASE=testdb \ -p 3306:3306 \ mysql*
Then run the testcase below.
You will have created a table
* create table if not exists `ENTITIES`(`_identity` char null)*
and then an exception on insertion of data;
* insert into `ENTITIES` (`_identity`) values ('123')*
*org.jooq.exception.DataAccessException: SQL [insert into `ENTITIES`
(`_identity`) values (?)]; Data truncation: Data too long for column
'_identity' at row 1*
If you change to a single character value instead of "123", it will execute
without exception.
Even if I change the field declaration to be (instead of String.class)
Field<String> identityColumn = DSL.field( DSL.name( "_identity" ),
SQLDataType.VARCHAR );
I get the same result.
I don't know if you can force MySQL into some other behavior, but I think
that is not the right way to go. I don't understand why CHAR was chosen in
the first place. if there was some clever reason...
Cheers
Niclas
-o-o-o-
package org.hedhman.niclas;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
import org.jooq.Configuration;
import org.jooq.ConnectionProvider;
import org.jooq.DSLContext;
import org.jooq.Field;
import org.jooq.Name;
import org.jooq.Record;
import org.jooq.SQLDialect;
import org.jooq.Table;
import org.jooq.TransactionProvider;
import org.jooq.conf.RenderNameStyle;
import org.jooq.conf.Settings;
import org.jooq.impl.DSL;
import org.jooq.impl.DataSourceConnectionProvider;
import org.jooq.impl.DefaultConfiguration;
import org.jooq.impl.TableImpl;
import org.jooq.impl.ThreadLocalTransactionProvider;
import org.junit.Test;
public class Experiment
{
@Test
public void test1()
throws Exception
{
String host = "127.0.0.1";
int port = 3306;
DataSource dataSource = dbcpDataSource( host, port );
Settings settings = new Settings().withRenderNameStyle(
RenderNameStyle.QUOTED );
SQLDialect dialect = SQLDialect.MARIADB;
ConnectionProvider connectionProvider = new
DataSourceConnectionProvider( dataSource );
TransactionProvider transactionProvider = new
ThreadLocalTransactionProvider( connectionProvider, false );
Configuration configuration = new DefaultConfiguration()
.set( dialect )
.set( connectionProvider )
.set( transactionProvider )
.set( settings );
DSLContext dsl = DSL.using( configuration );
Field<String> identityColumn = DSL.field( DSL.name(
"_identity" ), String.class );
Name entitiesTableName = DSL.name( "ENTITIES" );
Table<Record> entitiesTable = new TableImpl<Record>(
entitiesTableName );
dsl.transaction( t -> {
dsl.createTableIfNotExists( entitiesTable )
.column( identityColumn )
.execute();
});
dsl.transaction( t -> {
dsl.insertInto( entitiesTable )
.set( identityColumn, "123" )
.execute();
});
}
private DataSource dbcpDataSource( String host, int port )
throws Exception
{
BasicDataSource pool = new BasicDataSource();
String driverClass = "com.mysql.jdbc.Driver";
Class.forName( driverClass );
pool.setDriverClassName( driverClass );
pool.setUrl( "jdbc:mysql://" + host + ":" + port + "/testdb" );
pool.setUsername( "root" );
pool.setPassword( "testing" );
pool.setDefaultAutoCommit( false );
return pool;
}
}
--
Niclas Hedhman, Software Developer
http://polygene.apache.org - New Energy for Java
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
