HomePowerBuilderAppeon Documentation Center

SyntaxFromSQL

Description

Generates DataWindow source code based on a SQL SELECT statement.

Applies to

Transaction objects

Syntax

transaction.SyntaxFromSQL ( sqlselect, presentation, err )

Argument

Description

transaction

The name of a connected transaction object.

sqlselect

A string whose value is a valid SQL SELECT statement.

presentation

A string whose value is the default presentation style you want for the DataWindow. The simple format is: 

Style(Type=presentationstyle)

Values for presentationstyle correspond to selected styles in the New DataWindow dialog box in the DataWindow painter. Keywords are:

(Default) Tabular

Grid

Form (for freeform)

Graph

Group

Label

The Usage section lists the keywords you can use in presentation.

err

A string variable to which PowerBuilder will assign any error messages that occur.


Return value

String.

Returns the empty string ("") if an error occurs. If SyntaxFromSQL fails, err may contain error messages if warnings or soft errors occur (for example, a syntax error). If any argument's value is null, SyntaxFromSQL returns null.

Usage

To create a DataWindow object, you can pass the source code returned by SyntaxFromSQL directly to the Create function.

Table owner in the SQL statement

If the value of the LogID property of the Transaction object is not the owner of the table being accessed in the SQL statement for the SyntaxFromSQL function, then the table name in the SQL SELECT statement must be qualified with the owner name.

Note for Adaptive Server Enterprise

If your DBMS is Adaptive Server Enterprise and you call SyntaxFromSQL, PowerBuilder must determine whether the tables are updatable through a unique index. This is only possible if you set AutoCommit to true before calling SyntaxFromSQL, as shown here: 

sqlca.autocommit=TRUE
ls_dws=sqlca.syntaxfromsql (sqlstmt, presentation, err)
sqlca.autocommit=FALSE

The presentation string can also specify object keywords followed by properties and values to customize the DataWindow. You can specify the style of a column, the entire DataWindow, areas of the DataWindow, and text in the DataWindow. The object keywords are:

Column

DataWindow

Group

Style

Text

Title

A full presentation string has the format:

"Style ( Type=value property=value ... )
   DataWindow ( property=value ... )
   Column ( property=value ... )
   Group (colnum1 colnum2 ... property ... ) //Effective only when Type=Group
   Text (property=value ... )
   Title ( 'titlestring' )"

The checklists in DataWindow Object Properties in DataWindow Reference identify the properties that you can use for each object keyword.

If a database column has extended attributes with font information, then font information you specify in the SyntaxFromSQL presentation string is ignored.

Examples

The following statements display the DataWindow source for a tabular DataWindow object generated by the SyntaxFromSQL function in a MultiLineEdit.

If errors occur, PowerBuilder fills the string ERRORS with any error messages that are generated: 

string ERRORS, sql_syntax
 
sql_syntax = "SELECT emp_data.emp_id," &
   + "emp_data.emp_name FROM emp_data " &
   + "WHERE emp_data.emp_salary >45000"
 
mle_sql.text = &
   SQLCA.SyntaxFromSQL(sql_syntax, "", ERRORS)

The following statements create a grid DataWindow dw_1 from the DataWindow source generated in the SyntaxFromSQL function. If errors occur, the string ERRORS contains any error messages that are generated, which are displayed to the user in a message box. Note that you need to call SetTransObject with SQLCA as its argument before you can call the Retrieve function: 

string ERRORS, sql_syntax
string presentation_str, dwsyntax_str
 
sql_syntax = "SELECT emp_data.emp_id,"&
   + "emp_data.emp_name FROM emp_data "&
   + "WHERE emp_data.emp_salary > 45000"
 
presentation_str = "style(type=grid)"
 
dwsyntax_str = SQLCA.SyntaxFromSQL(sql_syntax, &
   presentation_str, ERRORS)
 
IF Len(ERRORS) > 0 THEN
   MessageBox("Caution", &
   "SyntaxFromSQL caused these errors: " + ERRORS)
   RETURN
END IF
 
dw_1.Create( dwsyntax_str, ERRORS)
 
IF Len(ERRORS) > 0 THEN
   MessageBox("Caution", &
      "Create cause these errors: " + ERRORS)
   RETURN
END IF

See also

Create method for DataWindows in the section called “Create” in DataWindow Reference.

Information on DataWindow Object Properties in DataWindow Reference

Prev  Up  Next
Synchronize  Home  SystemRoutine