Re: Connecting to a legacy database (FoxPro)

Tue, 18 Mar 2008 02:32:30 -0700 

Hi
  I try to use HXTT DBF ODBC but I found error as follows:
2008-03-18 16:22:01,765 (main) [ DatabaseUtil.java:351:ERROR] Could not create table [HXTT]: SQL Exception while executing the following: CREATE TABLE HXTT (ACCID C(20) CONSTRAINT NOT NULL, ITEMID C(10), DEPRE N(18,2), LAST_UPDATED_STAMP T, LAST_UPDATED_TX_STAMP T, CREATED_STAMP T, CREATED_TX_STAMP T, CONSTRAINT PK_HXTT PRIMARY KEY (ACCID)) 
Error was:* java.sql.SQLException: Syntax error:  Stopped parse at C*
2008-03-18 16:22:01,781 (main) [ DatabaseUtil.java:2747:ERROR] Could not create declared indices for entity [hxtt]: SQL Exception while executing the following: 
CREATE INDEX HXTT_TXSTMP ON HXTT (LAST_UPDATED_TX_STAMP)
Error was: java.sql.SQLException: Table C:\data\HXTT.DBF doesn't exist.
2008-03-18 16:22:01,781 (main) [ DatabaseUtil.java:2747:ERROR] Could not create declared indices for entity [hxtt]: SQL Exception while executing the following: 
CREATE INDEX HXTT_TXCRTS ON HXTT (CREATED_TX_STAMP)
Error was: java.sql.SQLException: Table C:\data\HXTT.DBF doesn't exist.


And I also try VFPODBC. I also got error as follows:
DatabaseUtil.java:351:ERROR] Could not create table [ODBCFOXPRO]: SQL Exception while executing the following: CREATE TABLE ODBCFOXPRO (ACCID C(20) CONSTRAINT NOT NULL, ITEMID C(10), DEPRE N(18,2), LAST_UPDATED_STAMP T, LAST_UPDATED_TX_STAMP T, CREATED_STAMP T, CREATED_TX_STAMP T, CONSTRAINT PK_ODBCFOXPRO PRIMARY KEY (ACCID)) Error was: *java.sql.SQLException: [Microsoft][ODBC Visual FoxPro Driver]Syntax error.*
2008-03-18 16:27:33,562 (main) [ DatabaseUtil.java:2747:ERROR] Could not create declared indices for entity [odbcfoxpro]: SQL Exception while executing the following: 
CREATE INDEX ODBCFOXPRO_TXSTMP ON ODBCFOXPRO (LAST_UPDATED_TX_STAMP)
Error was: *java.sql.SQLException: [Microsoft][ODBC Visual FoxPro Driver]Syntax error.*
2008-03-18 16:27:33,562 (main) [ DatabaseUtil.java:2747:ERROR] Could not create declared indices for entity [odbcfoxpro]: SQL Exception while executing the following: 
CREATE INDEX ODBCFOXPRO_TXCRTS ON ODBCFOXPRO (CREATED_TX_STAMP)
Error was: *java.sql.SQLException: [Microsoft][ODBC Visual FoxPro Driver]Syntax error.* 

I would like to know:
1. Am I correct to define DBF type? I follow fieldtypevisualfoxpro.xml of Bruno. 
2. How can I do to connect foxpro with OFBiz?
3. Why both 2 drivers got syntax errors?

Please suggest me.

Thanks in advance,
Nattanicha

Nattanicha Rittammanart wrote:

Hi, Bruno
I'm also develop the application on OFBiz by connecting with Foxpro. Did you use "webtools"? In there you can schedule task depend on your need. And I also found some code relate to scheduling as follows: 

*To schedule a service to run at a later time or to repeat use this:*

// This example will schedule a job to run now.
Map context = UtilMisc.toMap("message","This is a test.");
try {
long startTime = (new Date()).getTime();
dispatcher.schedule("testScv", context, startTime);
}
catch (GenericServiceException e) {
e.printStackTrace();
}
// This example will schedule a service to run now and repeat once every 5 seconds a total of 10 times. 
Map context = UtilMisc.toMap("message","This is a test.");
try {
long startTime = (new Date()).getTime();
int frequency = RecurrenceRule.SECONDLY;
int interval = 5;
int count = 10;
dispatcher.schedule("testScv", context, startTime, frequency, interval, count); 
}
catch (GenericServiceException e) {
e.printStackTrace();
}
I got this from http://ofbiz.apache.org/docs/services.html#ServiceEngine. Hope this help.
I also would like to ask you about how to set ODBC. I saw from your code that you use sun.jdbc.odbc.JdbcOdbcDriver. I need to use it also but I cannot connect it with DBF file. Could you please describe me how to do this in details? 

Thanks in advance,
Nattanicha


