HomeAppeon Web | Appeon Mobile | Appeon PowerBuilderAppeon Online Help Center

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 258. 

    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 PowerServer or redeploy the application to disable the caches.

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

    • The length of return value on the Web is different from PowerBuilder, because the return value on the Web 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 the Web, please note that the generated DataWindow contains no primary key information and cannot be updated on the Web 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).

Executing SaveAs function for dynamically created DataWindows

  1. A dynamic DataWindow can only be saved as TXT, HTML or XLS file. Saving it to WMF will give the error "DataObject information does not exist". The reason is that SaveAs to TXT, HTML or XLS is directly executed at the client while SaveAs to other formats is executed by the server. PowerServer cannot save the DataWindow since it cannot get the dynamically-created SQL.

  2. Timestamp data type is unsupported when dynamically creating DataWindows.

  3. It is unsupported to have stored procedure with input parameters as the data source when dynamically creating DataWindow.

  4. It is unsupported to use EXECUTE statement to execute the SQL when dynamically creating DataWindows.

    For example, the following script is unsupported:

    Execute ('select * from employee') //Unsupported

  5. If the DataWindow object is created dynamically, the SetSQLSelect function is unsupported.

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, 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 Web: The length of return value on the Web is different from PowerBuilder, because the return value on the Web 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)

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

    When you use the Modify function, be aware of that:

    1) 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 PowerServer Web, the Modify function cannot be used to modify the database information.

    2) When executing Modify function to change the dddw.property on the Web, properties must be separated with spaces when they are concatenated into one value. For example,

    Unsupported code:

    dw_1.Modify = "emp_id.BackGround.Color=" + String(RGB(192,192,192)) + "emp_id.Protect=1"

    Supported code:

    dw_1.Modify = "emp_id.BackGround.Color=" + String(RGB(192,192,192)) + " emp_id.Protect=1"

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 add 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 the Web may be different from in PowerBuilder.

Prev  Up  Next
Displaying and validating data  Home  DataWindow operators and expressions