invalid DDS packet from socket

[pwierzgala]

Hi,

I was trying to test the connection

import drda

conn = drda.connect(host='localhost', port=1527, database='jdbc:derby:<path to the database directory>')
cur = conn.cursor()
cur.execute('select * from <table>;')

but I got the following error:

ConnectionError: invalid DDS packet from socket

The Derby server outputted the following error:

Mon Oct 31 16:25:55 CET 2022 : Execution failed because of Permanent Agent Error: SVRCOD = 40; RDBNAM = jdbc:derby:/home/p; diagnostic msg = Cannot invoke "org.apache.derby.iapi.jdbc.EngineStatement.clearWarnings()" because the return value of "org.apache.derby.impl.drda.DRDAStatement.getStatement()" is null
org.apache.derby.impl.drda.DRDAProtocolException: Execution failed because of Permanent Agent Error: SVRCOD = 40; RDBNAM = jdbc:derby:/home/p; diagnostic msg = Cannot invoke "org.apache.derby.iapi.jdbc.EngineStatement.clearWarnings()" because the return value of "org.apache.derby.impl.drda.DRDAStatement.getStatement()" is null
    at org.apache.derby.impl.drda.DRDAProtocolException.newAgentError(DRDAProtocolException.java:340)
    at org.apache.derby.impl.drda.DRDAConnThread.sendUnexpectedException(DRDAConnThread.java:8805)
    at org.apache.derby.impl.drda.DRDAConnThread.handleException(DRDAConnThread.java:8762)
    at org.apache.derby.impl.drda.DRDAConnThread.run(DRDAConnThread.java:325)
