12 Replies Latest reply on Feb 25, 2016 12:43 PM by m.ardito

Read .dbf format files

m.ardito Feb 23, 2016 10:42 AM

I have one client who still transfer info in this old file format.

I managed to find a jdbc driver (http://csvjdbc.sourceforge.net/doc.html), which works in squirellsql. The url points to a folder, adding the "fileExtension" property with value ".dbf", every file in the folder is read as a sql table, I can select with "like", etc.

Now I would try to do the same in Teiid, if possible: I deployed the .jar (which must include a dependency as the site suggests), I created the DS, but I don't know which translator could work... "file" doesn't, apparently.

Is this possible now, later, never?

On this space (Jboss developer) I only found this old IronJacamar page Read DBF format

But this doesn't help me...

Thanks,

Marco

1. Re: Read .dbf format files

rareddy Feb 23, 2016 10:53 AM (in response to m.ardito)

Do everything as reading from any JDBC source, but translator name in the VDB for the physical model, use "jdbc-simple" and that should read from the above driver.
1 of 1 people found this helpful
Actions
2. Re: Read .dbf format files

m.ardito Feb 23, 2016 11:20 AM (in response to rareddy)

Thanks, it nearly works, I see from DDL generated on the web console that for each .dbf file present in the folder, I get a TABLE, with all the fields, etc.
But I I connect from Squirrel to the teiid VDB, I can see the tables, but no records...

deployment log is
==============================
17:05:48,373 INFO [org.jboss.as.server.deployment] (MSC service thread 1-4) JBAS015876: Starting deployment of "testdbf-vdb.xml" (runtime-name: "testdbf-vdb.xml")
17:05:48,397 INFO [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-6) TEIID40118 VDB testdbf.1 added to the repository - is reloading false
17:05:48,401 INFO [org.teiid.RUNTIME] (MSC service thread 1-6) TEIID50029 VDB testdbf.1 model "testdbf" metadata is currently being loaded. Start Time: 23/02/16 17.05
17:05:48,406 INFO [org.teiid.CONNECTOR] (teiid-async-threads - 5) SimpleJDBCExecutionFactory Commit=true;DatabaseProductName=CsvJdbc;DatabaseProductVersion=1;DriverMajorVersion=1;DriverMajorVersion=0;DriverName=CsvJdbc;DriverVersion=1;IsolationLevel=0
17:05:48,421 INFO [org.teiid.RUNTIME] (teiid-async-threads - 5) TEIID50030 VDB testdbf.1 model "testdbf" metadata loaded. End Time: 23/02/16 17.05
17:05:48,421 INFO [org.jboss.as.server] (HttpManagementService-threads - 68) JBAS015859: Deployed "testdbf-vdb.xml" (runtime-name : "testdbf-vdb.xml")
17:05:48,425 INFO [org.teiid.RUNTIME.VDBLifeCycleListener] (teiid-async-threads - 5) TEIID40003 VDB testdbf.1 is set to ACTIVE
==============================

then connecting from squirell presents the table structure, one table per file, all column names are the same as if connecting from squirrel directly with the csvjdbc.jar, all identical,
but I get no rows (and no rowcount either)

DDL autogenerated is like:
==============================
CREATE FOREIGN TABLE datiapi_20110116 (
    CODICE_DIT string(32767) OPTIONS (NAMEINSOURCE '"CODICE_DIT"', NATIVE_TYPE 'String'),
    RAGIONE1 string(32767) OPTIONS (NAMEINSOURCE '"RAGIONE1"', NATIVE_TYPE 'String'),
    RAGIONE2 string(32767) OPTIONS (NAMEINSOURCE '"RAGIONE2"', NATIVE_TYPE 'String'),
    TIPO string(32767) OPTIONS (NAMEINSOURCE '"TIPO"', NATIVE_TYPE 'String'),
    NUMFAX string(32767) OPTIONS (NAMEINSOURCE '"NUMFAX"', NATIVE_TYPE 'String'),
    NUMTEL string(32767) OPTIONS (NAMEINSOURCE '"NUMTEL"', NATIVE_TYPE 'String'),
    INDIRIZZO string(32767) OPTIONS (NAMEINSOURCE '"INDIRIZZO"', NATIVE_TYPE 'String'),
    CITTA string(32767) OPTIONS (NAMEINSOURCE '"CITTA"', NATIVE_TYPE 'String'),
    E_MAIL string(32767) OPTIONS (NAMEINSOURCE '"E_MAIL"', NATIVE_TYPE 'String'),
    STATO string(32767) OPTIONS (NAMEINSOURCE '"STATO"', NATIVE_TYPE 'String'),
    PAESE string(32767) OPTIONS (NAMEINSOURCE '"PAESE"', NATIVE_TYPE 'String'),
    CAP string(32767) OPTIONS (NAMEINSOURCE '"CAP"', NATIVE_TYPE 'String'),
    NUMERO_OPE double OPTIONS (NAMEINSOURCE '"NUMERO_OPE"', NATIVE_TYPE 'Double'),
    NUMERO_IMP double OPTIONS (NAMEINSOURCE '"NUMERO_IMP"', NATIVE_TYPE 'Double'),
    NUMERO_DIR double OPTIONS (NAMEINSOURCE '"NUMERO_DIR"', NATIVE_TYPE 'Double'),
    LAVORATORI double OPTIONS (NAMEINSOURCE '"LAVORATORI"', NATIVE_TYPE 'Double'),
    CATEGORIA_ string(32767) OPTIONS (NAMEINSOURCE '"CATEGORIA_"', NATIVE_TYPE 'String'),
    CONTRATTO1 string(32767) OPTIONS (NAMEINSOURCE '"CONTRATTO1"', NATIVE_TYPE 'String'),
    CONTRATTO2 string(32767) OPTIONS (NAMEINSOURCE '"CONTRATTO2"', NATIVE_TYPE 'String'),
    CONTRATTO3 string(32767) OPTIONS (NAMEINSOURCE '"CONTRATTO3"', NATIVE_TYPE 'String'),
    CONTRATTO4 string(32767) OPTIONS (NAMEINSOURCE '"CONTRATTO4"', NATIVE_TYPE 'String'),
    UC1 string(32767) OPTIONS (NAMEINSOURCE '"UC1"', NATIVE_TYPE 'String'),
    UC2 string(32767) OPTIONS (NAMEINSOURCE '"UC2"', NATIVE_TYPE 'String'),
    UC3 string(32767) OPTIONS (NAMEINSOURCE '"UC3"', NATIVE_TYPE 'String'),
    PARTITAIVA string(32767) OPTIONS (NAMEINSOURCE '"PARTITAIVA"', NATIVE_TYPE 'String')
==============================

trying a simple sql select gets:
==============================
Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 testdbf: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT "PUBLIC"."datiapi_20110116"."CODICE_DIT", "PUBLIC"."datiapi_20110116"."RAGIONE1", "PUBLIC"."datiapi_20110116"."RAGIONE2", "PUBLIC"."datiapi_20110116"."TIPO", "PUBLIC"."datiapi_20110116"."NUMFAX", "PUBLIC"."datiapi_20110116"."NUMTEL", "PUBLIC"."datiapi_20110116"."INDIRIZZO", "PUBLIC"."datiapi_20110116"."CITTA", "PUBLIC"."datiapi_20110116"."E_MAIL", "PUBLIC"."datiapi_20110116"."STATO", "PUBLIC"."datiapi_20110116"."PAESE", "PUBLIC"."datiapi_20110116"."CAP", "PUBLIC"."datiapi_20110116"."NUMERO_OPE", "PUBLIC"."datiapi_20110116"."NUMERO_IMP", "PUBLIC"."datiapi_20110116"."NUMERO_DIR", "PUBLIC"."datiapi_20110116"."LAVORATORI", "PUBLIC"."datiapi_20110116"."CATEGORIA_", "PUBLIC"."datiapi_20110116"."CONTRATTO1", "PUBLIC"."datiapi_20110116"."CONTRATTO2", "PUBLIC"."datiapi_20110116"."CONTRATTO3", "PUBLIC"."datiapi_20110116"."CONTRATTO4", "PUBLIC"."datiapi_20110116"."UC1", "PUBLIC"."datiapi_20110116"."UC2", "PUBLIC"."datiapi_20110116"."UC3", "PUBLIC"."datiapi_20110116"."PARTITAIVA" FROM "PUBLIC"."datiapi_20110116" WHERE "PUBLIC"."datiapi_20110116"."INDIRIZZO" LIKE '%FRANCIA%']
SQLState: 50000
ErrorCode: 30504
==============================

then in the logs it seems that a "PUBLIC." table prefix is added with the query... I don't know why, though... and it expect that to be the file name...?

'File not found: /opt/msaccess/PUBLIC.datiapi_20110116.dbf'"

this is what I find
==============================
17:08:00,041 WARN [org.teiid.CONNECTOR] (Worker33_QueryProcessorQueue581) nu4Cln8rXF8K Connector worker process failed for atomic-request=nu4Cln8rXF8K.5.3.905: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT 1 FROM "PUBLIC"."datiapi_20110116"]
        at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.3.jar:8.12.3]
        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:349)
        at sun.reflect.GeneratedMethodAccessor90.invoke(Unknown Source) [:1.7.0_91]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_91]
        at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_91]
        at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
        at com.sun.proxy.$Proxy49.execute(Unknown Source)
        at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
        at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_91]
        at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_91]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_91]
        at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_91]
