You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Brett Wooldridge <bw...@alterpoint.com> on 2008/03/16 13:09:39 UTC

Derby and Bitronix JTA connection failure

Hello list,

I am stuck trying to figure out an issue with using the Bitronix JTA Transaction Manager with Derby (v10.3.2.1).  Here's the details:

I'm running Derby as a network server on the standard port.  I have a unit test which uses the ClientXADatasource to connect to my Derby database, and this succeeds.

The client code looks like this:

        ClientXADataSource ds = new ClientXADataSource();        ds.setDatabaseName("ziptie");        ds.setPortNumber(1527);        ds.setServerName("localhost");        Connection connection = ds.getConnection();
As I said, this client connects successfully, and the derby.log records this:

Connection number: 2.
2008-03-16 11:45:05.302 GMT Thread[DRDAConnThread_6,5,derby.daemons] (DATABASE = ziptie), (DRDAID = {2}), Apache Derby Network Server connected to database ziptie

Then, I try to start an application which uses the Bitronix TM to perform a similar connection to Derby.  However, this connection fails as recorded by the derby.log as well:

Connection number: 3.
2008-03-16 11:45:10.817 GMT Thread[DRDAConnThread_6,5,derby.daemons] (DATABASE = ziptie), (DRDAID = {3}), Database not available

The properties for Bitronix look like this:

resource.ds.className=org.apache.derby.jdbc.ClientXADataSource resource.ds.uniqueName=ziptie
resource.ds.driverProperties.databaseName=ziptie resource.ds.driverProperties.serverName=localhost resource.ds.driverProperties.portNumber=1527

When the connection fails, I get a network client trace that looks like this:

(snip)
derby] BEGIN TRACE_DIAGNOSTICS
[derby][SQLException@a5cd0c] java.sql.SQLException
[derby][SQLException@a5cd0c][Sqlca@ecf7fa] DERBY SQLCA from server
[derby][SQLException@a5cd0c][Sqlca@ecf7fa] SqlCode        = -1
[derby][SQLException@a5cd0c][Sqlca@ecf7fa] SqlErrd        = { 0, 0, 0, 0, 0, 0 }
[derby][SQLException@a5cd0c][Sqlca@ecf7fa] SqlErrmc       = Database not available
[derby][SQLException@a5cd0c][Sqlca@ecf7fa] SqlErrp        = CSS10030
[derby][SQLException@a5cd0c][Sqlca@ecf7fa] SqlState       = 08006
[derby][SQLException@a5cd0c][Sqlca@ecf7fa] SqlWarn        =
[derby][SQLException@a5cd0c] SQL state  = 08006
[derby][SQLException@a5cd0c] Error code = -1
[derby][SQLException@a5cd0c] Tokens     = Database not available
[derby][SQLException@a5cd0c] Stack trace follows
org.apache.derby.client.am.SqlException: DERBY SQL error: SQLCODE: -1, SQLSTATE: 08006, SQLERRMC: Database not available
    at org.apache.derby.client.am.Connection.completeSqlca(Unknown Source)
(snip)

I have enabled derby.drda.traceAll on the server and here is what I get for the SUCCESSFUL connection:

      (2008.3.16 11:58:53) Request fill DRDAConnThread_6 5

       RECEIVE BUFFER: EXCSAT              (ASCII)           (EBCDIC)
       0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
0000   0065D0410001005F  10410010115E8485  .e.A..._.A...^..  ..}..........;de
0010   9982A88495839481  89950009116DC485  .............m..  rbydncmain..._De
0020   9982A80020115AC4  D5C3F1F0F0F3F061  .... .Z........a  rby...!DNC10030/
0030   F1F04BF34BF14BF4  4060404DF5F6F1F7  ..K.K.K.@`@M....  10.3.1.4 - (5617
0040   F9F45D0014140414  0300072407000724  ..]........$...$  94).............
0050   0F00071440000700  0E1147D8C4C5D9C2  ....@.....G.....  .... ......QDERB
0060   E861D1E5D40026D0  0100020020106D00  .a....&..... .m.  Y/JVM..}......_.
0070   0611A20004001621  10A98997A3898540  .......!.......@  ..s......ziptie
0080   4040404040404040  404040            @@@@@@@@@@@


       (2008.3.16 11:58:53) Reply flush DRDAConnThread_6 5

       SEND BUFFER: EXCSATRD               (ASCII)           (EBCDIC)
       0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
0000   009BD04200010095  14430035115ED585  ...B.....C.5.^..  ..}....n.....;Ne
0010   A3A6969992E28599  A58599C39695A399  ................  tworkServerContr
0020   969340E2A38199A3  40D385A5859340C5  ..@.....@.....@.  ol Start Level E
0030   A58595A340C489A2  9781A38388859900  ....@...........  vent Dispatcher.
0040   1414041403000724  070007240F000714  .......$...$....  ................
0050   40000700101147C1  978183888540C485  @.....G......@..   ......Apache De
0060   9982A80018116DD5  85A3A6969992E285  ......m.........  rby..._NetworkSe
0070   99A58599C39695A3  9996930020115AC3  ............ .Z.  rverControl...!C
0080   E2E2F1F0F0F3F061  F1F04BF34BF24BF1  .......a..K.K.K.  SS10030/10.3.2.1
0090   4060404DF5F9F9F1  F1F05D0010D00200  @`@M......].....   - (599110)..}..
00A0   02000A14AC000611  A20004            ...........       ........s..


       (2008.3.16 11:58:53) Request fill DRDAConnThread_6 5

       RECEIVE BUFFER: SECCHK              (ASCII)           (EBCDIC)
       0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
