How do I get the IDENTITY field from the d/b?

Did you checked with the  "identity" datawindow column property to set to "No". But this is also depend upen the DBMS. Or may be related to Foreign Key in your Master and Detail tables.

The identity is checked into the Update Properties of the master datawindow. I am using SQL Server 7.0 and Powerbuilder 7.

The problem still remains. It doesn't get the identity m_id from the Amaster table, in order to pass it into the m_id field (not allowing NULLS) of the Adetail table, so the application gives me the error...

Adetail has a primary key d_id which is identity, autonumber, and a foreign key, m_id, which is the primary key of Amaster. Amaster has 3 keys, one of them being the "m_id", a text field "txt_id", and an int "int_id". The values of txt_id and int_id are predefined and have no problem with them. The m_id gives me all the problems.

I am understading so (may me is wrong), but any how, m_id is a PK of the master table and d_id is the PK and FK of the detail table. one to many relation is master to detail relation.

If you have no such script that it inserts into master the d_id you are inserting in detail you should with hand
enter the value in master table m_id. (because one m_id can be a many d_id in detail).

I don't know you are inserting in detail first then in  master or first in master then in detail.

But if you are inserting in detail first the m_id as d_id you are inserting in detail must be in m_id.

I realy afraid but you can test it if you have a test database also.

Please let me know I understood the problem?

Adetail has a primary key d_id which is identity, autonumber, and a foreign key, m_id, which is the primary key of Amaster. Amaster has 3 keys, one of them being the "m_id", a text field "txt_id", and an int "int_id". The values of txt_id and int_id are predefined and have no problem with them. The m_id gives me all the problems.

Again:

TABLE Amaster WITH
PK m_id
FK txt_id
FK int_id

TABLE Adetail WITH
PK d_id
FK m_id

RELATIONSHIP:
One record in Amaster can have many relationships to records in Adetail. So, many records of Adetail can refer to 1 record in Amaster.

The problem is that when I try to enter data using a master/detail sheet, when it updates the database, it first updates the Amaster table, but it doesn't pass the m_id (that was generated when the record was inserted) into the detail datawindow, so when the update function of the detail is called, m_id is NULL, and it gives me this problem. Is there a way of grabbing the m_id when the data is inserted into the Amaster table (100% accurate in multiuser environment) and pass it into the Adetail INSERT query, when inserting the detail datawindow records?


The problem is getting

You are inserting values in  Adeatil table  (d_id - which is primary key and foreign key of Adetail table and related to primary key of the Amaster table m_id). I think you must first enter with hand the d_id you are inserting in Adetail table in Amaster table.

In otherworlds enter the m_id with hand in Amaster table which you want to insert in Adetail as d_id.

Again:

TABLE Amaster WITH
PK m_id
FK txt_id
FK int_id

TABLE Adetail WITH
PK d_id
FK m_id

RELATIONSHIP:
One record in Amaster can have many relationships to records in Adetail. So, many records of Adetail can refer to 1 record in Amaster.

The problem is that when I try to enter data using a master/detail sheet, when it updates the database, it first updates the Amaster table, but it doesn't pass the m_id (that was generated when the record was inserted) into the detail datawindow, so when the update function of the detail is called, m_id is NULL, and it gives me this problem. Is there a way of grabbing the m_id when the data is inserted into the Amaster table (100% accurate in multiuser environment) and pass it into the Adetail INSERT query, when inserting the detail datawindow records?


The problem is getting

This is the problem with you last comment. Since I don't have the m_id (it is autoincrement in SQL Server), if I don't commit the row in Amaster, I cannot get the m_id, in order to pass it into the Adetail's m_id field. The problem is with the way master/detail datawindows work. The m_id MUST BE KNOWN, when I start inserting rows into the detail sheet in my window...

I am afraid but you know that column with Primary keys and Foreign keys to adjust as autoincrement is sometime dangerous. For exemple I don't know what you are inserting but if  you want to insert in detail number 27 and your master table has only max number 25 then the autoincement in master is 26 not 27, so cannot be updated.

You can also check with max function to get the master max number and then check what you want to insert is the same with increment in the master or not.

Please think on this proposal and let me know, I am thinking further.

If I use the max function, and two users try to write at the same table, then they will both get the same m_id, and then pass it into the Adetail table, and duplicate key error will occur. I need to somehow save the data into my Amaster table, then get the m_id that was assigned ACCURATELY, in order to pass it into the Adetail table.

