You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Deepa Remesh <dr...@gmail.com> on 2005/11/05 01:45:14 UTC
Duplication of first 5 characters of a SQLState
For DERBY-682, I was adding SQLStates with severity >=
session_severity to the test lang/errorCode.sql. While doing this, I
found that the SQLState 'XJ081' appears two times in the message
properties file with two different severity levels. Output from ij is:
ij> select * from new org.apache.derby.diag.ErrorMessages() c where
sql_state = 'XJ081';
SQL_&|MESSAGE
|SEVERITY
--------------------------------------------------------------------------------------------------------------------------------------------------
XJ081|Invalid value '{0}' passed as parameter '{1}' to method '{2}'
|20000
XJ081|Conflicting create/restore/recovery attributes specified.
|40000
SQLState.java has this:
String INVALID_API_PARAMETER = "XJ081.S";
String CONFLICTING_RESTORE_ATTRIBUTES = "XJ081.C";
I checked the code and both these are exceptions are thrown from the code.
The Javadoc for org.apache.derby.iapi.reference.SQLState says this:
"If the state is seven characters long then only the first five will
be seen by the error reporting code and exception." Can this
duplication of first 5 characters in SQLState cause any problems?
Thanks,
Deepa
Re: Duplication of first 5 characters of a SQLState
Posted by "David W. Van Couvering" <Da...@Sun.COM>.
It is possible to have multiple messages with the same SQL state. For
example, 22007 is specifically called out in the SQL spec for "invalid
datetime format."
In HADB we actually use the same SQL State for many different errors
(they are distinguishable by the SQLException errno rather than by SQL
State).
That said, it is likely that some or all of these are just mistakes and
have no rationale. SQL State classes 42 and XJ are not defined in the
spec (at least as far as I can find) so they are Derby-specific.
David
Rick Hillegas wrote:
> Hi Deepa,
>
> Sql states are supposed to uniquely identify error conditions. To me
> these look like different errors, each of which should have its own sql
> state. Could you fix this while you're in there? It looks like there are
> four sql states whose codes are duplicated:
>
> ij> select sql_state, count(sql_state)
> from new org.apache.derby.diag.ErrorMessages() c
> group by sql_state
> having count(sql_state) > 1
> ;
> SQL_&|2
> -----------------
> 22007|2
> 42601|2
> 42815|2
> XJ081|2
>
> The duplications of 42601 and XJ081 look like mistakes to me.
>
> The other two sql states (22007 and 42815) have 11 character codes. The
> header comment in SQLState.java explains the meaning of 5, 7, and 9
> character codes. But I don't see any explanation of these 11 character
> codes. Can anyone shed some light on this?
>
> Thanks,
> -Rick
>
> Deepa Remesh wrote:
>
>> For DERBY-682, I was adding SQLStates with severity >=
>> session_severity to the test lang/errorCode.sql. While doing this, I
>> found that the SQLState 'XJ081' appears two times in the message
>> properties file with two different severity levels. Output from ij is:
>> ij> select * from new org.apache.derby.diag.ErrorMessages() c where
>> sql_state = 'XJ081';
>> SQL_&|MESSAGE
>> |SEVERITY
>> --------------------------------------------------------------------------------------------------------------------------------------------------
>>
>> XJ081|Invalid value '{0}' passed as parameter '{1}' to method '{2}'
>> |20000
>> XJ081|Conflicting create/restore/recovery attributes specified.
>> |40000
>>
>> SQLState.java has this:
>> String INVALID_API_PARAMETER = "XJ081.S";
>> String CONFLICTING_RESTORE_ATTRIBUTES = "XJ081.C";
>> I checked the code and both these are exceptions are thrown from the
>> code.
>>
>> The Javadoc for org.apache.derby.iapi.reference.SQLState says this:
>> "If the state is seven characters long then only the first five will
>> be seen by the error reporting code and exception." Can this
>> duplication of first 5 characters in SQLState cause any problems?
>>
>> Thanks,
>> Deepa
>>
>>
>
Re: Duplication of first 5 characters of a SQLState
Posted by "David W. Van Couvering" <Da...@Sun.COM>.
As far as I can tell 'XJ' represents JDBC exceptions/messages, as this
is where the majority of duplicate messages between network and embedded
clients appear to be located, in my investigations.
David
Dag H. Wanvik wrote:
> Hi,
> looking at the Javadoc for SQLState, I noticed that it describes some
> state ranges, e.g. for Basic services, Connectivity, Language, but
> these explanations are not comprehensive, e.g. states starting with
> "XJ.." are not mentioned. What's the story here?
> Shouldn't this be augmented?
>
> Dag
>
>
>>>>>>"Rick" == Rick Hillegas <Ri...@Sun.COM> wrote:
>
> Rick>
> Rick> Hi Deepa,
> Rick> Sql states are supposed to uniquely identify error conditions. To me
> Rick> these look like different errors, each of which should have its own sql
> Rick> state. Could you fix this while you're in there? It looks like there are
> Rick> four sql states whose codes are duplicated:
> Rick>
> Rick> ij> select sql_state, count(sql_state)
> Rick> from new org.apache.derby.diag.ErrorMessages() c
> Rick> group by sql_state
> Rick> having count(sql_state) > 1
> Rick> ;
> Rick> SQL_&|2
> Rick> -----------------
> Rick> 22007|2
> Rick> 42601|2
> Rick> 42815|2
> Rick> XJ081|2
> Rick>
> Rick> The duplications of 42601 and XJ081 look like mistakes to me.
> Rick>
> Rick> The other two sql states (22007 and 42815) have 11 character codes. The
> Rick> header comment in SQLState.java explains the meaning of 5, 7, and 9
> Rick> character codes. But I don't see any explanation of these 11 character
> Rick> codes. Can anyone shed some light on this?
> Rick>
> Rick> Thanks,
> Rick> -Rick
> Rick>
> Rick> Deepa Remesh wrote:
> Rick>
> Rick> >For DERBY-682, I was adding SQLStates with severity >=
> Rick> >session_severity to the test lang/errorCode.sql. While doing this, I
> Rick> >found that the SQLState 'XJ081' appears two times in the message
> Rick> >properties file with two different severity levels. Output from ij is:
> Rick> >ij> select * from new org.apache.derby.diag.ErrorMessages() c where
> Rick> >sql_state = 'XJ081';
> Rick> >SQL_&|MESSAGE
> Rick> >
> Rick> >|SEVERITY
> Rick> >--------------------------------------------------------------------------------------------------------------------------------------------------
> Rick> >XJ081|Invalid value '{0}' passed as parameter '{1}' to method '{2}'
> Rick> > |20000
> Rick> >XJ081|Conflicting create/restore/recovery attributes specified.
> Rick> > |40000
> Rick> >
> Rick> >SQLState.java has this:
> Rick> >String INVALID_API_PARAMETER = "XJ081.S";
> Rick> >String CONFLICTING_RESTORE_ATTRIBUTES = "XJ081.C";
> Rick> >I checked the code and both these are exceptions are thrown from the code.
> Rick> >
> Rick> >The Javadoc for org.apache.derby.iapi.reference.SQLState says this:
> Rick> >"If the state is seven characters long then only the first five will
> Rick> >be seen by the error reporting code and exception." Can this
> Rick> >duplication of first 5 characters in SQLState cause any problems?
> Rick> >
> Rick> >Thanks,
> Rick> >Deepa
> Rick> >
> Rick> >
> Rick>
Re: Duplication of first 5 characters of a SQLState
Posted by Rick Hillegas <Ri...@Sun.COM>.
I agree. To roll up the two questions, can someone shed light on the
following:
o What is supposed to be encoded by the trailing 3 characters of
11-character sql states?
o Why isn't the XJ* series of sql states gathered into a tidy, commented
block as is done elsewhere in this file?
Thanks,
-Rick
Dag H. Wanvik wrote:
>Hi,
>looking at the Javadoc for SQLState, I noticed that it describes some
>state ranges, e.g. for Basic services, Connectivity, Language, but
>these explanations are not comprehensive, e.g. states starting with
>"XJ.." are not mentioned. What's the story here?
>Shouldn't this be augmented?
>
>Dag
>
>
>
>>>>>>"Rick" == Rick Hillegas <Ri...@Sun.COM> wrote:
>>>>>>
>>>>>>
>Rick>
>Rick> Hi Deepa,
>Rick> Sql states are supposed to uniquely identify error conditions. To me
>Rick> these look like different errors, each of which should have its own sql
>Rick> state. Could you fix this while you're in there? It looks like there are
>Rick> four sql states whose codes are duplicated:
>Rick>
>Rick> ij> select sql_state, count(sql_state)
>Rick> from new org.apache.derby.diag.ErrorMessages() c
>Rick> group by sql_state
>Rick> having count(sql_state) > 1
>Rick> ;
>Rick> SQL_&|2
>Rick> -----------------
>Rick> 22007|2
>Rick> 42601|2
>Rick> 42815|2
>Rick> XJ081|2
>Rick>
>Rick> The duplications of 42601 and XJ081 look like mistakes to me.
>Rick>
>Rick> The other two sql states (22007 and 42815) have 11 character codes. The
>Rick> header comment in SQLState.java explains the meaning of 5, 7, and 9
>Rick> character codes. But I don't see any explanation of these 11 character
>Rick> codes. Can anyone shed some light on this?
>Rick>
>Rick> Thanks,
>Rick> -Rick
>Rick>
>Rick> Deepa Remesh wrote:
>Rick>
>Rick> >For DERBY-682, I was adding SQLStates with severity >=
>Rick> >session_severity to the test lang/errorCode.sql. While doing this, I
>Rick> >found that the SQLState 'XJ081' appears two times in the message
>Rick> >properties file with two different severity levels. Output from ij is:
>Rick> >ij> select * from new org.apache.derby.diag.ErrorMessages() c where
>Rick> >sql_state = 'XJ081';
>Rick> >SQL_&|MESSAGE
>Rick> >
>Rick> >|SEVERITY
>Rick> >--------------------------------------------------------------------------------------------------------------------------------------------------
>Rick> >XJ081|Invalid value '{0}' passed as parameter '{1}' to method '{2}'
>Rick> > |20000
>Rick> >XJ081|Conflicting create/restore/recovery attributes specified.
>Rick> > |40000
>Rick> >
>Rick> >SQLState.java has this:
>Rick> >String INVALID_API_PARAMETER = "XJ081.S";
>Rick> >String CONFLICTING_RESTORE_ATTRIBUTES = "XJ081.C";
>Rick> >I checked the code and both these are exceptions are thrown from the code.
>Rick> >
>Rick> >The Javadoc for org.apache.derby.iapi.reference.SQLState says this:
>Rick> >"If the state is seven characters long then only the first five will
>Rick> >be seen by the error reporting code and exception." Can this
>Rick> >duplication of first 5 characters in SQLState cause any problems?
>Rick> >
>Rick> >Thanks,
>Rick> >Deepa
>Rick> >
>Rick> >
>Rick>
>
>
Re: Duplication of first 5 characters of a SQLState
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Hi,
looking at the Javadoc for SQLState, I noticed that it describes some
state ranges, e.g. for Basic services, Connectivity, Language, but
these explanations are not comprehensive, e.g. states starting with
"XJ.." are not mentioned. What's the story here?
Shouldn't this be augmented?
Dag
>>>>> "Rick" == Rick Hillegas <Ri...@Sun.COM> wrote:
Rick>
Rick> Hi Deepa,
Rick> Sql states are supposed to uniquely identify error conditions. To me
Rick> these look like different errors, each of which should have its own sql
Rick> state. Could you fix this while you're in there? It looks like there are
Rick> four sql states whose codes are duplicated:
Rick>
Rick> ij> select sql_state, count(sql_state)
Rick> from new org.apache.derby.diag.ErrorMessages() c
Rick> group by sql_state
Rick> having count(sql_state) > 1
Rick> ;
Rick> SQL_&|2
Rick> -----------------
Rick> 22007|2
Rick> 42601|2
Rick> 42815|2
Rick> XJ081|2
Rick>
Rick> The duplications of 42601 and XJ081 look like mistakes to me.
Rick>
Rick> The other two sql states (22007 and 42815) have 11 character codes. The
Rick> header comment in SQLState.java explains the meaning of 5, 7, and 9
Rick> character codes. But I don't see any explanation of these 11 character
Rick> codes. Can anyone shed some light on this?
Rick>
Rick> Thanks,
Rick> -Rick
Rick>
Rick> Deepa Remesh wrote:
Rick>
Rick> >For DERBY-682, I was adding SQLStates with severity >=
Rick> >session_severity to the test lang/errorCode.sql. While doing this, I
Rick> >found that the SQLState 'XJ081' appears two times in the message
Rick> >properties file with two different severity levels. Output from ij is:
Rick> >ij> select * from new org.apache.derby.diag.ErrorMessages() c where
Rick> >sql_state = 'XJ081';
Rick> >SQL_&|MESSAGE
Rick> >
Rick> >|SEVERITY
Rick> >--------------------------------------------------------------------------------------------------------------------------------------------------
Rick> >XJ081|Invalid value '{0}' passed as parameter '{1}' to method '{2}'
Rick> > |20000
Rick> >XJ081|Conflicting create/restore/recovery attributes specified.
Rick> > |40000
Rick> >
Rick> >SQLState.java has this:
Rick> >String INVALID_API_PARAMETER = "XJ081.S";
Rick> >String CONFLICTING_RESTORE_ATTRIBUTES = "XJ081.C";
Rick> >I checked the code and both these are exceptions are thrown from the code.
Rick> >
Rick> >The Javadoc for org.apache.derby.iapi.reference.SQLState says this:
Rick> >"If the state is seven characters long then only the first five will
Rick> >be seen by the error reporting code and exception." Can this
Rick> >duplication of first 5 characters in SQLState cause any problems?
Rick> >
Rick> >Thanks,
Rick> >Deepa
Rick> >
Rick> >
Rick>
--
Dag H. Wanvik
Sun Microsystems, Web Services, Database Technology Group
Haakon VII gt. 7b, N-7485 Trondheim, Norway
Tel: x43496/+47 73842196, Fax: +47 73842101
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NOTICE: This email message is for the sole use of the intended
recipient(s) and may contain confidential and privileged
information. Any unauthorized review, use, disclosure or distribution
is prohibited. If you are not the intended recipient, please contact
the sender by reply email and destroy all copies of the original
message.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Re: Duplication of first 5 characters of a SQLState
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Deepa,
Sql states are supposed to uniquely identify error conditions. To me
these look like different errors, each of which should have its own sql
state. Could you fix this while you're in there? It looks like there are
four sql states whose codes are duplicated:
ij> select sql_state, count(sql_state)
from new org.apache.derby.diag.ErrorMessages() c
group by sql_state
having count(sql_state) > 1
;
SQL_&|2
-----------------
22007|2
42601|2
42815|2
XJ081|2
The duplications of 42601 and XJ081 look like mistakes to me.
The other two sql states (22007 and 42815) have 11 character codes. The
header comment in SQLState.java explains the meaning of 5, 7, and 9
character codes. But I don't see any explanation of these 11 character
codes. Can anyone shed some light on this?
Thanks,
-Rick
Deepa Remesh wrote:
>For DERBY-682, I was adding SQLStates with severity >=
>session_severity to the test lang/errorCode.sql. While doing this, I
>found that the SQLState 'XJ081' appears two times in the message
>properties file with two different severity levels. Output from ij is:
>ij> select * from new org.apache.derby.diag.ErrorMessages() c where
>sql_state = 'XJ081';
>SQL_&|MESSAGE
>
>|SEVERITY
>--------------------------------------------------------------------------------------------------------------------------------------------------
>XJ081|Invalid value '{0}' passed as parameter '{1}' to method '{2}'
> |20000
>XJ081|Conflicting create/restore/recovery attributes specified.
> |40000
>
>SQLState.java has this:
>String INVALID_API_PARAMETER = "XJ081.S";
>String CONFLICTING_RESTORE_ATTRIBUTES = "XJ081.C";
>I checked the code and both these are exceptions are thrown from the code.
>
>The Javadoc for org.apache.derby.iapi.reference.SQLState says this:
>"If the state is seven characters long then only the first five will
>be seen by the error reporting code and exception." Can this
>duplication of first 5 characters in SQLState cause any problems?
>
>Thanks,
>Deepa
>
>