Solved RichText and Oracle

General discussion forum about all databases other than SqlBase.
Johann
Austria
Posts: 369
Joined: 30 Mar 2017, 09:58
Location: Vienna

RichText and Oracle

Post by Johann » 17 Jun 2012, 08:08

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

Re: RichText and Oracle

Post by Jeff Luther » 18 Jun 2012, 23:46

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
Austria
Posts: 369
Joined: 30 Mar 2017, 09:58
Location: Vienna

Re: RichText and Oracle

Post by Johann » 19 Jun 2012, 10:55

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

Re: RichText and Oracle

Post by Jeff Luther » 20 Jun 2012, 00:27

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
Austria
Posts: 369
Joined: 30 Mar 2017, 09:58
Location: Vienna

Re: RichText and Oracle

Post by Johann » 20 Jun 2012, 09:54

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
Austria
Posts: 369
Joined: 30 Mar 2017, 09:58
Location: Vienna

Re: RichText and Oracle

Post by Johann » 21 Jun 2012, 12:07

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

Re: RichText and Oracle

Post by Jeff Luther » 21 Jun 2012, 19:26

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.

Return to “General Discussion”

Who is online

Users browsing this forum: No registered users and 0 guests