Tuesday, December 15, 2009

TYPE CONVERSIONS IN C++


Three types of type conversions are possible in C++

1. Class type to Basic type
2. Basic type to Class type
3. One class type to another class type

Basic types refer to fundamental data types like int , float , double , char etc. Class type refers to a user defined class type.We use the terms "source type" to denote the initial type and the term "destination type" to denote the final converted type.

The type conversions can be performed in two ways using

1. Costructor(only when the destination type is class type)
2. Overloaded Operator function(only when the source type is a class type)

depending on the type of the conversion.

Class type to Basic type:
The conversion from a class type to a basic type can be performed only using an overloaded operator function.It cannot be done using a constructor,since the destination type is a basic type.

Consider the following example,

class test
{

int x;
public:
test(int y)
{
x=y;
}
operator int()
{
return x;
}

};

void main()
{

int d;
test t(100);
d=t; //calls operator function.converts "test" type to "int" type
//d has value 100

}

Here we have converted a class type to a basic type.While writing the operator function,keep in mind the following points,

1) The operator function should not be explicitly specified a return type. However the operator function should return the destination type,but the return type should not be mentioned in the function declaration.

2)The operator function should be defined inside the class.

Basic type to Class type :
The conversion from a basic type to a class type can be performed using a constructor.It cannot be done using an operator function since the source type is a basic type.

Consider the following example,

class test
{

int x;
public:
test()
{
}
test(int y)//constructor for type conversion
{
x=y;
}

};

void main()
{

int d=100;
test t;
t=d; //calls the constructor

}

Here we have converted the "int" type to "test" type ie from a basic type to a class type.

Class type to Class type :
Conversion between two class types can be done by using either a constructor or a operator function.

Using a constructor :
The conversion is done in the constructor of the destination class.

Consider the following example
//Conversion from metre to centimetre

class centimetre;
class metre
{

float m;
public:
metre(float x)
{
m=x;
}
friend class centimetre;
};

class centimetre
{

float cm;
public:
centimetre()
{
}
centimetre(metre ob) // constructor for type conversion
{
centimetre temp;
temp.cm=ob.m * 100;
return temp;
}

};

void main()
{

metre d(12.345);
centimetre t;
t=d; //conversion from metre to centimetre
//t object has a centimetre of 1234.5

}

Thus we have converted from one class type to another class type using a constructor . Remember to perform the type conversion in the destination class constructor.

Using operator function :
The operator function should be given in the source class.

Consider the following example,
// conversion from metre to centimetre

class centimetre;
class metre
{

float m;
public:
metre(float x)
{
m=x;
}
operator centimetre();

};

class centimetre
{

float cm;
public:
centimetre()
{
}

friend class metre;

};

metre::operator centimetre() //operator function for type conversion
{
centimetre temp;
temp.cm= m*100;
return temp;
}

void main()
{

centimetre t;
metre r(12.345);
t=r; //calls the operator function
//t object has a centimetre of 1234.5

}

Thus we have converted one class type to another class type using a operator function. Remember to use the operator function inside the source class.

Sunday, December 13, 2009

Calling Oracle procedures from Visual Basic 6

Consider the following relation

employee(emp_no number(4),e_name varchar2(15),designation varchar2(15),salary number(6),ph_no number(10),address varchar2(25));

Consider the following oracle procedure to raise the salary of employees belonging to a particular designation.

create or replace procedure employee_bonus(desig IN varchar2,bonus IN number)
is
cursor emp
is
select emp_no from employee where designation=desig;
y number(5);
begin
open emp;
loop
fetch emp into y;
exit when emp%NOTFOUND;
update employee set salary=salary+bonus where emp_no=y;
end loop;
close emp;
end ;
/

Assume the user enters the designation and the bonus in textbox1 and textbox2 respectively . The following code calls the oracle procedure "employee_bonus" from Visual Basic 6.

Dim oconn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim m as String
Dim n as Integer
Set oconn = New ADODB.Connection
oconn.Open "Provider=msdaora;Data Source=localhost;User Id=paul;Password=oracle;"
Set pro = New ADODB.Command
pro.ActiveConnection = oconn
pro.CommandText = "employee_bonus"
pro.CommandType = adCmdStoredProc
pro.Parameters.Append pro.CreateParameter("desig", adVarChar, adParamInput, 25)
pro.Parameters.Append pro.CreateParameter("bonus", adNumeric, adParamInput, 5)
m=Text1.Text
n=Text2.Text
pro("desig") = m
pro("bonus") = n
pro.Execute

To execute a procedure ,first create a ADODB Connection to the database.Then declare a ADODB command object and assign it with the created ADODB Connection.In the above code,we create a ADODB Connection called "oconn" and a ADODB command object called "pro".In the statements,

Set pro = New ADODB.Command
pro.ActiveConnection = oconn

we create a new ADODB command object and assign it with a ADODB Connection.

The next step is to assign the "CommandText" and "CommandType" property of command object "pro".CommandText property is used to specify the name of the oracle procedure to be called.CommandType specifies the type the command object points to.So we write the statements

pro.CommandText = "employee_bonus"
pro.CommandType = adCmdStoredProc

The next step is to create each parameter to be passed to the oracle procedure.Thus here we write the statements to create two parameters,

pro.Parameters.Append pro.CreateParameter("desig", adVarChar, adParamInput, 25)
pro.Parameters.Append pro.CreateParameter("bonus", adNumeric, adParamInput, 5)

Here inside the CreateParameter() ,the first argument is the name of the parameter as mentioned in the oracle procedure.The next argument is the type of the parameter(ie chracter or numeric).The third argument specifies whether the parameter belongs to IN or OUT or INOUT type as mentioned in the oracle procedure.The IN parameter cannot be modified inside the oracle procedure.The OUT parameter can be modified inside the oracle procedure.The INOUT parameter is the combination of both types.The last argument in CreateParameter() is the length of the parameter to be passed.

The next step is to assign values to the parameters to be passed to the oracle procedure.

pro("desig") = m
pro("bonus") = n

The last step is to execute the procedure.This is done by the following command.

pro.Execute

Thus we have successfully executed a oracle procedure from VB6.

Writing SQL Queries from Visual Basic 6

Consider the Oracle relation

customer(cid number(5),first_name varchar2(10),last_name varchar2(10),
ph_no number(10) , address varchar2(25))

Inserting into customer relation from vb6 form:

Assume we enter the following fields in the following controls in the vb form

cid - text1
first_name - text2
last_name - text3
ph_no - text4
address - text5

The following code inserts a new record into the customer table.

Dim oconn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim str As String
Set oconn = New ADODB.Connection
oconn.Open "Provider=msdaora;Data Source=localhost;User Id=paul;Password=oracle;"
oconn.CursorLocation = adUseClient

strSQL = "insert into customer(cid,first_name,last_name,ph_no,address) values(" + Text1.Text + ",' " + Text2.Text + " ',' " + Text3.Text + " '," + Text4.Text + ",' " + Text5.Text + " ') "

oconn.Execute strSQL

The statement,

strSQL = "insert into customer(cid,first_name,last_name,ph_no,address) values(" + Text1.Text + ",' " + Text2.Text + " ',' " + Text3.Text + " '," + Text4.Text + ",' " + Text5.Text + " ') "

inserts a new record.We use the + symbol to concatenate the input data to be inserted into the oracle table.When we want to insert a numeric field we just enclose it with double quotes.This is a part of vb syntax.When we want to insert character or string field ,we first enclose it with
double quotes as a part of vb syntax,then we have to enclose it with single quotes as a part of oracle syntax(oracle requires character or string fields to be entered in single qoutes).

Deleting from customer realtion :

Consider we delete a record based on the entered customer id(cid) value.Let the cid value be entered in textbox1 in the form.The following code deletes a record from the table.

Dim oconn As New ADODB.Connection
Dim f As String
Dim rs As New ADODB.Recordset
Dim strSQL As String
Set oconn = New ADODB.Connection
oconn.Open "Provider=msdaora;Data Source=localhost;User Id=paul;Password=oracle;"
strSQL = "delete from customer where cid=" + Text1.Text + " "
oconn.Execute strSQL

Connecting vb6 to oracle using ADO object

Inorder to use ADO object, first you have to add a reference to the ADO object library.To add a reference select Project->References from the visual basic menubar.then select Microsoft ActiveX Data Objects Library.There are many versions available like 2.6,2.7 etc.,You may select any version supported by your computer.

Then you have to include a ADO Data Grid Control to display your oracle table or any recordset.Inorder to do that ,select Project->Components from Visual Basic menubar and select Microsoft Data Grid Control 6.0.When you click the OK button,the DataGrid Control is added to the Visual Basic toolbox.

With the DataGrid control in your Toolbox, now it's time to add it to your form.

Let's place this code in the Load Event Procedure of the Form.

Dim oconn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM EMPLOYEES"
Set oconn = New ADODB.Connection
oconn.Open "Provider=msdaora;Data Source=local host;User Id=paul;
Password=yourpassword;"
rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.Open strSQL, oconn, , , adCmdText
Set DataGrid1.DataSource = rs

Let me explain. These first two lines of code declare 2 object variables.The first is an ADO Connection Object

Dim oconn As New ADODB.Connection

followed by an ADO Recordset Object

Dim rs As New ADODB.Recordset

Now we have to declare a string variable to 'hold' the SQL statement.

Dim strSQL As String

Now we assign a SQL statement to the string variable.Let us assign a SQL statement to display all the records of the EMPLOYEES table.

strSQL = "SELECT * FROM EMPLOYEES"

Our next step is to open our ADO Connection. We do that by executing the Open method of our Connection Object. The Open method is looking for four parameters: the Provider name, the Data Source (or HostName), the User ID and the Password of the database.

oconn.Open "Provider=msdaora;Data Source=local host;User Id=paul Password=yourpassword;"

This is actually the connection string that we built while using ADO Data Control.You can also build the connection string using the ADO Data Control and then copy and paste it here.

Before we build the Recordset object we need to adjust three properties of the Recordset object the CursorType,CursorLocation and LockType.

rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic

Now it's time to open the Recordset.

rs.Open strSQL, oconn, , , adCmdText

Now we can use the Set statement to assign the Recordset object to the DataSource property of our DataGrid.

Set DataGrid1.DataSource = rs

That's all,now it's time to run your program.The code in the load event procedure gets executed.A Connection object is created, initiating the Connection to your Oracle Database.Then a Recordset object is created, retrieving Employee records.Finally, the DataSource property of the DataGrid is set to point to the Recordset object.Thus all the records of the EMPLOYEES table gets displayed in the Data Grid.

Thursday, December 10, 2009

Connecting VB 6 to Oracle Database

We can connect to vb6 with oracle by two methods :
1.Using ADO data control.
2.Using ADO object.

1.Using ADO data control to connect to oracle:

First you have to know your hostname for oracle server you are using.This can be found in the TNSNAMES file which comes installed along with the oracle.You just need to locate the file and find the host name.Most commonly the host name is like "localhost" or anything.Now with knowing your host name connecting to oracle is easy.

First, let's add the ADO Data Control to the Visual Basic Toolbox by selecting Project-Components from the Visual Basic Menu Bar, then selecting
Microsoft ADO Data Control 6.00 (be sure it has OLEDB at theend of the name) and clicking the OK Button.

For this demonstration, we'll be populating a Data Grid with the data from the Employees table in an Oracle table I've built. We need to add the Data Grid to the Visual Basic Toolbox first, and we do that just the way we added the ADO Data Control by selecting Project-Components from the Visual Basic Menu Bar. An important point here---you must use the OLEDB version of the DataGrid in conjunction with the ADO Data Control---as you can see in the screen shot below, this DataGrid has the word OLEDB after it in the selection list. DON'T select the Data Bound Grid Control 5.0 --- that grid can only be used with the DAO Data Control.

As you can see, both the ADO Data Control and the DataGrid Control have been added to the Visual Basic Toolbox.

With both controls in your Toolbox, now it's time to add them to your form.

Bring up the Properties window for your ADO Data Control and select the Connection Property--this is the key to achieving the connection. The three dots (ellipsis) indicates that a window will open for you when you click on it.

This is a Property Page for the Connection Property. Click on the Build button to start 'building' the Connection String.

And this window will appear, asking you to select the Provider for your database. For Oracle, you want to select the Microsoft OLE DB Provider for Oracle. Do so, then click on the Next button.

The Oracle Connection requires that you designate a Server name, a User Name, and a Password. The Server name is the name of your Host File as designated in the TNSNAMES file I mentioned earlier. In my instance, it is 'local host'. My User name is 'paul' and my password is (well, that's a secret). Notice how I have checked 'Allow saving password'---this eliminates a nasty popup dialog box prompting you for a password from appearing when you execute the program containing this connection.

Enter your own Host Name, User name and Password for your Oracle database.
Be sure to test the connection by clicking on the 'Test Connection' button.

If the information you supply is correct, you should see this dialog box appear.

If the information you supply is incorrect, you'll receive this sad message.

If that happens, check with your Oracle Database Administrator to verify the name of your Host File, your User Name and Password.

If your connection test proves successful, click on the OK button and you should notice that the Connection String Property of your Data Control has been filled in for you.

When we discuss using ADO Objects to achieve your connection, the Connection String value that you see here will be used in code.

Now that you've verified your Connection, you've done the work necessary to open your Oracle Database---now you need to tell Visual Basic the exact information you require. This could be a table name, or it could be a recordset built by a SQL Statement. Regardless, you need to provide some information to Visual Basic in the RecordSource property of the ADO Data Control. Select the RecordSource Property. Again, the three dots (ellipsis) indicates that a window will open up when you click on it.

The easiest type of connection to achieve is one where you specify a table name, and you start that process by selecting adCmdTable in the Command Type dropdown listbox. When you do so, a list of tables for your database will appear in the Table or Stored Procedure Name dropdown listbox. My Oracle database contains two tables, Employees and Vendors. I'll select Employees.

You could also choose to build your connection using a SQL statement instead of a single table name.SQL statements allow you to build a recordset with information from more than one table.To specify a SQL statement instead of a table name ,specify adCmdtText as the Command Type and enter your SQL Statement into the Command Text Textbox.

Either way, click on the OK button, and you should see that the RecordSource property of your Data Control now has a value.

The last step is to bind the ADO Data Control to another control capable of displaying the data from our Oracle table. For demonstration purposes, nothing could be easier than the DataGrid. Bring up its Property window, and select the ADO Data Control for its DataSource property.

Now run the program, and you see that the DataGrid is now populated with the data present in the Employees table of your Oracle database.