Caused by: java.sql.SQLException: File not found: /opt/msaccess/PUBLIC.datiapi_20110116.dbf
        at org.relique.jdbc.csv.CsvStatement.executeParsedQuery(Unknown Source)
        at org.relique.jdbc.csv.CsvStatement.executeParsedQuery(Unknown Source)
        at org.relique.jdbc.csv.CsvPreparedStatement.executeQuery(Unknown Source)
        at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
        at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.3.jar:8.12.3]
        ... 17 more

17:08:00,092 WARN [org.teiid.PROCESSOR] (Worker32_QueryProcessorQueue582) nu4Cln8rXF8K TEIID30020 Processing exception for request nu4Cln8rXF8K.5 'TEIID30504 testdbf: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT 1 FROM "PUBLIC"."datiapi_20110116"]'. Originally TeiidProcessingException 'File not found: /opt/msaccess/PUBLIC.datiapi_20110116.dbf' org.relique.jdbc.csv.CsvStatement.executeParsedQuery(Unknown Source). Enable more detailed logging to see the entire stacktrace.
17:08:02,161 WARN [org.teiid.CONNECTOR] (Worker33_QueryProcessorQueue586) nu4Cln8rXF8K Connector worker process failed for atomic-request=nu4Cln8rXF8K.7.1.906: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT "PUBLIC"."datiapi_20110116"."CODICE_DIT", "PUBLIC"."datiapi_20110116"."RAGIONE1", "PUBLIC"."datiapi_20110116"."RAGIONE2", "PUBLIC"."datiapi_20110116"."TIPO", "PUBLIC"."datiapi_20110116"."NUMFAX", "PUBLIC"."datiapi_20110116"."NUMTEL", "PUBLIC"."datiapi_20110116"."INDIRIZZO", "PUBLIC"."datiapi_20110116"."CITTA", "PUBLIC"."datiapi_20110116"."E_MAIL", "PUBLIC"."datiapi_20110116"."STATO", "PUBLIC"."datiapi_20110116"."PAESE", "PUBLIC"."datiapi_20110116"."CAP", "PUBLIC"."datiapi_20110116"."NUMERO_OPE", "PUBLIC"."datiapi_20110116"."NUMERO_IMP", "PUBLIC"."datiapi_20110116"."NUMERO_DIR", "PUBLIC"."datiapi_20110116"."LAVORATORI", "PUBLIC"."datiapi_20110116"."CATEGORIA_", "PUBLIC"."datiapi_20110116"."CONTRATTO1", "PUBLIC"."datiapi_20110116"."CONTRATTO2", "PUBLIC"."datiapi_20110116"."CONTRATTO3", "PUBLIC"."datiapi_20110116"."CONTRATTO4", "PUBLIC"."datiapi_20110116"."UC1", "PUBLIC"."datiapi_20110116"."UC2", "PUBLIC"."datiapi_20110116"."UC3", "PUBLIC"."datiapi_20110116"."PARTITAIVA" FROM "PUBLIC"."datiapi_20110116"]
        at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.3.jar:8.12.3]
        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:349)
        at sun.reflect.GeneratedMethodAccessor90.invoke(Unknown Source) [:1.7.0_91]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_91]
        at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_91]
        at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
        at com.sun.proxy.$Proxy49.execute(Unknown Source)
        at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
        at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_91]
        at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_91]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_91]
        at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_91]
