|
Home > Archive > Apache JDO Project > July 2005 > Binding a char to PreparedStatement
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Binding a char to PreparedStatement
|
|
| Michael Watzek 2005-07-18, 5:45 pm |
| Hi Andy, Erik,
we have a lot of derby errors "ERROR 22001: A truncation error was
encountered trying to shrink CHAR 'XXX' to length 1."
I analysed the problem writing a little Java program that uses JDBC
directly. The program inserts a row into a table having a single CHAR(1)
column. The program uses a prepared statement and binds a Java character
to it using different methods:
1) PreparedStatement.setShort
2) PreparedStatement.setInt
3) PreparedStatement.setObject
4) PreparedStatement.setString
Note: There is no PreparedStatement.setChar. The program runs with
different Java characters:
If the Java character is greater or equal 0xA, then 1), 2), 3) fail.
If the Java character is less than 0xA and greater or equal 0x0, then
only 3) fails.
When 1) and 2) fails, then the exception message is the same as above.
3) always fails with "An attempt was made to get a data value of type
'CHAR' from a data value of type 'java.lang.Character'." (I do not
understand this message). 4) is always sucessfull.
It seems that 1) and 2) fail, because Derby stores numbers in string
representation if the database column type is CHAR, e.g 0xA => "10".
So, I wonder if JPOX uses 1) or 2) when binding a single character? This
would be an explanation for 25 error messages. Can you please check
that? I think the right method to use is 4).
Regards,
Michael
--
-------------------------------------------------------------------
Michael Watzek Tech@Spree Engineering GmbH
mailto:mwa.tech@spree.de Buelowstr. 66
Tel.: ++49/30/235 520 36 10783 Berlin - Germany
Fax.: ++49/30/217 520 12 http://www.spree.de/
-------------------------------------------------------------------
| |
| Andy Jefferson 2005-07-20, 2:45 am |
| Hi Michael,
> we have a lot of derby errors "ERROR 22001: A truncation error was
> encountered trying to shrink CHAR 'XXX' to length 1."
I've never seen one of these in our testcases, so we've got some difference in
how the TCK is specifying things and how we've done it.
> I analysed the problem writing a little Java program that uses JDBC
> directly. The program inserts a row into a table having a single CHAR(1)
> column. The program uses a prepared statement and binds a Java character
> to it using different methods:
>
> 1) PreparedStatement.setShort
> 2) PreparedStatement.setInt
> 3) PreparedStatement.setObject
> 4) PreparedStatement.setString
What type is the Java type and what type is the DB column ? (and is there a
jdbc-type specified in the MetaData?). Give an example of a Java field, its
MetaData entry, and the RDBMS column type and we should be able to isolate
this [OK, I could run the TCK and try to find one, but since you have these
fresh in your memory its easier to ask you :-)]
JPOX could use many different JDBC methods depending on what Java type and
what RDBMS type so it depends on your situation. For example :-
Java=char/Character, RDBMS="INTEGER", uses JDBC setInt()
Java=char/Character, RDBMS="CHAR", uses JDBC setString()
--
Andy
| |
| Michael Watzek 2005-07-20, 5:45 pm |
| Hi Andy,
I extended the test program to make objects persistent using JPOX in
addition to the JDBC test I described below:
There is a persistence capable class having a field of type "char".
There is a table having a single column of type "CHAR(1)". The orm
metadata for this class specifies a field mapping from the "char" field
to the "CHAR(1)" column. The orm column attribute "jdbc-type" is
omitted. Thus, the JPOX default is used.
When an object is made persistent using that mapping, then Derby throws
error 22001 (see below).
But if the mapping contains the orm column attribute "jdbc-type" setting
it to "CHAR" explicitly, then Derby does not throw that error. Instead,
pm.makePersistent succeeds.
It seems, that JPOX does not default orm column attribute "jdbc-type" to
"CHAR" if the field is of type "char" or "java.lang.Character". Instead,
JPOX defaults to a numerical type, e.g. "INTEGER". Can you verify that?
Craig,
the spec specifies in chapter 18.5:
"The jdbc-type attribute declares the type of the column in the
database. This type is defaulted based on the type of the field being
mapped."
Does this mean, that an implementation may choose for an default?
Another question: The TCK orm metadata does not specify column attribute
"jdbc-type". Thus, implementations use their default when the TCK runs.
Does it make sense, to add the jdbc type to all column attributes
ensuring that the mapping fits to the database schema?
Regards,
Michael
> Hi Michael,
>
>
>
>
> I've never seen one of these in our testcases, so we've got some difference in
> how the TCK is specifying things and how we've done it.
>
>
>
>
> What type is the Java type and what type is the DB column ? (and is there a
> jdbc-type specified in the MetaData?). Give an example of a Java field, its
> MetaData entry, and the RDBMS column type and we should be able to isolate
> this [OK, I could run the TCK and try to find one, but since you have these
> fresh in your memory its easier to ask you :-)]
>
> JPOX could use many different JDBC methods depending on what Java type and
> what RDBMS type so it depends on your situation. For example :-
> Java=char/Character, RDBMS="INTEGER", uses JDBC setInt()
> Java=char/Character, RDBMS="CHAR", uses JDBC setString()
>
>
--
-------------------------------------------------------------------
Michael Watzek Tech@Spree Engineering GmbH
mailto:mwa.tech@spree.de Buelowstr. 66
Tel.: ++49/30/235 520 36 10783 Berlin - Germany
Fax.: ++49/30/217 520 12 http://www.spree.de/
-------------------------------------------------------------------
| |
| Craig Russell 2005-07-20, 5:45 pm |
| | |
| Andy Jefferson 2005-07-20, 5:45 pm |
| > > "The jdbc-type attribute declares the type of the column in the
>
> That was not the intent. The intent was that the JDBC type would be
> obvious. ;-) And the "obvious" jdbc-type for char and Character is
> CHAR. I'll raise this issue with the JDO experts to be sure.
Well in the case of a char it is, but in the case of a Java double for
example ? It all depends on the RDBMS. Some RDBMS support DOUBLE, some
DECIMAL, some NUMERIC, some have other types ...
--
Andy
Java Persistent Objects - JPOX
| |
| Craig Russell 2005-07-20, 5:45 pm |
| | |
| Andy Jefferson 2005-07-20, 5:45 pm |
| > > Well in the case of a char it is, but in the case of a Java double for
>
> This is the jdbc-type ("generic sql type"), not the sql-type we're
> talking about (there is a separate sql-type that the user can specify
> if they want to get database specific).
>
> The idea is that the user could override the jdbc-type if necessary,
> and the jdo impl would use jdbc-type plus length plus nullability to
> map to a natural sql-type that is database specific.
I'm also talking about the jdbc-type (or to be specific javax.sql.Types). So
the JDO impl should always take JDBC Types.DOUBLE when the user has a Java
double field? Many JDBC drivers *don't* provide a type mapping for
Types.DOUBLE (many don't provide support for several of the "standard" JDBC
types).
We've always encouraged our users to specify the jdbc-type if they want things
to be JDO-impl independent for this very reason.
While a default may make sense so people know what they get if nothing is
specified, there are *a lot* of situations where the JDBC driver doesnt
support a "generic" sql type even
--
Andy
Java Persistent Objects - JPOX
| |
| Craig Russell 2005-07-20, 5:45 pm |
| | |
| Andy Jefferson 2005-07-20, 5:45 pm |
| Hi Craig,
>
> I'm trying to see where the confusion is. The attribute is only
> specified if the user wants to override the default for the field
> type. Is this what you're talking about?
Just trying to understand what you're proposing with respect to how JDO impls
decide a "default" RDBMS type. You previously said that your intent of the
spec was that the jdbc-type (when not specified) should be the "obvious one
for the Java type". I was simply pointing out that if the user has a Java
double, then the obvious jdbc-type would be "DOUBLE", and that many RDBMS
dont map that in their JDBC drivers, so what use is that default ?
The origin of the problem Michael reported is down to the lack of a jdbc-type
specification in the metadata, and the fact that JPOX's current "default" for
char is to store it as INTEGER (for whatever reason) - hence why he got the
problem.
We know JPOX's internal type mapping needs more flexibility, but the JDO spec
doesn't define what we have to do currently, so our assumption til now was
that the user can define their required type if they have one in mind,
otherwise they take what we give them.
--
Andy
| |
| Craig Russell 2005-07-20, 5:45 pm |
| | |
|
| See inline
Erik Bengtson
-----Original Message-----
From: Craig Russell [mailto:Craig.Russell@Sun.COM]
Sent: Thursday, July 21, 2005 12:26 AM
To: jdo-dev@db.apache.org
Subject: Re: Binding a char to PreparedStatement
>I was thinking that most of the value in the jdbc-type would be in the
java-to-database schema creation, where the user could rely on the jdo
impl to create appropriate schema. In the case of char or >Character,
that would be CHAR(1). In the case of double or Double, with a length of
20 and a scale of 12, that would be DOUBLE if the database supported it,
or DECIMAL(20, 12) if not.
CHAR(1) will not allow Unicode storage, you need either to use NCHAR(1),
CHAR(4) or another type that allows you to store 2 bytes. We use INTEGER
data type by default which works in all databases, and more, it allows
operators like > < on SQL queries without having to convert it using
ASCII functions.
The origin of the problem Michael reported is down to the lack of a
jdbc-type
specification in the metadata, and the fact that JPOX's current
"default" for
char is to store it as INTEGER (for whatever reason) - hence why he got
the
problem.
Ok. I thought that you would agree that this is a bug in JPOX, since the
"obvious default" for a char column is CHAR.
It is not that obvious when you have to support a wide range of
databases and data types. The most obvious choice to me is the most
common type among these databases.
| |
| Craig Russell 2005-07-21, 5:45 pm |
| | |
| erik@jpox.org 2005-07-21, 5:45 pm |
| > >
> I'm not aware of any database that doesn't support CHAR column type.
> And I think that having a default of jdbc-type = CHAR for a char
> field is very reasonable. If I put this into the specification, I
> assume you would be ok with it?
>
I would be very ok, if you show me how to store a char into CHAR(1) working for
most of databases (take only the open sources derby, mysql, postgresql). AFAIK,
in the char type is stored with 1 byte only in most of databases.
JPOX has opted to fully support the Java types, following the the Java spec
rules, so we have INTEGER supporting our needs.
It will be a long way if we want to define what are the default types for orm.
Why not leave it alone, and suggest users for portability explicity use the
metadata.
The TCK problem scenario is another thing, the TCK has an existing schema and a
java model but the metadata does not specify which jdbc type, therefore the JDO
implementation should select the most appropriate method(jdbc type) to store
the data from Java to db and vice-versa, without requiring another clue.
Also, it does not implies that we will always use a jdbc driver to access a
rdbms. We can use a socket conn and send SQL commands.
[vbcol=seagreen]
>
> Craig
| |
| Craig Russell 2005-07-21, 8:45 pm |
| | |
| Daniel John Debrunner 2005-07-22, 2:45 am |
| Craig Russell wrote:
> Hi Erik,
>
> On Jul 21, 2005, at 2:10 AM, erik@jpox.org <mailto:erik@jpox.org> wrote:
>
>
>
> Correct. The point is what should be the default.
Well, implementation defined really. Derby defines CHAR(1) as storage
for one *character*, not one byte, character is really the intent of the
SQL spec. Derby always uses Unicode as the character set, so CHAR(1) can
store any Unicode character from Java.
Dan.
| |
| Craig Russell 2005-07-23, 5:45 pm |
| |
|
|
|
|