XCL47 SQLState duplicated in messages_en.properties?

• Key: DERBY-180
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Minor
• Assignee: Andrew McIntyre
• Reporter: George Baklarz
• Component/s: SQL
• Affects Version/s: 10.0.2.0
• Fix Version/s: 10.1.1.0

Description

In the messages_en.properties file, there are two items that are similar:

XCL47.S=Use of ''{0}'' requires database to be upgraded from version {1} to version {2} or later.
XCL478.S=The requested function can not reference tables in SESSION schema.

SQLSTATEs are supposed to be 5 characters long. When I issue a CREATE VIEW on a SESSION table, I receive the proper error message:

ij> declare global temporary table x (a int) not logged;
0 rows inserted/updated/deleted
ij> create view z as (select * from session.x);
ERROR XCL47: The requested function can not reference tables in SESSION schema.

So, was XCL478 truncated to XCL47? Does this mean that if I tried to do something that invokes the other version of XCL47 that I will get the wrong message? I suspect one of the message numbers needs to be changed and that the SQL error be fixed to be 5 characters long instead of 6.

Comments

[Dyre Tjeldvoll]

Yes the messageId was truncated. I think you will get the correct message, because the full messageId is stored in the
Exception object. getSQLState() will only return a string made up of the first 5 characters. getMessageId() (not a part of
SQLException, but found in EmbedSQLException) will return the full string, including ".S".

I haven't figured out how ij finds the string to print, but by modifying SimpleApp.java (renamed Try.java) to trigger your exception you can see the that the full string is in the exception thrwon:

 dt136804@atum10~/java$ java -classpath $(find-jars.sh ~/derbyjars):. Try
Try starting in embedded mode.
Loaded the appropriate driver.
Connected to and created database derbyDB
Created table derbyDB
Inserted 1956 Webster
Inserted 1910 Union
Updated 1956 Webster to 180 Grand
Updated 180 Grand to 300 Lakeshore
Verified the rows
 Venturing into unknown territory...
exception thrown:
SQL Exception: The requested function can not reference tables in SESSION schema.
SQLState: XCL47 MessageId: XCL478.S
--> note the difference between SQLState and MessageId

ERROR XCL47: The requested function can not reference tables in SESSION schema.
--> This looks very much like the string printed by ij, but I don't know if printStackTrace and ij use a common function --> for this
 
       at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:301)
        at org.apache.derby.impl.sql.compile.CreateViewNode.bindViewDefinition(CreateViewNode.java:281)
        at org.apache.derby.impl.sql.compile.CreateViewNode.bind(CreateViewNode.java:184)
        at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:332)
        at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:107)
        at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:688)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:501)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:475)
        at Try.go(Try.java:162)
        at Try.main(Try.java:64)
null
Try finished

I think you're right about the MessageId for XCL478 being too long. I tried to use svn blame to see when this was done
(and perhaps why), but it looks like this was inherited from Cloudscape. From what I could see, there are only about 50 other XCLxx messages, so it should be easy to find a unique id.

Of course, changing error codes/ids is dangerous, since you risk breaking existing client code that relies on the old (incorrect) behavior. Maybe some of the Derby/Cloudscape gurus can shed some more light on this?

[Mamta A. Satoor]

It seems to me that the MessageId XCL478 may have been an oversight when it was picked up to be 6 characters in length.

[Andrew McIntyre]

attaching patch previously submitted to derby-dev

[Andrew McIntyre]

Committed, revision 179260.