Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Date: Mon, 07 Nov 2005 10:34:45 -0800
From: "David W. Van Couvering" <David.Vancouvering@Sun.COM>
Subject: Re: Duplication of first 5 characters of a SQLState
In-reply-to: <436F6775.7080209@sun.com>
To: derby-dev@db.apache.org
Message-id: <436F9E45.3010505@sun.com>
MIME-version: 1.0
Content-type: multipart/mixed; boundary="Boundary_(ID_YqamvR7LgMiMwyZpzLgN0Q)"
User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
References: <58ed70f50511041645i3d5bd714p2440c94ad13d228b@mail.gmail.com>
 <436F6775.7080209@sun.com>

This is a multi-part message in MIME format.

--Boundary_(ID_YqamvR7LgMiMwyZpzLgN0Q)
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT

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
>>  
>>
> 

--Boundary_(ID_YqamvR7LgMiMwyZpzLgN0Q)
Content-type: text/x-vcard; charset=utf-8; name=david.vancouvering.vcf
Content-transfer-encoding: 7BIT
Content-disposition: attachment; filename=david.vancouvering.vcf

begin:vcard
fn:David W Van Couvering
n:Van Couvering;David W
org:Sun Microsystems, Inc.;Database Technology Group
email;internet:david.vancouvering@sun.com
title:Senior Staff Software Engineer
tel;work:510-550-6819
tel;cell:510-684-7281
x-mozilla-html:TRUE
version:2.1
end:vcard


--Boundary_(ID_YqamvR7LgMiMwyZpzLgN0Q)--