Syntax

[Amy_N]

I am trying to do a number of things. One of these is using SQL e.g. to create charts but also editing the database directly. Is there a list of the syntax anywhere?

I am also interested in the syntax used in Sparx directly e.g. for shape scripts. Is there a list of commands anywhere?

[Geert Bellekens]

There is not EA specific syntax.
EA just forwards the SQL to the underlying database, so you'll need to use the SQL dialect of the repositories database type.

For scripting you can use VBScript, JavaScript and JScript using their standard syntax.

Geert

[qwerty]

"just forwards" is not completely true. EA uses it's own compiler first. Some keywords (e.g. AS) must be capitalized. The column Note(s?) is treated specially.

q.

[Eve]

EA just forwards the SQL to the underlying database, so you'll need to use the SQL dialect of the repositories database type.

"just forwards" is not completely true. EA uses it's own compiler first. Some keywords (e.g. AS) must be capitalized. The column Note(s?) is treated specially.

Although there are a couple of situations where EA does process the sql, the examples provided by qwerty aren't. They are examples of how EA handles the information coming back from the server.

• ea_guid AS CLASSGUID must be in sql results exactly for EA to try matching to an element
• Note column can be hidden, show a short preview below the row or show the full notes below the row
• Object_Type will show the profile metatype if available.

There are things that EA looks for before sending the request to the server. See Create Search Definitions for the list.

That list is mainly for custom sql searches. It won't be handled in all situations, but if my memory serves they can be used for charts as well.

The other alteration that could come up, is wrapping keywords in square brackets. eg. select [Value] from t_objectproperties

[qwerty]

The other alteration that could come up, is wrapping keywords in square brackets. eg. select [Value] from t_objectproperties

So if my memory serves well this is only valid for M$Access/EAP. Other SQL derivates demand back-ticks or other quotes instead of square brackets.
q.

[Geert Bellekens]

The other alteration that could come up, is wrapping keywords in square brackets. eg. select [Value] from t_objectproperties

So if my memory serves well this is only valid for M$Access/EAP. Other SQL derivates demand back-ticks or other quotes instead of square brackets.
q.

The square brackets work for SQL Server as well. And if they work for SQL server then they will probably work for ASA as well as SQL Server was orginally the Sybase database engine before Microsoft purchased and rebranded it. I'm not sure about other SQL dialects though.

Geert

[qwerty]

It looks like the different dialects are more tolerant than I thought. SQLite accepts square brackets as well as back ticks.

q.

[Eve]

The other alteration that could come up, is wrapping keywords in square brackets. eg. select [Value] from t_objectproperties

I mentioned this because EA explicitly handles it for all sql queries it runs. Otherwise it would still fit under the category of "use the SQL dialect of the repositories database type".