SQL/Router Oracle 5.1 Incorrect BufferLength in Long String

Post found bugs and possible workarounds.
johi

SQL/Router Oracle 5.1 Incorrect BufferLength in Long String

Post by johi » 18 Oct 2007, 10:44

SQL/Router Oracle 5.1 - Incorrect BufferLength in Long String

set in SqlFetchNext causes failure of string concatenation.

repro case:



Sql Handle: hSql

Boolean: bRet

Long String: sSelect

Long String: sSelectString

Long String: sFrom

Long String: sWhere

Long String: sGroupBy

Number: nIsGroupBy

Number: nRetVal



Set bRet = SqlPrepareAndExecute(hSql,

'SELECT aza_select, aza_from, aza_where, aza_groupby, aza_isgroupby FROM saza_t_art

WHERE aza_azaid = 1 INTO :sSelect, :sFrom, :sWhere, :sGroupBy, :nIsGroupBy')

Set bRet = SqlFetchNext(hSql, nRetVal)

! SalStrLength(sSelect) correctly returns 249

! SalStrGetBufferLength(sSelect) returns 998 instead of 500

! Workaround Set sSelect = SalStrLeftX(sSelect, SalStrLength(sSelect) )

Set sSelectString = 'SELECT ' || sSelect || ' FROM ' || sFrom

! does not append the ' FROM ' part.

! Observation: only the first into-Variable has incorrect BufferLength



Versions

Team Developer SQLWindows 5.1.0.5917

SQL/Router Oracle sqlora32.dll 5.1.0.5911

Database Table, Content

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production

NLS_CHARACTERSET=AL32UTF8

NLS_NCHAR_CHARACTERSET=AL16UTF16

same problem with another DB with Encoding WE8ISO8859P1



SQL> desc saza_t_art



Name Null? Type

-------------------------- -------- --------------------

AZA_AZAID NOT NULL NUMBER(10)

AZA_FRMID NUMBER(4)

AZA_T_BEZ VARCHAR2(10 CHAR)

AZA_SELECT VARCHAR2(2000 CHAR)

AZA_FROM VARCHAR2(2000 CHAR)

AZA_WHERE VARCHAR2(2000 CHAR)

AZA_GROUPBY VARCHAR2(2000 CHAR)

AZA_AEND DATE

AZA_USRID VARCHAR2(30 CHAR)

AZA_USER_DEFINED NOT NULL NUMBER(1)

AZA_USER_DEFINED_BASE NUMBER(10)

AZA_ISGROUPBY NUMBER(1)



SQL> select * from saza_t_art where aza_azaid = 1;



AZA_AZAID AZA_FRMID AZA_T_BEZ

---------- ---------- ----------

AZA_SELECT

--------------------------------------------------------------------------------------------------------------------------

AZA_FROM

--------------------------------------------------------------------------------------------------------------------------

AZA_WHERE

--------------------------------------------------------------------------------------------------------------------------

AZA_GROUPBY

--------------------------------------------------------------------------------------------------------------------------

AZA_AEND AZA_USRID AZA_USER_DEFINED AZA_USER_DEFINED_BASE AZA_ISGROUPBY

------------------- ------------------------------ ---------------- --------------------- -------------

1 160 WO-2436

distinct adr_adrid, decode (adr_adrkz,1, 'red', 'black'), decode (adr_adrkz,1, '@', ''), adr_name1, adr_name2, adr_ort, ad

r_plz, adr_staid, adr_str, adr_wrgid, adr_sort, adr_geox, adr_geoy, decode(adr_lbo, 1,'X',null), decode(adr_sperre, 1,'X',

null)

sadr_adresse





2005.06.28 17:10:42 johi 0 0


Jean-Marc Gemperle

SQL/Router Oracle 5.1 Incorrect BufferLength in Long String

Post by Jean-Marc Gemperle » 16 Nov 2007, 18:22

Hi Joe



You should see TD-3927 fixed in the december patch



Jean-Marc

johi

SQL/Router Oracle 5.1 Incorrect BufferLength in Long String

Post by johi » 20 Dec 2007, 14:48

Unfortunately, in SP1 this bug has been replaced by another one.
The long string now contains UTF-8 instead of UTF-16. The new workaround would be
SalStrToWideChar(sSelect, sSelect1, ENC_UTF8)
Set sSelect1 = sSelect
Nothing has changed for us.

Oracle native router: Select into Long String does not work without additional workaround

Why not get rid of Long String special treatment? See enhancement request TD-4048,
viewtopic.php?f=21&t=901&p=1820

Jean-Marc Gemperle

SQL/Router Oracle 5.1 Incorrect BufferLength in Long String

Post by Jean-Marc Gemperle » 20 Dec 2007, 19:21

Hi Joe,

Yes...sorry to hear. I entered issue TD-4181 to address this. So it seems now all long string with ORACLE are returned as UTF8...
Well with SB this does not cause problem. Will check other back-ends.

PS: I agree with the feature, on a side note it was question to have specific data type for binay data, it is not exaclty the same but just wanted to mentiom that.

JM
You do not have the required permissions to view the files attached to this post.

johi

SQL/Router Oracle 5.1 Incorrect BufferLength in Long String

Post by johi » 21 Dec 2007, 13:24

Hi Jean Marc, what is the meaning of SB?
Regarding Long String: Maybe there is a reason for keeping long string, but it is hard to understand, why the oracle router refuses to store a database column longer than 254 characters in a normal String (an empty string is stored instead). The length limit of 254 comes from the 16 bit version of Gupta for Windows 3.1, where only a single byte was used for string length.

Jean-Marc Gemperle

SQL/Router Oracle 5.1 Incorrect BufferLength in Long String

Post by Jean-Marc Gemperle » 21 Dec 2007, 16:36

Hi Dave,

Sorry about the use of acronyms. SB stands for SQLBase, TD you know TEAM DEVELOPER, WAM Web Application manager, RB report builder, ON object nationalized, TOM team object manager. Also I often refert to SB10 API, I imply here SQLBASE INTERNALTIONAL (version 10) API the one you MUST use now with TD 5.1...

Anyway yes string vs long string allways caused trouble and confusion....It got me caught too recently in an application where you had a Table Window with a column with a CLOB (correctly defined as LONG STRING) another column VARCHAR2 < 255 BUT INCORRECTLY defined as LONG STRING caused error 210 (Invalid long field number) on SalTblPopulate() only when using NATIVE ROUTER, NOT OLEDB. Did not find the reason until someone point me out the use of the long string for the table window column... Those problem allways existed. CCOD.PDF p4.3 online manual gives a table of data type mapping and you must make sure it matches. All this are design issues as far as I know mainly due to the uses of the SQLBASE API when routers are used, because of string data types in TD not matching the corresponding character column types in databases forcing the user to use long string for char and varchar column with length greater that 254 characters.

Currently for that issue of having now UTF8 returned in long strings using TD 5.1SP1, the only solution is to convert them using SalStrToWideChar() with nEncoding parameter set to ENC_OEMCP or ENC_UTF8 OR using a LONG colun data type instead varchar2!

We are currently looking at that new issue....

Jean-Marc

Jean-Marc Gemperle

SQL/Router Oracle 5.1 Incorrect BufferLength in Long String

Post by Jean-Marc Gemperle » 01 Feb 2008, 16:56

Hi,

SQL/Router Oracle 5.1 Incorrect BufferLength in Long String IS FIXED IN TD 5.1SP1. This is not what caused that regression in the SP1 bellow

TD-4181
TD5.1 SP1 REGRESSION. Long string are now returned as UTF8

I confirm this bug to be fixed in internal SP2 build 6286. So you should get it fixed once the SP2 is out, actually planed end of FEB

Cheers
JM

johi

SQL/Router Oracle 5.1 Incorrect BufferLength in Long String

Post by johi » 08 Apr 2008, 07:06

Back to the Start.
With SP2 the situation is like it was when I started this thread in October.
SalStrGetBufferLength(sSelect) now returns 996 instead of 500, in October, 998 was returned.
The result is the same: subsequent string concatenation does not work.
Workaround is still valid: Correct internal buffer length with
Set sSelect = SalStrLeftX(sSelect, SalStrLength(sSelect) )
We have hundred or more long string select into columns!
I get the impression that Oracle native router is not worth being tested anymore.

caa

SQL/Router Oracle 5.1 Incorrect BufferLength in Long String

Post by caa » 09 Apr 2008, 00:32

johi wrote:I get the impression that Oracle native router is not worth being tested anymore.
Sigh, not true. We spent a good deal of time testing all of the native routers. I can't imagine why we missed this, especially after it was confirmed fixed. Our fault, and I'll try to figure out what went wrong.

johi

SQL/Router Oracle 5.1 Incorrect BufferLength in Long String

Post by johi » 09 Apr 2008, 08:31

Chris, thanks for your reply.
Our other big problem is TD-3949. In TD 5.1 SP2: Final Fixed Defects there were also two items referring to SqlPLSQLCommand with Oracle, so we hoped that TD-3949 might be fixed too, but in vain.
Greetings from Austria

caa

SQL/Router Oracle 5.1 Incorrect BufferLength in Long String

Post by caa » 09 Apr 2008, 14:18

Johi,

Although TD-3949 is still open as a defect, our internal testing showed it as not being reproducible anymore with the repro case that we have. We could use a repro case that shows the problem.

johi

SQL/Router Oracle 5.1 Incorrect BufferLength in Long String

Post by johi » 09 Apr 2008, 15:37

Repro case (TD-3949 ?)
Native Router Oracle, Oracle Instant Client 10.2, DB Oracle 10.2.0.4 with NLS_CHARACTERSET AL32UTF8.
TD 5.1 SP2. Works fine with TD 3.1

String sText
On SAM_AppStartup
Set SqlUser = 'sped'
Set SqlPassword = 'xxxx'
Set SqlDatabase = 'TFUTF8'
Call SqlConnect(hSql)
Set sText = 'johi'
Set bRet = SqlPLSQLCommand(hSql, "DBMS_APPLICATION_INFO.SET_CLIENT_INFO (sText )")
Set bRet = SqlDisconnect(hSql)
Today with SP2 I got.
ORA-04043: object DBMS_APPLICATION_INFO does not exist
If I click on continue on the standard error handler,
SqlDisconnect fails with
ORA-24323: value not allowed
In our application, I get ORA-24323 right away on SqlPLSQLCommand.

tommi has a similar problem with the standard Oracle package DBMS_APPLICATION_INFO
http://newforum.com/phpBB3/viewtopic.ph ... INFO#p3708

johi

SQL/Router Oracle 5.1 Incorrect BufferLength in Long String

Post by johi » 09 Apr 2008, 15:45

If I prefix the PLSQLCommand with SYS:
SYS.DBMS_APPLICATION_INFO.SET_CLIENT_INFO (sText)
I get yet another error message
ORA-24328: illegal attribute value

Jean-Marc Gemperle

SQL/Router Oracle 5.1 Incorrect BufferLength in Long String

Post by Jean-Marc Gemperle » 10 Apr 2008, 14:30

Hi Johi,

Yes confirming with 5.1 SP2 and ORA 11g, your sample is attached to the bugTD-3949.

Thanks JM

Return to “Bug Reports”

Who is online

Users browsing this forum: [Ccbot] and 0 guests