Schema Modeler Frequently Asked Questions

Can Schema Modeler retrieve metadata for custom SQL?

Yes for .NET connections and No for Delphi connections. For Delphi connections Schema Modeler can get such information only for auto-generated SQL query (stAutoSql statement type)

Do the Schema Modeler and the TDABusinessProcessor use the same BusinessClassID?

No. They reference two different kinds of business classes: the first for the client datatables, the second for server side deltas. Business helper classes provide a list of virtual methods that you can override to implement your own business logic (i.e. OnBeforePost, OnBeforeProcessDelta). These methods have a one-to-one relationship with the events of the classes TDADataTable or TDABusinessProcessor. See the strongly typed demo for a full example.

How are the different columns used in Data Abstract's Field Mappings?

Given this SQL statement: 

SELECT CUST_NAME AS CustomerName FROM ...

and a data table defined where the field is called "Name" in the schema, the following column mapping is needed:

Schema Table Field Database Table Field SQL Alias
Name CUST_NAME CustomerName
  • Schema Table Field is the internal reference used by Data Abstract. Its use allows multiple connections with different Database Table Field entries. For example, a different database may provide a CUSTOMERS field but Data Abstract can use the column mappings to refer to the field on either database as Name.
  • Database Table Field is the name of the FIELD in the actual database TABLE. This will be used in any auto-generated code, such as when updating/inserting data, or if a SELECT is generated automatically.
  • SQL Alias is the ORIGIN of the data as returned by the SQL provided. This is used only to locate the field when executing the above query (which returns CustomerName, in this case) and nowhere else. SQL Alias is only needed if a SQL statement is provided containing AS clauses (or other SQL Syntax that changes the names of the data returned). Apart from that, Database Table Field will be used and the value in the SQL Origin doesn't matter.

How is column mapping done in the Schema Modeler and what is the SQL Alias column?

SQL Alias is the actual name used in the SQL statement which you will need when creating WHERE clauses. For instance, assume that you have the following statement: 

SELECT C.NAME as CustName, C.CITY as CustCity FROM Customers C

If you want to filter by name or city, you cannot use the names "CustName" or "CustCity", but need to write a WHERE like: 

WHERE C.NAME='Jack'

That is what you would set your SQL origin to (C.NAME).

BUT, on the Client side this has no relevance. Your datatable would have a field called CustName and when you do something like 

myDataTable.Where.Add('CustName', equal, 'Jack')

and try to open it, the client would send the following value for the parameter UserFilter of the GetDatasetEx method: '##CustName=''Jack''.

The magic now happens server side: before the SQL is built adding this condition, DA checks to see if there is a ##FieldName in the UserFilter. If there is, it will be replaced with the SQL Origin value, which would finally turn that into a C.Name='Jack'

When should I use Schema Modeler commands?

You should only use commands when you need custom and very specialized SQL. In that situation, you can use the SQL commands generated by the Schema Modeler as a starting point.

If you don't need custom SQL, then you don't need to generate Schema Modeler commands since the Business Processors generate those basic statements by themselves.

Why do Advantage.NET (ADS) table names have trailing spaces when I add them to my Schema?

This happens because Advantage Database Server pads strings with spaces. Advantage.NET provider gives a possibility to change this behavior by setting connection string parameter TrimTrailingSpaces to true. This parameter is added by default to the connection string when new connection is created in Schema Modeler.

Why stored procedure parameters cannot be retrieved for ElevateDB .NET database?

Unfortunately ADO.NET driver for ElevateDB doesn't implement method DeriveParameters that is used by DataAbstract to retrieve information about stored procedure parameters.