anyone know how to deal with oracle in vb 6.0

[peacer]

hi, i have a problem again.

this time round is about oracle and vb 6.0

do anyone knows how to add new records, update records to the oracle database and delete records from the oracle database using vb 6.0

ah xing

[phinds]

read the ADO tutorial at the link below my name. Check the on-line help for the connection string necessary for Oracle instead of Access

[doofusboy]

Here are 2 ways of connecting to Oracle through VB that I dug up from my files:

Dim adoCnn As New ADODB.Connection
 
'* Open Connection.
adoCnn.Open "Provider=MSDAORA;Password=Password;User ID=Username;Data Source=DataSource"
 
'* Advise user of result.
If adoCnn.State <> 1 Then
    MsgBox "Unable to connect to Oracle!", vbInformation
Else
    MsgBox "Connected to Oracle!", vbInformation
End If
 
'* Close Connection.
adoCnn.Close

You can also connect using 0040...

Dim oraSession As Object
Dim oraDatabase As Object
 
Dim strDatabase As String
Dim strUser As String
 
strDatabase = "Database"
strUser = "Username/Password"
 
Set oraSession = CreateObject("OracleInProcServer.XOraSession")
Set oraDatabase = oraSession.DbOpenDatabase(strDatabase, strUser, 0&)
 
MsgBox "Connected to " & oraDatabase.Connect & "@" & oraDatabase.DatabaseName
 
Set oraDatabase = Nothing
Set oraSession = Nothing

[RudyL]

It isn't all that different than M$ Acce$$.. I often use M$ to help creat intense SQL strings..

The only major difference is how you connect..

Rudy

[ahara]

Looks like you have ample ways to connect....for your specific SQL statements, I should warn you (I am working with Oracle 8i right now), there are the occasional curves depending on the data types. Oracle's flavour of SQL is extraordinarily powerful and capable of almost anything, so let us know if there are specific SQL statements you may be having trouble with. Cheers.

[RudyL]

one more tip, in M$ Acce$$ you need to use "#" around date/time fields, in oracle you only need to use "'"

[ahara]

Indeed, RudyL has pointed out one of the differences when pass/retrieving dates, and if I may elaborate....if the format of date you are using does not match the default system date format in your version of Oracle, you will need to use the to_date function on insert, and the to_char function when retrieving it...

ie to_date('28/03/99', 'dd/mm/yy')

Now, we are getting a bit off topic for a "Visual Basic" forum, so if you run into these kind of SQL questions, you may want to post another thread on one of the DB forums. Cheers.

[peacer]

is there a big different between Oracle and MS Access, i mean the declaration....beside the connection string, is there any more differences...

Ah Xing

[RudyL]

Originally posted by peacer
is there a big different between Oracle and MS Access, i mean the declaration....beside the connection string, is there any more differences...

Ah Xing

There are alot of subtle differences.. To many to get into them in general... If you had a more specific question, or a particular sql string you need help with it would not be a problem..

I don't mean to sound unhelpfull but there can be so many different things to talk about.

My experience tells me that if you know one you should not have to much trouble with the other..

Rudy

[peacer]

one example:

Dim rslocal As New ADODB.Recordset
On Error GoTo ShowError:
rslocal.CursorLocation = adUseClient
rslocal.Open "Select * From TB_PHOTO_CONTENT", con, adOpenDynamic, adLockOptimistic
With rslocal
.AddNew
!Photo_Serial_no = .RecordCount
GetPhoto File1.Path & "\" & File1.List(File1.ListIndex), rslocal, "Image", "PicSize"
!PicName = File1.filename
!PersonelName = Text1.Text
.Update
End With

this is the coding for adding a record to the MS Access database.
then what will be the coding if i want it to add into Oracle Database

Ah Xing

[ahara]

Hello;

Your previous example of code should also work in Oracle, however I think another key point should be made. Another significant difference between Access and Oracle is performance. This is why Oracle is used for the really big and important stuff. Oracle has caching capabilities that Access does not. When you send a query to Oracle, it first checks the cache to see if it is already there. If not, it recompiles it and runs it - but if it finds it in the cache, the query will fetch the records more efficiently. We are an Oracle 8i shop over here. We have implemented a standard of using paramterized command objects only for all sql statements. The following example illustrates:

dim cmdCommand as new ADODB.Command
set cmdCommand.ActiveConnection = [your connection object]
cmdCommand.CommandType = adCmdText
dim prmParameter as new ADODB.Parameter
dim sql as String
 
