Re: Binding a char to PreparedStatement
Hi Erik,On Jul 21, 2005, at 2:10 AM, [EMAIL PROTECTED] wrote: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. 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?Thanks,Craig Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp! smime.p7s Description: S/MIME cryptographic signature
Re: Binding a char to PreparedStatement
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. Craig
Re: Binding a char to PreparedStatement
Hi Erik,On Jul 21, 2005, at 2:10 AM, [EMAIL PROTECTED] wrote:See inlineErik Bengtson-Original Message-From: Craig Russell [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 21, 2005 12:26 AMTo: jdo-dev@db.apache.orgSubject: Re: Binding a char to PreparedStatement I was thinking that most of the value in the jdbc-type would be in thejava-to-database schema creation, where the user could rely on the jdoimpl 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 of20 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. Correct. The point is what should be the default. I believe that if you are using a single-byte character encoding like Latin or well, some others, that this is sufficient. And columns that need to accommodate national use characters are usually well-known by the application. I believe if the application has to store wide characters, it's ok for the user to have to tell the JDO metadata that you want an INTEGER to store these single characters.We use INTEGERdata type by default which works in all databases, and more, it allowsoperators like on SQL queries without having to convert it usingASCII functions.Unfortunately, using and on SQL queries of characters converted from Unicode to numbers using a trivial translation is meaningless due to various ordering schemes that are often locale-dependent.The origin of the problem Michael reported is down to the lack of ajdbc-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 gotthe problem.I'd say it is the lack of a default in the specification as opposed to the lack of a jdbc-type in the metadata. The usage for single character columns is often trivial: M/F for gender, A/B/C/D/E for answer, 0/1 for binary data where the database doesn't understand boolean, etc.To summarize, I'd prefer that the default be whatever the JDO experts agree is the default, and we adapt the TCK to specify the jdbc-type in case the default turns out to be something other than CHAR.Ok? I'll see you on the JDO expert alias. :-)CraigOk. 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 ofdatabases and data types. The most obvious choice to me is the mostcommon type among these databases. Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp! smime.p7s Description: S/MIME cryptographic signature
Re: Binding a char to PreparedStatement
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, 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() -- --- Michael Watzek [EMAIL PROTECTED] Engineering GmbH mailto:[EMAIL PROTECTED]Buelowstr. 66 Tel.: ++49/30/235 520 36 10783 Berlin - Germany Fax.: ++49/30/217 520 12 http://www.spree.de/ ---
Re: Binding a char to PreparedStatement
Hi,On Jul 20, 2005, at 8:49 AM, Michael Watzek wrote: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?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.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?I don't want to add the jdbc-type because it's not supposed to be needed for the simple TCK tests. I had thought that we should add jdbc-type for completeness, just to make sure the JDO implementation can handle it, but not in the general case.CraigRegards,Michael Hi Michael, we have a lot of derby errors "ERROR 22001: A truncation error wasencountered 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 JDBCdirectly. 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.setShort2) PreparedStatement.setInt3) PreparedStatement.setObject4) 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() -- ---Michael Watzek [EMAIL PROTECTED] Engineering GmbHmailto:[EMAIL PROTECTED] Buelowstr. 66Tel.: ++49/30/235 520 36 10783 Berlin - GermanyFax.: ++49/30/217 520 12 http://www.spree.de/--- Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp! smime.p7s Description: S/MIME cryptographic signature
Re: Binding a char to PreparedStatement
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? 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
Re: Binding a char to PreparedStatement
Hi Andy,On Jul 20, 2005, at 9:40 AM, Andy Jefferson wrote:"The jdbc-type attribute declares the type of the column in thedatabase. This type is defaulted based on the type of the fieldbeing mapped."Does this mean, that an implementation may choose for an default? That was not the intent. The intent was that the JDBC type would beobvious. ;-) And the "obvious" jdbc-type for char and Character isCHAR. 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 ...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. Thanks,Craig-- AndyJava Persistent Objects - JPOX Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp!
smime.p7s
Description: S/MIME cryptographic signature
Re: Binding a char to PreparedStatement
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 ... 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
Re: Binding a char to PreparedStatement
Hi Andy,On Jul 20, 2005, at 9:57 AM, Andy Jefferson wrote:Well in the case of a char it is, but in the case of a java double forexample ? It all depends on the RDBMS. Some RDBMS support DOUBLE, someDECIMAL, some NUMERIC, some have other types ... This is the jdbc-type ("generic sql type"), not the sql-type we'retalking about (there is a separate sql-type that the user can specifyif 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 tomap 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). 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?We've always encouraged our users to specify the jdbc-type if they want things to be JDO-impl independent for this very reason.Right, so your users just want to specify the database specific type because they know what kind of database they are using.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 evenWhere does this give you trouble?Thanks,Craig-- AndyJava Persistent Objects - JPOX Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp!
smime.p7s
Description: S/MIME cryptographic signature
Re: Binding a char to PreparedStatement
Hi Craig, 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). 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
Re: Binding a char to PreparedStatement
Hi Andy,On Jul 20, 2005, at 10:24 AM, Andy Jefferson wrote:Hi Craig, I'm also talking about the jdbc-type (or to be specificjavax.sql.Types). Sothe JDO impl should always take JDBC Types.DOUBLE when the user hasa Javadouble field? Many JDBC drivers *don't* provide a type mapping forTypes.DOUBLE (many don't provide support for several of the"standard" JDBCtypes). I'm trying to see where the confusion is. The attribute is onlyspecified if the user wants to override the default for the fieldtype. 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 ?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.But regardless of what was used for schema creation, I expected that when binding values to a prepared statement, the jdo impl would use the setDouble method, and when retrieving values from a result set, it would use getDouble. Similarly, unless the user had explicitly said to use jdbc-type INTEGER, I'd expect the jdo impl to use setString and getString for char fields. I had not expected that the jdbc-type would be used for deciding how to perform parameter or result set bindings. But I can see how you could implement JPOX using the jdbc-type as a clue.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.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.It wasn't clear to me that the issue was flexibility, just changing the way you bind to result set and prepared statements for char types.Craig-- Andy Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp! smime.p7s Description: S/MIME cryptographic signature
