Tables

Purpose:	Open a database table. Optionally, you can also specify a script from your publication's Subroutine Action that you want executed whenever the table changes.
Category:	Tables
Syntax:	dbpOpenTable "database id" "table" "subroutine" database id The name assigned to the database containing the table you want to open. table The name of the table you wish to open. subroutine The name of a subroutine from your publication's Subroutine Action. The subroutine specified here will be automatically executed whenever the table is updated or the current record number changes. This can be useful if you want to activity to take place whenever the reader displays a different record. Subroutines are entered from the Actions page of VisualNEO for Windows's Book Properties screen. VisualNEO for Windows's help file contains more information on using subroutines. Note: By default, dbpOpenTable will perform a "show all" type query on the table as soon as it's opened. This is desirable for small and medium sized tables, but may degrade performance with extremely large tables. Instead, you may prefer to open large tables with the dbpExecSQL action, so you can limit the amount of data returned and improve performance.
Example:	dbpOpenTable "DB1" "sales" "OnChangeSub"
SQL Equivalent:	SELECT * FROM table

Purpose:

Create a new database table.

Category:

Tables

Syntax:

dbpCreateTable "database id" "table" "field defs"

database id

The name assigned to the database where the new table will be created.

table

The name of the table you wish to create. Attempting to use a name that belongs to an existing table will generate an error.

field defs

This is a list that defines the types of fields that will comprise each record in the new table. Each field definition consists of a unique field name, the field type and, depending on the field and database type, size and options. Multiple fields are separated by semicolons (;). For example:

"field1 type(size) options;field2 type(size) options;..."

Field names can be just about anything you like as long as each name is used only once in the same table. Typically a field's name should reflect the type of data it will contain, such as LastName, City, State, etc.

Tables store different types of data in specific types of fields. Text is stored in either a string, char or memo type field, while numbers are stored in an integer or float type field. Since VisualNEO for Windows doesn't really distinguish between text and numbers the way traditional programming environments do, you can get by with defining all of you fields as strings if you like. The only exception would be if you plan on loading your database file into another program. In that case, you may want to define your fields using the field types that more closely match your data.

Note: Unfortunately, different databases offer different, sometimes incompatible, array of choices when it comes to field or data types. Sometimes even field types with the same names can have different capacities or properties in another database. To be sure you're getting what you expect, check the database's documentation.

The types of database fields supported by NeoDBpro are listed below:

String	Variable-length character data with a maximum of 8,000* characters. Used for storing text or combinations of text and numbers, such as addresses. Can also be used for numbers that do not require calculations, such as telephone numbers, part numbers, postal codes, etc. Some types of databases refer to this field type as a "VarChar". The desired size of the field is specified in parenthesis. For example: String(500) Note: For Microsoft Access databases the size of a String field is limited to 255 characters.
Char	Fixed-length character data with a maximum length of 255* characters. The desired size is specified in parenthesis. For example: Char(35)
Memo	Variable-length character data with a maximum length of 64,000* characters. This field type is sometimes called a "Text" field.
Boolean	Can contain either "true" or "false". This field type is sometimes called a Bit.
Integer	A whole number between –32,768 and 32,767*.
BigInt	A very large whole number between –2,147,483,648 and 2,147,483,647*.
AutoInc	A unique sequential number automatically inserted when a record is added. The number will be incremented by one for each new record.
Currency	Monetary values. Accurate to 15 digits to the left of the decimal point and 4 digits to the right. This field type is sometimes called a "Money" field.
Float	Decimal number between 10^28-1 and 10^28-1*.
Date	Date. This type is not supported by MS Access, use DateTime instead.**
Time	Time. This type is not supported by MS Access, use DateTime instead.**
DateTime	Date and time data.**
Picture	Can be used to store just about any type of binary date, including pictures, document files, etc. This field type is sometimes called "binary", "varbinary" and "blob". Not all types of databases support this type of field, and there are no standard formats for storing data. This means that data stored here may not be easily exchanged between different types of databases. See Picture Field Considerations for more information.

*Maximum field sizes vary widely between databases and even among versions of the same database. Check your database's documentation for exact field properties.

**Microsoft Access does not distinguish between date and time fields internally. In order to display date and time values correctly, you must manually define the field's appearance using the DisplayFormat property of the dbpSetFieldProperties action.

