Anyone here have any oracle / VB 6 experience?

Discussion in 'Software' started by Wookie, Aug 10, 2004.

  1. Wookie

    Wookie Sergeant Major

    Worth a shot im not getting a big reply at other forums and I am kind of stuck.

    Heres my problem if anyone knows anything about databases
    -----------------------------------------------------------

    I am trying to connect to an oracle database. There is an oracle ODBC driver on the system with a DSN set up called tns:live

    The driver name is sqora32.dll

    I tried connecting to it like this
    Code:
    Code:
     
     Dim oCn1 As ADODB.connection 
     Dim oRs1 As ADODB.Recordset 
     Dim connection1 As String 
     connection1 = "Provider=MSDAORA.1;Password=xxxx;User ID=xxxx;Data Source=tns:live;Persist Security Info=True" 
     oCn1.Open connection1 
     


    It didnt work. I am guesssing because the driver is different. Would I change the provider and what would I change it to?

    I also have an excel file using VB that connects just fine like this:
    Code:
    Code:
     
     Sub RetrieveData() 
     Dim Chan As Variant 
     Dim Stmt As String 
     
     Chan = SQLOpen("DSN=Live Database") 
     
     
    from what ive researched thats using xlodbc.xla which is an excel add in. I dont think I can use sqlopen in VB 6, but correct me if I am wrong.

    Should I research SQLopen more or is it just a driver name problem? I appreciate any help anyone has to offer. Thanks
     
    Last edited by a moderator: Aug 10, 2004
  2. Kodo

    Kodo SNATCHSQUATCH

    have you tried it with out doing a DSN?
    make a udl file and configure it appropriately, then edit the udl file with a text editor and there is your string..
     
  3. Wookie

    Wookie Sergeant Major

    ah sheit man didnt know that would give me a connection string. Ill try that string thanks
     
  4. Wookie

    Wookie Sergeant Major

    P.S. the name and pass were fake :) thanks anyway tho.
     
  5. Kodo

    Kodo SNATCHSQUATCH

    hey, we gotta watch our bro's backs ;)
     
  6. Kodo

    Kodo SNATCHSQUATCH

    what's the verdict ?
     
  7. Wookie

    Wookie Sergeant Major

    Ill find out tomorrow, had to do tech support all day then go troubleshoot an adtran 612 with a cisco Pix. Fun stuff.
     
  8. Wookie

    Wookie Sergeant Major

    Things arent going good right now the DSN quit working, using oracle net8 config assistant I keep getting a no listener error but the server is up fine, there is an error message on the screen tho but I know nothing about unix and this is a main server for 5 stores :( not a good morning.
     
  9. Kodo

    Kodo SNATCHSQUATCH

    do you have the latest MDAC?
     
  10. Wookie

    Wookie Sergeant Major

    Got it going thankfully, the company called me back (I was shocked) and I guess they restarted the server a while back and the listener service doesnt auto start, they walked me through how to start it so I can connect. My Software keeps erroing out so I am installing oracle client on my work laptop right now so I an connect with it and debug.

    Hopefully Ill figure this crap out :) I got the connection strung from the UDL file tho thanks.
     
  11. Wookie

    Wookie Sergeant Major

    Heres my problem, when I try to open I am geting this error

    "error cannot load resource file sqresus.dll"


    Wonder why I am getting that
     
  12. Kodo

    Kodo SNATCHSQUATCH

    Try reinstalling your Oracle ODBC driver and reboot.
     
  13. Wookie

    Wookie Sergeant Major

    reboot worked

    now I am getting this error here

    Code:
    oRs1.Open "ITM_PUR_HST", oCn1, adOpenKeyset, adLockOptimistic, adCmdTable

    Error: Object variable or with block variable not set


    boy debugging is fun :)
     
  14. Kodo

    Kodo SNATCHSQUATCH

    why are you opening up a whole table? that's slow. You should pick the fields you want even if you want them all.
     
  15. Wookie

    Wookie Sergeant Major

    I got it to work, ill refine everything later I am just making sure I can get the connection going.

    Code:
    Set oCn = New ADODB.connection
    Set oRs = New ADODB.Recordset
    I had forgotten that :)

    I am rushed right now ill refine my SQL later, gotta go fix an email now then go listen to my boss bitch that I havnt got this done after he sends me 3 other places.

    I love computers :)
     
  16. Wookie

    Wookie Sergeant Major

    MUHAUAHAUAHAUAHHAUAH


    Its the greatest feeling to see something you created work :)

    Thanks for your help Kodosan
     
  17. Kodo

    Kodo SNATCHSQUATCH

    I hear that my man! :)
     
  18. Wookie

    Wookie Sergeant Major

    Ill have to get on making that SQL statement it takes forever to extract it lol
     
  19. Wookie

    Wookie Sergeant Major

    yeah about 2 minutes only got me 1200 records, very slow
     
  20. Kodo

    Kodo SNATCHSQUATCH

    holy crap.. yeah man.
     
  21. Wookie

    Wookie Sergeant Major

    Maybe you can help me out here, im not used to programming with anything bigger than a 1 meg access database. This oracle database is huge, records since 1997 for sales for 6 diff furniture stores. I need to check to make sure I dont output duplicate names for sales. Whats the fastest way to do this? Ive had a busy day and am having rouble thinking of a way to do this right now and the damn air isnt working in the office which makes it even harder to think.
     
  22. Kodo

    Kodo SNATCHSQUATCH

    Select DISTINCT fieldname from tablename where somecondition=somecriteria

    make sure you explicitly state the fieldnames you want to pull. use AdLockReadOnly and AdOpenForwardOnly (as they are the fastest) if you're just selecting data without modifying it.
     
  23. Wookie

    Wookie Sergeant Major

    ok let me see how clear I can make it, It goes through the database and searches for records based on a criteria,

    There is a record of sales for items, sometimes a person might buy more than item and they go in 2 + times, but they dont want to pull this record 2 + times only once. If I grab it once how am I to decide whether or not I already got this person. The sales and the persons info are in two seperate tables. So I look at a sale, I see a customer number I compare that customer number to another table and once I find a match add it to the excel file. I cant think of a way of knowing that I already grabbed a record without cycling all the way through my file and checking again, which would require two connections which I dont think is possible, unless I make two files, one that will take written data and one for double input comparison. You see what I am saying now?
     
  24. Wookie

    Wookie Sergeant Major

    oRs.Open sql, oCn, adOpenKeyset, adLockOptimistic, adCmdTable

    All I am doing is reading data how is that connection? adLockOptimistic?

    Nowhere in this program will I be editing data or adding new records. Only reading
     
  25. Kodo

    Kodo SNATCHSQUATCH

    got a phone number I can call you at? I hate typing this crap..
     
  26. Wookie

    Wookie Sergeant Major

    check your PM
     
  27. Wookie

    Wookie Sergeant Major

    Is this select possible?

    Code:
    ql1 = "SELECT DISTINCT ITM_PUR_HST.CUST_CD FROM ITM_PUR_HST WHERE ITM_PUR_HST.STAT_CD = " & Temp & " AND ITM_PUT_HST.FINAL_DT BETWEEN " & Calendar1.Value & " AND " & Calendar2.Value
    
    I was sure if I could user STAT_CD since I didnt select it, if I can what do you think of this statement it gets me all the sales that are distinct then I can just compare or joint to the other table. Let me know

    Thanks
     
  28. Kodo

    Kodo SNATCHSQUATCH

    in criteria, you can use any valid field name even if you didn't select it. Is your calendar value a date? or is it an integer?
     
  29. Wookie

    Wookie Sergeant Major

    Its a date, I tested it in a msgbox, replies something like 8/16/2004, im hoping the data isnt formatted 08/16/2004 right now
     
  30. Kodo

    Kodo SNATCHSQUATCH

    the leading 0 in the date shouldn't raise an error.. but you may have to close your statement with a &" " at the end since your last date is not an integer data type.
     
  31. Wookie

    Wookie Sergeant Major

    well if its a 08/16/2004 it will not match up if I look for 8/16/2004, I wont get any data. Easy fix tho if it comes to that.
     
  32. Kodo

    Kodo SNATCHSQUATCH

    it should.. easy fix would be to use the formatdatetime function to make all values universal.
     
  33. Wookie

    Wookie Sergeant Major

    Hey is there anyway to add a status bar to this. Case it takes a while dont want to user thinking it locked or something. Never used a status bar before. If you dont know ill google it.
     
  34. Kodo

    Kodo SNATCHSQUATCH

  35. Wookie

    Wookie Sergeant Major

    Got it working correctly

    Code:
    oRs.Open sql, oCn, adOpenForwardOnly, adLockReadOnly
    was adForwardOnly :) I love VB's drop down menu's for data members.

    I searched it by city and pulled 3800 records for one city. Took a good 5 minutes but I dont have the SQL statement looking I gotta redo that I was trying to test the connection as much as I could while I had time on site. Should be good to do what we wanted to do now.

    Thanks for all the SQL schoolin

    Code:
    SELECT ASPPROGRAMMER.Name WHERE ASPPROGRAMMER.Name > ALL
    Debug.output
    Kodosan
    
     
  36. Kodo

    Kodo SNATCHSQUATCH

    glad you got it working, wookie. One of my biggest problems is trying to rattle this stuff off without being at a keyboard and I always switch that around..lol.

    that 5 minutes bugs me though. Pulling only 3800 records should take about 2.5 seconds. Not 5 minutes. That ODBC driver has to be the issue with that part.
     
  37. Wookie

    Wookie Sergeant Major

    Here was the code I was using at the time for testing which wasnt very good

    Code:
    If Option1.Value = True Then
    sql = "SELECT CUST.FNAME, CUST.CITY, CUST.LNAME, CUST.CITY, CUST.ST_CD, CUST.ZIP_CD, CUST.ADDR1, CUST.ADDR2 FROM CUST;
    oRs.Open sql, oCn, adOpenForwardOnly, adLockReadOnly
        Do Until oRs.EOF = True
            If oRs.Fields("CITY") = Text1.Text And oRs.EOF <> True Then
            richtemp.Text = richtemp.Text & oRs.Fields("FNAME") & "," & _
            oRs.Fields("LNAME") & "," & oRs.Fields("ADDR1") & "," & oRs.Fields("ADDR2") & _
            "," & oRs.Fields("CITY") & "," & oRs.Fields("ST_CD") & "," & oRs.Fields("ZIP_CD") & vbCrLf
            End If
            oRs.MoveNext
        Loop
    End If
    
    I pretty much went through every record and looked to see if it matched my text box, I dodnt let the sql statement reduce the number of records, so the time may come down yet. I dont know if the status bar will work, the windw goes blank during the process. Ill get to try it again in a few days probably :)

    This is also an older server, my guess would be not over 200Mhz. Old Unix box, SCO I think
     
  38. Kodo

    Kodo SNATCHSQUATCH

    you could speed things up a bit by using an array here and a little bit of extra string manipulation. Would require a bit more programming but you'd probably be able to dump it all out at once instead of writing to the file every record hit. That can't be good for speed either. ;)
     
  39. Wookie

    Wookie Sergeant Major

    True, might do that.
     
  40. Wookie

    Wookie Sergeant Major

    How's this look
    Code:
    If Option1.Value = True Then
        sql = "SELECT CUST.FNAME, CUST.CITY, CUST.LNAME, CUST.CITY, CUST.ST_CD, CUST.ZIP_CD, CUST.ADDR1, CUST.ADDR2 FROM CUST WHERE CUST.CITY = '" & Text1.Text & "'"
        oRs.Open sql, oCn, adOpenForwardOnly, adLockReadOnly
        If Not oRs.EOF Then
            myAry = oRs.GetRows
        End If
        If IsArray(myAry) Then
            For i = 0 To UBound(myAry, 2)
                richtemp.Text = richtemp.Text & myAry(0, i) & "," & _
                myAry(1, i) & "," & myAry(2, i) & "," & myAry(3, i) & _
                "," & myAry(4, i) & "," & myAry(4, i) & "," & myAry(5, i) & vbCrLf
            Next
        End If
    End If
    Q: if I break the oRs into get rows with myAry will the fileds be in the same order I selected them in?
     
  41. Kodo

    Kodo SNATCHSQUATCH

    yes, the fields will be in the same order. the index starts at 0 as your first field.

    If Option1.Value = True Then
    sql = "SELECT CUST.FNAME, CUST.CITY, CUST.LNAME, CUST.CITY, CUST.ST_CD, CUST.ZIP_CD, CUST.ADDR1, CUST.ADDR2 FROM CUST WHERE CUST.CITY = '" & Text1.Text & "'"
    oRs.Open sql, oCn, adOpenForwardOnly, adLockReadOnly
    If Not oRs.EOF Then
    myAry = oRs.GetRows
    End If
    ors.close
    set ors=nothing 'do this here so you can release resources early.

    If IsArray(myAry) Then
    For i = 0 To UBound(myAry, 2)
    richtemp.Text = richtemp.Text & myAry(0, i) & "," & _
    myAry(1, i) & "," & myAry(2, i) & "," & myAry(3, i) & _
    "," & myAry(4, i) & "," & myAry(4, i) & "," & myAry(5, i) & vbCrLf
    Next

    Not sure why you're looping through here again. You're still hitting the
    text file on every increment of i. What you need to do is concatenate one full
    length of data (firstname, lastname,blah,blah,blah) and then mark the end of the
    string with line feed char. So your string would be:
    "blah,blah,blah,blah"&vbcrlf&"blah,blah,blah,blah"

    so now you have all the text moving to the next line after each increment.
    Then you could pump it out to your csv file in one shot :)
    End If
    End If
     

MajorGeeks.Com Menu

Downloads All In One Tweaks \ Android \ Anti-Malware \ Anti-Virus \ Appearance \ Backup \ Browsers \ CD\DVD\Blu-Ray \ Covert Ops \ Drive Utilities \ Drivers \ Graphics \ Internet Tools \ Multimedia \ Networking \ Office Tools \ PC Games \ System Tools \ Mac/Apple/Ipad Downloads

Other News: Top Downloads \ News (Tech) \ Off Base (Other Websites News) \ Way Off Base (Offbeat Stories and Pics)

Social: Facebook \ YouTube \ Twitter \ Tumblr \ Pintrest \ RSS Feeds