JDBC Driver: DatabaseMetaData.getColumn API call from JDBC client brings down server, pegs CPU at 100%

[mwalker]

[Please let me know if there's a better forum to post in, thanks.]

We're trying to develop a simple JDBC application that accesses FM 8.0 v4 Advanced server. We are using the supplied JDBC driver for FM. FYI, this driver uses DataDirect's Sequelink driver underneath the covers.

We find that FMP consistently takes WAY too long to respond to the JDBC API call DatabaseMetaData.getColumns().

This call is used to lookup column information (e.g. name and datatype) for a given table. It is listed as "supported" by the driver, according to the documentation.

Against a production server, the CPU usage moves to 100% whenever this query is issued for a particular table, which causes problems for other users. After about 15 minutes (or more), the query completes, and the column information is returned. The tables have 20-100 columns in them.

Has anyone else experienced this problem? Has it been fixed in a patch or a later release? Is there a workaround?

Any help would be greatly appreciated. Please let me know if more information would be useful, thanks!

[mwalker]

I should mention that I can replicate this problem with the FM-supplied "testforjdbc.bat" Sequelink testing program. Here is example code taken from that application:

		Connection con = null;

		DatabaseMetaData dmd = null;

		try{

			Class.forName("com.ddtek.jdbc.sequelink.SequeLinkDriver");

			Class.forName("com.ddtek.jdbcspy.SpyDriver");

		    con = DriverManager.getConnection("jdbc:spy:{jdbc:sequelink://;serverDataSource=};log=System.out;logTName=yes;timestamp=yes","username","password");

			System.out.println("Getting metadata");

			dmd = con.getMetaData();

			System.out.println("Got metadata");

		}catch(Exception e){

		    System.out.println(e);

		}



		ResultSet results = null;

		try {

			System.out.println("Getting columns for Lot");

		    results = dmd.getColumns(null, null, "Lot", "%");

			System.out.println("Got columns");

		} catch (SQLException e){

		    System.out.println(e);

		}

I find that the program hangs at the "dmd.getColumns" call for 20 minutes or more. This table has about 110 columns. Other databases typically return the answer to this query in a few miilliseconds. What is going on here?

Edited March 31, 2008 by Guest

[Jesse Barnum]

Try our Open Source FileMaker JDBC driver at https://woof.dev.java.net to see if your results are any better. We also noticed really bad performance problems back in FileMaker Server 7 days when there were hundreds of columns in the ResultSet, which is one of the reasons we wrote our own. I thought this problem had been fixed, but I have not tested it since FMS 7.

[mwalker]

Thanks Jesse. I'm about 90% sure it's a server-side issue, but it certainly would not hurt to try a different driver. Since this driver uses the XML publishing interface, I need to set it up properly before I can give it a try.

FYI, I've also opened up a case with FM support and hope to get information from them soon.

[Jesse Barnum]

It is definitely a server-side issue, but the WooF JDBC driver goes through the regular XML web publishing gateway. The FileMaker JDBC driver talks to a server plugin, and that's server plugin is where the speed problem is happening.

[mwalker]

Hi Jesse,

Thanks for your suggestion.

We turned on XML publishing and tried the driver against FMP8 and FMP9. Although the driver does not hang up the server, it looks like it is running into other issues that prevent it from retrieving the appropriate metadata. I'm also unsure that the metadata returned is accurate.

Firstly, we receive this error about 50% of the time we execute a connection and a metadata query:

There was an error, so i'm setting all of the variables and continuing

Exception in thread "Parsing Thread" java.lang.RuntimeException: java.io.IOException: Stream closed

	at com.prosc.fmpjdbc.FmXmlRequest$1.run(FmXmlRequest.java:168)

Caused by: java.io.IOException: Stream closed

	at java.io.BufferedInputStream.getBufIfOpen(Unknown Source)

	at java.io.BufferedInputStream.read(Unknown Source)

	at java.io.FilterInputStream.read(Unknown Source)

	at sun.net.www.protocol.http.HttpURLConnection$HttpInputStream.read(Unknown Source)

	at com.sun.org.apache.xerces.internal.impl.XMLEntityManager$RewindableInputStream.read(Unknown Source)

	at com.sun.org.apache.xerces.internal.impl.io.UTF8Reader.read(Unknown Source)

	at com.sun.org.apache.xerces.internal.impl.XMLEntityScanner.load(Unknown Source)

	at com.sun.org.apache.xerces.internal.impl.XMLEntityScanner.skipSpaces(Unknown Source)

	at com.sun.org.apache.xerces.internal.impl.XMLDocumentScannerImpl$TrailingMiscDispatcher.dispatch(Unknown Source)

	at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)

	at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)

	at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)

	at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(Unknown Source)

	at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(Unknown Source)

	at javax.xml.parsers.SAXParser.parse(Unknown Source)

	at com.prosc.fmpjdbc.FmXmlRequest$1.run(FmXmlRequest.java:161)

