Problem with last inserted id.

[puunnnk]

I have this table:

CREATE TABLE codigo
(account_id INT NOT NULL DEFAULT 0,
id INT NOT NULL AUTO_INCREMENT,
Codigo INT,
PRIMARY KEY (id),
FOREIGN KEY (account_id) REFERENCES empresas(id) ON DELETE CASCADE
)

And I want to select the last value(or the max id) and put it in an Edit.

How do I select the last id????

I'm using c++ builder 5.

Thanks all.

[btrewern]

You can use the LAST_INSERT_ID() SQL function in MySQL.

Regards,

Ben

[puunnnk]

I found out.

the is this:

Code: Select all

ZQuery1->Active=false;
ZQuery1->SQL->Clear();
ZQuery1->SQL->Add("SELECT max(id) as id FROM codigo");
ZQuery1->Active=true;
AnsiString lastid =  ZQuery1->FieldByName("id")->AsString;

Thanks for helping me.

[hudgi]

Puunnnk,

"SELECT max(id) as id FROM codigo"

it won't work on a network environment. What will you do if some other users also insert new records in parallel with you? You will see the id inserted last time by anybody, not only you...

I do not know if there is a Mysql object "sequence" like in PostgreSQL or "generator" (?) in Firebird, but such an object would be much solid.
I use Postgres and when I want to insert a new record I first get a new ID (integer) from the sequence and I insert my row with that id. This ensures that that ID will not be used by any other users.

hth,
-- Hudgi

[mdaems]

All 3 answers are correct...

Puuunk, what's exactly the reason of your question?
- You want to know the id of a row you just inserted? -> use select last_insert_id() like Ben told. If you inserted using Append on a ZTable or a ZQuery the id should be filled automatically after posting, however.
- You just want to know the highest number. -> Your solution is what you need
- You want to use that number to insert the next record -> hudgi 's right : forget it. It's bad coding practise... You will have to build some sequence/generator system yourself (there are nice samples for mysql on the web).
- ???

Mark

[hudgi]

Mark,
thank you for your summary. You are absolutely right.
Does this append feature also work with Postgres tables? Does it handle sequences as well?

-- h.

[mdaems]

The automatic thing with append doesn't work in Postgres unless you use a TZSequence component. That one doesn't work with mysql, however as mysql doesn't have sequences.
If it has to work for both databases : implement a sequence trick with stored procs in mysql and implement your own TZSequence for mysql. I think it's not so diffucult, but it wouldn't work on a standard mysql installation, which is why we don't implement TZSequence for mysql.

Question from a Postgres dummy : Can you easily check if a column uses a generator? Like using 'describe' or something similar? That would enable a postgres specialist to add this automatic thing easily as well. (Mysql shows 'autoincrement' as part of the column definition, so we immediately call 'last_insert_id' after posting inserts)

Mark

[hudgi]

Mark,
if you create a column with type Serial, an explicit sequence will be created automatically. In PGAdmin3 you can see on the properties page a sequence field which gives you the name of the associated sequence for the selected field object. ("[FIELDNAME]_seq" by default)
Because this sequence name can be anything else you can't lean on the guessed default name. You should extract the correct information from the information_schema.

If you want to retrieve this info by code you can use one of the following solutions:

From PostgreSQL 8.0:

Code: Select all

SELECT pg_get_serial_sequence('MyTableName', 'MyFieldName');

Before PostgreSQL 8.0:

Code: Select all

SELECT s1.nspname || '.' || t1.relname AS tablename,
a.attname,
s2.nspname || '.' || t2.relname AS sequencename
FROM pg_depend AS d
JOIN pg_class AS t1 ON t1.oid = d.refobjid
JOIN pg_class AS t2 ON t2.oid = d.objid
JOIN pg_namespace AS s1 ON s1.oid = t1.relnamespace
JOIN pg_namespace AS s2 ON s2.oid = t2.relnamespace
JOIN pg_attribute AS a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
WHERE t1.relkind = 'r'
AND t2.relkind = 'S' 
AND t1.relname='MyTableName'
AND a.attname='MyFieldName';

Using pg_get_serial_sequence it will result in an exception when you pass a non-existent table or field name. Using directly the query in this case you will get an empty result set. If you plan to remain compatible with earlier versions, as I guess, you should use the later solution which I tested on v8.1.4 server successfully.

