problem getting the last serial value
cornelhughes asked why dbinfo('sqlca.sqlerrd1') returned 0 for the last serial value. Rudy Fernandes said it must run immediately after the INSERT and the select must return a row, e.g. from systables where tabid = 1. Art S. Kagel and Andrew Pearson added that SELECT MAX is unsafe with concurrent users while dbinfo is per session.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I have tried using select dbinfo('sqlca.sqlerrd1') from systables where tabid = 122 but I keep on getting 0. The name of the serial field is cust_id. Does anyone know of a fix for this? Is using select max (cust_id) from cash_balance the best alternative? Sent via Deja.com http://www.deja.com/ Before you buy.
To get correct results from "select dbinfo(sqlca.sqlerrd1) ...", you must ensure the following : 1. It must be run immediately after the INSERT statement who's serial you are looking for. 2. The select stmt must return at least 1 row (ideally, exactly 1 row) - it does not matter against which table or table-row this is aimed at. Usually, to ensure the 2nd condition, one uses a statement like select dbinfo('sqlca.sqlerrd1') from systables where tabid = 1; or select dbinfo('sqlca.sqlerrd1') from systables where tabname = 'systables'; Rudy cornelhughes@netscape.net wrote: > I have tried using select dbinfo('sqlca.sqlerrd1') from systables where > tabid = 122 but I keep on getting 0. The name of the serial field is > cust_id. Does anyone know of a fix for this? Is using select max > (cust_id) from cash_balance the best alternative? > > Sent via Deja.com http://www.deja.com/ > Before you buy.
cornelhughes@netscape.net wrote: > I have tried using select dbinfo('sqlca.sqlerrd1') from systables where > tabid = 122 but I keep on getting 0. The name of the serial field is > cust_id. Does anyone know of a fix for this? Is using select max > (cust_id) from cash_balance the best alternative? You do realize that you have to call dbinfo immediately AFTER inserting the row into the cash_balance with a zero value for the serial column (cust_id?)? And no SELECT MAX(cust_id) will not work since other users can have inserted a row using the next sequential value immediately after you fetch it if you are planning to use that value for insertion or immediately after you inserted it if you are trying to get the value for use in foreign keys. Art S. Kagel
<cornelhughes@netscape.net> wrote in message news:8de4a9$i28$1@nnrp1.deja.com... > I have tried using select dbinfo('sqlca.sqlerrd1') from systables where > tabid = 122 but I keep on getting 0. The name of the serial field is > cust_id. Does anyone know of a fix for this? Is using select max > (cust_id) from cash_balance the best alternative? > SELECT MAX(cust_id) is not really a good alternative, DBINFO has a great advantage for concurrent users - in that it works for the current session. If you select the max id you'll get whatever anyone happens to have just inserted into the table, whereas dbinfo will give you the serial from the last row that your session inserted - as long as you call it directly after the insert. -- Andrew Pearson "exactly what the web needs less of"