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.

Returns

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 groupby_colnum1 Fby_colnum2 ... property ... )

   Text property=value ... )

   Title ( 'titlestring' )"

The checklists in the DataWindow object properties chapter in the 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

Example 1

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)

Example 2

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