Hello,
Having seen there are a few Postgres users out there it may be
interesting to comment a problem we had here.
First of all the software versions we are using are the following:
PostgreSQL 7.2.1
OJB 1.0.rc.5
On a Debian Linux platform.
The problem is related to sequences. When we configure OJB to assign
unique values for primary keys and we perform a query like this one:
"select rows from our_table" (through OJBs ODMG implementation)
the correct results are shown but a new row is INSERTED in the table
being this row a copy of the first row (id=0) with a new id.
Now, if we perform a the following query:
"select rows from our_table where id=0" (always through OJBs ODMG
implementation)
Instead of getting the row with id=0, a new row with a new ID is shown.
This row, which is an exact copy of the row we wanted with a new id, is
also inserted by OJB in our table.
If we dont perform querys which include the ID=0 row as a result
everything works fine. We really do not use this OJB feature and we
discovered this testing. This bug caused all our application to die
because in our original table definition one of the columns (not the ID
column) was UNIQUE and OJB tried to insert a row with this column
duplicated.
I'll give out here the configuration details for those interested, if
you need more details just ask for them.
----begining repository_user.xml-----
<class-descriptor
class="Product"
table="PRODUCT"
>
<field-descriptor
name="id"
column="ID"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
/>
<field-descriptor
name="name"
column="NAME"
jdbc-type="VARCHAR"
/>
<field-descriptor
name="price"
column="PRICE"
jdbc-type="DOUBLE"
/>
<field-descriptor
name="stock"
column="STOCK"
jdbc-type="INTEGER"
/>
</class-descriptor>
----end repository_user.xml----
----begining repository_database.xml----
<jdbc-connection-descriptor
jcd-alias="default"
default-connection="true"
platform="PostgreSQL"
jdbc-level="2.0"
driver="org.postgresql.Driver"
protocol="jdbc"
subprotocol="postgresql"
dbalias="//localhost:5432/ojbtutor"
username="postgres"
password=""
>
</jdbc-connection-descriptor>
----end repository_database.xml----
----begining Product.java----
/**
* represents product objects in the tutorial system
*
* (taken from OJB tutorial)
*/
public class Product
{
private Double price; /**price per item**/
private Integer stock;/** stock of currently available items*/
private String name; /** product name*/
private Integer id; /**artificial primary key atribute*/
/**getters an setters not shown**/
}
----end Product.java----
----begining ODMGExample.java----
import org.odmg.DList;
import org.odmg.Database;
import org.odmg.Implementation;
import org.odmg.OQLQuery;
import org.odmg.Transaction;
import org.apache.ojb.odmg.OJB;
import org.apache.ojb.odmg.TransactionExt;
/**
* ODMG API Usage Examples for the ODMG Tutorial
*
* @version
*/
public class ODMGExample
{
private Implementation impl = null;
private Database db = null;
public ODMGExample() {
impl = OJB.getInstance();
db = impl.newDatabase();
// Open database
try {
db.open("default", Database.OPEN_READ_WRITE);
} catch(Exception ex) {
ex.printStackTrace();
}
}
public void productList()
{
Transaction tx = impl.newTransaction();
try {
tx.begin();
OQLQuery query = impl.newOQLQuery();
//query.create("select products from " +
Product.class.getName());
query.create("select products from " + Product.class.getName());
//query.create("select products from " +
Product.class.getName() + " where id = 0");
DList results = (DList) query.execute();
tx.commit();
java.util.Iterator iter = results.iterator();
while( iter.hasNext() ){
Product product = (Product) iter.next();
System.out.println(product.getId() + "\t" +
product.getName());
}
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
public void logout()
{
try {
db.close();
} catch(Exception ex) {
ex.printStackTrace();
}
}
}
----end ODMGExample.java----
----begining Tutor.java----
public class Tutor
{
public static void main(String[] args) throws Exception
{
ODMGExample odmg = new ODMGExample();
odmg.productList();
odmg.logout();
}
}
----end Tutor.java----
----begining table.sql----
CREATE TABLE product(
id SERIAL PRIMARY KEY,
name VARCHAR(50),
price DECIMAL(12,5),
stock INTEGER
);
INSERT INTO product (id,price,name,stock) VALUES (0, 5.4, 'Conflictive
Product', 3);
INSERT INTO product (id,price,name,stock) VALUES (1, 5.99, 'video', 9);
INSERT INTO product (id,price,name,stock) VALUES (2, 8.43, 'car', 400);
INSERT INTO product (id,price,name,stock) VALUES (3, 25.3, 'house', 8);
INSERT INTO product (id,price,name,stock) VALUES (4, 3.8, 'computer',
3000);
INSERT INTO product (id,price,name,stock) VALUES (5, 87.46, 'book',
100);
INSERT INTO product (id,price,name,stock) VALUES (6, 9.4, 'printer', 4);
INSERT INTO product (id,price,name,stock) VALUES (7, 7.29, 'television',
6);
INSERT INTO product (id,price,name,stock) VALUES (8, 20.3, 'window',
10);
----end table.sql----
Greets,
Martin Ivan Levi
Universitat Politecnica de Catalunya
Centre Tecnologic de Transferencia de Calor
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
