Valentina Database - ActiveX

[rhenderso]

I'm having problems working with the Valentina ActiveX (VCOM.dll). I've seen the examples of connecting to databases via ADO but I can't count on users having ADO on their machines. Can you provide an example using the Valentina ActiveX to connect and query a database?

Thanks,
Raymond

[SWFKit]

Generally, the ADO must have been installed. You can distribute the ADO package with your application and install it if necessary(When you cannot create the ado objects or cannot make a connection).

Do you have a Valentina ActiveX sample that can be migrated into swfkit? We are not familiar with the ActiveX component, it's a bit hard for us to make a new sample, but it will be much easier if you have a sample to migrate.

[rhenderso]

Thanks for your attention. Here's a VB example that ships with the control.

Option Explicit

'This module contains test which illustarate technique
'to work with tables related by RDB-link

Dim V As New Valentina

Function CreateTestDb() As VDataBase

Dim Db As New VDataBase

'create 'sample' database with default mode & cluster size
Db.Create App.Path & "\sample"

'then create 2 tables 'Person' & 'Employee'

Dim sql As String
sql = "CREATE TABLE person ("
sql = sql + "ID Long NOT NULL, "
sql = sql + "FirstName String(20) NOT NULL, "
sql = sql + "MiddleName String(20) NOT NULL, "
sql = sql + "LastName String(20) NOT NULL )"
Db.SqlExecute sql

' ... & 'Employee'
sql = "CREATE TABLE employee ("
sql = sql + "ID Long NOT NULL, "
sql = sql + "PersonID Long NOT NULL, "
sql = sql + "Salary Float NOT NULL )"
Db.SqlExecute sql

Db.Flush
Set CreateTestDb = Db
End Function

Sub AddRecords(ByRef Db As VDataBase)
Dim i As Integer
Dim sql As String
Dim res As Long

Const PersonCount As Long = 5

'create some clients
For i = 1 To PersonCount
sql = "INSERT INTO person VALUES("
sql = sql & i & ", "
sql = sql & "'FN#" & i & "', "
sql = sql & "'MN#" & i & "', "
sql = sql & "'LN#" & i & "') "
res = Db.SqlExecute(sql)
Next

Dim j As Long
j = 1
For i = 1 To PersonCount

'each person has two occupations
sql = "INSERT INTO employee VALUES("
sql = sql & j & ", "
sql = sql & i & ", "
sql = sql & CInt(Rnd * 2000) & ")"
res = Db.SqlExecute(sql)

j = j + 1
sql = "INSERT INTO employee VALUES("
sql = sql & j & ", "
sql = sql & i & ", "
sql = sql & CInt(Rnd * 2000) & ")"
res = Db.SqlExecute(sql)

j = j + 1
Next

End Sub

Sub main()

On Error GoTo Error

'initialize Valentina database engine
V.Init 10

Dim Db As VDataBase
Set Db = CreateTestDb
If Not Db Is Nothing Then

'populate it now with some records
AddRecords Db

'build cursor
'select those orders only which belongs to Client#1 and order them by Date
Dim VC As VCursor
Dim sql As String
sql = "SELECT Person.FirstName, Person.MiddleName, Person.LastName, Employee.Salary "
sql = sql + "FROM Person, Employee "
sql = sql + "WHERE Person.ID = PersonID And Employee.Salary > 1000 "
sql = sql + "Order by Employee.Salary"
Debug.Print sql
Set VC = Db.SqlSelect(sql)

'... and display result in the 'immediate' window
If Not VC Is Nothing Then
DisplayRecords VC
End If
End If

'IMPORTANT: destroy the object
'before calling Shutdown
Set Db = Nothing

'destroy Valentina database engine and free all resources
V.ShutDown
End

Error:
MsgBox Err.Description, vbCritical, "Test failed"
V.ShutDown
End Sub

[meester]

Is this also possible for other databases that are free for commercial use.
By example: PostgreSQL

[SWFKit]

PostgreSQL has an ole db driver, so it can be accessed by using ADO

You only need to change the connection string

PgOleDb supports the following connection attributes:
Provider - mandatory. Must be set to "PostgreSQL", "PostgreSQL.1" or "PostgreSQL OLE DB Provider"
Data Source - server name or address
location - database name
User ID - PG user name to log in as
password - the password
timeout - how long to wait when establishing the initial connection

The methods of connecting PostgreSQL, MS access, Mysql, sql server, oracle or any other databases using ADO are all the same.

http://gborg.postgresql.org/project/...d/download.php

[SWFKit]

//This module contains test which illustarate technique
//to work with tables related by RDB-link

function CreateTestDb() 
{    
    var Db = new ActiveXObject("VCOM.VDataBase");
    
    //create 'sample' database with default mode & cluster size
    Db.Create("c:\\sample");
    
    //then create 2 tables 'Person' & 'Employee'   
    var sql = "CREATE TABLE person (" +
    "ID Long NOT NULL, " +
    "FirstName String(20) NOT NULL, " +
    "MiddleName String(20) NOT NULL, " +
    "LastName String(20) NOT NULL )";
    Db.SqlExecute(sql);
    
    // ... & 'Employee'
    sql = "CREATE TABLE employee (" +
    "ID Long NOT NULL, " +
    "PersonID Long NOT NULL, " +
    "Salary Float NOT NULL )";
    Db.SqlExecute(sql);
    
    Db.Flush();
    return Db;
}

function AddRecords(Db)
{
    var i;
    var sql;
    var res;
    
    var PersonCount = 5;
    
    //create some clients
    for (i = 1; i <= PersonCount; i++)
    {
        sql = "INSERT INTO person VALUES(";
        sql = sql + i + ", ";
        sql = sql + "'FN#" + i + "', ";
        sql = sql + "'MN#" + i + "', ";
        sql = sql + "'LN#" + i + "') ";
        res = Db.SqlExecute(sql);
    }
    
    var j = 1
    for (i = 1; i <= PersonCount; i++)
    {        
        //each person has two occupations
        sql = "INSERT INTO employee VALUES(";
        sql = sql + j + ", ";
        sql = sql + i + ", ";
        sql = sql + parseInt((Math.random() * 2000).toString()) + ")";
        res = Db.SqlExecute(sql);
        
        j = j + 1;
        sql = "INSERT INTO employee VALUES(";
        sql = sql + j + ", ";
        sql = sql + i + ", ";
        sql = sql + parseInt((Math.random() * 2000).toString()) + ")";
        res = Db.SqlExecute(sql);
        
        j = j + 1;
    }
    
}
    
//'initialize Valentina database engine
var V = new ActiveXObject("VCOM.Valentina");
V.Init(10);

var Db = CreateTestDb();
if (Db != null)
{        
    //populate it now with some records
    AddRecords(Db);
    
    //build cursor
    //select those orders only which belongs to Client#1 and order them by Date

    var sql = "SELECT Person.FirstName, Person.MiddleName, Person.LastName, Employee.Salary ";
    sql = sql + "FROM Person, Employee ";
    sql = sql + "WHERE Person.ID = PersonID And Employee.Salary > 1000 ";
    sql = sql + "Order by Employee.Salary";
    trace(sql);
    var VC = Db.SqlSelect(sql);
    
    //... and display result in the 'immediate' window
    if (VC != null)
    {
    trace("no 'DisplayRecords' method"); 
        //DisplayRecords(VC);
    }
}

//IMPORTANT: destroy the object
//before calling Shutdown
delete db;

//destroy Valentina database engine and free all resources
V.ShutDown();