User Answers

Take a look at the %SQL.Util.Procedures class - specifically the CSVTOCLASS method. The interface isn't very nice and it isn't intuitive but it will do what you want - and more. You can invoke this directly or you can execute it as an SQL procedure. I worked up a very quick example using the data you included - I just created a simple file. In the file you can replace the column headers with a ROWTYPE (SQL standard defines this thing) or you can pass the ROWTYPE as an argument (that is what I did here).

Another option that abstracts the caller completely from the quoting requirements is to use a parameter. Parameters in Dynamic SQL are positional. Also, keep in mind that literals are replaced automatically in dynamic SQL statements so using a parameter for this will not add any overhead to the processing.

As you have already discovered, there is no opportunity for a user to change the collection type class. That class is determined by the compiler when processing LIST and ARRAY keywords. This assignment, indicated by the compile-only keyword of RUNTIMETYPE, occurs during inheritance resolution and it cannot be overridden by the user. Your solution of coercing the RUNTIMETYPE using a method generator is not completely correct even though the runtime behavior seems correct.

That phrase is not a reference to a special type of class, instead it refers to the reason the class is added to the set of classes to be compiled. For example, if you compile Sample.Employee it becomes a "direct class" in the set of classes to be compiled. The compiler evalutes each class in the set of classes to produce an "expanded set of classes". In this case, Sample.Person would be included as an "expanded class". HTH.

To set a property whose type class is a serializable class (extends %Library.SwizzleObject - streams, serial classes, persistent classes) you need to call the property's SetObject or SetObjectId method if you wish to set the value to a serialized value (id value often). I think that in your example, tvar1 is holds an ID value of the referenced ZenCrm.Relationtypes class? If so, then this should work:


do RelationMatrix.RelationAIDSetObjectId(tvar)

The ID is assigned by %SaveData() and %OnBeforeSave is invoked before %SaveData is called. For a new object the ID value will not be reliable until after %SaveData has returned. I do not know whether or not this is documented explicitly.

I defined this simple class:

Yes, this should work but you are missing a step - you must execute the statement. Dynamic SQL allows for a statement to be prepared once and executed many times. A prepared statement is executed, returning a statement result. You can provide different parameter values for each execution.

set result = tStatement.%Execute()

Alternatively, you can do this in a single comment -

set result = $system.SQL.Execute("UPDATE table Set Status = 'Completed' WHERE ID in (1,2,3,4)")


Errors can be encountered during the normal execution of any code and the cause of those errors is not always known or predictable. Caché has three primary error reporting mechanisms two are passive, meaning the user code is responsible for checking some error indicator and one is active, meaning the error triggers a change in the code path. The passive error reporting, %Status and SQLCODE, both require the user code to check for error conditions after executing some code.

Hi Chris,

Good question! This is a poorly understood area and there is much to say about it. I'll try to be brief. Feel free to follow up.

Caché Objects persistent classes project to SQL as tables. Caché Objects relationships are binary - a relationship must have a defined inverse relationship in the related class. We only support relationship/inverse relationship cardinality pairs of one:many and parent:children (a special case of one:many where the parent is implicitly required and is also implicitly part of the IDKEY). A Caché Objects n-cardinality relationship (children, many) is transient in the container and there is no projection to SQL.