You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by Bruno Busco <br...@gmail.com> on 2008/03/15 09:08:37 UTC
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)
Posted by Nattanicha Rittammanart <st...@ait.ac.th>.
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 <st...@ait.ac.th>:
>
>> 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 <st...@ait.ac.th>:
>>>>
>>>>
>>>>> 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 <br...@gmail.com>:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>> 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 <br...@gmail.com>:
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>> 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 <ja...@les7arts.com>:
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>> Did you try to create a fieldtypevisualfoxpro.xml file and put
>>>>>>>>>> "visualfoxpro" in field-type-name ?
>>>>>>>>>>
>>>>>>>>>> Jacques
>>>>>>>>>>
>>>>>>>>>> From: "Bruno Busco" <br...@gmail.com>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>> 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 <br...@gmail.com>:
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>> 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)
Posted by Bruno Busco <br...@gmail.com>.
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 <st...@ait.ac.th>:
>
> 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 <st...@ait.ac.th>:
> >>
> >>> 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 <br...@gmail.com>:
> >>>>>
> >>>>>
> >>>>>> 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 <br...@gmail.com>:
> >>>>>>
> >>>>>>
> >>>>>>> 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 <ja...@les7arts.com>:
> >>>>>>>
> >>>>>>>
> >>>>>>>> Did you try to create a fieldtypevisualfoxpro.xml file and put
> >>>>>>>> "visualfoxpro" in field-type-name ?
> >>>>>>>>
> >>>>>>>> Jacques
> >>>>>>>>
> >>>>>>>> From: "Bruno Busco" <br...@gmail.com>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>> 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 <br...@gmail.com>:
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>>> 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)
Posted by Nattanicha Rittammanart <st...@ait.ac.th>.
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 <st...@ait.ac.th>:
>>
>>> 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 <br...@gmail.com>:
>>>>>
>>>>>
>>>>>> 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 <br...@gmail.com>:
>>>>>>
>>>>>>
>>>>>>> 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 <ja...@les7arts.com>:
>>>>>>>
>>>>>>>
>>>>>>>> Did you try to create a fieldtypevisualfoxpro.xml file and put
>>>>>>>> "visualfoxpro" in field-type-name ?
>>>>>>>>
>>>>>>>> Jacques
>>>>>>>>
>>>>>>>> From: "Bruno Busco" <br...@gmail.com>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>> 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 <br...@gmail.com>:
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>> 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)
Posted by Nattanicha Rittammanart <st...@ait.ac.th>.
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 <st...@ait.ac.th>:
>
>> 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 <br...@gmail.com>:
>>>>
>>>>
>>>>> 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 <br...@gmail.com>:
>>>>>
>>>>>
>>>>>> 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 <ja...@les7arts.com>:
>>>>>>
>>>>>>
>>>>>>> Did you try to create a fieldtypevisualfoxpro.xml file and put
>>>>>>> "visualfoxpro" in field-type-name ?
>>>>>>>
>>>>>>> Jacques
>>>>>>>
>>>>>>> From: "Bruno Busco" <br...@gmail.com>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>> 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 <br...@gmail.com>:
>>>>>>>>
>>>>>>>>
>>>>>>>>> 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)
Posted by Bruno Busco <br...@gmail.com>.
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 <st...@ait.ac.th>:
>
> 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 <br...@gmail.com>:
> >>
> >>> 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 <br...@gmail.com>:
> >>>
> >>>> 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 <ja...@les7arts.com>:
> >>>>
> >>>>> Did you try to create a fieldtypevisualfoxpro.xml file and put
> >>>>> "visualfoxpro" in field-type-name ?
> >>>>>
> >>>>> Jacques
> >>>>>
> >>>>> From: "Bruno Busco" <br...@gmail.com>
> >>>>>
> >>>>>
> >>>>>> 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 <br...@gmail.com>:
> >>>>>>
> >>>>>>> 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)
Posted by Nattanicha Rittammanart <st...@ait.ac.th>.
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 <br...@gmail.com>:
>>
>>> 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 <br...@gmail.com>:
>>>
>>>> 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 <ja...@les7arts.com>:
>>>>
>>>>> Did you try to create a fieldtypevisualfoxpro.xml file and put
>>>>> "visualfoxpro" in field-type-name ?
>>>>>
>>>>> Jacques
>>>>>
>>>>> From: "Bruno Busco" <br...@gmail.com>
>>>>>
>>>>>
>>>>>> 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 <br...@gmail.com>:
>>>>>>
>>>>>>> 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)
Posted by Nattanicha Rittammanart <st...@ait.ac.th>.
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 <br...@gmail.com>:
>
>> 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 <br...@gmail.com>:
>>
>>> 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 <ja...@les7arts.com>:
>>>
>>>> Did you try to create a fieldtypevisualfoxpro.xml file and put
>>>> "visualfoxpro" in field-type-name ?
>>>>
>>>> Jacques
>>>>
>>>> From: "Bruno Busco" <br...@gmail.com>
>>>>
>>>>
>>>>> 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 <br...@gmail.com>:
>>>>>
>>>>>> 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)
Posted by Hansen Wang <ha...@gemmall.com>.
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 <br...@gmail.com> 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 <br...@gmail.com>:
> >
> > 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 <br...@gmail.com>:
> > >
> > > 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 <ja...@les7arts.com>:
> > > >
> > > > Did you try to create a fieldtypevisualfoxpro.xml file and put
> > > > "visualfoxpro" in field-type-name ?
> > > >
> > > > Jacques
> > > >
> > > > From: "Bruno Busco" <br...@gmail.com>
> > > >
> > > > >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 <br...@gmail.com>:
> > > > >>
> > > > >> 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)
Posted by Bruno Busco <br...@gmail.com>.
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 <br...@gmail.com>:
>
> 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 <br...@gmail.com>:
> >
> > 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 <ja...@les7arts.com>:
> > >
> > > Did you try to create a fieldtypevisualfoxpro.xml file and put
> > > "visualfoxpro" in field-type-name ?
> > >
> > > Jacques
> > >
> > > From: "Bruno Busco" <br...@gmail.com>
> > >
> > > >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 <br...@gmail.com>:
> > > >>
> > > >> 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)
Posted by Bruno Busco <br...@gmail.com>.
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 <br...@gmail.com>:
>
> 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 <ja...@les7arts.com>:
> >
> > Did you try to create a fieldtypevisualfoxpro.xml file and put
> > "visualfoxpro" in field-type-name ?
> >
> > Jacques
> >
> > From: "Bruno Busco" <br...@gmail.com>
> >
> > >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 <br...@gmail.com>:
> > >>
> > >> 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)
Posted by Bruno Busco <br...@gmail.com>.
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 <ja...@les7arts.com>:
>
> Did you try to create a fieldtypevisualfoxpro.xml file and put
> "visualfoxpro" in field-type-name ?
>
> Jacques
>
> From: "Bruno Busco" <br...@gmail.com>
>
> >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 <br...@gmail.com>:
> >>
> >> 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)
Posted by Jacques Le Roux <ja...@les7arts.com>.
Did you try to create a fieldtypevisualfoxpro.xml file and put "visualfoxpro" in field-type-name ?
Jacques
From: "Bruno Busco" <br...@gmail.com>
>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 <br...@gmail.com>:
>>
>> 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)
Posted by Nattanicha Rittammanart <st...@ait.ac.th>.
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
>
>
Re: Connecting to a legacy database (FoxPro)
Posted by Bruno Busco <br...@gmail.com>.
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)
Posted by Adrian Crum <ad...@hlmksw.com>.
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 <br...@gmail.com>:
>> 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)
Posted by Bruno Busco <br...@gmail.com>.
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 <br...@gmail.com>:
>
> 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
>