Creating Databases

The process for creating a new database from scratch varies depending on what type of database you're using. Some databases can only be created by using special software running on the server. Other databases can be created by simply creating a new folder on the server or local hard drive. For these reasons NeoDBpro doesn't offer options for creating all of the different types of databases. The exception is Microsoft Access format databases, which can be created easily using the special dbpCreateAccessDatabase action. For other types of databases consult your database product's documentation.


Creating Tables

Unline databases, tables are realative easy to create and NeoDBpro provides the tools needed to create just about any kind of table you can imagine. Of course, building a new table from scratch is a little more complicated than opening one that's already been created, but NeoDBpro's dbpCreateTable action makes the process mostly painless.


To create a new table from scratch, you will need to decide on a name of your new table and provide some basic information about the fields you want it to contain. If a table with the name you specify already exists in the database, NeoDBpro will generate an error rather than overwrite it. (You can use the dbpDropTable action to remove exists table if you want to ensure that dbpCreateTable won't fail. Be sure to exercise caution when using dbpDropTable because any data the deleted table contains will also be deleted.


Next, you will need to create a list of 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)

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 data, 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.


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.


Here's an example showing how to create a simple database consisting of first name, last name and telephone fields:


dbpCreateTable "AddrBook" "Contacts" "FirstName String(25);LastName String(25);Telephone(30)"


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.