SyntaxFromSQL PowerScript function
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