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