In order to connect to a database server you will need to know some specific information such as the name of the database, your user name and a password. This information, called a Connection String, must be formatted precisely or you will not be allowed to access the data.


Installing the proper drivers and configuring the server Connection String is often the most challenging part of working with ODBC/ADO databases, because each type of server requires slightly different information in order to establish a connection. Fortunately, NeoDBpro includes some wizards to make the process of connecting to most types of popular databases easy than it usually would be.


Opening a Microsoft Access (MDB or ACCDB) Database

The easiest to configure, and the most portable type of database, is Microsoft Access. As described above, most other types of databases require special software and drivers to be installed on your computer. The Microsoft Data Access Drivers (MDAC) required for Microsoft Access databases are included with Windows starting with 98. Therefore publications that deploy Access databases should be viewable on all but the very oldest Windows 95 computers. (Windows 95 users can download MDAC free from Microsoft's web site if needed.)


To open an existing Access database, simply pass the file name to NeoDBpro's dbpOpenAccessDatabase action. For example, opening a file called "AddressBook.mdb" located in the "c:\my documents" folder would look like this:


dbpOpenAccessDatabase "MyDB" "c:\my files\AddressBook.mdb" ""


The first parameter "MyDB" is the Database ID, which is simply the name that you want to use to refer to this database in the future. It can be anything you like, but it's often easiest to use something short and descriptive. A Database ID is also sometimes referred to by database professionals as an "Alias".


The second parameter is the database file name and the third parameter is the database password if required.


Opening Other Types of Databases

To open or connect to most other types of databases, you will use an action called dbpOpenDatabase which requires something called a Connection String.


The connection string is used to specify the information needed to connect to the database server.  Connection strings can be tricky because they are often different depending on what type of server/ODBC driver you're using. The string consists of one or more elements required to establish the connection. Specify multiple elements as a list with individual elements separated by semicolons. For example, the command to open a MySQL database looks like this:


dbpOpenDatabase "MyDB" "Provider=MSDASQL.1;Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=test;User=root;Password=apple;Option=3"


The first parameter "MyDB" is the Database ID, which is simply the name that you want to use to refer to this database in the future. It can be anything you like, but it's often easiest to use something short and descriptive. The Database ID is also sometimes referred to by database professionals as an Alias.


The second parameter is the connection string. This particular connection string contains the unique elements required by MySQL. A different type of database might contain some of the same elements or completely different ones. Check your database's documentation to determine the appropriate connection string for your server. (See disclaimer here!) Your database administrator should also be able to provide you with an appropriate connection string. You can also find example connection strings by searching for “ADO connection string” on Google. Otherwise, you can just experiment with different connection strings to see what works.


Working with Very Large Databases

By default NeoDBpro uses something called a client-side cursor. Simply put, this is a method of accessing data that relies on the client application to handle most of the data storage and processing. Client-side processing is very flexible and generally provides the fastest performance for small to medium sized databases. For large databases, however, a client-side cursor can sometimes consume too many system resources resulting in poor performance. To compensate for this problem, you may want to use a server-side cursor when working with very large databases. In NeoDBpro you can switch to a server-side cursor by adding "CursorLocation=Server" to your connection string. For example:


dbpOpenDatabase "MyDB" "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[PubDir]AddressBook.mdb;CursorLocation=Server"


With a client-side cursor (the default) all data is copied to the local machine and processed there. This provides access to features not normally supported by servers such as sorting and indexing. When using a SQL query, only the data returned is copied to the local machine. A server-side cursor doesn't provide as much flexibility, but is often more appropriate for large databases, and may be required when the size of a database exceeds the available memory and disk space available on a local machine.


After Opening a Database

To tell if the dbpOpenAccessDatabase or dbpOpenDatabase action has worked, you can monitor the variable screen of VisualNEO for Windows's debugger. When NeoDBpro successfully connects to the database server, your debugger should display the variable below:


MyDB.$Status = Connected


MyDB is the Database ID you selected.


Once a connection has been established, you can open tables with the dbpOpenTable action, or perform a query by passing an SQL "SELECT" statement to the dbpExecSQL action. For example, to view the contents of a table named "Customers", use one of the following:


dbpExecSQL "MyDB" "SELECT * FROM Customers”


or


dbpOpenTable "MyDB" "Customers" ""


This will access all of the fields and records in the Security table. The first parameter is the Database ID name used in the dbpOpenDatabase action. The second parameter is the SQL statement. See Working with Data to see how NeoDBpro handles the actual data from the database.


Closing a Database

When you're finished reading from the database, you can close the connection using the dbpCloseDatabase action. For example:


dbpCloseDatabase "MyDB"


If you forget to close the connection, NeoDBpro will do it for you automatically when your publication shuts down.