Database Use in VI

Wondering how a VI web accesses a database, and what should be installed?..

The web server and your pc will need the OLEDB/ADO/ODBC data access dll's loaded (and probably already are). These facilities are installed via a single executable called MDAC. There are various versions that seem to co-exist quite hapily. You will need to install ver 1.5 at least, though 1.6 is better.

Your PC needs the connections to assit during design-time. The server connects during runtime. ODBC/OLEDB uses connection pooling, so multiple web users re-use the same connection - so you must avoid certain commands that would alter the connection settings (like changing user or setting the isolation level) as other web users would be affected.

Once installed, the web server and your pc can connect to a whole host of databases. You can connect to these data sources using Visual Basic, C++ or via ASP in JavaScript or VBScript. You may need to install various database-specific client libraries - so Oracle would provide DLLs for a client pc to communicate with the server, and the ODBC/OLEDB drivers would use these.

The DTC's in VI are handy, because they generate all of the required connection and recordset handling code for you, but you can copy-paste this code if you are unsure of DTC's (Right-Click the DTC, and select Show Runtime text). You will NOT need any additional software on the server unless...

VI, and the DTC's support an optional thing called the DataEnvironment. It is a way of centrally storing all SQL commands for use in your app. It can be very useful and powerful - but it can be tricky to configure and update on the live server. You need to install the DataEnvironment dll's on the server - which is done by installing the VI Server Components (on disk 2 of the VI disk set). This is needed both on your PC and the Server ONLY if you want to use the DataEnvironment - but I recommend it!

The location of the database (on the web server or on a dedicated server) is irrelevent. You would use a dedicated database server only if the performance or security needs demanded it.

The _ScriptLibrary in Visual Interdev contains Server-Side (.ASP) runtime files and Client-Side (.HTM) files. These are chosen depending on the page options - DTC Scripting Platform - that you choose. Have a look at these files - particalurly RECORDSET.ASP, to understand how the thing works.

At DESIGN TIME only, a whole host of ActiveX Controls are used to render the controls in the VI editor, and to provide the Property Setting dialogs for the various DTC's. These are NOT USED at runtime at all. They simply write the required JavaScript that later calls the routines contained in the _ScriptLibrary at runtime.

VI will store the webs' database connection string in Application variables as set in the GLOBAL.ASA file (requires DataEnvironment, I think). This is a good use of the Application object, and is faster and more secure than 'include' configuration files. Right-Mouse click the GLOBAL.ASA to add a connection.