Re: Connecting to a legacy database (FoxPro)

Tue, 18 Mar 2008 10:52:47 -0700 

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 <[EMAIL PROTECTED]>:

 

Hi
   I try to use HXTT DBF ODBC but I found error as follows:

2008-03-18 16:22:01,765 (main) [       DatabaseUtil.java:351:ERROR]
Could not create table [HXTT]: SQL Exception while executing the
following:
CREATE TABLE HXTT (ACCID C(20) CONSTRAINT NOT NULL, ITEMID C(10), DEPRE

N(18,2), LAST_UPDATED_STAMP T, LAST_UPDATED_TX_STAMP T, 
CREATED_STAMP T,

CREATED_TX_STAMP T, CONSTRAINT PK_HXTT PRIMARY KEY (ACCID))
Error was:* java.sql.SQLException: Syntax error:  Stopped parse at C*

2008-03-18 16:22:01,781 (main) [       DatabaseUtil.java:2747:ERROR]

Could not create declared indices for entity [hxtt]: SQL Exception 
while

executing the following:
CREATE INDEX HXTT_TXSTMP ON HXTT (LAST_UPDATED_TX_STAMP)
Error was: java.sql.SQLException: Table C:\data\HXTT.DBF doesn't exist.

2008-03-18 16:22:01,781 (main) [       DatabaseUtil.java:2747:ERROR]

Could not create declared indices for entity [hxtt]: SQL Exception 
while

executing the following:
CREATE INDEX HXTT_TXCRTS ON HXTT (CREATED_TX_STAMP)
Error was: java.sql.SQLException: Table C:\data\HXTT.DBF doesn't exist.


And I also try VFPODBC. I also got error as follows:

DatabaseUtil.java:351:ERROR] Could not create table [ODBCFOXPRO]: SQL
Exception while executing the following:
CREATE TABLE ODBCFOXPRO (ACCID C(20) CONSTRAINT NOT NULL, ITEMID C(10),
DEPRE N(18,2), LAST_UPDATED_STAMP T, LAST_UPDATED_TX_STAMP T,
CREATED_STAMP T, CREATED_TX_STAMP T, CONSTRAINT PK_ODBCFOXPRO PRIMARY
KEY (ACCID))
Error was: *java.sql.SQLException: [Microsoft][ODBC Visual FoxPro
Driver]Syntax error.*

2008-03-18 16:27:33,562 (main) [       DatabaseUtil.java:2747:ERROR]

Could not create declared indices for entity [odbcfoxpro]: SQL 
Exception

while executing the following:
CREATE INDEX ODBCFOXPRO_TXSTMP ON ODBCFOXPRO (LAST_UPDATED_TX_STAMP)
Error was: *java.sql.SQLException: [Microsoft][ODBC Visual FoxPro
Driver]Syntax error.*

2008-03-18 16:27:33,562 (main) [       DatabaseUtil.java:2747:ERROR]

Could not create declared indices for entity [odbcfoxpro]: SQL 
Exception

while executing the following:
CREATE INDEX ODBCFOXPRO_TXCRTS ON ODBCFOXPRO (CREATED_TX_STAMP)
Error was: *java.sql.SQLException: [Microsoft][ODBC Visual FoxPro
Driver]Syntax error.*

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

Please suggest me.

Thanks in advance,
Nattanicha


Nattanicha Rittammanart wrote:

   

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

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

// This example will schedule a job to run now.
Map context = UtilMisc.toMap("message","This is a test.");
try {
long startTime = (new Date()).getTime();
dispatcher.schedule("testScv", context, startTime);
}
catch (GenericServiceException e) {
e.printStackTrace();
}

// This example will schedule a service to run now and repeat once
every 5 seconds a total of 10 times.
Map context = UtilMisc.toMap("message","This is a test.");
try {
long startTime = (new Date()).getTime();
int frequency = RecurrenceRule.SECONDLY;
int interval = 5;
int count = 10;
dispatcher.schedule("testScv", context, startTime, frequency,
interval, count);
}
catch (GenericServiceException e) {
e.printStackTrace();
}

I got this from
http://ofbiz.apache.org/docs/services.html#ServiceEngine. Hope this

      

help.

   

I also would like to ask you about how to set ODBC. I saw from your
code that you use sun.jdbc.odbc.JdbcOdbcDriver. I need to use it also
but I cannot connect it with DBF file. Could you please describe me
how to do this in details?

Thanks in advance,
Nattanicha


Bruno Busco wrote:

     