Caused by: java.sql.SQLException: Syntax error: Encountered " <NAME> "\"CODICE_DIT\" "" at line 1, column 36.
Was expecting:
    <EOF>

        at org.relique.jdbc.csv.CsvPreparedStatement.<init>(Unknown Source)
        at org.relique.jdbc.csv.CsvConnection.prepareStatement(Unknown Source)
        at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:748)
        at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:734)
        at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:405)
        at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:199) [translator-jdbc-8.12.3.jar:8.12.3]
        at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:121) [translator-jdbc-8.12.3.jar:8.12.3]
        ... 17 more

17:08:02,245 WARN [org.teiid.PROCESSOR] (Worker32_QueryProcessorQueue587) nu4Cln8rXF8K TEIID30020 Processing exception for request nu4Cln8rXF8K.7 'TEIID30504 testdbf: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT "PUBLIC"."datiapi_20110116"."CODICE_DIT", "PUBLIC"."datiapi_20110116"."RAGIONE1", "PUBLIC"."datiapi_20110116"."RAGIONE2", "PUBLIC"."datiapi_20110116"."TIPO", "PUBLIC"."datiapi_20110116"."NUMFAX", "PUBLIC"."datiapi_20110116"."NUMTEL", "PUBLIC"."datiapi_20110116"."INDIRIZZO", "PUBLIC"."datiapi_20110116"."CITTA", "PUBLIC"."datiapi_20110116"."E_MAIL", "PUBLIC"."datiapi_20110116"."STATO", "PUBLIC"."datiapi_20110116"."PAESE", "PUBLIC"."datiapi_20110116"."CAP", "PUBLIC"."datiapi_20110116"."NUMERO_OPE", "PUBLIC"."datiapi_20110116"."NUMERO_IMP", "PUBLIC"."datiapi_20110116"."NUMERO_DIR", "PUBLIC"."datiapi_20110116"."LAVORATORI", "PUBLIC"."datiapi_20110116"."CATEGORIA_", "PUBLIC"."datiapi_20110116"."CONTRATTO1", "PUBLIC"."datiapi_20110116"."CONTRATTO2", "PUBLIC"."datiapi_20110116"."CONTRATTO3", "PUBLIC"."datiapi_20110116"."CONTRATTO4", "PUBLIC"."datiapi_20110116"."UC1", "PUBLIC"."datiapi_20110116"."UC2", "PUBLIC"."datiapi_20110116"."UC3", "PUBLIC"."datiapi_20110116"."PARTITAIVA" FROM "PUBLIC"."datiapi_20110116"]'. Originally TeiidProcessingException 'Syntax error: Encountered " <NAME> "\"CODICE_DIT\" "" at line 1, column 36.
Was expecting:
    <EOF>
    ' org.relique.jdbc.csv.CsvPreparedStatement.<init>(Unknown Source). Enable more detailed logging to see the entire stacktrace.
