Databases & Tables
WebDNA can be used to construct sophisticated database searches, resulting in formatted output, without the need for connecting to a separate database such as SQL. WebDNA uses its own database system of tab-delimited text files saved on disk but operated in RAM. Once the database is loaded into RAM, all subsequent searches are performed on that RAM data, which is much faster than searching data stored on disk. All changes to the RAM data are written back to disk with a separate process so that the disk data remains synced with RAM.
[append] context
[append db=base.db]cust=Grant&address=1492 High Street&zip=9000[/append]Parameters
| Parameter | Description |
|---|---|
| autonumber | Instructs WebDNA to automatically generate the 'next highest number' value for the given fieldname. |
[append db=base.db&autonumber=cust_id]cust=Grant&address=1492 High Street&zip=9000[/append]The database "base.db" is opened, and a new record is added to the end. The field name "cust" is set to "Grant," the field name "address" is set to "1492 High Street" the field name "zip" is set to "9000," and the field name "date" is set to the current date.
Any field names not existing in the database are ignored, and any fields you do not specify are left blank in the new record. Certain letters are illegal, such as <tab> or <carriage return>, so they are converted to <space> and <soft return> before being added to the database. Some computers use the two-character sequence <carriage return><line feed> to indicate a single end of line, which is automatically converted to a single <soft return> character before being added to the database.
You may specify an absolute or partial path to the database file, as in "/WebCatalog/folder/base.db" or "../base.db". Relative paths start in the same folder as the template, just like URLs, so "../" will look "up" one folder level from the template, and "/" will start at the web server's root.
Normally all database file paths are relative to the local template, or if they begin with "/" they are relative to the web server's virtual host root. You may optionally put "^" in front of the file path to indicate the file can be found in a global root folder called "Globals" inside the WebCatalogEngine folder. This global root folder is the same regardless of the virtual host.
You can specifiy a WebDNA [table], in place of a db file, in the Append context.
For example: [append table=TableName]values[/append]
You can use [thisautonumber] tag from within an [append] or [replace] context, to retrieve the current auto-generated number if the AUTONUMBER parameter was used.
Some database filenames are reserved.
You may not create your own database named "WebCatalog Prefs," "Users.db," "ErrorMessages.db," "StandardConversions.db," or "Triggers.db"
[replace] context
[replace Parameters]new values[/replace]Parameters
| Parameter | Description |
|---|---|
| db | path to database file, relative to this template. |
| table | In place of a db file, you can specify a named reference to a WebDNA table object. |
| SearchParameters | Search information describing which records should be found and replaced. Can be any complex search criteria, works exactly like [search] context. |
| Append | (Optional) "T" if you want a new record to be added to the end of the database in the case where no records were found to be replaced. Any fields you do not specify are left blank in the new record. |
| autonumber | (Optional) Instructs WebDNA to automatically generate the 'next highest number' value for the given fieldname. |
To replace records in an ODBC-compliant table controlled by a SQL server, use the [SQL] context.
You can specifiy a WebDNA table, in place of a db file, in the Replace context.
For example: [replace table=TableName&...]values[/replace]
Example WebDNA code:
[replace db=somebase.db&eqNAMEdata=Grant]name=John&address=1492 Street&zip=9000&date=[date][/replace]The database "somebase.db" opens and all records whose name field is "Grant" are found. The field names "name" is set to "John," "address" is set to "1492 Street" "zip" is set to "9000," and "date" is set to the current date. Notice that any WebDNA [xxx] tags inside the context are first substituted for their real values before being written to the database. The name of the database itself may also be an [xxx] tag, as in [replace db=[formvariable]][/replace].
Any field names not existing in the database are ignored, and if you leave some existing field names out of the replace context, they will remain unchanged in the database. Certain letters are illegal, such as TAB or CARRIAGE RETURN, so they are converted to SOFT TAB and SOFT RETURN before being added to the database. Some computers use the two-character sequence CARRIAGE RETURN - LINE FEED to indicate a single end of line, which is automatically converted to a single SOFT RETURN character before being added to the database. These 'soft' characters are automatically converted back to 'hard' versions (the originals) whenever you retrieve fields from a search of the database.
You may specify an absolute or relative path to the database file, as in "/WebCatalog/GeneralStore/somebase.db" or "../somebase.db".
Normally all database filepaths are relative to the local template, or if they begin with "/" they are relative to the web server's virtual host root (MacOS and Unix versions only; PC versions treat the DBServer.exe folder as root regardless of the virtual host). You may optionally put "^" in front of the file path to indicate the file can be found in a global root folder called "Globals" inside the WebCatalogEngine folder. This global root folder is the same regardless of the virtual host.
You can use the 'AUTONUMBER=' parameter with the [append] or [replace] context to instruct WebDNA to automatically generate the 'next highest number' value for the given fieldname. This is useful for 'ID' type fields, where unique values are required.Here is a demonstration of the AUTONUMBER feature using a WebDNA table, this will also work on a WebDNA database field.
Example WebDNA code:
[table name=table_1&fields=ID,NAME,EMAIL][/table]
[append table=table_1&AUTONUMBER=ID]NAME=Scott&EMAIL=scott@here.com[/append]
[append table=table_1&AUTONUMBER=ID]NAME=Lee&EMAIL=lee@there.com[/append]
[append table=table_1&AUTONUMBER=ID]NAME=OMNI&EMAIL=omni@everywhere.com[/append]
[delete table=table_1&eqIDdata=2]
[append table=table_1&AUTONUMBER=ID]NAME=Lee&EMAIL=lee@there.com[/append]
[search table=table_1&neIDdata=[blank]]
[founditems]
[ID] - [NAME] - [EMAIL]
[/founditems]
[/search]
Results:
1 - Scott - scott@here.com
3 - OMNI - omni@everywhere.com
4 - Lee - lee@there.com
[thisautonumber] tag can be used from within an [append] or [replace] context, to retrieve the current auto-generated number, if the AUTONUMBER parameter was used.
[delete] tag
[delete db=some.db&eqNAMEdata=Fred]if the database has username and password fields, then the records will not be deleted unless the visitor's web browser username/password match the record's username/password.
You can specifiy a WebDNA table, in place of a db file.
For example: [delete table=TableName&...].
[exclusivelock] WebDNA v4.0 context
[exclusivelock database list]...WebDNA...[/exclusivelock]Example WebDNA code:
[exclusivelock db=orders.db&db=lineitems.db&db=accesslog.db]
...search, delete, or modify any of orders.db, lineitems.db, or accesslog.db while being assured that no other threads can modify any of these databases until the closing /exclusivelock tag is reached.
[/exclusivelock][lastautonumber] WebDNA v5.1+ tag
Example WebDNA code:
[append db=mydata.db&autonumber=ID]name=Jack&lastname=Jones[/append]
The last autonumber in mydata.db = [lastautonumber]
[closedatabase] tag
[closedatabase db=mydata.db]WebDNA automatically closes databases when it needs more memory, so you typically do not need to use this tag.
Adding 'commit=f' as [closedatabase db=mydata.db&commit=f] will cause the specified database file to be closed but not written to disk, any unsaved changes will be lost
[commitdatabase] tag
[commitdatabase db=mydata.db][flushdatabases] tag
[flushdatabases]It is important to understand that writing all your databases to disk might take several seconds, depending on the size of your databases and the speed of your disk. In most cases, [commitdatabase] is more appropriate.
[addfields] WebDNA v5.0 context
[addfields db=...]...WebDNA...[/addfields]Example WebDNA code:
[closedatabase db=addfields_test.db]
[writefile file=addfields_test.db]ID NAME
1 Scott
2 Rusty
3 David
4 Daniel
5 Dustin
[/writefile]
[search db=addfields_test.db&neIDdata=[blank]&rank=off]
[founditems]
[ID] - [NAME]<br>
[/founditems]
[/search]1 - Scott
2 - Rusty
3 - David
4 - Daniel
5 - Dustin
Now, let's use the [addfields] context to add EMAIL and PHONE fields to the 'addfields_test.db' database, initializing the new field value with some arbitrary data. We will again perform a simple search on the same database to confirm that the new fields, and field data, have been added.
Example WebDNA code:
[addfields db=addfields_test.db]EMAIL=us@com&PHONE=12-1235[/addfields]
[search db=addfields_test.db&neIDdata=[blank]&rank=off]
[founditems]
[ID] - [NAME] - [EMAIL] - [PHONE]<br>
[/founditems]
[/search]1 - Scott - us@com - 12-1235
2 - Rusty - us@com - 12-1235
3 - David - us@com - 12-1235
4 - Daniel - us@com - 12-1235
5 - Dustin - us@com - 12-1235
the [addfields] context will not add fieldnames that already exist in the target database.
[addfields] will not add more than 4 fields at one time; in the following example, the "fifth" field will not be added
[addfields db=test.db]address=[blank]&website=[blank]&brochure=[blank]&Home=[blank]&fifth=[blank][/addfields][deletefields] feature has been added in version 8.6
[deletefields db=test.db&fieldnames=column1,column4]
[listdatabases] context
[listdatabases] Database Tags [/listdatabases]The following tags are available inside a [listdatabases] context:
| Tag | Description |
|---|---|
[name] | the name of the database (can be a partial or full path). |
| [index] | A number from 1 to the total number of databases, indicating this database's index placement in the list. |
| [numrecords] | The number of records (rows) in this database. |
| [numfields] | The number of fields (columns) in this database. |
| [memoryusage] | Woukd show the RAM usage for a specific database. [format thousands 0d][math][memoryusage]/1024[/math][/format]K |
| [xxx] | Any other WebDNA tag or context, including [ListFields] [listfields path=[name]][fieldname], [/listfields] |
| [break] | From version 8.1, if the [listdatabases] context sees the [break] tag while executing a loop, it will stop looping, once it finishes the current loop. Thus the [break] tag should only appear in a [showif] statement that is evaluated at the end (bottom) of the loop. |
[listdatabases]
Name:[name]<br>
# Records:[numrecords]<br>
[/listdatabases][listfields] context
[listfields Params] Database Tags [/listfields]The following tags are available inside a [listfields] context:
| Tag | Description |
|---|---|
| [FieldName] | the name of the field. |
| [index] | A number from 1 to the total number of fields, indicating this field's index placement in the list. |
| The following tags are available when using [listfields] to list SQL fields | |
| [ordinal] | Will retrieve the literal position of the current iterated field. |
| [datatype] | Will retrieve the named SQL datatype of the current iterated field. |
| [allownull] | Returns whether or not (T or F) the current iterated field will accept a null value as a possible entry/value. |
| [width] | Will retrieve the maximum length of data the current iterated field will allow. |
[listdatabases]
Fields in database [name]:<br>
[listfields db=[name]]
Fieldname: [fieldname]<br>
[/listfields]
<hr>
[/listdatabases]To display a list of all fields in an SQL table (after a [SQLConnect] context), specify the SQL connection and database table.
Example WebDNA code:
[listfields conn_ref=conn1&table=inventory]
<b>[fieldname]</b><br>
ordinal: [ordinal]<br>
datatype: [datatype]<br>
allownull: [allownull]<br>
width: [width]<br>
<hr>
[/listfields]To display a list of all fields in a [SQLresult] record set, include the [listfields] inside of a [SQLresult] context.
Example WebDNA code:
[SQLresult result_ref=rs1]
The result set from query "select * from employees;" has [numfields] fields:<br>
[listfields]
<b>[fieldname]</b><br>
ordinal: [ordinal]<br>
datatype: [datatype]<br>
allownull: [allownull]<br>
width: [width]
<hr>
[/listfields]
[/SQLresult][createdb] WebDNA v8.5 tag
Parameters
| Parameter | Description |
|---|---|
| name | The name of the database to create, including the path if required. |
| fields | The name of the fields to be added to the database |
| overwrite | (optional) T/F defaults to F. Allow an existing database to be overwritten. |
[createdb name=/data/newdatabase.db&fields=FROM,TO,MATCH&overwrite=f][dbencrypt] WebDNA v8.6+ tag
Encrypted databases are written out by WebDNA, the newly encrypted database file name must end in "_encrypted.db". WebDNA will write the new file if it does not exist.
Parameters
| Parameter | Description |
|---|---|
| from | The name of the database to be copied and the path to the db if required. |
| to | The name of the NEW database and the path to the db if required. |
| overwrite | (optional) T/F defaults to F. Allow an existing database to be overwritten. |
[dbencrypt from=/data/original.db&to=/data-encrypted/copy_encrypted.db&overwrite=f]Error message when a db attempts to overwrite a db when overwrite is blank or set to F or f
File exists and OVERWRITE not set
Encrypted databases are portable, this means that the encrypted databases can be used on another server with a different copy of WebDNA that is version 8.6 or higher.
[table] WebDNA v5.0 context
With [table], you define the name of the table and the fields. Between the table tags, you populate the table with tab delimited values, with the record separated by carriage returns, just like a .db file.
Parameters
| Parameter | Description |
|---|---|
| name | The name used to reference the table during the duration of the template. |
| fields | A comma delimited, ordered, list of fieldnames to be used for the table. |
Example WebDNA code:
[table name=filelisting&fields=fname,fsize,fdate]
[listfiles images/uploads][filename] [size] [moddate]
[/listfiles]
[/table]Example WebDNA code:
[search table=filelisting&nefnamedatarq=[blank]&fsizesort=1]
[founditems]
[filename] ([fsize]), [fdate]<br>
[/founditems]
[/search]You could use these examples to offer three different sort options for the file listing, something [listfiles] alone can't do.
You can declare a table by using the context without adding records. Then, later on the page, you can use append and replace (and delete, for that matter) to populate the table. Then when you're done with all that, do your search.
Another example to replace each letter of the alphabet with the corresponding number:Example WebDNA code:
[text]alphabet=abcdefghijklmnopqrstuvwxyz[/text]
[table name=t1&fields=from,to]
[listchars chars=[alphabet]]
[char] [index]
[/listchars]
[/table]
[convertchars table=t1]this is your text[/convertchars]