Now i can access the Visual Fox Pro database from within ofbiz (using
a demo
version of the jdbc driver from http://www.hxtt.com).
I have defined a new entity and i can browse the VFP table using the
OFBiz
entity manager. That's cool!

In order to have the OFBiz regularly updated from the external
database I

should now write a service that transfer information from the 
external

entities to the product and price in OFBiz, am i correct?

Could someone please indicate where i can find some similar tasks in
OFBiz
where i could start from?
I mean:
- how to write a task or service to transfer information between
entities in
OFBiz?
- how to schedule to have this task run automatically.

Many thanks in advance.
-Bruno


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


       

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

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


         

I have created it now with this content:

<fieldtypemodel 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";

        xsi:noNamespaceSchemaLocation="
http://ofbiz.apache.org/dtds/fieldtypemodel.xsd";>

  <!-- ===================== field-type-def ==================== 
-->

    <!-- General Types -->

    <field-type-def type="blob" sql-type="C" 
java-type="java.sql.Blob

"></field-type-def>

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

    <field-type-def type="date" sql-type="D" 
java-type="java.sql.Date

"></field-type-def>

    <field-type-def type="time" sql-type="T" 
java-type="java.sql.Time

"></field-type-def>

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

java-type="Long"><validate method="isSignedLong" 
/></field-type-def>


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

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

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

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

java-type="String"><validate method="isNotEmpty" 
/></field-type-def>

    <field-type-def type="id-long-ne" sql-type="C(60)"

java-type="String"><validate method="isNotEmpty" 
/></field-type-def>

    <field-type-def type="id-vlong-ne" sql-type="C(250)"

java-type="String"><validate method="isNotEmpty" 
/></field-type-def>

    <field-type-def type="tel-number" sql-type="C(60)"
java-type="String"><validate method="isInternationalPhoneNumber"
/></field-type-def>
</fieldtypemodel>

but now ofbiz aborts completely. The last messages i can see is:
2008-03-15 17:54:50,265 (main) [    ModelViewEntity.java:468:WARN ]
Conversion for complex-alias needs to be implemented for cache and
in-memory

eval stuff to work correctly, will not work for alias: 
statusDelay of

view-entity ExampleStatusDetail
2008-03-15 17:54:50,390 (main) [    ModelViewEntity.java:626:INFO ]
Throwing out field alias in view entity InventoryItemAndDetail
because one
already exists with the alias name [unitCost] and field name
[IID(InventoryItemDetail).unitCost], existing field name is
[II.unitCost
]
2008-03-15 17:54:50,406 (main) [    ModelViewEntity.java:468:WARN ]
Conversion for complex-alias needs to be implemented for cache and
in-memory
eval stuff to work correctly, will not work for alias:
quantityOrdered of
view-entity OrderItemQuantityReportGroupByItem
2008-03-15 17:54:50,406 (main) [    ModelViewEntity.java:468:WARN ]
Conversion for complex-alias needs to be implemented for cache and
in-memory
eval stuff to work correctly, will not work for alias: quantityOpen

            

of

   

view-entity OrderItemQuantityReportGroupByItem
2008-03-15 17:54:50,406 (main) [    ModelViewEntity.java:468:WARN ]
Conversion for complex-alias needs to be implemented for cache and
in-memory
eval stuff to work correctly, will not work for alias:
quantityOrdered of
view-entity OrderItemQuantityReportGroupByProduct
2008-03-15 17:54:50,406 (main) [    ModelViewEntity.java:468:WARN ]
Conversion for complex-alias needs to be implemented for cache and
in-memory
eval stuff to work correctly, will not work for alias: quantityOpen

            

of

   

view-entity OrderItemQuantityReportGroupByProduct
2008-03-15 17:54:50,421 (main) [    ModelViewEntity.java:468:WARN ]
Conversion for complex-alias needs to be implemented for cache and
in-memory
eval stuff to work correctly, will not work for alias:
quantityOrdered of
view-entity OrderItemAndShipGrpInvResAndItemSum
2008-03-15 17:54:50,421 (main) [    ModelViewEntity.java:468:WARN ]
Conversion for complex-alias needs to be implemented for cache and
in-memory
eval stuff to work correctly, will not work for alias:
totQuantityAvailable
of view-entity OrderItemAndShipGrpInvResAndItemSum
2008-03-15 17:54:50,546 (main) [        ModelReader.java:385:INFO ]
FINISHED LOADING ENTITIES - ALL FILES; #Entities=766

            

#ViewEntities=192

   

#Fields=7915 #Relationships=2640 #AutoRelationships=1947
2008-03-15 17:54:50,734 (main) [   GenericDelegator.java:148:INFO ]
Doing entity definition check...
2008-03-15 17:54:50,734 (main) [ ModelEntityChecker.java:500:INFO ]
[initReservedWords] array length=1023






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


           

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

Jacques

From: "Bruno Busco" <[EMAIL PROTECTED]>



             

I am working on Win XP, i have the FoxPro database and have
installed


                

the


             

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

                

odbc

   

driver


             

works well.

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


                

with the


             

following:

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


                

org.ofbiz.entity.datasource.GenericHelperDAO"


             

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

jndi-name="java:/MySqlDataSource" 
isolation-level="Serializable"/>



                

-->


             

   </datasource>

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

                

:1288:ERROR]

   

Error


             

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


                

Column not


             

found

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

                

case

   

how can


             

i get a Visual FoxPro fieldtype file?)

Thank you
- Bruno



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


               

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


                  

legacy


             

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

                  

of

   

once a


             

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

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

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

                  

start

   

from?


             

Many thanks for you suggestions!

Bruno



                  


__________ NOD32 2953 (20080317) Information __________

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



        

      __________ NOD32 2954 (20080318) Information __________
      This message was checked by NOD32 antivirus system.
http://www.eset.com




      

    




__________ 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




























					Reply via email to