0000   002DD04100010027  106E000611A20004  .-.A...'.n......  ..}......>...s..
0010   00162110A98997A3  8985404040404040  ..!.......@@@@@@  ....ziptie
0020   4040404040400007  11A0C1D7D700A8D0  @@@@@@..........        ....APP.y}
0030   01000200A2200100  162110A98997A389  ..... ...!......  ....s......zipti
0040   8540404040404040  4040404040000621  .@@@@@@@@@@@@..!  e            ...
0050   0F2407000C112EC4  D5C3F1F0F0F3F000  .$..............  .......DNC10030.
0060   3C210437C4D5C3F1  F0F0F3F0D1E5D440  <!.7...........@  ....DNC10030JVM
0070   4040404040404040  4040404040408485  @@@@@@@@@@@@@@..                de
0080   9982A88495839481  8995404040404040  ..........@@@@@@  rbydncmain
0090   4040C1D7D7404040  404000000D002FD8  @@...@@@@@..../.    APP     .....Q
00A0   E3C4E2D8D3C1E2C3  00172135D5C6F0F0  ..........!5....  TDSQLASC....NF00
00B0   F0F0F0F14BC6F2F6  F60118B774E40200  ....K.......t...  0001.F266....U..
00C0   1600350006119C04  B80006119D04B000  ..5.............  ................
00D0   06119E04B8                          .....             .....


       (2008.3.16 11:58:53) Reply flush DRDAConnThread_6 5

       SEND BUFFER: SECCHKRM               (ASCII)           (EBCDIC)
       0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
0000   0015D0420001000F  1219000611490000  ...B.........I..  ..}.............
0010   000511A4000039D0  0200020033220100  ......9.....3"..  ...u...}........
0020   0611490000000C11  2EC3E2E2F1F0F0F3  ..I.............  .........CSS1003
0030   F0000D002FD8E3C4  E2D8D3C1E2C30010  ..../...........  0....QTDSQLASC..
0040   00350006119C04B8  0006119E04B8      .5............    ..............


       (2008.3.16 11:58:53) Request fill DRDAConnThread_6 5

       RECEIVE BUFFER:                     (ASCII)           (EBCDIC)
       0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
0000   00

------------------------

And here is what I get for the UNSUCCESSFUL connection (initiated by Bitronix's connection pool):

      (2008.3.16 11:58:57) Request fill DRDAConnThread_6 5

       RECEIVE BUFFER: EXCSAT              (ASCII)           (EBCDIC)
       0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
0000   0081D0410001007B  10410028115E8485  ...A...{.A.(.^..  .a}....#.....;de
0010   9982A8849583E2A3  8199A340D385A585  ...........@....  rbydncStart Leve
0020   9340C5A58595A340  C489A29781A38388  .@.....@........  l Event Dispatch
0030   85990009116DC485  9982A80020115AC4  .....m...... .Z.  er..._Derby...!D
0040   D5C3F1F0F0F3F061  F1F04BF34BF14BF4  .......a..K.K.K.  NC10030/10.3.1.4
0050   4060404DF5F6F1F7  F9F45D0018140414  @`@M......].....   - (561794).....
0060   0300072407000724  0F0007144000071C  ...$...$....@...  ............ ...
0070   010007000E1147D8  C4C5D9C2E861D1E5  ......G......a..  .......QDERBY/JV
0080   D40026D001000200  20106D000611A200  ..&..... .m.....  M..}......_...s.
0090   0400162110A98997  A389854040404040  ...!.......@@@@@  .....ziptie
00A0   40404040404040                      @@@@@@@


       (2008.3.16 11:58:57) Reply flush DRDAConnThread_6 5

       SEND BUFFER: EXCSATRD               (ASCII)           (EBCDIC)
       0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
0000   009FD04200010099  14430035115ED585  ...B.....C.5.^..  ..}....r.....;Ne
0010   A3A6969992E28599  A58599C39695A399  ................  tworkServerContr
0020   969340E2A38199A3  40D385A5859340C5  ..@.....@.....@.  ol Start Level E
0030   A58595A340C489A2  9781A38388859900  ....@...........  vent Dispatcher.
0040   1814041403000724  070007240F000714  .......$...$....  ................
0050   4000071C01000700  101147C197818388  @.........G.....   ..........Apach
0060   8540C4859982A800  18116DD585A3A696  .@........m.....  e Derby..._Netwo
0070   9992E28599A58599  C39695A399969300  ................  rkServerControl.
0080   20115AC3E2E2F1F0  F0F3F061F1F04BF3   .Z........a..K.  ..!CSS10030/10.3
0090   4BF24BF14060404D  F5F9F9F1F1F05D00  K.K.@`@M......].  .2.1 - (599110).
00A0   10D0020002000A14  AC000611A20004    ...............   .}..........s..


       (2008.3.16 11:58:57) Request fill DRDAConnThread_6 5

       RECEIVE BUFFER: SECCHK              (ASCII)           (EBCDIC)
       0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
