Blobing with JDBC

[dwanehollands]

Hey all i am trying to download a picture from a container field in a FMP7 database ... i checked the conversions from filemaker types to JDBC sql types and Container converts to BLOG so after connecting to the databas i executed the following code

public void executeSQL(String statement){

try{

Statement stat = con.createStatement();

ResultSet result;

System.out.println(stat.execute("SELECT thing FROM TEMP"));

result = stat.getResultSet();

result.next();

ResultSetMetaData md = result.getMetaData();

System.out.println(md.getColumnName(1));

System.out.println(md.getColumnType(1));

while(result.wasNull() != true){

Blob blob = result.getBlob(1);

System.out.println(result.getFetchSize() + "records");

result.next();

}

result.close();

stat.close();

}

catch(Exception e){

System.out.println("Error executing sql: " + e);

}

}

So anyway the problems is that the driver doesnt read the contained as a blob it reads it as a VARCHAR so i get a cast exception

can anyone tell me what im doing wrong

thanks in advance

[passenger7]

// getting blob object from result set

fileBean.setFile((java.sql.Blob)rs.getBlob(7));

//generate the following exception

java.sql.SQLException: [DataDirect][sequeLink JDBC Driver]Unsupported data conversion

// reconstructing blob object

// attempt to save a file to file system

java.sql.Blob objFile = (java.sql.Blob)fileBean.getFile();

System.out.println(objFile.length());

try

{

InputStream in = objFile.getBinaryStream();

byte[] bytes = new byte[in.available()];

for (int i = 0; i < bytes.length; i++)

{

bytes = (byte)in.read();

}

FileOutputStream out = new FileOutputStream(file);

out.write(bytes);

out.close();

}

catch (Exception err)

{

System.err.println(err);

}

}

I also have this problem and will continue to investigate...

[passenger7]

So far I tested and confirm the problem in Developer 7, using the FileMaker Developer 7 JDBC Client Software driver, on my computer running Window XP SP 2, CPU 2GHz, 640MB RAM.

I open up a new test case with FileMaker, Case 2536141:

JDBC Client Software driver is not working as documented in the jdbc and odbc developer manual, page 37:

1. FileMaker field type number should be converted to JDBC Sql type java.sql.Types.DECIMAL

2. FileMaker field type container should be converted to JDBC Sql type java.sql.Types.BLOB

When attempting to retrieve information using ResultSet.getBlob(), as expected, it returns the following exception:

java.sql.SQLException: [DataDirect][sequeLink JDBC Driver]Unsupported data conversion.

Using the <PATH>JDBC Client Driver Installertestforjdbctestforjdbc.bat utility included in the JDBC client software, I was able to render out the information below, which demonstrate that both DECIMAL and BLOB type columns are returned in the wrong java class. This issue is also being discussed in fmpforum.com, a filemaker community, and others are having the same problem. I've been trying to migrate our Financial System to the new version since the release date, but had no success due to these migration issues in the new JDBC Click Software packaged in the new FileMaker 7 family.

I did not get a change to test the calculation field. Perhaps it's a good idea to confirm that the correct data type is being returned correctly, based on result type (VARCHAR, DECIMAL, DATE, TIMESTAMP, TIME, and BLOB).

To reproduce,

1. Install the JDBC Client Software on your computer by double clicking on <PATH>xDBCJDBC Client Driver Installersljcinstaller.jar

2. Run <PATH>xDBCJDBC Client Driver Installertestforjdbctestforjdbc.bat

3. Connect to a FileMaker 7 database and type in SQL to retrieve both number and blob type fields from database.

4. Get column meta data and notice the conversion type problem.'

getMetaData() Succeeded.

Catalog Name

Column Display Size 25

Column Label File ID

Column Name File ID

Column Type 8

Column Type Name DECIMAL

Precision 15

Scale 0

Schema Name

Table Name

Auto Increment false

Case Sensitive false

Is Currency false

Definitely Writable false

Is Nullable 1

Read Only false

Searchable true

Is Signed true

Is Writable true

Column Class Name java.lang.Double

=====================================

executeQuery() Results: Returned ResultSet

getMetaData() Succeeded.

Catalog Name

Column Display Size 1000000

Column Label file_blob_container

Column Name file_blob_container

Column Type 12

Column Type Name VARCHAR, Why is BLOB type column being read as VARCHAR in JDBC

Precision 1000000

Scale 0

Schema Name

Table Name

Auto Increment false

Case Sensitive true

Is Currency false

Definitely Writable false

Is Nullable 1

Read Only false

Searchable true

Is Signed false

Is Writable true

Column Class Name java.lang.String

[passenger7]

Case 2536141 Update, fmp is aware of the problem and has escalated the case with no time frame for patch. This bug is present in both ODBC and JDBC...

Forward response from a DataDirect Developer at http://forums.datadirect.com on this issue, which contradicts the information stated in the FMP ODBC and JDBC manual:

" I discussed this with the team that implemented the filemaker driver and have a partial answer.

With regrads to a Blob container being described as a VARCHAR, this was implemented this way per FileMaker's request. Both the ODBC driver and the JDBC driver should behave this way.

I am still looking into the DECIMAL case.

"

=============================================================================

Forward Message from FileMaker,user case 2536141 as a reference

Jose,

Just a note to let you know that we have received your file attachment.

We have checked the functionality of the JDBC driver and verified your

findings that both DECIMAL and BLOB type columns are returned in the wrong

java class.

This problem has been escalated. We do not have an estimate on when a fix

might be available.

Thank You for contacting FileMaker,