1.2 Connection Object

Features:

1.2.1 Attributes property

Performs commit or aborts and starts a new transaction automatically.

Syntax

Object.Attributes = Value

Type

Long.

Values

Attribute

Function

AdXactCommitRetaining

Performs retaining commits - calls CommitTrans automatically and starts a new transaction.

AdXactAbortRetaining

Performs retaining aborts - calls RollbackTrans automatically and starts a new transaction.

Attributes

Read/Write.

Remarks

For a Connection object, the Attributes property is read/write, and its value can be the sum of any one or more of these XactAttributeEnum values. By default the attributes is set to zero.

1.2.2 ConnectionString property

Contains the information used to establish a connection to a data source.

Syntax

object.ConnectionString

Type

String.

Attributes

Read/write.

Remarks

The format of the connection string is similar to an ODBC connection string. For example: DBType=0;Database=MyServer-TCP.World;UID=Scott;PWD=Tiger

DBType

The valid DBTypes are:

  • 4 for MySQL database

  • 1 for B2System SQLIntegrator database

  • 0 for Oracle 8 database

Database

A valid database name that corresponds to the database type specified by DBType

UID

A valid user ID for the database.

PWD

A valid password for the user ID and database.

Examples for Representing the Connection String

To Access MySQL Database

CN.Open ("DBTYPE=4;DATABASE=test", "root","")

To Access a Remote Database

Add the string Host = IP Address at the end of the connection string, where ip address represents the ip address of the server hosting the remote database.

For example the connection string to access the remote database present in the server 123.45.678.910 is:

CN.Open ("DBTYPE=4;DATABASE=test;HOST=123.45.678.910", "root","")

To Access a Command Object

Modify the active connection state. For example to access the MySQL database, the DBTYPE should be set to 4.

CN.ActiveConnection ="DBTYPE=4;DATABASE=test;UID=root;PWD="

Add the string Host = IP Address at the end of the connection string, where ip address represents the ip address of the server hosting the remote database.

For example the connection string to access the remote database present in the server 123.45.678.910 is:

CN.ActiveConenction ("DBTYPE=4;DATABASE=test;HOST=123.45.678.910", "root","")

1.2.3 DefaultDatabase property

Returns the default database for a Connection object.

Syntax

object.DefaultDatabase

Type

String. Evaluates to the name of a database available from the provider.

Attributes

Read/write.

1.2.4 State property

Describes the current state of an object.

Syntax

object.State

Type

Constant.

Attributes

Read-only.

Remarks

This property returns one of the following constants:

ADSTATECLOSED

0

Default. Indicates that the object is closed.

ADSTATEOPEN

1

Indicates that the object is open.

ADXACTCOMMITRETAINING

2

Performs retaining commits - calls CommitTrans automatically and starts a new transaction.

ADXACTABORTRETAINING

4

Performs retaining aborts - calls RollbackTrans automatically and starts a new transaction.

ADBOOLEAN

4

Indicates a boolean value.

1.2.5 Version property

Returns the data connector component’s version number.

Syntax

object.Version

Type

String.

Attributes

Read-only.

1.2.6 BeginTrans method

Begins a new transaction.

Syntax

object.BeginTrans()

Parameters

None.

Return Values

None.

Remarks

To specify a different isolation level, use the Execute methods (of the Command, Connection, and Database objects) to begin the transaction (B2Systems SQLIntegrator only.).

The default isolation level that is enforced by this method is READ COMMITTED. This allows users to change or add records while these records are being read by other users.

Consider the following scenario:

  1. Alan reads a record.

  2. Beth modifies or deletes the record in a transaction, then commits the transaction.

  3. Alan re-reads the record and finds that it has been changed or deleted. Alan then reads a set of records that satisfy a specific WHERE clause.

  4. Beth inserts a record that satisfies the same WHERE clause.

  5. When Alan repeats the first query, the set of records satisfying the WHERE clause has changed.

1.2.7 Close method

Closes a Connection object and any active Recordset objects associated with the Connection.

Syntax

object.Close()

Parameters

None.

Return Values

None.

Remarks

Using this method to close a Connection object also closes any active Recordset objects associated with the connection.

A Command object associated with the Connection object you are closing will persist, but it will no longer be associated with a Connection object. That is, its ActiveConnection property will be set to Nothing. In addition, the Command object’s Parameters collection will be cleared of any provider-defined parameters.

See Also

1.2.8 CommitTrans method

Saves any changes and ends the current transaction.

Syntax

object.CommitTrans()

Parameters

None.

Return Values

None.

1.2.9 Execute method

Executes the specified query, SQL statement, stored procedure, or provider-specific text.

Syntax

object.Execute(
   CommandText As String, 
   RecordsAffected As Long)

Parameters

CommandText

A String containing the SQL statement, table name, stored procedure, or provider-specific text to execute. Currently supports only SQL statements.

RecordsAffected

Optional. A Long variable to which the provider returns the number of records that the operation affected.

Return Values

Returns a Recordset object reference if the command text specifies a row returning query; otherwise, it returns Nothing.

1.2.10 Open method

Opens a connection to a data source.

Syntax

object.Open(
   ConnectionString As String, 
   UserID As String, 
   Password As String)

Parameters

ConnectionString

A String containing connection information.