sql = "update tableA set field1=?, field2=?, field 3=? WHERE rec_id=?"
 
set prmParameter = cmdCommand.CreateParameter("field1", [data type], adParamInput, [size of field], [value])
cmdCommand.Parameters.Append prmParameter
 
... 'add other two parameters
 
cmdCommand.CommandText = sql
cmdCommand.Execute

Now if this is totally new to you, check out MSDN's tutorial at

http://msdn.microsoft.com/library/de...jparameter.asp

With regards to the code above.....Notice how the sql statement uses "?" marks. These represent the paramters going in. The parameters must be specified in the same order as the "?" marks in the sql statement. So each time this statement comes into Oracle's sql compiler, it will be found in the cache and run right away. I hope this does not complicate matters too much for you, but Oracle is such a good database (WAY BETTER THAN ACCESS), I truly believe as developers we should make the most of it. Cheers!

[peacer]

when i run this application and wanted to make a connection, it come out with an error...

this is the code i use to connect:

con.Open "Provider=MSDAORA;Password=sitcro;User ID=crosit;Data Source=Crot3;Locale Identifier=1033"


what is "Locale Identifier=1033" means...??????/

this is the error msg i got :
Run-time error
ORA-12154: TNS:could not resolve service name

what this error msg suppose to mean....????

Ah XIng

[RudyL]

Originally posted by peacer
when i run this application and wanted to make a connection, it come out with an error...

this is the code i use to connect:

con.Open "Provider=MSDAORA;Password=sitcro;User ID=crosit;Data Source=Crot3;Locale Identifier=1033"


what is "Locale Identifier=1033" means...??????/

this is the error msg i got :
Run-time error
ORA-12154: TNS:could not resolve service name

what this error msg suppose to mean....????

Ah XIng

The TNS error is because what you have setup in Oracle as the DSN (Data Source Name) does not match what you put in "Provider=MSDAORA". If you are using Oracle 8 or higher goto the Oracle Data Source Administrator and click on the System DSN tab. Then double click on the one that is your Oracle database. The screen that pops up will display the TNS name.

Rudy

[peacer]

RudyL, any wrong in this coding...because when i run the application, it will hang and will not respone...

the zipped file i upload is only one of the part...as i cant upload all cause its too great to be uploaded.

im using Oracle Database and vb 6.0....
i can connect to Oracle Database aready, but the main problem comes aready, the images in Oracle Database is "ORABLOB" mean in blob.
So now i have to convert the blob to binary before i can retrieve and put into a folder. the Oracle Database has about 2000++ records, and i have to retrieve all the images out.

if youu have better example im more than appreciate...

Ah Xing...

[ahara]

Hello;

I took a peek at your code, and it reminded me of some work I did a little while ago.....I created a little module that uploads and retrieves Excel files from and to Oracle. Now I don't mean the content of the file, the actual file itself stored as a BLOB object, which is what you are doing with your images. The following code worked just fine at uploading a file and saving it to the user's pc which I believe is what you want to do with images:

Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim mStream As ADODB.Stream
    Dim sconnstmt As String
    Dim iId As String
    
    Set mStream = New ADODB.Stream
    
    Set conn = New ADODB.Connection
    sconnstmt = "Provider=OraOLEDB.Oracle;Password=[pwd];User ID=[userID];Data Source=[dataSource];Persist Security Info=True"
    conn.Open sconnstmt
        
    Set rs = New ADODB.Recordset
    rs.Open "select * from test_file where file_id = " & theId, conn
    
    Set mStream = New ADODB.Stream
    mStream.Type = adTypeBinary
    mStream.Open
    
    mStream.Write rs.Fields(1).Value
    mStream.SaveToFile "c:\temp\fileForupdate.xls", adSaveCreateOverWrite
 
    
    MsgBox "The Excel file has been renamed to 'fileForupdate' and has been placed in the c:\temp directory"

Hope that helps....let me know if you would like to see the code that uploads TO the database. Cheers!

[peacer]

i have seen your code....i want ask you a quest, why i must save to file ah...what this one mean.

My Oracle have about 2000++ records, and i need to retireve all out and put into a folder, by a click of a button, like what i have uploaded to the forum.

so in that case i need a for loop in the vb application right....

izzit everytime i .AdTypeBinary right i will need to have .SaveToFile for the use of .Stream

