1934

Oracle database query throws error on second run

Question:

i have an VBA code where i am calling oracle to retrieve data twice using ODBC. First data retrieval is fine. But 2nd time it is saying ,

**RunTime Error '-2147467259 (80004005)'; Unspecified error**

My Code is as follows,

<blockquote>

Note: Same code works for connecting Teradata but failed when i use Oracle

</blockquote> 'First Data retrieval Query1 = "Select TableName from all_tables" CmdSQLData.CommandText = Query1 CmdSQLData.CommandType = adcmdText CmdSQLData.Timeout=0 set rs = CmdSQLData.Execute() 'Then code to store data ... 'This part gives proper result ... rs.close() 'Second Data retrieval Query2 = "Select * from db.Event" CmdSQLData.CommandText = Query2 CmdSQLData.CommandType = adcmdText CmdSQLData.Timeout=0 set rs = CmdSQLData.Execute() 'This line Gives Error - RunTime Error '-2147467259 (80004005)'; Unspecified error <blockquote>

Also i tried creating new command object as cmdSQLData1 but still same error

</blockquote>

May i know why the error is coming for second query ? There is no problem with query as i have tested in oracle directory. Please let me know

Answer1:

You won't see this documented much of anywhere, but reusing Command objects with different comamndText is actually a bad practice. You don't say what kind of connection you're using, but for example if it's ODBC, this will internally send a fake invalid SQL to Oracle to force a cleanup of some kind. So instead, throw away your Command object after use and create a new one.

Reusing Command objects is a <em>good</em> practice when you're re-executing the same query with different parameter values, but that's not the case here.

Answer2:

You do not need to use command text at all for those types of queries what you could do is :-

` Dim con As New ADODB.Connection Dim rs As New ADODB.Recordset

con.Open "DSN=Oracle", "User", "Password" rs.Open "select * from table_a", con ' Read all results rs.Close rs.Open "select * from table_b", con ' Read all results rs.Close con.Close

You only need to use "Command" if you plan to use a store procedure or a query with bound parameters.

Recommend

  • vba ODBC gives Unspecified error when oracle table is having timestamp
  • ASP.NET Session ending abruptly
  • Passing inputs to program prompt in a batch file
  • Timeout a command efficiently and capture output to a variable in bash script
  • Julia: Can you set a time limit on eval
  • variable scope in AJAX calls
  • ASP.NET 2.0 Session Timeout
  • How to get text which is not part of any element using jsoup?
  • Query timeout expired in django-mssql when executing custom SQL directly
  • Is it possible to specialize on a static lifetime?
  • ExecuteNonQuery not working in C#
  • How to make HttpWebRequest async
  • Timeout a query
  • Get a trait object reference from a vector
  • Consuming a WCF service in a Java Client using wsHttpBinding
  • Cannot upload to OneDrive using the new SDK
  • Security issues with PHP's Readfile method
  • onBackPressed() not being executed
  • How do I change content of ComboFieldEditor?
  • Ajax jQuery multiple calls at the same time - long wait for answer and not able to cancel
  • What is Eclipse's Declaration View used for?
  • Javascript Callbacks with Object constructor
  • Hazelcast - OperationTimeoutException
  • Display Images one by one with next and previous functionality
  • Weird JavaScript statement, what does it mean?
  • Do I've to free mysql result after storing it?
  • Jquery - Jquery Wysiwyg return html as a string
  • Delete MySQLi record without showing the id in the URL
  • SVN: Merging two branches together
  • File upload with ng-file-upload throwing error
  • Proper folder structure for lots of source files
  • SQL merge duplicate rows and join values that are different
  • How to set the response of a form post action to a iframe source?
  • Change div Background jquery
  • Qt: Run a script BEFORE make
  • Append folder name and increment by 1 using batch script
  • Is there any way to bind data to data.frame by some index?
  • Programmatically clearing map cache
  • reshape alternating columns in less time and using less memory
  • How can I use `wmic` in a Windows PE script?