Persistant non-blocking operation problem - OO4O and Wizards(Archived)

When using asynchronous database calls with non-blocking methods, I am getting a persistant error on the CreateSQL method call - the error is "OIP-04153: Non-Blocking operation in progress". This is raised even though the database connection has been closed and restarted and no non-blocking operations called. The methods used are similar to the following example code:
Dim OraDatabase as OraDatabase
Dim OraStmt as OraSQLStmt
Dim stat as long
Dim OraSess as OraSession
Set OraSess = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase =OraSess.OpenDatabase("ExampleDb", "scott/tiger", 0)
'execute the select statement with NONBLOCKING mode on
set OraStmt = OraDatabase.CreateSQL ("update emp set sal = sal + 1000", ORASQL_NONBLK)
'Check if the call has completed
stat = OraStmt.NonBlockingState
MsgBox "Cancelling the asynchronous operation that is underway"
End if
I cannot cancel the operation using OraStmt.Cancel because OraStmt Is Nothing!


Create SQL in VBA

I wan to create a create sql in VBA. I read a some sites but I haven't found why my script doesn't work.
This is my script:
Sub Create_Temp_Table()
Dim username
Dim password
Dim sid
Dim objSession
Dim objdatabase
Dim OraDynaset
Dim oraSqlStmt
Dim strSQL
Dim stdout
Dim i
Set stdout = WScript.stdout
username = ""
password = ""
' connect to database
stdout.WriteLine "Connecting to database . . ."
Set objSession = CreateObject("OracleInProcServer.XOraSession")
Set objdatabase = objSession.OpenDatabase(SERVICE_NAME, username & "/" & password, 0)
' This command takes a long time to execute when doing ranks/order bys
Set OraDynaset = objdatabase.DBCreateDynaset(strSQL, 0&)
Do While OraDynaset.EOF = False
Set OraDynaset = Nothing
Set objSession = Nothing
Set objdatabase = Nothing
End Sub
The error is at line
Set objSession = CreateObject("OracleInProcServer.XOraSession")
Can somebody help me? thx 
Please describe the environment in which you trying to make this run. (What OS version and edition, Oracle Client package and version, VBA host app, etc.)
Tyrus wrote:
The error is at line
Set objSession = CreateObject("OracleInProcServer.XOraSession")What error? 
I have created another scrip:
Sub Create_table()
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim OraDynaset As OraDynaset
+' Use OO4O+
Set objSession = CreateObject("OracleInProcServer.XOraSession")
Set objdatabase = objSession.OpenDatabase("XE", "SYSTEM/BCG", 0&)
Set OraDynaset = OraDatabase.CreateDynaset("DROP TABLE TTT_DATEPAR", 0&)
Set OraDynaset = OraDatabase.CreateDynaset("CREATE TABLE TTT_DATEPAR(STARTDATE date,ENDDATE date)", 0&)
Set OraDynaset = OraDatabase.CreateDynaset("INSERT INTO  TTT_DATEPAR VALUES ('01-JAN-2010', '01-JUN-2010')", 0&)
Set OraDynaset = objdatabase.DBCreateDynaset(Sql, 0)
Set objSession = Nothing
Set objdatabase = Nothing
End Sub
My OS is Windows7 with Office 2010. I have installed Oracle client 11.2.0 and Oracle Express Edition 10gE. The database name is XE (default name). The error is:
Run-time error '91': Object variable or With block variable not set with debug on
Set OraDynaset = OraDatabase.CreateDynaset("DROP TABLE TTT_DATEPAR", 0&)
I wrote also this command and I get the same error:
Set OraDynaset = OraDatabase.CreateDynaset("select table_name from all_tables", 0&)
The script model is this one
i tried now other methods. Like this one from
rowcount = OraDatabase.ExecuteSQL("create table X (A date)")
rowcount = OraDatabase.ExecuteSQL("insert into X values (sysdate)")
OraSession.CommitTrans ' or OraSession.Rollback
Error run time 424 Object required.
the only script that does something is this one:
Sub GetEmployees()
+' Use OO4O+
Set objSession = CreateObject("OracleInProcServer.XOraSession")
Set objdatabase = objSession.OpenDatabase("XE", "SYSTEM/BCG", 0)
Sql3 = "INSERT INTO  TTT_DATEPAR VALUES ('01-AUG-2010', '01-JAN-2011')"
Set OraDynaset = objdatabase.DBCreateDynaset(Sql1, 1)
Set OraDynaset = objdatabase.DBCreateDynaset(Sql2, 0)
Set OraDynaset = objdatabase.DBCreateDynaset(Sql3, 0)
Set objdatabase = Nothing
End Sub
Does anyone know which command should i use for create, insert and drop? Is it DBCreateDynase or OraDatabase.ExecuteSQL
found the solution using this post Insert Range into table using vba
This is my script:
Sub test1()
Dim OraDatabase As Object
Dim OraDynaSet As Object
Dim OraSession As Object
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("XE", "SYSTEM/BCG", 0)
OraDatabase.ExecuteSQL "INSERT INTO TTT_DATEPAR VALUES ('01-JUN-2010', '01-JAN-2011')"
Set OraDynaSet = Nothing
Set OraDatabase = Nothing
Set OraSession = Nothing
End Sub 
Tyrus wrote:
Run-time error '91': Object variable or With block variable not set with debug on
Set OraDynaset = OraDatabase.CreateDynaset("DROP TABLE TTT_DATEPAR", 0&)Seems you forgot to set OraDatabase variable (in previous line objdatabase was set).
Add option explicit to your code to help avoid such mistakes.

If I open a connection, how do I close it?

