ZUpdateSQL with 2 Tabels with Autoinc-Field

[Chaosworld]

Hi

I'm using ZQuery with Select-Query and I am using this 2 tables:
Artikel:
ID : autoinc
Name: string,
...
AtikelBild:
ArtikelID: integer
Bild: blob,
...

the query looks like this

Code: Select all

Select * from Artikel, AtikelBild where artikel.ID=artikelbild.artikelID

This query works perfect, but is obvouslly not writable. So I am trying to work with ZUpdateSQL. The Problem is the Insert-Statement, that looks liek this:

Code: Select all

Insert into Artikel (Name,...) Value (:Name,..);
Insert into AtikelBild (ArtikelID,Bild,...) Value (:ID, :Bild..);

The problem is the 2. line. Just after executing the 1. line, the value of ID is known. But at the 2. I need this value. The question is now, how I have to change the Statement, so that i can us the value of the 1. line directly after in the second line? Or does there exists another solution?

I found here in the forum the tip to use RETURNING. But I am using MSSQL and it looks as it this function is not integrated for this DB. Or did i made a mistake?
Oh by the way, I am using a DBCTRLGrid to insert information.

Thanks for every answer

Chaosworld

PS: sorry i wrote this text first in german. I was looking here and also a lot with google and was reading before in german so that i forgot that english is used in this forum!

[mdaems]

Hi Chaosworld,
There's no problem with German queries/table structures, I think. As long as it's human readable in most charactersets that will be allright. (sorry for the chinese )

Concerning your problem:
I suppose a subquery retrieving 'max(i) from Artikel' would do the trick when the right transaction_isolation_level is used. (Supported by mssql??)
If Artikel.Name is unique you could use that as a key in you subquery as well. (isolation level not important)
For mysql I would use the last_insert_id function. Is there something similar in mssql? (I know, exists for SQLite, but not for Oracle, so you'll have to look in the reference)

Mark