|
Replies:
7
-
Pages:
1
-
Last Post:
Apr 13, 2006 8:33 PM
by: kazar
|
Threads:
[
Previous
|
Next
]
|
|
Posts:
7
Registered:
12/30/04
|
|
|
|
Incorrect column types returned in JDBC, OEM FileMaker 7
Posted:
Dec 30, 2004 8:40 PM
|
|
|
We hit a wall in our migration efforts from FileMaker 6 to FileMaker 7 with the new DirectData JDBC driver embedded with the FileMaker distribution. Using the jdbc:sequelink utility 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
Problem: DECIMALS are returned as java.lang.Double and BLOBS are returned as VARCHAR java.lang.String.
NOTE: This is not the case in ODBC....
Information rendered by the testforjdbc utility connecting to a FileMaker Pro 7 database.
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
|
|
Posts:
7
Registered:
12/30/04
|
|
|
|
Re: Incorrect column types returned in JDBC, OEM FileMaker 7
Posted:
Jan 7, 2005 3:46 PM
in response to: 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 Installer\testforjdbc\testforjdbc.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>xDBC\JDBC Client Driver Installer\sljcinstaller.jar
2. Run <PATH>\xDBC\JDBC Client Driver Installer\testforjdbc\testforjdbc.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
|
|
Posts:
204
Registered:
7/7/04
|
|
|
|
Re: Incorrect column types returned in JDBC, OEM FileMaker 7
Posted:
Jan 7, 2005 4:24 PM
in response to: passenger7
|
|
|
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.
|
|
Posts:
7
Registered:
12/30/04
|
|
|
|
Re: Incorrect column types returned in JDBC, OEM FileMaker 7
Posted:
Jan 10, 2005 5:29 PM
in response to: mbiamonte
|
|
|
|
|
Unfortunately, I suspect a communication meltdown between developers at FileMaker and DataDirect. In the attached zip file you will find the following:
1. fm7_odbc_jdbc_developer.pdf, FileMaker documented support for: VARCHAR,DECIMAL,DATE,TIME,TIMESTAMP,BLOB, conversions, in ODBC and JDBC DataDirect driver.
2. FileMakerODBCView.rtf and ODBCContradiction.rtf, demonstrate a connection to FileMaker 7 Employees database via ODBC, using the Advanced Query Tool 6.1.4, rendering meta data that communicate support for all VARCHAR,DECIMAL,DATE,TIME,TIMESTAMP,BLOB, conversion. When executing a query, I notice that the results communicated as you said, BLOB returned as VARCHAR, as text. This is not the case for the DECIMAL type, which is returned as documented.
3. Employees.fp7 and ToReproduce.pdf are also included if you need to communicate this problem with developers.
The problem is that the JDBC Client Software driver is not working as documented in the FileMaker 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
I only ask that you may communicate this problem with FileMaker so that they may update their documentation, and not use the conversion feature as false information to hype their product, or better yet, invest in submitting new specs to data direct developers to come up with an adequate driver that support all expected conversions: VARCHAR,DECIMAL,DATE,TIME,TIMESTAMP,BLOB
for both JDBC and ODBJC
|
|
Posts:
7
Registered:
12/30/04
|
|
|
|
Re: Incorrect column types returned in JDBC, OEM FileMaker 7
Posted:
Jan 11, 2005 9:21 AM
in response to: mbiamonte
|
|
|
use technical support case 2536141 as a reference
Message from FileMaker
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,
|
|
Posts:
1
Registered:
3/4/05
|
|
|
|
Re: Incorrect column types returned in JDBC, OEM FileMaker 7
Posted:
Mar 4, 2005 10:50 AM
in response to: passenger7
|
|
|
I had the same problem (decimal for number, blob for text fields) but with using of ODBC.
I have also contacted so many addresses (FileMaker, DataDirect, Microsoft, Cognos...) - all vendoor parties which its software I am working. But nobody could say to me anything useful.
So I solved this issue - simply, I have designated each field (Define Database..., Fields..., Validation, Maximum number of characters...) at number 100. And everything is working!
Why 100? (I don't need more)! :) In the help of FileMaker I have found that ODBC driver supports specific entry-level conformance (SQL-92):
"FileMaker Pro uses the DataDirect 32-BIT SequeLink 5.4 drivers to share data as a data source. The ODBC driver, when used with FileMaker Pro, supports ODBC 3.5 Level 1 with some features of Level 2. The JDBC driver, when used with FileMaker Pro, supports JDBC 3.0. The ODBC and JDBC drivers support SQL-92 entry-level conformance, with some SQL-92 intermediate features."
As SQL for itself 'read' text fields up to 255 characters, but FileMaker can store at fields up to 2 Gb of data - the logic way was to define 'somewhere' which is size of the field.
Although this is only first 'key' to extract FileMaker data to any other database. Second key is to define the same table (at importing side) with field attributes: text as varchar, number as nvarchar!
I am using BI software (Cognos), but until today I couldn't find any option for direct connecting to FileMaker. Instead of this, I am forced to re-load data from FileMaker to Microsoft SQL or Access, or simple txt or csv - and only then to use.
Good product, bad exporting, but there is some solution..
Hope this will help!
|
|
Posts:
2
Registered:
2/17/05
|
|
|
|
Re: Incorrect column types returned in JDBC, OEM FileMaker 7
Posted:
Apr 17, 2005 8:42 PM
in response to: passenger7
|
|
|
This problem has been escalated. We do not have an estimate on when a fix
might be available.
Thank You for contacting FileMaker
I got the same answer from Filemaker when complaining about how slow the JDBC driver operates on databases with a large number of fields. They told me it was "software" and you can't set a date on fixes. I'm puzzled I thought Filemaker was a software company and would be able to judge how long a fix on a defective driver might take. The failure of the JDBC driver will greatly impact Filemaker's quest to have large companies consider their database as an enterprise solution.
|
|
Posts:
2
Registered:
4/13/06
|
|
|
|
|