Here is the way I connect:
Sub EmpData()
'Declare variables as objects
Dim OraSession As Object
Dim OraDatabase As Object
Dim EmpDynaset As Object
Dim flds() As Object
Dim fldcount As Integer
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("DEVDB", "scott/tiger", 0&)
Set EmpDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)
Do I have to close the connection before I can connect again? If so, how? Thanks, Jeremy 
Letting the objects go out of scope or setting them to Nothing will work. The disconnect may not be immediate in either case, but the InProc server will clean it up. If you are pooling connections then they are stored for possible reuse saving the overhead of a re-connection.

Problem Retrieving RecordSet Item from Oracle7 with OraOLEDB.Oracle

I have to connect to a Oracle7 DB from an VB application, the machine running the VB Program will have Oracle 8.1.7 & 8.1.7 ODBC latest patch installed. (due to other things running).
I can connect OK, and retrieve a recordset as expected with the syntax in the below sample code;
However as soon as I do;
myVar = objADORecordSet.Fields.Item("MY_FIELD").Value
I get things like ???||||||||?| instead of SOME_NAME
If i switch to a oracle8 DB the problem goes away immediately
I would really appreciate any help you can give me!
Thanks in Advance
'* Declare All Variables and Objects *'
Dim objADOConnection As Object
Dim objADORecordSet As Object
Dim myVar As Variant
Dim I As Long
Dim strConnectionString As String
'* Instantiate Objects and Variables *'
Set objADORecordSet = CreateObject("ADODB.RecordSet")
Set objADOConnection = CreateObject("ADODB.Connection")
'* Open AODB Connection, Get Records *'
strConnectionString = "Provider=OraOLEDB.Oracle;Data Source=ORACLE7;User ID=MY_USER;Password=MY_PASSWORD"
objADOConnection.Open (strConnectionString)
Set objADORecordSet = objADOConnection.Execute("SELECT MY_FIELD FROM MY_TABLE")
If Not objADORecordSet.EOF Then
I = 0
Do While Not (objADORecordSet.EOF Or objADORecordSet.bof)
myVar = objADORecordSet.Fields.Item("MY_FIELD").Value '*** I get things like ???||||||||?| instead of SOME_NAME
MsgBox "What Did We Find ??: " & myVar
I = I + 1
MsgBox "Number of Records: " & I
End If
'* Destroy Objects *'
Set objADORecordSet = Nothing
Set objADOConnection = Nothing

Using bind variables with sql statements

We connect from a VB 6.0 program via OO4O to an Oracle 8.1.7 database, using bind variables in connection with select statements. Running ok, but performance again by using bind vars not as good as expected!
When looking into the table v$sqlarea, we were able to detect the reason. We expected that our program submits the sql statement with bind vars, Oracle parses this once, and with each select statement again, we do not have a reparse. But: It seems that with each new session Oracle reparses the sql statement, that is, Oracle is not able to memorize or cache bind vars and statements. Even more worrying, this kind of behaviour was visible with each new dynaset, but the same database/session.
Is there anybody our there with an idea of what is happening here?
Code snippet:
Dim OraSession As OracleInProcServer.OraSessionClass
Dim OraDatabase As OracleInProcServer.OraDatabase
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase(my database", "my connect", 0&)
OraDatabase.Parameters.Add "my_bind", 0, ORAPARM_INPUT
OraDatabase.Parameters("my_bind").DynasetOption = ORADYN_NOCACHE
OraDatabase.Parameters("my_bind").serverType = ORATYPE_NUMBER ' Bind Var Type
Dim RS As OracleInProcServer.OraDynaset
strSQLstatement= "Select * from my_table where igz= [my_bind] "
Set RS = OraDatabase.CreateDynaset(strSQLstatement, &H4)
OraDatabase.Parameters("my_bind").Value = myValue
Cheers and thanks a lot :)
Michael Sonntag

OO4O and MTS

I have created a DLL in VB and registered it on the server as a COM component. I am using the open database method which specifies to use MTS. It seems to work well enough. I am pretty sure that Oracle Services for MTS is not installed on the server. A snippet of code is below.
Does OO4O get its connections from MTS's connection pooling? Basically I read some of the documentation and I still don't understand how OO4O and MTS interact or don't interact.
Function getDynaArray(ByVal sqlString As String, _
ByVal tnsString As String, _
ByVal loginString As String, _
ByVal maxBlocks As Long, _
ByVal fetchLimit As Long)
Dim dsess As OraSession
Dim dbase As OraDatabase
Dim dSet As OraDynaset
'Create the OraSession Object.
Set dsess = CreateObject("OracleInProcServer.XOraSession")
'Create the OraDatabase Object by opening a connection to Oracle.
Set dbase = dsess.OpenDatabase(tnsString, loginString, 10&)
'Create the OraDynaset Object.
Set dSet = dbase.CreateCustomDynaset(sqlString, 12&, 1024, 100, maxBlocks, fetchLimit, 500000)
' Set dSet = dbase.CreateDynaset(sqlString, 12)
If Not dSet.EOF Then
getDynaArray = dSet.GetRows
End If
Set dSet = Nothing
Set dbase = Nothing
Set dsess = Nothing
'Display the first record.
End Function
Not as I understand it. The docs are brief (to say the least):
Oracle database transactions initiated in Oracle Objects for OLE (OO4O) automatically participate in global transactions coordinated by the MS Distributed Transaction Coordinator (DTC) in the Microsoft Transaction Server (MTS), if all the following conditions are true:
�     The OpenDatabase method of OraSession uses the ORADB_ENLIST_IN_MTS option.
�     OO4O determines that it is running in the context of a global transaction in MTS.
�     Oracle Service for MTS is installed and running.
So nowhere do I see anything about it using the connection pool from MTS.