Finally, the field size is used to define the maximum length for String and Char types. Non MS Access databases can optionally specify sizes for Integer, BigInt and Float types. Size is ignored for all other field types. You can reduce the size of your database by specifying maximum sizes for each String and Char fields. For example, a zip code field rarely requires more than 5 characters and last names can usually fit in 25 characters. Allocating more space for these types of fields will make your database file larger than it needs to be.

Because dbpCreateTable can be complex, the easiest way to properly construct this syntax is to select the action from VisualNEO for Windows's Select and Action list and use the dbpCreateTable wizard pictured below:

After you define your table using the wizard you will be asked if you want NeoDBpro to create basic Text Entry boxes for each of your table's fields. If you answer "Yes" the Text Entry boxes will be placed onto the Windows clipboard. You can then return to VisualNEO for Windows and select Paste from Edit menu to place the objects onto your publication.

Note: Advanced users may choose to create tables by passing SQL commands to the dbpExecSQL action.

Example:

dbpCreateTable "AddrBook" "Contacts" "FirstName String(25);LastName String(25);Company String(50);Street String(50);City String(25);State String(10);Zip String(10);Country String(50) Default=USA;Telephone String(20);EMail String(35);WebSite String(35);Comments Memo"

SQL Equivalent:

CREATE TABLE table ( field defs )

Purpose:	Create an alternate, virtual view of a table. The data displayed in the view is identical to the data in the source table. Only one copy of the data exists in the source table. Edits applied to the view are automatically reflected in the source table and vise versa. Once created, the view is added to the database's list of tables and acts and behaves exactly like a normal table. A view can be opened with dbpOpenTable, displayed with dbpShowGrid and deleted with dbpDropTable. Deleting a view with dbpDropTable does not affect any of the data which is stored in the source table.
Category:	Tables
Syntax:	dbpCreateView "database id" "source table" "view name" database id The name assigned to the database. source table The name of the table to be used as the source for the view. view name The name to use for the view. This must be a unique name not used by any other tables or views in the database.
Example:	dbpCreateView "AddrBook" "Clients" "ViewOfClients"
SQL Equivalent:	CREATE VIEW view name AS SELECT * FROM source table

Purpose:	Delete a database table. Use this action with caution. Once a table is deleted it cannot be recovered.
Category:	Tables
Syntax:	dbpDropTable "database id" "table" database id The name assigned to the database containing the table you want to delete. table The name of the table to delete.
Example:	dbpDropTable "DB1" "sales"
SQL Equivalent:	DROP TABLE table

Purpose:	Close a database table. Calling dbpCloseTable is optional since any tables that are open when a publication is shutdown will automatically be closed.
Category:	Tables
Syntax:	dbpCloseTable "database id" "table" database id The name assigned to the database containing the table you want to close. table The name of the table to Close.
Example:	dbpCloseTable "DB1" "sales"
SQL Equivalent:	N/A

Purpose:	Obtain the names of all tables in a database and store the results in a variable. Table names will separated by the specified delimiter.
Category:	Tables
Syntax:	dbpGetTableNames "database id" "delimiter" "variable" database id The name assigned to the database. delimiter The character or characters used to separate the table names. variable The name of the variable where the table names will be stored. Each table name will be separated by the specified delimiter.
Example:	dbpGetTableNames "AddrBook" "[#13]" "[TableList]"
SQL Equivalent:	SHOW TABLES

Purpose:	Obtain the names of all field names in a table and store the results in a variable. Field names will separated by the specified delimiter.
Category:	Tables
Syntax:	dbpGetFieldNames "database id" "table" "delimiter" "variable" database id The name assigned to the database containing the table. table The name of the table whose fields you want to retrieve. delimiter The character or characters used to separate the field names. variable The name of the variable where the field names will be stored. Each field name will be separated by the specified delimiter.
Example:	dbpGetfieldNames "AddrBook" "Contacts" "[#13]" "[FieldList]"
SQL Equivalent:	DESCRIBE table

Purpose:	Obtain the field definitions used to create the table. Each field definition consists of a unique field name, the field type and, depending on the field and database type, size and options. Multiple fields will separated by the specified delimiter. For example: field1 type(size) options;field2 type(size) options;... This format is also used by dbpCreateTable. Note: Different databases offer different, sometimes incompatible, array of choices when it comes to field types. Sometimes even field types with the same names can have different capacities or properties in another database. For this reason, the field defs returned by dbpGetFieldDefs may differ slightly from the ones used when the table was created.
Category:	Tables
Syntax:	dbpGetFieldDefs "database id" "table" "delimiter" "variable" database id The name assigned to the database containing the table. table The name of the table whose field definitions you want to retrieve. delimiter The character or characters used to separate the field definitions. variable The name of the variable where the field definitions will be stored. Each field definition will be separated by the specified delimiter.
Example:	dbpGetfieldDefs "AddrBook" "Contacts" "[#13]" "[FieldInfo]"
SQL Equivalent:	DESCRIBE table

