Connecting to a legacy database (FoxPro)
Locked
Connecting to a legacy database (FoxPro)
 
|
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 |
Re: Connecting to a legacy database (FoxPro)
|
|
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 <[hidden email]>: > > 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 > |
Re: Connecting to a legacy database (FoxPro)
Administrator
|
Did you try to create a fieldtypevisualfoxpro.xml file and put "visualfoxpro" in field-type-name ?
Jacques From: "Bruno Busco" <[hidden email]> >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 <[hidden email]>: >> >> 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 >> > |
Re: Connecting to a legacy database (FoxPro)
|
|
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 <[hidden email]>: > > Did you try to create a fieldtypevisualfoxpro.xml file and put > "visualfoxpro" in field-type-name ? > > Jacques > > From: "Bruno Busco" <[hidden email]> > > >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 <[hidden email]>: > >> > >> 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 > >> > > > |
Re: Connecting to a legacy database (FoxPro)
|
|
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 <[hidden email]>: > > 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 <[hidden email]>: > > > > Did you try to create a fieldtypevisualfoxpro.xml file and put > > "visualfoxpro" in field-type-name ? > > > > Jacques > > > > From: "Bruno Busco" <[hidden email]> > > > > >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 <[hidden email]>: > > >> > > >> 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 > > >> > > > > > > > |
Re: Connecting to a legacy database (FoxPro)
|
|
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 <[hidden email]>: > > 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 <[hidden email]>: > > > > 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 <[hidden email]>: > > > > > > Did you try to create a fieldtypevisualfoxpro.xml file and put > > > "visualfoxpro" in field-type-name ? > > > > > > Jacques > > > > > > From: "Bruno Busco" <[hidden email]> > > > > > > >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 <[hidden email]>: > > > >> > > > >> 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 > > > >> > > > > > > > > > > > > |
|
|
You can find the scheduled jobs from "webtools" -> "jobs". The related
database tables are JobSandbox, RecurrenceRule and RecurrenceInfo. One of the configured job from seed is "Order Auto-Cancel" which is defined in applications/order/data/OrderScheduledServices.xml. Regards, Hansen On 3/17/08, Bruno Busco <[hidden email]> 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 <[hidden email]>: > > > > 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 <[hidden email]>: > > > > > > 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 <[hidden email]>: > > > > > > > > Did you try to create a fieldtypevisualfoxpro.xml file and put > > > > "visualfoxpro" in field-type-name ? > > > > > > > > Jacques > > > > > > > > From: "Bruno Busco" <[hidden email]> > > > > > > > > >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 <[hidden email]>: > > > > >> > > > > >> 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 > > > > >> > > > > > > > > > > > > > > > > > > |
Re: Connecting to a legacy database (FoxPro)
|
|
In reply to this post by Bruno Busco
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 <[hidden email]>: > >> 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 <[hidden email]>: >> >>> 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 <[hidden email]>: >>> >>>> Did you try to create a fieldtypevisualfoxpro.xml file and put >>>> "visualfoxpro" in field-type-name ? >>>> >>>> Jacques >>>> >>>> From: "Bruno Busco" <[hidden email]> >>>> >>>> >>>>> 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 <[hidden email]>: >>>>> >>>>>> 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 > > |
Re: Connecting to a legacy database (FoxPro)
|
|
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 <[hidden email]>: >> >>> 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 <[hidden email]>: >>> >>>> 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 <[hidden email]>: >>>> >>>>> Did you try to create a fieldtypevisualfoxpro.xml file and put >>>>> "visualfoxpro" in field-type-name ? >>>>> >>>>> Jacques >>>>> >>>>> From: "Bruno Busco" <[hidden email]> >>>>> >>>>> >>>>>> 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 <[hidden email]>: >>>>>> >>>>>>> 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 > > > |
Re: Connecting to a legacy database (FoxPro)
|
|
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 <[hidden email]>: > > 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 <[hidden email]>: > >> > >>> 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 <[hidden email]>: > >>> > >>>> 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 <[hidden email]>: > >>>> > >>>>> Did you try to create a fieldtypevisualfoxpro.xml file and put > >>>>> "visualfoxpro" in field-type-name ? > >>>>> > >>>>> Jacques > >>>>> > >>>>> From: "Bruno Busco" <[hidden email]> > >>>>> > >>>>> > >>>>>> 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 <[hidden email]>: > >>>>>> > >>>>>>> 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 > > > > > > > > |
Re: Connecting to a legacy database (FoxPro)
|
|
Hi Bruno,
Thanks very much. Now I can connect with Foxpro. Actually I tried to the same with your suggestion before I got it but I cannot success because I have problem with column which are auto generated by OFBiz. So, you made me know how to use no-auto-stamp="true". Thanks again so much. ^^ From I can connect to dbf by using HXTT, I have a problem with charactor encoding when I inserted record via webtools. Then I changed to use Visual FoxPro odbc driver and I can solved that problem. I would like to suggest you to use Visual FoxPro odbc driver instead of HXTT because it's free. You can download here http://download.microsoft.com/download/vfoxodbcdriver/Install/6.1/W9XNT4/EN-US/VFPODBC.msi and put in jdbc folder. Please config it the same as config in HXTT below. You just only change something in entityengine.xml to connect with Visual FoxPro odbc driver (you can try this in Squirrel also). You need to have dbf files before connecting them. If you have some problems with using Visual FoxPro odbc driver, feel free to ask me. I will immediately help you if I can. Hope this help, Nattanicha Bruno Busco wrote: > 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 <[hidden email]>: > >> 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 <[hidden email]>: >>>> >>>> >>>>> 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 <[hidden email]>: >>>>> >>>>> >>>>>> 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 <[hidden email]>: >>>>>> >>>>>> >>>>>>> Did you try to create a fieldtypevisualfoxpro.xml file and put >>>>>>> "visualfoxpro" in field-type-name ? >>>>>>> >>>>>>> Jacques >>>>>>> >>>>>>> From: "Bruno Busco" <[hidden email]> >>>>>>> >>>>>>> >>>>>>> >>>>>>>> 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 <[hidden email]>: >>>>>>>> >>>>>>>> >>>>>>>>> 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 >>> >>> >>> >>> >> > > > > __________ NOD32 2956 (20080318) Information __________ > > This message was checked by NOD32 antivirus system. > http://www.eset.com > > |
Re: Connecting to a legacy database (FoxPro)
|
|
Hi Bruno,
Sorry I told you something wrong. For Visual Foxpro odbc driver, you just download and install it on your machine. Then please set odbc and test connect with Squirrel. There are no jar file which is needed to put in lib. Nattanicha Rittammanart wrote: > Hi Bruno, > Thanks very much. Now I can connect with Foxpro. Actually I tried > to the same with your suggestion before I got it but I cannot success > because I have problem with column which are auto generated by OFBiz. > So, you made me know how to use no-auto-stamp="true". Thanks again so > much. ^^ > > From I can connect to dbf by using HXTT, I have a problem with > charactor encoding when I inserted record via webtools. Then I changed > to use Visual FoxPro odbc driver and I can solved that problem. I > would like to suggest you to use Visual FoxPro odbc driver instead of > HXTT because it's free. You can download here > http://download.microsoft.com/download/vfoxodbcdriver/Install/6.1/W9XNT4/EN-US/VFPODBC.msi > and put in jdbc folder. > > Please config it the same as config in HXTT below. You just only > change something in entityengine.xml to connect with Visual FoxPro > odbc driver (you can try this in Squirrel also). You need to have dbf > files before connecting them. > > If you have some problems with using Visual FoxPro odbc driver, feel > free to ask me. I will immediately help you if I can. > > Hope this help, > Nattanicha > > Bruno Busco wrote: >> 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 <[hidden email]>: >> >>> 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 <[hidden email]>: >>>>> >>>>> >>>>>> 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 <[hidden email]>: >>>>>> >>>>>> >>>>>>> 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 <[hidden email]>: >>>>>>> >>>>>>> >>>>>>>> Did you try to create a fieldtypevisualfoxpro.xml file and put >>>>>>>> "visualfoxpro" in field-type-name ? >>>>>>>> >>>>>>>> Jacques >>>>>>>> >>>>>>>> From: "Bruno Busco" <[hidden email]> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>>> 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 <[hidden email]>: >>>>>>>>> >>>>>>>>> >>>>>>>>>> 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 >>>> >>>> >>>> >>>> >>> >> >> >> >> __________ NOD32 2956 (20080318) Information __________ >> >> This message was checked by NOD32 antivirus system. >> http://www.eset.com >> >> > > > __________ NOD32 2956 (20080318) Information __________ > > This message was checked by NOD32 antivirus system. > http://www.eset.com > > > |
Re: Connecting to a legacy database (FoxPro)
|
|
Could you please detail how you use the odbc driver?
Have you defined a DNS for the database in the Windows Control Panel? What is the DB connection URL? -Bruno 2008/3/18, Nattanicha Rittammanart <[hidden email]>: > > Hi Bruno, > > Sorry I told you something wrong. For Visual Foxpro odbc driver, you > just download and install it on your machine. Then please set odbc and > test connect with Squirrel. There are no jar file which is needed to put > in lib. > > > Nattanicha Rittammanart wrote: > > Hi Bruno, > > Thanks very much. Now I can connect with Foxpro. Actually I tried > > to the same with your suggestion before I got it but I cannot success > > because I have problem with column which are auto generated by OFBiz. > > So, you made me know how to use no-auto-stamp="true". Thanks again so > > much. ^^ > > > > From I can connect to dbf by using HXTT, I have a problem with > > charactor encoding when I inserted record via webtools. Then I changed > > to use Visual FoxPro odbc driver and I can solved that problem. I > > would like to suggest you to use Visual FoxPro odbc driver instead of > > HXTT because it's free. You can download here > > > http://download.microsoft.com/download/vfoxodbcdriver/Install/6.1/W9XNT4/EN-US/VFPODBC.msi > > and put in jdbc folder. > > > > Please config it the same as config in HXTT below. You just only > > change something in entityengine.xml to connect with Visual FoxPro > > odbc driver (you can try this in Squirrel also). You need to have dbf > > files before connecting them. > > > > If you have some problems with using Visual FoxPro odbc driver, feel > > free to ask me. I will immediately help you if I can. > > > > Hope this help, > > Nattanicha > > > > Bruno Busco wrote: > >> 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.ziphowever > >> 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 <[hidden email]>: > >> > >>> 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 <[hidden email]>: > >>>>> > >>>>> > >>>>>> 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 <[hidden email]>: > >>>>>> > >>>>>> > >>>>>>> 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 <[hidden email]>: > >>>>>>> > >>>>>>> > >>>>>>>> Did you try to create a fieldtypevisualfoxpro.xml file and put > >>>>>>>> "visualfoxpro" in field-type-name ? > >>>>>>>> > >>>>>>>> Jacques > >>>>>>>> > >>>>>>>> From: "Bruno Busco" <[hidden email]> > >>>>>>>> > >>>>>>>> > >>>>>>>> > >>>>>>>>> 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 <[hidden email]>: > >>>>>>>>> > >>>>>>>>> > >>>>>>>>>> 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 > >>>> > >>>> > >>>> > >>>> > >>> > >> > >> > >> > >> __________ NOD32 2956 (20080318) Information __________ > >> > >> This message was checked by NOD32 antivirus system. > >> http://www.eset.com > >> > >> > > > > > > __________ NOD32 2956 (20080318) Information __________ > > > > This message was checked by NOD32 antivirus system. > > http://www.eset.com > > > > > > > > |
Re: Connecting to a legacy database (FoxPro)
|
|
Hi,
I will decribe steps of how to use ODBC driver. 1. Download Visual Foxpro ODBC driver from the link which I gave you and then install. 2. Set your ODBC in the Windows Control Panel. Go to System DSN and click add button, select Microsoft visual foxpro driver and put your datasource name(I put VFPODBC), choose database type(I choose Free table directory) and browse to the path of your dbf files(I choose C:). 3. Test connection in Squirrel SQL client then I can get JDBC-driver = "sun.jdbc.odbc.JdbcOdbcDriver" and JDBC-uri="jdbc:odbc:VFPODBC". 4. go to entityengine.xml and put the code below |<group-map group-name="org.ofbiz.odbcfoxpro" datasource-name="odbcfoxpro"></group-map>| .. .. |<field-type name="visualfoxpro" loader="fieldfile" location="fieldtypevisualfoxpro.xml"/>| ... ... <datasource name="odbcfoxpro" 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="sun.jdbc.odbc.JdbcOdbcDriver" jdbc-uri="jdbc:odbc:VFPODBC" <!-- ขึ้นกับการตั้งชื่อ ODBC นะคะ อันนี้ ODBC ของเราชื่อ VFPODBC ค่ะ--> jdbc-username="" jdbc-password="" isolation-level="ReadCommitted" pool-minsize="2" pool-maxsize="250"/> </datasource> 5. I put the code to entitygroup.xml as follows |<entity-group group="org.ofbiz.odbcfoxpro" entity="SimpleAcc"></entity-group>| SimpleAcc is my dbf file(simple_acc.dbf) and then this is my entitymodel.xml <entity package-name="org.ofbiz.odbcfoxpro" entity-name="SimpleAcc" no-auto-stamp="true"> <field type="id" name="accid"></field> <field type="short-varchar" name="itemid"></field> <field type="currency-amount" name="depre"/> <prim-key field="accid"></prim-key> </entity> 6. ant run-install, startofbiz.bat and test in webtools. If your can see the table and can work with it, you can finish your work!! Hope this help, Nattanicha Bruno Busco wrote: > Could you please detail how you use the odbc driver? > Have you defined a DNS for the database in the Windows Control Panel? > What is the DB connection URL? > > -Bruno > > 2008/3/18, Nattanicha Rittammanart <[hidden email]>: > >> Hi Bruno, >> >> Sorry I told you something wrong. For Visual Foxpro odbc driver, you >> just download and install it on your machine. Then please set odbc and >> test connect with Squirrel. There are no jar file which is needed to put >> in lib. >> >> >> Nattanicha Rittammanart wrote: >> >>> Hi Bruno, >>> Thanks very much. Now I can connect with Foxpro. Actually I tried >>> to the same with your suggestion before I got it but I cannot success >>> because I have problem with column which are auto generated by OFBiz. >>> So, you made me know how to use no-auto-stamp="true". Thanks again so >>> much. ^^ >>> >>> From I can connect to dbf by using HXTT, I have a problem with >>> charactor encoding when I inserted record via webtools. Then I changed >>> to use Visual FoxPro odbc driver and I can solved that problem. I >>> would like to suggest you to use Visual FoxPro odbc driver instead of >>> HXTT because it's free. You can download here >>> >>> >> http://download.microsoft.com/download/vfoxodbcdriver/Install/6.1/W9XNT4/EN-US/VFPODBC.msi >> >>> and put in jdbc folder. >>> >>> Please config it the same as config in HXTT below. You just only >>> change something in entityengine.xml to connect with Visual FoxPro >>> odbc driver (you can try this in Squirrel also). You need to have dbf >>> files before connecting them. >>> >>> If you have some problems with using Visual FoxPro odbc driver, feel >>> free to ask me. I will immediately help you if I can. >>> >>> Hope this help, >>> Nattanicha >>> >>> Bruno Busco wrote: >>> >>>> 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.ziphowever >>>> 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 <[hidden email]>: >>>> >>>> >>>>> 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 <[hidden email]>: >>>>>>> >>>>>>> >>>>>>> >>>>>>>> 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 <[hidden email]>: >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>>> 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 <[hidden email]>: >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>>> Did you try to create a fieldtypevisualfoxpro.xml file and put >>>>>>>>>> "visualfoxpro" in field-type-name ? >>>>>>>>>> >>>>>>>>>> Jacques >>>>>>>>>> >>>>>>>>>> From: "Bruno Busco" <[hidden email]> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>>> 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 <[hidden email]>: >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>>> 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 >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>> >>>> __________ NOD32 2956 (20080318) Information __________ >>>> >>>> This message was checked by NOD32 antivirus system. >>>> http://www.eset.com >>>> >>>> >>>> >>> __________ NOD32 2956 (20080318) Information __________ >>> >>> This message was checked by NOD32 antivirus system. >>> http://www.eset.com >>> >>> >>> >>> >> > > > > __________ NOD32 2959 (20080319) Information __________ > > This message was checked by NOD32 antivirus system. > http://www.eset.com > > |
Re: Connecting to a legacy database (FoxPro)
|
|
In reply to this post by Bruno Busco
Bruno,
As an alternative to using the FoxPro ODBC driver, you can use a database that supports the FoxPro DBF format: http://www.sybase.com/products/databasemanagement/advantagedatabaseserver -Adrian Bruno Busco wrote: > 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 <[hidden email]>: >> 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 >> > |
Re: Connecting to a legacy database (FoxPro)
|
|
OK,
i can connect OFBiz to the FoxPro DB using the jdbc-odbc bridge + microsoft odbc driver. But: if i put check-on-start="true" in the database tag i get lots of the following errors: 2008-03-19 22:32:57,875 (main) [ DatabaseUtil.java:1288:ERROR] Error getting column info for column. Error was:java.sql.SQLException: Column not found I am using Windows XP. In any case OFBiz is able to access the entity through the Web tools. Adrian, thank you for suggesting the Advantage solution. For the moment i will try to use the free solution of odbc driver. - Bruno |
Re: Connecting to a legacy database (FoxPro)
|
|
Hi Bruno,
Can you access the entity through the webtools if you put check-on-start="false"? For me, I can also access via webtools when I config the same as I told you before. Why do you need to put check-on-start="true"??? Nattanicha > OK, > i can connect OFBiz to the FoxPro DB using the jdbc-odbc bridge + microsoft > odbc driver. > > But: > if i put check-on-start="true" in the database tag i get lots of the > following errors: > > 2008-03-19 22:32:57,875 (main) [ DatabaseUtil.java:1288:ERROR] Error > getting column info for column. Error was:java.sql.SQLException: Column not > found > > I am using Windows XP. > > In any case OFBiz is able to access the entity through the Web tools. > > Adrian, > thank you for suggesting the Advantage solution. For the moment i will try > to use the free solution of odbc driver. > > > - Bruno > > > > __________ NOD32 2960 (20080319) Information __________ > > This message was checked by NOD32 antivirus system. > http://www.eset.com > > |
«
Return to OFBiz - User
|
1 view|%1 views
| Free forum by Nabble | Edit this page |