I need some help with a problem that we are experiencing. Up until now, we have only been testing with your DBF driver. I have not had any issues recently and it has worked great, but then we had this problem:
There are two applications/drivers that will be accessing the data.
1. Our current ERP system accesses the system and displays and processes data to administration users.
2. The new applications that I've written for our production operations that will use your DBF driver to send and receive data to/from the database tables.
We have 2 tables that each have primary key IDs as integers. One of these tables also has a reference field back to the other table's ID field.
PartEvent
-----------
int ID
varchar eventNotes
... other fields
PartEventData
---------------
int ID
int PartEventRef
varchar dataValue
... other fields
The Following code is for each of these tables:
@Entity
@Table(name = "zPRTEVT")
public class PartEvent implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID", nullable = false)
private Integer id;
@Basic(optional = true)
@Column(name = "NOTES", nullable = true, length = 254)
private String notes;
...
@Entity
@Table(name = "zPTEDATA")
public class PartEventData implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID", nullable = false)
private Integer id;
@Basic(optional = false)
@Column(name = "DATAVALUE", nullable = true, length = 254)
private String dataValue;
@JoinColumn(name = "PEVENTREF", referencedColumnName = "ID", nullable = false)
@ManyToOne(optional = false)
private PartEvent partEventRef;
...
The following code is for persisting new data into these tables:
// Create a new part event record
PartEvent newPartEventRef = new PartEvent();
newPartEventRef.setEventDate(date);
newPartEventRef.setEventTypeRef(partEventTypeRef);
newPartEventRef.setNotes(eventNotes);
newPartEventRef.setPartSNRef(partSNRef);
newPartEventRef.setStation(stationRef);
emgr.getTransaction().begin();
emgr.persist(newPartEventRef);
emgr.getTransaction().commit();
PartEventData eventDataRef = new PartEventData();
eventDataRef.setDataValue(data.getDataValue());
eventDataRef.setPartEventRef(partEventRef);
emgr.getTransaction().begin();
emgr.persist(eventDataRef);
emgr.getTransaction().commit();
Here is the problem:
Our ERP system requires that indexes be varchar, not integers.
Is there any way to have 2 indexes against this primary key ID field? One index be the integer primary key index and the other be the varchar index. I realize this may sound strange and if you need more information, please ask.
Thank you for any help that you are able to offer.
|
>Our ERP system requires that indexes be varchar, not integers.
It's visiting that table through ODBC? If both system are modifing data at the same time, you need lockType=VFP connection property.
>Is there any way to have 2 indexes against this primary key ID field? One index be the integer primary key index and the other be the varchar index.
An ID index expression can't be integer type and varchar type at the same time, you can use ID and STR(ID) as index expression.
Maybe one solution is to modify your ID column as a varchar type, since HXTT DBF allow varchar and integer convertion automatically.
|
|
The problem is that the Id field was not autoincrementing when even a second separate character index was pointing to the field. The entry was entered but with Id =0.
|
>The problem is that the Id field was not autoincrementing when even a second separate character index was pointing to the field.
Because An int type is not autoincrementing, but you can use int AUTO_INCREMENT type if your dabase format is VFP. For instance, backup yourTable first, then run "alter table yourTable modify id int AUTO_INCREMENT;"
>@GeneratedValue(strategy = GenerationType.IDENTITY)
You can try @GeneratedValue(strategy = GenerationType.SEQUENCE) too.
|
But it was autoincrementing when the field is set to integer, but not when there is an index pointing to it set as a varchar.
I want to keep strategy = GenerationType.IDENTITY.
I believe this is what is correct for what I'm doing.
|
>But it was autoincrementing when the field is set to integer, but not when there is an index pointing to it set as a varchar.
Which set an int AUTO_INCREMENT type as a varchar type?
|
I might be able to explain better with pictures, could I send you an email with attachments? What email address should I use?
The ID field was autoincrementing properly when the field was an integer and if I were to set it to varchar (I assume) it would not autoincrement. With indexes that are varchar pointing to those fields, the ID field stops autoincrementing.
|
|
You can use support @ hxtt.com . If possible, you can send us a database sample.
|
|
My associate (Charles Schilling) is going to send you a sample of the table with the compound indexes.
|
Main
webmaster@hxtt.com