Tom,
thank you for the JDBC sample. I tried out the following, quite similar
program:
Class.forName(driverJAR);
final Connection connection = DriverManager.getConnection(
connectionURL, userID, password);
try {
JDBCTest.execute(connection, "CREATE TABLE mytable (id
INTEGER NOT NULL PRIMARY KEY)");
try {
for (int i = 1; i < 16; i++)
JDBCTest.execute(connection, "INSERT INTO mytable
(id) VALUES (" + i + ")");
final PreparedStatement stmt =
connection.prepareStatement("SELECT * FROM mytable",
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
stmt.setMaxRows(12);
stmt.execute();
final ResultSet rs = stmt.getResultSet();
for (int i = 2; i < 15; i++) {
rs.absolute(i);
System.out.println(rs.getInt(1));
}
} finally {
JDBCTest.execute(connection, "DROP TABLE mytable");
}
} finally {
connection.close();
}
As you can see, it is exactly what you wanted me to do: It creates the
table, adds 15 lines, sets MaxRows to 12, executes the query, does
absolute(<first result>, here: 2). It even uses the same numbers than
the unit test (15 rows, first result = 2, max rows = 12). And here is
the interesting result: It works well (as I expected):
2
3
4
5
6
7
8
9
10
11
12
Exception in thread "main" java.sql.SQLException: [Microsoft][ODBC
Driver Manager] Ungültiger Cursorstatus
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLGetDataInteger(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcResultSet.getDataInteger(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcResultSet.getInt(Unknown Source)
at JDBCTest.main(JDBCTest.java:43)
Obviously there is no bug in the driver: It is correct to throw the
exception at "absolute(13)" because I said "setMaxResult(12)", so (13)
is out of range (since the first line is absolute(1) but not absolute(0)
as programmers typically would expect). Also the start is correct. I
said "absolute(2)" and it prints id=2. It prints 11 rows because I said
setMaxResult(2) (retrieves 12 rows) but then scrolled to the second of
the twelve rows before printing.
There is only one thing that I remarked that is strange: The driver
needs an explicit "CONCUR_*" (why ever, seems to be a limitation of the
driver -- it works with both, READ_ONLY and UPDATABLE, but it throws an
OperationNotSupported when calling prepare() with no explicit CONCUR_*
parameter!
So in fact we are as far as one hour ago. :-(
Do you have some other ideas what you like me to check?
In fact I cannot see anything wrong in the driver's behaviour.
Thanks
Markus
Tom Ware schrieb:
> Hi Markus,
>
> I don't have the exact JDBC handy, but here's an approximation based
> on a JDBC example I have on my machine
>
> - get a connection eg:
> // Load the database driver
> Class.forName( "oracle.jdbc.driver.OracleDriver" ) ;
>
> // Get a connection to the database
> // Connection conn =
> DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl",
> "scott", "tiger") ;
>
> - create a Prepared Statement
> PreparedStatement stmt = conn.prepareStatement("select * from
> mytable", java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE);
>
> - set max rows
> stmt.setMaxRows(<maxrows>);
>
> - execute
> boolean result = stmt.execute("Select SQL_DATE from CMP3_DATE_TIME");
>
> - get the result set
>
> ResultSet rs = stmt.getResultSet();
>
> - cal absolute
>
> rs.absolute(<firstresult>)
>
> Then take a look at the results.
>
> -Tom
>
>
> Markus KARG wrote:
>
>> Michael,
>>
>> I checked it again -- sorry I did a typo: It is 15 results in common,
>> then applying setFirstResult(2) in conjunction with setMaxResults(12)
>> but not (14) as I write before -- but I am getting still 13 results,
>> which is one more than twelve. So actually it is NOT a bug in the
>> unit test, but a bug somewhere in TopLink or in the JDBC driver.
>>
>> Tom, can you please write five short code lines what you want me to
>> check with the JDBC driver?
>>
>> Thanks
>> Markus
>>
>> Markus KARG schrieb:
>>
>>
>>> Michael,
>>>
>>> you are definitively right! Following the official description of
>>> setMaxResults in the EJB 3.0 JPA specification, the SQLAnywhere
>>> driver is working absolutely correct, while the expected result in
>>> the test is false!
>>>
>>> So I will file a bug report for that.
>>>
>>> Thanks a lot!
>>> Markus
>>>
>>> Michael Bouschen schrieb:
>>>
>>>> Hi Markus, hi Tom,
>>>>
>>>> please correct me if I'm wrong, but I think returning 13 instances
>>>> for the third test is the correct query result. The description of
>>>> method setMaxResult in the spec says: "Set the maximum number of
>>>> results to retrieve.". I read this that setMaxResult specifies the
>>>> size of the query result and not the index of the last result
>>>> element. The third test case calls query.setFirstResult(2) and
>>>> query.setMaxResults(14). So it is specified to return at most 14
>>>> instances. But there are only 13 rows left when firstResult is
>>>> specified as 2 (the numbering starts at 0).
>>>>
>>>> What do you think?
>>>>
>>>> Regards Michael
>>>>
>>>>
>>>>> Hi Markus,
>>>>>
>>>>> There has actually been some recent discussion on the mailing list
>>>>> about setFirstResult() and setMaxResult(). Here's how they work:
>>>>>
>>>>> The current implementation of setFirstResult() and setMaxResult()
>>>>> make
>>>>> use of JDBC constructs rather than RDBMS constructs.
>>>>>
>>>>> For setMaxResult() we call the java.sql.Statement.setMaxRows(int)
>>>>> method.
>>>>>
>>>>> For setFirstResult() we make use of the ResultSet that is returned in
>>>>> JDBC. If firstResult is set, we build query that returns a
>>>>> TYPE_SCROLL_INSENSITIVE ResultSet and call resultSet.absolute(int) on
>>>>> that result set.
>>>>>
>>>>> Is it possible the result for the SQLAnywhere driver is not what
>>>>> we expect?
>>>>>
>>>>> -Tom
>>>>>
>>>>>
>>>>> Markus KARG wrote:
>>>>>
>>>>>
>>>>>> When I was implementing the SQLAnywhere platform I remarked a
>>>>>> failing test: complexResultPropertiesTest. That test tries three
>>>>>> things: setFirstResult, setMaxResult and a combination of both.
>>>>>> The used table holds 15 row. The first test (setFirstResult(2))
>>>>>> return 13 rows (what is correct, since 15-2=13). The second test
>>>>>> (setMaxResults(15-1)) returns 14 rows (what is correct, since
>>>>>> 15-1=14). But the third test fails. The correct result would be
>>>>>> to return 12 rows (= 15-2-1). But the actual result is 13 rows!
>>>>>>
>>>>>> I have not seen any faulty SQL (in fact, it seems that TopLink
>>>>>> does this internally -- it does not apply any "SELECT TOP START
>>>>>> AT" range constraints to the SQL, but seems to process the range
>>>>>> constraints internally.
>>>>>>
>>>>>> So did I detect a bug in TopLink, or where is there a fault in my
>>>>>> platform implementation?
>>>>>>
>>>>>> Thanks
>>>>>> Markus
>>>>>>
>>>>>>
>>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: dev-unsubscribe_at_glassfish.dev.java.net
>>>>> For additional commands, e-mail: dev-help_at_glassfish.dev.java.net
>>>>>
>>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: dev-unsubscribe_at_glassfish.dev.java.net
>>>> For additional commands, e-mail: dev-help_at_glassfish.dev.java.net
>>>>
>>>>
>>
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe_at_glassfish.dev.java.net
> For additional commands, e-mail: dev-help_at_glassfish.dev.java.net
>