Actions
3. Re: Read .dbf format files

rareddy Feb 23, 2016 11:43 AM (in response to m.ardito)
may be prepared statements are not supported. Add the translator override below to your -vdb.xml file turn off prepared statements on "jdbc-simple" translator

<translator name="jdbc-simple-override" type="jdbc-simple"> <property name="UseBindVariables" value="false"/> </translator>

then use the "jdbc-simple-override" as the translator name in your model.
Actions
4. Re: Read .dbf format files

m.ardito Feb 23, 2016 11:58 AM (in response to rareddy)

..Not yet.

still getting this, though.

vdb is now
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="testdbf" version="1">
    <description>test dbf</description>
    <property name="UseConnectorMetadata" value="true" />
    <model name="testdbf">
        <property name="importer.useFullSchemaName" value="false"/>
        <source name="testdbf" translator-name="jdbc-simple-override" connection-jndi-name="java:/testdbf"/>
    </model>
    <translator name="jdbc-simple-override" type="jdbc-simple">
        <property name="UseBindVariables" value="false"/>
    </translator>
</vdb>

still getting this, though. this "public" prefix is coming from nowhere...

and for the 'File not found: /opt/msaccess/PUBLIC.datiapi_20110116.dbf', the real file is "/opt/msaccess/datiapi_20110116.dbf"

