Add records to related tables (1:n, 1:1) URGENT !

[Marco]

Hi !

If got a problem that will take a while to describe:

I'm working on an SQL-Server Database via ODBC with CDatabase an CRecordset-derived classes.

The database got several relations between tables.

I've got to tables like:
TABLE ADRESS
-------------------
ID PRIMARY KEY, IDENTY (automatically updated)
Name
adress
... etc

TABLE CUSTOMER
----------------
CustNo PRIMARY KEY - but that doesn't matter here
AdressID FOREIGN KEY, UNIQUE
... additional customer data

and a relation from customer.AdressID into adress.ID

Now to the problem:
I want to insert an entry in the customer table and a corresponding entry in the adress table.

So i do:

pAdr: pointer to class derived from CRecordset reflecting adress table
pCus: pointer .... reflecting customer table

BOTH RECORDSETS ARE OPEND AS SNAPSHOT, 'CAUSE DYNASET IS NOT SUPPORTED FOR DATABASE !!!!

----
long adrID;

pAdr->AddNew();
pAdr->m_Name = ...
pAdr->m_... = ...
...
adrID = m_ID; // ID makes no sense ... value like 123456798
pAdr->Update();
adrID = m_ID; // ID of the row positioned last BEFORE AddNew();
--------

now I need the newly added ID of the new adress row to do:

----------
pCus->AddNew();
pCus->m_CustNo = ...
pCus->m_adrID = adrID;
...
---------

How can I get the ID of the newly added adress row WITHOUT requerying pAdr ? the new row isn't member of the snaphot-Recordset .... and I dont have any other unique values in the adress-table except the ID ....

When working with dynasets in an earlyer project I could use:
----
...
pAdr->Update();
pAdr->MoveLast();
adrID = pAdr->m_ID;
-----
in a snapshot this doesn't work ...

Does any one know a workaround ? Perhaps with an CDatabase::ExecuteSQL( "INSERT ... " ) statement ? I would'nt like this, cause actually the tables have MANY fields ...


Please help me out ....

Thanks...

Marco

eMail: [email protected]

Why do you not want to requery the recordset? just curious!

[Marco]

Ok, let me explain ....

1) the recordset's table has MANY rows, really MANY !!!
The problem with customers and adresses is not the only one I have ...
I've the same problem with a table that contains half a million rows ...

2)It is not simple to re-identify the newly added row. As I told in my message the ID is the only unique field in the table. All other fields may occure more than one time .... So I would have to do an identification over nearly the whole database

3) in one case the recordset is shown on an other Window in list-view with all changes permanently updated. I would have to create a second instace of the recordset only to requerry it and get back the ID - and i'm not shure that there are not any side-effects to other windows ....

4) In one case the data displayed to the user in one form is stored in about 8 different, related tables. Some They are related 1:n:n:1:n - so i would have to do the whole procedure with MANY recordsets ... a lot of code for an simple Append-process ... that can't be the only way to do it !!!!!!!

If I understood you corrctly, the reason you dont want to query is time consuming. I can understand that to be a big conern if you have big tables.
One time i had to deal with an issue like this, and if the unique field being generated in these tables is sequencial, then you could do this,
before you insert in the first table

I am not sure how you are doing your sqls but I hope this gives you an idea
Do
Select max ID from address table
then do you insert in address table
when inserting in customer then
customer.ID = ID+1;

[Gary Grant]

A wild idea (have not tried it my self)
Create a stored insert procedure with a return code and then return the ID.

[IVS]

Hi!
I don't exactly know how it is going in SQL-Server, but in MS Access the way you proposed will not work for auto-number unique identifier with increment. For an example, the table tAddresses contains 10 records with ID's from 1 to 10. Then we delete the last record from tAddress. Query "SELECT MAX(ID) FROM tAddresses" gives 9, but new inserted record in tAddresses will have an ID of 11. So, as it seems to me, the only way to get an ID of inserted record is to use query "SELECT MAX(ID) FROM tAddresses" after the record was inserted. In MS Access this query is proccessed very fast (May be Access knows that it is an auto number).

Hi IVS,
You are correct, I think I stated it wrong, in all database types( MSSQL, ORACLE, INFORMIX, MSACCESS, ..), they all behave the
way you stated, so it correct that an insert need to be done in address table before querying the max ID.
Thanks

[Marco]

I dont want to requerry the recordset of two reasons:
1) You are right ... I am concerned bout the time it would take, because the table is of course BIG !
2) The ID is the only way to surely identify the row ...

Your idea is good, I considered one like this, but:

- If a row had recently added to the adress table (lets say with ID 1000) and deleted after that and the database has'nt been compressed, that ID will be skipped. The result I get from "max ID" would be 999 and the new row I'm inserting will get ID 1001 ... !!!

And:
- And the database will be used in an multi-user environment. If beetween the result from "max ID" and the new Insertion an other user inserts. The multi-user environment, too is the reason why i cant set the ID by "hand" - or better to say - by code !

But thank's for your reply !!!

Regards, Marco

[Marco]

The idea is good ...

I have to examine that closer .... but ... as far as I remeber MFC ODBC classes cant handle the result code of a stored procedure ... I will have to take a closer look at this ... Thanks for your advise !!!

Regards,
Marco

[Gary Grant]

Check Article ID: Q183001 on msdn or the web site. I myself haven't hat to try it so I may be pointing
you in the wrong direction.

[brevert]

I don't know why you can't use a dynaset (or why you would want to.) If you are performing updates and inserts, a snapshot is not the way to go either. The proper resultset to create is a keyset. This type is supported by ADO. If you are not using ADO, shame on you. If you don't know it, learn it. It's methods are similar to the old DAO recordsets, but it uses OLEDB as its foundation. The keyset will operate similar to a dynaset but is magnitudes faster.

MFC and ODBC can handle stored procedures via CRecordsets

They can return values and you can bind other variables via the use of parameterised Recordsets.

I recently had a similar Multi-User environment problem with unique primary keys and the only way I solved this was to use Stored procedures.

If you want example of this source code

email me on [email protected]

[Marco]

After considering all ideas and possible solutions for my problem from you I found out that the following solution is the best in my opinion (I haven't fully implemented it so far, but it should work):

* I changed the database so that the ID fields are no longer auto-numbering but controlled by the application

* I inserted a new table that keeps the highest ID values of all the tables

* I overwrote the AddNew() member of every CRecordset derived class to:
- look up the new table for the highest ID so far in
- increment it by one
- set the incremented ID as the ID in the new recordset
I'm now considering using stored procedures for that.


Known Problems of my solution:

* That only works if all applications accessing the database (sql server - you remember) are using this technique and therefore is no solution for a new app accessing existing databases with existing apps working with it.

* I have to force the AddNew() member function to use a pessimistic locking pattern to keep the "highest-id"-table persistant (you remember - it is a multi-user environment)


If someone is interested I can post the whole solution when I finished it.


Thanks for all your tips !!!

Regards, Marco