NAME
result -- (ODBC) result NST class container
PROTOTYPE
unitptr result( char *pcSQLunit, char *pcFmt, unitptr uHost)
ARGUMENTS
- char *pcSQLunit
- name of ODBC data_base unit to attach to
- char *pcFmt
- format string
- unitptr uHost
- host unit
RETURN VALUE:
A pointer to the created unit or NULL in the case of an error.
DESCRIPTION:
This class container provides methods to retrieve and store data in
conjunction with the data_base container class.
Each method of this container is implemented as a virtual unit. This
allows a closely cooperation of these units with the desired data_base
class container. The underlaying idea is as follows: The data_base
class container provides the methods to connect to a database and the
result class container provides the methods to actually gain access
to the data via NST/NEO.
NEO CREATION:
When creating the unit, the following parameters have to be specified:
- Name of data_base unit:
- The name of the data_base class container to
attach to.
Further more, the data format has to be specified in a way similar to the
var_units: The interface is arranged as a matrix of buttons (plus an
input field which allows to specify a dimension value). The first button
column controls the creation of input/output fields. If the button is set to
"fld", a new field will be created. A value of "-" causes the creation of
merged fields.
The second column adjusts the NEO pin data types. With this interface, the
binding between columns in a SQL result set and NEO data pins can be
specified. The following types are available:
- float:
- specifies a field containing unpacked floats of dimension iDim.
- float[]:
- a packed float field of dimension iDim (in case of a positive
dimension for that field, the data is expected in different columns of the
result set. A negative value can be used to specify a BLOB datatype).
- int[]:
- analogous to float[]
- char[]:
- analogous to float[]
- string:
- specifies a field containing iDim string pins
- skip:
- The next iDim columns of the result set will not be bound to any
data pin
- date:
- Depending on the value of the "Conversion" button, a date entry will
be converted to the following data types:
- "nop":
- a string containing the date (e.g. "1998-22-06")
- "string":
- same as "nop"
- "day/sec":
- days since 1970-01-01
- "numbers":
- three scalar values: year, month, day
- time:
- Depending on the value of the "Conversion" button, a time entry will
be converted to the following data types:
- "nop":
- a string containing the time (e.g. "11:25:13")
- "string":
- same as "nop"
- "day/sec":
- convert to single scalar: seconds since 00:00:00
- "numbers":
- convert to three scalar values: hours, minutes, seconds
EXAMPLE:
The following SQL statement would select the id, the feature value, the
name, the date and the gray value image data (256x256 pixels) from an image
database:
"select id, feature, name, date, raw_data from images where name like
'hsom%'"
Therefore we would use the following specification to map this data to NEO
data pins:
- float with iDim = 2:
- to map the entries for id and feature into an
unpacked float field.
- string with iDim = 1:
- to map the name of the image to a NST-string.
- date with Conversion="days", iDim=1:
- to convert the date entry to a single
scalar.
- char[] with iDim=-65536:
- to write the BLOB data into an array holding 65536
values between 0 and 255.
DATA TRANSFER POLICY:
The data which is sent by the database server is directly copied into the
pins of the referenced subunit. This is achieved through an ODBC binding
mechanism which considerably speeds up the transfer rate. Therefore, only
the BY_REFERENCE and BY_COPY transfer policies of the use_method_unit
guarantee that the received data is visible at the output pins of the
use_method_unit. So, do _not_ use the BY_REDIRECTION option of the
use_method_unit.
EXECUTION:
Execution of the created unit does nothing. However, execution of
any of its named subunits (via a use_method unit) invokes the
corresponding C++ class method. The interface of each named
subunit provides inputs and outputs for the corresponding method.
NEO INTERFACE AND DESCRIPTION OF NAMED SUBUNITS:
- fetch_next:
-
- Input field 0:
- (string) The SQL query string
- Input field 1:
- (float 1) control pin
- Output field 0 - n:
- (variable) n output fields according to format
specification
- Output field n+1:
- (float 2) current position in result set plus flag
indicating whether end of result set has been reached
Execution: When an SQL query is submitted, the database server executes the
query and returns with a status message. Since it is not desirable that the
server sends the complete result to the client (that could be a megabyte of
data), an index list for each result is kept on the server. fetch_next
sends a message to the server, requesting the next line of the result data
set. If the end of the result set has been reached, the last pin of the last
field is set to 1, otherwise the next row is fetched from the server and
copied to the output pins. As described in the data_base manpage each
statement handle is stored inside a hash table which is indexed by the query
string. fetch_next expects this query to be present at the Input field
0. If the query was already submitted to the database server (using
data_base:query), the string is used to retrieve the desired statement
handle. Otherwise a new connection is made and the query is submitted.
- fetch_all:
-
- Input field 0:
- (string) The SQL query string
- Input field 1:
- (float 1) control pin
- Output field 0 - n:
- (variable) n output fields according to format
specification
- Output field n+1:
- (float 1) current position in result
Does the same as fetch_next, but iterates over all rows of the result set.
- write:
-
- Input field 0 - n:
- (variable) n input fields according to format
specification
- Input field 1:
- (string) String specifying update or insert query
This method can be used to store data into a database. Internally an
"SQLBindParameter" is executed which binds the parameters of the query
string to the input pins of the unit. Therefore the following query
string could be used to store a name plus age into a database:
"insert into ages (name, age) values (?,?)" This would use the table named
"ages" and store the information in the columns "name" and "age".
NST FORMAT SPECIFICATION:
The specification strings are similar to those used by the primitive_unit3
function call. The available data types are:
- s:
- scalar value (NST_SCALAR)
- f:
- (packed) float value (NST_FLOAT)
- i:
- integer value (NST_INT)
- c:
- char value (NST_BYTE)
- t:
- string value (NST_STRING)
- k:
- skip columns of result set
- d:
- date value (not converted, result will be a string: "1998-22-06")
- D:
- date value (convert to single scalar: days since 1970-01-01)
- n:
- date value (convert to three scalars: year, month, day)
- T:
- time value (not converted, result will be a string: "11:25:13")
- S:
- time value (convert to single scalar: seconds since 00:00:00)
- N:
- time value (convert to three scalars: hours, minutes, seconds)
- B:
- blob (binary large object) of type char (NST_BYTE)
- F:
- blob of type float (NST_FLOAT)
- I:
- blob of type integer (NST_INT)
Each data type is preceded by its dimension. For example
the query "select avg_01 avg_02 avg_03 avg_04 avg_05 from averages" will
select the columns named "avg_01" to "avg_05" from the table named "averages".
Thus, the result set will contain 5 columns. A format string %5f would map the
data of these 5 columns into a 5 dim packed float pin. Note, that
there is no mechanism to name the desired output fields, thus the sequence
of field definitions has to reflect the sequence of the columns in the
result set.
If there is no field terminator %< inside the format specification string,
all pins will be merged into one field.
EXAMPLES:
"%n1%<%12f" will generate two fields. The first contains a 3 dim scalar pin
into which an expected date value will be written, the second contains a
12 dim packed float pin.
"%3k%65536B%1t" will generate a packed field containing two pins.
The first 3 columns of the result set will be skipped,
the first pin will be associated with the 4th columns of the result set, which
has to be a BLOB type with a maximum size of 65536 bytes (to store b&w images).
The second pin contains a single string and will be associated with the
5th column of the result set. (Note that there is no need to specify
the size of a string pin. Their sizes are automatically determined by
the unit).
SEE ALSO:
data_base class container
FILE
nst_result.cc