Querying Data using ODBC

ScreenSurfer ODBC Support


Page Links	SELECTS, DBSOURCE, DBSELECT, SQL Parameters, TEFETCH, DBCOMMAND, TEDBEXEC

Examples	TEDECLARE DBSOURCE, TEDECLARE DBSOURCE #2, DBSELECT, TEFETCH, DBCOMMAND/TEDBEXEC

Querying, Updating and Inserting Data using ODBC

This section describes the support in ScreenSurfer for the population of template variables from ODBC-based SQL Select statements, as well as the execution of ODBC SQL commands such as INSERT, UPDATE and DELETE.

The purpose of this support is to enable the integration of user or context-driven data with 3270 application data, as well as support such functionality as logging user activity and replicating host updates to departmental databases.

As of Build 1.3.7a of ScreenSurfer, multi-row selects have been implemented by enabling the execution of a SurferScript section connected to the fetching of each row in a result set.

Special Features

The ODBC support in ScreenSurfer implements many performance-related features which will maximize throughput in the server-based environment. They are:

Statement Caching- SQL statements are prepared once, and kept open indefinitely, avoiding the overhead of preparation at each execution
Statement Sharing- Prepared Select statements are shared across all sessions and can utilize a single ODBC connection. This minimizes resource requirements in both the ODBC layer and the target database engine.
Flexible Parameter Support- SQL parameters are supported, enabling a single statement to be re-used regardless of the changing values of parameter data-- this addresses how most database engines optimize SQL execution
Automatic Variable Generation- The Template compiler prepares and analyses each SELECT statement and automatically generates session-scope template variables which match the column names of the result set. This minimizes coding requirements for the template author and enables a highly optimized fetch operation at runtime.
Integration with Template Tracing- The <TEFETCH> and <TEDBEXEC> statements provides full tracing of both input and result-set values when the "Compile Templates for Tracing" option is requested from the Programming Page.
Flexible connection Model- New in 1.3E is the ability to specify a connection is single-threaded and/or session scope. This provides flexibility when connecting to non-thread safe databases such as Oracle 7 as well as providing the ability to give each user session its own connection.

Defining and Fetching ODBC Data

There are three statements involved in retrieving ODBC-based data. They are:

<TEDECLARE DBSOURCE>	Defines and names an ODBC datasource
<TEDECLARE DBSELECT>	Defines and names an SQL SELECT statement--is associated with a DBSOURCE
<TEFETCH>	Executes a SELECT defined in a TEDECLARE DBSELECT statement--also defines any runtime parameters used in the WHERE clause of the SELECT SQL statement

TEDECLARE DBSOURCE Define an ODBC Datasource