17:50:06,221 WARN [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue11) aRZr1leO/PNq Connector worker process failed for atomic-request=aRZr1leO/PNq.5.1.915: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT "PUBLIC"."datiapi_20110116"."CODICE_DIT", "PUBLIC"."datiapi_20110116"."RAGIONE1", "PUBLIC"."datiapi_20110116"."RAGIONE2", "PUBLIC"."datiapi_20110116"."TIPO", "PUBLIC"."datiapi_20110116"."NUMFAX", "PUBLIC"."datiapi_20110116"."NUMTEL", "PUBLIC"."datiapi_20110116"."INDIRIZZO", "PUBLIC"."datiapi_20110116"."CITTA", "PUBLIC"."datiapi_20110116"."E_MAIL", "PUBLIC"."datiapi_20110116"."STATO", "PUBLIC"."datiapi_20110116"."PAESE", "PUBLIC"."datiapi_20110116"."CAP", "PUBLIC"."datiapi_20110116"."NUMERO_OPE", "PUBLIC"."datiapi_20110116"."NUMERO_IMP", "PUBLIC"."datiapi_20110116"."NUMERO_DIR", "PUBLIC"."datiapi_20110116"."LAVORATORI", "PUBLIC"."datiapi_20110116"."CATEGORIA_", "PUBLIC"."datiapi_20110116"."CONTRATTO1", "PUBLIC"."datiapi_20110116"."CONTRATTO2", "PUBLIC"."datiapi_20110116"."CONTRATTO3", "PUBLIC"."datiapi_20110116"."CONTRATTO4", "PUBLIC"."datiapi_20110116"."UC1", "PUBLIC"."datiapi_20110116"."UC2", "PUBLIC"."datiapi_20110116"."UC3", "PUBLIC"."datiapi_20110116"."PARTITAIVA" FROM "PUBLIC"."datiapi_20110116"]
        at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.3.jar:8.12.3]
        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:349)
        at sun.reflect.GeneratedMethodAccessor90.invoke(Unknown Source) [:1.7.0_91]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_91]
        at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_91]
        at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
        at com.sun.proxy.$Proxy49.execute(Unknown Source)
        at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
        at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_91]
        at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_91]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_91]
        at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_91]
Caused by: java.sql.SQLException: Syntax error: Encountered " <NAME> "\"CODICE_DIT\" "" at line 1, column 36.
Was expecting:
    <EOF>

        at org.relique.jdbc.csv.CsvStatement.executeQuery(Unknown Source)
        at org.jboss.jca.adapters.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:344)
        at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:119) [translator-jdbc-8.12.3.jar:8.12.3]
        ... 17 more

