SyntaxFromSQL PowerScript function
Description
Generates DataWindow source code based on a SQL SELECT statement.
Controls
Transaction objects
Syntax
|
1 |
<span>transaction</span><span>.SyntaxFromSQL</span> ( <span>sqlselect</span>, <span>presentation</span>, <span>err</span> ) |
|
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
Values for presentationstyle correspond
The Usage section lists the keywords you can use in presentation. |
||
|
err |
A string variable to which PowerBuilder |
Return Values
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 |
sqlca.autocommit=TRUE |
|
1 |
ls_dws=sqlca.syntaxfromsql (sqlstmt, presentation, err) |
|
1 |
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 |
"Style ( Type=<span>value property</span>=<span>value</span> ... ) |
|
1 |
DataWindow ( <span>property</span>=<span>value</span> ... ) |
|
1 |
Column ( <span>property</span>=<span>value</span> ... ) |
|
1 |
Group <span>groupby_colnum1</span> <span>Fby_colnum2</span> ... <span>property</span> ... ) |
|
1 |
Text <span>property</span>=<span>value</span> ... ) |
|
1 |
Title ( '<span>titlestring</span>' )" |
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
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 |
string ERRORS, sql_syntax |
|
1 |
|
1 |
sql_syntax = "SELECT emp_data.emp_id," & |
|
1 |
+ "emp_data.emp_name FROM emp_data " & |
|
1 |
+ "WHERE emp_data.emp_salary >45000" |
|
1 |
|
1 |
mle_sql.text = & |
|
1 |
SQLCA<span>.SyntaxFromSQL</span>(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 |
string ERRORS, sql_syntax |
|
1 |
string presentation_str, dwsyntax_str |
|
1 |
|
1 |
sql_syntax = "SELECT emp_data.emp_id,"& |
|
1 |
+ "emp_data.emp_name FROM emp_data "& |
|
1 |
+ "WHERE emp_data.emp_salary > 45000" |
|
1 |
|
1 |
presentation_str = "style(type=grid)" |
|
1 |
|
1 |
dwsyntax_str = SQLCA.<span>SyntaxFromSQL</span>(sql_syntax, & |
|
1 |
presentation_str, ERRORS) |
|
1 |
|
1 |
IF Len(ERRORS) > 0 THEN |
|
1 |
MessageBox("Caution", & |
|
1 |
"SyntaxFromSQL caused these errors: " + ERRORS) |
|
1 |
RETURN |
|
1 |
END IF |
|
1 |
|
1 |
dw_1.Create( dwsyntax_str, ERRORS) |
|
1 |
|
1 |
IF Len(ERRORS) > 0 THEN |
|
1 |
MessageBox("Caution", & |
|
1 |
    "Create cause these errors: " + ERRORS) |
|
1 |
RETURN |
|
1 |
END IF |
See Also
-
Create method
for DataWindows in the DataWindow Reference or online Help -
Information on DataWindow object properties in the DataWindow
Reference