All this in a master/detail datawindow.

Before inserting please compare with master ID,
if necessary first insert in master untill  approach the inserted number
and then insert in the detail.

( Am I right?)

Can you try the following
(1) Update the master datawindow, and COMMIT the change
(2) Read the value of the m_id column. (You will need to have put the m_id col in the datawindow. On doing  a ReselectRow(rownum) for the master, you will get the value of m_id)
(3) SetItem the value of m_id in all of the detail datawindow row(s) and update n COMMIT the detail datawindow.

You will have to take care of the transaction control manually i.e., deleting the changes from the Amaster table, if the update of Adetail is unsuccessful.

Hallo qm395,

What about IDENTITY ?

Sorry I am on a small vacation, I will talk to you from my office, tomorrow morning.Thanks.

Normally, I would think Powerbuilder would understand in such cases that m_id is an identity field, and before storing all the data into the detail datawindow, would automatically commit the master's data, get the m_id and pass it into the Adetail table. Can this be implemented automatically.

For the moment I had to remove the autoincrement from the Amaster table, and use another table named Aidentityvalue, in which I store a unique value, and each time I want to insert a record in Amaster (and related records in Adetail) I insert a record into Aidentityvalue, get its unique ID, and pass it as integer in Amaster. In this way, it is automatically passed to both Amaster/Adetail.

The problem in which I need your help now is the following:

I get the unique ID value from Aidentityvalue table is that I add a record with a large random number, and then I get the max(id) from that table, where random number="the random I have inserted before".

Now, if more than 1 users access the table at the same time, and accidentally, get the same random number 1/32767, then the whole system is screwed. Since the previous actions is only an INSERT and a SELECT, how do I lock these two actions, so that if a users runs these two SQLs, noone else can access the table?

I am writing all this info into the script of a button, and I want to do this:

LOCK table Aidentityvalue
INSERT INTO Aidentityvalue ....
SELECT FROM Aidentityvalue ....
UNLOCK table

what are the commands for implementing this LOCK/UNLOCK of the table in Powerbuilder 7.0.

Thanx

If two or more people access the Aidentityvalue

May I write you in detail later because I am busy in some requests to change.

Please look a "Lock database preferences" in Help SQLCA.Lock = "Value" may this top brings some results.

talk you later.

Did you read "Lock database preference"?

Yes, but it seems that I have to define locks for the whole database (I am using SQL Server 7.0). The only lock I need is at these two actions I described before OR ... a lock on any action on the table Aidentityvalue.

Any help there?

Try using TABLOCK or TABLOCKx.

The syntax is:

SELECT <col_name>
FROM <table_name>
WHERE <where_stuff> (TABLOCKX)

The lock is released when your transaction is complete.

As I understand this will lock the table for the SELECT statement. I need the table locked for both the INSERT and SELECT statements. How would this work?

I think you need not to lock the tables. If SQL Server support embaded SQL as you write in SELECT statement at the end , FOR UPDATE will lock the table untill COMMIT OR ROLLBACK worked.

I come across with the same case.  For me, in the save event, try to update the header datawindow, since I have another column that is unique also, I try to retrieve the identity column based on it.  Set the identity column value into details database and update the details datawindow.  Finally commit the all changes if no error found.  That's it.  If you don't have an unique column other than the identity column, skip above opinion.  Suggest you to generate the key value instead of auto-gen by system.

I don't see, where is the problem. I use it each day. No locking is needed. Identity value will appear in master datawindow after successful insert (when the column is marked as identity), commit is not needed. Then You have to transcribe this value into detail DW, column m_id, and update of detail DW will success too. Commit should be done after both datawindow updates.

There is only one bug in PB. Update with no resetflag "dw.update(true,false)" will cause, the master datawindow will obtain identity values, that can be rolled back, if second update (detail) will fail. Now, before second attempt to update master DW, You have to set identity values to NULL in master datawindow in rows, that remained newmodified, otherwise (this is the bug) datawindow will not get new identity values.

May be it's database driven.  I'm using MS Access 97/2000, the identity field can't refresh once inserted.

I tried everything. The problem is that in a single D/W it works perfectly, and returns the variable, but if I am adding data into the detail datasheet, it seems that in the second row, and the next ones, it does not pass the ID field. I fixed it by removing the identity field, and getting values from SELECTing from another table. At least it works...

it is just a partial solution to my problem.