17:50:06,286 WARN [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue12) aRZr1leO/PNq TEIID30020 Processing exception for request aRZr1leO/PNq.5 'TEIID30504 testdbf: 0 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT "PUBLIC"."datiapi_20110116"."CODICE_DIT", "PUBLIC"."datiapi_20110116"."RAGIONE1", "PUBLIC"."datiapi_20110116"."RAGIONE2", "PUBLIC"."datiapi_20110116"."TIPO", "PUBLIC"."datiapi_20110116"."NUMFAX", "PUBLIC"."datiapi_20110116"."NUMTEL", "PUBLIC"."datiapi_20110116"."INDIRIZZO", "PUBLIC"."datiapi_20110116"."CITTA", "PUBLIC"."datiapi_20110116"."E_MAIL", "PUBLIC"."datiapi_20110116"."STATO", "PUBLIC"."datiapi_20110116"."PAESE", "PUBLIC"."datiapi_20110116"."CAP", "PUBLIC"."datiapi_20110116"."NUMERO_OPE", "PUBLIC"."datiapi_20110116"."NUMERO_IMP", "PUBLIC"."datiapi_20110116"."NUMERO_DIR", "PUBLIC"."datiapi_20110116"."LAVORATORI", "PUBLIC"."datiapi_20110116"."CATEGORIA_", "PUBLIC"."datiapi_20110116"."CONTRATTO1", "PUBLIC"."datiapi_20110116"."CONTRATTO2", "PUBLIC"."datiapi_20110116"."CONTRATTO3", "PUBLIC"."datiapi_20110116"."CONTRATTO4", "PUBLIC"."datiapi_20110116"."UC1", "PUBLIC"."datiapi_20110116"."UC2", "PUBLIC"."datiapi_20110116"."UC3", "PUBLIC"."datiapi_20110116"."PARTITAIVA" FROM "PUBLIC"."datiapi_20110116"]'. Originally TeiidProcessingException 'Syntax error: Encountered " <NAME> "\"CODICE_DIT\" "" at line 1, column 36.
Was expecting:
    <EOF>
    ' org.relique.jdbc.csv.CsvStatement.executeQuery(Unknown Source). Enable more detailed logging to see the entire stacktrace.
17:50:08,011 WARN [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue14) aRZr1leO/PNq Connector worker process failed for atomic-request=aRZr1leO/PNq.6.3.916: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT 1 FROM "PUBLIC"."datiapi_20110116"]
        at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.3.jar:8.12.3]
        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:349)
        at sun.reflect.GeneratedMethodAccessor90.invoke(Unknown Source) [:1.7.0_91]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_91]
        at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_91]
        at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
        at com.sun.proxy.$Proxy49.execute(Unknown Source)
        at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
        at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_91]
        at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_91]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_91]
        at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_91]
Caused by: java.sql.SQLException: File not found: /opt/msaccess/PUBLIC.datiapi_20110116.dbf
        at org.relique.jdbc.csv.CsvStatement.executeParsedQuery(Unknown Source)
        at org.relique.jdbc.csv.CsvStatement.executeParsedQuery(Unknown Source)
        at org.relique.jdbc.csv.CsvStatement.executeQuery(Unknown Source)
        at org.jboss.jca.adapters.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:344)
        at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:119) [translator-jdbc-8.12.3.jar:8.12.3]
        ... 17 more

17:50:08,060 WARN [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue15) aRZr1leO/PNq TEIID30020 Processing exception for request aRZr1leO/PNq.6 'TEIID30504 testdbf: 0 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT 1 FROM "PUBLIC"."datiapi_20110116"]'. Originally TeiidProcessingException 'File not found: /opt/msaccess/PUBLIC.datiapi_20110116.dbf' org.relique.jdbc.csv.CsvStatement.executeParsedQuery(Unknown Source). Enable more detailed logging to see the entire stacktrace.
Actions
5. Re: Read .dbf format files

rareddy Feb 23, 2016 12:13 PM (in response to m.ardito)

How does your data source URL connection look like in standalone-teiid.xml?
Actions
6. Re: Read .dbf format files

m.ardito Feb 23, 2016 12:17 PM (in response to rareddy)

It's this one... I created it form the web console though.

<datasource jta="true" jndi-name="java:/testdbf" pool-name="testdbf" enabled="true" use-ccm="true" statistics-enabled="false">
        <connection-url>jdbc:relique:csv:/opt/msaccess</connection-url>
        <driver-class>org.relique.jdbc.csv.CsvDriver</driver-class>
        <connection-property name="fileExtension">
                .dbf
        </connection-property>
        <driver>csvjdbc.jar</driver>
        <validation>
                <validate-on-match>false</validate-on-match>
                <background-validation>false</background-validation>
        </validation>
        <timeout>
                <set-tx-query-timeout>false</set-tx-query-timeout>
                <blocking-timeout-millis>0</blocking-timeout-millis>
                <idle-timeout-minutes>0</idle-timeout-minutes>
                <query-timeout>0</query-timeout>
                <use-try-lock>0</use-try-lock>
                <allocation-retry>0</allocation-retry>
                <allocation-retry-wait-millis>0</allocation-retry-wait-millis>
        </timeout>
        <statement>
                <share-prepared-statements>false</share-prepared-statements>
        </statement>
