Home » Open Source » Programming Interfaces » Oracle and Visual Basic 6.0
icon5.gif  Oracle and Visual Basic 6.0 [message #269019] Thu, 20 September 2007 08:11 Go to next message
devendra_ora
Messages: 6
Registered: September 2007
Junior Member
Hi,
1. I wanted to know that how one can give runtime connection string in visual basic so as to connect to oracle database 10g.
2. I also wanted to know how we can verify users credentials and give permissions to modify,insert and view the database only in visual basic as frontend.
kindly help me.
Re: Oracle and Visual Basic 6.0 [message #269035 is a reply to message #269019] Thu, 20 September 2007 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase(<service name>, <username/password>, ORADYN_DEFAULT)

Of course, you have to previously install an Oracle client.

2.
Use a logon trigger to check the program (easiest but not safe way).
Use secure application role (check for this in documentation)

Regards
Michel

[Updated on: Thu, 20 September 2007 09:06]

Report message to a moderator

Re: Oracle and Visual Basic 6.0 [message #269143 is a reply to message #269035] Thu, 20 September 2007 16:31 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

You can do it also using ODBC driver
con.Open "Provider=OraOLEDB.Oracle.1;User ID=" & User_Name & ";Password=" & Pass_word & ";Data Source=" & data_Source
.
You can read the values User_Name,Pass_word,data_Source using INI file. Sample code is here:
Function Read_INI_File(dbName As String)
Dim fnum As Integer
Dim one_line As String
Dim pos As Integer
Dim iniValue As String

    fnum = FreeFile
    Open CurDir & "\" & "DBNAME.INI" For Input As fnum

    Do While Not EOF(fnum)
        ' Read a line.
        Line Input #fnum, one_line
        'MsgBox one_line
        ' See what it is.
        If Left$(one_line, 1) = "[" Then
            ' Section heading.
            iniValue = Mid$(one_line, 2, Len(one_line) - 2)
            If dbName = iniValue Then
                Line Input #fnum, one_line
                pos = InStr(one_line, "=")
                data_Source = Mid$(one_line, pos + 1)
                Line Input #fnum, one_line
                pos = InStr(one_line, "=")
                User_Name = Mid$(one_line, pos + 1)
                Line Input #fnum, one_line
                pos = InStr(one_line, "=")
                Pass_word = Mid$(one_line, pos + 1)
                'MsgBox databaseName & User_Name & Password
                Exit Do
            End If
        End If
    Loop

    Close fnum
End Function


INI File should contains like that:
[HR]
DATABASE=XE
USER=HR
PASSWORD=HR


Cheers
Soumen

[Updated on: Thu, 20 September 2007 16:32]

Report message to a moderator

icon7.gif  Re: Oracle and Visual Basic 6.0 [message #271098 is a reply to message #269019] Sat, 29 September 2007 06:00 Go to previous messageGo to next message
devendra_ora
Messages: 6
Registered: September 2007
Junior Member
Hi
Can anyone tell me how do we insert pictures(.jpg) in oracle database.What will be the SQL statement that will be used for inserting a picture say xyz.jpj in scott's schema.
regards
Devendra
Re: Oracle and Visual Basic 6.0 [message #271109 is a reply to message #271098] Sat, 29 September 2007 09:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this still a VB question?
Have a look at DBMS_LOB.LOADBLOBFROMFILE procedure.

Regards
Michel
Re: Oracle and Visual Basic 6.0 [message #271457 is a reply to message #269019] Mon, 01 October 2007 12:52 Go to previous messageGo to next message
devendra_ora
Messages: 6
Registered: September 2007
Junior Member
Hi
I am facing problem in displaying image in VB which is stored in oracle 10g database scott's schema.
Visual basic simply is not able to read the image !
Re: Oracle and Visual Basic 6.0 [message #271463 is a reply to message #271457] Mon, 01 October 2007 13:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unable to say anything with what you posted.

Regards
Michel
Re: Oracle and Visual Basic 6.0 [message #271467 is a reply to message #269019] Mon, 01 October 2007 13:22 Go to previous messageGo to next message
devendra_ora
Messages: 6
Registered: September 2007
Junior Member
1. The datatype of the stored image is BLOB in oracle database.
I am using VB's dataenvronment to connect to the oracle database.
But it does not retreves the field of image which is of BLOB type rest all fields VB is able to retrieve from oracle database.

2. Secondly one peculiar thing i noticed while working with Visual basic and oracle database,that if u change something in textbox which is connected to one of the fields of table in oracle database.And if u simply move to next record usng browse button in Vb form, the change gets updated even if I have notused or clicked the update button which uses
"dataevironment1.rsemp.Update" method in VB.
How to prevent this.
Re: Oracle and Visual Basic 6.0 [message #271486 is a reply to message #271467] Mon, 01 October 2007 13:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The datatype of the stored image is BLOB in oracle database.

Post a description of the table.

Quote:
I am using VB's dataenvronment to connect to the oracle database

Post code.

Quote:
But it does not retreves the field of image which is of BLOB type rest all fields VB is able to retrieve from oracle database.

Post code.

2. It is your control, see what are the options/attributes/methods.

Regardds
Michel
Re: Oracle and Visual Basic 6.0 [message #271596 is a reply to message #271486] Tue, 02 October 2007 02:35 Go to previous messageGo to next message
devendra_ora
Messages: 6
Registered: September 2007
Junior Member
The code given below is of the VB form which uses to connect to patient table of SCOTT in Oracle 10g.The problem is that when user changes text in the textbox and moves to next record by "de1.rspatient.MoveNext" the changed data gets updated on its own.
how to resolve this problem.
Private Sub Command1_Click()
de1.rsdoctor.AddNew
End Sub

Private Sub Command2_Click()
Dim u As Integer
u = MsgBox("Do you want to Update the data !", 1, "Confirm Update !")
If u = 1 Then
de1.rsdoctor.Update
End If
End Sub

Private Sub Command3_Click()
Dim a As Integer
a = MsgBox("Do you want to delete this Record !", 1, "Confirm Delete !")
If a = 1 Then
de1.rsdoctor.Delete
End If
End Sub

Private Sub Command4_Click()
de1.rsdoctor.MoveFirst
End Sub

Private Sub Command5_Click()
de1.rsdoctor.MovePrevious
If de1.rsdoctor.BOF Then
MsgBox ("You are at the First Record !")
de1.rsdoctor.MoveNext
End If
End Sub

Private Sub Command6_Click()
de1.rsdoctor.MoveNext
If de1.rsdoctor.EOF Then
MsgBox ("You are at the Last Record !")
de1.rsdoctor.MovePrevious
End If
End Sub

Private Sub Command7_Click()
de1.rsdoctor.MoveLast
End Sub


Re: Oracle and Visual Basic 6.0 [message #271609 is a reply to message #271596] Tue, 02 October 2007 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is what I said.
Check in your control parameter if you don't an attribute autocommit or commitOnChange or the like.

Do you use ODBC, OO4O, OLE Db...? What is your interface with Oracle?
Search for the same parameter in your interface.
Use transaction functions(like BeginTrans, EndTrans) to control the transactions.
...

Regards
Michel
Re: Oracle and Visual Basic 6.0 [message #272280 is a reply to message #271609] Thu, 04 October 2007 07:25 Go to previous messageGo to next message
devendra_ora
Messages: 6
Registered: September 2007
Junior Member
Thanks Michel !
I used Microsoft OLEDB for oracle ODBC driver for connecting with oracle.
I 'll check regarding the attribute parameters what u said and get back to u !
2. Secondly I had problem regarding views.I have created a view which connects one primary table and 7 other secondary table
which connect to the main table with primary key-foreign key relationship with the key ID(nunmber(10)).
Now the view so created reproduces cartesian product of all the 8 tables resulting into huge data.Although the tables contain only 4 records each.Can I get result from this view using where clause in which i give a partcular ID to find.
Re: Oracle and Visual Basic 6.0 [message #272291 is a reply to message #272280] Thu, 04 October 2007 08:14 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Missing conditions in where clause is likely the root of the problem.

Regards
Michel
Previous Topic: Microsoft ODBC for Oracle Can't Find Server
Next Topic: SQLJ Error
Goto Forum:
  


Current Time: Thu Mar 28 17:33:14 CDT 2024