Bruno Busco wrote:
Now i can access the Visual Fox Pro database from within ofbiz (using a demo 
version of the jdbc driver from http://www.hxtt.com).
I have defined a new entity and i can browse the VFP table using the OFBiz 
entity manager. That's cool!
In order to have the OFBiz regularly updated from the external database I 
should now write a service that transfer information from the external
entities to the product and price in OFBiz, am i correct?
Could someone please indicate where i can find some similar tasks in OFBiz 
where i could start from?
I mean:
- how to write a task or service to transfer information between entities in 
OFBiz?
- how to schedule to have this task run automatically.

Many thanks in advance.
-Bruno


2008/3/15, Bruno Busco <[EMAIL PROTECTED]>:

oops,
i found the problem. I forgot to include the line:
    <field-type name="visualfoxpro" loader="fieldfile" location="
fieldtypevisualfoxpro.xml"/>
in entityengine.xml. Sorry.

2008/3/15, Bruno Busco <[EMAIL PROTECTED]>:

I have created it now with this content:
<fieldtypemodel xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
        xsi:noNamespaceSchemaLocation="
http://ofbiz.apache.org/dtds/fieldtypemodel.xsd";>
  <!-- ===================== field-type-def ==================== -->
    <!-- General Types -->
    <field-type-def type="blob" sql-type="C" java-type="java.sql.Blob
"></field-type-def>

    <field-type-def type="date-time" sql-type="T" java-type="
java.sql.Timestamp"></field-type-def>
    <field-type-def type="date" sql-type="D" java-type="java.sql.Date
"></field-type-def>
    <field-type-def type="time" sql-type="T" java-type="java.sql.Time
"></field-type-def>

    <field-type-def type="currency-amount" sql-type="N(18,2)"
java-type="Double"><validate method="isSignedDouble" /></field-type-def> 
    <field-type-def type="currency-precise" sql-type="N(18,3)"
java-type="Double"><validate method="isSignedDouble" /></field-type-def> 
    <field-type-def type="floating-point" sql-type="F"
java-type="Double"><validate method="isSignedDouble" /></field-type-def> 
    <field-type-def type="numeric" sql-type="I"
java-type="Long"><validate method="isSignedLong" /></field-type-def>

    <field-type-def type="id" sql-type="C(20)"
java-type="String"></field-type-def>
    <field-type-def type="id-long" sql-type="C(60)"
java-type="String"></field-type-def>
    <field-type-def type="id-vlong" sql-type="C(250)"
java-type="String"></field-type-def>

    <field-type-def type="indicator" sql-type="C(1)"
java-type="String"></field-type-def>
    <field-type-def type="very-short" sql-type="C(10)"
java-type="String"></field-type-def>
    <field-type-def type="short-varchar" sql-type="C(60)"
java-type="String"></field-type-def>
    <field-type-def type="long-varchar" sql-type="C(255)"
java-type="String"></field-type-def>
    <field-type-def type="very-long" sql-type="TEXT"
java-type="String"></field-type-def>

    <field-type-def type="comment" sql-type="C(255)"
java-type="String"></field-type-def>
    <field-type-def type="description" sql-type="C(255)"
java-type="String"></field-type-def>
    <field-type-def type="name" sql-type="C(100)"
java-type="String"></field-type-def>
    <field-type-def type="value" sql-type="C(255)"
java-type="String"></field-type-def>

    <!-- Specialized Types -->
    <field-type-def type="credit-card-number" sql-type="C(255)"
java-type="String"><validate method="isAnyCard" /></field-type-def>
    <field-type-def type="credit-card-date" sql-type="C(20)"
java-type="String"><validate method="isDateAfterToday" /></field-type-def> 
    <field-type-def type="email" sql-type="C(255)"
java-type="String"><validate method="isEmail" /></field-type-def>
    <field-type-def type="url" sql-type="C(255)"
java-type="String"></field-type-def>
    <field-type-def type="id-ne" sql-type="C(20)"
java-type="String"><validate method="isNotEmpty" /></field-type-def>
    <field-type-def type="id-long-ne" sql-type="C(60)"
java-type="String"><validate method="isNotEmpty" /></field-type-def>
    <field-type-def type="id-vlong-ne" sql-type="C(250)"
java-type="String"><validate method="isNotEmpty" /></field-type-def>
    <field-type-def type="tel-number" sql-type="C(60)"
java-type="String"><validate method="isInternationalPhoneNumber"
/></field-type-def>
</fieldtypemodel>

but now ofbiz aborts completely. The last messages i can see is:
2008-03-15 17:54:50,265 (main) [    ModelViewEntity.java:468:WARN ]
Conversion for complex-alias needs to be implemented for cache and in-memory 
eval stuff to work correctly, will not work for alias: statusDelay of
view-entity ExampleStatusDetail
2008-03-15 17:54:50,390 (main) [    ModelViewEntity.java:626:INFO ]
Throwing out field alias in view entity InventoryItemAndDetail because one 
already exists with the alias name [unitCost] and field name
[IID(InventoryItemDetail).unitCost], existing field name is [II.unitCost 
]
2008-03-15 17:54:50,406 (main) [    ModelViewEntity.java:468:WARN ]
Conversion for complex-alias needs to be implemented for cache and in-memory eval stuff to work correctly, will not work for alias: quantityOrdered of 
view-entity OrderItemQuantityReportGroupByItem
2008-03-15 17:54:50,406 (main) [    ModelViewEntity.java:468:WARN ]
Conversion for complex-alias needs to be implemented for cache and in-memory 
eval stuff to work correctly, will not work for alias: quantityOpen of
view-entity OrderItemQuantityReportGroupByItem
2008-03-15 17:54:50,406 (main) [    ModelViewEntity.java:468:WARN ]
Conversion for complex-alias needs to be implemented for cache and in-memory eval stuff to work correctly, will not work for alias: quantityOrdered of 
view-entity OrderItemQuantityReportGroupByProduct
2008-03-15 17:54:50,406 (main) [    ModelViewEntity.java:468:WARN ]
Conversion for complex-alias needs to be implemented for cache and in-memory 
eval stuff to work correctly, will not work for alias: quantityOpen of
view-entity OrderItemQuantityReportGroupByProduct
2008-03-15 17:54:50,421 (main) [    ModelViewEntity.java:468:WARN ]
Conversion for complex-alias needs to be implemented for cache and in-memory eval stuff to work correctly, will not work for alias: quantityOrdered of 
view-entity OrderItemAndShipGrpInvResAndItemSum
2008-03-15 17:54:50,421 (main) [    ModelViewEntity.java:468:WARN ]
Conversion for complex-alias needs to be implemented for cache and in-memory eval stuff to work correctly, will not work for alias: totQuantityAvailable 
of view-entity OrderItemAndShipGrpInvResAndItemSum
2008-03-15 17:54:50,546 (main) [        ModelReader.java:385:INFO ]
FINISHED LOADING ENTITIES - ALL FILES; #Entities=766 #ViewEntities=192
#Fields=7915 #Relationships=2640 #AutoRelationships=1947
2008-03-15 17:54:50,734 (main) [   GenericDelegator.java:148:INFO ]
Doing entity definition check...
2008-03-15 17:54:50,734 (main) [ ModelEntityChecker.java:500:INFO ]
[initReservedWords] array length=1023






2008/3/15, Jacques Le Roux <[EMAIL PROTECTED]>:

Did you try to create a fieldtypevisualfoxpro.xml file and put
"visualfoxpro" in field-type-name ?

Jacques

From: "Bruno Busco" <[EMAIL PROTECTED]>
I am working on Win XP, i have the FoxPro database and have installed 
the

Visual FoxPro odbc driver.
I can access the FoxPro database from Access so i know that the odbc

driver

works well.

Now i am trying to configure ofbiz to access the FoxPro database

with the

following:

   <datasource name="odbcfoxpro"
           helper-class="

org.ofbiz.entity.datasource.GenericHelperDAO"

           field-type-name="mysql"
           check-on-start="true"
           add-missing-on-start="true"
           check-indices-on-start="true"
           use-foreign-keys="false"
           use-foreign-key-indices="true"
           join-style="ansi-no-parenthesis"
           alias-view-columns="false"
           always-use-constraint-keyword="true">
       <inline-jdbc
               jdbc-driver="sun.jdbc.odbc.JdbcOdbcDriver"
               jdbc-uri="jdbc:odbc:Arca2000Pro"
               jdbc-username=""
               jdbc-password=""
               isolation-level="ReadCommitted"
               pool-minsize="2"
               pool-maxsize="250"/>
       <!-- <jndi-jdbc jndi-server-name="localjndi"
jndi-name="java:/MySqlDataSource" isolation-level="Serializable"/>

-->

   </datasource>

but I get a lot of
2008-03-15 15:27:57,625 (main) [       DatabaseUtil.java:1288:ERROR]

Error

getting column info for column. Error was:java.sql.SQLException:

Column not

found

Can anybody please suggest a solution?
Could it be becouse of the field-type-name="mysql" (but in this case

how can

i get a Visual FoxPro fieldtype file?)

Thank you
- Bruno



2008/3/15, Bruno Busco <[EMAIL PROTECTED]>:

Hi,
i need to make my ofbiz box retrieve products information from a

legacy

FoxPro database.
The data should be done on a regular basis (lets say every night of

once a

week) so it is not just a one time data import.

Googling around i have found this
http://lists.ofbiz.org/pipermail/users/2005-May/007836.html

Is out there any more updated how-to or documentation i could start

from?

Many thanks for you suggestions!

Bruno





__________ NOD32 2953 (20080317) Information __________

This message was checked by NOD32 antivirus system.
http://www.eset.com




__________ NOD32 2954 (20080318) Information __________

This message was checked by NOD32 antivirus system.
http://www.eset.com

Reply via email to