Purpose:	Add a field to an existing table.
Category:	Tables
Syntax:	dbpAddField "database id" "table" "field" "field defs" database id The name assigned to the database containing the table. table The name of the table where the new field will be added. field The name of the new field. field defs The field's definition. A field definition consists of a unique field name, the field type and, depending on the field and database type, size and options. See dbpCreateTable for more information.
Example:	dbpAddfield "AddrBook" "Contacts" "Occupation" "String(50)"
SQL Equivalent:	ALTER TABLE table ADD COLUMN field name field defs

Purpose:	Remove a field and all associated data from a table.
Category:	Tables
Syntax:	dbpDropField "database id" "table" "field" database id The name assigned to the database containing the table. table The name of the table containing the field to be deleted. field The name of the field to delete.
Example:	dbpDropfield "AddrBook" "Contacts" "Occupation"
SQL Equivalent:	ALTER TABLE table DROP COLUMN field

Purpose:	Determine if a database contains a specific table.
Category:	Tables
Syntax:	dbpTableExists "database id" "table" "variable" database id The name assigned to the database containing the table. table The name of the table. variable The name of the variable where the result will be stored. The variable will be set to "True" if the table exists or "False" if it does not.
Example:	dbpTableExists "AddrBook" "Contacts" "[Result]" If "[Result]" "=" "True" AlertBox "Hello" "A table named Contacts already exists." EndIf
SQL Equivalent:	N/A

Purpose:	Determine if a table contains a specific field.
Category:	Tables
Syntax:	dbpFieldExists "database id" "table" "field" "variable" database id The name assigned to the database containing the table. table The name of the table containing the field. field The name of the field. variable The name of the variable where the result will be stored. The variable will be set to "True" if the field exists or "False" if it does not.
Example:	dbpFieldExists "AddrBook" "Contacts" "LastName" "[Result]" If "[Result]" "=" "True" AlertBox "Hello" "A field named LastName already exists." EndIf
SQL Equivalent:	N/A

Purpose:	Establish a master-detail relationship between two tables. Once a relationship is defined, navigational changes to the master table will automatically display matching records in the detail table. In order to link two tables, they must share a common field such as a customer name, order number or part number. The contents of the master table's master field will be used to query the linked field in the detail table.
Category:	Tables
Syntax:	dbpDefineRelationship "database id" "mastertable" "masterfield" "detailtable" "detailfield" "fieldmask" database id The name assigned to the database containing the tables to be linked. mastertable The name of the master table. masterfield The name of the field in the master table to link. detailtable The name of the detail table. detailfield The name of the field in the detail table to link. fieldmask A list of fields (separated with commas) from the detail table to be included in the query that controls the master-detail relationship. The fields listed here will displayed in the detail table's grid. Fields not listed here will be omitted from the grid. (Basically, you should be able to use anything that can be inserted between SELECT and FROM in SQL.) To use all fields, leave this parameter blank. Note: The master field and detail field must be of the same or compatible types.
Example:	dbpDefineRelationship "DB1" "Orders" "orderNum" "OrderDetails" "orderNum" "" The fieldmask parameter can be used to limit which fields are displayed. For example: dbpDefineRelationship "DB1" "Orders" "orderNum" "OrderDetails" "orderNum" "Quantity, Description, UnitPrice" You can even use the fieldmask to add a temporary calculated field to the table. For example: dbpDefineRelationship "DB1" "Orders" "orderNum" "OrderDetails" "orderNum" "Quantity, Description, UnitPrice, Quantity * UnitPrice AS Total"
SQL Equivalent:	See JOIN or INNER JOIN.

Purpose:	Clear a master-detail relationship previously established with dbpDefineRelationship. Calling dbpEndRelatioship is optional since relationships are automatically terminated when a table or database is closed. If more than one relationship exists between two table this action will clear all of them.
Category:	Tables
Syntax:	dbpEndRelationship "database id" "mastertable" "detailtable" database id The name assigned to the database containing the linked tables. mastertable The name of the master table. detailtable The name of the detail table.
Example:	dbpEndRelationship "DB1" "Orders" "OrderDetails"
SQL Equivalent:	N/A