Working With Database

Below are the steps involved to connect to database(DB)

1. Create an object of type ADODB.Connection.
2. Using the connection string establish a connection to the DB.
3. Execute the query and store the query result to Record set collection.

***********************************************************************************

We have two types of connections

1)DSN oriented Connection

2)DSN less connection
***********************************************************************************

DSN oriented Connection :
***********************************************************************************

                  In this type of connection first we need to create system DSN then you can use that directly to establish a connection.

Example:

Set connect = CreateObject (“ADODB.Connection”)

connect.open “QT_Flight32″  ‘QT_Flight32 is the system DSN name

Set RS = connect.Execute(“select flight_number from flights where flight_number = 2005″ )

Count1 = RS.fields.count

Do

For  i = 0 To count1-1

MsgBox(RS.fields(i).name)

MsgBox(RS.fields(i).value)

Next

RS.MoveNext

Loop until RS.EOF
***********************************************************************************

Note :=

        System DSN should be created respective machine manually, otherwise scripts will get fail

***********************************************************************************
DSN less Connection :
***********************************************************************************

                  In this type of connection we don’t need to create any system DSN , we can establish a connection using(Driver name , Server name , DB username & DB password) directly

Example:

Set connect = CreateObject (“ADODB.Connection”)

Connect.open “Driver={Microsoft ODBC for Oracle}:Server=DBserver,UID=Sa,Password=sa”

Set RS = connect.execute(“select flight_number from flights where flight_number = 2004″ )

Count1 = RS.fields.count

Do

For  i = 0 To count1-1

MsgBox(RS.fields(i).name)

MsgBox(RS.fields(i).value)

Next

RS.MoveNext

Loop until RS.EOF
***********************************************************************************