Ah Xing

[peacer]

i have use your method to do on my application, but when i retrieve the blob(the images in the oracle database) and put into the folder right, the folder is empty, mean that it doesn't retrieve anything out from the oracle database.

this is what i have done:

Private Sub btnGetImages_Click()
On Error Resume Next
Dim strFullPath As String
strFullPath = App.Path & "\img\"
Dim arrImgData() As Byte
Dim strImageName As String
Dim i As Integer
Dim objStream As ADODB.Stream

On Error Resume Next
If Dir(strFullPath) = "" Then
MkDir strFullPath
End If

'position the recordset on the first recordset
objRs.MoveFirst
'Do While Not objRs.EOF
For i = 1 To 2

Set objStream = New ADODB.Stream
Set objRs = New Recordset
'Read the record from the database
strImageName = objRs("PHOTO_SERIAL_NO")
With objStream
.Type = adTypeBinary
.Open
.Write objRs.Fields("IMAGE").Value
.SaveToFile strFullPath & strImageName, adToSaveCreateOverWrite
End With
'create the file
'Open strFullPath & strImageName For Binary As #1
'arrImgData = objRs("IMAGE").GetChunk(objRs("IMAGE").ActualSize)
'Write to the file
'Put #1, , arrImgData
'Close #1
'MsgBox "Done"
'move on the next record
objRs.MoveNext
Next
'Loop
MsgBox "Done"

RetrieveAllImagesFrm.Hide

End Sub

Private Sub Form_Load()

objConn.Open "Provider=MSDAORA;Password=demo252;User ID=demo234;Data Source=demo252;Locale Identifier=1033"

End Sub


its working but nothing is retrieved from the oracle....

Ah Xing

[ahara]

Having looked at the code you posted, I see the following line as a problem:

Set objRs = New Recordset

It occurs right before you try to read a record in. This will effectively reinstantiate the record set and wipe out all that was there before. If you fix this, and still you are not getting records, check the cursor location of your connection object - in my case it was adUseServer.

[peacer]

ahara, i have an solution, but it can only retrieve one record at a time, can you help me look at this code see is it possible that it can loop itself and retrieve all to a folder....

this is the code i have:

Private Sub btnGetImages_Click()
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim PartImage As OraBlob
Dim buffer() As Byte
Dim filename As String
Dim i As String
Dim rss As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim j As Integer

'Create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")

'Create the OraDatabase Object.
Set OraDatabase = OraSession.OpenDatabase("crot3", "crosit/sitcro", 0&)

'Add PartDesc as an Output parameter and set its initial value.
OraDatabase.Parameters.Add "OutImage", Null, ORAPARM_OUTPUT
OraDatabase.Parameters("OutImage").serverType = ORATYPE_BLOB

'Begin the transcation
OraSession.BeginTrans
rss.Open "Provider=MSDAORA;Password=sitcro;User ID=crosit;Data Source=Crot3;Locale Identifier=1033"
rs.Open "Select PHOTO_SERIAL_NO FROM TB_PHOTO_CONTENT", rss, adOpenKeyset, adLockOptimistic

'Execute the statement returing 'PartDesc'
OraDatabase.ExecuteSQL ("BEGIN select IMAGE into :OutImage from TB_PHOTO_CONTENT where photo_serial_no like '" & Combo1.Text & "' for update NOWAIT; END;")

'Get 'PartDesc' from Parameters collection
Set PartImage = OraDatabase.Parameters("OutImage").Value

i = Text1.Text
'Get a free file number
'FNum = FreeFile
'Open the file.
filename = App.Path & "\img\" & i & ".jpg"
'Open filename For Binary As #FNum

'Re adjust the buffer size to hold entire file data
'ReDim buffer(LOF(FNum))

'Get #FNum, , buffer

'Do one write operation
'amount_written = PartImage.Write(buffer)
PartImage.CopyToFile (filename)

'Close FNum

MsgBox "Amount written to the LOB data is " & amount_written

'Ends the trascation
OraSession.CommitTrans

MsgBox "Write operation successfull"


End Sub

[ahara]

Hello;

I have never used the API you are using for Oracle, but if I understand your present stage, you need to loop through all the jpeg files in the folder (?? - Ithink) If this is what you meant by your question, I would check out the file system object (FSO). It has methods that allow you to retriev a folder, and then loop through the contents of that folder and grab the files you need)....for more info on FSO, check out:

http://msdn.microsoft.com/library/de...bjectmodel.asp

[peacer]

this is the problem i have:

i have an error msg stated: Byref argument type mismatch

this is my code:

Private Sub Form_Load()
Text1.Enabled = False
Text2.Enabled = False
Dim objConn As New ADODB.Connection
Dim obj As New ADODB.Recordset
Dim i As Integer

objConn.Open "Provider=MSDAORA;Password=sitcro;User ID=crosit;Data Source=Crot3;Locale Identifier=1033"
obj.Open "SELECT PHOTO_SERIAL_NO FROM TB_PHOTO_CONTENT", objConn, adOpenDynamic, adLockOptimistic

Combo1.Clear
For i = 1 To obj.RecordCount
While obj.EOF = False
Combo1.AddItem obj.Fields("PHOTO_SERIAL_NO")
obj.MoveNext
Wend
Next i
obj.close
If Combo1.ListCount > 0 Then
Combo1.Enabled = True
Combo1.ListIndex = 0
Else
Combo1.Enabled = False
End If


End Sub

Private Sub UpdateAllImages_Click()
'Ignore this code its for adding images in the DB
Dim strFile As String
Dim files As New Collection
Dim file As Variant
Dim pattern As String
Dim pic As StdPicture
Dim i As String

strFile = App.Path & "\img\"
pattern = "*.jpg"

Set files = getFilesInDirectory(strFile, pattern)

i = Text2.Text
filenames = App.Path & "\img\" & i

Call FileToBlob(Image, filenames)

MsgBox "Images Updated"

RetrieveAllImagesFrm.Hide
End Sub

' Copy a file's contents into a BLOB field.

Sub FileToBlob(fld As ADODB.Field, filename As String, Optional ChunkSize As Long = 8192)
Dim fnum As Integer, bytesLeft As Long, bytes As Long
Dim tmp() As Byte

' Raise an error if the field doesn't support GetChunk.
If (fld.Attributes And adFldLong) = 0 Then
Err.Raise 1001, , "Field doesn't support the GetChunk method."
End If
' Open the file; raise an error if the file doesn't exist.
If Dir$(filename) = "" Then Err.Raise 53, , "File not found"

fnum = FreeFile
Open filename For Binary As fnum
' Read the file in chunks, and append data to the field.
bytesLeft = LOF(fnum)
Do While bytesLeft
bytes = bytesLeft
If bytes > ChunkSize Then bytes = ChunkSize
ReDim tmp(1 To bytes) As Byte
Get #1, , tmp
fld.AppendChunk tmp
bytesLeft = bytesLeft - bytes
Loop

Close #fnum
End Sub

the bolded part is when the error occur...!!!!!!

Ah Xing

[abhijit]

Originally posted by ahara
Hello;

Your previous example of code should also work in Oracle, however I think another key point should be made. Another significant difference between Access and Oracle is performance. This is why Oracle is used for the really big and important stuff. Oracle has caching capabilities that Access does not. When you send a query to Oracle, it first checks the cache to see if it is already there. If not, it recompiles it and runs it - but if it finds it in the cache, the query will fetch the records more efficiently. We are an Oracle 8i shop over here. We have implemented a standard of using paramterized command objects only for all sql statements. The following example illustrates:

dim cmdCommand as new ADODB.Command
set cmdCommand.ActiveConnection = [your connection object]
cmdCommand.CommandType = adCmdText
dim prmParameter as new ADODB.Parameter
dim sql as String
 
sql = "update tableA set field1=?, field2=?, field 3=? WHERE rec_id=?"
 
set prmParameter = cmdCommand.CreateParameter("field1", [data type], adParamInput, [size of field], [value])
cmdCommand.Parameters.Append prmParameter
 
... 'add other two parameters
 
cmdCommand.CommandText = sql
cmdCommand.Execute

Now if this is totally new to you, check out MSDN's tutorial at

http://msdn.microsoft.com/library/de...jparameter.asp

With regards to the code above.....Notice how the sql statement uses "?" marks. These represent the paramters going in. The parameters must be specified in the same order as the "?" marks in the sql statement. So each time this statement comes into Oracle's sql compiler, it will be found in the cache and run right away. I hope this does not complicate matters too much for you, but Oracle is such a good database (WAY BETTER THAN ACCESS), I truly believe as developers we should make the most of it. Cheers!

I agree. Binding is the way to go for Oracle.
Cheers,
Abhijit