linked table (ODBC), %New() -> %Save()

Hi folks,

My fist post here and a tricky question right away!

I have a remote MySQL database table ("SomeData"; not under my control), and a Caché-class (remote.SomeDataAccess) linked to it via ODBC using the link table wizard.

The remote table has a field "id" which is the primary key and an autoincrement bigint value. This has been considered in the setup and the generated class has an

Index MainIndex On id [ IdKey ];

which looks fine to me. The storage is

 Storage GSQLStorage
{
<StreamLocation>^remote.SomeDataAccessS</StreamLocation>
<Type>%CacheSQLStorage</Type>
}

For the most part this works perfect. I can %OpenId(), modify, %Save() and even %DeleteId() objects in my class thus modifying the remote table.

The problem arises with %New(). When creating a new record using %New(), setting some properties (say s myobj.a=4, myobj.b=5; whatever, just not "id") and trying to %Save() the object I get an error

err(2)="ERROR #7207: Datatype value '' is not a valid number"_$c(13,10)_"  > ERROR #5802: Datatype validation failed on property 'remote.SomeDataAccess:id', with value equal to """""

coming - we analyzed this far - from the Caché side %ValidateObject.

When using:

 &sql(INSERT into remote.SomeDataAccess (a,b) VALUES (4,5))

I get a new record which I can search the id (by the values of a and b) and then %OpenId(). The new record then has a proper value for id (which actually is the .%Id() and autoincremented).

For some reason or other we want to avoid using an INSERT and would rather use %New()/%Save() to create records. For, I think, obvious reasons we don't want to guess the correct value for id when doing so.

Does anybody have an idea how to achieve this, i.e. making %Save() work for SQL-linked Classes in the "usual" way (which would mean here: no OID there? => fine, make an insert without the id and open the new object).

Kind Regards

Matthias Ruckenbauer