12 Replies Latest reply on Feb 25, 2016 12:43 PM by Marco Ardito

    Read .dbf format files

    Marco Ardito Master

      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
          Ramesh Reddy Master

          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
          • 2. Re: Read .dbf format files
            Marco Ardito Master

            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.

            • 3. Re: Read .dbf format files
              Ramesh Reddy Master

              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.

              • 4. Re: Read .dbf format files
                Marco Ardito Master

                ..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.

                • 5. Re: Read .dbf format files
                  Ramesh Reddy Master

                  How does your data source URL connection look like in standalone-teiid.xml?

                  • 6. Re: Read .dbf format files
                    Marco Ardito Master

                    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>

                    • 7. Re: Read .dbf format files
                      Ramesh Reddy Master

                      Also try setting the "importer.useQualifiedName" to "false". For some reason the fully qualified name is being used when you access the table.

                      • 8. Re: Read .dbf format files
                        Marco Ardito Master

                        YES! That was the reason... thank you for your assistance, again.

                        Another little gem learned...

                         

                        Marco

                        • 9. Re: Read .dbf format files
                          Marco Ardito Master

                          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

                          • 10. Re: Read .dbf format files
                            Marco Ardito Master

                            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

                            • 11. Re: Read .dbf format files
                              Ramesh Reddy Master

                              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
                              • 12. Re: Read .dbf format files
                                Marco Ardito Master

                                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