0000   002DD04100010027  106E000611A20004  .-.A...'.n......  ..}......>...s..
0010   00162110A98997A3  8985404040404040  ..!.......@@@@@@  ....ziptie
0020   4040404040400007  11A0C1D7D700A8D0  @@@@@@..........        ....APP.y}
0030   01000200A2200100  162110A98997A389  ..... ...!......  ....s......zipti
0040   8540404040404040  4040404040000621  .@@@@@@@@@@@@..!  e            ...
0050   0F2407000C112EC4  D5C3F1F0F0F3F000  .$..............  .......DNC10030.
0060   3C210437C4D5C3F1  F0F0F3F0D1E5D440  <!.7...........@  ....DNC10030JVM
0070   4040404040404040  4040404040408485  @@@@@@@@@@@@@@..                de
0080   9982A8849583E2A3  8199A340D385A585  ...........@....  rbydncStart Leve
0090   9340C1D7D7404040  404000000D002FD8  .@...@@@@@..../.  l APP     .....Q
00A0   E3C4E2D8D3C1E2C3  00172135D5C6F0F0  ..........!5....  TDSQLASC....NF00
00B0   F0F0F0F14BC6F2F6  F70118B774F2DE00  ....K.......t...  0001.F267....2..
00C0   1600350006119C04  B80006119D04B000  ..5.............  ................
00D0   06119E04B8                          .....             .....


       (2008.3.16 11:58:57) Reply flush DRDAConnThread_6 5

       SEND BUFFER: SECCHKRM               (ASCII)           (EBCDIC)
       0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
0000   0015D0420001000F  1219000611490000  ...B.........I..  ..}.............
0010   000511A4000026D0  5200020020221A00  ......&.R... "..  ...u...}........
0020   0611490008001621  107A697074696520  ..I....!.ziptie   .........:......
0030   2020202020202020  2020200023D05300             .#.S.  .............}..
0040   02000D002FD8E3C4  E2D8D3C1E2C30010  ..../...........  .....QTDSQLASC..
0050   00350006119C04B8  0006119E04B8005D  .5.............]  ...............)
0060   D003000200572408  00FFFFFFFF303830  .....W$......080  }...............
0070   3036435353313030  3330000000000000  06CSS10030......  ................
0080   0000000000000000  0000000000000000  ................  ................
0090   0000002020202020  2020202020200000  ...           ..  ................
00A0   0016446174616261  7365206E6F742061  ..Database not a  .../././...>?../
00B0   7661696C61626C65  0000FF            vailable...       ./.%/.%....


       (2008.3.16 11:58:57) Request fill DRDAConnThread_6 5

       RECEIVE BUFFER:                     (ASCII)           (EBCDIC)
       0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
0000   00

I can't really read these DRDA traces that well, so I can't figure out what the difference is between the successful connection and the failure.  Any help from one of the developers would be greatly appreciated.

-Brett

Re: Derby and Bitronix JTA connection failure

Posted by Kristian Waagan <Kr...@Sun.COM>.
Dyre.Tjeldvoll@Sun.COM wrote:
> Brett Wooldridge <bw...@alterpoint.com> writes:
[ snip - details ]

> 
> As you probably have figured out yourself, it is the SECCHK command
> which fails when you use the TXNMGR. I did notice that you are using a
> a different versions for the server andt the client. (10.3.2.1 rev
> 599110) for the server vs (10.3.1.4 rev
> 561794) for the client. That should not be a problem, but could be an
> easy thing to test...
> 

Hi Dyre,

I didn't notice this before after I posted my message, but I also tried 
with the revisions you mentioned.
The repro still worked, so it doesn't seem to be a problem with those 
two versions.



BTW, Brett:
What kind of BTM data source do you use?
And does the error happen when you do <BTM-DataSource>.init()?
It could be an idea to stop the network server to provoke the 
"Connection refused" exception and look at the stack trace. Which path 
is taken to create the connection?
(I'm posting the one I get for reference)


-- 
Kristian


Exception in thread "main" 
bitronix.tm.resource.ResourceConfigurationException: cannot create JDBC 
datasource named derby1
         at 
bitronix.tm.resource.jdbc.PoolingDataSource.init(PoolingDataSource.java:50)
         at jtatest.Test.main(Test.java:27)
Caused by: java.sql.SQLTransientConnectionException: 
java.net.ConnectException : Error connecting to server localhost on port 
1527 with message Connection refused.
         at 
org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown 
Source)
         at 
org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
         at 
org.apache.derby.client.ClientPooledConnection.<init>(Unknown Source) 
      at org.apache.derby.client.ClientXAConnection.<init>(Unknown Source)
         at org.apache.derby.client.ClientXAConnection40.<init>(Unknown 
Source)
         at 
org.apache.derby.client.net.ClientJDBCObjectFactoryImpl40.newClientXAConnection(Unknown 
Source)
         at 
org.apache.derby.jdbc.ClientXADataSource.getXAConnectionX(Unknown Source)
         at 
org.apache.derby.jdbc.ClientXADataSource.getXAConnection(Unknown Source)
         at 
org.apache.derby.jdbc.ClientXADataSource.getXAConnection(Unknown Source)
         at 
bitronix.tm.resource.jdbc.PoolingDataSource.createPooledConnection(PoolingDataSource.java:168)
         at 
bitronix.tm.resource.common.XAPool.createPooledObject(XAPool.java:200)
         at bitronix.tm.resource.common.XAPool.<init>(XAPool.java:49)
         at 
bitronix.tm.resource.jdbc.PoolingDataSource.buildXAPool(PoolingDataSource.java:68)
         at 
bitronix.tm.resource.jdbc.PoolingDataSource.init(PoolingDataSource.java:48)
         ... 1 more
Caused by: org.apache.derby.client.am.DisconnectException: 
java.net.ConnectException : Error connecting to server localhost on port 
1527 with message Connection refused.
         at org.apache.derby.client.net.NetAgent.<init>(Unknown Source)
         at org.apache.derby.client.net.NetConnection.newAgent_(Unknown 
Source)
         at org.apache.derby.client.am.Connection.initConnection(Unknown 
Source)
         at org.apache.derby.client.am.Connection.<init>(Unknown Source)
         at org.apache.derby.client.net.NetConnection.<init>(Unknown Source)
         at org.apache.derby.client.net.NetConnection40.<init>(Unknown 
Source)
         at 
org.apache.derby.client.net.ClientJDBCObjectFactoryImpl40.newNetConnection(Unknown 
Source)
         at 
org.apache.derby.client.net.NetXAConnection.createNetConnection(Unknown 
Source)
         at org.apache.derby.client.net.NetXAConnection.<init>(Unknown 
Source)
         at 
org.apache.derby.client.ClientPooledConnection.getNetXAConnection(Unknown 
Source)
         ... 13 more
Caused by: java.net.ConnectException: Connection refused
         at java.net.PlainSocketImpl.socketConnect(Native Method)
         at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
         at 
java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
         at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
         at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)
         at java.net.Socket.connect(Socket.java:518)
         at java.net.Socket.connect(Socket.java:468)
         at java.net.Socket.<init>(Socket.java:365)
         at java.net.Socket.<init>(Socket.java:179)
         at 
javax.net.DefaultSocketFactory.createSocket(SocketFactory.java:196)
         at org.apache.derby.client.net.OpenSocketAction.run(Unknown Source)
         at java.security.AccessController.doPrivileged(Native Method)
         ... 23 more



Re: Derby and Bitronix JTA connection failure

Posted by Dy...@Sun.COM.
Brett Wooldridge <bw...@alterpoint.com> writes:

> Hello list,
>
> I am stuck trying to figure out an issue with using the Bitronix JTA
> Transaction Manager with Derby (v10.3.2.1).  Here’s the details:
>
> I’m running Derby as a network server on the standard port.  I have a unit
> test which uses the ClientXADatasource to connect to my Derby database, and
> this succeeds.
>
> The client code looks like this:
>
>         ClientXADataSource ds = new ClientXADataSource();
>        ds.setDatabaseName("ziptie");        ds.setPortNumber(1527);
>        ds.setServerName("localhost");        Connection connection =
> ds.getConnection();
> As I said, this client connects successfully, and the derby.log records this:
>
> Connection number: 2.
> 2008-03-16 11:45:05.302 GMT Thread[DRDAConnThread_6,5,derby.daemons] (DATABASE
> = ziptie), (DRDAID = {2}), Apache Derby Network Server connected to database
> ziptie
>
> Then, I try to start an application which uses the Bitronix TM to perform a
> similar connection to Derby.  However, this connection fails as recorded by
> the derby.log as well:
>
> Connection number: 3.
> 2008-03-16 11:45:10.817 GMT Thread[DRDAConnThread_6,5,derby.daemons] (DATABASE
> = ziptie), (DRDAID = {3}), Database not available
>
> The properties for Bitronix look like this:
>
> resource.ds.className=org.apache.derby.jdbc.ClientXADataSource
> resource.ds.uniqueName=ziptie
> resource.ds.driverProperties.databaseName=ziptie
> resource.ds.driverProperties.serverName=localhost
> resource.ds.driverProperties.portNumber=1527
>
> When the connection fails, I get a network client trace that looks like this:
>
> (snip)
> derby] BEGIN TRACE_DIAGNOSTICS
> [derby][SQLException@a5cd0c] java.sql.SQLException
> [derby][SQLException@a5cd0c][Sqlca@ecf7fa] DERBY SQLCA from server
> [derby][SQLException@a5cd0c][Sqlca@ecf7fa] SqlCode        = -1
> [derby][SQLException@a5cd0c][Sqlca@ecf7fa] SqlErrd        = { 0, 0, 0, 0, 0, 0
> }
> [derby][SQLException@a5cd0c][Sqlca@ecf7fa] SqlErrmc       = Database not
> available
> [derby][SQLException@a5cd0c][Sqlca@ecf7fa] SqlErrp        = CSS10030
> [derby][SQLException@a5cd0c][Sqlca@ecf7fa] SqlState       = 08006
> [derby][SQLException@a5cd0c][Sqlca@ecf7fa] SqlWarn        =            
> [derby][SQLException@a5cd0c] SQL state  = 08006
> [derby][SQLException@a5cd0c] Error code = -1
> [derby][SQLException@a5cd0c] Tokens     = Database not available
> [derby][SQLException@a5cd0c] Stack trace follows
> org.apache.derby.client.am.SqlException: DERBY SQL error: SQLCODE: -1,
> SQLSTATE: 08006, SQLERRMC: Database not available
>     at org.apache.derby.client.am.Connection.completeSqlca(Unknown Source)
> (snip)
>
> I have enabled derby.drda.traceAll on the server and here is what I get for
> the SUCCESSFUL connection:

I guess you don't see a call-stack in derby.log?

Anyway, I think the error originates from 

java/engine/org/apache/derby/jdbc/EmbeddedXADataSource.java:174

and is caused by the resource adapter obtained from the database being
null (or maybe database itself is null). This sounds like a Derby bug to
me. Unless someone tells you otherwise, I suggest logging a jira issue
for this.

Be adviced though, that as long as this can only be reproduced with a
specific Transaction Manager, there is not much chance of it being
fixed, I fear. If you could find a way to replicate the behavior of the
Transaction manger the odds would be better (unless Bitronix is
free/open source product)

>
>       (2008.3.16 11:58:53) Request fill DRDAConnThread_6 5
>
>        RECEIVE BUFFER: EXCSAT              (ASCII)           (EBCDIC)
>        0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
> 0000   0065D0410001005F  10410010115E8485  .e.A..._.A...^..  ..}..........;de
> 0010   9982A88495839481  89950009116DC485  .............m..  rbydncmain..._De
> 0020   9982A80020115AC4  D5C3F1F0F0F3F061  .... .Z........a  rby...!DNC10030/
> 0030   F1F04BF34BF14BF4  4060404DF5F6F1F7  ..K.K.K.@`@M....  10.3.1.4 - (5617
> 0040   F9F45D0014140414  0300072407000724  ..]........$...$  94).............
> 0050   0F00071440000700  0E1147D8C4C5D9C2  ....@.....G.....  .... ......QDERB
> 0060   E861D1E5D40026D0  0100020020106D00  .a....&..... .m.  Y/JVM..}......_.
> 0070   0611A20004001621  10A98997A3898540  .......!.......@  ..s......ziptie
> 0080   4040404040404040  404040            @@@@@@@@@@@                       
>
>        (2008.3.16 11:58:53) Reply flush DRDAConnThread_6 5
>
>        SEND BUFFER: EXCSATRD               (ASCII)           (EBCDIC)
>        0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
> 0000   009BD04200010095  14430035115ED585  ...B.....C.5.^..  ..}....n.....;Ne
> 0010   A3A6969992E28599  A58599C39695A399  ................  tworkServerContr
> 0020   969340E2A38199A3  40D385A5859340C5  ..@.....@.....@.  ol Start Level E
> 0030   A58595A340C489A2  9781A38388859900  ....@...........  vent Dispatcher.
> 0040   1414041403000724  070007240F000714  .......$...$....  ................
> 0050   40000700101147C1  978183888540C485  @.....G......@..   ......Apache De
> 0060   9982A80018116DD5  85A3A6969992E285  ......m.........  rby..._NetworkSe
> 0070   99A58599C39695A3  9996930020115AC3  ............ .Z.  rverControl...!C
> 0080   E2E2F1F0F0F3F061  F1F04BF34BF24BF1  .......a..K.K.K.  SS10030/10.3.2.1
> 0090   4060404DF5F9F9F1  F1F05D0010D00200  @`@M......].....   - (599110)..}..
> 00A0   02000A14AC000611  A20004            ...........       ........s..     
>
>        (2008.3.16 11:58:53) Request fill DRDAConnThread_6 5
>
>        RECEIVE BUFFER: SECCHK              (ASCII)           (EBCDIC)
>        0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
> 0000   002DD04100010027  106E000611A20004  .-.A...'.n......  ..}......>...s..
> 0010   00162110A98997A3  8985404040404040  ..!.......@@@@@@  ....ziptie      
> 0020   4040404040400007  11A0C1D7D700A8D0  @@@@@@..........        ....APP.y}
> 0030   01000200A2200100  162110A98997A389  ..... ...!......  ....s......zipti
> 0040   8540404040404040  4040404040000621  .@@@@@@@@@@@@..!  e            ...
> 0050   0F2407000C112EC4  D5C3F1F0F0F3F000  .$..............  .......DNC10030.
> 0060   3C210437C4D5C3F1  F0F0F3F0D1E5D440  <!.7...........@  ....DNC10030JVM
> 0070   4040404040404040  4040404040408485  @@@@@@@@@@@@@@..                de
> 0080   9982A88495839481  8995404040404040  ..........@@@@@@  rbydncmain      
> 0090   4040C1D7D7404040  404000000D002FD8  @@...@@@@@..../.    APP     .....Q
> 00A0   E3C4E2D8D3C1E2C3  00172135D5C6F0F0  ..........!5....  TDSQLASC....NF00
> 00B0   F0F0F0F14BC6F2F6  F60118B774E40200  ....K.......t...  0001.F266....U..
> 00C0   1600350006119C04  B80006119D04B000  ..5.............  ................
> 00D0   06119E04B8                          .....             .....           
>
>        (2008.3.16 11:58:53) Reply flush DRDAConnThread_6 5
>
>        SEND BUFFER: SECCHKRM               (ASCII)           (EBCDIC)
>        0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
> 0000   0015D0420001000F  1219000611490000  ...B.........I..  ..}.............
> 0010   000511A4000039D0  0200020033220100  ......9.....3"..  ...u...}........
> 0020   0611490000000C11  2EC3E2E2F1F0F0F3  ..I.............  .........CSS1003
> 0030   F0000D002FD8E3C4  E2D8D3C1E2C30010  ..../...........  0....QTDSQLASC..
> 0040   00350006119C04B8  0006119E04B8      .5............    ..............  
>
>        (2008.3.16 11:58:53) Request fill DRDAConnThread_6 5
>
>        RECEIVE BUFFER:                     (ASCII)           (EBCDIC)
>        0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
> 0000   00
>
> ------------------------
>
> And here is what I get for the UNSUCCESSFUL connection (initiated by
> Bitronix’s connection pool):
>
>       (2008.3.16 11:58:57) Request fill DRDAConnThread_6 5
>
>        RECEIVE BUFFER: EXCSAT              (ASCII)           (EBCDIC)
>        0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
> 0000   0081D0410001007B  10410028115E8485  ...A...{.A.(.^..  .a}....#.....;de
> 0010   9982A8849583E2A3  8199A340D385A585  ...........@....  rbydncStart Leve
> 0020   9340C5A58595A340  C489A29781A38388  .@.....@........  l Event Dispatch
> 0030   85990009116DC485  9982A80020115AC4  .....m...... .Z.  er..._Derby...!D
> 0040   D5C3F1F0F0F3F061  F1F04BF34BF14BF4  .......a..K.K.K.  NC10030/10.3.1.4
> 0050   4060404DF5F6F1F7  F9F45D0018140414  @`@M......].....   - (561794).....
> 0060   0300072407000724  0F0007144000071C  ...$...$....@...  ............ ...
> 0070   010007000E1147D8  C4C5D9C2E861D1E5  ......G......a..  .......QDERBY/JV
> 0080   D40026D001000200  20106D000611A200  ..&..... .m.....  M..}......_...s.
> 0090   0400162110A98997  A389854040404040  ...!.......@@@@@  .....ziptie     
> 00A0   40404040404040                      @@@@@@@                           
>
>        (2008.3.16 11:58:57) Reply flush DRDAConnThread_6 5
>
>        SEND BUFFER: EXCSATRD               (ASCII)           (EBCDIC)
>        0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
> 0000   009FD04200010099  14430035115ED585  ...B.....C.5.^..  ..}....r.....;Ne
> 0010   A3A6969992E28599  A58599C39695A399  ................  tworkServerContr
> 0020   969340E2A38199A3  40D385A5859340C5  ..@.....@.....@.  ol Start Level E
> 0030   A58595A340C489A2  9781A38388859900  ....@...........  vent Dispatcher.
> 0040   1814041403000724  070007240F000714  .......$...$....  ................
> 0050   4000071C01000700  101147C197818388  @.........G.....   ..........Apach
> 0060   8540C4859982A800  18116DD585A3A696  .@........m.....  e Derby..._Netwo
> 0070   9992E28599A58599  C39695A399969300  ................  rkServerControl.
> 0080   20115AC3E2E2F1F0  F0F3F061F1F04BF3   .Z........a..K.  ..!CSS10030/10.3
> 0090   4BF24BF14060404D  F5F9F9F1F1F05D00  K.K.@`@M......].  .2.1 - (599110).
> 00A0   10D0020002000A14  AC000611A20004    ...............   .}..........s..
>
>        (2008.3.16 11:58:57) Request fill DRDAConnThread_6 5
>
>        RECEIVE BUFFER: SECCHK              (ASCII)           (EBCDIC)
>        0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
> 0000   002DD04100010027  106E000611A20004  .-.A...'.n......  ..}......>...s..
> 0010   00162110A98997A3  8985404040404040  ..!.......@@@@@@  ....ziptie      
> 0020   4040404040400007  11A0C1D7D700A8D0  @@@@@@..........        ....APP.y}
> 0030   01000200A2200100  162110A98997A389  ..... ...!......  ....s......zipti
> 0040   8540404040404040  4040404040000621  .@@@@@@@@@@@@..!  e            ...
> 0050   0F2407000C112EC4  D5C3F1F0F0F3F000  .$..............  .......DNC10030.
> 0060   3C210437C4D5C3F1  F0F0F3F0D1E5D440  <!.7...........@  ....DNC10030JVM
> 0070   4040404040404040  4040404040408485  @@@@@@@@@@@@@@..                de
> 0080   9982A8849583E2A3  8199A340D385A585  ...........@....  rbydncStart Leve
> 0090   9340C1D7D7404040  404000000D002FD8  .@...@@@@@..../.  l APP     .....Q
> 00A0   E3C4E2D8D3C1E2C3  00172135D5C6F0F0  ..........!5....  TDSQLASC....NF00
> 00B0   F0F0F0F14BC6F2F6  F70118B774F2DE00  ....K.......t...  0001.F267....2..
> 00C0   1600350006119C04  B80006119D04B000  ..5.............  ................
> 00D0   06119E04B8                          .....             .....           
>
>        (2008.3.16 11:58:57) Reply flush DRDAConnThread_6 5
>
>        SEND BUFFER: SECCHKRM               (ASCII)           (EBCDIC)
>        0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
> 0000   0015D0420001000F  1219000611490000  ...B.........I..  ..}.............
> 0010   000511A4000026D0  5200020020221A00  ......&.R... "..  ...u...}........
> 0020   0611490008001621  107A697074696520  ..I....!.ziptie   .........:......
> 0030   2020202020202020  2020200023D05300             .#.S.  .............}..
> 0040   02000D002FD8E3C4  E2D8D3C1E2C30010  ..../...........  .....QTDSQLASC..
> 0050   00350006119C04B8  0006119E04B8005D  .5.............]  ...............)
> 0060   D003000200572408  00FFFFFFFF303830  .....W$......080  }...............
> 0070   3036435353313030  3330000000000000  06CSS10030......  ................
> 0080   0000000000000000  0000000000000000  ................  ................
> 0090   0000002020202020  2020202020200000  ...           ..  ................
> 00A0   0016446174616261  7365206E6F742061  ..Database not a  .../././...>?../
> 00B0   7661696C61626C65  0000FF            vailable...       ./.%/.%....     
>
>        (2008.3.16 11:58:57) Request fill DRDAConnThread_6 5
>
>        RECEIVE BUFFER:                     (ASCII)           (EBCDIC)
>        0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
> 0000   00
>
> I can’t really read these DRDA traces that well, so I can’t figure out what
> the difference is between the successful connection and the failure.  Any help
> from one of the developers would be greatly appreciated.

