SyntaxFromSQL
PowerScript function
Description
Generates DataWindow source code based on a SQL SELECT
statement.
Applies to
Transaction objects
Syntax
|
1 |
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 |
||
|
presentation |
A string whose value is the default presentation style you
Values for presentationstyle correspond to selected styles (Default) Tabular Grid Form (for freeform) Graph Group Label The Usage section lists the keywords you can use in |
||
|
err |
A string variable to which PowerBuilder will assign any |
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:
|
1 2 3 |
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:
|
1 2 3 4 5 6 |
"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:
|
1 2 3 4 5 6 7 8 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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