PostgreSQL and QT4 automate id problem!

[nnidza]

I am having problem with my database application. I am using a postgreSQL and QT4. I have created a connection in the main.cpp and it works fine.
There are other forms too, but none of them is using an inline function to automaticaly generate the id number to some of the tables.

Here is the structure of the table:

CREATE TABLE "Blood"
(
"GroupID" int4 NOT NULL,
"Group" varchar(3),
CONSTRAINT "Blood_pkey" PRIMARY KEY ("GroupID")
)
WITHOUT OIDS;
ALTER TABLE "Blood" OWNER TO postgres;

Here are the functions I use to insert a new data in the table:

inline int generateId(const QString &table)
{
	QSqlQuery query;
	query.exec(" SELECT MAX(GroupID) FROM " + table);
	if (!query.isActive())
    	cerr << "Error" << endl;
	int id = 0;
	if (query.next())
		id = query.value(0).toInt() + 1;	
	return id; 
 
}
 
void Krv::AddRec()
{
	int row = model->rowCount();
	model->insertRow(row);
	QModelIndex index = model->index(row, Group);
	tableView->setCurrentIndex(index);
	tableView->edit(index);
}
 
void Krv::beforeInsertRec(QSqlRecord &record)
{
	record.setValue("GroupID", generateId(table));
}

inline int generateId(const QString &table)
{
	QSqlQuery query;
	query.exec(" SELECT MAX(GroupID) FROM " + table);
	if (!query.isActive())
    	cerr << "Error" << endl;
	int id = 0;
	if (query.next())
		id = query.value(0).toInt() + 1;	
	return id; 
   		
}

void Krv::AddRec()
{
	int row = model->rowCount();
	model->insertRow(row);
	QModelIndex index = model->index(row, Group);
	tableView->setCurrentIndex(index);
	tableView->edit(index);
}

void Krv::beforeInsertRec(QSqlRecord &record)
{
	record.setValue("GroupID", generateId(table));
}

To copy to clipboard, switch view to plain text mode 

I am constantly getting an error message as the query is not active.
I can delete from the same form, I can insert a first record, but the id wont iterate.
It always got the id=0 value? Can someone help???

[wysota]

The ID should get autogenerated by the database if you pass NULL to it as the primary key value. You shouldn't try to generate the id yourself. Imagine there are two concurrent inserts into the database - manual fetching of the maximum id present in the database doesn't guarantee that something else doesn't use that id in between the time when you do the check and when you actually insert the record. You'd have to use transactions to make sure of that, but this slows up the mechanism significantly (locking the row or making a snapshot of the table, then rolling back the transaction if it fails and repeating the process again and again), so you should avoid such methods as well and rely on internal database mechanisms for generating primary keys. You just have to set up the table correctly so that it knows how to generate keys (for example you can use a trigger for that).

As for the error itself, I suggest you use QSqlError::text() to peek for the nature of the error in the query.

[jacek]

CREATE TABLE "Blood"
(
"GroupID" int4 NOT NULL,
...
)
WITHOUT OIDS;
ALTER TABLE "Blood" OWNER TO postgres;

Try:

CREATE TABLE "Blood"
(
  "GroupID" serial NOT NULL,
  ...
)
...

CREATE TABLE "Blood"
(
  "GroupID" serial NOT NULL,
  ...
)
...

To copy to clipboard, switch view to plain text mode 

and:

void Krv::beforeInsertRec(QSqlRecord &record)
{
	record.setGenerated( "GroupID", false );
}

void Krv::beforeInsertRec(QSqlRecord &record)
{
	record.setGenerated( "GroupID", false );
}

To copy to clipboard, switch view to plain text mode 

This way PostgreSQL will use a sequence to generate GroupIDs in a safe manner.

It always got the id=0 value? Can someone help???

The problem is that you probably don't submit changes to the database, so every time you call generateId() is operates on an empty table, but first make sure that the query is correct.

See QSqlTableModel::setEditStrategy().

[GreyGeek]

CREATE TABLE employee
(
  empid int8 NOT NULL DEFAULT nextval('employee_empid_seq'::regclass),
  empname varchar(30) NOT NULL,
  revid varchar(10),
  passwd varchar(15) NOT NULL DEFAULT 'unknown'::character varying,
....
}

CREATE TABLE employee
(
  empid int8 NOT NULL DEFAULT nextval('employee_empid_seq'::regclass),
  empname varchar(30) NOT NULL,
  revid varchar(10),
  passwd varchar(15) NOT NULL DEFAULT 'unknown'::character varying,
....
}

To copy to clipboard, switch view to plain text mode 

and to generate the empid field value automatically:

CREATE SEQUENCE employee_empid_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1361
  CACHE 1;

CREATE SEQUENCE employee_empid_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1361
  CACHE 1;

To copy to clipboard, switch view to plain text mode 

When you insert a row into the table don't include the id field, it will be supplied automatically by the sequence.

[nnidza]

Ok, it works now.
Wysota thank you for your advice, I'll keep that on my mind when I make some more complex forms...
I didn't understand your solution Jacek. What is serial exactly? A new data type? Sorry I am not so familiar with PostgreSQL.
GreyGeek you solution works! I don't need any transactions now as I am under the PostgreSQL protections? Am I right?

And some of you mentioned trigers. No clue so far... Is it a complex explanation to drop a few lines before I read a tutorial?

Marry Christmas to all of you that celebrates...

[wysota]

And some of you mentioned trigers. No clue so far... Is it a complex explanation to drop a few lines before I read a tutorial?

http://en.wikipedia.org/wiki/Database_trigger

Marry Christmas to all of you that celebrates...

Thanks

[jacek]

I didn't understand your solution Jacek. What is serial exactly? A new data type?

Yes, it's a special data type. It's an equivalent of GreyGeek's solution, only shorter.

http://www.postgresql.org/docs/8.2/s...ATATYPE-SERIAL

[GreyGeek]

Yes, it's a special data type. It's an equivalent of GreyGeek's solution, only shorter.

http://www.postgresql.org/docs/8.2/s...ATATYPE-SERIAL

Last week I upgraded from PostgreSQL 8.1.4 to 8.2 on my W2K workstation. My compiles stopped with an error while attempting to load a dll.

qsqlpsql.dll loaded ssleay32.dll which loaded leay32.dll which failed because the first dll was looking for functions which weren't present on one of the secure shell dlls. Dependencychecker showed the problem, but I am at home and don't have access to my notes. But, basically, when Qt attempts to load a library and it fails, it is marked so that it won't load again until the library is recompiled, or at least touched, so that the timestamp changes. Then Qt will attempt to load it again.

When I reverted back to 8.1.4 Qt4.2.2 apps compiled OK.

[GreyGeek]

I don't need any transactions now as I am under the PostgreSQL protections? Am I right?

No. You still may need to use transactions.

A classic example is the General Journal posting. Say you have a table containing daily postings of debits and credits to various accounts. You want to post those amounts to their specific debit and credit accounts to that your accounts remain balanced. If any of them fail you want to revert all of them. You do that by
Begin;
post the daily transactions to the general ledger
Commit;
(or Rollback if things didn't go well).
See http://www.postgresql.org/docs/8.1/i...nsactions.html

This avoids the "dirty read" problem:
http://www.informit.com/articles/art...&seqNum=6&rl=1

[GreyGeek]

Triggers are very useful. Their use is only limited by your imagination.

A simple use is backing up changes made by clerks. A trigger on the UPDATE would save the *.NEW or *.OLD record in a history table automatically. Then, if a question arises as to who changed what and when you can consult the history table. If you need to revert a change you can use the history table as the source of the previous record.