Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v7.1
  HXTT Cobol v5.0
  HXTT DBF v7.1
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Excel v6.1
  HXTT Paradox v7.1
  HXTT PDF v2.0
  HXTT Text(CSV) v7.1
  HXTT Word v1.1
  HXTT XML v4.0
Offshore Outsourcing
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)

HXTT DBF
statement.setMaxRows with subselect
Rob Gansevles
2011-03-08 07:07:18.0
Hi,

I am getting wrong results when using statement.setMaxRows().
When the sql contains a subselect, the maxrows limit seems to be applied to that as well, whilst it should only be applied to the outer select.

See sample java program below, the select query should return 2 rows (as it does when you comment out the setMaxRows() call).

Thanks in advance for looking at this,

Rob

///// start of sample java program /////
public class MaxRowsTest
{
public static void main(String[] args) throws Exception
{
String url = "jdbc:dbf:////tmp/foxpro";
String driver = "com.hxtt.sql.dbf.DBFDriver";

Class.forName(driver);

java.sql.Connection connection = java.sql.DriverManager.getConnection(url, "", "");

try
{
exec(connection, "drop table tst1");
}
catch (Exception e)
{
}
try
{
exec(connection, "drop table tst2");
}
catch (Exception e)
{
}

exec(connection, "create table tst1 (x int)");

exec(connection, "insert into tst1 values (1)");
exec(connection, "insert into tst1 values (2)");
exec(connection, "insert into tst1 values (3)");

exec(connection, "create table tst2 (x int)");

exec(connection, "insert into tst2 values (1)");
exec(connection, "insert into tst2 values (1)");
exec(connection, "insert into tst2 values (1)");
exec(connection, "insert into tst2 values (1)");
exec(connection, "insert into tst2 values (2)");

java.sql.PreparedStatement ps = connection.prepareStatement("select x from tst1 where x in (select x from tst2)");
// limiting the result to 3 seems also to limit the result in the subselect, when next line is commented out the test succeeds
ps.setMaxRows(3);
java.sql.ResultSet rs = ps.executeQuery();
int n = 0;
while (rs.next())
{
System.out.println(rs.getObject(1));
n++;
}
rs.close();
ps.close();

exec(connection, "drop table tst1");
exec(connection, "drop table tst2");

connection.close();

if (n == 2)
{
System.out.println("OK");
}
else
{
System.err.println("Expecting 2 results");
}
}

public static void exec(java.sql.Connection connection, String sql) throws Exception
{
java.sql.PreparedStatement ps = connection.prepareStatement(sql);
ps.execute();
ps.close();
}
}
Re:statement.setMaxRows with subselect
HXTT Support
2011-03-09 04:40:27.0
Thanks. Fixed. Please download the latest package.
Re:Re:statement.setMaxRows with subselect
Rob Gansevles
2011-03-09 08:12:23.0
Yes it works.

Thanks!

Rob

Search Key   Search by Last 50 Questions




Google
 

Email: webmaster@hxtt.com
Copyright © 2003-2019 Heng Xing Tian Tai Lab of Xi'an City. | All Rights Reserved. | Privacy | Legal | Sitemap