SQL Queries returning empty Recordset

[mmateyak]

I'm having trouble with an ASP/VBScript driven application. For some reason, when I connect to an Oracle 8.1.6 Database and Execute an SQL Query...the results are very erroneous.

I have two applications:

Application A, which is erroneous on SQL queries
Application B, which ALWAYS works and returns the proper rows.

Sometimes Application A returns the desired rows. Other times, it returns nothing and I get an EOF for the first Recordset read.(even though I know there are rows there...identical query to the first one).

One way I've found to "refresh" the application is to STOP/START the IIS Admin Service and

http://WWW Service.

But, this gets pretty tedious.

Another way I've noticed is with Application B, I can try to query the same Database (and it works all the time with App B), then if I go an try Application A again...it works sometimes (other times it doesn't).

There are multiple users using these apps at any given time.
I have played around with everything in terms of ADO Objects, Opening, Closing, Set to nothing, etc...but I don't see anything different between the two in terms of database connectivity or management.

Any ideas what could possibly be causing App A to sometimes return an Empty Recordset?

i.e.
Not closing the previous connection properly?
Resource (ASP.DLL) contention with multiple users utilizing the same app?


Any suggestions on how to catch any potential errors?


Any assistance would be greatly appreciated...Thanks!

 

[mmateyak]

ttt

 

[mmateyak]

ttt

Anyone have an answer or thought on this issue?

 

[Dynapen]

What kind of database are you connecting to, and how are you calling the recordset? The money's gone, the brain is shot.....but the liquor we still got.

 

[mmateyak]

Oracle 8.1.6 hosted on Sun Solaris
Oracle 8.1.7.0 ODBC Drivers

Code Skeleton:
--------------

Dim objConn
Dim logRec
Dim sqlquery

Set objConn = Server.CreateObject("ADODB.Connection&quot
objConn.Open "dsn=someDSN;uid=user;pwd=pass;"

sqlquery = "SELECT * FROM SOMETABLE WHERE SOMEFIELD = 'SOMEVAL'"

Set logRec = objConn.Execute(sqlquery)

If (logRec.EOF) Then
%>
<B>No Data Found for Given Search Criteria</B>
<%
End If

logRec.close
objConn.close

Set logRec = nothing
Set objConn = nothing


Other Side Notes:
-----------------

The particular query is dynamically built based on a user's HTML form selection. With the same identical HTML form selection (and thus the same identical SQL query), I experience the problems. Sometimes the rows are returned, other times no rows are returned.

I have checked the ASP Error Object collection and it always returns 0. I've compared the ODBC connection properties with the working application, and they are identical. There is nothing on the ASP/VBscript application level that is indicating any error. It just seems like the data being returned by the query is very inconsistent.

I've also verified the user connection pooling in Oracle, and there is no contention with the database (5 user connections total (minus the Oracle system ones)...with a max of like 200 available) So I don't think connection contention is an issue either.

Different queries to the same database and same tables from a different application ALWAYS work and return the correct rows. So, that makes me believe this is SQL Query specific.

At this point, I'm beginning to think it may be the Oracle 8.1.7.0 ODBC driver that is causing the problems, since it is responsible for managing the connection and query details between the client sided application and server sided/host database.

Any thoughts? I can't seem to find a clear answer anyway, and will be trying out the more updated ODBC drivers soon. But, I wanted to get some feedback from some ASP/VBScript programmers to see if they've experienced this problem before.

 

[Dynapen]

First thought would be to verify that you have the exact same SQL statment being run. Use the same HTML forms selection and do a response.write on the SQL statement to make sure they are the same. Then pass both queries to the Server using the connection that you are using in the page.

the 8.1.7 ODBC driver. That should let you know if hte problem is web baes, DB Based, or in between. The money's gone, the brain is shot.....but the liquor we still got.

 

[mmateyak]

I've actually already done the following...but I will verify again to make sure.

I've output the SQL query, that is passed to the Execute command, to the HTML page.

I've also copied and pasted that SQL query into PL *SQL and executed it from there and it returned the rows I expected.

Thanks for the help thus far, I'll let you know how I make out with the verification.

Anything else come to mind? Anything other than the ODBC drivers?

 

[jlgdeveloper]

Does the pull always succeed in VB? If so, write your code/page/output in a vb dll, call that from the web page, and you are free and clear. It will be compiled, so will work faster. Jonathan Galpin

http://www.keen.com/categories/categorylist_expand.asp?sid=5215353&gid=0&pmode=0

 

[mmateyak]

I'm new to ASP/VBscript driven programming, so I have no experience with VB DLLs or invoking them with web based applications.

Any tutorials you know of online that can help me along that route (to at least give it a go and see what happens?)

I also don't have the Visual Suite here at work, although I can get it if need be.

But, I don't think the issue is ASP/VBScript as much as it is the driver!

Oracle doesn't even support 8.1.7.0 ODBC drivers anymore...I would imagine there were quite a few problems with them corrected in a later version.


Any other thoughts, please let me know!


Thanks