Board index » jbuilder » default values problem with mysql version 5

default values problem with mysql version 5


2007-08-17 11:16:34 PM
jbuilder4
I have an application written in java with jBuilder.
This uses JDBC to access a mySQL database which I am converting from
version 4.1 to version 5.0
When my application tries to INSERT a record but does not specify a
value for a text field that is defined as NOT NULL with no default set
the following error message is returned :
Field 'somedata' doesn't have a default value
this doesn't happen with JDBC on mysql 4.1
It also doesn't happen when I run the command in mysql, or when using
mySql query browser but only when I connect using JDBC
Any clues ?
Anton
 
 

Re:default values problem with mysql version 5

Anton Jungreuthmayer wrote:
Quote
I have an application written in java with jBuilder.

This uses JDBC to access a mySQL database which I am converting from
version 4.1 to version 5.0

When my application tries to INSERT a record but does not specify a
value for a text field that is defined as NOT NULL with no default set
the following error message is returned :

Field 'somedata' doesn't have a default value

this doesn't happen with JDBC on mysql 4.1
It also doesn't happen when I run the command in mysql, or when using
mySql query browser but only when I connect using JDBC

Any clues ?

Anton
This is probably a result of the changes to mySQL 5 and the JDBC
Drivers. Which MySQL JDBC driver (5.1 is the latest) are you using and
which JDK version?
This is not going to be related to JB, unless you are using the
QueryDataSet, which may or may not work correctly with newer JDBC
implementations.
 

Re:default values problem with mysql version 5

Paul Nichols [TeamB] wrote:
Quote
Anton Jungreuthmayer wrote:
>I have an application written in java with jBuilder.
>
>This uses JDBC to access a mySQL database which I am converting from
>version 4.1 to version 5.0
>
>When my application tries to INSERT a record but does not specify a
>value for a text field that is defined as NOT NULL with no default set
>the following error message is returned :
>
>Field 'somedata' doesn't have a default value
>
>this doesn't happen with JDBC on mysql 4.1
>It also doesn't happen when I run the command in mysql, or when using
>mySql query browser but only when I connect using JDBC
>
>Any clues ?
>
>Anton

This is probably a result of the changes to mySQL 5 and the JDBC
Drivers. Which MySQL JDBC driver (5.1 is the latest) are you using and
which JDK version?

This is not going to be related to JB, unless you are using the
QueryDataSet, which may or may not work correctly with newer JDBC
implementations.

JDBC driver - 5.0.6
java version 1.5.0_04-b05
 

{smallsort}

Re:default values problem with mysql version 5

Anton Jungreuthmayer wrote:
Quote
Paul Nichols [TeamB] wrote:
>Anton Jungreuthmayer wrote:
>>I have an application written in java with jBuilder.
>>
>>This uses JDBC to access a mySQL database which I am converting from
>>version 4.1 to version 5.0
>>
>>When my application tries to INSERT a record but does not specify a
>>value for a text field that is defined as NOT NULL with no default
>>set the following error message is returned :
>>
>>Field 'somedata' doesn't have a default value
>>
>>this doesn't happen with JDBC on mysql 4.1
>>It also doesn't happen when I run the command in mysql, or when using
>>mySql query browser but only when I connect using JDBC
>>
>>Any clues ?
>>
>>Anton
>
>This is not going to be related to JB, unless you are using the
>QueryDataSet, which may or may not work correctly with newer JDBC
>implementations.
>

JDBC driver - 5.0.6
java version 1.5.0_04-b05
Thanks for this info. The problem has to do with a commit error with V5
and the newer mySQL database transaction mechanism. It is a bug and has
been reported.
To get around this issue try setting jdbcCompliantTruncation=false. This
will prevent the exception from being thrown before it gets to the MySQL
Server.
See example below:
Class.forName("com.mysql.jdbc.Driver");
String dburl = "jdbc:mysql://server/TEST";
String dburlTrunc =
"jdbc:mysql://server/TEST?jdbcCompliantTruncation=false";
String dbuser = "TEST";
String dbpass = "TEST";
Connection connection = DriverManager.getConnection(dburl, dbuser,
dbpass);
 

Re:default values problem with mysql version 5

Paul Nichols [TeamB] wrote:
Quote
Anton Jungreuthmayer wrote:
>Paul Nichols [TeamB] wrote:
>>Anton Jungreuthmayer wrote:
>>>I have an application written in java with jBuilder.
>>>
>>>This uses JDBC to access a mySQL database which I am converting from
>>>version 4.1 to version 5.0
>>>
>>>When my application tries to INSERT a record but does not specify a
>>>value for a text field that is defined as NOT NULL with no default
>>>set the following error message is returned :
>>>
>>>Field 'somedata' doesn't have a default value
>>>
>>>this doesn't happen with JDBC on mysql 4.1
>>>It also doesn't happen when I run the command in mysql, or when
>>>using mySql query browser but only when I connect using JDBC
>>>
>>>Any clues ?
>>>
>>>Anton
>>
>>This is not going to be related to JB, unless you are using the
>>QueryDataSet, which may or may not work correctly with newer JDBC
>>implementations.
>>
>
>JDBC driver - 5.0.6
>java version 1.5.0_04-b05


Thanks for this info. The problem has to do with a commit error with V5
and the newer mySQL database transaction mechanism. It is a bug and has
been reported.

To get around this issue try setting jdbcCompliantTruncation=false. This
will prevent the exception from being thrown before it gets to the MySQL
Server.

See example below:

Class.forName("com.mysql.jdbc.Driver");
String dburl = "jdbc:mysql://server/TEST";
String dburlTrunc =
"jdbc:mysql://server/TEST?jdbcCompliantTruncation=false";
String dbuser = "TEST";
String dbpass = "TEST";
Connection connection = DriverManager.getConnection(dburl, dbuser,
dbpass);
Thank you so much - that has fixed it.
 

Re:default values problem with mysql version 5

Anton Jungreuthmayer wrote:
Quote
Paul Nichols [TeamB] wrote:
Thank you so much - that has fixed it.
Super!!! Glad that did the trick.
Just mark this down somewhere. It is a JDBC bug in the mySQL drivers,
not with Java JDK or JBuilder.