TD 2.1 : How to select a CLOB-field from ORACLE10 DB?

[Timmy]

Hi,

I've been looking to replace all LONG datafields in our Oracle10-DB with CLOB/BLOB fields.
I created a simple test-app and have some issues.

Inserting or updating a CLOB seems to be no problem using SqlSetLongBindDatatype( 1, 22 ) for CLOB and SqlSetLongBindDatatype( 1, 23 ) for BLOB.
... but reading them back out is.
The problem isn't even the into longstring.
The moment a CLOB-field is included in the select it gives an oracle error on the SqlPrepare(hSqlConnect,sLSQL):

ORA-01084 invalid argument in OCI call

I've tried searching the forums for this, but I'm still clueless.
Using TD2.1 so the USE_LOB = 1 option is not available.

I've read about setting the DB-CharacterSet: select value$ from sys.props$ where name='NLS_CHARACTERSET' --> WE8ISO8859P15, but where/how do I set NLS_LANG? I was getting errors when adding this to SQL.INI.

Or does TD2.1 not support CLOB at all?

Thanks in advance

[Warren]

Hi,
We are using TD2005 so I don;t know whether this will work for you, but it might be worth a try.
We found issues with the USE_LOB = 1 so instead we do the following.

Whenever we need to Save/Retrieve a BLOB we connect with a new SQL Handle ( hSql) and call

Call SqlSetParameterAll( hSql, DBP_ORAUSELOB, 1, STRING_Null, TRUE )

When we have finished we then disconnect the Sql Handle.

I hope this helps.

Regards,

Warren

[Jeff Luther]

Or does TD2.1 not support CLOB at all?

Here's the place to check that: http://www.guptatechnologies.com/Produc ... atrix.aspx
scroll down to 2.1 and click. That's the list of what was certified by Gupta for 2.1.

"support" -- well, CTD 2.1 was not certified for it, and like Warren did, general types of questions can be answered if anyone here can provide additional info. If you are not already doing so, I would be sure to put together a small test case and really test this. LONG is all 2.1 knew about back in its day, so CLOB/BLOB will need to be well-tested before converting over. I would think (I have not tested 2.1) you would need to call SqlSetLongBindDatatype for the fetch as well, like this:
SqlPrepare...
SqlExecute...
SqlSetLongBindDatatype...
SqlFetchNext...

[Timmy]

This is what i've tried now:

Call SqlConnect(hSql)
Call SqlSetParameterAll( hSql, 5128, 1, STRING_Null, TRUE )
Set bLOk = SqlPrepare(hSql, sLSQL)
Set bLOk = bLOk AND SqlExecute(hSql)
Call SqlSetLongBindDatatype( 1, 22 )
Set bLOk = bLOk AND SqlFetchNext(hSql, nLFetchResult)

* Seperate sql-handle
* Set DPB_ORAUSELOB, constant not defined in TD2.1
* SetLongBindDatatype

The following pop-up error appears when executing the SqlPrepare-line:


I left out the into part, so this is just a select of a clob-field.
I think the uselob parameter is not recognised (no error when not found) and the prepare can't handle it.