Is this a known issue with the driver?



Next, I notice that I'm getting the same metadata back, no matter which table I query, and it doesn't appear to contain the table column names. If I call getColumnNames on my DataBaseMetaData.getColumns() results, I receive this:

			results = dmd.getColumns(null, null, "Lot", "Status");

		//	 GET ALL RESULTS 

		StringBuffer buf = new StringBuffer();

		ResultSetMetaData rsmd = null;

		try {

		    rsmd = results.getMetaData();

		    int numCols = rsmd.getColumnCount();

		    int i, rowcount = 0;

		    System.out.println("There are " + numCols + " columns.");

	

		   // get column header info

		    for (i=1; i <= numCols; i++){

		        if (i > 1) buf.append(",");

		        //buf.append(rsmd.getColumnLabel(i));

		        buf.append(rsmd.getColumnName(i));

		       

		    }

		    buf.append("n");



Output:

There are 22 columns.

TABLE_CAT,TABLE_SCHEM,TABLE_NAME,COLUMN_NAME,DATA_TYPE,TYPE_NAME,COLUMN_SIZE,BUFFER_LENGTH,DECIMAL_DIGITS,NUM_PREC_RADIX,NULLABLE,REMARKS,COLUMN_DEF,SQL_DATA_TYPE,SQL_DATETIME_SUB,CHAR_OCTET_LENGTH,ORDINAL_POSITION,IS_NULLABLE,SCOPE_CATLOG,SCOPE_SCHEMA,SCOPE_TABLE,SOURCE_DATA_TYPE

I would expect to have received the column names, but this appears to be the column names of some system table. Perhaps getColumns is not properly implemented to get a particular table's metadata?





Finally, I've run into a number of unsupported methods, which causes problems for my client. Here are a few examples:

 Exception in thread "main" java.lang.AbstractMethodError: getColumnLabel is not implemented yet.

	at com.prosc.fmpjdbc.FmResultSetMetaData.getColumnLabel(FmResultSetMetaData.java:41)

	at fmp.Mytest.main(Mytest.java:67)





java.lang.AbstractMethodError: isReadOnly is not implemented yet.

   at com.prosc.fmpjdbc.FmMetaData.isReadOnly(FmMetaData.java:597) 







java.lang.AbstractMethodError: getAutoCommit is not implemented yet.

   at com.prosc.fmpjdbc.FmConnection.getAutoCommit(FmConnection.java:258) 

Is the WOOF driver still under development, and do you have plans to address these issues/unsupported methods anytime soon? If so, I will suggest we re-evaluate the driver when these methods are supported.

Thanks for your suggestion and I'm glad I was able to get familiar with this option.

[Jesse Barnum]

Hi mwalker, I'll check into this. However, this week is bad, but I think I'll have some time next week to look at why that's happening.

[Sherry Tirko]

Has anyone had any luck with this? I'm in the process of upgrading to Filemaker 10 and I'm getting the same error. I'm currently using WOOF with Filemaker 6 and it works wonderfully.

Thanks,

Sherry