Matthias Ruckenbauer · Aug 29, 2019

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

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

0 3 158 2


Hi Matthias.

Try adding [Identity] to the attributes of property id in generated class.

Something like follows:

Property id As %Integer(EXTERNALSQLNAME = "id", EXTERNALSQLTYPE = 4) [ Identity, SqlColumnNumber = 2, SqlFieldName = ID ];

Hope this helps,

That did it! smiley

Man - we looked into that for hours ripping our hairs and grinding our teeth.

Thank You so very much!


As an additional comment, since this was not clear to me in advance:

The field 'id' is not set in the %Save() (as the %Id() would be with a normal Caché class). This ist reasonable sice neither of the fields 'a' or 'b' in my example ar part of the id and the id is not read from ODBC-connection.

Thus, the object has to be re-opened after the %Save() searching the id using a select on the table specifying a and b.