WebDNA Software

Technical References - [SQL]

Performs a SQL statement on an ODBC data source.

numero = 212
interpreted = N
texte = [SQL Statement]Found Items[/SQL]To search through an ODBC-compliant database (or add new records, or delete or replace records,) place a [SQL] context into a WebDNA template. You may specify any dsn (Data Source Name) that has been properly configured through the ODBC setup control panel on the web server computer.The [SQL] context is not limited to searching -- you may perform any legal SQL statement, such as SELECT, INSERT, DROP, etc. The SQL language is too broad to describe here; it is assumed you have a working knowledge of SQL before using this context.Example:

[SQL dsn=Pubs&statement=SELECT * from Authors]Found [numfound] items[founditems][au_lname], [au_fname], [title][/founditems][/SQL]

Whenever WebDNA encounters a [SQL] context, it uses ODBC to attempt to make a connection to the DSN you specify. It then executes the SQL statement and retrieves the results, if any. For SQL SELECT statements, you almost always put a [founditems]...[/founditems] context inside the [SQL] context so you can display the information from the matching records.

you can substitute any [xxx] tags in the SQL parameters, as in [SQL dsn=Pubs&statement=SELECT * from Authors where au_lname > '[form.name]'].

UNIX users can get infomation on how to setup WebDNA with ODBC

Parameter	Description
dsn	Name of the database you wish to search.
username	Username required to access this ODBC database (Optional for Window platform, if none specified, "sa" is used).
password	Password required to access this ODBC database (Optional for Window platform, if none is specified, a blank password is used).
statement	Any legal SQL statement.
max	A number indicating how many records should be displayed at once before showing a list of "Show Items xx-yy" hyperlinks.

The following tags are available inside a [SQL] context:

Tag	Description
[NumFound]	A number indicating how many records matched the search request. Some ODBC drivers do not support this feature, so WebDNA compensates by visiting every record in the database in order to count them. For large datasets, this can be very slow, and you should consider writing a SQL statement that performs a count instead. Do not put [NumFound] inside a SQL context that INSERTS new records, because the statement will be executed twice in order to perform the count. This will cause invalid a second record to be added to your database.
[founditems]...[/founditems]	Normally you put a [founditems] loop inside a [SQL] context that has performed a SELECT statement, so you can display all the matching records. You can put any database field names inside the [founditems] loop to display them in HTML.

Tag

Description

[NumFound]

A number indicating how many records matched the search request. Some ODBC drivers do not support this feature, so WebDNA compensates by visiting every record in the database in order to count them. For large datasets, this can be very slow, and you should consider writing a SQL statement that performs a count instead. Do not put [NumFound] inside a SQL context that INSERTS new records, because the statement will be executed twice in order to perform the count. This will cause invalid a second record to be added to your database.

[founditems]...[/founditems]

Normally you put a [founditems] loop inside a [SQL] context that has performed a SELECT statement, so you can display all the matching records. You can put any database field names inside the [founditems] loop to display them in HTML.

[SQL Statement]Found Items[/SQL]

To search through an ODBC-compliant database (or add new records, or delete or replace records,) place a [SQL] context into a WebDNA template. You may specify any dsn (Data Source Name) that has been properly configured through the ODBC setup control panel on the web server computer.

The [SQL] context is not limited to searching -- you may perform any legal SQL statement, such as SELECT, INSERT, DROP, etc. The SQL language is too broad to describe here; it is assumed you have a working knowledge of SQL before using this context.

Example:

[SQL dsn=Pubs&statement=SELECT * from Authors]
Found [numfound] items
[founditems]
[au_lname], [au_fname], [title]
[/founditems]
[/SQL]