Re: Oracle/PowerBuilder dll connection issues

Dan schreef:
> We are having very strange problems using oracle 9i and the
> powerbuilder oracle drivers. Certain sql statements are retrieving
> several records and then hanging while oracle drops the session. I
> have started a thread about this a long time ago but I got no where
> with it. It contains logs and more info though:
> http://groups.google.com/group/comp.databases.oracle.misc/browse_frm/thread/c0adea9871bc2eab?hl=en
>
> The two servers we access and have had a problem with are accessed over
> a VPN. I don't think this problem has anything to do with loose cables
> or a firewall or anything though for two reasons:
>
> 1. The queries that give us problems select specific columns and if I
> make the slightest change to the column list, it works fine. For
> example, one query that is currently giving me this problem is:
> SELECT "STAFF"."NAME_LAST",
> "STAFF"."NAME_FIRST",
> "STAFF"."LOGIN_ID",
> "STAFF"."STAFF_ID",
> "STAFF"."STATION_EXTENSION",
> "STAFF"."STATUS",
> "STAFF"."ACTIVE",
> "STAFF"."TITLE"
> FROM "STAFF"
> while even the addition of a static column fixes it:
> SELECT "STAFF"."NAME_LAST",
> "STAFF"."NAME_FIRST",
> "STAFF"."LOGIN_ID",
> "STAFF"."STAFF_ID",
> "STAFF"."STATION_EXTENSION",
> "STAFF"."STATUS",
> "STAFF"."ACTIVE",
> "STAFF"."TITLE",
> ' '
> FROM "STAFF"
>
> 2. If I run the same query from another utility such as Toad or
> SQLPlus, it works fine, but running it through the PowerBuilder ide or
> any application created using PowerBuilder results in this problem.
>
> These problems seem to have to do with both the specific records being
> selected, and the specific columns being selected. #1 above shows what
> I mean about columns and I will give you an example of what I mean with
> records: through a process of trial and error (deleting rows and
> putting them back) we were able to determine that the statement above
> fails when retrieving 1 specific row (it retrieves about 2100 out of
> the 2306 records in the staff table before hanging. I assume the next
> row it would retrieve would be the one we identified because all rows
> are retrieve if I delete that one.)
>
> I tried a few more things with the problem record from the example
> above: I backed up the row, along with 6 other rows, to a temp table
> (T_STAFF1) and could select from it fine. I then backed those same
> rows up to another temp table (T_STAFF2) and ran the following:
> <B>insert into t_staff2 SELECT * FROM STAFF WHERE STAFF_ID NOT IN
> (SELECT STAFF_ID FROM T_STAFF2) which inserted 2299 records and I was
> still able to select from T_STAFF2 fine. I then deleted the bad row
> from T_STAFF2 and reinserted it into T_STAFF2 from T_STAFF1 and then
> hit the problem again when selecting from T_STAFF2.
>
> If none of this seems to make any sence, that's because the problem
> makes no sence. I really hope someone knows what this problem is
> and/or can help me out since we've run into this problem off and on for
> a long time and it's very annoying!
>
> Thanks,
> Dan
>

Drop the "STAFF" prefix on every field:

    SELECT "NAME_LAST",
           "NAME_FIRST",
           "LOGIN_ID",
           "STAFF_ID",
           "STATION_EXTENSION",
           "STATUS",
           "ACTIVE",
           "TITLE",
      FROM "STAFF"

Better yet, drop the quotes!

Or even better: drop PowerBuilder; does it still generate lock statements?

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...