As you probably have figured out yourself, it is the SECCHK command
which fails when you use the TXNMGR. I did notice that you are using a
a different versions for the server andt the client. (10.3.2.1 rev
599110) for the server vs (10.3.1.4 rev
561794) for the client. That should not be a problem, but could be an
easy thing to test...

-- 
dt

Re: Derby and offset

Posted by Thomas Nielsen <Th...@Sun.COM>.
Hi Pertti,

Pertti Ylijukuri wrote:
> I have understood that in derby doesn't support offset in sql.
Correct. Derby does not support offset.
To my knowledge offset is actually not part of neither the SQL99 nor 
SQL2003 specs. Derby (attempts to) strictly adhere to these specs, and 
this is one of the main reasons why it's not supported.
> However in hibernate with derby this is possible using 
> Query.setFirstRow(int row). What it actually to do that?
Derby implements scrollable result sets, which is a requirement for 
Hibernate to provide Query.setFirstRow().
Please refer to any online JDBC tutorial, and/or the derby docs, on how 
to use scrollable resultsets.

Also, the upcoming derby 10.4 release includes the ROW_NUMBER() window 
function which is SQLs standardized way of limiting the number of rows 
returned and it's offset. Please see the following wikipage for a quick 
introduction in case you are unfamiliar with ROW_NUMBER():
  http://wiki.apache.org/db-derby/OLAPRowNumber

Hope this helps :)

Cheers,
Thomas

Derby and offset

Posted by Pertti Ylijukuri <pe...@gmail.com>.
I have understood that in derby doesn't support offset in sql.
However in hibernate with derby this is possible using  
Query.setFirstRow(int row). What it actually to do that?

Re: Derby and Bitronix JTA connection failure

Posted by Kristian Waagan <Kr...@Sun.COM>.
Brett Wooldridge wrote:
> Hello list,
> 
> I am stuck trying to figure out an issue with using the Bitronix JTA 
> Transaction Manager with Derby (v10.3.2.1).  Here’s the details:
> 
> I’m running Derby as a network server on the standard port.  I have a 
> unit test which uses the ClientXADatasource to connect to my Derby 
> database, and this succeeds.
> 
> The client code looks like this:
> 
>         ClientXADataSource ds = new ClientXADataSource(); 
>        ds.setDatabaseName("ziptie");        ds.setPortNumber(1527); 
>        ds.setServerName("localhost");        Connection connection = 
> ds.getConnection();
> As I said, this client connects successfully, and the derby.log records 
> this:
> 
> Connection number: 2.
> 2008-03-16 11:45:05.302 GMT Thread[DRDAConnThread_6,5,derby.daemons] 
> (DATABASE = ziptie), (DRDAID = {2}), Apache Derby Network Server 
> connected to database ziptie
> 
> Then, I try to start an application which uses the Bitronix TM to 
> perform a similar connection to Derby.  However, this connection fails 
> as recorded by the derby.log as well:
> 
> Connection number: 3.
> 2008-03-16 11:45:10.817 GMT Thread[DRDAConnThread_6,5,derby.daemons] 
> (DATABASE = ziptie), (DRDAID = {3}), Database not available
> 
> The properties for Bitronix look like this:
> 
> resource.ds.className=org.apache.derby.jdbc.ClientXADataSource 
> resource.ds.uniqueName=ziptie
> resource.ds.driverProperties.databaseName=ziptie 
> resource.ds.driverProperties.serverName=localhost 
> resource.ds.driverProperties.portNumber=1527

Hello Brett,

I have never used BTM, but I found it on Codehaus and downloaded the 
newUserDemo (look under "New user's guide"). The demo already uses 
Derby, but obtains the connections through an embedded XA data source.

I modified the demo to use ClientXADataSource, and it worked fine.
Could you have a look, maybe try to replace the settings with your 
specific settings, and see if it works for you?
(to use it against your own server, you only need to create one simple 
table in your database).
If the demo works, but your application doesn't, are you able to modify 
the demo to reproduce the problem?
What distinguishes your setup from the simple setup in the demo?

I tried with Derby 10.2.2.0, 10.3.2.1 and 10.5 alpha (development version).

Sorry I can't provide more help, but maybe the demo thing can give us a 
lead to follow?


FYI: I did not check under which license the demo is distributed.


regards,
-- 
Kristian

> 

[ snip - DRDA traces ]