Just a note...

    LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, 

This field will only define the LAST_UPDATED field at INSERT, not for an UPDATE.  You probably want something like:

    LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) NOT NULL, 

This will cause LAST_UPDATED to be set at INSERT and UPDATE to the current timestamp to microsend precision.

Daniel,

I guess it looks like we don't support CodeMode = generator for overriding property methods. CodeMode = code should work, but you need to access type parameters from the property definition in order to generate the proper code. I believe you will need to extend the %String datatype and override Normalize, then use the new datatype class in your property definition. Here is an example:

Class User.StringoNoEmpty Extends %String [ Language = objectscript ] {

ClassMethod Normalize(%val As %RawString) As %String [ CodeMode = generator, ServerOnly = 1 ]

{

{
    set code="%val"
    if %parameter("TRUNCATE"),%parameter("MAXLEN")'="" { set code="$e(%val,1,"_(+%parameter("MAXLEN"))_")" }
    $$$GENERATE("    RETURN $tr("_code_",$c(0),"""")")
    RETURN $$$OK
}

}

Property myString As User.StringoNoEmpty(MAXLEN = 54, TRUNCATE = 1);

Hope this helps,

-dave

$c(0) is the internal representation of the SQL empty string ''.  Your insert must be using '' as the value for myProperty instead of null if you are seeing $c(0) for the field valuesSQL null and empty-string string are very different.  null means unknown, while empty string is a 0 length string value.

If you always want to convert '' to null for myProperty values, write a myPropertyNormalize property member method that converts the value to null.  This will override the %String Normalize() method and convert $c(0) to "" whenever the property/field's value is normalized by the filers.

Trying to do this in a trigger will not work as triggers are not meant to be used to modify property/field values being filed.

Can you be a little more specific? Are you are asking about embedded SQL, dynamic sql, ODBC, JDBC,  or limits for each interface?

The answer is different for each, although I'm not sure there is an exact length limit.  For embedded SQL, I don't believe there is any limit other than the maximum size of the code you can define for a .MAC routine or class method.

I believe for dynamic SQL the max length should be pretty close to the maximum string length supported by your Caché instance.  I was able to Prepare a using %SQL.Statement that was a bit over 3,600,000 characters in length (using IRIS, I don't have immediate access to a Caché instance))

Hi Suman,

For the case where the ID is not system generated, try the following query after a successful INSERT:

   SELECT LAST_IDENTITY()

You can actually do this for the case where the ID is system generated also, but in that case getGeneratedKeys is probably a better option.

 LAST_IDENTITY() simply returns the value of the %ROWID variable, which is set after a successful INSERT to the value of the rowid field.

Try this:

CREATE TABLE test (
  identifier   VARCHAR(200) NOT NULL,
  value        INTEGER COMPUTEONCHANGE("%%INSERT","%%UPDATE") COMPUTECODE { if %oper="INSERT" { set {*}=1 } elseif %oper="UPDATE" { set {*}={value}+1 } },
  PRIMARY KEY (identifier)
)

[SQL]SQL:USER>>select identifier, value from test

identifier value

0 Rows(s) Affected

[SQL]SQL:USER>>insert or update test (identifier) values ('row 1')

1 Row Affected

[SQL]SQL:USER>>insert or update test (identifier) values ('row 1')

1 Row Affected

[SQL]SQL:USER>>insert or update test (identifier) values ('row 2')

1 Row Affected

[SQL]SQL:USER>>insert or update test (identifier) values ('row 2')

1 Row Affected

[SQL]SQL:USER>>insert or update test (identifier) values ('row 2')

1 Row Affected

[SQL]SQL:USER>>insert or update test (identifier) values ('row 3')

1 Row Affected

[SQL]SQL:USER>>select identifier, value from test                 

identifier value

row 1     2    

row 2     3    

row 3     1    

3 Rows(s) Affected

1) Primary Key in Caché / IRIS is useful in that you can expose to third-party tools (typically through xDBC catalog queries) what the Primary Key is for your table.  Other than that, it has no special meaning to us.

2) You can define bitmap indices using Index1 if you tweak the property definition of Identifier to tell the system the value is a positive integer:

                    Property Identifier As %Integer(MINVAL=1)