Support for PostgreSQL sequences

[Hannes Wallnoefer]

Anybody using PostgreSQL out there? I just checked in support for
PostgreSQL sequence syntax for ID generation in Helma:

select nextval('sequence_name')

All you have to do is add something the following to the
type.properties of your Prototype:

_idgen = sequence_name

Please let me know if it works. I'm too lazy to install and configure
PostgreSQL just for the testing :-)

hannes

[Julian Tree]

Hmmm... I am having problem add a new record for postgresql

Root/type.properties:
---------------------------------------------------
_children = collection(Company)
_children.accessname = company_id

Company/type.properties:
---------------------------------------------------
_db = filepressdb
_table = company
_id = company_id
_idgen = company_company_id_seq
_name = company_name
creator = creator
modifier = modifier
contact_name = contact_name
company_name = company_name
email = email
company_type = company_type


Root/new.hac
-------------------------------
var c = new Company();
c.company_name = "Blah Blah";
c.company_type = "agency";
root.add(c);


I get following error
java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR:
relation "dual" does not exist
(/helma-1.5.0/apps/filepress/Root/temp.hac#10)
the sql Log gives me this : SELECT 'company_company_id_seq'.nextval FROM dual


if I comment out the _idgen line I get this error
ERROR: column "company_id" is of type integer but expression is of
type character varying
sql Log: INSERT INTO company ( company_id, company_type, creator,
modifier, contact_name, company_name, email, phone_main, phone_fax,
phone_mobile, phone_other, web_site, comments ) VALUES ( ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ? )




Just to add a note, I created Company table using following SQL ,
notice company_id is created using serial, but it is jsut a int4
field. Any ideas


CREATE TABLE company
(
company_id serial NOT NULL,
company_type varchar(10) NOT NULL DEFAULT 'SHOP'::character varying,
creator varchar(20) NOT NULL DEFAULT ''::character varying,
creationtime timestamp NOT NULL DEFAULT '0001-01-01
00:00:00'::timestamp without time zone,
modifier varchar(20) NOT NULL DEFAULT ''::character varying,
modificationtime timestamp NOT NULL DEFAULT '0001-01-01
00:00:00'::timestamp without time zone,
contact_name varchar(100) NOT NULL DEFAULT ''::character varying,
company_name varchar(100) NOT NULL DEFAULT ''::character varying,
email varchar(60) NOT NULL DEFAULT ''::character varying,
phone_main varchar(11) NOT NULL DEFAULT ''::character varying,
phone_fax varchar(11) NOT NULL DEFAULT ''::character varying,
phone_mobile varchar(11) NOT NULL DEFAULT ''::character varying,
phone_other varchar(11) NOT NULL DEFAULT ''::character varying,
web_site varchar(255) NOT NULL DEFAULT ''::character varying,
comments text NOT NULL DEFAULT ''::text,
creation_stamp text NOT NULL DEFAULT ''::text,
CONSTRAINT company_id PRIMARY KEY (company_id)
)

Post by Hannes Wallnoefer
Anybody using PostgreSQL out there? I just checked in support for
select nextval('sequence_name')
All you have to do is add something the following to the
_idgen = sequence_name
Please let me know if it works. I'm too lazy to install and configure
PostgreSQL just for the testing :-)
hannes
_______________________________________________
Helma-user mailing list
http://helma.org/mailman/listinfo/helma-user

[Hannes Wallnoefer]

Finally some Postgresql testing! Great!

