OraSession Oracleinprocserver

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Prozedur liest nur Daten aus der Oracle Datenbank aus - kein Schreiben!
Hier ein Teil der Routine der am Besten beschreibt wie das mit ODBC war.
-------------------------------------------------------------------------
Sub perf_input()
Dim OraSession As Object
Dim OraDatabase As Object
Dim RS As Object
Sheets("STU1").Activate
Range("A1").Select
'oeffnen der Oracle DAtabase und üebernahme der Daten
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.DbOpenDatabase("appdbprod", "maskdba/maskdbapw", 0&)
SEL = "C.CODE,A.TYP"
SOU1 = "MASKDBA.AKTIVE_MASKEN A, "
SOU2 = "MASKDBA.RETICLES B, "
SOU3 = "MASKDBA.MASK_LOCATIONS C "
SOU = SOU1 & SOU2 & SOU3
con = "A.ID=B.ID_MASK and B.ID_LOCATION=C.ID and B.AKTIV = 1 and (C.CODE='N' or C.CODE='E' or C.CODE='I' or C.CODE='K' or C.CODE='Q' or C.CODE='T')"
Set RS = OraDatabase.DbCreateDynaset("select " & SEL & " from " & SOU & " where " & con, 0&)
'Auslesen des Recordset
I = 0
While Not RS.EOF
For j = 0 To RS.Fields.Count - 1
If j = 0 Then
LOC = RS.Fields(j).Value
If LOC = "N" Then C1 = C1 + 1
If LOC = "E" Then C2 = C2 + 1
If LOC = "I" Then C3 = C3 + 1
If LOC = "K" Then C4 = C4 + 1
If LOC = "Q" Then C5 = C5 + 1
If LOC = "T" Then C6 = C6 + 1
' If X1 End If
If j = 1 Then
LOC = RS.Fields(j).Value
If LOC If LOC >= 2 Then C8 = C8 + 1
End If
Next j
RS.MoveNext
I = I + 1
Wend
RS.Close
OraDatabase.Close
_________________________________________________________________________________________
Aufgrund der Tips habe ich zumindest die Anmeldung via ADODB hinbekommen, aber beim RS.OPEN habe ich noch gewlatige probleme:
' Microsoft ActiveX Data Object 2.n Library must be referenced
Dim conDB As ADODB.Connection ' Connection Object
Dim rsRows As ADODB.Recordset ' DB Recordset
Dim strConnect As String ' Connect-String
strConnect = "Provider=OraOLEDB.Oracle" & _
";Data Source=" & "appdbprod" & _
";User Id=" & "maskxxx" & _
";Password=" & "maskxxxpw"
' Make the Connection
Set conDB = New ADODB.Connection ' New Instance of Connection
conDB.ConnectionString = strConnect ' Set the ConnectionString in Object
conDB.Open strConnect ' Connect to the Database
' Open Recordset and execute SQL
Set rsRows = New ADODB.Recordset ' New Instance of Recordset
rsRows.CursorLocation = adUseClient ' Set the CursorLocation-Attribute
---> rsRows.Open "SELECT * FROM myTable", conDB, adOpenStatic ' Select to DB
'....processing recordset
keine Ahnung wie ich das mit dem Befehl von oben unter "Auslesen der Recordset" (Set RS = OraDatabase.DbCreateDynaset("select " & SEL & " from " & SOU & " where " & con, 0&)) ändern soll?
lg
Michael

Sub neu() ' Microsoft ActiveX Data Object 2.n Library must be referenced Dim conDB As ADODB.Connection ' Connection Object Dim rsRows As ADODB.Recordset ' DB Recordset Dim strConnect As String ' Connect-String strConnect = "Provider=OraOLEDB.Oracle" & _ ";Data Source=" & "appdbprod" & _ ";User Id=" & "maskxxx" & _ ";Password=" & "maskxxxpw" ' Make the Connection Set conDB = New ADODB.Connection ' New Instance of Connection conDB.ConnectionString = strConnect ' Set the ConnectionString in Object conDB.Open strConnect ' Connect to the Database ' Open Recordset and execute SQL Set rsRows = New ADODB.Recordset ' New Instance of Recordset rsRows.CursorLocation = adUseClient ' Set the CursorLocation-Attribute SEL = "C.CODE,A.TYP" SOU1 = "MASKDBA.AKTIVE_MASKEN A, " SOU2 = "MASKDBA.RETICLES B, " SOU3 = "MASKDBA.MASK_LOCATIONS C " SOU = SOU1 & SOU2 & SOU3 con = "A.ID=B.ID_MASK and B.ID_LOCATION=C.ID and B.AKTIV = 1 and (C.CODE='N' or C.CODE='E' _ or C.CODE='I' or C.CODE='K' or C.CODE='Q' or C.CODE='T')" DBQuery = "select " & SEL & " from " & SOU & " where " & con & ";" rsRows.Open Source:=DBQuery, ActiveConnection:=conDB, _ CursorType:=adOpenStatic, LockType:=adLockOptimistic ' copy whole recordset to a table, just for testing 'Worksheets("Tabelle1").Cells(1, 1).CopyFromRecordset rsRows While Not rsRows.EOF For j = 0 To rsRows.Fields.Count - 1 If j = 0 Then Loc = rsRows.Fields(j).Value If Loc = "N" Then C1 = C1 + 1 If Loc = "E" Then C2 = C2 + 1 If Loc = "I" Then C3 = C3 + 1 If Loc = "K" Then C4 = C4 + 1 If Loc = "Q" Then C5 = C5 + 1 If Loc = "T" Then C6 = C6 + 1 ' If X1 = 2 Then C8 = C8 + 1 End If Next j rsRows.MoveNext i = i + 1 Wend End Sub

Beliebteste Forumthreads (12 Monate)

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread