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

ODBC, Caché

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

Answers

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,
Alexander.

That did it! smiley

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

Thank You so very much!

Matthias

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.

Regards
Matthias