Mon Oct 31 16:25:55 CET 2022 : Cannot invoke "org.apache.derby.iapi.jdbc.EngineStatement.clearWarnings()" because the return value of "org.apache.derby.impl.drda.DRDAStatement.getStatement()" is null
java.lang.NullPointerException: Cannot invoke "org.apache.derby.iapi.jdbc.EngineStatement.clearWarnings()" because the return value of "org.apache.derby.impl.drda.DRDAStatement.getStatement()" is null
    at org.apache.derby.impl.drda.DRDAConnThread.parseEXCSQLSETobjects(DRDAConnThread.java:5604)
    at org.apache.derby.impl.drda.DRDAConnThread.parseEXCSQLSET(DRDAConnThread.java:5466)
    at org.apache.derby.impl.drda.DRDAConnThread.processCommands(DRDAConnThread.java:801)
    at org.apache.derby.impl.drda.DRDAConnThread.run(DRDAConnThread.java:300

I've managed to successfully connect with the database and execute the same query through derby console (ij). Do you have any suggestions what I might did wrong?

Versions: python: 3.9.13 pydrda: 0.4.5 db-derby: 10.15.2.0 and 10.16.1.1 (tested both) java: openjdk version "17.0.4" 2022-07-19 OpenJDK Runtime Environment (build 17.0.4+8-Ubuntu-122.04) OpenJDK 64-Bit Server VM (build 17.0.4+8-Ubuntu-122.04, mixed mode, sharing)

[nakagami]

Specify a database name for database parameter

The database name is the database name specified when derby is started as a network server.

https://db.apache.org/derby/papers/DerbyTut/ns_intro.html#start_ns

The prefix jdbc:derby in the database parameter is not necessary. I have never specified the database with the full path, so I do not know if the database can be specified with the full path.

[nakagami]

TODO:

• Check if the database can be specified with the full path
• Consider whether a clear error message can be returned

[nakagami]

The full path to the database could not be specified. Need to specify database name without path separator slash

Network server error messages could not be remedied in any way from the client side.

[nakagami]

Here is the test code. https://github.com/nakagami/pydrda/blob/master/test_derby.py

The database 'testdb' is created and connected.

[pwierzgala]

When I wrote my previous comment I had no problems connecting the database using the full path. The mentioned error was caused by the execute method. The problem still exists for me. Below I enclose more detailed description and analysis of the problem.

Problem

I have an existing database I'd like to connect to and interact with.

Setup

According to: https://db.apache.org/derby/papers/DerbyTut/ns_intro.html#start_ns I had to start the Network Server from the directory with my database. That way I could specify just its name as you've suggested.

"If you use the Derby Network Client JDBC driver, by default the database is created in the directory where the Network Server was started up;"

1. Execute export DERBY_HOME=<path to derby distribution>
2. Change directory to the directory with the data base.
3. Execute java -jar $DERBY_HOME/lib/derbyrun.jar server start

My code

import drda

HOST = 'localhost'
DATABASE = 'testdb'
PORT = 1527

connection = drda.connect(
    host=HOST,
    database=DATABASE,
    port=PORT,
)
cur = connection.cursor()
cur.execute("SELECT * FROM tbl_units;")
for r in cur.fetchall():
    print(r[0], r[1])

Results

Executing a query with the python code

When I specify a database name as DATABASE = 'jdbc:derby:testdb' I get the same error as in my previous comment:

  File "<path>/pydrda_test/main.py", line 16, in run_derby_test
    cur.execute("SELECT * FROM tbl_units;")
  File "<path>/python3.9/site-packages/drda/cursor.py", line 61, in execute
    self._rows, self.description = self.connection._query(self.query, args)
  File "<path>/python3.9/site-packages/drda/connection.py", line 391, in _query
    rows, description, params_description = self._parse_response()
  File "<path>/python3.9/site-packages/drda/connection.py", line 50, in _parse_response
    dds_type, chained, number, code_point, obj = ddm.read_dds(self.sock)
  File "<path>/python3.9/site-packages/drda/ddm.py", line 236, in read_dds
    raise ConnectionError("invalid DDS packet from socket")
ConnectionError: invalid DDS packet from socket

When I specify the database name as DATABASE = 'testdb' the error changes to:

  File "<path>/pydrda_test/main.py", line 16, in run_derby_test
    cur.execute("SELECT * FROM tbl_units;")
  File "<path>/python3.9/site-packages/drda/cursor.py", line 61, in execute
    self._rows, self.description = self.connection._query(self.query, args)
  File "<path>/python3.9/site-packages/drda/connection.py", line 391, in _query
    rows, description, params_description = self._parse_response()
  File "<path>/python3.9/site-packages/drda/connection.py", line 52, in _parse_response
    err_msg = ddm.parse_reply(obj).get(cp.SRVDGN).decode('utf-8')
AttributeError: 'NoneType' object has no attribute 'decode'

Please note that both errors are in line cur.execute("SELECT * FROM tbl_units;")

Executing a query with the derby console

I assumed that maybe there's some incompatibility between the database I use, Derby version or Java version. If there's something wrong with any of that I shouldn't be able to execute the query from the Derby console. Here is how I tested it:

1. Open console: java -jar $DERBY_HOME/lib/derbyrun.jar ij
2. Connect with the database connect 'jdbc:derby:testdb';. Here I had to use the jdbc:derby prefix because without that I got the error: ERROR 08001: No suitable driver found for testdb.
3. Execute query: SELECT NAME FROM tbl_units fetch first 5 rows only;

I've received the expected result:

NAME                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
LVL 2000                                                                                                                        
m2*d                                                                                                                            
kcal                                                                                                                            
GJ                                                                                                                              
lb av

Summary

Both python code and console commands ran against the same derby server. The console commands worked but the python code didn't. The python code was as simple as possible and it reflected console commands. To me it looks like some error in the pydrda package.

[nakagami]

I'm not sure how to handle errors properly. Sorry.

The semicolon at the end of the SQL statement is not included.

It's not like this.

cur.execute("SELECT * FROM tbl_units;")

Please do it this way.

cur.execute("SELECT * FROM tbl_units")

[nakagami]

Not perfect, but fixed and released version 0.4.7

[pwierzgala]

Thanks, it works without semicolon. That was a silly mistake.