</datasource>
Actions
7. Re: Read .dbf format files

rareddy Feb 23, 2016 6:09 PM (in response to m.ardito)

Also try setting the "importer.useQualifiedName" to "false". For some reason the fully qualified name is being used when you access the table.
Actions
8. Re: Read .dbf format files

m.ardito Feb 24, 2016 3:06 AM (in response to rareddy)

YES! That was the reason... thank you for your assistance, again.
Another little gem learned...

Marco
Actions
9. Re: Read .dbf format files

m.ardito Feb 24, 2016 12:08 PM (in response to rareddy)

Now I am having a related issue, about the folder specified:

in the above example I used
<connection-url>jdbc:relique:csv:/opt/msaccess</connection-url>

where the .dbf path (/opt/msaccess) is a linux native folder, and this now works well

but my real files usually are on a windows NTFS share so on teiid server (debian) I mounted it under
/mnt/myshare/TEST

and then modified the connection url accordingly, leaving all the rest untouched, like:
<connection-url>jdbc:relique:csv:/mnt/myshare/TEST</connection-url>

from linux CLI I can browse the folder and see the .dbf files.
But the DB won't load, in teiid, and shows an error related to the DBF library (in the jar file), reported below

from my tests, any linux local folder works, but a windows share folder mounted like that, won't.

Now, I understand that this may be having nothing to do with teiid, but from squirrel, on windows 7, using the same jar and using a connection url like
jdbc:relique:csv:V:\TEST

(which is the exact same folder mounted in linux as /mnt/myshare/TEST, now used as network unit V:, thus containing the same exact files that linux sees through its mount point... )

I can load the files, and see/use the tables

So I guess this should not be related really to the files themselves, or the filesystem but there should be some other issue, maybe the DBF library can't reach a NTFS share mounted under linux?
Or what else could it be, on the teiid/jboss server side? What to look for?

Marco

==============================
17:44:35,756 INFO [org.jboss.as.server] (HttpManagementService-threads - 49) JBAS015859: Deployed "testdbf-vdb.xml" (runtime-name : "testdbf-vdb.xml")
17:44:35,760 WARN [org.teiid.RUNTIME] (teiid-async-threads - 4) TEIID50036 VDB testdbf.1 model "testdbf" metadata failed to load. Reason:TEIID11010 java.sql.SQLException: DANS DBF Library error: java.lang.reflect.InvocationTargetException: org.teiid.translator.TranslatorException: TEIID11010 java.sql.SQLException: DANS DBF Library error: java.lang.reflect.InvocationTargetException
        at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecutionFactory.java:297)
        at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecutionFactory.java:68)
        at org.teiid.query.metadata.NativeMetadataRepository.getMetadata(NativeMetadataRepository.java:83) [teiid-engine-8.12.3.jar:8.12.3]
        at org.teiid.query.metadata.NativeMetadataRepository.loadMetadata(NativeMetadataRepository.java:60) [teiid-engine-8.12.3.jar:8.12.3]
        at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(ChainingMetadataRepository.java:55) [teiid-engine-8.12.3.jar:8.12.3]
        at org.teiid.jboss.VDBService$6.run(VDBService.java:395) [teiid-jboss-integration-8.12.3.jar:8.12.3]
        at org.teiid.jboss.VDBService$7.run(VDBService.java:446) [teiid-jboss-integration-8.12.3.jar:8.12.3]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_91]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_91]
        at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_91]
        at org.jboss.threads.JBossThread.run(JBossThread.java:122)
