CLOB in Oracle

[Yvonne Grossmann]

Hi,

I have problems to handle long strings with oracle. I'm using the data type CLOB since LONG is said to be deprecated. The following statements were send from the TD with SqlPrepareAndExecute, sSTRING01 is a string and sLONGSTRING01 a long string.

The statement

update mytable set myclob = :sSTRING01

writes only the first 4000 characters into the database. After the statement

update mytable set myclob = :sLONGSTRING01

a select in SQLTalk shows only the first character. The length is the double of the character number. If I select each character separately, every second character ist shown empty, e.g. substr (myclob, 1, 1) = x, substr (myclob, 2, 1) = , substr (myclob, 3, 1) = x, substr (myclob, 4, 1) = and so on. Using the function SqlSetLongBindDatatype doesn't influence the behaviour.

The second problem is to read the data. Both

select myclob from mytable into :sSTRING01

and

select myclob from mytable into :sLONGSTRING01

bring empty results, with length 0.

How to handle long strings with oracle? I'm using Oracle 10g via ODBC and TD 5.2 SP 1. Using the data type NCLOB instead of CLOB makes no difference.

Thank you and best regards

Yvonne

[Jeff Luther]

Well, CLOB is a LONG-type and if sSTRING01 really is a String (not a Long String) then I'm surprised anything is inserted and/or fetched.

(In the TD world any LONG-type DB column on the back requires a Long String variable or table window/grid column or data/multiline field. TD uses that Long String type as an indicator to use the correct SQL/API calls for reading/writing longs to/from the DB.)

update mytable set myclob = :sLONGSTRING01

This Long String var. is what I would expect to work for I/O to your CLOB. What's "longbuffer=" to in your SQL.INI file in the [oragtwy] section?

Once again, I'd need to get a test case from you, Yvonne, to be able to test what you have. Could be simple: it creates a table, has a var./multiline field with a value, has pbs for insert, fetch, etc.

[Yvonne Grossmann]

Hi Jeff,

Yes, I know that a CLOB should be used with long strings, but the results with a string were a little bit less strange

Well, the table and the entry are the same as in my post to the load problem.

The TD 5.2 test application ist the following:

Long String: sLONGSTR01
Long String: sLONGSTR02
Long String: sLONGSTR03

Function: CREATE_CHARS
Returns
String:
Parameters
Number: nCOUNT
String: sCHAR
Local variables
String: sRESULT
Actions
Set sRESULT = ''
While nCOUNT > 0
Set sRESULT = sRESULT || sCHAR
Set nCOUNT = nCOUNT - 1
Return sRESULT

On SAM_AppStartup
Call SqlConnect (hSQL_SYS)

Set sLONGSTR01 = 'b' || CREATE_CHARS (10000, 'w') || 'x'

Call SqlSetLongBindDatatype (1, 22)

Call SqlPrepareAndExecute (hSQL_SYS,
' update my_ktab set ktab_ltext = :sLONGSTR01 ')

Call SqlPrepareAndExecute (hSQL_SYS,
' SELECT ktab_ltext from my_ktab into :sLONGSTR02 ')

Call SqlCommit (hSQL_SYS)

Call SalMessageBox (sLONGSTR02, SalNumberToStrX (SalStrLength (sLONGSTR02), 0) || SalStrRightX (sLONGSTR02, 1), 0)

Call SqlPrepareAndExecute (hSQL_SYS,
' SELECT ktab_format from my_ktab into :sLONGSTR03 ')

Call SqlCommit (hSQL_SYS)

Call SalMessageBox (sLONGSTR03, SalNumberToStrX (SalStrLength (sLONGSTR03), 0) || SalStrRightX (sLONGSTR03, 1), 0)

This application OracleClob.app updates the clob column ktab_ltext and tries to select the modified column ktab_ltext and the clob column ktab_format containing an unmodified ascii string. In both cases it shows empty strings with length 0.

The following Script shows the result of the modification of ktab_ltext in SqlTalk:

select length (ktab_ltext), substr (ktab_ltext, 1, 1), substr (ktab_ltext, 2, 1), substr (ktab_ltext, 3, 1), ktab_ltext from my_ktab;

The double length, only every second character is found. The SqlDeveloper shows, that the other characters are not printable.

My longbuffer in the sql.ini is 327670, both for odbctr and oragtwy.

Thank you and best regards

Yvonne

[Jeff Luther]

Hi: when I mentioned a test case, what I meant was a zip of a TD app. that could be run. As you can see from your copy/paste of the code, to be able to 'run' that means I have to hand type all that in.

Displaying a short code fragment in your msg. is fine. In that case, select all the code then click the 'Code' button at the top of this multiline edit control. That will preserve the format, like this from your code:

Code: Select all

While nCOUNT > 0
    Set sRESULT = sRESULT || sCHAR
    Set nCOUNT = nCOUNT - 1

As you can see from your msg, just a copy/paste left-justifies everything. I'll take a look at this issue when I can.

[Jeff Luther]

Yvonne:

P.S. Looks like Jean-Marc forwarded me some file you had sent to him. I'll look at this when I get a chance.

[Yvonne Grossmann]

Hi Jeff,

yes, I've send the same test case to Jean-Marc, indeed. By the way: I've forgotten to write the SqlFetchNext statement into the test application, but that makes no difference in this case.

You've asked, if I had uninstalled the TD5.2 before installing the SP1: I've tryed to do so, but it's not possible to install the SP1 without a valid TD5.2 installation. This makes sense, since the SP1 is a free download, but the TD5.2 only with GLS.

In the mean time I've brought the TD5.2 SP1 to run on another machine with the native oracle router, but 9i. The application handles the long strings properly, insert, update and select. Only the load with SqlTalk inserts the ascii codes of the characters of the long strings instead of the characters, e.g. '41' instead of 'A'.

I know, that due to the compatibily matrix oracle 9i isn't certified with TD5.2. Does it only mean, that it has not been tested, or do you know real problems? It seems to be the best working combination in my tests.

Thank you and best regards

Yvonne

[Jeff Luther]

Couple issues, Yvonne:

First, the call to SqlSetLongBindDatatype() must be after SqlPrepare() and before SqlExecute(), so you cannot use SqlPrepareAndExecute(). It needs to be like this:

Code: Select all

If NOT SqlPrepare(hSQL_SYS, 
      ' update my_ktab set ktab_ltext = :sLONGSTR01  ')
   ...
If NOT SqlSetLongBindDatatype (1, 22)
   ...
If NOT SqlExecute (hSQL_SYS )
   ...

SqlFetchNext() calls were missing, as you noted.

Also, because your test is inserting using SQLTalk, but fetching using your TD case, while you updated the value for ktab_ltext and it is correctly being sent to the CLOB, you only use the SQLTalk insert value for ktab_format and it isn't correct in the DB table. So, I added code in your sample to correct that by updating the ktab_format column as well. (Looks like it should be: 'R15,"","",L,"","",#.##0,00' ) I'll attach my corrected test case. (Looks like an update in SQLTalk would need an Oracle to convert to a correct value for ktab_format?)

[Yvonne Grossmann]

Hi Jeff,

I have two issues, indeed. One is loading data with SQLTalk, which I've described in the other topic. Here I have the problem, that I' get the error message for the date using odbc and that the clob are imported as the ascii values (e.g. the string '41' instead of the string 'A) with the native router. I didn't use any TD application for this issue, but watched the data with SQLTalk or the Oracle SQL Developer.

The second issue is the handling of clob with a TD application. Your corrected application has the same behaviour as my original: it works with the native router but not with odbc. With the native router it works also without any SqlSetLongBindDatatype function call, probably since text (22) is the default due to the documentation. So it seems that I should forget odbc but try to use the native router in every case. But that doesn't solve the other problem to load data in SQLTalk. But this is the other topic.

Thank you and best regards

Yvonne