The above information is discussed here:
http://svr5.postgresql.org/pgsql-genera ... g01381.php

Cheers,
-- hudgi

[btrewern]

I can't seem to find anywhere that decisively marks a field as a serial field (similar to AUTO_INCREMENT in MySQL speak).

But you can use something like

Code: Select all

SELECT
  column_default
FROM
  information_schema.columns
WHERE
  table_schema = 'schema' AND table_name = 'table_name' AND column_name = 'column_name'

The column default will be something like

Code: Select all

next_val('table_name_column_name_seq'::regclass)

which could be used to find the next value in the sequence.

BTW information_schema was introduced in the version 7.4 of PostgreSQL.

Hope this helps.

Regards,

Ben

[btrewern]

Which is the oldest version of PostgreSQL that Zeos supports?

Schemas came in at 7.3 so you may need to make the query version dependent.

Ben

[hudgi]

Ben,
AFAIC, in postgres the dependency links a sequence to a table field and there are no explicit markers.

I think your solution is not really solid because you can't decide if a sequence is referenced in column_default or not. Certainly you can judge by searching for character sequences like "next_val" but I think it is not that rock solid way.

In the case when e.g. you have a serial field a dependency entry is automatically inserted, but if you use a standard integer type in the definition and you set the default expression to get a new value from a sequence (which you must create separately) the sequence does not appear in the pg_depend. So you have to know the name of the separately created sequence.

We could say that a component supports automatically sequenced fields only if the field was defined with serial type. If the field is not serial (no dependency entry was created) the programmer must give a sequence name to get the next value from.

I hope I did not say silly things!!! Maybe we should also ask this topic on the PostgreSQL mailing lists...

-- hudgi

[mdaems]

Hudgi,
Ben,

Good question for the PostgreSQL lists, indeed.
Now, who is going to find out how you can implement this serial thing in Zeoslib? I'm not using PostgreSQL and I don't now about an active developer using it (basically we now have 2 active developers : cipto (Firebird) an me (mysql)). I don't plan to learn pg in the near future.
So it will have to be a new person. Please show up if you have some time to spend.

I can point you to the way it's implemented for Mysql to give you some ideas.

For the moment we only have pg7 and pg8 in zeos. Not sure if this means 'all 7 and 8 versions'. I suppose we best think of it as 'higher 7 and 8 versions'.

Mark

[hudgi]

Mark, Ben,
now came into my mind that the default expression is quite a good approach (thx for Ben) because that whole data stored in information_schema.columns.column_default is the complete expression which must be executed before every insert. (I mean that we do not need to extract the sequence name and then build probably the same expression. )

So this is correct anyway - and it probably works on earlier postgres versions too. (back to 7.3? the first version armed with information schema)

Regarding the development: I will think over my near future plans - I have a lot of work, and I do not want to promise something which not sure to keep. BTW, I feel some motivation... What about Ben?

-- Hudgi

[mdaems]

Hudgi,

I think you're wrong... You don't need 'nextval' but 'currval' to know what serial has been inserted. When you use the default value calculation after a null insert to fill out the missing value in your dataset you'll increment the sequence.
This is what the TSequence component does : it calculates the nextval of the sequence and this value is added to the insert statement, so the default value will not be used. This works well, but you'll have to add a TZSequence component and set the name of the sequence which should be used.
The append thing without TSequences should do this : check if null was inserted in a 'autoincrement' field. Then get the current value of the sequence used by the server and use this value to bring the cached resultset up to date. This is more 'invisible', but then we'll lhave to find out how to get the sequence name.

[hudgi]

Mark,
You are right if you use this approach. Sorry, but I think this is that "bad programming practice" you mentioned earlier. (note the risk of other users simultaneous inserts)

In my opinion it would be a much more solid solution to obtain the next_value Before executing the insert statement, and then cutting together the insert statement using the newly obtained ID.

OK, I admit that from a default expression it is not evident whether the field is connected to a sequence or not. (e.g.: one can define a stored procedure as a default expression to get a new value from any sequence...). My fault was that I omitted this problem.

I have dropped a question on the PostgreSQL.General mailing list with the problem (subject: "Getting sequence name for a non-serial column"). Let's wait for the answers.

BTW, if there is no way to find out the sequence name, we have that nice TSequence to use... It would be a big step forward however to set up serial automation.

??

-- Hudgi