(4 out of 9)
DSQL Objects
Dynamic SQL allows the user to bind GUI objects to vendor-specific SQL objects called DSQL.
DSQL objects are defined by the database vendor being used. For instance, Transact SQL would be used for Sybase, and PL/SQL and standard Oracle SQL would be used for Oracle.
- Note: If the developer uses strictly ANSI SQL, then DSQL Objects will be portable between database vendors.
How DSQL Objects are Managed
DSQL objects are saved on your hard disk by using a naming convention described below. Sapphire/Web needs to know where to read and write DSQL Objects at start-up. If the environment variable DSQLDIR is set to point to a directory path, Sapphire/Web will look in that path. The DSQLDIR environment variable is useful for sharing DSQL objects between multiple developers.
If the DSQLDIR environment variable is not set, Sapphire/Web will look in the project's directory.
DSQL File Naming Convention
DSQL objects are saved using the following naming conventions:
Sybase:
server,database,object_name,S
where
server
- is the Sybase server name
database
- is the name of the Sybase database
object_name
- is the name of the DSQL object
S
- vendor_id
Oracle:
ORACLE,connection_string,object_name,O
where
ORACLE
- is a constant for all Oracle DSQL objects
connection_string
- Oracle's database connection string. This can be in any of the following forms (see your Oracle reference manuals for details):
- alias
- @T:hostname:sid
- @T:ip_address:sid
- the value of the environment variable ORACLE_SID
object_name
- the name of the DSQL object
O
- vendor_id
Informix:
server,database,object_name,I
where
server
- the Informix server name
database
- the name of the Informix database
object_name
- the name of the DSQL object
I
- is the vendor_id
Manually Modifying Files
The DSQL files can be created and modified manually, although it is not recommended. The reason is the tool parses the DSQL object when you save it and therefore finds any syntax errors from the database or from the placeholder specification. If there is an error in a manually created/modified DSQL file, the tool will not be able to load the object into the Object Bind Editor.
Binding DSQL Objects vs. Stored Procedures
Binding DSQL objects is equivalent to binding Stored Procedures except that there is no return status (There is no return status for Oracle procedures either). The bindings consist of Activators, Arguments (placeholders), and results data.
How to Create DSQL Objects
To create a DSQL object, use the Object Editor. The Object Editor is also used to create and modify Stored Procedures.
Placeholders
DSQL uses placeholders to facilitate user input, WHERE CLAUSE support, and dynamic SQL capabilities. When creating DSQL objects, type in your SQL statement(s), leaving room for placeholders. To mark a placeholder, use the following format:
#id, type [, default]#
- where:
#
- denotes begin placeholder and end placeholder
id
- any name by which you wish to refer to this placeholder
type
- the native vendor's data type (No data type dimensions are necessary) or a type SPECIFIER
default
- An optional value to be used when parsing Sybase DSQL objects and retrieving meta data. Default values should never be quote wrapped. Sometimes there are convention conflicts with values used as dummy arguments when parsing¯that cause loading and saving DSQL objects to fail. If a conflict occurs, make the default value something that complies with the convention.
Type Specifiers
Type specifiers tell Sapphire/Web if a placeholder should be quote wrapped when the DSQL string is assembled.
Type specifiers may also be the native database vendor type. However, user-defined data types will be unknown. In this case, use the generic type specifiers.
The type specifiers are not case sensitive.
Known Sybase Datatypes
* Sybase System 10 data types
Known Oracle Datatypes
Known Informix Datatypes
When DSQL Objects Get Parsed
When DSQL objects are saved, a dummy DSQL statement is assembled and parsed. Default values are used for the placeholders so that the dummy string will be valid. This dummy statement, when executed, will tell Sapphire/Web what the resulting data will look like. Stored Procedures get executed as well to determine the result column information.
Example 1:
The following two DSQL objects are equivalent:
SELECT * FROM emp WHERE emp_id = #empnum, NUMBER#
SELECT * FROM emp WHERE emp_id = #empnum, NO_WRAP#
The dummy string would look like this in either case:
SELECT * FROM emp WHERE emp_id = 0
Example 2:
The following two DSQL objects are equivalent:
SELECT * FROM titles WHERE title = #booktitle, VARCHAR#
SELECT * FROM titles WHERE title = #booktitle, WRAP#
The dummy string would look like this in either case:
SELECT * FROM titles WHERE title = '0'
Example 3:
The following DSQL objects are equivalent:
SELECT * FROM a_table WHERE date = #a_date, datetime#
SELECT * FROM a_table WHERE date = #a_date, DATE#
The dummy string would look like this in either case:
Sybase
SELECT * FROM a_table WHERE date = getdate()
Oracle
SELECT * FROM a_table WHERE date = SYSDATE
Informix
SELECT * FROM a_table WHERE date = TODAY
Example 4:
NO_PARSE is useful for SQL fragments which the developer wishes to generate at run time. However, if you use this type specifier in the select list, the execution results may not match the bindings you performed. This is because new select list items would not be known at design time, and therefore cannot be bound ahead of time. However, your application can take care of this itself by extracting the data from the population callback structure and populating the HTML template.
SELECT * FROM players #where_clause, NO_PARSE#
The dummy string would look like this:
SELECT * FROM players
Example 5:
SELECT * FROM publishers where pub_id = #id, WRAP, 9999#
The dummy string would look like this:
SELECT * FROM publishers where pub_id = `9999'
Because there is a rule associated with pub_id that limits valid pub_id values to 9900-9999 and three arbitrary numbers, the above DSQL object would use the default value 9999 when creating dummy strings.
Arguments vs. Placeholders
Remember, all of the placeholders will show up in the Object Bind Editor as Arguments. The argument name will be the id you have specified for the placeholder and the data type will be the type you specified for the placeholder.
Database Vendors and DSQL
DSQL objects must be defined differently based upon the limitations/features of different Database vendors. Here is a summary:
Sybase
- Sybase allows any valid Transact SQL statements or batches to be used as DSQL objects. Anything accomplished by a Sybase stored procedure can be done in a DSQL object with the use of placeholders instead of arguments.
Oracle
- Oracle DSQL can contain any single SQL statement or any PL/SQL block.
- Each PL/SQL block must be wrapped with BEGIN and END.
- The single SQL statements may be select, insert, update or delete statements.
- Oracle should never use a `;' at the end of single statements.
- Oracle PL/SQL blocks limit their use of the select statements to SELECT INTO and therefore, row-producing select statements may only be used in DSQL objects.
- Since Oracle Call Interfaces and Pro*C do not support multiple SQL statement processing in a single database request, DSQL objects also have this limitation.
Informix
- Informix DSQL can contain any single SQL statement.
- The single SQL statements may be select, insert, update or delete statements.
- Informix should never use a `;' at the end of single statements.
- Since Informix-ESQL/C does not support multiple SQL statement processing in a single database request, DSQL objects also have this limitation.
(4 out of 9)