PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
anyone know how to deal with oracle in vb 6.0-VBForums
Results 1 to 22 of 22

Thread: anyone know how to deal with oracle in vb 6.0

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Feb 2004
    Posts
    128

    anyone know how to deal with oracle in vb 6.0

    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

  2. #2
    PowerPoster
    Join Date
    Aug 2001
    Location
    new jersey
    Posts
    2,904
    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

  3. #3
    Fanatic Member doofusboy's Avatar
    Join Date
    Apr 2003
    Posts
    526
    Here are 2 ways of connecting to Oracle through VB that I dug up from my files:

    VB Code:
    1. Dim adoCnn As New ADODB.Connection
    2.  
    3. '* Open Connection.
    4. adoCnn.Open "Provider=MSDAORA;Password=Password;User ID=Username;Data Source=DataSource"
    5.  
    6. '* Advise user of result.
    7. If adoCnn.State <> 1 Then
    8.     MsgBox "Unable to connect to Oracle!", vbInformation
    9. Else
    10.     MsgBox "Connected to Oracle!", vbInformation
    11. End If
    12.  
    13. '* Close Connection.
    14. adoCnn.Close


    You can also connect using 0040...


    VB Code:
    1. Dim oraSession As Object
    2. Dim oraDatabase As Object
    3.  
    4. Dim strDatabase As String
    5. Dim strUser As String
    6.  
    7. strDatabase = "Database"
    8. strUser = "Username/Password"
    9.  
    10. Set oraSession = CreateObject("OracleInProcServer.XOraSession")
    11. Set oraDatabase = oraSession.DbOpenDatabase(strDatabase, strUser, 0&)
    12.  
    13. MsgBox "Connected to " & oraDatabase.Connect & "@" & oraDatabase.DatabaseName
    14.  
    15. Set oraDatabase = Nothing
    16. Set oraSession = Nothing
    Do canibals not eat clowns because they taste funny?

  4. #4
    Frenzied Member RudyL's Avatar
    Join Date
    Mar 2001
    Location
    Chicago
    Posts
    1,519
    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
    10 different ways to skin a cat and amazingly enough each and every one has the same result, the cat gets skinned! The same can be applied to code, so be nice and accept each others "preferences".

  5. #5
    Fanatic Member ahara's Avatar
    Join Date
    Nov 2003
    Location
    Toronto
    Posts
    531
    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.
    "Knowledge is gained when different people look at the same information in different ways"

    - Louis Pasteur

  6. #6
    Frenzied Member RudyL's Avatar
    Join Date
    Mar 2001
    Location
    Chicago
    Posts
    1,519
    one more tip, in M$ Acce$$ you need to use "#" around date/time fields, in oracle you only need to use "'"
    10 different ways to skin a cat and amazingly enough each and every one has the same result, the cat gets skinned! The same can be applied to code, so be nice and accept each others "preferences".

  7. #7
    Fanatic Member ahara's Avatar
    Join Date
    Nov 2003
    Location
    Toronto
    Posts
    531
    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.
    "Knowledge is gained when different people look at the same information in different ways"

    - Louis Pasteur

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Feb 2004
    Posts
    128
    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

  9. #9
    Frenzied Member RudyL's Avatar
    Join Date
    Mar 2001
    Location
    Chicago
    Posts
    1,519
    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
    10 different ways to skin a cat and amazingly enough each and every one has the same result, the cat gets skinned! The same can be applied to code, so be nice and accept each others "preferences".

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Feb 2004
    Posts
    128
    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

  11. #11
    Fanatic Member ahara's Avatar
    Join Date
    Nov 2003
    Location
    Toronto
    Posts
    531
    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:

    VB Code:
    1. dim cmdCommand as new ADODB.Command
    2. set cmdCommand.ActiveConnection = [your connection object]
    3. cmdCommand.CommandType = adCmdText
    4. dim prmParameter as new ADODB.Parameter
    5. dim sql as String
    6.  
    7. sql = "update tableA set field1=?, field2=?, field 3=? WHERE rec_id=?"
    8.  
    9. set prmParameter = cmdCommand.CreateParameter("field1", [data type], adParamInput, [size of field], [value])
    10. cmdCommand.Parameters.Append prmParameter
    11.  
    12. ... 'add other two parameters
    13.  
    14. cmdCommand.CommandText = sql
    15. 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!
    "Knowledge is gained when different people look at the same information in different ways"

    - Louis Pasteur

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Feb 2004
    Posts
    128

    problem connecting to oracle

    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
    Last edited by peacer; Mar 23rd, 2004 at 10:26 PM.

  13. #13
    Frenzied Member RudyL's Avatar
    Join Date
    Mar 2001
    Location
    Chicago
    Posts
    1,519

    Re: problem connecting to oracle

    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
    10 different ways to skin a cat and amazingly enough each and every one has the same result, the cat gets skinned! The same can be applied to code, so be nice and accept each others "preferences".

  14. #14

    Thread Starter
    Addicted Member
    Join Date
    Feb 2004
    Posts
    128

    RudyL, help me see this code

    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...
    Attached Files Attached Files
    Last edited by peacer; Mar 25th, 2004 at 02:38 AM.

  15. #15
    Fanatic Member ahara's Avatar
    Join Date
    Nov 2003
    Location
    Toronto
    Posts
    531
    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:

    VB Code:
    1. Dim conn As ADODB.Connection
    2.     Dim rs As ADODB.Recordset
    3.     Dim mStream As ADODB.Stream
    4.     Dim sconnstmt As String
    5.     Dim iId As String
    6.    
    7.     Set mStream = New ADODB.Stream
    8.    
    9.     Set conn = New ADODB.Connection
    10.     sconnstmt = "Provider=OraOLEDB.Oracle;Password=[pwd];User ID=[userID];Data Source=[dataSource];Persist Security Info=True"
    11.     conn.Open sconnstmt
    12.        
    13.     Set rs = New ADODB.Recordset
    14.     rs.Open "select * from test_file where file_id = " & theId, conn
    15.    
    16.     Set mStream = New ADODB.Stream
    17.     mStream.Type = adTypeBinary
    18.     mStream.Open
    19.    
    20.     mStream.Write rs.Fields(1).Value
    21.     mStream.SaveToFile "c:\temp\fileForupdate.xls", adSaveCreateOverWrite
    22.  
    23.    
    24.     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!
    "Knowledge is gained when different people look at the same information in different ways"

    - Louis Pasteur

  16. #16

    Thread Starter
    Addicted Member
    Join Date
    Feb 2004
    Posts
    128

    ahara

    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

  17. #17

    Thread Starter
    Addicted Member
    Join Date
    Feb 2004
    Posts
    128

    ahara

    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

  18. #18
    Fanatic Member ahara's Avatar
    Join Date
    Nov 2003
    Location
    Toronto
    Posts
    531
    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.
    "Knowledge is gained when different people look at the same information in different ways"

    - Louis Pasteur

  19. #19

    Thread Starter
    Addicted Member
    Join Date
    Feb 2004
    Posts
    128

    ahara....i have a solution aready

    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

  20. #20
    Fanatic Member ahara's Avatar
    Join Date
    Nov 2003
    Location
    Toronto
    Posts
    531
    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
    "Knowledge is gained when different people look at the same information in different ways"

    - Louis Pasteur

  21. #21

    Thread Starter
    Addicted Member
    Join Date
    Feb 2004
    Posts
    128

    ahara, or anyone can you solve this

    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
    Last edited by peacer; Mar 30th, 2004 at 12:40 AM.

  22. #22
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,219
    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:

    VB Code:
    1. dim cmdCommand as new ADODB.Command
    2. set cmdCommand.ActiveConnection = [your connection object]
    3. cmdCommand.CommandType = adCmdText
    4. dim prmParameter as new ADODB.Parameter
    5. dim sql as String
    6.  
    7. sql = "update tableA set field1=?, field2=?, field 3=? WHERE rec_id=?"
    8.  
    9. set prmParameter = cmdCommand.CreateParameter("field1", [data type], adParamInput, [size of field], [value])
    10. cmdCommand.Parameters.Append prmParameter
    11.  
    12. ... 'add other two parameters
    13.  
    14. cmdCommand.CommandText = sql
    15. 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
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width