<TEDECLARE DBSOURCE
  {[NAME=]"NAME"}
  {[DATASOURCE=]"DATASOURCE"}
  [[USERID=]"USERID"] [[PASSWORD=]"PASSWORD"]
  [[OPTION=]"[READONLY][,MANUALCOMMIT][,SingleThreaded]"
  [[SCOPE=]"[GLOBAL | SESSION]">

Parameters:

NAME (required) Is the template name of the datasource-- this is the name referenced in TEDECLARE DBSELECT statements

DATASOURCE (required) Is the ODBC datasource name, as registered in the ODBC Administrator accessed from the Control Panel

USERID (optional) Optional logon USERID, used to make the ODBC connection for databases with security defined, where the NT service security is not appropriate for the database connection.

PASSWORD (optional) Optional logon PASSWORD, used to make the ODBC connection for databases with security defined

OPTION (optional) Optional settings, currently supporting one, READONLY. Specify Option="ReadOnly" to optimize the connection you are defining for non-updateable queries.

Specify Option="ManualCommit" to disable auto-commit and enable the ability to have more than one SQL statement as part of a unit of work. Note that when you utilize this option, you will need to define both a COMMIT and ROLLBACK DBCOMMAND object in order to properly manage your units of work.

Specify Option="SingleThreaded" To drive all statements "pointing" to this connection through a single thread. This is recommended if your database is non-thread safe. For example, Oracle 7 databases should use this option. The default is multi-threaded, which will connect to the database on one thread, then each defined SQL statement has its own thread that all ScreenSurfer sessions share.

If you specify SCOPE=SESSION, you will also have all database interaction driven through the same thread (SCOPE=SESSION will force SingleThreaded for the connection), but there will be one database thread for each ScreenSurfer screen session.

SCOPE (optional) (Default="GLOBAL") Scope is a new attribute introduced in release 1.3E. By declaring SCOPE="SESSION" a connection to the database will be opened for each ScreenSurfer session started. You should not use this option unless you have a complex database environment involving transactional units-of-work or if you have a large number of concurrent sessions.
When you declare SCOPE="SESSION", each statement (SELECT or COMMAND) which references the subject DBSOURCE will be forced to "SCOPE=SESSION" also, meaning individual statements will be prepared, (opened, executed and closed) for each ScreenSurfer session.
SCOPE="GLOBAL" which is the default will create only one database connection, and individual statements will be opened only once per database. In this mode, the ScreenSurfer sessions "share" each statement through a dedicated thread. While this can impose some performance issues for very heavy database transactional volume, this is compensated by the fact that the database only has one statement thread which keeps making the same request over and over...requests are single-threaded for each statement through one "choke point", but they execute extremely quickly when they do!

Comments:

The TEDECLARE DBSOURCE provides the template developer with a single point for defining a connection to an ODBC database. This connection can then be referenced in more than one TEDECLARE DBSELECT or TEDECLARE DBCOMMAND statements; at runtime, only one connection will be made (default mode, see the SCOPE="Session" attribute, above for creating a connection for every session) and will be shared by the multiple DBSELECT/DBCOMMAND statements. It can occur in any template file, and the naming scope is system-wide (the name should not be prefixed with any template or section names when referencing a DBSOURCE in other template files.

Note-- while you can declare a DBSOURCE in one file, then reference it in another file, the DBSOURCE must be compiled prior to the DBSELECT/DBCOMMAND.

If you experience compile errors due to having DBSELECT or DBCOMMAND statements in a different template file, it is probably due to a sequence problem (the order in which your .STML files are compiled).

If this is the case, you can control the order your template files are compiled through the use of a template list file, or by including all DBSOURCE and DBSELECT statements in a single template file. Note that TEFETCH statements can be compiled in any order relative to the DBSOURCE and DBSELECT declarations, as these are bound to their associated DBSELECT statements at completion of the compile ("fixup" time).

TEDECLARE DBSOURCE Example 1

<TEDECLARE DBSOURCE USER UserProfiles sdoor hyena> <TEDECLARE DBSOURCE NAME="USER" DATASOURCE="UserProfiles" USERID="sdoor" PASSWORD="hyena">
The two examples above (which compile to identical definitions) both declare a DBsource named "User", which will connect to an ODBC datasource registered as "UserProfiles". The userid and password of "sdoor" and "hyena" will be passed respectively at logon time.

TEDECLARE DBSOURCE Example 2

<TEDECLARE DBSOURCE Name="STATS" Datasource="Statistics" Options="UserCommit" Scope="Session">
The above example declares a DBSOURCE named STATS which references the ODBC name "Statistics" and logs on with the default userid and password for ScreenSurfer (may be entered in the services control panel).
STATS will not perform any "auto commits" meaning that there should be a "COMMIT" and "ROLLBACK" command defined for this DBSOURCE, so that each unit-of-work can be explicitly committed in SurferScript.
A STATS connection will be created for each screen session started, at session initialization time. Each screen session will have its own STATS connection.

TEDECLARE DBSELECT Define an SQL SELECT Statement

Syntax:


<TEDECLARE DBSELECT
  {[NAME=]"NAME"}
  {[DBSOURCE=]"DBSOURCE"}
  [[PARENT=]"ParentSelect"]
  {[[SQL=]"SQL"]}
  [NUMERICS="YES"]>

Where:

NAME (required) Is the template name of the SELECT-- this is the name used in TEFETCH statements

DBSOURCE (required) Is the name defined in a TEDECLARE DBSOURCE which will be used by this DBSELECT to connect and retrieved data from an ODBC database

PARENT (optional) SurferScript enables the concept of object-oriented SQL!!! If you specify the PARENT clause in a DBSELECT, you are specifying a subclass of the parent. A subclass DBSELECT shares with the parent: the result set (the automatically generated variables) and the select list (the columns that are to be selected).
When you specify a PARENT in a DBSELECT, your SQL attribute (description follows) starts with the WHERE of the overall SELECT.
Specifying a PARENT in a DBSELECT allows you to specify many SELECT statements while only changing the WHERE clause. This allows you to share a great deal of SurferScript, since all you are changing is the WHERE clause but can then format the results using the same HTML.

SQL (required) The SQL statement used to SELECT data. This may be any valid SELECT statement. Any dynamic input data item is defined using a question mark (such as criteria in a WHERE clause). Note: Parameters in ODBC statements are type sensitive, meaning that you must properly specify the type of each parameter in your SQL. See comments for more information.
Note that if you have specified the PARENT attribute, you only include the WHERE clause in this definition.

NUMERICS (optional, default=NO) By default, the DBSELECT declare will create all text SurferScript session variables, one for each column in select. By setting this attribute to YES, you are requesting that the compiler create SurferScript variables that are as close as possible to the source database types.
For example, an INTEGER database type will be managed in your session variable space as an integer, if this attribute is set to YES.

Comments:

The TEDECLARE DBSELECT statement defines a particular SQL Select statement for use in your templates. You can define both single and multi-row selects (see the TEFETCH tag for information on how to actually execute the select).

See comments for the TEDECLARE DBSOURCE statement regarding the ordering and relationship of DBSOURCE and DBSELECT declarations.

Use of Parameter Markers and Data Typing

ODBC (as well as most SQL implementations) provides the capability of including "parameter markers" in your SQL as a means of optimizing runtime performance and flexibility. A parameter marker is represented in SQL by a question mark ("?"). A parameter marker represents "future data" that the SQL statement will be provided at execution time. By using parameter markers for the dynamic (changing) data, an SQL statement can be prepared only once and "cached" in the template runtime environment for re-use.

Meanwhile, parameter markers in high-level environments such as ScreenSurfer require the developer to get "a little low" and understand the SQL data types involved in the SELECT statement WHERE clause. This is because the ODBC and database SQL engines typically don't support proper datatyping information for parameters at statement preparation time...but require the data types to be exactly right at execution time (a few ODBC drivers at ODBC level 2 provide parameter typing information, but not enough to make it a generally available feature).

Parameter Marker Syntax in ODBC Statements

ScreenSurfer provides a simple extension of the parameter marker syntax to enable the Template Author to properly datatype input parameters so that the database engine receives properly formatted input data at execution time. The syntax is to follow the question mark ("?") with a single letter and an optional sizing value which matches the formats for SQL Data Description Language (DDL). The following table summarizes the appropriate parameter marker syntax for each SQL Data type.

SQL Data Type Parameter Marker Template Expression Type Notes

INTEGER or INT ?I INT Text expressions which are valid numeric values are automatically converted to correct numeric format at execution time. (such as environment or web variables).

SMALLINT ?S INT see above

FLOAT ?F NUMBER see above

DECIMAL ?D
--or--
?D(precision,scale) TEXT If omitted, the parenthesized precision, scale default to 15,2.
Text expressions are used to assure full precision where number of digits can exceed standard floating-point precision

NUMBER ?N
--or--
?N(precision,scale) TEXT see above

CURRENCY ?N
--or--
?N(15,4) TEXT "Currency" is a common datatype in Microsoft Access databases, and translates to a NUMBER(15,4) DDL entry.

CHAR ?C
--or--
?C(size) TEXT If omitted, the parenthesized size defaults to 15.

VARCHAR ?V
--or--
?V(size) TEXT See above.

SQL Data Type	Parameter Marker	Template Expression Type	Notes
INTEGER or INT	?I	INT	Text expressions which are valid numeric values are automatically converted to correct numeric format at execution time. (such as environment or web variables).
SMALLINT	?S	INT	see above
FLOAT	?F	NUMBER	see above
DECIMAL	?D --or-- ?D(precision,scale)	TEXT	If omitted, the parenthesized precision, scale default to 15,2. Text expressions are used to assure full precision where number of digits can exceed standard floating-point precision
NUMBER	?N --or-- ?N(precision,scale)	TEXT	see above
CURRENCY	?N --or-- ?N(15,4)	TEXT	"Currency" is a common datatype in Microsoft Access databases, and translates to a NUMBER(15,4) DDL entry.
CHAR	?C --or-- ?C(size)	TEXT	If omitted, the parenthesized size defaults to 15.
VARCHAR	?V --or-- ?V(size)	TEXT	See above.

DBSELECT Example

******Following is the Data Description Language for a table in ODBC database "Profiles" **** CREATE TABLE PROFILE_TABLE (USER_KEY INTEGER, NAME VARCHAR(40), DEPT INTEGER, EMPID CHAR(8), HIREDATE DATE, DAYBEGIN TIME, DAYEND TIME, PRIMARY KEY (USER_KEY)) ******Following is the SurferScript TEDECLARE statements for selecting a row from the above table ********************** <TEDECLARE DBSOURCE Name="USER" DataSource="Profiles"> <TEDECLARE DBSELECT Name="PROFILE" DBSOURCE="USER" SQL="SELECT * FROM PROFILE_TABLE WHERE USER_KEY=?I"> ******Following is the TEFETCH found inside a section in any template file***** <TEFETCH SELECT="PROFILE" web.userkey>
The above example defines a DBSELECT named "PROFILE", which performs a select of all columns from the table "PROFILE_TABLE" in the datasource defined by the DBSource with a name of "USER". The index of the target table in this example is a column named "USER_KEY" which is defined in the table's DDL as SQL type INTEGER.

Automatic Variable Declarations

When the above statement is compiled, the template compiler will create a number of automatic variables which are always at the session scope. These variables are always TEXT variables, and will have a compound name consisting of the DBSELECT name, a period, and the name of the column in the SELECT statements result set. For the above statement, the following template variable names will be automatically generated:

profile.user_key
profile.name
profile.dept
profile.empid
profile.hiredate
profile.daybegin
profile.dayend
profile.SqlCode
profile.SqlState
profile.SqlRows

When the TEFETCH for the above DBSELECT is executed, the above variables will be populated with the data from the row selected. Note that the "namespace" for these variables is the database namespace, and thus they are referenced in any template file using just the two-part name consisting of the dbselectName.ColumnName form.

Special Columns for Status Information

Note that there are three columns (in italics) that are not part of the subject select statement (profile.sqlcode, profile.sqlstate, and profile.sqlrows). These three columns can provide your template logic with information regarding the status of the fetch:

*SqlCode*	Return code from the ODBC calls. A value of zero means no warnings or errors; 100 indicates that there were no rows matching the selection criteria, a value greater than 0 indicates a warning and a value less than zero indicates an error.
*SqlState*	SqlState is a "universal database return code" designed to provide a cross-database set of result codes. It shares many codes with SqlCode (for example, 0 is good, 100 is not found), but is a TEXT data type, as many of the SqlState codes include text characters. Refer to your database documentation for the meaning of various SQLSTATE codes.
*SqlRows*	Will be 0 if no rows were found, otherwise 1 or more.

TEFETCH Retrieve a row of data to Session Variables

<TEFETCH
   {[SELECT=]SelectName}
   [SECTION=SectionName]
   [MAXROWS=MaxRows]
   [Parm1Expression] [Parm2Expression]
   [...]>

Where:

SELECT (required) Is the template name of the SELECT-- this is the name used in the corresponding TEDECLARE DBSELECT

SECTION (optional) Is the template section name which will execute for each row selected in the <TEFETCH. This enables multiple row fetches, as the section executed can work with each variable in the selected row as it is read from the database.

MAXROWS (optional) When a multi-row SELECT is being executed, you can optionally limit the number of rows processed by specifying a numeric expression for this attribute.

Parm1Expression An expression matching a corresponding parameter marker in the SQL Text for the corresponding DBSELECT.

Comments:

Executes a single-row fetch (or multiple row if SECTION= is specified) for an SQL SELECT defined using a TEDECLARE DBSELECT. Populates the variables automatically generated by the DBSELECT with the contents of the result set column data.

For each parameter defined in the SQL statement for the select (using a "?" parameter marker), a corresponding expression is included in the TEFETCH, following the DBSELECT name. The datatype of each expression will be converted at execution time to its corresponding parameter datatype, so any type of expression may be used.

When there are more than one expressions, expressions should be separated by one or more blanks or carriage returns, and should not include any blanks between tokens (blanks are allowed inside text literals).

TEFETCH Examples

<TEDECLARE DBSELECT Name="UserList" DBSOURCE="USER" SQL="SELECT * FROM PROFILE_TABLE WHERE NAME LIKE ?V(40)"> <TEDECLARE DBSELECT Name="UserDeptList" DBSOURCE="USER" PARENT="UserList" SQL="WHERE Dept=?I"> **** Inside a TESECTION **** <TABLE BORDER> <TR> <TH>Name</TH><TH>Department</TH><TH>Hire Date</TH> </TR> <TEIF web.SearchType="Name"> <TEFETCH SELECT="UserList" SECTION="UserRow" web.NameStart+"%"> <TEELSE> <TEFETCH SELECT="UserDeptList" SECTION="UserRow" web.Dept> </TEIF> </TABLE> **** Further on in same template **** <TESECTION UserRow> <TR> <TD><TESHOW UserList.Name></TD> <TD><TESHOW UserList.Dept></TD> <TD><TESHOW UserList.HireDate></TD> </TR> </TESECTION>
This example uses the database table defined in the third example. It demonstrates a number of techniques including the use of PARENT in a DBSELECT as well as the SECTION in a DBFETCH.
Note that even though there are two DBSELECT statements, they share the same variables (starting with UserList), since the second is a "subclass" of the first. This allows the same SurferScript formatting code to be used for both SELECT statements, even though the WHERE clauses are different.

Executing ODBC Commands such as INSERT, UPDATE and DELETE

There are three statements involved in executing SQL commands using ODBC. They are:

<TEDECLARE DBSOURCE>	Defines and names an ODBC datasource- note that multiple statements can share one DBSOURCE declaration.
<TEDECLARE DBCOMMAND>	Defines and names an SQL UPDATE, INSERT, DELETE or any executable statement--is associated with a DBSOURCE
<TEDBEXEC>	Executes a command defined in a TEDECLARE DBCOMMAND statement--also passes any runtime parameters required by the command.

TEDECLARE DBCOMMAND Define an SQL Statement that performs Data Manipulation

Syntax:


<TEDECLARE DBCOMMAND
  {[NAME=]"NAME"}
  {[DBSOURCE=]"DBSOURCE"}
  [[SQL=]"SQL"]}

Where:

NAME (required) Is the template name of the COMMAND-- this is the name used in TEDBEXEC statements

DBSOURCE (required) Is the name defined in a TEDECLARE DBSOURCE which will be used by this DBSELECT to connect and retrieved data from an ODBC database

SQL (required) The SQL statement used to manipulate the database tables. This is also known as "Data Manipulation Language" or DML. There is a wide variety of DML possible; refer to your database documentation for appropriate syntax.
As with the WHERE clause of the DBSELECT statement, all parameters must use the parameter marker syntax of SurferScript SQL, which is describe above in the SQL Parameters description.

Comments:

The TEDECLARE DBCOMMAND statement defines a particular SQL command for use in your templates. . As with other TEDECLARE tags, this creates a runtime object at runtime; to actually execute the statement you use the TEDBEXEC tag, described in the following section, which includes a DBCOMMAND Example

See comments for the TEDECLARE DBSOURCE statement regarding the ordering and relationship of DBSOURCE and DBSELECT declarations.

TEDBEXEC Execute and SQL Command defined in a TEDECLARE DBCOMMAND

<TEDBEXEC
   {[COMMAND=]CommandName}
   [Parm1Expression] [Parm2Expression][...]>

Where:

COMMAND (required) Is the template name of the COMMAND-- this is the name used in the corresponding TEDECLARE DBCOMMAND

Parm1Expression An expression matching a corresponding parameter marker in the SQL Text for the corresponding DBCOMMAND--you may have as many parameter expressions as are defined for the statement, seperated by at least one "whitespace" character (blank, CRLF or tab).

Comments:

Executes any SQL statement that doesn't return data, such as an INSERT, UPDATE or DELETE, defined using a TEDECLARE DBCOMMAND.

For each parameter defined in the SQL statement for the command (using a "?" parameter marker), a corresponding expression is included in the TEDBEXEC, following the DBCOMMAND name. The datatype of each expression will be converted at execution time to its corresponding parameter datatype, so any type of expression may be used. See Use of Parameter Markers and Data Typing in the DBFETCH section.

When there is more than one expression, expressions should be separated by one or more blanks or carriage returns, and should not include any blanks between tokens (blanks are allowed inside text literals).

DBCOMMAND EXAMPLE

<TEDECLARE DBCOMMAND NAME="AddUser" DBSOURCE="User" SQL="INSERT INTO PROFILE_TABLE (USER_KEY, NAME, DEPT, EMPID, HIREDATE, DAYBEGIN, DAYEND) Values(?I, ?V(40), ?I, ?C(8) ?C(10), ?C(8), ?C(8))"> <TESECTION AddUserToDatabase> <TESET Screen.UserErrors=1>  <TEDBEXEC Command="AddUser" web.key web.name Screen(10,20,5) Screen(11,20,8) Screen(12, 20, 10) Screen(13,20,8) Screen(14,20,8)> <TEIF AddUser.sqlcode==0> <B>User <TESHOW web.name> added successfully...</B> <TEELSE> <B>User <TESHOW web.name> not added due to SQL Error! SQL code=<TESHOW AddUser.sqlcode> ErrorText=<TESHOW Task.ErrorText> </TEIF> </TESECTION>
This example uses the database table defined in the third example. It adds a new row to the user profile table. Note how the source for parameters may come from any valid SurferScript expression, including variables from an HTML form and the currently active screen.
This example introduces the concept of error handling-- you can trap any errors caused by SQL by setting the variable Screen.UserErrors to 1. This will prevent a SurferScript exception from occurring inside any SQL execution statement. This allows you to interrogate the SQLCODE for the statement to check if everything worked as planned.

As with the DBFETCH statement, three special variables are created by the TEDECLARE DBCOMMAND statement and updated by the TEDBEXEC statement. They are:

*CmdName.SqlCode*	Return code from the ODBC calls. A value of zero means no warnings or errors; 100 indicates that there were no rows matching the selection criteria (for an UPDATE or DELETE), a value greater than 0 indicates a warning and a value less than zero indicates an error.
*CmdName.SqlState*	Return code from the ODBC calls. A value of zero means no warnings or errors; 100 indicates that there were no rows matching the selection criteria. Note that this is a "universal" database code that is supposed to be consistant across all database engines, while the SQLCODE is database engine-specific. Refer to your database documentation for relavant SQLSTATE codes.
*CmdName.SqlRows*	Will be 0 if no rows were affected, otherwise the number of affected rows (number of rows deleted/updated and so on).

ER="0">

NAME (required)	Is the template name of the datasource-- this is the name referenced in TEDECLARE DBSELECT statements
DATASOURCE (required)	Is the ODBC datasource name, as registered in the ODBC Administrator accessed from the Control Panel
USERID (optional)	Optional logon USERID, used to make the ODBC connection for databases with security defined, where the NT service security is not appropriate for the database connection.
PASSWORD (optional)	Optional logon PASSWORD, used to make the ODBC connection for databases with security defined
OPTION (optional)	Optional settings, currently supporting one, READONLY. Specify Option="ReadOnly" to optimize the connection you are defining for non-updateable queries. Specify Option="ManualCommit" to disable auto-commit and enable the ability to have more than one SQL statement as part of a unit of work. Note that when you utilize this option, you will need to define both a COMMIT and ROLLBACK DBCOMMAND object in order to properly manage your units of work. Specify Option="SingleThreaded" To drive all statements "pointing" to this connection through a single thread. This is recommended if your database is non-thread safe. For example, Oracle 7 databases should use this option. The default is multi-threaded, which will connect to the database on one thread, then each defined SQL statement has its own thread that all ScreenSurfer sessions share. If you specify SCOPE=SESSION, you will also have all database interaction driven through the same thread (SCOPE=SESSION will force SingleThreaded for the connection), but there will be one database thread for each ScreenSurfer screen session.
SCOPE (optional) (Default="GLOBAL")	Scope is a new attribute introduced in release 1.3E. By declaring SCOPE="SESSION" a connection to the database will be opened for each ScreenSurfer session started. You should not use this option unless you have a complex database environment involving transactional units-of-work or if you have a large number of concurrent sessions. When you declare SCOPE="SESSION", each statement (SELECT or COMMAND) which references the subject DBSOURCE will be forced to "SCOPE=SESSION" also, meaning individual statements will be prepared, (opened, executed and closed) for each ScreenSurfer session. SCOPE="GLOBAL" which is the default will create only one database connection, and individual statements will be opened only once per database. In this mode, the ScreenSurfer sessions "share" each statement through a dedicated thread. While this can impose some performance issues for very heavy database transactional volume, this is compensated by the fact that the database only has one statement thread which keeps making the same request over and over...requests are single-threaded for each statement through one "choke point", but they execute extremely quickly when they do!

NAME (required)	Is the template name of the SELECT-- this is the name used in TEFETCH statements
DBSOURCE (required)	Is the name defined in a TEDECLARE DBSOURCE which will be used by this DBSELECT to connect and retrieved data from an ODBC database
PARENT (optional)	SurferScript enables the concept of object-oriented SQL!!! If you specify the PARENT clause in a DBSELECT, you are specifying a subclass of the parent. A subclass DBSELECT shares with the parent: the result set (the automatically generated variables) and the select list (the columns that are to be selected). When you specify a PARENT in a DBSELECT, your SQL attribute (description follows) starts with the WHERE of the overall SELECT. Specifying a PARENT in a DBSELECT allows you to specify many SELECT statements while only changing the WHERE clause. This allows you to share a great deal of SurferScript, since all you are changing is the WHERE clause but can then format the results using the same HTML.
SQL (required)	The SQL statement used to SELECT data. This may be any valid SELECT statement. Any dynamic input data item is defined using a question mark (such as criteria in a WHERE clause). *Note: Parameters in ODBC statements are type sensitive, meaning that you must* properly specify the type of each parameter in your SQL. See comments for more information.** Note that if you have specified the PARENT attribute, you only include the WHERE clause in this definition.
NUMERICS (optional, default=NO)	By default, the DBSELECT declare will create all text SurferScript session variables, one for each column in select. By setting this attribute to YES, you are requesting that the compiler create SurferScript variables that are as close as possible to the source database types. For example, an INTEGER database type will be managed in your session variable space as an integer, if this attribute is set to YES.

SELECT (required)	Is the template name of the SELECT-- this is the name used in the corresponding TEDECLARE DBSELECT
SECTION (optional)	Is the template section name which will execute for each row selected in the <TEFETCH. This enables multiple row fetches, as the section executed can work with each variable in the selected row as it is read from the database.
MAXROWS (optional)	When a multi-row SELECT is being executed, you can optionally limit the number of rows processed by specifying a numeric expression for this attribute.
*Parm1Expression*	An expression matching a corresponding parameter marker in the SQL Text for the corresponding DBSELECT.

NAME (required)	Is the template name of the COMMAND-- this is the name used in TEDBEXEC statements
DBSOURCE (required)	Is the name defined in a TEDECLARE DBSOURCE which will be used by this DBSELECT to connect and retrieved data from an ODBC database
SQL (required)	The SQL statement used to manipulate the database tables. This is also known as "Data Manipulation Language" or DML. There is a wide variety of DML possible; refer to your database documentation for appropriate syntax. As with the WHERE clause of the DBSELECT statement, all parameters must use the parameter marker syntax of SurferScript SQL, which is describe above in the SQL Parameters description.

COMMAND (required)	Is the template name of the COMMAND-- this is the name used in the corresponding TEDECLARE DBCOMMAND
*Parm1Expression*	An expression matching a corresponding parameter marker in the SQL Text for the corresponding DBCOMMAND--you may have as many parameter expressions as are defined for the statement, seperated by at least one "whitespace" character (blank, CRLF or tab).