Home » Infrastructure » Windows » DbCreateDynaset from text file (Excel VBA windows 7)
DbCreateDynaset from text file [message #630064] Fri, 19 December 2014 09:32 Go to next message
oteixeira
Messages: 33
Registered: May 2007
Member
Hello to all,

I need to execute a huge sql statement which is not easy to handle inside the VBA code.
So i wrote it into a text file and load it into the qry1 variable.
When i try to use it:
Set excDynaset = OraDatabase.DbCreateDynaset(qry1)


It errors with "Wrong number of arguments or invalid property assignment"
Does someone know if there is a way of overcome this?
Thanks a lot in advance for any kind help.
Octavio
Re: DbCreateDynaset from text file [message #630066 is a reply to message #630064] Fri, 19 December 2014 09:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to provide more of your code.
The declaration instructions and where you set them.

Re: DbCreateDynaset from text file [message #630067 is a reply to message #630066] Fri, 19 December 2014 10:09 Go to previous messageGo to next message
oteixeira
Messages: 33
Registered: May 2007
Member
Michel, thank for answering.

Basically i'm reading the file when opening the workbook and executing the query in a module:

Private Sub Workbook_Open()
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")
    Set OraDatabase = OraSession.OpenDatabase("PRODL", "apps/apps", 0&)
    ConnectStat = True
    g_strVar = ImportTextFile("C:\lixo\Sheet_1.sql")

    executar
    
    Exit Sub

End Sub

Function ImportTextFile(strFile As String) As String
    Open strFile For Input As #1
    ImportTextFile = Input$(LOF(1), 1)
    Close #1
End Function

Public Sub executar()
	Dim qry1 As String
	qry1 = g_strVar
	OraDatabase.Parameters.Remove "qlocation"
	OraDatabase.Parameters.Remove "qstatus"
	OraDatabase.Parameters.Remove "qdatai"
	OraDatabase.Parameters.Remove "qdataf"
	OraDatabase.Parameters.Add "qlocation", UserForm1.TextBox1.Text, ORAPARM_INPUT
	OraDatabase.Parameters.Add "qstatus", UserForm1.TextBox2.Text, ORAPARM_INPUT
	OraDatabase.Parameters.Add "qdatai", UserForm1.DTPicker1, ORAPARM_INPUT
	OraDatabase.Parameters.Add "qdataf", UserForm1.DTPicker2, ORAPARM_INPUT
	Set excDynaset = OraDatabase.DbCreateDynaset(qry1)  'HERE the error occurs
End Sub


Octavio
Re: DbCreateDynaset from text file [message #630068 is a reply to message #630067] Fri, 19 December 2014 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From the documentation (it is an old I know but as I said in your previous topic I didn't use VBA since last century):

Quote:
Description

Creates an OraDynaset object from the specified SQL SELECT statement and options.

Usage

Set oradynaset = oradatabase.CreateDynaset(sql_statement, options, SnapShotID)

Arguments
	Description
sql_statement
	A string containing any valid Oracle SQL SELECT statement.
Options
	A bit flag indicating the status of any optional states of the dynaset. You can combine one or more options by adding their respective values. Specifying &H0& gives the following defaults for the dynaset:
· Behave like Visual Basic Mode for a database: Field values not explicitly set are set to NULL, overriding server column defaults.
· Perform automatic binding of database parameters.
· Strip trailing blanks from character string data retrieved from the database.
· Create an updatable dynaset.
· Cache data on client.
· Force a MoveFirst on dynaset creation.
· Maintain read-consistency.
SnapShotID [optional]
	A SnapshotID obtained from the SnapShot property of an OraDynaset.

The following table describes the options flag values.

Options Flag Values

Constant
	Value
	Description
ORADYN_DEFAULT
	&H0&
	Accept the default behavior.
ORADYN_NO_AUTOBIND
	&H1&
	Do not perform automatic binding of database parameters.
ORADYN_NO_BLANKSTRIP
	&H2&
	Do not strip trailing blanks from character string data retrieved from the database.
ORADYN_READONLY
	&H4&
	Force dynaset to be read-only.
ORADYN_NOCACHE
	&H8&
	Do not create a local dynaset data cache. Without the local cache, previous rows within a dynaset are unavailable; however, increased performance results during retrieval of data from the database (move operations) and from the rows (field operations). Use this option in applications that make single passes through the rows of a dynaset for increased performance and decreased resource usage.
ORADYN_ORAMODE
	&H10&
	Behaves same as Oracle Mode for a database except it affects only the dynaset being created. If database was created in Oracle Mode, dynaset inherits the property from it (for compatibility)
ORADYN_NO_REFETCH
	&H20&
	Behaves same as ORADB_NO_REFETCH mode for a database except this mode affects only the dynaset being created. If the database was created in ORADB_NO_REFETCH mode, the dynaset inherits the property for compatibility.
ORADYN_NO_MOVEFIRST
	&H40&
	Does not force a MoveFirst on dynaset creation. BOF and EOF are both TRUE.
ORADYN_DIRTY_WRITE
	&H80&
	Update and Delete will not check for read consistency.


It seems the second parameter is not optional; so use ORADYN_DEFAULT to use the default behaviour.

Re: DbCreateDynaset from text file [message #630070 is a reply to message #630068] Fri, 19 December 2014 10:44 Go to previous message
oteixeira
Messages: 33
Registered: May 2007
Member
Michel, great!
Set excDynaset = OraDatabase.DbCreateDynaset(qry1, 0&)

It's working!
Thanks a million.
Previous Topic: OracleDBConsole<Instance> don't start after install a second database
Next Topic: Common Array Manager
Goto Forum:
  


Current Time: Thu Mar 28 18:39:09 CDT 2024