You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Ян Программист <we...@gmail.com> on 2010/04/14 12:16:34 UTC
JDBC Metadata cursor
Following code gives an exception; a can't figure out how to get scrollable
cursor for metadata:
import static org.junit.Assert.*;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.apache.derby.client.am.Cursor;
import org.apache.derby.client.am.ResultSet;
import org.apache.derby.iapi.sql.Statement;
import org.apache.derby.iapi.sql.execute.CursorResultSet;
import org.apache.derby.impl.jdbc.EmbedResultSet40;
import org.apache.derby.impl.jdbc.EmbedStatement40;
import org.apache.derbyTesting.functionTests.tests.lang.CursorTest;
import org.apache.derbyTesting.junit.Derby;
import org.hamcrest.core.Is;
import org.junit.After;
import org.junit.AfterClass;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
public class DatabaseMetaDataTest {
private static java.sql.Connection derby_conn;
/**
* @throws java.lang.Exception
*/
private static java.sql.DatabaseMetaData metadata;
@BeforeClass
public static void setUpBeforeClass() throws Exception {
derby_conn =
DriverManager.getConnection("jdbc:derby:repository;create=true");
}
/**
* @throws java.lang.Exception
*/
@AfterClass
public static void tearDownAfterClass() throws Exception {
derby_conn.close();
}
/**
* @throws java.lang.Exception
*/
@Before
public void setUp() throws Exception {
Statement stmt = (EmbedStatement40)
derby_conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
metadata = derby_conn.getMetaData();
}
/**
* @throws java.lang.Exception
*/
@After
public void tearDown() throws Exception {
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getSchemaTerm()}.
* @throws SQLException
*/
@Test
public final void testGetSchemaTerm() throws SQLException {
String schemaTerm = metadata.getSchemaTerm();
assertNotNull("...", schemaTerm);
assertTrue(schemaTerm.length() > 0);
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#isCatalogAtStart()}.
* @throws SQLException
*/
@Test
public final void testIsCatalogAtStart() throws SQLException {
boolean isAtStart = metadata.isCatalogAtStart();
assertNotNull(isAtStart);
assertTrue(isAtStart);
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getMaxColumnNameLength()}.
* @throws SQLException
*/
@Test
public final void testGetMaxColumnNameLength() throws SQLException {
int length = metadata.getMaxColumnNameLength();
assertNotNull(length);
assertTrue("", length > 0);
assertTrue("", length > 10);
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getMaxColumnsInGroupBy()}.
*/
@Test
public final void testGetMaxColumnsInGroupBy() {
fail("Not yet implemented"); // TODO
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getMaxColumnsInIndex()}.
*/
@Test
public final void testGetMaxColumnsInIndex() {
fail("Not yet implemented"); // TODO
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getMaxColumnsInSelect()}.
*/
@Test
public final void testGetMaxColumnsInSelect() {
fail("Not yet implemented"); // TODO
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getMaxColumnsInTable()}.
*/
@Test
public final void testGetMaxColumnsInTable() {
fail("Not yet implemented"); // TODO
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getSchemas()}.
*/
@Test
public final void testGetSchemas() {
fail("Not yet implemented"); // TODO
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getCatalogs()}.
*/
@Test
public final void testGetCatalogs() {
fail("Not yet implemented"); // TODO
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getColumns(java.lang.String,
java.lang.String, java.lang.String, java.lang.String)}.
* @throws SQLException
*/
@Test
public final void testGetColumns() throws SQLException {
java.sql.ResultSet column_set = metadata.getColumns("APP", null,
"SERVER_SHUTDOWN_TYPE", null);
column_set.relative(2);
String column_name = column_set.getString("COLUMN_NAME");
assertNotNull(column_name);
assertTrue(column_name.length()>0);
//For scrollable result set:
//column_set.absolute(1);
//assertTrue(column_set.first());
//column_set.beforeFirst();
//assertTrue(column_set.next());
//assertTrue(column_set.isFirst());
//For updatable result set:
//column_set.moveToCurrentRow();
//assertTrue(column_set.next());
}
private static int getRowCount(java.sql.ResultSet columnSet) throws
SQLException
{
int rowCount;
int currentRow = columnSet.getRow(); // Get current row
rowCount = columnSet.last() ? columnSet.getRow() : 0; // Determine number
of rows
if (currentRow == 0) // If there was no current row
columnSet.beforeFirst(); // We want next() to go
to first row
else // If there WAS a current row
columnSet.absolute(currentRow); // Restore it
return rowCount;
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getBestRowIdentifier(java.lang.String,
java.lang.String, java.lang.String, int, boolean)}.
*/
@Test
public final void testGetBestRowIdentifier() {
fail("Not yet implemented"); // TODO
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getVersionColumns(java.lang.String,
java.lang.String, java.lang.String)}.
*/
@Test
public final void testGetVersionColumns() {
fail("Not yet implemented"); // TODO
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getPrimaryKeys(java.lang.String,
java.lang.String, java.lang.String)}.
* @throws SQLException
*/
@Test
public final void testGetPrimaryKeys() throws SQLException {
java.sql.ResultSet pk_set = metadata.getPrimaryKeys("APP", null,
"SERVER_SHUTDOWN_TYPE");
assertTrue(getRowCount(pk_set) > 0);
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getImportedKeys(java.lang.String,
java.lang.String, java.lang.String)}.
*/
@Test
public final void testGetImportedKeys() {
fail("Not yet implemented"); // TODO
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getExportedKeys(java.lang.String,
java.lang.String, java.lang.String)}.
*/
@Test
public final void testGetExportedKeys() {
fail("Not yet implemented"); // TODO
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getTypeInfo()}.
*/
@Test
public final void testGetTypeInfo() {
fail("Not yet implemented"); // TODO
}
}
And .next() throws an exception about undefined cursor state:
import static org.junit.Assert.*;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.apache.derby.client.am.Cursor;
import org.apache.derby.client.am.ResultSet;
import org.apache.derby.iapi.sql.Statement;
import org.apache.derby.iapi.sql.execute.CursorResultSet;
import org.apache.derby.impl.jdbc.EmbedResultSet40;
import org.apache.derby.impl.jdbc.EmbedStatement40;
import org.apache.derby.jdbc.EmbeddedDriver;
import org.apache.derbyTesting.functionTests.tests.lang.CursorTest;
import org.apache.derbyTesting.junit.Derby;
import org.hamcrest.core.Is;
import org.junit.After;
import org.junit.AfterClass;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
public class DatabaseMetaDataTest {
private static EmbeddedDriver driver;
private static java.sql.Connection derby_conn;
/**
* @throws java.lang.Exception
*/
private static java.sql.DatabaseMetaData metadata;
@BeforeClass
public static void setUpBeforeClass() throws Exception {
driver = new org.apache.derby.jdbc.EmbeddedDriver();
derby_conn = driver.connect("jdbc:derby:repository;create=true", null);
}
/**
* @throws java.lang.Exception
*/
@AfterClass
public static void tearDownAfterClass() throws Exception {
derby_conn.close();
}
/**
* @throws java.lang.Exception
*/
@Before
public void setUp() throws Exception {
derby_conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
metadata = derby_conn.getMetaData();
}
/**
* @throws java.lang.Exception
*/
@After
public void tearDown() throws Exception {
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getSchemaTerm()}.
* @throws SQLException
*/
@Test
public final void testGetSchemaTerm() throws SQLException {
String schemaTerm = metadata.getSchemaTerm();
assertNotNull("...", schemaTerm);
assertTrue(schemaTerm.length() > 0);
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#isCatalogAtStart()}.
* @throws SQLException
*/
@Test
public final void testIsCatalogAtStart() throws SQLException {
boolean isAtStart = metadata.isCatalogAtStart();
assertNotNull(isAtStart);
assertTrue(isAtStart);
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getMaxColumnNameLength()}.
* @throws SQLException
*/
@Test
public final void testGetMaxColumnNameLength() throws SQLException {
int length = metadata.getMaxColumnNameLength();
assertNotNull(length);
assertTrue("", length > 0);
assertTrue("", length > 10);
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getMaxColumnsInGroupBy()}.
*/
@Test
public final void testGetMaxColumnsInGroupBy() {
fail("Not yet implemented"); // TODO
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getMaxColumnsInIndex()}.
*/
@Test
public final void testGetMaxColumnsInIndex() {
fail("Not yet implemented"); // TODO
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getMaxColumnsInSelect()}.
*/
@Test
public final void testGetMaxColumnsInSelect() {
fail("Not yet implemented"); // TODO
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getMaxColumnsInTable()}.
*/
@Test
public final void testGetMaxColumnsInTable() {
fail("Not yet implemented"); // TODO
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getSchemas()}.
*/
@Test
public final void testGetSchemas() {
fail("Not yet implemented"); // TODO
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getCatalogs()}.
*/
@Test
public final void testGetCatalogs() {
fail("Not yet implemented"); // TODO
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getColumns(java.lang.String,
java.lang.String, java.lang.String, java.lang.String)}.
* @throws SQLException
*/
@Test
public final void testGetColumns() throws SQLException {
java.sql.ResultSet column_set = metadata.getColumns("APP", null,
"SERVER_SHUTDOWN_TYPE", null);
column_set.relative(2);
String column_name = column_set.getString("COLUMN_NAME");
assertNotNull(column_name);
assertTrue(column_name.length()>0);
//For scrollable result set:
//column_set.absolute(1);
//assertTrue(column_set.first());
//column_set.beforeFirst();
//assertTrue(column_set.next());
//assertTrue(column_set.isFirst());
//For updatable result set:
//column_set.moveToCurrentRow();
//assertTrue(column_set.next());
}
private static int getRowCount(java.sql.ResultSet columnSet) throws
SQLException
{
int rowCount;
int currentRow = columnSet.getRow(); // Get current row
rowCount = columnSet.last() ? columnSet.getRow() : 0; // Determine number
of rows
if (currentRow == 0) // If there was no current row
columnSet.beforeFirst(); // We want next() to go
to first row
else // If there WAS a current row
columnSet.absolute(currentRow); // Restore it
return rowCount;
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getBestRowIdentifier(java.lang.String,
java.lang.String, java.lang.String, int, boolean)}.
*/
@Test
public final void testGetBestRowIdentifier() {
fail("Not yet implemented"); // TODO
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getVersionColumns(java.lang.String,
java.lang.String, java.lang.String)}.
*/
@Test
public final void testGetVersionColumns() {
fail("Not yet implemented"); // TODO
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getPrimaryKeys(java.lang.String,
java.lang.String, java.lang.String)}.
* @throws SQLException
*/
@Test
public final void testGetPrimaryKeys() throws SQLException {
java.sql.ResultSet pk_set = metadata.getPrimaryKeys("APP", null,
"SERVER_SHUTDOWN_TYPE");
assertTrue(getRowCount(pk_set) > 0);
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getImportedKeys(java.lang.String,
java.lang.String, java.lang.String)}.
*/
@Test
public final void testGetImportedKeys() {
fail("Not yet implemented"); // TODO
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getExportedKeys(java.lang.String,
java.lang.String, java.lang.String)}.
*/
@Test
public final void testGetExportedKeys() {
fail("Not yet implemented"); // TODO
}
/**
* Test method for {@link
org.apache.derby.client.am.DatabaseMetaData#getTypeInfo()}.
*/
@Test
public final void testGetTypeInfo() {
fail("Not yet implemented"); // TODO
}
}
In the mean time this code from Derby tests works fine:
DatabaseMetaDataTest.java:
public void testGetColumnsReadOnly() throws Exception
{
ResultSet[] rs = getColumns(null, null, null, null);
for ( int j =0 ; j<2 ; j++) {
checkColumnsShape(rs[j], j);
crossCheckGetColumnsAndResultSetMetaData(rs[j], false, j);
}
}
Please help anyone. John
Re: JDBC Metadata cursor
Posted by Ян Программист <we...@gmail.com>.
Here is the output of your example class "z":
Next column is called TABLEID
Next column is called TABLENAME
Next column is called TABLETYPE
Next column is called SCHEMAID
Next column is called LOCKGRANULARITY
Interesting, but my code started to work:
@Test
public final void testGetColumns() throws SQLException {
java.sql.ResultSet column_set = metadata.getColumns(null, "SYS",
"SYSTABLES", "%");
//column_set.relative(2);
//For scrollable result set:
//column_set.absolute(1);
//assertTrue(column_set.first());
//column_set.beforeFirst();
//assertTrue(column_set.next());
//assertTrue(column_set.isFirst());
//For updatable result set:
//column_set.moveToCurrentRow();
//assertTrue(column_set.next());
//int i=0;
//while ( column_set.next() )
//{
assertTrue(column_set.next());
String column_name = column_set.getString(3);
assertNotNull(column_name);
assertTrue(column_name.length()>0);
System.out.println(column_name);
//}
//assertNotNull(column_name);
//assertTrue(column_name.length()>0);
}
JUnit test reporter indicates this test passed("green" state).
System.out.prinln() pushed following into output stream:
SYSTABLES
The same is for while() loop.
Thanks help. The courious thing is that I remember exactly that in one of my
previous tryouts there where only next(), but an exception about undefined
cursor state fired. Hm,..
By the way - metadata connection was properly defined in setUp().
P.S. In normal unit tests it is a bad idea to write into System.out
stream. Normally it should be written into logger (Log4j). Otherwise, in
some cases, benchmarks would not be usable; if data writing operation would
take much more time than assertions. John
Re: JDBC Metadata cursor
Posted by Rick Hillegas <ri...@oracle.com>.
Ян Программист wrote:
> So, how would I get data from result set? .next() not working! John
Hi John,
As Knut said, you should remove the fancy positioning calls and just use
next() to advance your metadata result sets. Also, be aware that the
first argument to many metadata creators is an SQL catalog name. Derby
does not support the extra catalog namespace, so the catalog argument
should always be null. Here is an example of how to use
DatabaseMetaData.getColumns():
import java.sql.*;
public class z
{
public static void main( String[] args ) throws Exception
{
Class.forName( "org.apache.derby.jdbc.EmbeddedDriver" );
Connection conn = DriverManager.getConnection(
"jdbc:derby:memory:dummy;create=true" );
DatabaseMetaData metadata = conn.getMetaData();
ResultSet cursor = metadata.getColumns( null, "SYS", "SYSTABLES", "%" );
while ( cursor.next() )
{
System.out.println( "Next column is called " + cursor.getString( 4 ) );
}
cursor.close();
conn.close();
}
}
Hope this helps,
-Rick
Re: JDBC Metadata cursor
Posted by Ян Программист <we...@gmail.com>.
So, how would I get data from result set? .next() not working! John
Re: JDBC Metadata cursor
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
On 04/14/10 12:16 PM, Ян Программист wrote:
> Following code gives an exception; a can't figure out how to get
> scrollable cursor for metadata:
Hi John,
I'm afraid that's not possible. The JDBC 4.0 specification says (in
section 7.5, SQL Objects and Their Attributes):
"The ResultSet objects that are returned from a DatabaseMetaData method have
a sensitivity of TYPE_FORWARD_ONLY and a concurrency of CONCUR_READ_ONLY."
--
Knut Anders