RichText and Oracle

[Johann]

Hello dear friends,

I want to use richttext in my app on a oracle backend. I hve there a varchar2(4000) column and want to convert the content into a blob. What I did: added a new blob column, wanted to update the blob with the plain varchar2 column content -> didn't work, some conversion should take place but don't know what kind.

Is maybe a function in oracle which does the conversion?

Any hint is appreciated

Regards
Hans

[Jeff Luther]

That should work in a TD app to fetch each row from the varchar2 INTO a Long String variable, then insert that into that row's blob.

Often it can be helpful to detail some issue you report, like here:
wanted to update the blob [how?] with the plain varchar2 column content -> didn't work [why not?]

[Johann]

Hi Jeff,

the update statement was straight forward: update table_with_blob set col_blob = col_varchar2;

error ORA-01465 invalid hex number

Solution:

update table_with_blob set col_blob = UTL_RAW.CAST_TO_RAW(col_varchar2);
commit;
select id, UTL_RAW.CAST_TO_VARCHAR2(table_with_blob) from table_with_blob;

Regards
Hans

[Jeff Luther]

So your Oracle column is RAW? If so, this page has a good write-up on this topic:
http://en.allexperts.com/q/Oracle-1451/raw-datatype.htm

RTF data is considered 'binary' in TD and might go in BLOB or VARCHAR2 column too. And you'd want to use SqlSetLongBindDatatype( ..., 23 ) indicating a bind var. type of binary, for DML called between the Prepare and the Execute. (And on select after PrepareExecute and before the Fetch.)

[Johann]

Hi Jeff,

the above happened not in TD environment but in SqlTalk, and sure I use the SqlSetLongBindDatatype( ..., 23 ) after Prepare and before Execute/Fetch in TD. The thing above, was to change the ora-datatype (DDL) to hold lager amounts of data than a varchar2. With 8K max (varchar2) you will run very fast out of range - rtf has a big amount of formatting characters, not to speak about images embedded in rtf texts.

Regards
Hans

[Johann]

Hi Jeff,

a question, why is it necessary to use the SqlSetLongBindDatatype( ..., 23 ) function? When the statement is prepared, I'm sure it's known by the prepare function, what types of columns are addressed in the statement. I mean, the SqlSetLongBindDatatype( ..., 23 ) stuff could also be done automatically in the TD runtime.

Regards
Hans

[Jeff Luther]

and sure I use the SqlSetLongBindDatatype( ..., 23 ) after Prepare and before Execute/Fetch in TD.

No, what I wrote was: on select after PrepareExecute and before the Fetch
like this:
SqlPrepareAndExecute()
SqlSetLongBindDatatype( ... )
SqlFetchNext call(s)

For DML (INSERT/UPDATE) it's a bit different:
SqlPrepare()
SqlSetLongBindDatatype( ... )
SqlExecute call(s)

why is it necessary to use the SqlSetLongBindDatatype( ..., 23 )

So the router know how to set up buffers and what type of data it is handling. Default type is 22 (Text) so it is not necessary, though a good way to self-document your code -- but for binary data '23' is not required.

what types of columns are addressed in the statement

As another forum thread clarified, it is not the DB column type that is the issue, it is the data type or kindthat is involved which is the issue.