Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ADO & RDO Error

Posted on 1999-07-27
14
Medium Priority
?
498 Views
Last Modified: 2013-12-25
Hello!

  I've been trying to find a solution for these errors and I can't, can somebody help me please ???

  I'm using VB6 to access an Oracle Database, but I need to execute a BIG length SQL (more than 120 words) using either ADO or RDO technology...

The error that I get using RDO is: "NOT ENOUGH STACK SPACE", while the Error that I get using ADO is: "UNEXPECTED FAILURE"

  If I tried to execute a short lenght SQL like: "Select * from TB", it doesn't produce any error...Why ???
 
  The reason of executing a complex query is that I need to get from a single SQL the data stored in 9 tables, with 19 fields each approximately and parameters, big enough ah!!

  S.O.S  :)
 
  Oh! I forgot, Why is it that the recordcount property always says -1 instead of the number of records obtained through the SQL statement ????
0
Comment
Question by:donotknow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +4
14 Comments
 

Author Comment

by:donotknow
ID: 1507161
Edited text of question.
0
 

Author Comment

by:donotknow
ID: 1507162
Edited text of question.
0
 

Author Comment

by:donotknow
ID: 1507163
Edited text of question.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 15

Expert Comment

by:Tommy Hui
ID: 1507164
Note too sure about Oracle, but on Sql Server, there is a concept called stored procedures. A stored procedure consists of a script stored on the server that can be invoked through ADO calls. The stored procedures can have parameters, so it is not entirely hardcoded, but most of it can. Then the SQL statement is much shorter.
0
 
LVL 2

Expert Comment

by:vspeter
ID: 1507165
I am pretty sure that you've written your SQL statement in stored procedure form. If that is the case then you don't have to worry about how long is your SQL bcoz.

If you have not then you better be, there are lots of advantages. From your concerns, its limitation in term of the max number chars can be overcome via various techniques.

The error returns by RDO and ADO might not be due long SQL statement, but I think it is due to the fact that you are joinning quite a number of tables and the overall number of records is way too much for the stack space available.
0
 

Author Comment

by:donotknow
ID: 1507166
Thank you for your suggestions thui and vpester, but I don't know how to write my SQL in procedure form..

  Suposse that my SQL is:

 SQL = "SELECT ID, NAME, ADDRESS, CITY, STATE FROM PEOPLE, COUNTRIES WHERE PEOPLE.ID = COUNTRIES.ID(+) AND NAME LIKE 'SUSAN' AND CITY LIKE 'MIAMI' AND STATE LIKE 'FL'"

  Using RDO I would do it like:

  set RS = cn.openresultset(SQL, rdopenforwardonly, rdlockreadonly)

  How can I call a procedure from RDO so I would retrieve records to my recordset RS ????

 Thanks :)
0
 

Author Comment

by:donotknow
ID: 1507167
Edited text of question.
0
 

Author Comment

by:donotknow
ID: 1507168
 Oh! I forgot, Why is it that the recordcount property always says -1 instead of the number of records obtained through the SQL statement ????
0
 
LVL 1

Expert Comment

by:pietjepuk
ID: 1507169
As to your last question:

Recordcount cannot be performed on a ForwardOnly recordset. Has to be KeySet or Static.
0
 
LVL 5

Accepted Solution

by:
mayhew earned 800 total points
ID: 1507170
If you're just looking to get a resultset from a select statement, I don't know that I would bother with a stored proc.

Why not just create a view on the Oracle box that you can select from in your vb app?


In Oracle, create the view like:

create view MyView as
select ....     --Your messy query goes here.



Then in your vb app, replace the messy query with:

SQL = "Select * from MyView"


That should clear up your problem.

And pietjepuk is right.  You need to declare a cursor type that will allow a movelast and then a movefirst.
0
 
LVL 1

Expert Comment

by:rdobson
ID: 1507171
The recordset also has to use client-side cursors in order for the recordcount property to work (rs.cursortype = adclientside).

If you are using an odbc provider to access the oracle db, then there is a 255 char limit to sql statements. You need to pass an array or strings instead of just a long string. Look in the vb documentation on how to do this. If you are really stuck, just post a message and I will put up code on how to do this.

There is no sense in messing with stored procedure if what you really want is to issue the sql from your app.
0
 
LVL 5

Expert Comment

by:mayhew
ID: 1507172
Have you had any success with this?
0
 
LVL 1

Expert Comment

by:rdobson
ID: 1507173
Mayhew... is that a question for me? Then the answer is yes. I have successfully done this several times from many apps.
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6823078
This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.

This is the Community Support link, if help is needed, along with the link to All Topics since many new ones were recently added.

http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
http://www.experts-exchange.com/jsp/zonesAll.jsp
 
Thanks,
Moondancer
Moderator @ Experts Exchange
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question