Caused by: java.sql.SQLException: DANS DBF Library error: java.lang.reflect.InvocationTargetException
        at org.relique.jdbc.dbf.DbfReader.<init>(Unknown Source)
        at org.relique.jdbc.csv.CsvStatement.executeParsedQuery(Unknown Source)
        at org.relique.jdbc.csv.CsvStatement.executeParsedQuery(Unknown Source)
        at org.relique.jdbc.csv.CsvStatement.executeQuery(Unknown Source)
        at org.relique.jdbc.csv.CsvDatabaseMetaData.getColumns(Unknown Source)
        at org.teiid.translator.jdbc.JDBCMetdataProcessor.getColumns(JDBCMetdataProcessor.java:356)
        at org.teiid.translator.jdbc.JDBCMetdataProcessor.getTables(JDBCMetdataProcessor.java:308)
        at org.teiid.translator.jdbc.JDBCMetdataProcessor.getConnectorMetadata(JDBCMetdataProcessor.java:158)
        at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecutionFactory.java:295)
        ... 10 more
Actions
10. Re: Read .dbf format files

m.ardito Feb 25, 2016 4:07 AM (in response to m.ardito)

I also opened a thread on csvjdbc form on SF, let's hope something comes out on that side, too...
edit: here https://sourceforge.net/p/csvjdbc/discussion/56964/thread/a6446460/

I'm puzzled because on the same windows share mounted I read .xls files with the excel translator.... so it should not be something related to the mount itself, or the linux user accessing it...

like

~# ls -la /mnt/winshare/PATH/TO/EXCEL/FILES/
totale 2588
drwxr-xr-x 1 root root      0 feb 24 10:06 .
drwxr-xr-x 1 root root      0 ott 22 14:09 ..
-rwxr-xr-x 0 root root 878592 feb 24 10:00 A.xls
-rwxr-xr-x 0 root root 800256 feb 23 13:45 B.xls

~# ls -la /mnt/winshare/TEST/
totale 1128
drwxr-xr-x 0 root root      0 feb 24 17:49 .
drwxr-xr-x 1 root root      0 ott 14 16:28 ..
-rwxr-xr-x 0 root root 576522 gen 11 12:11 A.dbf
-rwxr-xr-x 0 root root 575194 feb 11 11:06 B.dbf

mount command output is :
//192.168.X.Y/winshare on /mnt/winshare type cifs (rw,relatime,sec=ntlm,unc=\\192.168.X.Y\winshare,username=username,uid=0,noforceuid,gid=0,noforcegid,addr=192.168.X.Y,file_mode=0755,dir_mode=0755,nounix,serverino,rsize=61440,wsize=65536,actimeo=1)

Marco
Actions
11. Re: Read .dbf format files

rareddy Feb 25, 2016 9:08 AM (in response to m.ardito)

IMO, it is not the mount nor the Teiid. I believe something else with the driver. Also try giving the "write" permissions on the files, I am not sure if driver may be trying to open in read-write mode and user running the Teiid is "root" or not. After that you may need to use java debugger to walk the driver code to see what is causing this.

Ramesh..
1 of 1 people found this helpful
Actions
12. Re: Read .dbf format files

m.ardito Feb 25, 2016 12:43 PM (in response to rareddy)

Ramesh Reddy ha scritto:

IMO, it is not the mount nor the Teiid. I believe something else with the driver. Also try giving the "write" permissions on the files, I am not sure if driver may be trying to open in read-write mode

Ramesh..

...and once again, you were absolutely right... I was maybe misleaded from the driver home page that states "CsvJdbc is a read-only JDBC driver that uses Comma Separated Value (CSV) files or DBF files as database tables."

edit:
I dropped a line on that forum thread, too, to point this out, and save other's some struggle perhaps.

edit2:
For anyone else following my path, keep in mind that in any case this driver apparently does not support spaces in the folder path and in the .dbf file names... if in the folder you have any file called like "dbdata 2016.dbf", the driver will look for a file named "dbdata.dbf", it will not find it and then fail loading the whole VDB. If all files are like "dbdata_2016.dbf", instead, it loads properly.
I can change that for my files, no problem, but anyway this sis to be kept in mind.

but, really I could not have this sorted out so fast, without your great help, again.

Another little gem learned... the second in a thread...

Many, many thanks

Marco
Actions

Go to original post