UserID

A String containing a user name to use when establishing the connection.

Password

A String containing a password to use when establishing the connection.

Return Values

Returns a Recordset object reference if the command text specifies a row returning query else returns NULL.

Remarks

The format of the connection string is similar to an ODBC connection string. For example: DBType=0;Database=MyServer-TCP.World;UID=Scott;PWD=Tiger

DBType

The valid DBTypes are:

  • 4 for MySQL database

  • 1 for B2System SQLIntegrator database

  • 0 for Oracle 8 database

Database

A valid database name that corresponds to the database type specified by DBType

UID

A valid user ID for the database.

PWD

A valid password for the user ID and database.

Examples for Representing the Connection String

To Access MySQL Database

CN.Open ("DBTYPE=4;DATABASE=test", "root","")

To Access a Remote Database

Add the string Host = IP Address at the end of the connection string, where ip address represents the ip address of the server hosting the remote database.

For example the connection string to access the remote database present in the server 123.45.678.910 is:

CN.Open ("DBTYPE=4;DATABASE=test;HOST=123.45.678.910", "root","")

To Access a Command Object

Modify the active connection state. For example to access the MySQL database, the DBTYPE should be set to 4.

CN.ActiveConnection ="DBTYPE=4;DATABASE=test;UID=root;PWD="

Add the string Host = IP Addressat the end of the connection string, where ip address represents the ip address of the server hosting the remote database.

For example the connection string to access the remote database present in the server 123.45.678.910 is:

CN.ActiveConenction ("DBTYPE=4;DATABASE=test;HOST=123.45.678.910", "root","")

1.2.11 Sample to Access the MySQL Database Using the Connection Object

This sample script retrieves the details stored in the table emp present in the database mysql2 hosted by the server 123.45.678.910a.

Option VBA 
’On Error Resume Next

Set CN = CreateObject("UCX:NDODB.CONNECTION")
CN.Open ("DBTYPE=4;DATABASE=mysql2;HOST=123.45.678.910",
"root","")
Set RS = CN.Execute("select * from mysql2",lrec)

DisplayRS (RS)
RS.Close
’ Subroutine to display the details of a record set.

Sub DisplayRS(RS As Object)
Dim FLDS As Object
Dim FLD As Object
Dim RecValue As String
Dim FldCount As Long
Set FLDS = RS.FieldsRecValue = ""
For FldCount = 1 To FLDS.Count
Set FLD = FLDS.Item(FldCount)
RecValue = RecValue & " " & CStr(FLD.Name)
Next
Print RecValue
PrintLine (60)
Print " RS COUNT"
Print RS.RecordCount
Do
RecValue = ""
Set FLDS = RS.Fields
For FldCount = 1 To FLDS.Count
Set FLD = FLDS.Item(FldCount)
RecValue = RecValue & " " & CStr(FLD.Value)
Next
Print RecValue
RS.MoveNext
Print RS.EOF
Loop While Not RS.EOF
End Sub
’Subroutine to print a line

Sub PrintLine(Number)
For PrintCount = 1 To Number
Line = Line & "-"
Next
Print Line
End Sub

1.2.12 Sample to Access the Oracle Database Using the Connection Object

This sample script retrieves details about all the employees stored in the EMP table of Oracle* database.

The sample does the following:

’Create a connection object and open a connection to Oracle database.

Option VBA
On Error Resume Next

Set CN = CreateObject("UCX:NDODB.CONNECTION")
CN.BeginTrans
CN.Open ("DBTYPE=0;DATABASE=ORANW81.WORLD", "scott","tiger")

’Execute the SQL query to retrieve the employee details

Set RS = CN.Execute("Select * from emp", lRec)
If Err <> 0 Then
   GoTo errorhandler
End If
DisplayRS (RS)’ Display the properties of the connection object
’
PrintLine(50)
Print "RecordSet ActiveConnection"
Print  RS.ActiveConnection
RS.ClosePrintLine(50)
Print "Connection String " 
Print  CN.ConnectionString

PrintLine(50)
Print "Connection State " 
Print  CN.State

PrintLine(50)
Print "Connection version " 
Print  CN.Version

PrintLine(50)
Print "Default DataBase " 
Print CN.DefaultDatabase

PrintLine(50)
Print "Connection Attributes "
Print  CN.Attributes
PrintLine(50)
CN.Close

’ Subroutine to display the details of a record set.
’
Sub DisplayRS(RS As Object)
Dim FLDS As Object
Dim FLD As Object
Dim RecValue As String
Dim FldCount As Long
    Set FLDS = RS.Fields
    RecValue = ""
    For FldCount = 1 To FLDS.Count
        Set FLD = FLDS.Item(FldCount)
        RecValue = RecValue & " " & CStr(FLD.Name)
    Next
    Print RecValue
    PrintLine (60)
    Do
        RecValue = ""
        For FldCount = 1 To FLDS.Count
            Set FLD = FLDS.Item(FldCount)
            RecValue = RecValue & " " & CStr(FLD.Value)
        Next
        Print RecValue
        RS.MoveNext
    Loop While Not RS.EOF
End Sub’Subroutine to print a line
’
Sub PrintLine(Number)
    For PrintCount = 1 To Number
        Line = Line & "-"
    Next
    Print Line
End Sub