Post by Julian Tree
I get following error
relation "dual" does not exist
(/helma-1.5.0/apps/filepress/Root/temp.hac#10)
the sql Log gives me this : SELECT 'company_company_id_seq'.nextval FROM dual

That's weird, because this is the Oracle syntax. What you should get
is "SELECT nextval('sequence_name')".

What is the class name of the JDBC driver you're using? Are you using
the latest Helma 1.5 CVS snapshot?

Post by Julian Tree
if I comment out the _idgen line I get this error
ERROR: column "company_id" is of type integer but expression is of
type character varying

OK, this is to be expected, I'm fixing it now.

hannes

[Julian Tree]

Thanks a Hannes, I meant to test it much sooner, just have being
distracted by another project for a few weeks.


BTW, I just want to clearfiy some thing about PostgreSQL.

Since postgresql is already managing the incrementing of the id, there
is really no need to put the id in the INSERT statement.

So simple EXCLUDE the primary key (or the key that is defined as _id)
in the insert statement will fix this problem. This will also allow
postgres to hand race conditions regarding id generation.

INSERT INTO company ( company_type, creator,
modifier, contact_name, company_name, email, phone_main, phone_fax,
phone_mobile, phone_other, web_site, comments ) VALUES ( ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ? )


If you need to grab the id of the last inserted record then use this
statement instead

SELECT CURRVAL('name_of_seq');


starting with postgresql 8, there is a better way to do get the next id,

SELECT CURRVAL(pg_get_serial_sequence('name_of_table','name_of_column'));


WIth this method the you only have to provide the table and column
name, and the postgresql will figure out the seq_name. This way the
user doesn't have to define the line such as

_idgen = seq_name

however, this only works in postgresql 8 and above, see this article
for more detail
http://sql-info.de/postgresql/notes/postgresql-8.0-serial-functionality.html

Post by Hannes Wallnoefer
Finally some Postgresql testing! Great!

Post by Julian Tree
I get following error
relation "dual" does not exist
(/helma-1.5.0/apps/filepress/Root/temp.hac#10)
the sql Log gives me this : SELECT 'company_company_id_seq'.nextval FROM dual

That's weird, because this is the Oracle syntax. What you should get
is "SELECT nextval('sequence_name')".
What is the class name of the JDBC driver you're using? Are you using
the latest Helma 1.5 CVS snapshot?

Post by Julian Tree
if I comment out the _idgen line I get this error
ERROR: column "company_id" is of type integer but expression is of
type character varying

OK, this is to be expected, I'm fixing it now.
hannes
_______________________________________________
Helma-user mailing list
http://helma.org/mailman/listinfo/helma-user

[Hannes Wallnoefer]

Post by Julian Tree
Since postgresql is already managing the incrementing of the id, there
is really no need to put the id in the INSERT statement.

Nope, automatic ID generation is not going to work with Helma. We do
not support this with any db, neither MySQL nor Oracle. You'll simply
have to define the primary key as integer column, and than let Helma
get the id via nextval(). Sorry for not seeing this right away.

Post by Julian Tree

Post by Hannes Wallnoefer
What is the class name of the JDBC driver you're using? Are you using
the latest Helma 1.5 CVS snapshot?

Could you still answere these two questions, please?

hannes

[Julian Tree]

oh I see now,

I am using postgresql-8.1-407, and last night I tried using the
lastest helma checkedout from csv. I am not getting the "the SELECT
'company_company_id_seq'.nextval FROM dual" error anymore,

I still get the following ERROR: column "company_id" is of type
integer but expression is of
type character varying
sql Log: INSERT INTO company ( company_id, company_type, creator,
modifier, contact_name, company_name, email, phone_main, phone_fax,
phone_mobile, phone_other, web_site, comments ) VALUES ( ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ? )


Thanks

Post by Hannes Wallnoefer

Post by Julian Tree
Since postgresql is already managing the incrementing of the id, there
is really no need to put the id in the INSERT statement.

Nope, automatic ID generation is not going to work with Helma. We do
not support this with any db, neither MySQL nor Oracle. You'll simply
have to define the primary key as integer column, and than let Helma
get the id via nextval(). Sorry for not seeing this right away.

Post by Julian Tree

Post by Hannes Wallnoefer
What is the class name of the JDBC driver you're using? Are you using
the latest Helma 1.5 CVS snapshot?

Could you still answere these two questions, please?
hannes
_______________________________________________
Helma-user mailing list
http://helma.org/mailman/listinfo/helma-user

[Hannes Wallnoefer]

Post by Julian Tree
I am using postgresql-8.1-407, and last night I tried using the
lastest helma checkedout from csv. I am not getting the "the SELECT
'company_company_id_seq'.nextval FROM dual" error anymore,

great!

Post by Julian Tree
I still get the following ERROR: column "company_id" is of type
integer but expression is of
type character varying

If you do a CVS update now and rebuild, that error should be gone,
too. Can you please check?

thanks,
hannes

[Julian Tree]

Ok, it is working now. Thanks.

So it seems that helma is not using any the advance features
postgresql is offering. I assume it is not recommended to use stored
procedure or triggers in combination with helma.

Post by Hannes Wallnoefer

Post by Julian Tree
I am using postgresql-8.1-407, and last night I tried using the
lastest helma checkedout from csv. I am not getting the "the SELECT
'company_company_id_seq'.nextval FROM dual" error anymore,

great!

Post by Julian Tree
I still get the following ERROR: column "company_id" is of type
integer but expression is of
type character varying

If you do a CVS update now and rebuild, that error should be gone,
too. Can you please check?
thanks,
hannes
_______________________________________________
Helma-user mailing list
http://helma.org/mailman/listinfo/helma-user