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