Weird listbox issue (Gupta(SqlWindows), Oracle)

From: MVk - Monika V. kycka <m.vaitkute_at_gmail.com>
Date: Tue, 2 Dec 2008 03:00:43 -0800 (PST)
Message-ID: <aaa9d694-a93d-43a6-ac81-b5ade03df887@x8g2000yqk.googlegroups.com>

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.

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 - 05:00:43 CST