The “I” and “G” of Oracle

The “i” stands for “internet” and the “g” stands for “grid”.

The i version started from 8i onwards. It stands for oracle internet. Because till then (in version 8)it did not support data transfer over the internet. At that point of time use of internet was not widely used. Next was 9i with some of the enhanced features of 8i. Next was 10g, it stands for Oracle GRID,because it supports grid architecture.

 

10g is Oracle’s grid computing product group including (among other things) a database management system (DBMS) and an application server. In addition to supporting grid computing features such as resource sharing and automatic load balancing, 10g products automate many database management tasks.

10g follows Oracle’s 9i platform. Oracle says that the g (instead of the expectedi) in the name symbolizes the company’s commitment to the grid model.

Next in database is 11g which is basically a ERP.

Advertisements

Oracle and VB 6.0 Connectivity (Part 4)

This part describes the way to implement procedure and cursor as well. I have together described cursor and procedure in a single example. What i have done is when you delete or update a record that record gets deleted from voter table and get inserted in to votertemp table.
In procedure what i have tried to do is the deleted record that is currently in votertemp gets deleted from votertemp table and gets inserted back to voter table as it was in its first instance

Oracle
1) type ed -> in the sql> command line -> it opens a notepad
2) type the procedure there!

create or replace procedure voterundo
is
cursor votercur is
select * from votertemp;
id number(5);
name varchar2(25);
address varchar2(50);
age varchar2(3);
gender varchar2 (6);
dob varchar2(30);
begin
open votercur;
loop
fetch votercur into id,name,address,age,gender,dob;
exit when votercur%notfound;
delete from voter;
insert into voter values(id,name,address,age,gender,dob) ;
delete from votertemp;
end loop;
end;
/

3) at prompt press “/”
4) it will say procedure created successfully!

Visual Basic

declare variable!
‘declare variables for connection recordset and command which we use for procedure
Dim conv As ADODB.Connection
Dim rsv As ADODB.Recordset
Dim cmdv As ADODB.Command

‘Form load
Private Sub Form_Load()
‘set connections
Set conv = New ADODB.Connection
Set rsv = New ADODB.Recordset
Set cmdv = New ADODB.Command
‘open connection
conv.Open “Provider=MSDASQL.1;Password=tiger;Persist Security Info=True;User ID=scott;Data Source=voter;Initial Catalog=voter”
End Sub

‘Undo Button double click then type this code
Private Sub Command9_Click()
‘when you click on undo button
‘this is the code for procedure you have to call it
‘these four lines of code call the procedure

cmdv.ActiveConnection = conv
cmdv.CommandType = adCmdStoredProc
cmdv.CommandText = “voterundo” ‘voterundo is the name of procedure that you created
‘finally
cmdv.Execute
MsgBox “undo”
End Sub

OutPut will make you clear to understant!
before delete
SQL> select * from voter;
ID NAME
————— ————————————-
ADDRESS AGE GENDER
————————————————————————— ————— ———
DOB
———————————————
77 nitesh
pune 22 male
10/23/1990

SQL> select * from votertemp;

no rows selected

after delete

SQL> select * from voter;

no rows selected

SQL> select * from votertemp;

ID NAME
————— ————————————-
ADDRESS AGE GENDER
————————————————————————— ————— ———
DOB
———————————————
77 nitesh
pune 22 male
10/23/1990

after undo procedure
SQL> select * from voter;

ID NAME
————— ————————————-
ADDRESS AGE GENDER
————————————————————————— ————— ———
DOB
———————————————
77 nitesh
pune 22 male
10/23/1990

SQL> select * from votertemp;
no rows selected

Oracle and VB 6.0 Connectivity (Part 3)

Now we will see how to insert values into table a table and to view them.

Oracle
Initially we had created table in part1.

Visual Basic 6.0
Now we wil insert data into the table through visual basic.

The code for inserting will be as follows

When you double click on add button it will open your code window it will have this code!

Private Sub Command1_Click()
Dim gen As String
If (Option1.Value = True) Then
gen = “male”
ElseIf (Option2.Value = True) Then
gen = “female”
End If
rsv.Open “insert into voter values(” & Text5.Text & “,’” & Text2.Text & “’,’” & Text3.Text & “’,’” & Combo1.Text & “’,’” & gen & “’,’” & DTPicker1.Value & “’)”, conv, adOpenDynamic, adLockOptimistic
If (rsv.State = 1) Then
rsv.Close
End If
MsgBox (“Data Entered”)
End Sub

This will add the data entered into the fields into the oracle database.

Next is to view this entered data from table.

Visual basic code
Private Sub Command7_Click()
Dim viewv As Integer
viewv = InputBox(“enter the voter id”)

rsv.Open “select id,name,address,age,gender,dob from voter where id=’ ” & viewv & ” ‘”, conv, adOpenDynamic, adLockOptimistic

Text5.Text = rsv.Fields(“id”)
Text2.Text = rsv.Fields(“name”)
Text3.Text = rsv.Fields(“address”)
Combo1.Text = rsv.Fields(“age”)

If (rsv.Fields(“gender”) = “male”) Then
Option1.Value = True
ElseIf (rsv.Fields(“gender”) = “female”) Then
Option2.Value = True
End If

DTPicker1.Value = rsv.Fields(“dob”)

If (rsv.State = 1) Then
rsv.Close
End If
MsgBox “view”
End Sub

You are done! In the next part you will see procedures! in part 4.
Part 4

Oracle and VB 6.0 Connectivity (Part 2)

The first part defined how to establish connectivity between the back end and the front end of the project. Next we will see how to implement triggers.
Oracle
We need to Create the triggers in the oracle.
1) type ed in sql
2) it opens a notepad window
3) type the trigger as in the below example
4) exit the notepad with saving
5)and then type “/”
6) this will show a message as trigger created!
7) It should not show trigger created with compilation errors. If this message is shown check your syntax of trigger.

Example:
create or replace trigger votertrig
after update or delete on voter
for each row
begin
insert into votertemp values(:old.id,:old.name,:old.address,:old.age,:old.gender,:old.dob);
end;

Whenever we perform an update or delete operation the old values get stored into the votertemp table and the changed data gets reflected into the permanent table

Visual Basic 6.0
In VB we do not have to do anything as triggers are automatically fired whenever an update or delete operation takes place. The admin can log in into the oracle and view the modifications including the old as well as new values from the voter and votertemp table.

We have successfully set up triggers in the Oracle Back End and when an user deletes or updates any record through the VB front end application the trigger gets fired. We will see procedures in the next part 3.
Part 3

Oracle and VB 6.0 Connectivity (Part 1)

Here is a basic VB application called voter information system demonstrated which explains the concepts quite clearly. The first step required to establish connectivity is preparing the back end and the front end of the project. These steps are divided into two parts first the back end that is the oracle and front end that is the Visual Basic 6.0.

Oracle
Firstly we need to set up the database. Create the tables, triggers and procedures and database in the oracle.
Create tables:
Example:
create table voter(id number(5) primary key, name varchar2(25),address varchar2(50),age number(3),gender varchar2(6),dob varchar2(30));
create table votertemp(id number(5) primary key, name varchar2(25),address varchar2(50),age number(3),gender varchar2(6),dob varchar2(30));

(we will be using the next table in the triggers that we will be implementing)

Visual Basic 6.0
Develop the VB application as in this example given below. Design the necessary forms for the application. The next part is establishing the connectivity.

//VB CODE FOR VOTER INFO
Dim conv As ADODB.Connection
Dim rsv As ADODB.Recordset
Dim cmdv As ADODB.Command
Private Sub Form_Load()
Set conv = New ADODB.Connection
Set rsv = New ADODB.Recordset
Set cmdv = New ADODB.Command
conv.Open “”
End Sub
Set up connectivity using data source
1) Open the control panel -> Administrative tools -> Data sources
2) System DSN -> Add -> MS ODBC for oracle -> Ok
3) DSN = voter
4) Username = scott -> ok “scott is login name for oracle”

Set up VB
1) Right click adodc control -> properties.

2) Use connection string ->build.
3) Select driver MS OLE DB provider for ODBC drivers -> next.
4) Select DSN table name voter from list.
5) Type username and password “scott and tiger” which ever used for logging in oracle.
6) Allowing saving password -> test connection. -> if test connection successful then proceed else the check again the above steps.
7) Type catalog name same as table name.

8) Copy the string generated in use connection string.
9) Paste it in the connection to open.
10) Eg. conv.Open “Provider=MSDASQL.1;Password=tiger;Persist Security Info=True;User ID=scott;Data Source=voter;Initial Catalog=voter”

Now the code will look like this:

Dim conv As ADODB.Connection
Dim rsv As ADODB.Recordset
Dim cmdv As ADODB.Command

Private Sub Form_Load()
Set conv = New ADODB.Connection
Set rsv = New ADODB.Recordset
Set cmdv = New ADODB.Command
conv.Open “Provider=MSDASQL.1;Password=tiger;Persist Security Info=True;User ID=scott;Data Source=voter;Initial Catalog=voter”
End Sub

You have successfully set up connection with the Oracle Back End and VB Front End. Adding and retrieving records we will see in next part.
Part 2