Using Vine OLEDB Provider

Installation

Many Vine products, such as Vine for Windows, Vine Server Setup, Vine Email Reader, Vine Import, Vine Object Manager use Vine OLEDB provider. You do not need to install Vine OLEDB Provider for Vine products as it is included in all the installers and installed automatically together with the product using it. However, if you want to use Vine OLEDB provider in a non-Vine product to connect to a Vine database, you need to make sure you have the provider on your system:

• If you want to use Vine OLEDB provider in a 32 bit product then you can simply install some 32 bit Vine product, such as Vine for Windows.

• If you need Vine OLEDB provider in a 64 bit product, for example, Microsoft Excel 64 bit then you should use the 64 bit Vine OLEDB installer which is now available. (Or you can install any available 64 bit version of a Vine product, such as Vine Server Setup.)

Basic information

Vine OLEDB Provider is an implementation of OLEDB Provider set of interfaces with proper registry settings allowing usage as OLEDB source in any OLEDB client applications.

Therefore it can be used in .Net applications through ADO.Net functionality using OleDb namespaces and assemblies.

Vine OLE DB Provider delegates SQL calls to VAS or Oracle directly depending on the prefix in the connection string:

connectionString="Provider=Vineyard.OLEDBProvider.1;Data Source=vas://vinevas.intra:6202/orcl2;Persist Security Info=True;Password=pass;User ID=vineyarddb"

Prefix: ‘vas://’  is used to establish VAS type of connection, otherwise ‘ora://’ should be used.

Example:

using System;
using System.Data.OleDb;

namespace VineOLEDBDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Provider=Vineyard.OLEDBProvider;
					Data Source=vas://petrvas.intra:6202; User ID=lisa; Password='demo';";
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();

                OleDbCommand command = new OleDbCommand("select name from vineyarddb.vy_resource_view order by name", connection);

                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine(reader["name"]);
                    }
                }
            }

            Console.ReadLine();
        }
    }
} 

Using SQL

It is recommended to use plain SQL commands ("insert" and "update") to work with objects and connections instead of the previously used Vine PL/SQL packages. (In case you need to support code still using the Vine PL/SQL packages you can find the detailed information about them in the Appendix in the SQL API chapter.)

Here is a sample how to get object data:

select id, resource, description from vineyarddb.vy_resource_view where id = :resourceid and status = 'B'

Working with objects

• Adding objects. Use the "insert" SQL command with "returning id" clause. For example:

  insert into vineyarddb.vy_resource (name) values ('Foo') returning id into :theid

• Modifying objects. Use the "update" SQL command.

  update vineyarddb.vy_resource set description = 'some desc' where id = :theid

• Deleting objects. Use the "update" SQL command to set the "status" field to 'D'.

  update vineyarddb.vy_resource set status = 'D' where id = :theid

For more information about objects please refer to the Standard Objects and Fields chapter.

For a full list of object tables and their fields please see the Server Database Tables chapter in the Appendix.

Working with Connections

• Adding connections. Use "insert" SQL command.

  insert into vineyarddb.vy_connection (connectiontype, fromobjectid, fromobjecttype, targetobjectid, targetobjecttype) values (1, :companyid, 'VY_COMPANY', :Personid, 'VY_PERSON') returning id into :connectionid

  This sample returns connection id which in most cases is not needed.
• Modifying connections. Connections must not be redirected. The only way to reconnect an object is to delete its existing connection and to create a new one.
• Deleting connections. Use "update" SQL command to set the "status" field to 'D'.

  update vineyarddb.vy_connection set status = 'D' where id = :connectionid or update vineyarddb.vy_connection set status = 'D' where connectiontype = 1 and fromobjectid = :companyid and targetobjectid = :personid

While working with connections you should be aware of the following:

• Direction: Each type of connection has a definite From-Target order (see Object Connections Management in the Overview Vine Server Database chapter). Connections must be saved in correct From-Target order.

• "Limited" connections: The "From" and "Target" ends of a connection type can have count options that can be equal to 1 - only single connection is permitted or 0 - any number of connections are permitted. See also Object Connections Management in the Overview Vine Server Database chapter.

• "Excluding" Collections: If an object is connected to a collection, it should be checked that the corresponding parent collection is not "excluding". If an object is connected to a collection whose parent is an "excluding" collection, then it cannot be connected to other child collections of that collection. For details, refer to the Collection Tree Hierarchy section in the Overview Vine Server Database chapter and the examples below.

For description of tables defining and storing connections please see Tables: VY_CONNECTION, VY_CONNECTIONTYPE in the Appendix.