Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Unanswered: VBScript only returning One Row from Oracle DB

    OK to start with the stats:
    Oracle 10g db on SunOS
    Windows 2003 standard edition 64 bit
    Installed on Windows Box:
    Oracle server 64 bit and Oracle ODAC 64 bit.

    *******Please Note that I AM NOT connecting to the DB on the Windows Box***********

    The SQL that I am passing:
    Code:
    select	*
    from
    (
    	select	distinct
    			cuname,
    			custno
    	from
    	(
    		select
    				tc.cuname,
    				tc.custno
    		from	temp_topcust tc,
    				temp_roles r,
    				roletypes rt
    		where	tc.custno = r.custno and
    				r.roletypeid = rt.id and
    				rt.name = :roletype1 and
    				tc.cuname like :custname
    		union all
    		select
    				tc.cuname,
    				tc.custno
    		from	temp_topcust tc,
    				temp_roles r,
    				roletypes rt
    		where	tc.custno = r.custno and
    				r.roletypeid = rt.id and
    				rt.name = :roletype2 and
    				tc.custno like :custno
    	)
    	order by
    			cuname,
    			custno
    )
    where rownum <= 10
    The DB Connection code in the application:
    Code:
    Set conn = openDB(marstop)
    		Set cmd = Server.CreateObject ("ADODB.Command")
    		Set cmd.ActiveConnection = conn
    		cmd.CommandText = sql
    		cmd.CommandType = adCmdText
    The code behind the openDB function:
    Code:
    Function openDB(provider)
    	Dim conn
    	Set conn = CreateObject("ADODB.connection")
    	conn.Open provider
    	Set openDB = conn
    
    End Function
    The Connection string to the DB that is used:
    Code:
    Const MARSTOP = "Provider=OraOLEDB.Oracle;Data Source=mrstools;User Id=marstop;Password=mcme74;Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;Incr Pool Size=5;Decr Pool Size=2;"
    The symptoms:
    Using ISQLPlus I can run this SQL replacing the variables with the correct responses and I get 10 Rows returned.

    When I use the webapp I only get one row returned. I have used Response.Write to display the results and only get one row. I have also changed the SQL to a count(*) and get a count of over 24K, so I know the DB has more than one answer.

    I have uninstalled and reinstalled the oracle clients many times, I have run the code manualy against the DB, I have done row counts and join inspections. Nothing that I have done can explane this, so now I am looking to the experts....What in the world is going on?

    Any help would be great, because I am not even getting an error code...

    Thanks for the support
    Jim
    Last edited by JDionne; 04-25-09 at 13:52.
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Decent post!
    I suspect the root cause is outside of Oracle.
    A basic question is can you ever get more than 1 row returned via your ADO?
    SELECT OBJECT_NAME FROM USER_OBJECTS where rownum <= 3;

    I suspect not.

    Good Luck!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    You are corect I still only get one row with your code.

    I am very new at this and I have no idea what this means.
    I do not even know what to google.
    Im guessing I have a problem with the ADO...or is it the asp.net instalation? Any thoughts?

    Quote Originally Posted by anacedent
    Decent post!
    I suspect the root cause is outside of Oracle.
    A basic question is can you ever get more than 1 row returned via your ADO?
    SELECT OBJECT_NAME FROM USER_OBJECTS where rownum <= 3;

    I suspect not.

    Good Luck!
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Im guessing I have a problem with the ADO...or is it the asp.net instalation? Any thoughts?
    I don't do Windoze; so I can not assist.
    I suggest visiting other forums (VB, ADO, ASP) to see if you can find working examples interfacing with Oracle.
    I know it can be done, but don't know any specifics.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    The one bit that I have not mentioned is that this is a server migration for this app. the current production environment works just fine, and I have duplicated it. So I have a working example....but something in this environment is not right....
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >So I have a working example....but something in this environment is not right...
    So it is time to compare & contrast.
    The one bit of advice I have for you is that Oracle RDBMS does not know or care about the flavor of client connecting to it.
    Oracle treats OCI, Java thin, ADO, etc. all the same.
    So my guess is that the problem exists between client & Oracle listener;
    because once the request get to the listener all are treated the same.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Thats an interesting thought...the oracle listener....where would i look for the config for that? On the oracle server i would guess? what would I be looking for with the listener?

    Quote Originally Posted by anacedent
    >So I have a working example....but something in this environment is not right...
    So it is time to compare & contrast.
    The one bit of advice I have for you is that Oracle RDBMS does not know or care about the flavor of client connecting to it.
    Oracle treats OCI, Java thin, ADO, etc. all the same.
    So my guess is that the problem exists between client & Oracle listener;
    because once the request get to the listener all are treated the same.
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Thats an interesting thought...the oracle listener....where would i look for the config for that? On the oracle server i would guess? what would I be looking for with the listener?

    There is nothing really to configure WRT the listener.
    Which version of Oracle to 4 decimal places?

    See the fact you are getting 1 row back, means listener is working.
    Otherwise it would be zero rows plus an error.
    Realize that the listener is only a temporary middle man.
    The listener gets the initial connection request from client.
    It taps the DB on its shoulder to inform DB a new client wants to connect & provides IP# of client to DB.
    The DB the sends a packet to the client & from then onwards the listener is no longer involved with anything between client & DB server.
    See the fact you are getting 1 row back, means listener is working.
    Otherwise it would be zero rows plus an error.

    The glitch is likely within the client s/w itself from where I sit.
    I have to believe Oracle is returning multiple rows, but for some unknown reason the application gets only a single row.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by JDionne
    Thats an interesting thought...the oracle listener....where would i look for the config for that?
    Forget about the listener.
    It is definitely not the cause of your problem.

    Background: it merely establishes the connection between the client (your program) and the Oracle server.
    It never does any processing of either the query sent or the data returned.

  10. #10
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Oracle Version from DB table

    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

    Quote Originally Posted by anacedent
    >Thats an interesting thought...the oracle listener....where would i look for the config for that? On the oracle server i would guess? what would I be looking for with the listener?

    There is nothing really to configure WRT the listener.
    Which version of Oracle to 4 decimal places?

    See the fact you are getting 1 row back, means listener is working.
    Otherwise it would be zero rows plus an error.
    Realize that the listener is only a temporary middle man.
    The listener gets the initial connection request from client.
    It taps the DB on its shoulder to inform DB a new client wants to connect & provides IP# of client to DB.
    The DB the sends a packet to the client & from then onwards the listener is no longer involved with anything between client & DB server.
    See the fact you are getting 1 row back, means listener is working.
    Otherwise it would be zero rows plus an error.

    The glitch is likely within the client s/w itself from where I sit.
    I have to believe Oracle is returning multiple rows, but for some unknown reason the application gets only a single row.
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  11. #11
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Ok so it looks like from both posts that the listener is not my issue. It is still back to my client config...but I am using the oracle ODAC. Is there a config for the ODAC i should be looking into?

    Quote Originally Posted by shammat
    Forget about the listener.
    It is definitely not the cause of your problem.

    Background: it merely establishes the connection between the client (your program) and the Oracle server.
    It never does any processing of either the query sent or the data returned.
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >this is a server migration for this app.
    >the current production environment works just fine,
    How many distinct DB server systems?
    How many distinct DB instances?
    How many distinct client systems?
    Provide actual names so everyone can count & have same frame of reference.

    Which systems can interoperate successfully with each other?
    Which systems have problem with producing correct/desired/expected results?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    There are two distinct DB servers, each DB server has one instance. For arguments sake we will call them ProductionDB and TestDB

    Each DB Environment has one Windows Web server pointed to them. Again we will call them ProductionWeb and TestWeb

    ProductionDB and ProductionWeb talk to eachother correctly.
    TestDB and TestWeb is where the issue is. I have even pointed Testweb to ProductionDB and gotten the same results as when it is pointed to TestDB. I cannot reverse this test for obvious reasons.

    For this reason I thnk it has to be an environmental issue with TestWeb. Im just not sure what environmental issue to chase....

    Quote Originally Posted by anacedent
    >this is a server migration for this app.
    >the current production environment works just fine,
    How many distinct DB server systems?
    How many distinct DB instances?
    How many distinct client systems?
    Provide actual names so everyone can count & have same frame of reference.

    Which systems can interoperate successfully with each other?
    Which systems have problem with producing correct/desired/expected results?
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I'd like to know the results going from ProductionWeb to TestDB.

    >I thnk it has to be an environmental issue with TestWeb.
    I concur.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  15. #15
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    So I just played with my prod environment....dont tell anyone...

    I just reconfigured an old virtual directory to point to the new testDB...and it works just like it should...

    What on the testweb server is configured wrong? I just dont get this at all...

    Quote Originally Posted by anacedent
    I'd like to know the results going from ProductionWeb to TestDB.

    >I thnk it has to be an environmental issue with TestWeb.
    I concur.
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •