Hi Markus,
Markus KARG wrote:
> 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!
Well there is one bug already as it should throw a SQLException not
OperationNotSupportedException
You lost me on the :
The driver needs an explicit "CONCUR_*"
Can you please explain this a little further, what is the statement you
were expecting to work?
>
> 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
>>
>