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
Received on Tue Dec 02 2008 - 10:32:28 CST