Dynamic DataWindow

DataWindow objects and all entities in them have a set of properties. You can look at and change the values of these properties during execution using DataWindow methods or property expressions. You can also create DataWindow objects during execution. Appeon supports dynamically creating DataWindows with the following presentation style: FreeForm, Tabular, Grid, Graph, CrossTab, N-Up, Label, Composite, Group, TreeView. However, dynamically created RichText DataWindow is unsupported.

Also DataWindow cannot be dynamically created if the database type is Informix or PostgreSQL.

Important: Configuration required for supporting dynamic DataWindows

Configuration at the database server is required for support of dynamic DataWindows: go to the %Appeon_Server_Installation_Path%\appeon\sql\dynamicsql folder, and execute the SQL file started with "install_" for the database you are using, for example, execute install_appeon_syntaxfromsql_ORACLE.sql for the Oracle database.

You can uninstall the SQL files and disable the support of dynamic DataWindows by running the SQL files started with "uninstall_" in the same folder, for example, execute uninstall_appeon_syntaxfromsql_ORACLE.sql for the Oracle database.

Note that you should execute the SQL statement in the database console, as executing it in PowerBuilder may cause errors.

Notes:

  1. When installing or uninstalling the Appeon-provided SQL files, you must use the corresponding SQL executing tool of each database. Some databases are listed below with the tool they provided. If your database is not listed below, please check the corresponding database documentation.

    Table 208. 

    ASA/SQL Anywhere

    Interactive SQL

    SQL Server

    Query Analyzer

    DB2

    Command Editor

    Oracle

    SQLPlus WorkSheet

    SAP IQ

    Interactive SQL

    SQL Server

    Query Analyzer

    Teradata

    Teradata client tool


  2. (For Teradata only) Before installing the SQL file install_appeon_syntaxfromsql_teradata.sql for the Teradata database, you will need to:

    1. Copy Appeon_teradata_extfun.jar and install_appeon_userfunction_teradata.sql to the machine where the Teradata database client tool is installed.

    2. Open install_appeon_userfunction_teradata.sql and modify the first line to point to the physical path of Appeon_teradata_extfun.jar.

    3. Execute install_appeon_userfunction_teradata.sql via the Teradata client tool.

  3. After installing the SQL files, you should restart Appeon Server or redeploy the application to disable the caches.

  4. When you use the transaction SyntaxFromSQL method, please note the following differences between PowerBuilder and mobile:

    • The length of return value on mobile is different from PowerBuilder, because the return value on mobile carries the names of all columns while the return value in PowerBuilder does not.

    • Text, the object keyword, is unsupported if there are calculation involved in SQL statements, For example, "Select emp_ID, emp_Name, salary/12, "Dept="+emp_deptname from employee".

  5. When using transaction SyntaxFromSQL method for DB2 on mobile, please note that the generated DataWindow contains no primary key information and cannot be updated on mobile under the following conditions:

    • The database table contains only a simple index.

    • The database table contains a primary key that uses alias.

    • Access a table that contains a primary key as the table owner (for example, using SELECT * FROM DB2ADMIN.PUB_T_DEPARTMENT).

Methods of dynamically creating DataWindows

  1. Using DataWindow Create method

    Supported syntax:

    integer dwcontrol.Create (string syntax {,string errorbuffer})

    Unsupported: Using PBSELECT statement in syntax to create DataWindows is unsupported.

  2. Using transaction SyntaxFromSQL method for ASE, ASA/SQL Anywhere, SQL Server and Oracle database type.

    Supported syntax:

    transaction.SyntaxFromSQL (sqlselect, presentation, err)

    When you use the transaction SyntaxFromSQL method, please note the following differences between PowerBuilder and mobile: The length of return value on mobile is different from PowerBuilder, because the return value on mobile carries the names of all columns while the return value in PowerBuilder does not.

Note: It is unsupported to dynamically create DataWindow if the column alias specified in the SQL has the same name as the column name in the quoted table. For example, if a table t1 has the column c1, c2 and c3, syntax like "SELECT c1= c2* 10, c3 FROM t1" is unsupported.

Methods of dynamically modifying DataWindow properties

  1. Using property expression

    Please refer to DataWindow | DataWindow operators and expressions for detailed information.

  2. Using DataWindow Modify function

    Supported syntax: string dwcontrol.Modify(string modstring)

    When you use the Modify function, be aware of that: If the Modify function is used to modify the WHERE clause of the DataWindow object's SQL SELECT statement, make sure that the new SQL SELECT statement is correct in syntax. Otherwise, syntax errors can occur because PowerBuilder does not validate the statement whereas JavaScript does. In Appeon Web, the Modify function cannot be used to modify the database information.

    You should verify that the data types of both sides of the equal symbol in the expression are compatible.

Adding or deleting controls in DataWindow objects

You can use Modify method to create and destroy the controls in a DataWindow object while noting the following issues.

  1. When you use this method to adding controls in a DataWindow, the name of the controls should not be same.

  2. If it is failed to add or delete a control in the DataWindow object, the error message reported on mobile may be different from in PowerBuilder.