Re: Weird listbox issue (Gupta(SqlWindows), Oracle)

MVk - Monika V. kycka schreef:
A weird problem has occured, maybe someone could give any advice?

Client has 2 Oracle 10g databases - test and real. Gupta application
used with test db works fine, but when connecting to real one, some
listboxes don't fill.Tendency is seen on litboxes, generated like this
one:
-----------------------------------------------------------------------------------------------------------------------------------------
Number: nComboID[*]
String: sComboValue[*]
String: sSqlSelectParm

Set sSqlSelectParm = "
SELECT
DP.ID,
'" || "(" || "' || DP.CODE || '" || ") " || "' || SUBSTRB( DP.NAME,
0, 200 )
FROM
DR_OBJEKTAS.DR_PRODUKTAS_T DP
ORDER BY
DP.CODE"

Set sSqlSelectParm=sSqlSelectParm||' INTO :nComboID[nI], :sComboValue
[nI]'

Call SalListClear( hWndItem )
Call SalArraySetUpperBound( nComboID, 1, -1 )
Call SalArraySetUpperBound( sComboValue, 1, -1 )

If SqlPrepareAndExecute( hSqlClass, sSqlSelectParm )
While SqlFetchNext( hSqlClass, nInd )
Call SalListInsert( hWndItem, nI, sComboValue[nI] )
Set nI=nI+1
Set __nUpperBound=nI
-----------------------------------------------------------------------------------------------------------------------------------------
CODE column type VARCHAR2(50)
NAME column type VARCHAR2(254)

Result should be list box filled with records like:
(code1) some_name_1
(code2) some_name_2
etc.

But application retrievs only first number value leaving string empty.

I've did loads of experiments to test possible reasons:
1. changing "(" and ")" to oracle chr(40) and chr(41) -
doesn't work
2. if selecting only one of the
columns - OK
3. CODE ||
'abc'
- OK
4. NAME ||
'abc'
- doesn't work
5. SUBSTRB( NAME, 0, 200 ) || 'abc'
- doesn't work
6. just SELECT
'abc' -
doesn't work

If adding bound variable String: sTest with same value "abc":
7. CODE ||
sTest
- OK
8. NAME ||
sTest
- doesn't work
9. SUBSTRB( NAME, 0, 200 ) || sTest -
doesn't work
10. just SELECT
sTest - OK
11. tried reducing SUBSTRB length value -
didn't work
12. switching between SUBSTR, SUBSTRB, SUBSTRC - doesn't work
13. tried switching listbox field datatype String<>Long String -
doesn't work
14. tried switching sComboValue to Long String -
application hangs
15. any of selects above work just fine if executed in Pl/Sql
Developer

The only work arround that worked fine was creating view in oracle
with concatenated string and selecting directly from there. But this
is not a good solution as there are many such listboxes and creating
view for each is not the best thing.

Actually, I think it IS the best thing. Better than cluttering your code with '""||() and so on... Views are for free, you don't have to pay for them...

And instead of testing all kind of modifications to your code, I suggest you try to find the difference in the databases. It can't be an Oracle bug, for it does work in one of the databases.

I would perform these queries in SQL Plus and check for the differences in the result set (null values, lengths, number of records returned)

Shakespeare

Has anyone any ideas on what's going on? Application is same for test
and real databases, run from the same location. I suppose it might be
an oracle issue, but I don't seem to find any information regarding
this and ran out of ideas of what to look for and where...

MVk - Monika V. kycka
.

Relevant Pages

  • Weird listbox issue (Gupta(SqlWindows), Oracle)
    ... Client has 2 Oracle 10g databases - test and real. ... String: sSqlSelectParm ... But application retrievs only first number value leaving string empty. ...
    (comp.databases.oracle.misc)
  • Weird listbox problem regarding filling with string data
    ... Client has 2 Oracle 10g databases - test and real. ... String: sSqlSelectParm ... But application retrievs only first number value leaving string empty. ...
    (comp.databases.gupta)
  • handling oracle long data types
    ... databases. ... i need to select text that is stored as "long" in oracle 9i. ... compare that with a string that i read from a file. ...
    (perl.dbi.users)
  • Re: Query Oracle from Excel
    ... You will need the Oracle client sofware installed on the PC you're running ... What exactly is DBDataProvider and DBDataSource? ... Global prmUsername As String ... Dim adoConn As New ADODB.Connection ...
    (microsoft.public.excel.programming)
  • Re: Need your help [arguments for open source databases]
    ... >> I would like to have your opinions about open source databases versus ... >> in the universe of relational databases. ... The main difference between PostgreSQL and Oracle is manageability. ...
    (RedHat)
Loading