Nattanicha, I have not used the JDBC-ODBC bridge. I did not manage to make it work. What I have used is the direct JDBC driver for DBF files from HXTT. You can get an evaluation copy here: http://www.hxtt.com/dbf.zip however this is limited to 50 queries and than you need to restart ofbiz.
So i have done the following: 1) Unzipped and copyed the DBF_JDBC30.jar in ofbiz/framework/entity/lib/jdbc folder. 2) I have changed the fieldtype I use like this: ofbiz/framework/entity/fieldtype/fieldtypevisualfoxpro.xml -------------------------------------------------------------------------------- <fieldtypemodel xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xsi:noNamespaceSchemaLocation=" http://ofbiz.apache.org/dtds/fieldtypemodel.xsd";> <!-- ===================== field-type-def ==================== --> <!-- General Types --> <field-type-def type="blob" sql-type="M NOCPTRANS" java-type=" java.sql.Blob"></field-type-def> <field-type-def type="date-time" sql-type="TIMESTAMP" java-type=" java.sql.Timestamp"></field-type-def> <field-type-def type="date" sql-type="TIMESTAMP" java-type=" java.sql.Date"></field-type-def> <field-type-def type="time" sql-type="TIMESTAMP" java-type=" java.sql.Time"></field-type-def> <field-type-def type="currency-amount" sql-type="DOUBLE" java-type="Double"><validate method="isSignedDouble" /></field-type-def> <field-type-def type="currency-precise" sql-type="DOUBLE" java-type="Double"><validate method="isSignedDouble" /></field-type-def> <field-type-def type="floating-point" sql-type="DOUBLE" java-type="Double"><validate method="isSignedDouble" /></field-type-def> <field-type-def type="numeric" sql-type="NUMERIC" java-type="Long"><validate method="isSignedLong" /></field-type-def> <field-type-def type="id" sql-type="VARCHAR(20)" java-type="String"></field-type-def> <field-type-def type="id-long" sql-type="VARCHAR(60)" java-type="String"></field-type-def> <field-type-def type="id-vlong" sql-type="VARCHAR(250)" java-type="String"></field-type-def> <field-type-def type="indicator" sql-type="VARCHAR(1)" java-type="String"></field-type-def> <field-type-def type="very-short" sql-type="VARCHAR(10)" java-type="String"></field-type-def> <field-type-def type="short-varchar" sql-type="VARCHAR(60)" java-type="String"></field-type-def> <field-type-def type="long-varchar" sql-type="VARCHAR(255)" java-type="String"></field-type-def> <field-type-def type="very-long" sql-type="TEXT" java-type="String"></field-type-def> <field-type-def type="comment" sql-type="VARCHAR(255)" java-type="String"></field-type-def> <field-type-def type="description" sql-type="VARCHAR(255)" java-type="String"></field-type-def> <field-type-def type="name" sql-type="VARCHAR(100)" java-type="String"></field-type-def> <field-type-def type="value" sql-type="VARCHAR(255)" java-type="String"></field-type-def> <!-- Specialized Types --> <field-type-def type="credit-card-number" sql-type="VARCHAR(255)" java-type="String"><validate method="isAnyCard" /></field-type-def> <field-type-def type="credit-card-date" sql-type="VARCHAR(20)" java-type="String"><validate method="isDateAfterToday" /></field-type-def> <field-type-def type="email" sql-type="VARCHAR(255)" java-type="String"><validate method="isEmail" /></field-type-def> <field-type-def type="url" sql-type="VARCHAR(255)" java-type="String"></field-type-def> <field-type-def type="id-ne" sql-type="VARCHAR(20)" java-type="String"><validate method="isNotEmpty" /></field-type-def> <field-type-def type="id-long-ne" sql-type="VARCHAR(60)" java-type="String"><validate method="isNotEmpty" /></field-type-def> <field-type-def type="id-vlong-ne" sql-type="VARCHAR(250)" java-type="String"><validate method="isNotEmpty" /></field-type-def> <field-type-def type="tel-number" sql-type="VARCHAR(60)" java-type="String"><validate method="isInternationalPhoneNumber" /></field-type-def> </fieldtypemodel> --------------------------------------- 3) I have defined the following entity in my application entitymodel.xmlfile: <entity entity-name="Articolo" table-name="MAGART" title="Articolo Legacy" no-auto-stamp="true"> <field name="codice" type="id-ne"></field> <field name="descrizione" col-name="DESCRIZION" type="description"></field> <field name="gruppo" col-name="GRUPPO" type="description"></field> <field name="prezzo" col-name="LISTINO9" type="currency-amount"></field> <prim-key field="codice"/> </entity> ------------------------------------------ 4) In the ofbiz/framework/entity/config/entityengine.xml file I have defined the followin datasource: <datasource name="localvisualfoxpro" helper-class="org.ofbiz.entity.datasource.GenericHelperDAO" field-type-name="visualfoxpro" check-on-start="false" add-missing-on-start="false" check-indices-on-start="false" use-foreign-keys="false" use-foreign-key-indices="false" join-style="ansi-no-parenthesis" alias-view-columns="false" always-use-constraint-keyword="false"> <inline-jdbc jdbc-driver="com.hxtt.sql.dbf.DBFDriver" jdbc-uri="jdbc:dbf:///F:/myfoxprotestfolder" jdbc-username="" jdbc-password="" isolation-level="ReadCommitted" pool-minsize="2" pool-maxsize="250"/> </datasource> Please note that you need to specify in the URL the FOLDER name where your dbf files are. An additional suggestion: To check that the JDBC driver and the URL used to connect to the DBF database was OK (indipendently from OFBiz) I have used the Squirrel SQL client. When he was happy with the driver and the url used than I moved to OFBiz. Hope this helps, Bruno 2008/3/18, Nattanicha Rittammanart <[EMAIL PROTECTED]>: > > Hi > I try to use HXTT DBF ODBC but I found error as follows: > > 2008-03-18 16:22:01,765 (main) [ DatabaseUtil.java:351:ERROR] > Could not create table [HXTT]: SQL Exception while executing the > following: > CREATE TABLE HXTT (ACCID C(20) CONSTRAINT NOT NULL, ITEMID C(10), DEPRE > N(18,2), LAST_UPDATED_STAMP T, LAST_UPDATED_TX_STAMP T, CREATED_STAMP T, > CREATED_TX_STAMP T, CONSTRAINT PK_HXTT PRIMARY KEY (ACCID)) > Error was:* java.sql.SQLException: Syntax error: Stopped parse at C* > > 2008-03-18 16:22:01,781 (main) [ DatabaseUtil.java:2747:ERROR] > Could not create declared indices for entity [hxtt]: SQL Exception while > executing the following: > CREATE INDEX HXTT_TXSTMP ON HXTT (LAST_UPDATED_TX_STAMP) > Error was: java.sql.SQLException: Table C:\data\HXTT.DBF doesn't exist. > > 2008-03-18 16:22:01,781 (main) [ DatabaseUtil.java:2747:ERROR] > Could not create declared indices for entity [hxtt]: SQL Exception while > executing the following: > CREATE INDEX HXTT_TXCRTS ON HXTT (CREATED_TX_STAMP) > Error was: java.sql.SQLException: Table C:\data\HXTT.DBF doesn't exist. > > > And I also try VFPODBC. I also got error as follows: > > DatabaseUtil.java:351:ERROR] Could not create table [ODBCFOXPRO]: SQL > Exception while executing the following: > CREATE TABLE ODBCFOXPRO (ACCID C(20) CONSTRAINT NOT NULL, ITEMID C(10), > DEPRE N(18,2), LAST_UPDATED_STAMP T, LAST_UPDATED_TX_STAMP T, > CREATED_STAMP T, CREATED_TX_STAMP T, CONSTRAINT PK_ODBCFOXPRO PRIMARY > KEY (ACCID)) > Error was: *java.sql.SQLException: [Microsoft][ODBC Visual FoxPro > Driver]Syntax error.* > > 2008-03-18 16:27:33,562 (main) [ DatabaseUtil.java:2747:ERROR] > Could not create declared indices for entity [odbcfoxpro]: SQL Exception > while executing the following: > CREATE INDEX ODBCFOXPRO_TXSTMP ON ODBCFOXPRO (LAST_UPDATED_TX_STAMP) > Error was: *java.sql.SQLException: [Microsoft][ODBC Visual FoxPro > Driver]Syntax error.* > > 2008-03-18 16:27:33,562 (main) [ DatabaseUtil.java:2747:ERROR] > Could not create declared indices for entity [odbcfoxpro]: SQL Exception > while executing the following: > CREATE INDEX ODBCFOXPRO_TXCRTS ON ODBCFOXPRO (CREATED_TX_STAMP) > Error was: *java.sql.SQLException: [Microsoft][ODBC Visual FoxPro > Driver]Syntax error.* > > I would like to know: > 1. Am I correct to define DBF type? I follow fieldtypevisualfoxpro.xml > of Bruno. > 2. How can I do to connect foxpro with OFBiz? > 3. Why both 2 drivers got syntax errors? > > Please suggest me. > > Thanks in advance, > Nattanicha > > > Nattanicha Rittammanart wrote: > > Hi, Bruno > > I'm also develop the application on OFBiz by connecting with Foxpro. > > Did you use "webtools"? In there you can schedule task depend on your > > need. And I also found some code relate to scheduling as follows: > > > > *To schedule a service to run at a later time or to repeat use this:* > > > > // This example will schedule a job to run now. > > Map context = UtilMisc.toMap("message","This is a test."); > > try { > > long startTime = (new Date()).getTime(); > > dispatcher.schedule("testScv", context, startTime); > > } > > catch (GenericServiceException e) { > > e.printStackTrace(); > > } > > > > // This example will schedule a service to run now and repeat once > > every 5 seconds a total of 10 times. > > Map context = UtilMisc.toMap("message","This is a test."); > > try { > > long startTime = (new Date()).getTime(); > > int frequency = RecurrenceRule.SECONDLY; > > int interval = 5; > > int count = 10; > > dispatcher.schedule("testScv", context, startTime, frequency, > > interval, count); > > } > > catch (GenericServiceException e) { > > e.printStackTrace(); > > } > > > > I got this from > > http://ofbiz.apache.org/docs/services.html#ServiceEngine. Hope this > help. > > > > I also would like to ask you about how to set ODBC. I saw from your > > code that you use sun.jdbc.odbc.JdbcOdbcDriver. I need to use it also > > but I cannot connect it with DBF file. Could you please describe me > > how to do this in details? > > > > Thanks in advance, > > Nattanicha > > > > > > Bruno Busco wrote: > >> Now i can access the Visual Fox Pro database from within ofbiz (using > >> a demo > >> version of the jdbc driver from http://www.hxtt.com). > >> I have defined a new entity and i can browse the VFP table using the > >> OFBiz > >> entity manager. That's cool! > >> > >> In order to have the OFBiz regularly updated from the external > >> database I > >> should now write a service that transfer information from the external > >> entities to the product and price in OFBiz, am i correct? > >> > >> Could someone please indicate where i can find some similar tasks in > >> OFBiz > >> where i could start from? > >> I mean: > >> - how to write a task or service to transfer information between > >> entities in > >> OFBiz? > >> - how to schedule to have this task run automatically. > >> > >> Many thanks in advance. > >> -Bruno > >> > >> > >> 2008/3/15, Bruno Busco <[EMAIL PROTECTED]>: > >> > >>> oops, > >>> i found the problem. I forgot to include the line: > >>> <field-type name="visualfoxpro" loader="fieldfile" location=" > >>> fieldtypevisualfoxpro.xml"/> > >>> in entityengine.xml. Sorry. > >>> > >>> 2008/3/15, Bruno Busco <[EMAIL PROTECTED]>: > >>> > >>>> I have created it now with this content: > >>>> <fieldtypemodel xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; > >>>> xsi:noNamespaceSchemaLocation=" > >>>> http://ofbiz.apache.org/dtds/fieldtypemodel.xsd";> > >>>> <!-- ===================== field-type-def ==================== --> > >>>> <!-- General Types --> > >>>> <field-type-def type="blob" sql-type="C" java-type="java.sql.Blob > >>>> "></field-type-def> > >>>> > >>>> <field-type-def type="date-time" sql-type="T" java-type=" > >>>> java.sql.Timestamp"></field-type-def> > >>>> <field-type-def type="date" sql-type="D" java-type="java.sql.Date > >>>> "></field-type-def> > >>>> <field-type-def type="time" sql-type="T" java-type="java.sql.Time > >>>> "></field-type-def> > >>>> > >>>> <field-type-def type="currency-amount" sql-type="N(18,2)" > >>>> java-type="Double"><validate method="isSignedDouble" > >>>> /></field-type-def> > >>>> <field-type-def type="currency-precise" sql-type="N(18,3)" > >>>> java-type="Double"><validate method="isSignedDouble" > >>>> /></field-type-def> > >>>> <field-type-def type="floating-point" sql-type="F" > >>>> java-type="Double"><validate method="isSignedDouble" > >>>> /></field-type-def> > >>>> <field-type-def type="numeric" sql-type="I" > >>>> java-type="Long"><validate method="isSignedLong" /></field-type-def> > >>>> > >>>> <field-type-def type="id" sql-type="C(20)" > >>>> java-type="String"></field-type-def> > >>>> <field-type-def type="id-long" sql-type="C(60)" > >>>> java-type="String"></field-type-def> > >>>> <field-type-def type="id-vlong" sql-type="C(250)" > >>>> java-type="String"></field-type-def> > >>>> > >>>> <field-type-def type="indicator" sql-type="C(1)" > >>>> java-type="String"></field-type-def> > >>>> <field-type-def type="very-short" sql-type="C(10)" > >>>> java-type="String"></field-type-def> > >>>> <field-type-def type="short-varchar" sql-type="C(60)" > >>>> java-type="String"></field-type-def> > >>>> <field-type-def type="long-varchar" sql-type="C(255)" > >>>> java-type="String"></field-type-def> > >>>> <field-type-def type="very-long" sql-type="TEXT" > >>>> java-type="String"></field-type-def> > >>>> > >>>> <field-type-def type="comment" sql-type="C(255)" > >>>> java-type="String"></field-type-def> > >>>> <field-type-def type="description" sql-type="C(255)" > >>>> java-type="String"></field-type-def> > >>>> <field-type-def type="name" sql-type="C(100)" > >>>> java-type="String"></field-type-def> > >>>> <field-type-def type="value" sql-type="C(255)" > >>>> java-type="String"></field-type-def> > >>>> > >>>> <!-- Specialized Types --> > >>>> <field-type-def type="credit-card-number" sql-type="C(255)" > >>>> java-type="String"><validate method="isAnyCard" /></field-type-def> > >>>> <field-type-def type="credit-card-date" sql-type="C(20)" > >>>> java-type="String"><validate method="isDateAfterToday" > >>>> /></field-type-def> > >>>> <field-type-def type="email" sql-type="C(255)" > >>>> java-type="String"><validate method="isEmail" /></field-type-def> > >>>> <field-type-def type="url" sql-type="C(255)" > >>>> java-type="String"></field-type-def> > >>>> <field-type-def type="id-ne" sql-type="C(20)" > >>>> java-type="String"><validate method="isNotEmpty" /></field-type-def> > >>>> <field-type-def type="id-long-ne" sql-type="C(60)" > >>>> java-type="String"><validate method="isNotEmpty" /></field-type-def> > >>>> <field-type-def type="id-vlong-ne" sql-type="C(250)" > >>>> java-type="String"><validate method="isNotEmpty" /></field-type-def> > >>>> <field-type-def type="tel-number" sql-type="C(60)" > >>>> java-type="String"><validate method="isInternationalPhoneNumber" > >>>> /></field-type-def> > >>>> </fieldtypemodel> > >>>> > >>>> but now ofbiz aborts completely. The last messages i can see is: > >>>> 2008-03-15 17:54:50,265 (main) [ ModelViewEntity.java:468:WARN ] > >>>> Conversion for complex-alias needs to be implemented for cache and > >>>> in-memory > >>>> eval stuff to work correctly, will not work for alias: statusDelay of > >>>> view-entity ExampleStatusDetail > >>>> 2008-03-15 17:54:50,390 (main) [ ModelViewEntity.java:626:INFO ] > >>>> Throwing out field alias in view entity InventoryItemAndDetail > >>>> because one > >>>> already exists with the alias name [unitCost] and field name > >>>> [IID(InventoryItemDetail).unitCost], existing field name is > >>>> [II.unitCost > >>>> ] > >>>> 2008-03-15 17:54:50,406 (main) [ ModelViewEntity.java:468:WARN ] > >>>> Conversion for complex-alias needs to be implemented for cache and > >>>> in-memory > >>>> eval stuff to work correctly, will not work for alias: > >>>> quantityOrdered of > >>>> view-entity OrderItemQuantityReportGroupByItem > >>>> 2008-03-15 17:54:50,406 (main) [ ModelViewEntity.java:468:WARN ] > >>>> Conversion for complex-alias needs to be implemented for cache and > >>>> in-memory > >>>> eval stuff to work correctly, will not work for alias: quantityOpen > of > >>>> view-entity OrderItemQuantityReportGroupByItem > >>>> 2008-03-15 17:54:50,406 (main) [ ModelViewEntity.java:468:WARN ] > >>>> Conversion for complex-alias needs to be implemented for cache and > >>>> in-memory > >>>> eval stuff to work correctly, will not work for alias: > >>>> quantityOrdered of > >>>> view-entity OrderItemQuantityReportGroupByProduct > >>>> 2008-03-15 17:54:50,406 (main) [ ModelViewEntity.java:468:WARN ] > >>>> Conversion for complex-alias needs to be implemented for cache and > >>>> in-memory > >>>> eval stuff to work correctly, will not work for alias: quantityOpen > of > >>>> view-entity OrderItemQuantityReportGroupByProduct > >>>> 2008-03-15 17:54:50,421 (main) [ ModelViewEntity.java:468:WARN ] > >>>> Conversion for complex-alias needs to be implemented for cache and > >>>> in-memory > >>>> eval stuff to work correctly, will not work for alias: > >>>> quantityOrdered of > >>>> view-entity OrderItemAndShipGrpInvResAndItemSum > >>>> 2008-03-15 17:54:50,421 (main) [ ModelViewEntity.java:468:WARN ] > >>>> Conversion for complex-alias needs to be implemented for cache and > >>>> in-memory > >>>> eval stuff to work correctly, will not work for alias: > >>>> totQuantityAvailable > >>>> of view-entity OrderItemAndShipGrpInvResAndItemSum > >>>> 2008-03-15 17:54:50,546 (main) [ ModelReader.java:385:INFO ] > >>>> FINISHED LOADING ENTITIES - ALL FILES; #Entities=766 > #ViewEntities=192 > >>>> #Fields=7915 #Relationships=2640 #AutoRelationships=1947 > >>>> 2008-03-15 17:54:50,734 (main) [ GenericDelegator.java:148:INFO ] > >>>> Doing entity definition check... > >>>> 2008-03-15 17:54:50,734 (main) [ ModelEntityChecker.java:500:INFO ] > >>>> [initReservedWords] array length=1023 > >>>> > >>>> > >>>> > >>>> > >>>> > >>>> > >>>> 2008/3/15, Jacques Le Roux <[EMAIL PROTECTED]>: > >>>> > >>>>> Did you try to create a fieldtypevisualfoxpro.xml file and put > >>>>> "visualfoxpro" in field-type-name ? > >>>>> > >>>>> Jacques > >>>>> > >>>>> From: "Bruno Busco" <[EMAIL PROTECTED]> > >>>>> > >>>>> > >>>>>> I am working on Win XP, i have the FoxPro database and have > >>>>>> installed > >>>>>> > >>>>> the > >>>>> > >>>>>> Visual FoxPro odbc driver. > >>>>>> I can access the FoxPro database from Access so i know that the > odbc > >>>>>> > >>>>> driver > >>>>> > >>>>>> works well. > >>>>>> > >>>>>> Now i am trying to configure ofbiz to access the FoxPro database > >>>>>> > >>>>> with the > >>>>> > >>>>>> following: > >>>>>> > >>>>>> <datasource name="odbcfoxpro" > >>>>>> helper-class=" > >>>>>> > >>>>> org.ofbiz.entity.datasource.GenericHelperDAO" > >>>>> > >>>>>> field-type-name="mysql" > >>>>>> check-on-start="true" > >>>>>> add-missing-on-start="true" > >>>>>> check-indices-on-start="true" > >>>>>> use-foreign-keys="false" > >>>>>> use-foreign-key-indices="true" > >>>>>> join-style="ansi-no-parenthesis" > >>>>>> alias-view-columns="false" > >>>>>> always-use-constraint-keyword="true"> > >>>>>> <inline-jdbc > >>>>>> jdbc-driver="sun.jdbc.odbc.JdbcOdbcDriver" > >>>>>> jdbc-uri="jdbc:odbc:Arca2000Pro" > >>>>>> jdbc-username="" > >>>>>> jdbc-password="" > >>>>>> isolation-level="ReadCommitted" > >>>>>> pool-minsize="2" > >>>>>> pool-maxsize="250"/> > >>>>>> <!-- <jndi-jdbc jndi-server-name="localjndi" > >>>>>> jndi-name="java:/MySqlDataSource" isolation-level="Serializable"/> > >>>>>> > >>>>> --> > >>>>> > >>>>>> </datasource> > >>>>>> > >>>>>> but I get a lot of > >>>>>> 2008-03-15 15:27:57,625 (main) [ DatabaseUtil.java > :1288:ERROR] > >>>>>> > >>>>> Error > >>>>> > >>>>>> getting column info for column. Error was:java.sql.SQLException: > >>>>>> > >>>>> Column not > >>>>> > >>>>>> found > >>>>>> > >>>>>> Can anybody please suggest a solution? > >>>>>> Could it be becouse of the field-type-name="mysql" (but in this > case > >>>>>> > >>>>> how can > >>>>> > >>>>>> i get a Visual FoxPro fieldtype file?) > >>>>>> > >>>>>> Thank you > >>>>>> - Bruno > >>>>>> > >>>>>> > >>>>>> > >>>>>> 2008/3/15, Bruno Busco <[EMAIL PROTECTED]>: > >>>>>> > >>>>>>> Hi, > >>>>>>> i need to make my ofbiz box retrieve products information from a > >>>>>>> > >>>>> legacy > >>>>> > >>>>>>> FoxPro database. > >>>>>>> The data should be done on a regular basis (lets say every night > of > >>>>>>> > >>>>> once a > >>>>> > >>>>>>> week) so it is not just a one time data import. > >>>>>>> > >>>>>>> Googling around i have found this > >>>>>>> http://lists.ofbiz.org/pipermail/users/2005-May/007836.html > >>>>>>> > >>>>>>> Is out there any more updated how-to or documentation i could > start > >>>>>>> > >>>>> from? > >>>>> > >>>>>>> Many thanks for you suggestions! > >>>>>>> > >>>>>>> Bruno > >>>>>>> > >>>>>>> > >>>> > >> > >> > >> > >> __________ NOD32 2953 (20080317) Information __________ > >> > >> This message was checked by NOD32 antivirus system. > >> http://www.eset.com > >> > >> > > > > > > > __________ NOD32 2954 (20080318) Information __________ > > > > > This message was checked by NOD32 antivirus system. > > http://www.eset.com > > > > > > > >
